Log File Shrink
-
Now that we got the log file backups under control, now it's time to shrink the log file. I'm going to start with the Test DB which is only 56 gig, and when they (consultants) created the log file initialy, they set the log file size to 59 gig. See below the results of DBCC SQLPERF(logspace). It looks as though I can really shrink this sucker. What do you think would be an appropriate size, remembering this is a test DB with not a lot of transactions going through.
-
It's anyone's guess right now. To know for sure log the results of DBCC SQLPERF(logspace) right before you take your log backups for a few days and see what the max % used it. Then shrink it to some size a little larger than that.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Well, been watching it for a few days, and it doesn't really get to 1% (see below, just before a log file backup). I'm thinking of shrinking to about a gig. My largest log file so far is around 300MB.
-
Sounds good to me. Thanks for posting back the results.
Now might be a good time to double-check on your auto-grow settings too. I like to use MB instead of % because growth is more predictable that way. I also like to autogrow the log file in relatively chunks, anywhere between 128MB and 512MB depending on the size and power of the server. Log files cannot be instantly initialized like data files so large log growth operations can cause huge delays for the lucky query that happens to initiate them.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
this could be a chance to really tidy up the log and reduce the number of vlfs as well. I would try shrinking the log right down to 1mb, and then grow it again in one go up to 1GB. This will set your vlf number and sizes to optimum values.
This is because for growths up to 64mb 4 vlfs are created, between 64mb and 1024mb 8 vlfs, and anything above 1024mb, 16 vlfs.
If the log was initially created at 59GB (perish the thought) in one go I would expect you to have problems shrinking it to anywhere near 1GB.
run dbcc loginfo(dbname), no of rows = no of vlfs
---------------------------------------------------------------------
-
another thing - have you run any reindexes whilst monitoring largest log size? Thats likely to be your biggest user of log space
---------------------------------------------------------------------
-
You MUST read (and follow the guidance from) these and their associated links:
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
I use them myself and advise every client I have to use them as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (3/20/2013)
You MUST read (and follow the guidance from) these and their associated links:http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
I use them myself and advise every client I have to use them as well.
Make these easier for others:
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
-
Great info, thanks for the links. No, I haven't run any reindexes while monitoring. Thanks for helping out a SQL rookie, you folks are great. I'll let you know how I make out.
Midnight
-
Well, got the test DB log file down to just over 500MB, from 59gig. Check it out. Now to tackle the Live DB. Thanks for all the help folks. I'm sure I'll have more questions in the future. Thanks again for helping.
https://www.sqlservercentral/Forums/Uploads/Images/1441763-1.jpg[/img]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply