April 29, 2013 at 12:15 pm
Steve JP (4/27/2013)
Depends as the log file doesn't always want to shrink. If it doesn't then like I said a single file attach is a sledgehammer approach, never said it was a preferred method 😛
This may not actually work - and you end up having to attach with the current transaction log file. Much simpler to just switch the recovery model to simple, issue a checkpoint - validate which VLF's are in use and shrink the file until it is the size you want.
You may need to shrink a couple of times, depending on when the log rolls back to the beginning of the file - but this will work and the log will shrink. Unless there is something else causing the log to not be able to be truncated (e.g. replication).
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2013 at 12:36 pm
zi (4/26/2013)
(you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,how to run a checkpoint?
Thanks
At this point, I'm going to recommend that you slowly back away from the computer and approach the phone to call a pro in for help. I understand that you're trying to fix this problem but you can really screw things up if you haven't done this a half dozen or more times before. Production is not the place to learn these fixes.
Seriously... get a pro in to help you.
--Jeff Moden
April 29, 2013 at 12:43 pm
Jeffrey Williams 3188 (4/29/2013)
Steve JP (4/27/2013)
Depends as the log file doesn't always want to shrink. If it doesn't then like I said a single file attach is a sledgehammer approach, never said it was a preferred method 😛This may not actually work - and you end up having to attach with the current transaction log file. Much simpler to just switch the recovery model to simple, issue a checkpoint - validate which VLF's are in use and shrink the file until it is the size you want.
You may need to shrink a couple of times, depending on when the log rolls back to the beginning of the file - but this will work and the log will shrink. Unless there is something else causing the log to not be able to be truncated (e.g. replication).
When you attach a database file, if the log file cant be found then it will recreate a default sized log file at the correct location. I think the new size is based on the model.ldf file size.
True its not ideal, hence my comment that it is a sledgehammer approach, unless you see a sledgehammer as a panacea in which case.......
But, if you only have a tiny maintenance window and the shrink doesn't work then yes the single file attach db is a method that does work.
In the old days (7 & 2000) the only other method to force a log file shrink was to move the active part of the log to the front before a dbcc shrinkfile was successful. It wouldn't resize to anything less that the last offset of the active virtual log file. And yes, to move the active part meant having a loop to consume a massive amount of log space with many log file backups and diff backups to maintain the SLAs. Updating a dumb row in a dumb table a million times with a replicate ('a' , 8000 ) does nudge the active part of the log file 😉
On a personal note I wouldnt want to start flipping the recovery state to simple on a business critical system unless it was during a maintenance window. All depends on the SLAs of course, but I have seen too many good intentions and "it will only be for a minute" going awry to risk it.
April 29, 2013 at 2:03 pm
Steve JP (4/29/2013)
When you attach a database file, if the log file cant be found then it will recreate a default sized log file at the correct location. I think the new size is based on the model.ldf file size.
True its not ideal, hence my comment that it is a sledgehammer approach, unless you see a sledgehammer as a panacea in which case.......
But, if you only have a tiny maintenance window and the shrink doesn't work then yes the single file attach db is a method that does work.
In the old days (7 & 2000) the only other method to force a log file shrink was to move the active part of the log to the front before a dbcc shrinkfile was successful. It wouldn't resize to anything less that the last offset of the active virtual log file. And yes, to move the active part meant having a loop to consume a massive amount of log space with many log file backups and diff backups to maintain the SLAs. Updating a dumb row in a dumb table a million times with a replicate ('a' , 8000 ) does nudge the active part of the log file 😉
On a personal note I wouldnt want to start flipping the recovery state to simple on a business critical system unless it was during a maintenance window. All depends on the SLAs of course, but I have seen too many good intentions and "it will only be for a minute" going awry to risk it.
There is the possibility that you will not be able to attach that database without the log file. The database has to be shutdown cleanly, with no open or in flight transactions that would have to be rolled back/rolled forward. If that situation occurs, the database will not create a new log file - and you then have to put the old log file back.
The ideal solution is to perform a log backup, but in this type of case - the ideal solution probably wouldn't work. So, changing the recovery model will truncate the log. Once the log has been truncated, you can then shrink the file to the active VLF...
Issue a checkpoint - maybe a couple - to get the log to roll back to the beginning and shrink again. Repeat until you have the log file at the appropriate size.
Once you have that, switch recovery model back to full - take a full backup - and restart your transaction log backups.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2013 at 8:15 pm
Jeffrey Williams 3188 (4/29/2013)
switch recovery model back to full - take a full backup - and restart your transaction log backups.
Taking a diff backup instead of a full maybe quicker and will still restore the log chain.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 30, 2013 at 12:11 am
There are a few more things other than just size to consider about the log file. I've been through this with every new company that I've worked for (quarter tera-byte log file blew up on my second day at one company) and I just went through all of this about 10 minutes ago on a similar thread.
In the area of performance, the initial settings are horrible. It'll grow the log file in a very fragmented way which can have a great impact on performance. It'll also allocate some pretty small "VLF's" or "Virtual Log Files". Think of it kind of like having a disk with a super small sector size. Not very effective. For more on how to "right size" the underlying VLF's in the Sql Server log file, please see the following URL.
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
That brings us to the next point. How big is your database and how big is the largest table in the database? There are a few more aspects to database maintenance that use the log file and that you must consider when trying to right size the log file. Index maintenance and the rebuilding of statistics. Both are extremely import to performance especially when you have nightly jobs that affect or read from many rows and both are logged in the log file when they occur just in case something goes wrong. The link to Gail's article that you were previously provided will help there quite a bit. I use the rule of thumb that the log file should be about 1.5 times the size of the largest table to start with and then should be managed for growth to control the size of the VLFs that I previously spoke of. Log file growth should never be a surprise (should never happen automatically) in my humble opinion. Neither should the data file growth.
Another thing to consider is how important your data is. Way too many people will set a database to "Simple" recovery mode because it's a small database and they do nightly full backups and think that's enough. If the data is important, nothing could be further from the truth. Ask yourself how many minutes or hours of data your company can afford to lose and then setup log backups to run in no more than that amount of time. Personally, I won't tolerate the loss of any data so I do "Point-in-Time" backups on my production databases every 15 minutes. That also helps keep the log size down on busy systems. "Point-in-Time" backups require that only the FULL recovery mode be used. If you go to the Bulk Logged mode, then every backup taken while you're in that mode must be used in it's entirety or not at all during a restore... and you can't skip logs. That's why they call it a "log chain".
Getting back to your original problem, step 1 is to visit the articles I recommended above and figure out what the correct size log file should actually be. Even if you decide it's too small and needs to grow, if the initial settings were left at the default, consider blowing away the log and starting over.
Step 1 would be to take a full backup.
Step 2 would be to take a log file backup.
Step 3 would be to change the recovery model to the SIMPLE mode when not much is happening so that if something does go wrong during this timeframe, you won't lose much.
Step 4 is to shrink the log file to 0 bytes. Don't shrink the database. Shrink only the log file. This will clear out all the "bad stuff" with the incorrect sized VLFs.
Step 5 is to change the recovery model back to FULL.
Step 6 is to change the initial size of the log file to the size you planned on like I recommmended earlier. Also change the growth setting to grow in MB rather than percent just in case there's acccidental growth. The number of MBs should be with what size you want the VLF's to be if growth occurs.
Step 7 would be to take either a differential backup (smaller and quicker than a full backup) or another full backup to restablish the log chain.
Step 8 would be to turn on a maintenance plan or custom code to do log file backups every x number of minutes as previously discussed.
Step 9 would be to ensure that regular tape backups are being taken of all of your SQL Server backups.
Step 10 would be to setup a plan to delete the backups from disk after they've been backed up to tape and after some period of time has elapsed. I keep 2 to 3 days of backups on disk for quick recoveries if anything goes wrong and it has saved my bacon more than once (especially when you have developers and other folks that have too many privs in prod).
To emphasize, the only time that I'd ever set a database to SIMPLE recovery on a permannent basis is when I truly didn't give a damn what was in it. Those could be staging databases, scratchpad databases (similar to Temp DB), and maybe even experimental "sandbox" databases. I would never permanently set a production database to anything other than the FULL recovery mode.
--Jeff Moden
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply