1

I recently took over management of a database that has been in use for 2-3 years, and it had no transaction log maintenance plan in place. The DB file is 8 GB, but the transaction log file is a whopping 54 GB. I started backing up the log file, and I need to reclaim that drive space. I have compared my DB to other sites within my company that had proper maintenance plans built, and their transaction logs are roughly 4 GB, which is what I would expect. This is the first time I've run into this problem.

I performed a full backup of the DB, and set up an initial transaction log maintenance plan, but I need to shrink this *.ldf file, because it is so grossly out of proportion. I searched the Stack Overflow message boards in the hope of finding a similar situation. Based on that research, I tried the DBCC SHRINKFILE but that did not yield the results I expected. I restored the DB to original (oversized log file in place) and tried the Full-Simple-Full recovery technique to truncate the log, but was still unable to reclaim the space. I even tried deleting the .ldf and going through the process of clearing the (Recovery Pending) status. I went back to the DBCC CHECKDB repair function, but after clearing the (Recovery Pending) status, I was unable to backup the transaction log at all. I started receiving a msg 42000 error 50000, which also referenced error 3013. In the end, I deleted the whole mess and restored it back to it's original state. I;ve tried to be as detailed as possible, and will be happy to clarify or expound if necessary. As I said, this is the first time I've run into something like this, but I have always started my projects from the beginning. This is the first time I've jumped into the middle of something that was built by someone else and broken when I got it.

ALTER DATABASE [DBName] SET EMERGENCY;

GO

ALTER DATABASE [DBName] set single_user

GO

DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

GO 

ALTER DATABASE [DBName] set multi_user 

GO

My expected result is an uncorrupted transaction log that is appropriately sized for my database. Let this be a cautionary tale and reminder to ask all the questions before accepting management of someone else's screw-up.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Thomas Glosser
  • 13
  • 2
  • I might be missing something here, but what is your question? – Thom A Oct 23 '19 at 17:48
  • I might have been too wordy... My question is how do I shrink the transaction log down to an appropriate size without corrupting it? I know why it is so huge, and I know that it's not supposed to be this way, which is always the first and second questions I've seen. Also, thanks for fixing my board formatting. I thought the template would do it automatically. – Thomas Glosser Oct 23 '19 at 17:50
  • 3
    [Shrinking a log file to a specified target size](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15#shrinking-a-log-file-to-a-specified-target-size). If that isn't working as you expect, you should be telling us why. – Thom A Oct 23 '19 at 17:53
  • If I knew why, I wouldn't be asking you for help, would I? – Thomas Glosser Oct 24 '19 at 16:24
  • If what you see isn't what you expect, but you don't know why, how can it be what you don't expect..? Only you know why it's not what you expect. – Thom A Oct 24 '19 at 16:51

1 Answers1

0

If you're on 2008 or older you can try 'BACKUP LOG WITH TRUNCATE_ONLY'

Otherwise switch the database to simple recovery, this will clear out the log. then run DBCC SHRINKFILE to shrink the file size itself.

Watermelon
  • 169
  • 4
  • DBCC SHRINKFILE not reduce log file ldf. For me log_reuse_wait_desc not returns any data. DBCC SQLPerf(logspace) return 99,99% Log Space Used DBCC LOGINFO returns 11059 rows, all Status = 2. – Kiquenet Jan 28 '21 at 20:10