November 18, 2008 at 1:37 pm
Hi,
I'm getting this error in one of my prod SQL Server 2005 64bit servers with 32GB RAM out of which I have allocated 27GB to SQL Server.
Error: 8645, Severity: 17, State: 1
A time out occurred while waiting for memory resources to execute the query. Rerun the query.
Also seeing atleast 2 SQLDump000.mdmp files
I know that when we have more .mdmp files its a sign of memory crunch on the server and the server needs a restart.
I would appreciate if anyone could share few more thoughts on this?
Regards,
Razi, M.
http://questivity.com/it-training.html
November 18, 2008 at 1:55 pm
Per MS: http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/diag/part3/75528c13.mspx?mfr=true
that is a result of:
1. Poor indexing.
2. High load.
Fast workaround would be to change a default value of QueryWait on server Advanced Properties from -1 to a number of seconds for query to wait for execution.
That would be just a fast and dirty temp solution, since you would need to investigate what is causing a problem.
November 18, 2008 at 9:55 pm
Hi,
Thanks for the reply, I checked the link and it is for SQL server 7 which im not sure will hold good for 2005.
I noticed onething on the server that sometimes the Avg. Disk Que length on the SAN which has tempdb was over 2.000 it should be less that 0.02(avg)... but we have lot of adhoc queries running from the application side which are sometimes resource intensive. Please note that the same set of queries were running very good till yesterday.
Sincerely,
Razi, M.
http://questivity.com/it-training.html
November 19, 2008 at 5:08 am
It wasn't so much about SQL 7 as about using execute instead of sp_executesql.
Running multiple ad hoc queries, you have to make sure that dynamic sql is used properly...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply