INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Server Profiler To Identify A Fault

SQL Server Profiler To Identify A Fault

(OP)
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'.

RE: SQL Server Profiler To Identify A Fault

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: What should I know before I post?

RE: SQL Server Profiler To Identify A Fault

(OP)
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'.

RE: SQL Server Profiler To Identify A Fault

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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close