August 4, 2004 at 12:05 pm
the log file not shrinking when running dbcc shrinkfile and database and backup transaction with truncate_only. please help running out of disk space
August 4, 2004 at 12:24 pm
The issue has to do with virtual log files. Here's a script that can help
http://www.sqlservercentral.com/scripts/contributions/26.asp
August 4, 2004 at 2:02 pm
do i need to do anything else? or just cut and paste this to query analyzer without modifying any syntax.
thank you very much for your help.
August 5, 2004 at 4:03 am
This is a useful script but the one pointed to here is a for version 7 (I think) and does not work in 2000. You will need to make a change to the script: the output from the dbcc loginfo command has a column called CreateLSN (the last column) while the procedure expects the last column to be a datetime and so fails to work.
Change the definition of the createdate column in the #loginfo table to varchar(50) and it should be ok. To run it simply type "exec sp_force_shrink_log" in query analyzer with the database you want to shrink selected as the current database.
I would recommend running this with fewer than the default number of iterations and on a test server first (of course). On the 2 occasions I've used this I used 10 replications to help tidy up the number of virtual log files, enabling the DBCC SHRINKFILE to actually remove some space.
August 6, 2004 at 3:16 pm
I had the same issue. All I did was to make a full backup, then dbcc shrinkfile. My log files were shrank immediately.
Minh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply