1

Just started with a company and noticed that their database was set to Simple Recovery.

I talked to the owner and suggested to convert it to Full Recovery, explained to him the benefit of using a transaction log and backed up every hour. After he agreed I did a full DB backup prior to conversion. Then scheduled hourly backup for the Transaction log file and Full Nightly backup for the Data File.

It was my impression that once the hourly backup started running, the size of the transaction log (60GB) would shrink. It's been more than a month but the size of the transaction log is still the same.

Is it okay to run DBCC ShrinkDB against the Log file without detaching and attaching the database?

RSPerez
  • 11
  • 4

2 Answers2

1

I have the impression that once the hourly backup started running the size of the Tlog, 60GB will start to shrink. It's been more than a month but the size of the Tlog is still the same.

Log file will not shrink automatically

Is it okay to run DBCC ShrinkDB against the Tlog

Don't shrink log file,unless you are in shortage of space.Reason behind this is, file growth operations are expensive

you can see below command to view free space in log file

dbcc sqlperf('logspace')
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • Greatly appreciated your response, I ran the dbcc sqlperf ('logspace') and here is the result ... DatabaseName LogSize = 61023.37 Log Space Used = 0.4820387. My Tlog ran an average of 38MB per day. Base on what I read, it is best to convert my database back to simple and run the Task to Shrink File. Once the size shrink I can return it back to FULL.. Is that something that I can use? – RSPerez Mar 19 '18 at 20:41
  • Yes, you can do that as well.But remeber it breaks log chain – TheGameiswar Mar 24 '18 at 05:06
  • Thank you very much – RSPerez Apr 12 '18 at 16:15
1

The log backups that you're taking will help to keep the log file from growing, but the log file won't shrink on its own. Internally, the log file is segmented into virtual log files (VLFs) and are used in (more or less) a cyclical manner. While you're running your work load, transactions are being recorded into these VLFs. When the log backup runs, it will read from any VLF that had transactions since the last log backup, write those transactions to disk, and then clear the VLF and mark it as available for reuse.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68