Can't reduce size of log file

Question

Can't reduce size of log file

gvdamn

SSC-Addicted

Points: 460
More actions
August 9, 2010 at 7:43 am

#387530

Hi,
My database is 213 MB in size. (The database is in full recovery mode.) The size of my log file is 1800 MB. The log space used is 2.01%. I (very) recently backed up my database and log file.
I'm trying to reduce the file size of my log file, but don't succeed. I used:
ALTER DATABASE DataBaseName
MODIFY FILE
(
NAME = DataBaseName_log
, SIZE = 500MB
)
I'm getting the error message:
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than or equal to current size.
Any help is much appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

gvdamn (8/9/2010)
Hi,
My database is 213 MB in size. (The database is in full recovery mode.) The size of my log file is 1800 MB. The log space used is 2.01%. I (very) recently backed up my database and log file.
I'm trying to reduce the file size of my log file, but don't succeed. I used:
ALTER DATABASE DataBaseName
MODIFY FILE
(
NAME = DataBaseName_log
, SIZE = 500MB
)
I'm getting the error message:
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than or equal to current size.
Any help is much appreciated.

1- Check if backups are actually running fine - Full backup as well as TLOG backup.

2- Check if there is a long standing transaction running on the system.

3- Take a TLOG backup

4- Shrink TLOG - instead of "alter database modify file" - this will mark TLOG to be shrunk as close as its original size as possible.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

gvdamn SSC-Addicted Points: 460 More actions · Answer 2

Thanks for your reply Paul.

There are no open transactions on the system.

I did take a backup of my transaction log (from SSMS). I guess that's the same as "3- Take a TLOG backup".

Could you give me some directions on suggestions 1 and 4?

gvdamn SSC-Addicted Points: 460 More actions · Answer 3

I did try "DBCC SHRINKFILE('DataBaseName_log',500);" but it did not reduce the size of my log file.

gvdamn SSC-Addicted Points: 460 More actions · Answer 4

Problem solved.

Following the 3 steps in:

http://sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx did the trick.

Thanks again Pablo (Paul) for your input.