SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot shrink log file 2 (LOG FILE) because all logical log files are in use.


Cannot shrink log file 2 (LOG FILE) because all logical log files are in use.

Author
Message
Asif Mansuri
Asif Mansuri
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22

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


mrivera
mrivera
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 31
Do you make backups of your transaction log?
srivathsani-296624
srivathsani-296624
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 125

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


Minaz Amin
Minaz Amin
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2562 Visits: 1750

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"
Ol'SureHand
Ol'SureHand
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 720
-- 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 . . .
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211693 Visits: 46258
Why do you need to shrink the log?

Please read through this - Managing Transaction Logs

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 recoverability

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


Ol'SureHand
Ol'SureHand
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 720
GilaMonster (1/15/2009)
Why do you need to shrink the log?

Please read through this - Managing Transaction Logs
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211693 Visits: 46258
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 recoverability

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


Ivanna Noh
Ivanna Noh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1585 Visits: 3046
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 :-)
Ol'SureHand
Ol'SureHand
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 720
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:
[url=http://support.microsoft.com/kb/907511][/url]
"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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search