July 24, 2014 at 7:41 pm
Hi all
This is my problem details:
07/22/2014 11:24:44,spid74,Unknown,Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 1146 milliseconds.
Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
07/22/2014 11:23:31,spid65,Unknown,Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 1503 milliseconds.
Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
This is my temp db mdf and ldf setting by querying (SELECT * FROM sys.master_files):
name physical_name statestate_descsizemax_size
tempdevF:\MSSQL11.CWSAPPS\MSSQL\Data\tempdb.mdf0ONLINE1024-1
templogF:\MSSQL11.CWSAPPS\MSSQL\Data\templog.ldf0ONLINE64-1
Basically by using SSMS, i can see that my tempdb mdf and ldf details are per below:
tempdb.mdf initial size is currently 440MB, set to 10% of autogrowth and maxsize is set to unlimited
templog.ldf initial size is currently 30MB, set to 10% of autogrowth and maxsize is set to unlimited
I read from this articles but i cant find actually what is the best solution that i need to implement :
http://support.microsoft.com/kb/2091024
Am i supposed to set my mdf and ldf not to autogrowth? And if i did this, what is the propose size that suit for most tempdb mdf and ldf size?
July 25, 2014 at 12:16 am
tempdev is the data file for tempdb. It's timing out after a very short time, 1.5seconds for 10% growth, around 44MB.
You should really set the growth increments to a size in MB rather than 10% and increase tempdev a few hundred MB (if not more).
Also, if there are no security concerns with being able to read old deleted data on disk, turn on Instant File Initialization which will instantly increase the MDF size (not the log file, but you dont have a problem there.) That will solve this issue (after reboot) without any other changes, but it is generally accepted to use set size MB growth intervals.
The log growth should also be set to MB growth. Based on it's current size, you could increase it to 128MB with 64MB growth but keep an eye on it. If it grows, increase the initial size.
July 25, 2014 at 8:58 am
adzuanamir (7/24/2014)
Hi allThis is my problem details:
07/22/2014 11:24:44,spid74,Unknown,Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 1146 milliseconds.
Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
07/22/2014 11:23:31,spid65,Unknown,Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 1503 milliseconds.
Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
This is my temp db mdf and ldf setting by querying (SELECT * FROM sys.master_files):
name physical_name statestate_descsizemax_size
tempdevF:\MSSQL11.CWSAPPS\MSSQL\Data\tempdb.mdf0ONLINE1024-1
templogF:\MSSQL11.CWSAPPS\MSSQL\Data\templog.ldf0ONLINE64-1
Basically by using SSMS, i can see that my tempdb mdf and ldf details are per below:
tempdb.mdf initial size is currently 440MB, set to 10% of autogrowth and maxsize is set to unlimited
templog.ldf initial size is currently 30MB, set to 10% of autogrowth and maxsize is set to unlimited
I read from this articles but i cant find actually what is the best solution that i need to implement :
http://support.microsoft.com/kb/2091024
Am i supposed to set my mdf and ldf not to autogrowth? And if i did this, what is the propose size that suit for most tempdb mdf and ldf size?
Here are a few articles that wil help you. Never restrict tempdb.
http://www.brentozar.com/sql/tempdb-performance-and-configuration/
http://www.confio.com/logicalread/sql-server-tempdb-best-practices-initial-sizing-w01/
MCSE SQL Server 2012\2014\2016
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply