December 19, 2013 at 7:33 am
I've been running my little SQL Server (2012) for three years now (started life as SS2k8), and suddenly, my log file is bigger than my db (4.5GB vs 3.8GB). I've had several (Full) backup failures due to insufficient disk. My backup package is supposed to backup, then truncate the log file every night. It's reporting success every night.
And, while the DB is 3.8GB, the backup of the same db (Not compressed) is 1.2GB....
Any ideas where to look?
Jim
December 19, 2013 at 7:36 am
Full backups do not truncate the log.
If the database is in full recovery, you need to be running log backups. If you don't need point in time recovery, switch to simple recovery model.
Please 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
December 19, 2013 at 7:38 am
My backup package does a full backup (including logs), then a db integrity check, then truncates the logs. I will read your link. Thank you.
Jim
December 19, 2013 at 7:40 am
JimS-Indy (12/19/2013)
My backup package does a full backup (including logs), then a db integrity check, then truncates the logs. I will read your link. Thank you.
A full backup does not truncate the log (and it doesn't include the logs, just a bit of the log). You cannot explicitly truncate the log on SQL 2008 or above without doing a log backup or changing recovery model. If you're not doing log backups on a database in full recovery, the log will grow until it fills the disk.
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
December 19, 2013 at 8:02 am
Since you're not doing anything with the log backups anyway, take the database out of full recovery. Just use Simple. That won't eliminate all log problems forever, but it'll certainly mitigate a lot of them. After you switch it to Simple, run a shrink on your existing log file to get it back down to size.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2013 at 8:29 am
Gail,Grant,
I appreciate your help. I want to solve the problem, and also to learn. There is a case for moving to Simple Recovery on my dbs. But I don't want to give up on PIT recovery just yet (it's a reasonable fallback.) I'll attach a file showing my current maint plan. I think what may be happening here is order-related. I back up the TL, then the DB, then start doing chores -- Reorganize Index, Update Statistics. I thought I had a step there that also shrunk the db and log files, but I guess I didn't.
So, maybe, even though the log file should is backed up, and the db is in Full Recovery mode, the Reorganize Index step is sort of re-ballooning the log file (along with a couple of other SQL tasks I run later that rebuild a couple of large non-normalized tables.) If that's the culprit, perhaps a new maint plan in a different order solves everything.
By the way, this ballooning of the log files MAY have started when I moved to SS2k12 from SS2k8. Perhaps the maintenance plans didn't translate well? I used the upgrade wizard to transition to SS2k12....
Also, Grant, you say "shrink the log file"...how? Is there a sproc?
Attachment Capture.PNG shows the Maint Plan.
I did read the articles by Gail and Ken, but I'm still not confident. It appears just backing up the log file should automatically shrink it?
Jim
December 19, 2013 at 8:38 am
If you still want point in time recovery, then add a second maintenance routine that runs... I don't know, once an hour (seems like you don't have many transactions) that does the log backups. That's going to add to your space requirements though, so plan accordingly.
Shrinking the log file. Not to be done routinely, but in a situation like this, I think it's fine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2013 at 8:57 am
Grant, thank you for sticking with me here. I've learned quite a bit.
I ran a backup of my log file, and in the backup, I checked "Truncate Log". That took a gig out of the log file. Then I ran
dbcc shrinkfile(2,640)
and that shrunk it to around 640MB.
So, I'm wondering how it got so big... Or is that an artifact, and it won't likely grow much bigger?
Jim
December 19, 2013 at 10:25 am
How it got so big: You aren't running regular log backups. In full recovery model only log backups mark the log as reusable, so you need to schedule them. Full backups do not truncate the log.
If you want to stay in full recovery, schedule log backups to meet your company's data loss allowance (see the article I referenced). If losing a full day of data is allowed (which is what your current backup strategy suggests), then switch to simple recovery and the log will maintain itseld
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
December 19, 2013 at 11:36 am
What Gail said. Likely you saw a spike or several spikes from index rebuilds. Regardless, the choices are, log backups, or simple recovery. Anything else just inflicts pain, and who needs that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2013 at 11:48 am
Guys, as you can see from my attachment, I run the log backup every night. I just never did a shrink. I suppose that's the issue. I think I will convert to Simple recovery.
I actually back up the db (not the log) as well to a second (network share) location at 5 PM weekdays with a copy-only backup that's compressed. Only been doing this for a few days. I got nervous about my backup disk.
I suspect the combination of the two backups probably has me in good shape. It would be unusual for the loss of one day to be catastrophic for this app.
Thanks
Jim
December 19, 2013 at 11:56 am
Sounds like a good plan. If you hit snags, you know where to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2013 at 12:42 pm
JimS-Indy (12/19/2013)
Guys, as you can see from my attachment, I run the log backup every night.
Yes, and that is the problem. The fact that the log backup was run nightly only.
I just never did a shrink. I suppose that's the issue.
No, that's not the issue. Shrinks shouldn't be scheduled, they shouldn't be regular operations and shrink would not have helped. You could have scheduled shrink to run every 5 minutes and you'd still end up with huge logs.
I suspect the combination of the two backups probably has me in good shape.
No, it doesn't. It left you with a massive log, one that had to be large enough to hold a full day's transactions and no better restore ability than just a daily full backup. You've got close to the worst of two worlds there.
When I say 'regular log backups' I don't mean daily, that's close to pointless. It leaves you with 24 hour data loss risk, same as if you just had daily full backups, and causes your log to grow huge.
Please, take a read through that article I referenced earlier.
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply