Shrinking the log file, can't get it to work 1

[techrob]

I have a log file that is using up all the space on disk where it resides, so i would like to shrink it but no matter how hard I try I can't get it to work. Here's what I've done.

* Performed a full backup
* Performed a Transaction log backup
* Ran the DBCC SHRINKFILE(file_name, NOTRUNCATE)
* Ran the DBCC SHRINKFILE(file_name, TRUNCATEONLY)
* I've also tried the shinking options from the gui

When I run DBCC SQLPERF(LOGSPACE) I get the following output:
Database Name Log Size (MB) Log Space Used (%) Status
-------------- ------------- ------------------ -----------
master 1,242188 51,88679 0
tempdb 109,7422 30,44778 0
model 5,054688 96,52241 0
msdb 4,117188 32,73245 0
agr55 14719,37 4,908091 0
agrhlp 2118,742 2,898402 0

Iget the following message when I run the DBCC SHRINKFILE(file_name, TRUNCATEONLY) or NOTRUNCATE:
Cannot shrink log file 2 (file_name) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
5 2 1884080 38400 1884080 38400

So it seams that I can't shrink it since all logical logfiles are active.

Is there a way to solve this?

 

[ptheriault]

The log file will not shrink if you have open transactions.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[techrob]

So do i need to take it offline or in some other way insure that noone is using the database?

 

[ptheriault]

No, The log will not shrink if there are un-commited transactions. So you need to see if you have a large transaction running. Do you have a maintenance window that you could try to shrink the log when there aren't any users logged in?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

You can also look at the output from dbcc opentran. When run within the user database it will tell you the oldest open transaction in the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[techrob]

I got to work early this morning and ran a full backup followed by a transaktion log backup. After that I tried shrinking the database again and it worked, reducing the log file with 12gb.

So it seams that I should do the db maintenance on off-hours.

Thanks for all tips.

 

[mrdenny]

Most maintenance should be done off hours, as it will usually impact system performance and give uses a poor experience.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog