SQL Server Locks
-
I'm not sure if I am posting this question in the correct forum...but I am having a periodic problem with locks on a database. The database is SQL Server 2000, and it is the database to an application we purchased. A BIG application. What is happening is periodically a user will run a query that causes a lock on the database for 10-15 minutes. (Enough time for the help desk to get flooded with calls because 'the app. stopped working'.) Anyway, I called Microsoft to see if they can help me track down either the sql statement running or the user (I.P. address). They want me to run a diagnostic tool constantly, which is a little concerning on a production server.
Before I run the diagnostic tool, I would like to get an idea of how often locks are happening, and for how long. Does anyone know how I can monitor this without constantly watching the server?
Thanks!
-
To get this information, you'll need to either physically watch the server or run some kind of monitoring / diagnostic software, right? Either that, or work from log files. Why not try what MS suggests? What tool do they want you to run? SQL Profiler? If so, you should be able to run that without much impact on your production server, as long as you store the results someplace else (not into the same database server, that is).
Chris
-
From experience, in such situations MS usually request that you follow their suggested techniques for detecting blocking. If that's what they've suggested, then follow their advice. It often has good results and is hardly intrusive.
Cheers,
- Mark -
create SQL job, select count (*) from sysprocesses where blocked 0, if not 0, send you an Email. This may help you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply