October 30, 2010 at 4:52 am
Dear friends,
Can any one tell me, How to restrict log file size -sql server 2005 and my log file size is 20 GB. Any fixed log file size ?
Urgently require !
regards,
T.Loganathan
October 30, 2010 at 4:57 am
Remove auto grow option. This will restrict the Log file growth.
Regards,
Sandesh Segu
Regards,
Sandesh Segu
http://www.SansSQL.com
October 30, 2010 at 10:14 am
The more important question to ask is, why is your log growing? Do you have a database in Full Recovery mode, but you're not running backups? Do you have log backups set up, but they're failing? Do you have really, really large transactions?
Before you just turn off auto-grow, you're going to need to manage the log and log processes. If you just turn off auto-grow, your log is likely to fill and then your system will be offline because it can't process any more transactions.
"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
October 30, 2010 at 10:37 am
Please read through this - Managing Transaction Logs[/url]
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
October 30, 2010 at 10:54 am
Sandesh Segu (10/30/2010)
Remove auto grow option. This will restrict the Log file growth.
However if you do that and the log runs out of space, the database will go readonly until something is done. Not usually a good idea unless you know EXACTLY how large the log needs to be and that your database maintenance is perfect.
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 1, 2010 at 9:18 am
@ t loganatham
What is the recovery model for the database?
What was the last time you perfomed a backup of your transaction logs.?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 2, 2010 at 3:21 am
The best way to strict the log file groeth is to take the transaction log backups regularly
November 3, 2010 at 9:31 am
If you are taking regular transaction log backups, you may want to increase the frequency. If backups are running hourly, change them to every 30 or even 15 minutes if necessary.
For upkeep, monitor the size of the log after the change to get an idea of how big it likes to grow. Set the initial size around that number and set the growth to a reasonable portion of that size. I wouldn't cap the growth unless you are concerned with filling the drive space. Keep monitoring the growth and set up alerts for specific thresholds.
But if you choose to ignore all of the advice offered in the responses to your question, you can use the following script to grow the log by 4GB each time and restrict the log size to 40GB (as an example):
USE [master]
GO
ALTER DATABASE [<dbname>] MODIFY FILE (NAME = N'<logfilename>', MAXSIZE = 40960MB , FILEGROWTH = 4096MB)
GO
November 4, 2010 at 8:18 am
I hope you are aware that if you restrict the log size, and you fill it up, then all DML activity will cease against the database.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 8, 2010 at 3:01 am
Dear Friend,
My DB recovery model is FULL and I am taking full backup on every day and transfer the backup to other server ( for Precaution)
My question is How to redusce the log file growth ?
Is auto grow should be disabled ! if so, any problem will arises in the DB
My log file size is 21 GB currently
Send reply ASAP.
regrs
T.Loganathan
my email id is logu.t@nic.in
November 8, 2010 at 3:06 am
T.LOGANATHAN (11/8/2010)
Dear Friend,My DB recovery model is FULL and I am taking full backup on every day and transfer the backup to other server ( for Precaution)
My question is How to redusce the log file growth ?
Is auto grow should be disabled ! if so, any problem will arises in the DB
My log file size is 21 GB currently
Send reply ASAP.
regrs
T.Loganathan
my email id is logu.t@nic.in
LOGANATHAN
Did you read the Gail's article ?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 8, 2010 at 3:13 am
Did you read the previous replay ?
Did you perform log backups?
Did you face any disk space problem?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 8, 2010 at 3:25 am
T.LOGANATHAN (11/8/2010)
My DB recovery model is FULL and I am taking full backup on every day and transfer the backup to other server ( for Precaution)
That's not sufficient in full recovery.
My question is How to redusce the log file growth ?
Please read through this - Managing Transaction Logs[/url]
Is auto grow should be disabled !
No! If you do then when (not if) your log hits that limit, all data modifications will fail.
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply