November 29, 2011 at 4:24 am
we have a database that is 17G and the log file is 40G, we cannot backup the file as there is not enough room on the server, we have tried shrinking the log file but it doesn't change, taking the database off line and deleting the log file isn't an option as they want to keep the database on line.
What can we do?
😀
November 29, 2011 at 4:49 am
Sounds like you're in FULL recovery but you haven't been running log backups[/url]. There's no way to bring in a portable drive or something to set up for a backup? If not, try changing the recovery to SIMPLE. Then run a CHECKPOINT. Wait about two minutes. Run another CHECKPOINT. You'll probably be able to shrink the files then.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2011 at 5:02 am
When running check point do I omit the checkpoint_duration or set to a specific value:-)
November 29, 2011 at 5:21 am
Just run checkpoint. You're telling sQL Server to flush to disk, which means it'll clear committed transactions from the log. But, since your logs are currently full, most of the writes will still be at the tail of the log, preventing you from shrinking it. That's why I recommend two checkpoint operations.
By the way, this is not a standard, do this weekly kind of operation. This is "I have an emergency" operation. If you find yourself doing this more than once a year (and that's too much), you need to follow the advice in the article I linked.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2011 at 6:14 am
All of what Mr. Fritchey said but I wanted to add a little.
If point in time restores are important, do not forget to switch back to full recovery after this is done. Run full backups after changing back to full recovery. Put a solid backup strategy in place to include transaction log backups and you won't (shouldn't) have this problem in the future.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply