{}

Our Brands

Welcome to the Schneider Electric Website

Welcome to our website.
Search FAQs
How to Find the Last Execution Time of a SQL Stored Procedure

Article available in these languages: Czech

Issue
When troubleshooting application and database issues, gathering some information regarding the stored procedures
could be very helpful in leading to the resolution or to identifying the cause of problems.

Product
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

Cause
Database maintenance and troubleshooting possible application and database issues.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

Execute the SQL query below, replacing YourSpName with the name of the stored procedure you are interested in, to find the last time a the procedure was executed.

SELECT st.text as SQL,qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%YourSpName%'


Note: contents of the DMVs will clear while restarting the SQL service, so the contents wont always be accurate.

** see attachment containing the SQL script text

Schneider Electric Canada

Attachment(s)
ExecutionTime.docx [11.56 KB]
How to Find the Last Execution Time of a SQL Stored Procedure
Issue
When troubleshooting application and database issues, gathering some information regarding the stored procedures
could be very helpful in leading to the resolution or to identifying the cause of problems.

Product
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

Cause
Database maintenance and troubleshooting possible application and database issues.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

Execute the SQL query below, replacing YourSpName with the name of the stored procedure you are interested in, to find the last time a the procedure was executed.

SELECT st.text as SQL,qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%YourSpName%'


Note:  contents of the DMVs will clear while restarting the SQL service, so the contents wont always be accurate.

** see attachment containing the SQL script text

Schneider Electric Canada

Attachment(s)
ExecutionTime.docx [11.56 KB]
Users group

Discuss this topic with experts

Visit our Community for first-hand insights from experts and peers on this topic and more.
Users group

Discuss this topic with experts

Visit our community and get advice from experts and peers on this topic and more
move-arrow-top
Your browser is out of date and has known security issues.

It also may not display all features of this website or other websites.

Please upgrade your browser to access all of the features of this website.

Latest version for Google Chrome, Mozilla Firefox or Microsoft Edgeis recommended for optimal functionality.
Your browser is out of date and has known security issues.

It also may not display all features of this website or other websites.

Please upgrade your browser to access all of the features of this website.

Latest version for Google Chrome, Mozilla Firefox or Microsoft Edgeis recommended for optimal functionality.