Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cannot shrink log file 2 (LOG FILE) because all logical log files are in use. Expand / Collapse
Author
Message
Posted Monday, February 26, 2007 4:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 3:53 AM
Points: 1, 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

 

 

Post #347383
Posted Monday, February 26, 2007 12:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 27, 2009 10:47 AM
Points: 74, Visits: 31
Do you make backups of your transaction log?
Post #347573
Posted Tuesday, February 27, 2007 12:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:48 AM
Points: 102, 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

 

Post #347719
Posted Tuesday, February 27, 2007 12:26 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:34 AM
Points: 549, Visits: 1,554

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"
Post #347724
Posted Wednesday, January 14, 2009 11:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:12 AM
Points: 370, Visits: 716
-- 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 . . .
Post #636841
Posted Thursday, January 15, 2009 12:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 39,882, Visits: 36,228
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 2008, MVP
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

Post #636864
Posted Thursday, January 15, 2009 4:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:12 AM
Points: 370, Visits: 716
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.
Post #636986
Posted Thursday, January 15, 2009 6:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 39,882, Visits: 36,228
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 2008, MVP
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

Post #637030
Posted Sunday, December 20, 2009 3:40 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 14, 2014 2:56 PM
Points: 843, Visits: 2,494
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
Post #836919
Posted Sunday, December 20, 2009 6:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:12 AM
Points: 370, Visits: 716
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.
Post #836928
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse