May 4, 2011 at 2:22 am
Hi;
Team; we have arround 50 databases in our SQL server 2005, all databases having full recovery mode.
Problem is the Log file size, as datbase recovery mode is full, log file size is increasing daily (some log files size is more than 5GB sometime >200GB)
We are taking full databses backup by using Arc srv SQL agent.
can you please advice how can we mange Log file size of all databses, also if possible, can we automate the process to manage log file size of all databases having recovery mode as full.
May 4, 2011 at 2:33 am
How often are you taking log backups?
Please read through this: http://www.sqlservercentral.com/articles/64582/
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
May 4, 2011 at 3:19 am
Is there any bulk transactions running on the database? Commit the transactions in small chunk and increase the tlog backup frequency may help.
Try to narrow down when exactly issue happen? Provide more details will help you to provide the best resolution.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 4, 2011 at 4:05 am
temp I m using following query to manage DB size (but this is not a proper solution)
========================================
USE Database_name;
GO
– Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Database_name
SET RECOVERY SIMPLE;
GO
– Shrink the truncated log file.
DBCC SHRINKFILE (Database_name_log);
GO
– Reset the database recovery model.
ALTER DATABASE Database_name
SET RECOVERY FULL;
GO
=======================================
We are taking daily full databases backup but not taking backup of transaction log, (dont want to take backup of log file).
I have created database maintenance plan for all DB to shrink databases but it is not affecting on log size.
May 4, 2011 at 4:29 am
SQL_Helper (5/4/2011)
We are taking daily full databases backup but not taking backup of transaction log, (dont want to take backup of log file).
If you are in full recovery model, you have to back the log up on a regular basis, or it will grow until it fills the disk. In fact, the ability to back the log up (for point-in-time restores) is the entire reason for a DB being in full recovery at all.
If you don't want the ability to restore to a point in time, then switch the DB to simple recovery model. If you do want the ability to restore the DB to point-in-time (eg exact time of failure), start taking log backups.
Please read through the article I referenced earlier.
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
May 4, 2011 at 5:09 am
If you db is in FULL recovery model then make sure log backup should be running else log will be growing like anything.and if log backup is not required then change the recovery model into SIMPLE or configure the log backup as per your log file growth duration and keep recovery as FULL.
May 4, 2011 at 5:22 am
You can't do both. If your database is in Full recovery, you have to run log backups. If you don't want to run log backups, you take the database to Simple Recovery. But, in so doing you lose the ability to perform a point in time recovery. I wrote a blog post[/url] on this because the question comes up so often.
"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
May 4, 2011 at 5:27 am
Team thanks;
1- I have configured daily backup of full database and transaction log of all databases
2- Recovery mode is still full
Now please help on following
* as now I m taking daily full backup of database and transaction logs, will it reduce the log size auto or need perform some extra tasks to maintain log file size after bakcup?
* if I select recovery mode as simple for some databses, how can we manage log size?
May 4, 2011 at 5:48 am
Daily is not enough for log backups. The interval between log backups should be the maximum amount of data you are willing to lose in a disaster.
Log backups are generally done hourly, every 30 min, ever 15 min or even more frequently.
You should do a once off shrink of the log file (and only the log file) to a reasonable size (not 1 MB) and then leave it alone.
In simple recovery the log will automatically be reused. Again, do a once-off shrink to a reasonable size and leave it alone.
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
May 4, 2011 at 5:52 am
Whether in Simple or in Full, you need enough space in your log to support the transactions you put through it. Exactly what that size represents is something that you need to determine at your end. You want it to be sized a bit large just to take into account that you may hit a spike in activity or someone may save an extra large set of data. Other than that, in general, you should be able to set it to a particular size and then leave it alone. If you find it grows one day, leave it at the larger size. You have to have enough space.
"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
July 2, 2013 at 9:10 am
What do you mean by once-off?
July 2, 2013 at 9:12 am
What do you mean by once-off?
July 2, 2013 at 9:19 am
Exactly that. Once.
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
July 2, 2013 at 9:23 am
Thanks for quick reply. Does it matter if I shrink at peak or off-peak hour?
July 2, 2013 at 9:30 am
If you don't care about degrading performance for your users, shrink at peak time.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply