Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am stuck.

I've just taken over maintenance on a SQL Server...that has not been backed up in quite a while...The log file has grown to over 1.61TB...Yes, 1.61TB.

It has swallowed all available disk space. I've tried to backup (can't because of the log file size), I've tried detaching (can't because the logfile is full), I've tried shrinking the DB and log file (can't...get various errors.) I'm in a really tough spot for a couple of reasons...

  1. This is no longer a program that is 'used' but folks still access it to get data.

  2. This has taken a 2TB drive down to 9.94MB.

Help.

EDIT: Additionally, I can not change the recovery mode to "FULL" to do a proper backup...It just sits and spins...I'm going to give it a couple of hours and check in on it.

What a pain.

share|improve this question
3  
What recovery model is the database in? –  squillman Aug 8 '13 at 15:08
    
I also hope/assume this is not the system disk. –  Katherine Villyard Aug 8 '13 at 20:21

3 Answers 3

Usually in these cases, it's best to switch the recovery mode of the database to Simple, then do a shrink on the log file (not the database itself). Sometimes you'll have to shrink the log a couple times to get it down.

If you can't get a good backup before doing this, be sure to get a good back immediately after.

share|improve this answer
    
DB has been in Simple...can't switch to full because...you guessed it, log file is full... –  JohnM Aug 8 '13 at 18:55
    
It's really odd for the transaction log to grow like that in simple recovery. –  Katherine Villyard Aug 8 '13 at 20:07
1  
The usual thing that can cause it to grow in Simple is a stuck open transaction. If you have a transaction that never closes, you cannot ever shrink the log. Run "DBCC OPENTRAN" to find the longest running transaction. –  KJ-SRS Aug 11 '13 at 19:03

Since you said the database is in simple recovery... Try this, in chunks, during scheduled maintenance (there will be downtime).

USE master
Go
ALTER DATABASE YourDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [YourDB] SET RECOVERY SIMPLE WITH NO_WAIT
GO

(I know, I know, you said it was simple. But that's some weird behavior for simple recovery.)


USE [YourDB]
GO
DBCC SHRINKFILE (N'YourDB_log' , 0, TRUNCATEONLY)
GO

and

USE [YourDB]
GO
DBCC SHRINKFILE (N'YourDB_log' , 0)
GO

(Repeat as necessary.)


ALTER DATABASE YourDB
SET MULTI_USER;
GO

And then back it up.


Edit: Okay, so that didn't work. If you have another SQL 2005 server, you might want to try copying the mdf to the other server and using

EXEC sp_attach_single_file_db @dbname='YourDb',
@physname=N'D:\Path\YourDb.mdf'
GO

If that works, you can back up the old database files elsewhere, drop the old database, and reattach the new database. Disclaim, disclaim, disclaim. (Uncommitted transactions will be lost.)

share|improve this answer
1  
Isn't the system disk... :-) –  JohnM Aug 8 '13 at 20:25
    
I will try that a little later. Thankfully, this DB is used as reference only, but we still do look at it quite a bit. –  JohnM Aug 8 '13 at 20:25
1  
OF course: Msg 9002, Level 17, State 6, Line 1 The transaction log for database 'Earglue' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases –  JohnM Aug 8 '13 at 20:28
    
Okay, I stepped up the suggestion to something more aggressive. –  Katherine Villyard Aug 8 '13 at 20:47

Having experienced this problem just last week, I switched it to simple mode, but it would not let me shrink the database. The solution I used was to turn the backup to Simple, then create a backup, which theoretically clears the log file. On my server it did not, so I went ahead and restored the Database from the backup I had just created, then deleted the original once everything checked out. My Database file size is now 25mb from 200gb. The downside to this however is that you loose all your point in time backups (meaning your oldest backup is now what is currently running on your server). It was not an issue for us, but may be an issue for you.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.