2

I am now taking log backups daily after a long gap(1.5 years). Now when I run,

DBCC SQLPERF('logspace')

Result,

Database  Log Size (MB)   Log Space Used (%)    Status
master     1.742188     64.09753               0
tempdb     51.17969     33.19341               0
model      0.9921875        48.4252            0
msdb       4.554688     55.6711            0
MyDb       15618.68     0.7029594              0

Should I shrink the log file. If yes then how?

Imran Qadir Baksh - Baloch
  • 1,299
  • 6
  • 18
  • 31
  • I don't see how this is a duplicate of that question. AFAICT, the OP has dealt with the log growth and now wants to shrink the file. – Jon Seigel Jun 09 '14 at 04:20
  • @Jon if it gets re-opened, I will likely close it as primarily opinion-based, as I'm not sure there's really any need for a "should I shrink?" question... – Aaron Bertrand Jun 10 '14 at 15:56

1 Answers1

0

If you're really strapped for disk space, you can of course shrink your database log file.

Switch your recovery model to SIMPLE and then shrink the database file using the DBCC SHRINKFILE('logfilename') command.

If you're running a production system, make sure that you switch your recovery model back to FULL immediately after shrinking the log file, and run a full database backup, in order to get a new point in time recovery point. You can then schedule your log file backups to run every few hours ( depending on your need ).

druzin
  • 2,308
  • 13
  • 15
  • 2
    -1, there's absolutely no reason to change the database recovery model in this situation. – Jon Seigel Jun 08 '14 at 16:07
  • @JonSeigel then what's your suggestion. – Imran Qadir Baksh - Baloch Jun 08 '14 at 18:42
  • I agree with @JonSeigel. You can do the DBCC SHRINKFILE without changing the recovery model. I would, however, include the target size parameter - the default will be the size it was at creation, and that's frequently not desirable. It is possible that the first shrink will not take it down to your desired size because the used blocks are at the end of the log file. If that happens, wait a while and then try it again. – Ed Leighton-Dick Jun 09 '14 at 00:10
  • @user960567: Run DBCC SHRINKFILE as suggested without touching the database recovery model. – Jon Seigel Jun 09 '14 at 04:20
  • @JonSeigel Means I need to run DBCC SHRINKFILE('MyDb')? How to get the log file name? – Imran Qadir Baksh - Baloch Jun 09 '14 at 05:06
  • 2
    @user960567: What I usually do is use the Files tab of the Database Properties window in SSMS. Set the desired size in the box and click OK (or script to wherever if you want to see what it's doing). Note that if the log file doesn't change size after this is run, read the second part of my answer here. – Jon Seigel Jun 09 '14 at 05:10