question about autogrowth and shrinkfile

Hi All:

I have a busy SQL 2005 dB with about 6 gig of data. I had recently moved it to a new server and neglected for several weeks to add a maintenance plan to backup the tlog. The server environment is an active/passive cluster with an iSCSI NAS holding the data.

Both files were set to autogrow by 20%, and by the time I noticed, the tlog was 14 gig. At first I was not able to back it up...in SMS when I executed a backup it seemed to just spin endlessly, but after googling around I was finally able to get it back down to a manageable size by doing the following:

changing the auto-grow setting to 100 meg instead of 20%
backing up the tlog
running the shrinkfile command
(i ran the "backup log" and "dbcc shrinkfile" one after the other in the same query)

The first time I backed it up, a 14-gig backup was created (which took about 25 minutes), but the shrinkfile failed (with 'all logical files in use' error). I ran the backup a second time, and then shrinkfile worked. I've noticed the same thing has happened in the past with large transaction logs.

A few questions:

Did the first shrinkfile fail because there SQL hadn't updated the statistics for the tlog or something like that? If so, do I need to execute an additional command between "backup log" and "shrinkfile", or am I doing something out of sequence?

Secondly, I'm curious as to what I should set the auto-growth to for a dB of this size. I've read that 1/8 the dB size is appropriate, but in this case that would be 750 MB for the dB? I was able to get the log down to 20 meg with shrinkfile, but what should I set the auto-growth to for the log?

I've read that setting the autogrowth increment too small will create a lot of fragmentation... 12 hours after backing it up and shrinking it, the tlog now has 36 VLFs, with about half of them inactive. Does this indicate 100 MB is too small an auto-growth increment? The log file is currently only 10 MB on disk.

After reading this page: http://www.karaszi.com/SQLServer/info_dont_shrink.asp, I'm confused as to whether I need to use autogrowth or not...I'm only a part-time DBA and have a tendency to "neglect" the dB, but I don't ever want to risk the dB or log running out of space.

Any comments would be appreciated.

.

Relevant Pages

  • Re: question about autogrowth and shrinkfile
    ... I ran the backup a second time, and then shrinkfile worked. ... Thanks...it does seem that the backup has to occur twice before the log can be shrunk. ... auto-grow is something that should never occur. ... said this database was 6 GB today. ...
    (microsoft.public.sqlserver.tools)
  • Re: Recovery model and log file shrinking
    ... their affect on a database. ... But even this backup of log file ... the log file is still 100mb. ... last two lines again(backup log & shrinkfile), ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction log too big
    ... between your backup log operations? ... > So now when I backup my database the last thing will be a DBCC SHRINKFILE. ...
    (microsoft.public.sqlserver.server)
  • Re: No truncating.
    ... After backup the physical LDF file will not get reduced automatcally. ... To shrink the physical ldf file after the log backup use dbcc shrinkfile ... See the dbcc shrinkfile command in boks online. ...
    (microsoft.public.sqlserver.server)
Loading