June 25, 2015 at 8:21 am
We have a standalone SQL Server 2008 R2 instance (standard edition). The database was installed with a full recovery model (application is 24 X 7), however no transaction log backup was configured. mdf is approximately 4 GB, ldf was up to 27 GB. We ran a transaction log backup last night.
Subsequent to that we ran a dbcc loginfo. All VLFs save one has status 0. BTW - there were 600+ VLFs.
The position of this record is near the end, the maximum I can reduce is less than 3 GB.
We then ran a dbcc opentran, only to get: "No active open transactions. DBCC execution completed". Subsequently ran another query against sys.databases, checking the log_reuse_wait_desc column, value was "NOTHING" and log_reuse_wait, value was 0.
I've been Googling this, there's no replication or mirroring. CDC is not enabled.
Any suggestions on this? What are my options?
June 25, 2015 at 8:23 am
What's the issue?
You've described a scenario, but haven't said what exactly is wrong.
Also, have you either scheduled log backups or put the DB into simple recovery (depending on recovery requirements)? If not, the log'll fill again.
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
June 25, 2015 at 9:24 am
The log won't shrink on it's own. If you ran a log backup, then the committed transactions have been removed. But, you'll need to shrink the log manually to get it back down to a reasonable size. Also, you may find that you need to run another log backup or two in order to get the shrink to go down a lot. Also, run a checkpoint operation between the log backups.
After that, set up regular log backups, or set the database to simple recovery.
"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
June 25, 2015 at 12:53 pm
Thank you for responding.
The vlf with status 2 is towards the end of the loginfo answerset. You can shrink an ldf up until that point - correct? For example - if you had a vlf status 2 at the very of your answerset, would you be able to shrink the ldf at all - I believe the answer to that is no. Reviewing the file size, there's about 2 -3 GB I can shrink. My issue is that still leaves a lot of storage that could be recovered. Is there anything I can do about that?
BTW - the transaction log backup has been run now 7 times now since the situation was identified.
June 25, 2015 at 1:05 pm
jralston88 (6/25/2015)
For example - if you had a vlf status 2 at the very of your answerset, would you be able to shrink the ldf at all - I believe the answer to that is no.
Not at that point. Later on, when the head of the log has wrapped around and the active portion is at the beginning of the file, you will be able to shrink further. The log is a circular file.
Make sure you have log backups scheduled regularly, then try again tomorrow to shrink, you should be able to get a lot more removed.
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
June 25, 2015 at 1:47 pm
That's it - thank you! And yes the transaction log backup is scheduled so it will come around. Good information. Thanks again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply