0

I came to work today and I saw the transaction log with 441GB, using almost all disk space. I use SQL Server 2012 and have Full backup, but somehow I lost control of the t-log. So, I need to do 2 things: first, clear the log, so I have the disk space back and control it. I thought to control it doing daily backups. But I just can't figure how to reduce it. Shriking is not a good thing to do, and anyway will take AGES to do it. I've try to bakcup the log like this: Backup Log DB_1 TO DISK = 'Nul:' but nothing changed. So what exatcly can I do to fix this situation?

Thank you!

Antonio
  • 147
  • 2
  • 2
  • 11
  • 1
    Why on earth would you try backing up to NUL? If you don't care about transaction log backups (and point-in-time recovery), stop using full recovery. If you do care, then start backing up your log properly (not to NUL). – Aaron Bertrand Mar 09 '15 at 15:33
  • I care about transaction log, but I have to reset then I will control it's size. It became a monster so I have to start over or at least reduce it size. I'm backuping to nul so it truncate. But the size did not changed. – Antonio Mar 09 '15 at 16:35
  • And even I don't care with a point-in-time recovery and change it to simple, the log still with 411GB. Need to clear it up! – Antonio Mar 09 '15 at 16:50
  • Truncate <> shrink. Truncate is just freeing up space within the file. Switch to simple recovery mode, issue CHECKPOINT; twice, then use DBCC SHRINKFILE. – Aaron Bertrand Mar 09 '15 at 16:59
  • USE DB_1; GO CHECKPOINT; GO CHECKPOINT; GO DBCC SHRINKFILE(Demo_Log, 200); GO

    Nothing changed.

     – Antonio Mar 09 '15 at 17:06
  • What does SELECT recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = N'demo'; say? – Aaron Bertrand Mar 09 '15 at 17:10
  • Nevermind. Changed to simple, shrinked and back to full, now controling the file. Thank you! – Antonio Mar 09 '15 at 17:27

0 Answers0