CODEWITHSUNDEEP
sql-serverdatabase-backupstransaction-log
RSPerez
RSPerez

Reputation: 11

Transaction Log remains 60GB size, even after changing to Full Recovery

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?

Upvotes: 1

Views: 728

Answers (2)

Ben Thul
Ben Thul

Reputation: 32667

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.

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

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

Upvotes: 1

Related Questions