November 24, 2009 at 12:49 am
I have a database that is currently 2GB. It should be about 200mb.
Recovery model is set to Full.
Daily backups of the databases is made via a job.
BACKUP DATABASE [MAINDB] TO DISK = N'D:\MSSQL\Backup\MAINDB \MAINDB Friday.bak' WITH NOFORMAT, INIT, NAME = N'MAINDB Backup - Friday', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Problem is that the log file isn't truncated, it makes up 95% 2gb.
How can I ensure that the log file is always truncated and still be able to keep the recovery model set to Full.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 24, 2009 at 1:30 am
You can truncate the LOG file just before taking full backup. This will solve the database size issue.
BACKUP LOG maindb WITH TRUNCATE_ONLY
Remember, you should always take the full backup after the command to ensure no risk for loss of data.
November 24, 2009 at 1:48 am
Just a note:
The Database is also mirrored on a backup server. Don't know whether that will make a difference to the whole scenario.
Somehow truncating the log just doesn't have any effect on the size of the database.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 24, 2009 at 7:45 am
DO NOT use TRUNCATE_ONLY! If you are mirroring you need the transaction log. The ONLY thing that allows the log file to be wrap when in FULL recovery is a Transaction Log backup.
Backup Log [DatabaseName] to DISK = [Path To Log Backup]
Please read this article, Managing Transaction Logs[/url]
I also recommend that you take a look at Paul Randal's blog at SQLSkills.com
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2009 at 4:12 am
You can't run the Transactio Log Truncate for Mirrored database. This will not work.
As your log file is very huge, and it is required to Truncate the log file for proper utilization of disk space, you need to stop the Mirroring activity, truncate the transaction log, change the database type to Simple (only if this is allowed in your business case), take a full backup and then reconfigure the database Mirroring.
This will solve the issue at large. I follow this method only when I am running out of disk space and Log file is consuming more than 50% space. 🙂
November 25, 2009 at 4:19 am
So what your saying is, Atul, is take the Database off mirroring, make backups, set to Simple, Truncate en then reverse the process to mirror it again?
Just a bit of stats ... this database has been running for 4months and is currently over 2GB. And its not even running at full capacity, maybe 10%
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 25, 2009 at 4:35 am
Turning the database on Simple recovery model will reduce the size of log file as well as improve the performance a bit.
Also make sure that you are taking the daily Full backup for your database.
If the database recovery model can't be changed, due to business requirement, then you should schedule the Transaction Log backup every 30 minutes and daily Full backup. This will release the Log file free space to operating system and Log file will not grow very fast.
November 25, 2009 at 4:44 am
Well the company has n strong policy on the mirroring issue. So that ain't going away.
Currently Full Backup are made on a daily basis.
Transaction Log Backups I have tried, but it doesn't have any effect on the size of the database. Is there a step i might me missing?
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 25, 2009 at 4:57 am
Taking a Transaction Lof will not reduce the Log file size soon. It will prevent from further fast growth atleast. You should setup the Transaction Log process in place.
Reconfiguring the Mirroring database of 200 MB is small task and can be done in 30min - 1 hour time. This does not require the principal database down even (no downtime required). You should convience the business owner 🙂
November 25, 2009 at 5:02 am
Log backups will not reduce the size of the log file. It will only clear the file so that it can be reused
Run DBCC SQLPERF('LOGSPACE')
You will see the percentage used
November 25, 2009 at 7:09 am
Atul
Is it really necessary to make Transaction Log Backups every 30min/hour?
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 25, 2009 at 7:16 am
roelofsleroux (11/25/2009)
Is it really necessary to make Transaction Log Backups every 30min/hour?
There are two things that affect the frequency of log backups.
1) How big you are willing to let the log grow. It will be minimum the size required for all the transactions in the interval between log backups. If that's too big, make the log backups more frequent.
2) How much data you're willing to lose in the case of a disaster. If no more than 10 minutes data loss is permitted, then your log backups must run at least once every 10 minutes.
Please, please, please go and read that article that Jack recommended.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2009 at 7:18 am
Atul DBA (11/25/2009)
As your log file is very huge, and it is required to Truncate the log file for proper utilization of disk space, you need to stop the Mirroring activity, truncate the transaction log, change the database type to Simple (only if this is allowed in your business case), take a full backup and then reconfigure the database Mirroring.
Huh? Why on earth would you do all that, requiring large amounts of maintenance and a risk of downtime (what happens if the principal fails while the mirror is down?)?
What's wrong with a simple scheduled log backup? (The correct way to allow log space reuse)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2009 at 7:32 am
Gila the reason we use mirrors is because this is for a stock exchange trading application.
If the database fails for some reason, the mirror kick in. If there is as much as n 10minute delay these guys could loose up to $1million due to down time.
The risk of having a database fail is too much. Hell if these guys could figure out a way to have 10 mirrors they would.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 25, 2009 at 7:36 am
roelofsleroux (11/25/2009)
Gila the reason we use mirrors is because this is for a stock exchange trading application.If the database fails for some reason, the mirror kick in. If there is as much as n 10minute delay these guys could loose up to $1million due to down time.
The risk of having a database fail is too much. Hell if these guys could figure out a way to have 10 mirrors they would.
All the more reason why you should be protecting the data by backing up the logs. Paranoia around this area is a good thing. Go and read the article that Jack & Gail have suggested.
"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 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply