Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log File Shrink


Log File Shrink

Author
Message
midnight251
midnight251
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 21
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.


Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
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
midnight251
midnight251
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 21
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.


Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
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
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6334 Visits: 13687
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

---------------------------------------------------------------------
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6334 Visits: 13687
another thing - have you run any reindexes whilst monitoring largest log size? Thats likely to be your biggest user of log space

---------------------------------------------------------------------
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5988 Visits: 8314
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 at GMail
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24227 Visits: 37978
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/


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
midnight251
midnight251
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 21
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
midnight251
midnight251
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 21
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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search