Cannot shrink log file 2 (LOG FILE) because all logical log files are in use.
-
Dear Sir,
I am not able to shrink the Log of my database.
While using the dbcc shrinkfile or dbcc shrinkdatabase command, I get the following Error
Cannot shrink log file 2 (LOG FILE) because all logical log files are in use.
Also in the results Grid I get the folowwing.
DbID FileID CurrentSize MinimumSize UsedPages EstimatedPages
7 2 3111832 2560 3111832 2560
-
Do you make backups of your transaction log?
-
Hi check out this post.This explains why DBCC shrinkfile fails and what do you need to do for it
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=933488&SiteID=17
-
What is the recovery model you have ?
1. Backup the transaction log.
2. Shrink it.
3 Create a maintenanace plan to do the backup regularly.
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
-
-- I do the following in a Maintenance Plan (SQL2005)
Inserted a T-SQL task after the successful backup and DBCC integrity check jobs.
This goes thru the "simple" recovery_model databases I have just backed up and does:
USE DBLogicalName
DBCC SHRINKFILE (DBLogicalName_Log, TRUNCATEONLY)
If the database is not in SIMPLE recovery mode (or has some other problem), you will get the error below (interactively):
"Cannot shrink log file 2 (DBLogicalName_Log) because all logical log files are in use."
This is not captured by the maintenance plan.
In my case, some of the log files were not truncated but there was no error in the logs/history.
Trying the same commands interactively in SSMS, I could see the above message and take corrective action.
If necessary and allowable, change the recovery_model to simple. In my case, I was surprised to find some of the databases were set to FULL recovery, although I had not meant that. It must be because the original instance installation set the "model" database to "FULL" therefore newly created databases (even restored from another server where they were in "SIMPLE" mode) became "FULL".
HTH some other budding DBA . . .
-
Why do you need to shrink the log?
Please read through this - Managing Transaction Logs[/url]
Minaz Amin (2/27/2007)
1. Backup the transaction log.2. Shrink it.
3 Create a maintenanace plan to do the backup regularly.
Create the maint plan to back the log up regularly. Shrinking the log on a regular basis is not recommended
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (1/15/2009)
Why do you need to shrink the log?Please read through this - Managing Transaction Logs[/url]
Create the maint plan to back the log up regularly. Shrinking the log on a regular basis is not recommended
Hi Gail,
had already read your wonderful article and learned a lot from it.
Our site "policy" is to not offer point-in-time recovery.
We do a full backup every night so disaster recovery may "lose" a day worth of data.
For that reason, database recovery model should have been "SIMPLE".
Seeing some log files grow excessively, I thought I'd add a "log shrink" to the nightly maintenance plan, effectively truncating the log down to nothing (for lack of space). That's when I grappled with the error message we are trying to elucidate for others.
Re-reading your article, I understand now that the excessive log growth may have been due to the recovery model being inadvertently set to "FULL" while we never took a LOG backup to help recover the space!
Nevertheless, if databases are small enough to back up in full and we don't have to offer point-in-time data recovery, the log can be truncated every night as it is useless after a successful full DB backup and integrity check.
It is not something everyone would do, and I will carefully consider changing the policy for some databases. But as far as shrinking manually or by schedule, it will only work for db's in simple recovery mode.
-
Ol'SureHand (1/15/2009)
Nevertheless, if databases are small enough to back up in full and we don't have to offer point-in-time data recovery, the log can be truncated every night as it is useless after a successful full DB backup and integrity check.If the DB is in simple recovery, there's absolutely no need to explicitly truncate the logs. That occurs at regular intervals. In simple, the only reason for the log to grow would be faulty replication or long lasting transactions. Index rebuilds fall into the latter.
As for shrinking it, only shrink a log if you know that it won't reach that size again. log grows are expensive and slow the DB down for the duration. Plus repeated shrink/grow causes fragmentation at the file level, which is hard to fix.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
just reviving this thread rather than starting another one...
I have a similar problem - a 20GB production database (FULL recovery model) with the log file size typically around 2GB. Once a week a maintenance job runs and reindexes the database - basically running a vendor supplied reindexing script (and a few other things). The problem is that the reindexing increases the log size to increase to over 20GB, consuming most of the remaining free disk space. Increasing the disk size is not an option at the moment.
To date I have manually shrunk the log file back to 2GB the day after the maintenance job runs (in order to do this I usually have to backup the log one or more times before using DBCC SHRINKFILE to shrink the log). Recently, I tried to automate the process using a scheduled job - i.e. step 1: backup log, step 2: shrink the log - but so far the job has always been unsucessful because (according to the error message)" Cannot shrink log file 2 (xxx_Log) because all logical log files are in use." I have no idea what would be using the log file in the middle of the night - certainly no other scheduled jobs. The database is mirrorred so switching to SIMPLE recovery is not an option either
I realize that shrinking the log is not particularly good practice - but a lack of disk space necessitates this. Is anyone else in a similar position? If so, how have you resolved this issue?
Thanks for any input 🙂
-
As long as you remember it is best to avoid shrinks as per Gail's admonitions and Kimberly Trip's recent article on transaction log fragmentation (sqlservercentral.com/blogs/dba_tipster/archive/2009/12/17/transaction-log-fragmentation.aspx)
[/url]
According to MS, you should be able to shrink the log after a transaction log backup:
"This article describes how to use the DBCC SHRINKFILE statement to shrink the transaction log file manually under the full recovery model in a SQL Server 2005 database".
but it also goes on to say:
"When you try to shrink a transaction log file that has little free space in SQL Server 2005, you may have to perform an additional log backup operation. The additional log backup operation truncates the transaction log file to a smaller size."
Caution: the following is all theoretic !
Since you had been manually re-running log backups, this might be your problem. Try doing the same thing in code.
Do your job in a stored proc invoked from the Maint Plan or SQL Agent.
In the stored proc, have something like that:
BACKUP LOG databasename TO devicename
label_for_Goto:
BEGIN TRY
DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
END TRY
BEGIN CATCH
BACKUP LOG databasename TO devicename
GOTO label_for_Goto --careful you don't spend the night re-running backups . . .
--this script is offered as a sample and has never been used in practice ....
END CATCH
Also check Gail and Kimberly's info about what happens with REINDEX ... seems a gotcha will get you if you run it in the wrong order.
-
thanks for the info Ol'SureHand - I will try adding a couple of additional log backups to the scheduled job (each in its own job step) and see if that works - I'd rather not introduce a loop...
🙂
-
Ivanna Noh (12/20/2009)
thanks for the info Ol'SureHand - I will try adding a couple of additional log backups ... 🙂Right you are ... but you may wish to introduce a counter and while loop, without the TRY block.
Which makes me remember a VERY FUNNY one:
"DBCC CHECKDB statement does not raise an exception when the DBCC CHECKDB statement detects a consistency error inside the database"
.... would be great if you tried that just to see if it triggers the CATCH in the particular circumstance of your error!
-
Ivanna Noh (12/20/2009)
jI have no idea what would be using the log file in the middle of the night - certainly no other scheduled jobs. The database is mirrorred so switching to SIMPLE recovery is not an option eitherSince you say the DB is mirrored, I'm assuming then that this is SQL 2005 not SQL 2000. (This thread is in the SQL 2000 forum). If that's the case then you can query sys.databases (not an option on SQL 2000) and look at the log_reuse_wait_desc column.
I would recommend not doing this regular shrink. Look at getting more disk space. Also look at replacing the index rebuild script with one that just rebuilds all the indexes without checking (if that's what the vendor-supplied script is doing) You can also look at putting a log backup between each index rebuild.
Please in future post new questions in a new thread rather than resurrecting an old one. Thanks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I rescheduled the backup log / shrink log job to run at a different time - and it worked ok this time, so I'll leave it set up like that until some additional disk space can be arranged (out of my control unfortunately).
Gail - when i query sys.databases the value in the log_reuse_wait_desc column is 'LOG_BACKUP' - is that a result of mirroring?
Ol'SureHand - thanks for your help, but it looks like no while loop etc is needed after all, just better timing!
apologies for posting in the 2000 forum
-
Ivanna Noh (12/21/2009)
Gail - when i query sys.databases the value in the log_reuse_wait_desc column is 'LOG_BACKUP' - is that a result of mirroring?Nope. It means exactly what it says. The space in the log cannot be reused at this point because it's waiting for a log backup to happen. If it was mirroring, that columns would say 'DATABASE_MIRRORING'
Do note the comment from Books Online.
If the reason is LOG_BACKUP, it may take two backups to actually free the space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply