Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-11-21 : 17:48:18
|
Hi guys, Need help, how i can get the list of SP in my Database that were not accessed in the last 3 Month?Please advise.Thank You. |
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-11-21 : 19:32:14
|
I am trying to run below queryselect DB_NAME(database_id) as "database_name" , OBJECT_NAME(object_id, database_id) as "procedure_name" , last_execution_timefrom sys.dm_exec_procedure_stats WHERE DB_NAME(database_id) = 'MYDB' ORDER BY last_execution_time DESC Question = Which time i am receving " last_execution_time" Most of the SP time saying todays date. Please guide me.Thank You. |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-11-21 : 22:32:56
|
Hi Sonu,Run the following sql to find the last execution date.SELECT p.* FROM sys.procedures AS p LEFT JOIN sys.dm_exec_procedure_stats AS s ON s.[object_id] = p.[object_id] WHERE s.object_id IS NULL;Thanks,Sri. |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-11-21 : 23:24:04
|
Hi Srimami, Thank you for your reply. Modified Date is my last execution date? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-22 : 00:28:25
|
quote: Originally posted by Sonu619 Hi Srimami, Thank you for your reply. Modified Date is my last execution date?
Modified_Date is the date when you modified(i.e. ALTER) your procedure.You can get last_execution_time from sys.dm_exec_procedure_stats--Chandu |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-11-22 : 00:46:09
|
If i change my query to Below Query, I am getting S.Last_Execution_Time is Null..SELECT p.*, S.Last_Execution_TimeFROM sys.procedures AS pLEFT JOIN sys.dm_exec_procedure_stats AS s ON s.[object_id] = p.[object_id]WHERE s.object_id IS NULL;and If i run Below QueryselectDB_NAME(database_id) as "database_name", OBJECT_NAME(object_id, database_id) as "procedure_name", last_execution_timefromsys.dm_exec_procedure_statsWHERE DB_NAME(database_id) = 'MYDB'ORDER BY last_execution_time DESC I am getting only Novembers data.Mean 80% of SP last_execution_time is November. I have more than 2000 SP and i am only seeing those who has last_execution_time in November. Any advice???? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-22 : 01:22:39
|
[code]-- Get list of all SPs in the current database (SQL 2005 and 2008) SELECT p.name AS 'SP Name', p.create_date, p.modify_date FROM sys.procedures AS p WHERE p.is_ms_shipped = 0 ORDER BY p.name; -- Get list of possibly unused SPs (SQL 2008 only) SELECT p.name AS 'SP Name' -- Get list of all SPs in the current database FROM sys.procedures AS p WHERE p.is_ms_shipped = 0 EXCEPT SELECT p.name AS 'SP Name' -- Get list of all SPs from the current database that are in the procedure cache FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id WHERE p.is_ms_shipped = 0;[/code]You can get only Last Modified date for unused procedures....For recently used procedures last_execution_date is available in cache procedure sys.dm_exec_procedure_stats--Chandu |
|
|
|
|
|
|
|