October 10, 2014 at 3:44 am
Hi - I'm currently investigating performance issues for an application running on a sql2008 back end database. As part of my investigations I have checked the data file size and the trn log size.
The data file size is 9.6gb & the trn log file is 14.5 gb. The Recovery model for the DB is set to simple & database file settings are as below:
Data file: initial size 9449 (MB), Autogrowth by 10 percent unrestricted growth
Log File: Initial size 14199 (MB), Autogrowth by 10 percent unrestricted growth
I have taken over this database from someone else with these settings in place. I'm assuming this could be the cause of the performance issues being experienced. I would be really greatful if someone can advise me on the correct steps to take, as I don't want to make the situation worse or put the database at risk. I understand creating a new log file is an extremely risky approach to take and could result in destroying a database altogether, so certainly won't be going down that route.
I look forward to any responses.
Many thanks
Dax
October 10, 2014 at 3:57 am
Large log file will not be the cause of performance problems. Look elsewhere for your performance issues.
Managing transaction logs: http://www.sqlservercentral.com/articles/Administration/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
October 10, 2014 at 4:10 am
GilaMonster (10/10/2014)
Large log file will not be the cause of performance problems. Look elsewhere for your performance issues.Managing transaction logs: http://www.sqlservercentral.com/articles/Administration/64582/
What if the log file has to grown continuously? Or when it is on a very slow disk?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 4:12 am
Hi - Many thanks for the prompt reply. What I get any benefit from reducing the size of the trn log then?
Regards
Dax
October 10, 2014 at 4:14 am
Koen Verbeeck (10/10/2014)
GilaMonster (10/10/2014)
Large log file will not be the cause of performance problems. Look elsewhere for your performance issues.Managing transaction logs: http://www.sqlservercentral.com/articles/Administration/64582/
What if the log file has to grown continuously? Or when it is on a very slow disk?
I said that a *large* log won't be the cause of performance problems, not that the log can't have performance implications if mis-configured (eg 1MB growth increments) or on slow storage.
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 10, 2014 at 4:28 am
Based on the information I've already provided, would you expect the log to cause an issue? Or would you recommend reducing the size. Apologies I'm still getting my head around the transaction log & just want to ensure any action I take is deemed as 'Good Practice'.
Regards
Dax
October 10, 2014 at 4:36 am
I'd recommend monitoring for a few weeks, see how much of that log is ever used. You can use DBCC SQLPERF(LOGSPACE) for that. With the DB in simple recovery, you'll need to log that every 5 min or so, so set up a job.
Once you know what the maximum amount of log that's used is, you can do a once-off shrink of the log to that value + some head room. This is cleanup, not for performance.
Then fix the growth increments, % growth is never recommended for data or log files because at small sizes the files grow often, at large size the files grow by huge amounts
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 10, 2014 at 1:11 pm
Is this a production database ? If so, is "simple" recovery acceptable from a disaster recovery perspective ?
October 20, 2014 at 3:13 am
Hi - Yes this is a production environment & I already had a discussion with the user base who are agreeable to DR options. Thanks for asking though!
Does anyone know of any scripts I can use to check any potential performance issues? I have my colleagues investigating the network/cabling and would like to rule out any DB issues.
Any help on this would be very much appreciated.
Thanks to everyone so far who has posted.
Regards
Dax
October 20, 2014 at 3:45 am
Hi - Many thanks for that, have just manually run the DBCC SQLPERF, see results below:
DB Name Log Size(MB) Log Space Used(%)
eXpress 14198.8 0.5367694
I will of course setup a job to monitor every 5 mins as suggested. You mention sorting out the percentage usage and shrinking the log. How would you suggest doing this without impacting on the DB?
Again thanks for your help.
Regards
Dax
October 20, 2014 at 4:09 am
Shrinking the log shouldn't, by itself, cause performance problems.
Once you've monitored for a while (few days, week), run some queries on the saved data, see what the max used space of the log is (the SQLPerf output gives you the total file size and % of file used. That'll give you a starting point on deciding whether to shrink and if so, to what size.
As for performance, take a look through this: http://www.red-gate.com/community/books/accidental-dba. Also Grant's book on performance tuning.
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply