AutoShrink Transaction Log File

Hello All,

I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.)OS is Windows 2000 server sp3
2.)SQL Server 2000 (Build 2195) sp3
3.)Database Recovery Mode is set to Full
4.)Maintenance Plan for "Transaction Log Backup" is set to remove
files older then 1hr.
5.)The "AutoShrink" option is on for the DB properties.

Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth. When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First Backup Log <database name> With Truncate_Only
Second DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file."Bucfan1" <support@.comcomsystems.com> wrote in message
news:c2bc5e26.0312110808.4ea17b86@.posting.google.c om...
> Hello All,
> I have been encountering trouble with a SQL Server 2000 Transaction
> log file, mainly with the constant growth and lack of the autoshrink
> option. Here are the details:
> 1.) OS is Windows 2000 server sp3
> 2.) SQL Server 2000 (Build 2195) sp3
> 3.) Database Recovery Mode is set to Full
> 4.) Maintenance Plan for "Transaction Log Backup" is set to remove
> files older then 1hr.
> 5.) The "AutoShrink" option is on for the DB properties.
> Also as part of the Maintenance plan the database in question is
> backed up nightly. The problem is that the transaction log file has
> grown so much that it was consuming all of the free space on the hard
> drive so I then restricted the file growth. When all of the drive
> space was consumed or the "database log file is full" message was
> encountered I would run the following Query scripts to shrink the file
> manually:
> First - Backup Log <database name> With Truncate_Only
> Second - DBCC Shrinkfile (<database name>_log, 200)
> Which brings the transaction log file down to 200mbs. I need to
> automate this so that I don't have to keep babysitting this database
> or manually shrinking the log file.

It's not clear from your post how often you back up the transaction log. You
mention a database backup every night, but not a log backup. Only log
backups will free up space in the log - a full backup will not. When you
backup the log, the space will be reused for future transactions, or the log
will shrink if the space isn't needed. See "Transaction Log Backups" in
Books Online for more information.

Simon|||[posted and mailed, please reply in news]

Bucfan1 (support@.comcomsystems.com) writes:
> I have been encountering trouble with a SQL Server 2000 Transaction
> log file, mainly with the constant growth and lack of the autoshrink
> option. Here are the details:
> 1.) OS is Windows 2000 server sp3
> 2.) SQL Server 2000 (Build 2195) sp3
> 3.) Database Recovery Mode is set to Full
> 4.) Maintenance Plan for "Transaction Log Backup" is set to remove
> files older then 1hr.
> 5.) The "AutoShrink" option is on for the DB properties.
> Also as part of the Maintenance plan the database in question is
> backed up nightly. The problem is that the transaction log file has
> grown so much that it was consuming all of the free space on the hard
> drive so I then restricted the file growth. When all of the drive
> space was consumed or the "database log file is full" message was
> encountered I would run the following Query scripts to shrink the file
> manually:
> First Backup Log <database name> With Truncate_Only
> Second DBCC Shrinkfile (<database name>_log, 200)
> Which brings the transaction log file down to 200mbs. I need to
> automate this so that I don't have to keep babysitting this database
> or manually shrinking the log file.

To add to Simon's reponse: if you don't care about the transaction log,
and you don't need up-to-the-point recovery, but it is always to sufficient
from the nightly backup, your best option is simply to switch to
simple recovery.

In any case, I would advice against setting up some automatic shrink from
what you describe above. If you shrink the log, it will autogrow, and
autogrow takes resources and slows down the system.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Bucfan1" <support@.comcomsystems.com> wrote in message
news:c2bc5e26.0312110808.4ea17b86@.posting.google.c om...
> Hello All,
> I have been encountering trouble with a SQL Server 2000 Transaction
> log file, mainly with the constant growth and lack of the autoshrink
> option. Here are the details:
> 1.) OS is Windows 2000 server sp3
> 2.) SQL Server 2000 (Build 2195) sp3
> 3.) Database Recovery Mode is set to Full
> 4.) Maintenance Plan for "Transaction Log Backup" is set to remove
> files older then 1hr.

Remove what files?

I don't see a step here about actually performing a transaction log backup.

> 5.) The "AutoShrink" option is on for the DB properties.
> Also as part of the Maintenance plan the database in question is
> backed up nightly. The problem is that the transaction log file has
> grown so much that it was consuming all of the free space on the hard
> drive so I then restricted the file growth.

This to me indicates that transaction log backups are not occurring.

> When all of the drive
> space was consumed or the "database log file is full" message was
> encountered I would run the following Query scripts to shrink the file
> manually:
> First - Backup Log <database name> With Truncate_Only
> Second - DBCC Shrinkfile (<database name>_log, 200)
> Which brings the transaction log file down to 200mbs. I need to
> automate this so that I don't have to keep babysitting this database
> or manually shrinking the log file.

Note, the minute you do a backup log with truncate_only, you break the
transaction log chain and lose a lot of your recovery opportunities.

Also, avoid where possible shrinking the file, it can ultimately lead to
file level fragmentation.

Autoshrink Log

Hello: I have a Database with a file log of 75 Mb which I have increased to 150 MB, but when i execute a "backup log with no_log" the size is returned to the initial size, I must mention that autoshrink is disabled.
:confused:with no_log or truncate_only they will only remove the checkpointed transactions in the log, it will not reduce the footprint (file size). If you need to shrink the file use dbcc shrinkfile. But be careful in this as if your log regularly has to grow, you are hurting your performance by constantly shrinking it.

HTH|||Hi :

I know that, but my problem is that i dont want to "shrink" the file but when i execute "Backup log" the file log is "shrinked".|||What syntax did you use to increase it?|||I'm executing "Backup Log NAME with no_log".

Results : Truncate the log and my file log that i increased to 150 Mb is decresed to the initial size of 75 Mb.

:confused:|||What syntax did you use to increase the transaction log?|||I'm using Enterprise Manager.|||I ran this multiple times and with simple and full recovery mode and can not duplicate what you are experiencing. If you can do what you are trying to do in SQL, plese post it, if you do not, run profiler while you make your changes in EM. See if this helps at all:

create database testing
use testing
dbcc sqlperf('logspace')

alter database testing
modify file
(NAME = testing_log,
SIZE = 20MB)

dbcc sqlperf ('logspace')

backup log testing with no_log

dbcc sqlperf ('logspace')|||I know that you can't duplicate my experience, that's why is a problem. but thats happen sometimes in SQL 7.0.

Autoshrink efficiency

Howdy folks!

I've got a database that needs to run 24/7. I'm looking into maintanence options and wanted to run the following by y'all:

Ok, I've read the MSDN "Maintaining databases" article and noticed the following statement about autoshrinking: "This technique uses almost no processor time and memory". I also searched these forums and found that many users say autoshrinking heavily lags down sql transfers. So who's right? And if it does lag transfers, by how much?

Another question I have about autoshrink is fragmentation. It would seem to me that over time solely depending on autoshrink would cripple a server in terms of fragmentation; is this the case?

Also, does autoshrink (or manual shrinking or compacting) update the statistics?

Final question!!! I'm programming in native c++, is there a way for me to run commands such as "DBCC SHRINKDATABASE" in native OLE DB code?

Thanks!

Autoshrink in SQL CE takes place when the last connection to the database is closed, so it has very littel effect on your app.

Autoshrink is not a replacement for CompactDatabase, so you should do that regurarly (every 14 days in ceonnection with sync process, for example)

Statistics: Most likely...yes

the native interface exposes CompactDatabase on an Engine object, see this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1934047&SiteID=1

|||Thanks for the reply Erik!

One more question...if I insert 1000 rows, delete all but 50, then insert 950 more, will my filesize stay consistant to just inserting 1000 rows?

In other words, are deleted rows replaced when inserting new rows?
|||No, used pages are not reclaimed, that is why there are features like "auto shrink" and CompactDatabase

Autoshrink disabled but still running?

Hi!
I've disabled auto shrink but it still seems to be in
effect. I try to expand my .log file in a database to 300
MB and it works great but a few hours later the log is
40MB again.
Auto Shrink is _not_ enabled and there is no job
automatically shrinking the db.
Any ideas?
I use sql 7 on nt 4.Perhaps there's some old shrink instruction lurking in there which SQL
Server tries to handle asynchronously?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Henry Fischer" <anonymous@.discussions.microsoft.com> wrote in message
news:f23101c3bd9a$033a6e00$a601280a@.phx.gbl...
> Hi!
> I've disabled auto shrink but it still seems to be in
> effect. I try to expand my .log file in a database to 300
> MB and it works great but a few hours later the log is
> 40MB again.
> Auto Shrink is _not_ enabled and there is no job
> automatically shrinking the db.
> Any ideas?
> I use sql 7 on nt 4.|||Yes, I suspect there is. The database was marked for
autoshrink when it was restored a few weeks ago. I
disabled the autoshrink but it still shrinks, but only the
logfile though...
Any ideas on how to get rid of this "feature"?
>--Original Message--
>Perhaps there's some old shrink instruction lurking in
there which SQL
>Server tries to handle asynchronously?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Henry Fischer" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f23101c3bd9a$033a6e00$a601280a@.phx.gbl...
>> Hi!
>> I've disabled auto shrink but it still seems to be in
>> effect. I try to expand my .log file in a database to
300
>> MB and it works great but a few hours later the log is
>> 40MB again.
>> Auto Shrink is _not_ enabled and there is no job
>> automatically shrinking the db.
>> Any ideas?
>> I use sql 7 on nt 4.
>
>.
>|||> Any ideas on how to get rid of this "feature"?
Perhaps let it shrink once to the level it is aiming for (the autoshrink is
the same as specifying 25% in the DBCC SHRINKDATABASE command)?
Also, I'm pretty certain that there's a KB on this particular situation, it
might be worth spending some time to find that article.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hentry Fischer" <anonymous@.discussions.microsoft.com> wrote in message
news:0a1301c3be23$855db6b0$a101280a@.phx.gbl...
> Yes, I suspect there is. The database was marked for
> autoshrink when it was restored a few weeks ago. I
> disabled the autoshrink but it still shrinks, but only the
> logfile though...
> Any ideas on how to get rid of this "feature"?
> >--Original Message--
> >Perhaps there's some old shrink instruction lurking in
> there which SQL
> >Server tries to handle asynchronously?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Henry Fischer" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:f23101c3bd9a$033a6e00$a601280a@.phx.gbl...
> >> Hi!
> >>
> >> I've disabled auto shrink but it still seems to be in
> >> effect. I try to expand my .log file in a database to
> 300
> >> MB and it works great but a few hours later the log is
> >> 40MB again.
> >> Auto Shrink is _not_ enabled and there is no job
> >> automatically shrinking the db.
> >> Any ideas?
> >> I use sql 7 on nt 4.
> >
> >
> >.
> >|||Thanks Tibor,
I don't know if this really works but atleast it has so
far, which is goot.
I let it shrink down and then expanded the log to 500 MB.
Immidiately afterwards I shrunk it with DBCC SHRINKFILE
down to 300.
Now the log is actually 320 MB, but I can live with
that. :)
>--Original Message--
>> Any ideas on how to get rid of this "feature"?
>Perhaps let it shrink once to the level it is aiming for
(the autoshrink is
>the same as specifying 25% in the DBCC SHRINKDATABASE
command)?
>Also, I'm pretty certain that there's a KB on this
particular situation, it
>might be worth spending some time to find that article.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Hentry Fischer" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a1301c3be23$855db6b0$a101280a@.phx.gbl...
>> Yes, I suspect there is. The database was marked for
>> autoshrink when it was restored a few weeks ago. I
>> disabled the autoshrink but it still shrinks, but only
the
>> logfile though...
>> Any ideas on how to get rid of this "feature"?
>> >--Original Message--
>> >Perhaps there's some old shrink instruction lurking in
>> there which SQL
>> >Server tries to handle asynchronously?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Henry Fischer" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:f23101c3bd9a$033a6e00$a601280a@.phx.gbl...
>> >> Hi!
>> >>
>> >> I've disabled auto shrink but it still seems to be in
>> >> effect. I try to expand my .log file in a database to
>> 300
>> >> MB and it works great but a few hours later the log
is
>> >> 40MB again.
>> >> Auto Shrink is _not_ enabled and there is no job
>> >> automatically shrinking the db.
>> >> Any ideas?
>> >> I use sql 7 on nt 4.
>> >
>> >
>> >.
>> >
>
>.
>

AutoShrink Database Transaction Log

I am using SQL Server 2000 Enterprise Edition with SP3A.
Is there away that can I autoshrink the database
transaction log and not the database? Is there a command
I can run periodically to shrink the transaction log file?
Is there away I can tell which process caused the database
transaction log to grow so large?
Thank You,
DanShrink implies actually reducing the amount of space used by the actual OS
file. Shrink does not mean freeing up space within the transaction log
itself. You probably understand this, but I wanted to make the distinction
clear since generally speaking... you shoud NEVER autoshrink an important
production database. Why? Shrinking a file can create any number of
bottlenecks. Shink operations in large systems should be planned operations
in a managed production enviornment. And... Since you're running SQL EE...
you're probably running something very big. Otherwise you paid 4X more for
your license than you should have paid...
There is not a check box setting to autoshrink the log and not the database.
However, it would be easy to create a job to do this periodically that
issues dbcc shrinkfile. Again, I wouldn't recommend this...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx
.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan|||Here are a couple of good KB articles that should provided you with the
information necessary to shrink your tranlog
http://support.microsoft.com/defaul...8&Product=sql2k
http://support.microsoft.com/defaul...kb;EN-US;256650
As far as knowing what application is causing your tranlog to grow becomes a
little more difficult. You really need to know what applications you have
running against your database and how they are commiting transactions. Here
is an article that might help you understand why the transaction log grows.
Hopefully this will give you some ideas on what it is that is causing your
problems.
http://support.microsoft.com/defaul...5&Product=sql2k
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx
.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan

AutoShrink Database Transaction Log

I am using SQL Server 2000 Enterprise Edition with SP3A.
Is there away that can I autoshrink the database
transaction log and not the database? Is there a command
I can run periodically to shrink the transaction log file?
Is there away I can tell which process caused the database
transaction log to grow so large?
Thank You,
Dan
Shrink implies actually reducing the amount of space used by the actual OS
file. Shrink does not mean freeing up space within the transaction log
itself. You probably understand this, but I wanted to make the distinction
clear since generally speaking... you shoud NEVER autoshrink an important
production database. Why? Shrinking a file can create any number of
bottlenecks. Shink operations in large systems should be planned operations
in a managed production enviornment. And... Since you're running SQL EE...
you're probably running something very big. Otherwise you paid 4X more for
your license than you should have paid...
There is not a check box setting to autoshrink the log and not the database.
However, it would be easy to create a job to do this periodically that
issues dbcc shrinkfile. Again, I wouldn't recommend this...

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan
|||Here are a couple of good KB articles that should provided you with the
information necessary to shrink your tranlog
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...b;EN-US;256650
As far as knowing what application is causing your tranlog to grow becomes a
little more difficult. You really need to know what applications you have
running against your database and how they are commiting transactions. Here
is an article that might help you understand why the transaction log grows.
Hopefully this will give you some ideas on what it is that is causing your
problems.
http://support.microsoft.com/default...&Product=sql2k
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan

AutoShrink Database Transaction Log

I am using SQL Server 2000 Enterprise Edition with SP3A.
Is there away that can I autoshrink the database
transaction log and not the database? Is there a command
I can run periodically to shrink the transaction log file?
Is there away I can tell which process caused the database
transaction log to grow so large?
Thank You,
DanShrink implies actually reducing the amount of space used by the actual OS
file. Shrink does not mean freeing up space within the transaction log
itself. You probably understand this, but I wanted to make the distinction
clear since generally speaking... you shoud NEVER autoshrink an important
production database. Why? Shrinking a file can create any number of
bottlenecks. Shink operations in large systems should be planned operations
in a managed production enviornment. And... Since you're running SQL EE...
you're probably running something very big. Otherwise you paid 4X more for
your license than you should have paid...
There is not a check box setting to autoshrink the log and not the database.
However, it would be easy to create a job to do this periodically that
issues dbcc shrinkfile. Again, I wouldn't recommend this...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan|||Here are a couple of good KB articles that should provided you with the
information necessary to shrink your tranlog
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
As far as knowing what application is causing your tranlog to grow becomes a
little more difficult. You really need to know what applications you have
running against your database and how they are commiting transactions. Here
is an article that might help you understand why the transaction log grows.
Hopefully this will give you some ideas on what it is that is causing your
problems.
http://support.microsoft.com/default.aspx?scid=kb;en-us;317375&Product=sql2k
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan

autoshrink causes "Could not complete cursor operation...table schema changed&quo

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"
Has anyone saw this before ?
If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens ever
y
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.
Autoshrink was turned on as a way to keep physical transaction log size unde
r
control [yes, I know, not a good idea]
Thanks,
Andy Mackie.That is not the way to keep the log file under control. Either it needs
that much space or it doesn't. If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions. If it does need more space than you have you are only hurting
the situation by shrinking it.
Andrew J. Kelly SQL MVP
"A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
news:xn0e2pqso958n30000@.news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
> Has anyone saw this before ?
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
> Thanks,
> Andy Mackie.|||And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#qXFTUhYFHA.3096@.TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control. Either it needs
> that much space or it doesn't. If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions. If it does need more space than you have you are only
hurting
> the situation by shrinking it.
> --
> Andrew J. Kelly SQL MVP
>
> "A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
> news:xn0e2pqso958n30000@.news.microsoft.com...
cause[vbcol=seagreen]
>|||Paul S Randal [MS] wrote:

> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure bu
t
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
> Thanks
OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenanc
e
plans and re-indexing, that is the real problem (all 2nd hand info).
I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups ar
e
scheduled, to keep the log file under control. Hopefully that will sort
things out.
Thanks,
Andy.|||A Mackie wrote:

> Paul S Randal [MS] wrote:
>
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sor
t
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled t
o
run at the same time as some cursor-processing.
Andy.

autoshrink causes "Could not complete cursor operation...table schema changed"

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"
Has anyone saw this before ?
If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens every
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.
Autoshrink was turned on as a way to keep physical transaction log size under
control [yes, I know, not a good idea]
Thanks,
Andy Mackie.
That is not the way to keep the log file under control. Either it needs
that much space or it doesn't. If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions. If it does need more space than you have you are only hurting
the situation by shrinking it.
Andrew J. Kelly SQL MVP
"A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
news:xn0e2pqso958n30000@.news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
> Has anyone saw this before ?
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
> Thanks,
> Andy Mackie.
|||And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#qXFTUhYFHA.3096@.TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control. Either it needs
> that much space or it doesn't. If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions. If it does need more space than you have you are only
hurting[vbcol=seagreen]
> the situation by shrinking it.
> --
> Andrew J. Kelly SQL MVP
>
> "A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
> news:xn0e2pqso958n30000@.news.microsoft.com...
cause
>
|||Paul S Randal [MS] wrote:

> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure but
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
> Thanks
OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenance
plans and re-indexing, that is the real problem (all 2nd hand info).
I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups are
scheduled, to keep the log file under control. Hopefully that will sort
things out.
Thanks,
Andy.
|||A Mackie wrote:

> Paul S Randal [MS] wrote:
>
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sort
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled to
run at the same time as some cursor-processing.
Andy.

autoshrink causes "Could not complete cursor operation...table schema changed"

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"
Has anyone saw this before ?
If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens every
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.
Autoshrink was turned on as a way to keep physical transaction log size under
control [yes, I know, not a good idea]
Thanks,
Andy Mackie.That is not the way to keep the log file under control. Either it needs
that much space or it doesn't. If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions. If it does need more space than you have you are only hurting
the situation by shrinking it.
--
Andrew J. Kelly SQL MVP
"A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
news:xn0e2pqso958n30000@.news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
> Has anyone saw this before ?
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
> Thanks,
> Andy Mackie.|||And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#qXFTUhYFHA.3096@.TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control. Either it needs
> that much space or it doesn't. If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions. If it does need more space than you have you are only
hurting
> the situation by shrinking it.
> --
> Andrew J. Kelly SQL MVP
>
> "A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
> news:xn0e2pqso958n30000@.news.microsoft.com...
> > I've had a report that turning on autoshrink causes the error "Could not
> > complete cursor operation because the table schema changed after the
> > cursor
> > was declared"
> >
> > Has anyone saw this before ?
> >
> > If a maintenance plan to re-index kicked in during some long running
> > process
> > caused this error, that I could understand. But why would autoshrink
cause
> > this ? The only thing I can speculate is that autoshrink (which happens
> > every
> > 30 minutes I believe) is taking a sufficiently long time to run, that it
> > may
> > be timing out the long running application process.
> >
> > Autoshrink was turned on as a way to keep physical transaction log size
> > under
> > control [yes, I know, not a good idea]
> >
> > Thanks,
> > Andy Mackie.
>|||Paul S Randal [MS] wrote:
> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure but
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
> Thanks
OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenance
plans and re-indexing, that is the real problem (all 2nd hand info).
I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups are
scheduled, to keep the log file under control. Hopefully that will sort
things out.
Thanks,
Andy.|||A Mackie wrote:
> Paul S Randal [MS] wrote:
> > And shrink (auto or otherwise) may cause the error as it has to move IAM
> > pages. Depending on the kind of internal scan the cursor was doing, the
> > locks required to move the IAM page may be interpreted the by the scan as
> > the IAM chain changing and hence the schema changing. I'm not 100% sure
> > but it kind of makes sense to me. If its causing you difficulty and you
> > can't turn off autoshrink (you really should..), call PSS to help you.
> >
> > Thanks
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sort
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled to
run at the same time as some cursor-processing.
Andy.

autoshrink and msdb question

I have a test database and I detach and reattch this database during my testing on my test system. This database is originally from my production system. I have a couple questions that came up during my testing:

1. When you detach/attach a database from a different server with the same setup, does it gather it database setup information from the model database on the current server?

2. When you switch the autoshrink database option from enable to disable, do you have to restart the server?

3. How to you change the default make of the model database?

Thanks in advance for any help.1. No. It doesn't need to. It just attaches it. You can have users out of sync if you do this though, because the users main information is stored in master.

2. No.

3. errrrr, what?? You just change it. You might want to explain this one a little.

Autoshrink and Autogrow

I have a large number of databases on a couple of heavily
utilized production 2000 servers (about 80 on one and
about 120 on another). Both servers hung mysteriously
last week and one of them had the following error:
"WARNING: EC 859eb5c8, 0 waited 300 sec. on latch a04e98.
Not a BUF latch."
The KB (310834) says this is from an overstressed system,
however my perf mon stats do not show this (unless context
switching greater > 5,000 for sustained periods is a
problem). But that same KB article says that
autogrow/autoshrink can be responsible and due to space
limitations, I have the autoshrink on all db's. So here
are my questions:
1. How can I monitor autogrow and autoshrink?
2. How can I tell how much cpu and disk they're using?
3. What is an acceptable threshold for context switching
for a four cpu server?
Why on earth do you have autoshrink enabled?
http://www.aspfaq.com/
(Reverse address to reply.)

> 1. How can I monitor autogrow and autoshrink?
> 2. How can I tell how much cpu and disk they're using?
> 3. What is an acceptable threshold for context switching
> for a four cpu server?
|||Space. They wouldn't get me more space so I kept having
drives filling up. I finally talked them into getting me
more space on one of the servers and just did a
migration. I of course am about take all the autoshrinks
off on that server.
But on the other server where I am still struggling with
space issues, I am looking for "fuel" to get them to get
me more space. That's part of why I'd like to monitor the
server and show them the effect (besides fragmentation of
course) that this is having on the server.
How can I show them that?

>--Original Message--
>Why on earth do you have autoshrink enabled?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>.
>
|||Well, you can have a trace running with minimal events (e.g.
autogrow/autoshrink) and capture the duration / cpu usage of those specific
events. Have you ever used Profiler?
http://www.aspfaq.com/
(Reverse address to reply.)
"Art" <anonymous@.discussions.microsoft.com> wrote in message
news:008f01c4a63b$41f93970$a301280a@.phx.gbl...[vbcol=seagreen]
> Space. They wouldn't get me more space so I kept having
> drives filling up. I finally talked them into getting me
> more space on one of the servers and just did a
> migration. I of course am about take all the autoshrinks
> off on that server.
> But on the other server where I am still struggling with
> space issues, I am looking for "fuel" to get them to get
> me more space. That's part of why I'd like to monitor the
> server and show them the effect (besides fragmentation of
> course) that this is having on the server.
> How can I show them that?
|||Here's some ammunition:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Art" <anonymous@.discussions.microsoft.com> wrote in message
news:008f01c4a63b$41f93970$a301280a@.phx.gbl...[vbcol=seagreen]
> Space. They wouldn't get me more space so I kept having
> drives filling up. I finally talked them into getting me
> more space on one of the servers and just did a
> migration. I of course am about take all the autoshrinks
> off on that server.
> But on the other server where I am still struggling with
> space issues, I am looking for "fuel" to get them to get
> me more space. That's part of why I'd like to monitor the
> server and show them the effect (besides fragmentation of
> course) that this is having on the server.
> How can I show them that?

Autoshrink and Autogrow

