September 30, 2011 at 6:52 am
One of our old SQL Databases has a log file that has grown to almost 100GB. I have a process for shrinking logs, but this one is too big to do it on since it requires doing a Transaction log backup and there isn't enough space to do one. What can I do to kill this file. I really don't care about any of the data in it, just need it off the drive as it is rapidly running out of space. I'm a developer with just enough knowledge of SQL Server to get myself in trouble so I don't want to break anything.
Sean
September 30, 2011 at 7:05 am
Since you've posted this in the SQL 7/2000 forums, I'll assume you are using one of those versions, so you should be able to truncate the log with this statement and then shrink it...
BACKUP LOG <db name> WITH TRUNCATE_ONLY
To stop it growing again, have a read of Gail's article on managing the transaction log...
September 30, 2011 at 7:30 am
Or switch the database to simple recovery
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2011 at 7:48 am
Ian Scarlett (9/30/2011)
Since you've posted this in the SQL 7/2000 forums, I'll assume you are using one of those versions, so you should be able to truncate the log with this statement and then shrink it...BACKUP LOG <db name> WITH TRUNCATE_ONLY
To stop it growing again, have a read of Gail's article on managing the transaction log...
What does that do exactly? That drive is so low on space at the moment, I'm afraid of filling it up and breaking the systems that work off of it.
Thanks.
Sean
September 30, 2011 at 7:59 am
Sean Grebey-262535 (9/30/2011)
What does that do exactly? That drive is so low on space at the moment, I'm afraid of filling it up and breaking the systems that work off of it.
There's a bit more to it, but essentially when you backup the transaction log, all the transactions are written to the backup file, then SQL Server throws away the transactions in the log, and starts again. If you backup using TRUNCATE_ONLY, it just throws the transactions away without actually backing them up, so you will be using no extra disk space.
As Gail mentioned, you could switch to Simple recovery mode. That's a bit like doing an automatic backup with TRUNCATE_ONLY every time a transaction has been committed.
Bear in mind, doing either of these things means you will no longer be able to recover the database to a point in time. All you will be able to do is restore the database to your last full or differential backup. Only you know if that is acceptable to your business.
September 30, 2011 at 8:00 am
Pretty much the same as switching to simple recovery. Discards all log records that are no longer needed, breaks the log chain. Lets you shrink the log back to a reasonable size (NOT 0).
Afterwards you need to switch back to full recovery (if you switched to simple) and take a full or differential backup in order to restart the log chain and allow log backups and point-in-time recovery again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply