1

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.

2
  • 3
    What recovery model is the database in?
    – squillman
    Aug 8, 2013 at 15:08
  • I also hope/assume this is not the system disk. Aug 8, 2013 at 20:21

3 Answers 3

4

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.

3
  • DB has been in Simple...can't switch to full because...you guessed it, log file is full...
    – JohnM
    Aug 8, 2013 at 18:55
  • It's really odd for the transaction log to grow like that in simple recovery. Aug 8, 2013 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, 2013 at 19:03
2

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.)

4
  • 1
    Isn't the system disk... :-)
    – JohnM
    Aug 8, 2013 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, 2013 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, 2013 at 20:28
  • Okay, I stepped up the suggestion to something more aggressive. Aug 8, 2013 at 20:47
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .