June 3, 2012 at 8:19 am
Log file size is increased to 3 GB in production. Can any one suggest me how to decrease log file size and
what are the possible reasons for log file size increase?
June 3, 2012 at 8:32 am
What recovery model are you using ... Simple or Full ..?
How often are you backing up the database?
June 3, 2012 at 9:17 am
Reasons for log file size increase? One would be that you are in the Full Recovery model, and the transaction log will grow without a log backup because the log portion won't be marked as reusable until the backup, therefore causing file growth.
June 3, 2012 at 9:45 am
Performing backup of complete database monthly 2 or 3 times.
Also hosting team will take regular backups of database. But i don't know which recovery model they are using.
Currently performance of database is slow.
June 3, 2012 at 11:36 am
banda.sai (6/3/2012)
Performing backup of complete database monthly 2 or 3 times.Also hosting team will take regular backups of database. But i don't know which recovery model they are using.
Currently performance of database is slow.
The first step is definitely finding out what recovery model the database is using. As for slow database performance...that could be a lot.
June 3, 2012 at 11:46 am
Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Performance is not going to be related to the size of the log.
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
June 3, 2012 at 2:49 pm
GilaMonster (6/3/2012)
Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/Performance is not going to be related to the size of the log.
Wouldn't performance would be degraded if the transaction log is often growing or growing with large growth? I know what you're saying, Gail, that the literal idea of the actual file size of the log has no impact on performance, but those directly related aspects do. Just thought it was worth clarifying for the OP.
June 3, 2012 at 3:08 pm
Thomas Stringer (6/3/2012)
Wouldn't performance would be degraded if the transaction log is often growing or growing with large growth?
If the log is actively growing, data modifications have to wait, selects will be unaffected.
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
June 3, 2012 at 3:09 pm
GilaMonster (6/3/2012)
Thomas Stringer (6/3/2012)
Wouldn't performance would be degraded if the transaction log is often growing or growing with large growth?If the log is actively growing, data modifications have to wait, selects will be unaffected.
Right, of course. I should have specified DML. Thanks for the clarification, Gail.
June 3, 2012 at 3:22 pm
Thomas Stringer (6/3/2012)
GilaMonster (6/3/2012)
Thomas Stringer (6/3/2012)
Wouldn't performance would be degraded if the transaction log is often growing or growing with large growth?If the log is actively growing, data modifications have to wait, selects will be unaffected.
Right, of course. I should have specified DML. Thanks for the clarification, Gail.
SELECT is a DML (Data Manipulation Language) statement. Data modifications and DDL can be slowed if the log grows but not selects
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
June 3, 2012 at 3:43 pm
Ok, I think triggers have narrowed my definition of DML, but you are right.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply