September 3, 2010 at 1:43 pm
Hi,
Any body can help me how to take log backup to disk while users are connected to sql server..
Main Purpose is .. I want to shrink the log file..
Even Better If you help me on better way to shrink log file without disturbing the conected users.
Ram..
🙂
September 3, 2010 at 2:09 pm
Is the database in FULL or SIMPLE recovery mode?
Is this due to running out of disk space or simple wanting to keep the log file size under control?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 3, 2010 at 2:18 pm
Our databases are in full recovery model..
When users reported me that they are getting timeout expire error and few applications are getting slow, I checked and found the log size is aroud 13 GB.
But for timebeing I asked all users to logoff their applications. and taken log backup with truncate_only, then I shrunk the log file..
But I fell this is not right decision to take backup like this way....
Ram..
🙂
September 3, 2010 at 2:37 pm
I would take a look at Gail Shaw's article on SSC:
Managing Transaction Logs[/url]
Then do a search on Bing/Google/SSC for "Transaction Log size" or "Log size" and you will get plenty of articles to read on the subject.
Transaction log backups keep the log file size under control. This is the only way to keep it under control when the database is in FULL recovery mode. If point-in-time recovery is not needed on the databases then there is no reason to have the DBs in FULL, change them to SIMPLE. SIMPLE recovery mode will keep the log file size under control automatically for you.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 3, 2010 at 4:46 pm
If you run regular log backups, you ought be able to set a log size and not mess with it. Read Gail's article and that should help you understand why.
Log backups do not disturb users.
September 3, 2010 at 5:09 pm
Ram:) (9/3/2010)
Our databases are in full recovery model..When users reported me that they are getting timeout expire error and few applications are getting slow, I checked and found the log size is aroud 13 GB.
But for timebeing I asked all users to logoff their applications. and taken log backup with truncate_only, then I shrunk the log file..
But I fell this is not right decision to take backup like this way....
Ram..
Nope - this was not the right decision. You have now broken the log chain and can no longer restore to a point in time after the last transaction log backup you have.
It sounds like you are not performing regular transaction log backups - you need to. At least every hour, if not more frequently.
You need to perform a full backup right now - and implement your transaction log backups as soon as possible. You will not be able to start backing up the transaction log until you have a new full backup because the log chain was broken.
Definitely review the article by Gail - link available in my sig.
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
September 7, 2010 at 5:20 am
Just to emphasize, except for circumstances where the system is under extreme load, you should be able to run database and log backups without interfering with the users. Even in systems where the load is extreme you should be able to run log backups without interfering with the users.
"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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply