Capture Stored Procedures

Question

Hi,

How to capture the currently running stored procedures on production server using SQL Server 2005?

Answer 1

There are some tools on my web site that may help you:

beta_lockinfo - http://www.sommarskog.se/sqlutil/beta_lockinfo.html
this will give you a snapshot of what is going on right, including current stored procedure and statement. You need to have at least VIEW SERVER STATE to run it.

sp_sqltrace - http://www.sommarskog.se/sqlutil/sqltrace.html
This tool permits you to run a trace filtered for a certain process for a while (default 10 seconds) and then summarises the data. You need the same permissions as to run Profiler, but everything is executed server-side.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Without Using traces/profiler methods, Is there any queries to find out the which stored procedures are currently running on sql server 2005?

Answer 3

The following forum thread is on the same topic:

How can i know which stored procedure is executed when and by which program?

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 4

Did you look at my beta_lockinfo?

If you don't have VIEW SERVER STATE permission, the answer is plain and simple: No.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Hi,

Determine the Currently Executing

http://blogs.msdn.com/b/taylaf/archive/2010/01/25/determine-the-currently-executing-statement-in-a-long-running-sql-stored-procedure.aspx

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/