3/10/2012 5:31:20 PM
Parikshit Savjani -
select name,log_reuse_wait_desc from sys.databases
DBCC SHRINKFILE(2, target_size_in_MB) -- (2 is the fileid for tlog file)
Parikshit Savjani (Member since: 1/15/2012 6:38:05 AM)
View Parikshit Savjani 's profile
This is a wonderful post, and i have been doing this whenever i face this problem of "unable to shrink the tlog file". Just one addition to this, instead of changing to simple recovery, we can take a tlog backup and shrinking the tlog file using dbcc shrinkfile in a Batch. DOing this is in a batch is very important else by the time we do dbcc shrinkfile, there may be transactions being posted which will update the last VLF and hence shrinking wont be possible.
Pls correct me if i am wrong...
also the command is dbcc loginfo and not logfile as specified in the post
Thanks Naginder for correcting the post. I have included your method in the blog post alongwith my comments.
Very well said Parkshit,
As you covered almost everything including the link of Too Many and Too few VLF
could be bad…Appending and adding to the post.
“LOG MANAGER” in SQL Server 2005 is the Brain
for Log files which is smarter, faster than SQL 2000, So. YES, You can
perform one DBCC SHRINKFILE statement to shrink the transaction log file
immediately to 2 virtual log files. You can do this because the SQL Server 2005
log manager creates 2 virtual log files by following the physical disk storage
order. Both of these virtual log files are at the start of the transaction log
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.
more details (Ref) - http://support.microsoft.com/kb/907511
One more link I wrote long time back for SQLAUTHORITY.com
How to truncate log file in SQL Server 2005
SQL Server 2005 is quite different from SQL Server 2000. To
truncate log file is one thing which is different from SQL Server 2000. In SQL
Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005,
sometime I cannot shrink the log file at all.
Here I want to describe some tricks to truncate log file for a
database in SQL Server 2005. The work environment is MS SQL Server Management
I. Shrink the log file size at the right time
I found out this trick:
Immediately after I use the SSIS package or Import the data to
the database ( highlight the database->Tasks->Import data … ), or Export
the data from the database ( highlight the database->Tasks->Export data …
), I can shrink the log file to the desired size, for example, 1MB. That is,
highlight the database->Tasks->Shrink->Files
set the file size, say, 1MB.
Then, click OK and you are done.
II. Eliminate the log file completely
Sometimes we just do not need the big log file. For example, I
have 40GB log file. I am sure I do not need this log file and want to get rid
of it completely to free up the hard drive space. The logic is
a. Detach the database
b. Rename the log file
c. Attach the database without the log file
d. Delete the log file
Let’s say, the database name is testDev. In the SQL Server
database-> Tasks->Detach..-> Click OK
Go to log file folder -> rename the testDev_log.ldf to be like
Highlight Databases->Attach…-> Click Add -> add the database testDev,
highlight the log file and click the ‘Remove’ button. This means you only
After this is done, you can verify the contents of the attached database and
then delete the log file.
Good to see your comment :) ... its long time we had a call
The only challenge here is, it is not a supported method of Microsoft and it may not work in future versions and hence I would not like to add it to the Blog Post.
If we can achieve the same results by using one of the supported Method then we dont want to promote unsupported methods
Agreed ….As Its important to know what is ‘not supported’ …Ha ha ha ha.
Leave a comment