SQL Server Profiler To Identify A Fault

[Skittle]

My SQL 2008 Server has suddenly started running out of memory at random times during the day.

The windows application event viewer indicates this via an MSQLSERVER error where I get errors that indicate lack of memory is preventing connections to SQL Server. This clears itself after about an hour and then connections are accepted again.

I can't see anything in the windows event logs that indicates something external to SQL Server itself it causing the problem so I am going to run an SQL Server Profiler all day so when the problem next occurs I have something to look at to see if I can spot the event that triggers the event.

I don't want to create a huge Profiler log file so I would like the log to limit itself in size and overwrite itself when it gets
large. Is there a way to do this? The profiler only seems to allow new files to be created each time a size limit is reached so I can't limit the overall disc space the profiler eats up.

The only alternative is for me to manually deleted old trace files every hour or so.


Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[SQLBill]

There's no way for it to overwrite the existing trace file. However, you can set up a job to delete old files.

Profiler uses up memory, so it might cause you additional memory issues and you may not capture where your real problem is.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Skittle]

Out of interest how would you go about setting up a job to delete the files?
Just a simple scheduled batch job in windows or a SSIS task?

I agree the profiler hits performance but I don't know what else to will identify the problem.
The windows application log shows nothing starting or finishing when the problem occurs and the error
seems to be within SQL Server. Is there another tool or approach I should use?




Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[yelworcm]

I ran into a similar problem a few years back. Turns out the File Server cache was eating enormous amounts of memory, which was not represented in Task Manager. The first sign was the SQL Server crash after it had given away as much memory as it possibly could.

So my question for you is when you get the out of memory errors, how much memory does the SQL Server process hold?