SQL Server Performance Forum – Threads Archive

Having trouble shrinking Log – HELP!

I don’t understand why my log file will not shrink. The SQL Server 7.0 database I inherited has never been maintained and the log file is 1046 MB. I have performed the following: Shrunk the database, truncated the log, added and deleted a record in a database table and backed up the database. I repeated the above steps a few times. I have also tried using DBCC SHRINKFILE on the log file and that does not work. I have run query "dbcc sqlperf(logspace)" and can see that only 1% of my log file is being used. However I cannot get the space back. I want to start performing log shipping and schedule weekly log truncations and backups of the database. Therefore I do not need a 1046 MB log file. I did read you cannot reduce the log file smaller than its original setup size. I know the log file was not setup originally with 1046 MB. I am not sure what the virtual log file (VLF) boundary is or how to find out. Why can I not shrink this log file? Is there a way I can force the log file to a smaller size? I tried deleting the database, recreating the database with a smaller log file and then trying to restore the database from a backup. This does not work. I am out of ideas. Thanx.
Jeff

You haven’t written, if you already tried to backup up the T-Log before shrinking.
Depending on your recovery model have you tried
< simple >
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE < full, bulk-logged >
BACKUP LOG
DBCC SHRINKFILE If this still does not work, as some kind of last resort, you can use a brute-force method (MAKE SURE YOU HAVE A CURRENT BACKUP FIRST !!!.
detach db
delete the physical *.ldf file
attach the db (ignore the error messages)
SQL Server should build a new T-LOG ———————–
–Frank
http://www.insidesql.de
———————–

Or as an interim solution if this is not a productiond database, detach the database using SP_DETACH_DB and delete the Transaction log file .LDF. And use SP_ATTACH_SINGLE_FILE_DB which will create a new Tlog file. Refer to books onlien for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Frank I tried your suggestions with the Backup Log and Shrinkfile. I tried them previously but gave it another shot. Still no luck. I may have to try you last suggestion about detaching the db. Satya this is production so I am going to have some downtime. I am still looking for other suggestions as to why the shrink is not working. Any help is appreciated. This is very frustrating because I am doing everything by the book.

Have you tried dummy updates? Recently someone posted script doing this, try to find that topic. Dummy updates are supposed to move inactive part of log at the end, then you should be able to have your log truncated.

mmarovic, yes… when i added and deleted a record in a database table this should cause the move the inactive part of the log to end.

In most cases one record is not enough.

i update 57000 records and still nothing happens.

quote:i update 57000 records and still nothing happens.

When your log is over 1 GB even that might not be enough. I’ve never had to solve this problem myself, but I know people who did it this way. If downtime is not acceptable to apply detach/attach solution maybe you can try a few more iterations with insert/delete approach.

The detach/attach approach may be my only solution. I tried it on a test server and it does work. It only takes a minute to do and my database will not be down too long. Thanx for all the help. If someone has a better way yet please feel free to let me know. Thanx to all.

Strange…
All I can offer are some links to MS knowledgebase. Maybe they are of some help
http://support.microsoft.com/?id=317375
http://support.microsoft.com/default.aspx?scid=kb;EN-US;110139
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318 Good luck! ———————–
–Frank
http://www.insidesql.de
———————–

Although not exactly the same, I have a similar problem in shrinking the datafiles in a couple of databses.
I started out with a 50GB db, and deleted a couple of years of historical data. I then rebuilt all the indexes, and ran a dbcc checkdb. Now when I try to shrink the database using either the gui or using dbcc shrink database or dbcc shrinkfile, I get the message back that my db has been successfully shrunk, but I still see that the data file has 18GB of unused space, and seems the same size on the disk. I have managed to do this on another database, which shrunk successfully using the same apporach (via the gui), but on this particular one – no cigar. I have the luxury that tis database is not yet part of the production system but it will be when shrunk, so I am able to take it offline,and restart the server etc… Any ideas of what I can do to force the datafile to shrink? Tia
Paul

Seehttp://www.sqlservercentral.com/scripts/contributions/26.asp. This script performs transactions to forcibly fill up the virtual log file and then it continually shrinks the log file. I have used this before with success. I have on occasion needed to run it more than once but ususally the first time works.

I figured out an easy way to shrink the DB and Log. This does not require any database downtime. I created a stored procedure which loops thru a couple update statements. Each time I perform the updates I then: truncate the log, shrink the database, shrink the log file and get the new log size. This eventually fills the log to the end and loops to the beginning of the log. The shrink then is able to set it to my desired size. The loop stops when the log reaches the size I desire. tblshrinkdb is a table I setup in my database. My table has about 4500 rows. The more rows updates the faster the log will fill to the end. Be sure the @newsize value in the While statement is set higher than the shrinkfile will make the log file size, otherwise you will loop forever. This stored procedure needs to run while pointing to the database which needs to be shrunk. I run it on Query Analyzer and change the DB drop down listbox to the database I want to shrink.
If you have any questions please let me know.
CREATE PROCEDURE usp_shrink_log AS declare @newsize int, @increment int select @newsize = size from sysfiles where name = ‘databasename_log’
set @increment = 1 print @newsize while @newsize > 5500
begin
update tblshrinkdb set facility_id = @increment
set @increment = @increment + 1
update tblshrinkdb set facility_id = @increment
set @increment = @increment + 1 backup log databasename with truncate_only
dbcc shrinkdatabase (databasename, 30)
dbcc shrinkfile(databasename_log, 40)
select @newsize = size from sysfiles where name = ‘databasename_log’
print @newsize end

]]>