September 17, 2013 at 9:32 am
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
September 17, 2013 at 9:51 am
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[/url].
John
September 17, 2013 at 10:52 am
Take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2013 at 1:41 pm
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
September 17, 2013 at 2:12 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply