SQL 7 Trans log too big. How shrinK
-
One user has a 13GB partiton on his D drive. The Database backup job failed letting the trans log grown to 8GB! I fixed the db dump problem but the trans log is still 8GB in size and won't let me skrink it. What can I do?
-
I am assuming that dbcc Shrinkdb is not making the log file smaller, right? Or is an error occurring?
If the log is not shrinking, it may be because the partitions are not empty. You can use this script to help shrink the files:
Steve Jones
-
using query analyzer run this below script
use master
exec sp_dboption 'your database name', 'trunc. log on chkpt.', 'TRUE'
then goto enterprise manager
right click on your database goto all taksks
click on shrink database and say ok
u r trascation log size is reduced.
-
That may or may not work. Sometimes the log file will not shrink because there are no full virtual files within the log. The script above will handle that.
Steve Jones
-
Thanks to everyone for your help.
I found another way to shrink the file size (on disk)of the ldf file: (only works if DB has 1 trans log)
sp_detachdb '<dbname>'
delete .ldf file
sp_attachdb '<dbname>'
When you reattach the DB, SQL Server notes that the log file is invalid an creates a new one (in my case of size 1 MB! Quite a drop from 8 GB).
Bill
-
Hi,
I've recently seen a log file that was 44 GB big.
The system had almost stopped working.
We've not been able to figure out why the log file had grown to this extreeme.
Do you know why this happens?
And more important: How do we avoid it re-occuring?
TIA
Henrik Staun Poulsen
Stovi Software
Denmark
-
This could happen if the db was being replicated and the log reader was not running. Without having it running to 'clear' entries in the log, there is no way to free up space. Index rebuilds can also take quite a bit of log space.
Andy
-
Yes,
What happened in my case was that the DB backup failed to run (?) and all changes were stored in the log file. An 8GB log is not that big but the original databases was about 0.5 GB!
-
Well,
<< What happened in my case was that the DB backup failed to run >>
I think I know why we got the big log file then.
It's a bit of a Catch 22 here.
The backup failed to run because it was out of disk, I think.
And the log file just grew and grew until it was really out of space.
Better do something about it
Henrik
-
Bill, didnt get a chance to reply earlier, but the detach/reattach solution is one I've used myself - has the merit of simplicity! It's also very fast.
Andy
-
Looks like it doesn't work on SQL Server 2000 SP3 anymore, though...
-
ngolovin,
I will certainly check into your remark regarding SP3! Thanks for the heads up.
Bill
-
A common method is to backup the transaction log then shrink the database files using DBCC SHRINKFILE to change the size of .ldf file
Luani
Luani -
The detach/attach does work with 2000/SP3. Had this problem and talked with Microsoft. They don't recommend the detach/attach approach although I've used it for two years with no problem. However, Microsoft did recommend backing up the Transaction Log independently. This keeps the size down. Also, they recommended these Disaster Recovery articles:
307775 INF: Disaster Recovery Articles for Microsoft SQL Server
http://support.microsoft.com/?id=307775
Backup Recovery webcast
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wcd092002/wcdblurb092002.asp
Troubleshooting Planning Books Online Topic
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_hightrbl_8pd3.asp
Command to perform a transaction log backup while initializing the file -
Backup log databasename to disk = 'c:\backups\databasename\tlog\tlog_databasename.bak' with init
Command to perform a transaction log backup and append to an existing file
Backup log databasename to disk = 'c:\backups\databasename\tlog\tlog_databasename.bak'
One more thing, this is an excellent Whitepaper written by the very man I spoke to at Microsoft:
Edited by - DALEC on 02/27/2003 10:44:57 AM
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
I have been using the Detach/Attach method without any problems, but I always rename the LDF file rather than delete it. Once I reattach the database and verify the Attach was successful, then I will delete the LDF. If you have problems running Attach, you can rename your LDF file back to the original name and Attach the existing LDF file.
If you are detaching thru QA and don't notice any sort of error message during the Detach process (like disk is full), you can be in a whole lot of trouble with a deleted log file when you try to re-attach. Also, in SQL 2000 EM, it is fairly easy thru the GUI to Detach and Attach.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply