Surf 11 » Cannot shrink log file because all logical log files are in use

Finding the best waves on the web.

August 31, 2007

If you are getting the following error message in Microsoft SQL Server when you try to run DBCC SHRINKFILE (DBNAME_Log):

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

Make sure that there are no running transactions, by running the command on the database in question:

DBCC OPENTRAN

It is possible to shrink the transaction log file while the SQL Server database is online, however if transactions are taking place while the operation is running you may experience this error.

Try running it in off hours the DBCC SHRINKFILE command in off hours, or shut down access to the database from other apps.

Add Comment | Web | save on del.icio.us | Tags: sqlserver, dbcc, transaction log, databases, sql, shrink, shrinkfile

Related Entries

Comments

On at Ladan wrote:

Thanks xangelx,

Your solution saved my life :)

On at xangelx wrote:

hehehe guys it's so easy!!!!! all u have to do is doing this steps and u will shrink ur database form enterprise manager and query analyzer 1. open enterprise manager. 2. right click on the database u wanna shrink it. 3. click on properties. 4. from the data properties go to options. 5. in the middle u will see recovery model make it "simple" then click on "ok" and try to shrink the database. it works 100%

On at xangelx wrote:

On at Gazza wrote:

sometimes you have to do the backup with truncate_only multiple times to cycle the vlfs to the appropriate place in the log.

I've just had one, took three truncates. before I could shrink the log, even though it looked totally empty.

database in simple mode.

On at kishore wrote:

i am kishore, for the past 1 month i am trying to shrink transaction log file. but i am failing to do that. 1) i tried by backing up the log file with tuncate option.( it worked for test databases but this is not working for real database right now the log file size is 20 GB) 2) DBCC SHRINKFILE (LearningArea2_MDB_log,200) this is giving the following error. Cannot shrink log file 2 (LearningArea2_MDB_log) because all logical log files are in use.

can any one advise some thing so that i can comeout of this problem. as i am not a frequent user of SQL i am finding more Difficulties.

please help me