CODEWITHSUNDEEP
sql-server
Sharjeel Riaz
Sharjeel Riaz

Reputation: 21

Shrinking log file in SQL Server

I am trying to shrink log file in SQL Server to save storage space but instead of decreasing the file size has been increased with a large volume. I have tried this twice, both of the time file size was increased.

Upvotes: 0

Views: 845

Answers (1)

S3S
S3S

Reputation: 25112

From what you have provided, it seems as if you ran DBCC SHRINKFILE on the log file, and it did not shrink. This is likely due to there not being any free space to actually shrink. What you want to do instead is BACKUP the log file, which will free up space, and allow you to shrink the log file back to your specified size. Afterwards, you probably want to schedule these backups based on your RPO and RTO, which it sounds like you don't have so I'd figure out what that is first.

Since it seems like your log file is growing pretty consistently, I'd assume you are in FULL or BULK LOGGED RECOVERY mode. Regardless, you'll have to conduct a FULL BACKUP before you can do a LOG BACKUP.

If this happens to be TEMPDB, clearing the cache can help.

Upvotes: 2

Related Questions