I have a large number of databases on a couple of heavily
utilized production 2000 servers (about 80 on one and
about 120 on another). Both servers hung mysteriously
last week and one of them had the following error:
"WARNING: EC 859eb5c8, 0 waited 300 sec. on latch a04e98.
Not a BUF latch."
The KB (310834) says this is from an overstressed system,
however my perf mon stats do not show this (unless context
switching greater > 5,000 for sustained periods is a
problem). But that same KB article says that
autogrow/autoshrink can be responsible and due to space
limitations, I have the autoshrink on all db's. So here
are my questions:
1. How can I monitor autogrow and autoshrink?
2. How can I tell how much cpu and disk they're using?
3. What is an acceptable threshold for context switching
for a four cpu server?Why on earth do you have autoshrink enabled?
--
http://www.aspfaq.com/
(Reverse address to reply.)
> 1. How can I monitor autogrow and autoshrink?
> 2. How can I tell how much cpu and disk they're using?
> 3. What is an acceptable threshold for context switching
> for a four cpu server?|||Space. They wouldn't get me more space so I kept having
drives filling up. I finally talked them into getting me
more space on one of the servers and just did a
migration. I of course am about take all the autoshrinks
off on that server.
But on the other server where I am still struggling with
space issues, I am looking for "fuel" to get them to get
me more space. That's part of why I'd like to monitor the
server and show them the effect (besides fragmentation of
course) that this is having on the server.
How can I show them that?
>--Original Message--
>Why on earth do you have autoshrink enabled?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>> 1. How can I monitor autogrow and autoshrink?
>> 2. How can I tell how much cpu and disk they're using?
>> 3. What is an acceptable threshold for context switching
>> for a four cpu server?
>
>.
>|||Well, you can have a trace running with minimal events (e.g.
autogrow/autoshrink) and capture the duration / cpu usage of those specific
events. Have you ever used Profiler?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Art" <anonymous@.discussions.microsoft.com> wrote in message
news:008f01c4a63b$41f93970$a301280a@.phx.gbl...
> Space. They wouldn't get me more space so I kept having
> drives filling up. I finally talked them into getting me
> more space on one of the servers and just did a
> migration. I of course am about take all the autoshrinks
> off on that server.
> But on the other server where I am still struggling with
> space issues, I am looking for "fuel" to get them to get
> me more space. That's part of why I'd like to monitor the
> server and show them the effect (besides fragmentation of
> course) that this is having on the server.
> How can I show them that?
> >--Original Message--
> >Why on earth do you have autoshrink enabled?
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >> 1. How can I monitor autogrow and autoshrink?
> >> 2. How can I tell how much cpu and disk they're using?
> >> 3. What is an acceptable threshold for context switching
> >> for a four cpu server?
> >
> >
> >.
> >|||Here's some ammunition:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Art" <anonymous@.discussions.microsoft.com> wrote in message
news:008f01c4a63b$41f93970$a301280a@.phx.gbl...
> Space. They wouldn't get me more space so I kept having
> drives filling up. I finally talked them into getting me
> more space on one of the servers and just did a
> migration. I of course am about take all the autoshrinks
> off on that server.
> But on the other server where I am still struggling with
> space issues, I am looking for "fuel" to get them to get
> me more space. That's part of why I'd like to monitor the
> server and show them the effect (besides fragmentation of
> course) that this is having on the server.
> How can I show them that?
>>--Original Message--
>>Why on earth do you have autoshrink enabled?
>>--
>>http://www.aspfaq.com/
>>(Reverse address to reply.)
>>
>> 1. How can I monitor autogrow and autoshrink?
>> 2. How can I tell how much cpu and disk they're using?
>> 3. What is an acceptable threshold for context switching
>> for a four cpu server?
>>
>>.

AutoShrink

Hi!
I have a question about the log file of my db, it
increases day by day of a lot of MB!!
To reduce its size I make the back up of transaction log
and then the Shink db tasks by Enterprise Manager.
I wonder if I check to true the option Auto Shrink on the
tab Option of the my db's properties, I get the same
result.
the Help sais that if this check is valued true, the
server periodically make the shrink...but "periodically"
means WHAT? and the resize would be the same I obtain with
the statements?
Thanks!If your log is legitimately growing, don't use auto-shrink, it will just
hurt your performance in your most active times of the day. If you do not
require point in time recovery, use simple mode.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"milena" <anonymous@.discussions.microsoft.com> wrote in message
news:955b01c3eb00$94202d90$a301280a@.phx.gbl...
> Hi!
> I have a question about the log file of my db, it
> increases day by day of a lot of MB!!
> To reduce its size I make the back up of transaction log
> and then the Shink db tasks by Enterprise Manager.
> I wonder if I check to true the option Auto Shrink on the
> tab Option of the my db's properties, I get the same
> result.
> the Help sais that if this check is valued true, the
> server periodically make the shrink...but "periodically"
> means WHAT? and the resize would be the same I obtain with
> the statements?
> Thanks!
>|||The tran log will be truncated "periodically" when the database is in
simple recovery mode... The process that does this is the checkpoint
process. You may adjust the frequency of checkpoints ( and the log
truncation ) using sp_configure "recovery interval". The default setting
causes this to occur every minute.
"milena" <anonymous@.discussions.microsoft.com> wrote in message
news:955b01c3eb00$94202d90$a301280a@.phx.gbl...
> Hi!
> I have a question about the log file of my db, it
> increases day by day of a lot of MB!!
> To reduce its size I make the back up of transaction log
> and then the Shink db tasks by Enterprise Manager.
> I wonder if I check to true the option Auto Shrink on the
> tab Option of the my db's properties, I get the same
> result.
> the Help sais that if this check is valued true, the
> server periodically make the shrink...but "periodically"
> means WHAT? and the resize would be the same I obtain with
> the statements?
> Thanks!
>|||Hi
SQL Server execute Shrink Method for remove unused pages. All depend of how You have the RECOVERY MODEL of the SQL Server, dependding on this its grow your log file
FULL: Save all information about transaction
SIMPLE: Only Save the neeseraly importan
SQL Server only can Shrink the Space Free, this depends on the Checkpoints the SQL Server, and this goes associated to BACKUP Process
The AutoShrink is not very good. It is better than executing for Transact-SQL with a Work, and thus your you can determine when this task is executed.
The AutoShrink is not very good. It is better than executing for Transact-SQL with a Job, and thus your you can determine when this task is executed
Look -> Books Online -> Index [Shrinking a Database
Look -> Books Online -> Index [Selecting a Recovery Model
Hermilson Tinoco

AutoShrink

Hi!
I have a question about the log file of my db, it
increases day by day of a lot of MB!!
To reduce its size I make the back up of transaction log
and then the Shink db tasks by Enterprise Manager.
I wonder if I check to true the option Auto Shrink on the
tab Option of the my db's properties, I get the same
result.
the Help sais that if this check is valued true, the
server periodically make the shrink...but "periodically"
means WHAT? and the resize would be the same I obtain with
the statements?
Thanks!If your log is legitimately growing, don't use auto-shrink, it will just
hurt your performance in your most active times of the day. If you do not
require point in time recovery, use simple mode.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"milena" <anonymous@.discussions.microsoft.com> wrote in message
news:955b01c3eb00$94202d90$a301280a@.phx.gbl...

quote:

---

> Hi!
> I have a question about the log file of my db, it
> increases day by day of a lot of MB!!
> To reduce its size I make the back up of transaction log
> and then the Shink db tasks by Enterprise Manager.
> I wonder if I check to true the option Auto Shrink on the
> tab Option of the my db's properties, I get the same
> result.
> the Help sais that if this check is valued true, the
> server periodically make the shrink...but "periodically"
> means WHAT? and the resize would be the same I obtain with
> the statements?
> Thanks!
>

---

|||The tran log will be truncated "periodically" when the database is in
simple recovery mode... The process that does this is the checkpoint
process. You may adjust the frequency of checkpoints ( and the log
truncation ) using sp_configure "recovery interval". The default setting
causes this to occur every minute.
"milena" <anonymous@.discussions.microsoft.com> wrote in message
news:955b01c3eb00$94202d90$a301280a@.phx.gbl...

quote:

---

> Hi!
> I have a question about the log file of my db, it
> increases day by day of a lot of MB!!
> To reduce its size I make the back up of transaction log
> and then the Shink db tasks by Enterprise Manager.
> I wonder if I check to true the option Auto Shrink on the
> tab Option of the my db's properties, I get the same
> result.
> the Help sais that if this check is valued true, the
> server periodically make the shrink...but "periodically"
> means WHAT? and the resize would be the same I obtain with
> the statements?
> Thanks!
>

---

|||Hi.
SQL Server execute Shrink Method for remove unused pages. All depend of how
You have the RECOVERY MODEL of the SQL Server, dependding on this its grow y
our log file.
FULL: Save all information about transactions
SIMPLE: Only Save the neeseraly important
SQL Server only can Shrink the Space Free, this depends on the Checkpoints t
he SQL Server, and this goes associated to BACKUP Process.
The AutoShrink is not very good. It is better than executing for Transact-S
QL with a Work, and thus your you can determine when this task is executed.
The AutoShrink is not very good. It is better than executing for Transact-SQ
L with a Job, and thus your you can determine when this task is executed.
Look -> Books Online -> Index [Shrinking a Database ]
Look -> Books Online -> Index [Selecting a Recovery Model]
Hermilson Tinoco