October 16, 2003 at 12:54 am
Hi All,
I encountered the following problem: The company I work for has a database installed with a 3rd party and for some reasons, no database backups have been done on that server for quite a while (Normal disk backups have been done ... Don't ask).
Now I had the chance to go through the database and found that the Transaction Log grew to about 2 Gb (Original size about 100 Mb when installed).
I did full backups, transaction file backups, etc. and tried to shrink and truncate the logs and the actual data is back to about 150 Mb, which is fine, but the file itself does NOT want to shrink, because for some reason, it thinks its original size is 2Gb.
I ran a couple of scripts to get the log file to shrink, from Microsoft, but this only made the situation worse. The Log file is now about 5Gb, where the data is still only 150Mb.
Any ideas anyone? Space is not a big issue now, but it could be in the future, when this problem persists, so I prefer to fix it ASAP ...
Thanks in advance for your help.
October 16, 2003 at 5:56 am
When I've ran into stubborn logs, this script has helped
http://www.databasejournal.com/scripts/article.php/1446961
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
October 16, 2003 at 6:06 am
Hi Ray,
That's the one I've been using (thought I found it on the MS website, but could be wrong), but it didn't work. The problem is that it only tries to shrink it to the ORIGINAL size, which for some reason is the 3GB filesize it has now....
According to BOL, the original size does not change with automatic growth, but apparently in this case it did ...
Robert
October 16, 2003 at 6:59 am
Which version of SQL Server you use?
October 16, 2003 at 7:17 am
That server is running SQL Server 7 SP4.
Rgds,
Robert
October 16, 2003 at 7:26 am
That is what I expected to hear. Shrink database transaction log in 7.0 is different than in 2000. See details in following KB.
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650&Product=sql
October 16, 2003 at 1:15 pm
You should use sp_force_shrink_log, written by Andre Zanevsky. It works on 7.0 and 2000.
October 17, 2003 at 1:22 am
Hi Clark,
That did the trick! Thanks! Also to Andre Zanevsky, whereever he is 😉
Allen, I read the article you mentioned before, but as with Ray's script, the problem is that for some reason, the log reports that it's ORIGINAL size is 3+ Gb, where it was originally created at a few hundred MBs.
I haven't had a thorough look at the script from Andre yet, but I'll try to find where the difference lies.
Anyway, thank you all for your help! It's much appreciated!.
Robert
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply