November 26, 2008 at 3:37 pm
If I use...
DBCC SHRINKFILE (Transaction_log_filename)
...would that shrink the transaction log to whatever size it is now?
Situation:
We have a server running low on space. We've got another new server on the way but I need to free up some space until we can get it in place. Right now it's 2GiB+. If I shrunk it, I'm guessing it would go down to about 10MB. A transaction log backup isn't shrinking the file. All I want to do is shrink it to whatever amount of data is in it. If there is 15MB, I don't want to lose data by doing this....
DBCC SHRINKFILE (Transaction_log_filename, 10)
.....or would I lose data by doing that?
November 26, 2008 at 3:49 pm
You are not going to lose any data by shrinking the transaction log. However, if your transaction log is 2GB now - something caused it to grow to that size previously. If you don't know what caused the file to grow in the first place, it is just going to grow again - but, this time you might not have enough space to complete the transaction.
If that happens, your database is going to a screeching halt until you can clear the transaction log. Are you sure that is what you want to happen?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 26, 2008 at 3:55 pm
Yes, I want to shrink the file.
I know why it got that big.....kind of a long story to type out. Bottom line is backups weren't being done for over 2 weeks after an upgrade.
Our transaction log backups are anywhere between 4MB and 20MB every 2 hours now.
November 26, 2008 at 4:16 pm
J M (11/26/2008)
Yes, I want to shrink the file.I know why it got that big.....kind of a long story to type out. Bottom line is backups weren't being done for over 2 weeks after an upgrade.
Our transaction log backups are anywhere between 4MB and 20MB every 2 hours now.
Absolutely reasonable reason for shrinking a transaction log file. A one time operation to reduce the file size after an abnormal event.
So, yeah - shrinkg the transaction log back down to a reasonable size. I would shrink it to 100MB myself, just to make sure I have enough space available. Use the second parameter to SHRINKFILE to specify the size.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 26, 2008 at 5:21 pm
Thank you. I appreciate your assistance.
I do have one more question.... if the transaction log is set to auto grow, would you still set the size to 100MB on the DBCC SHRINKFILE command?
John
November 26, 2008 at 7:32 pm
Yes, I would still set the size to 100MB. Why go through the overhead of an auto-growth event when you don't need to?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply