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

Why logs so big Expand / Collapse
Author
Message
Posted Tuesday, September 17, 2013 9:32 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
I have a small db at my client site (SS2012,MS Server 2008) with an Access front end. It does some importing through Access (imports Excel workbooks created by the corporate server....) These are necessarily big, and they are replaced into a table on each import because the corporate server doesn't provide a key column. No big deal, I work through it, but I can't quite figure out why my log file is so big....

On Mondays, the log file should get big, because all the imports are run on Mondays. But then, every morning at 3 AM, I have a backup sequence that backs up the entire instance, then (I thought...) truncates the log files. Yet, here it is Tuesday and the log files are huge (twice the size of the db....)

My best guess is that I never truncate the space occupied by the logs, just delete the logs themselves.... I guess that's OK, but it seams like a lot of wasted space....

Log file...6330 MB, DB file...3682 MB

Backiup script (SQL Server Agent Job...)

https://picasaweb.google.com/lh/photo/2rlN0nnxNR-sWWefPJZkudMTjNZETYmyPJy0liipFm0?feat=directlink


Jim
Post #1495559
Posted Tuesday, September 17, 2013 9:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:39 AM
Points: 5,205, Visits: 9,354
Jim

Truncating the log only removes none, some or all of the entries from it; it doesn't shrink it. You should consider backing up your log instead of truncating it, so that you don't lose your log chain and hence your ability to restore to a point in time in the event of a disaster. You might also consider doing your updates in small batches. This, combined with regular log backups, should limit the growth of your log file.

For more details and a better explanation, start with this.


John
Post #1495567
Posted Tuesday, September 17, 2013 10:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:00 PM
Points: 42,308, Visits: 35,365
Take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1495589
Posted Tuesday, September 17, 2013 1:41 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
John and Gail both referred me to the same resource. But I think the maintenance plan I included in the link shows that I do back up the log...every time I back up the db...daily.

So, if I want to shrink the log space once (to see if its current size is an anomaly...) how do I do that?

Never mind...I figured it out. But the shrunken logfile is still pretty large. Is that normal? It's now just a gig larger than the 4 gig data file....


Jim
Post #1495651
Posted Tuesday, September 17, 2013 2:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:00 PM
Points: 42,308, Visits: 35,365
Start by backing the log up more frequently. See the article I referenced about how often and why you back logs up. If you don't need to back the logs up, then consider simple recovery model

After a log backup, you can just shrink the log. Use the UI if you want. Shrink to a sensible size, not 0. Maybe half the size of the DB.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1495663
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse