log file huge; no drive space | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

log file huge; no drive space

I inherited a client yesterday and what I found on their server was: a database of 500 mb in bulk logged mode
a transaction log of 6 gigabytes a hard drive with total capacity of 20 gig with 1.6 gig remaining database has not been backed up since June I want to back up the database and truncate the log, but don’t have room for a backup. I could detach the log and move it, and reattach with no log — is this safe to do if the
database hasn’t been backed up? other suggestions welcome
Yes you can very well do that. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Before that its better if you can do the following (also): Create another database on other machine.
Use DTS or copy database wizard to make a copy of current database.
To keep additional copy if required. And force to truncate the transaction log and use DBCC SHRINKFILE to reduce the size of Log file rather than approaching the hard way to detach/attach. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Thanks for your help. Found a place I could copy the mdf and ldf files off to… set the db to full recovery
truncated the log
shrank the log file
logged into the database successfully set up db maintenance plan! thanks again
Why do you need the first two steps? And truncating the log will dimish the possibilty of database recovery in case of disaster. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Well Gaurav in this case Paul has successfully logged the database. But its a valid suggestion. Paul make sure you maintain regular backups and if required schedule a monthly job to shrink the Tlog size to avoid this situation again. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I still don’t understand, what does shrinking of log has to do with recovery model? Also generating transaction log backup would have made sense to me but not truncating it. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

But he hasn’t got space to take the backup for the Tlog, so truncation is only option before you shrink the Tlog. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Okay let’s take this one at a time. 1. Setting the recovery model to full recovery will make the transaction log fill faster. So if there is any little space left, that will also be consumed. 2. The maintainence plan never mentiones taking backup of database. If transaction log is getting truncated, immediate step should be to take backup. Am I missing something? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

1. True, but in case of failure it will provide complete protection against media failure.
2. If you use Maint.plans for backups. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>