Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anderskd
Starting Member
25 Posts |
 Posted - 2009-12-17 : 13:48:16
|
| Hello,I have a question regarding shrinking a log file on a database in Full recovery mode. I know this is a topic that has been discussed a lot, but I am still unclear on a couple things.I work with accounting software and help out with dba duties from time to time. Our dbs are all in full recovery mode and tlogs are taken every hour - so for the most part they don't grow too much bc they're flushed to a tlog backup regularly. Occasionally, I have to do an upgrade or big long procedure that writes a lot of transactions to the log in a short time. This can make the tlogs grow large pretty fast. We are pretty tight on our SAN storage drive where our tlogs are stored. When I have to do this on many databases, it can fill up (which is obviously not a good thing).So when I do this, I try to shrink the log files to keep them a reasonable size after running the large transactions. From everything I've read, there are two (possibly more) ways to do this.1. Backup the log with truncation, then shrink the file2. Switch to simple mode and shrink the log file, then switch back to full mode.Both seem to work about the same - however, both break the log chain and require a full database backup to ensure no data is lost.My question is this: Is there a way to keep the database in full mode and shrink the log file without breaking the log chain? I have tried running a tlog backup (which I can see frees up space within the log) then shrinking the file; but it won't physically reduce the size when I shrink it (unless I do one of the two options listed above).Thanks for any insight or explanation someone may have on this. I am using SQL 2000, so maybe this has been fixed or changed in more recent versions.--anderskd |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-17 : 20:06:31
|
| You can not truncate the transaction log without breaking the chain. Why do you need to shrink them though? If some future process is going to need the space, then you shouldn't shrink it at all. If disk space is a concern, then buy more to cover these processes. Or better yet, do your processes so that they perform the DML operations in smaller chunks.To answer your question though, run a normal tlog backup and then a normal dbcc shrinkfile without truncation. I see that you say you've done this, but it really does work. You just might have data at the very front of the file so you have to wait until it shifts to the back.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
|
|
|
|
|
Shrinking Log without breaking log chain