How to determine which Stored Procedures are not used? 2

[aalmeida]

All,

I came across the need to clean up some databases, and I'm in need of a Stored Procedure or process that would determine which stored procedures have not been used since the last restart of the server.

Does anyone have anything like this?

AL Almeida
Senior DBA

http://www.ajmedia.net/

"May all those that come behind us, find us faithfull"

 

[JohnTcolo]

This link below should get you started in the right direction:

http://msdn.microsoft.com/en-us/library/cc280701.aspx

 

[mrdenny]

SQL Server does not mark procedures when they are run.

You can use SQL Profiler to monitor what procedures are being run, then load this up into a table in SQL Server and do a NOT EXISTS against the sys.procedures table.

However you will want to run this for days or weeks to make sure that you capture all the commands that are run.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[aalmeida]

mrdenny, Sorry but you are flat wrong on this one, SQL Profiler will not provide you with any information about this.

JohnTcolo, yes that is a starting point, although I was looking for something a little further.

This is what I have done to solve this issue:

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblSprocNotUsed](
[SystemRestartDate] [datetime] NULL,
[ScriptRunDate] [datetime] NULL,
[DBName] [varchar](128) NULL,
[OBJECTID] [int] NULL,
[sproc_name] [varchar](128) NULL,
[run_count] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'cp_unusedsprocs')
BEGIN
DROP Procedure cp_unusedsprocs
END

GO

CREATE PROCEDURE dbo.cp_unusedsprocs
AS
SET NOCOUNT ON

DECLARE @sql VARCHAR(1000)
, @NAME SYSNAME
, @sproc_name SYSNAME
, @SysRestartDate DATETIME

DECLARE cr_dbs CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
FOR READ ONLY

OPEN cr_dbs
FETCH NEXT FROM cr_dbs INTO @NAME

SELECT @SysRestartDate = create_date FROM sys.databases WHERE name = 'tempdb'

IF DATEDIFF(dd, @SysRestartDate, GETDATE())< 2
BEGIN
TRUNCATE TABLE tblSprocNotUsed
END

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = "USE ["+ @NAME +"] INSERT tblSprocNotUsed (SystemRestartDate, ScriptRunDate, DBName, SPROC_NAME, OBJECTID) SELECT '"+cast(@SysRestartDate AS VARCHAR(30)) +"', '"+ CAST(GETDATE()AS VARCHAR(30)) +"', '"+ @NAME +"', o.name, bject_id FROM sys.objects O WHERE type = 'P' AND o.name NOT LIKE 'dt_%' AND o.name NOT LIKE 'cf_%' AND o.name NOT IN (SELECT SPROC_NAME FROM tblSprocNotUsed WHERE DBName = '"+ @NAME +"' )"

EXEC (@sql)

FETCH NEXT FROM cr_dbs INTO @NAME
END

CLOSE cr_dbs
DEALLOCATE cr_dbs

UPDATE tblSprocNotUsed
SET run_count = ISNULL(Execution_count, 0)
, SystemRestartDate = @SysRestartDate
, ScriptRunDate = GETDATE()
FROM (SELECT DB_NAME(st.DBID) DBName
, OBJECT_SCHEMA_NAME(st.objectid,DBID) SchemaName
, OBJECT_NAME(st.objectid,DBID) StoredProcedure
, ISNULL(MAX(cp.usecounts), 0) Execution_count
FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.DBID) IS NOT NULL
AND DB_NAME(st.DBID) NOT IN ('master', 'msdb', 'tempdb')
AND cp.objtype = 'proc'
GROUP BY cp.plan_handle
, DB_NAME(st.DBID)
, OBJECT_SCHEMA_NAME(objectid,st.DBID)
, OBJECT_NAME(objectid,st.DBID)
-- ORDER BY MAX(cp.usecounts)
) C
WHERE tblSprocNotUsed.DBName = C.DBName AND sproc_name = C.StoredProcedure
GO

exec cp_unusedsprocs
GO

SELECT DBName, OBJECTID, sproc_name, ISNULL(run_count, 0) run_count, SystemRestartDate, ScriptRunDate
FROM tblSprocNotUsed
WHERE run_count = 0
GROUP BY DBName, sproc_name, SystemRestartDate, OBJECTID, ScriptRunDate, run_count
ORDER BY DBName, run_count DESC, sproc_name
GO


AL Almeida
Senior DBA

http://www.ajmedia.net/

&quot;May all those that come behind us, find us faithfull&quot;

 

[mrdenny]

SQL Profiler will work for this task for commands run in the future.

Your procedure will work fine, provided that your execution cache has not had to flush data since the server was last restarted. If SQL Server has had to flush execution plans from the execution cache then your procedure will be missing data.

There is no way to guarantee that you have every command issued against the SQL Server unless you are running a trace from the time the SQL Server starts.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JohnBates]

aalmeida,

When it comes to cleaning up objects, I'm a big chicken. I never drop/delete an object unless I'm very sure it is not used.

First, I rename it to a name of 'DELETEactualObjectName'.

After a month or a quarter has passed with no jobs/procedures failing due to "missing object", then I delete it or copy it to a special database that holds deleted objects only.

If a job blows up due to object not found the fix is easy, just rename the object back to the original name.

Overkill? - maybe, but I prefer to avoid these kinds of problems.

John

 

[mutley1]

I'm with John there....the only surefire way is to remove it from live service (but not remove it from the database) and see over a period of time if any jobs go belly up or anyone complains that "stuff just ain't working".

 

[MDXer]

Depending on how many sprocedures you have in question why not build an audit table and a step in the proc to insert the name and date the procedure runs. after a couple months you can look at what is not logged and then take the above approach. But by doing an audit process first you are one step closer to avoiding any failures that may be costly.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[aalmeida]

That worked. Thanks.

AL Almeida
Senior DBA

http://www.ajmedia.net/

&quot;May all those that come behind us, find us faithfull&quot;

 

[JohnBates]

2 messages posted earlier today have been removed. Why?

 

[mrdenny]

You would need to ask the site owners via the contact us link. They are the only ones who can remove posts.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog