How can I shrink a LDF file of 54GB?
We have a SQL database of 6GB with a LDF file of 54GB. I have tried to shrink the database and log file, but when I choose shrink filetype LOG, and press start, the shrinking is finished in 2 seconds and I have the feeling that nothing happened. The file is still 54GB.
How can I reduce the filesize?
Is this logfile really necessary?
How can I reduce the filesize?
Is this logfile really necessary?
ASKER
Hi Lee,
ok, but we do need the FULL setting, because we backup the LOG file (although I am not sure this works fine, since the LDF is growing). But how can I shrink the LDF file, when leaving the setting to FULL, do I have to make a backup first?
ok, but we do need the FULL setting, because we backup the LOG file (although I am not sure this works fine, since the LDF is growing). But how can I shrink the LDF file, when leaving the setting to FULL, do I have to make a backup first?
Yes you must back the log up, and then shrink it. A full backup doesn't back the logs up. You must specify that you want to back the transaction logs up separately.
Lee
Lee
ASKER
Ok, so is it enough when we backup the LOG with our own backup software or do we need to backup with de SQL Management Studio?
Yes.
You will want to do a backup of the Transaction log and truncate. After this you would be able to shrink. You should not shrink the log often because of fragging. Best course of action is to setup a maintenance plan to backup and truncate the log file often this would then require little shrinking.
you could be backing up the LOG file, but if you are not truncating the file after backup, it will not shrink.basically, the log will continue to grow till you truncate. Once truncated, it will stay the same size and use that space until it fills. Shrinking reduces the actual size, but if you have not truncated, then it will do nothing.
Think of it as a glass of milk that you pour... Once it is full, you keep increasing the size of the glass to hold the milk and then continue to pour. Truncating is like drinking a bunch of milk. Shrinking is like going to a smaller glass now that there is not as much milk left. you cannot go to a smaller glass until you have consumed some milk.
You will want to do a backup of the Transaction log and truncate. After this you would be able to shrink. You should not shrink the log often because of fragging. Best course of action is to setup a maintenance plan to backup and truncate the log file often this would then require little shrinking.
you could be backing up the LOG file, but if you are not truncating the file after backup, it will not shrink.basically, the log will continue to grow till you truncate. Once truncated, it will stay the same size and use that space until it fills. Shrinking reduces the actual size, but if you have not truncated, then it will do nothing.
Think of it as a glass of milk that you pour... Once it is full, you keep increasing the size of the glass to hold the milk and then continue to pour. Truncating is like drinking a bunch of milk. Shrinking is like going to a smaller glass now that there is not as much milk left. you cannot go to a smaller glass until you have consumed some milk.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have fixed the problem by manually running the maintainance plan. 54GB turned into 9GB.
Excellent. Set your maintenance to run on a schedule so the log doesn't get that large. Again, it is best practice to NOT shrink the file unless it has grown too large. You best bet is to not let it get to that point by running maintenance.
https://www.experts-exchange.com/questions/24779598/SQL-Server-2005-massive-ldb-file.html
http://support.microsoft.com/kb/907511
Lee