All Logical Files are in Use Shrinking Transaction Log 1

[mdlaugh1]

Hi,
I have read many posts on this subject including thread183-430515 but am still confused. (new to SQL Admin) using SQL 2000.

We have a transaction log that is 20G, leaving only 3.4g free space on the server. the data file is about 225MB and the Index file is about 245MB.

We have done a database and transaction log backup, then ran the dbcc shrinkfile command. It failed with the "All logical files are in use" message.

In one of the posts I thought I read that we 'might' need to stop access to the database prior to running the shrinkfile command. Do we need to Detach the database prior to running the command?

Also, I thought I saw somewhere that doing a backup would cause the transaction to auto-shrink so it would remain a realistic size.

Please help me sort through the exact steps we need to do and the correct order to get the trans log size down and keep it small.

thank you!

 

[SQLSister]

Look in BOL for truncating transaction logs and shrinking transaction logs for what to do. Once you have truncated your log, it is very important to immediately get a full backup of your database.

You should not need to go to single user mode to shrink the log after truncating it. However, I might try it if you are having trouble.

Once you have shrunk the files, you must set up regular backups of the transaction log to keep it from growing out of control again. It will need to grow a certain amount fromthe shrinage to account for all the normal entries into the log (you would not want it to be too small or you would always have the system waiting while it grows).

If you still can;t get it shrunk, please post the exact commands you are using

"NOTHING is more important in a database than integrity." ESquared

 

[mdlaugh1]

This issue is resolved. We did the following steps:

1. BACKUP LOG databasename WITH TRUNCATE_ONLY

at this point the log file size on disk was shrunk from 20G to about 3G

2. dbcc shrinkfile (dbname_log,2)

After running the dbcc shrinkfile cmd we still did receive the message that "Unable to shrink log file 2" "All logical files are in use". However, the transaction log file was shrunk.

3. Did a Complete Database and Transaction Log backup.

 

[SQLSister]

now make sure to set up scheduled backups of both the log and the database or the problem will re-occur.

"NOTHING is more important in a database than integrity." ESquared