Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log File Shrink Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
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.

Post #1431679
Posted Friday, March 15, 2013 1:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
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
Post #1431729
Posted Tuesday, March 19, 2013 1:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
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.



Post #1432871
Posted Tuesday, March 19, 2013 3:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
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
Post #1432921
Posted Tuesday, March 19, 2013 4:42 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
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



---------------------------------------------------------------------

Post #1432938
Posted Wednesday, March 20, 2013 6:14 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
another thing - have you run any reindexes whilst monitoring largest log size? Thats likely to be your biggest user of log space

---------------------------------------------------------------------

Post #1433162
Posted Wednesday, March 20, 2013 7:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 4,351, Visits: 6,167
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
Post #1433216
Posted Wednesday, March 20, 2013 8:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
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/



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)
Post #1433277
Posted Wednesday, March 20, 2013 10:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
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
Post #1433342
Posted Friday, April 12, 2013 9:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
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.

Post #1441763
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse