August 4, 2009 at 12:52 pm
Folks,
One of our QA DB Server is experiencing slowness. This happened after we restored a Production DB copy onto the server. There is no blocking/deadlocking going on the server currently, the queries are all good as they were in Production.
After scrutinizing the SQL Error logs i found the following:-
'Autogrow of file 'AppQA_Log' in database App-QA' took 75937 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file......
I also pulled the Disk Usage reports and expanded the Data/Log Files Autogrow/Autoshrink Events tab and i found several entries relating to 'Log File Auto growth'. It seems like it's trying to auto increment the log file growth every 2 minutes and the operation lasts for about about 33000 ms.
We did not see those entries after we changed the recovery model from 'FULL' to 'SIMPLE'.
From what it looks like it's an AUTOGROWTH issue that is causing the transactions to time out and causing the server slowness. Any ideas, thoughts on this?
Thanks in advance.
Amol
Amol Naik
August 4, 2009 at 1:28 pm
are you backing up the transaction log? If not, set the recovery mode to simple. If it is just in your QA environment you may not need to be backing up the transaction log.
The probability of survival is inversely proportional to the angle of arrival.
August 4, 2009 at 1:39 pm
If its a staging /testing server and no logshipping in place then put DB to simple recovery mode so that it will truncate the log automatically
🙂
August 4, 2009 at 2:35 pm
Yeah we changed the recovery model from FULL to SIMPLE and seems to have made the server perform better. I want a more detailed explanation as to why it occurs.
Thanks for the response.
Amol
Amol Naik
August 4, 2009 at 3:20 pm
The transaction log is what captures all of the changes as they occur to your data. So - you add new rows, and the changes get recorded in the transaction log FIRST then applied to your data. Same idea if you are updating or deleting rows, etc...
In a FULL recovery mode (which allows you to do point in time restores), the changes recorded in the transaction logs are kepts until a. the transaction/operation completes and is committed and b. a transaction log backup is done (and c. the transaction is replicated if that should happen to apply). Once ALL of those occur, then the space inside of the log file is freed up as the log gets "truncated" of the committed items.
In SIMPLE, the change is maintained only long enough to make sure tha the operation finished correctly. It's what is called "auto-truncating".
In your case, your production server is clearly (and appropriately) set to be FULL (since you want the recovery ability). It's not a problem there since the backup schema no doubt includes the log backups (allowing the server to keep reusing the space and not force the log files to grow). On your TEST box however, you apparently are not performing any backups, so the log file just keeps getting bigger.
That's it in a nutshell. Check out TRANSACTION LOGS in book online for more info....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 4, 2009 at 3:32 pm
Thanks for the brilliant explanation Matt. Really appreciate it.
So that means the autogrowth doesn't matter when the recovery model is set to SIMPLE. Is it?
I also want to know if the above two issues that i mentioned in my first post indeed could have been the cause of the server slowness.
Amol Naik
August 4, 2009 at 8:23 pm
Nothing else runs while auto-growth is occurring and, yes, it will make a given task or set of tasks seem very slow while auto-growth occurs.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply