June 16, 2011 at 8:51 am
Expertrs,
Is it possible to to find Execution of a particular SP needs more memory (..than the max available memory for SQL server) ?
Because Mostly the execution of that procedure fails with error "Request timed out.."
I suspect memory is the culprit as the memory is very less and I have optimised that SP as much as possible.
It's in SQL Server 2005 32 Bit.
Thanks in advance.
June 16, 2011 at 9:03 am
When you have a real memory error in SQL Server's side you get an error in the sql error logs.
CREATE TABLE #logs (LogDate DATETIME, ProcessInfo VARCHAR(50), Txt VARCHAR(MAX))
INSERT INTO #logs (LogDate, ProcessInfo, Txt)
EXEC master.sys.xp_readerrorlog 0,1 --active log. Switch 2nd parameter to 2 for agent log, then increase the 1st parameter for older logs
SELECT * FROM #logs
WHERE LOWER(Txt) LIKE '%memory%' --I'm on case sensitive server
ORDER BY LogDate DESC
Here are a few sample errors (I was completely trashing the servers with massive queries) :
There is insufficient system memory to run this query.
Process physical/virtual memory pressure: 1/0 System physical memory pressure: 0
Edit : If you don't have anything like that in the log then it's probably just the client application timing out or erroring out because of the local pc's limits.
The solution is the optimize the query or streamline the process.
June 16, 2011 at 9:30 am
Hi,
Thanks for the quick response.
I executed your script and got the below single record...
----------------------------------
2011-05-08 20:44:33.940
Server
Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
----------------------------------
Thanks.
June 16, 2011 at 9:34 am
Did you run it for all 5-6 logs?
Maybe you have something else there.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply