February 11, 2009 at 3:31 am
I am trying to figure what stored procs in my DB may not be used anymore (either in a job or from an application)
Is there any way of determining this? I want to remove SP's that are not being executed anymore (ever).
Maybe a way to determine the last execution date of the SP?
Or is there a way to log each time an SP is executed?
Any help will be appreciated
Thanks
February 11, 2009 at 4:12 am
Beyond altering your SPs to include some sort of logging statement, I don't think there's anything simple you can do.
You could set up a Profiler trace to run over a period of time to identify the SPs that *are* being executed, and work backwards from that - the caveat being that performance may suffer a hit (although in theory, if you are running Profiler on a different machine to your DB server, and only selecting the minimum information to be monitored, it shouldn't be a problem)
February 12, 2009 at 2:50 am
mazzz (2/11/2009)
Beyond altering your SPs to include some sort of logging statement, I don't think there's anything simple you can do.
I second that.
mazzz (2/11/2009)
You could set up a Profiler trace to run over a period of time to identify the SPs that *are* being executed, and work backwards from that - the caveat being that performance may suffer a hit (although in theory, if you are running Profiler on a different machine to your DB server, and only selecting the minimum information to be monitored, it shouldn't be a problem)
Another caveat would be that the period of time you trace may need to be very long to make sure you give every store procedure a 'chance' to be run. Do you have month end processes? Any quarterly, year end, financial year end processes? You get the idea.
February 12, 2009 at 2:57 am
February 12, 2009 at 3:22 am
I had to face the same problem some time ago. I tried many solutions, but I couldn't find anything better than insert some logging statements in every procedure. Of course I tried to keep it as simple and concise I could:
First of all I built a new table to keep the log:
CREATE TABLE [dbo].[ProcedureAudit](
[DatabaseName] [varchar](50) NOT NULL,
[SchemaName] [varchar](50) NOT NULL,
[ProcedureName] [varchar](255) NOT NULL,
[FirstRun] [datetime] NULL,
[LastRun] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[DatabaseName] ASC,
[SchemaName] ASC,
[ProcedureName] ASC
)
)
Then I coded a procedure to insert an audit record:
CREATE PROCEDURE [dbo].[sp_Audit_sp]
@DatabaseName varchar(50),
@SchemaName varchar(50) = 'dbo',
@ProcedureName varchar(255)
AS
BEGIN
SET NOCOUNT ON;
UPDATE ProcedureAudit
SET LastRun = GETDATE()
WHERE DatabaseName = @DatabaseName
AND SchemaName = @SchemaName
AND ProcedureName = @ProcedureName
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO ProcedureAudit (
DatabaseName,
SchemaName,
ProcedureName,
FirstRun,
LastRun
)
VALUES (
@DatabaseName,
@SchemaName,
@ProcedureName,
GETDATE(),
GETDATE()
)
END
END
Then I had to insert the call in every procedure in my database. To do this I coded a small VB project, but you could do it any way you like.
You can choose two ways to set the procedure parameters: the first one, obviously, is to hardcode the parameters in the call:
EXEC dbo.sp_Audit_sp 'MyDatabase', 'dbo', 'MyProcedureName'
There's another way to make the call, that would allow you to copy/paste the same code in every procedure of yours. This involves declaring some variables, which maybe makes the code fragment a bit bulky:
DECLARE @dbName varchar(500)
DECLARE @procName varchar(500)
DECLARE @schemaName varchar(500)
SELECT @dbName = DB_NAME(),
@procName = name,
@schemaName = SCHEMA_NAME(schema_id)
FROM sys.objects
WHERE object_id = @@PROCID
EXEC dbo.sp_Audit_sp @dbName, @schemaName, @procName
This is because stored procedures don't allow functions in parameters directly. One thing you could do is to use defaults for omitted parameters in the audit procedure, so that only the procedure name has to be specified.
Hope this fits your needs.
Gianluca
[/code]
-- Gianluca Sartori
February 12, 2009 at 3:30 am
Just one thing to bear in mind is that some stored procedures may not run frequently if you do delete any, make sure you have a copy.
You'd not want to lose any stored procedure that may run a quaterly report, for example.
Also, if possible, do a text search in the codebase.
February 12, 2009 at 3:32 am
Gianluca,
I'd be interested to hear a bit more about the VB project you wrote.
Did it use SQLDMO (or whatever it's called these days), and append text to the StoredProcedure object's text, or something similar?
Thanks
February 12, 2009 at 4:06 am
The VB project makes much more than simply adding the audit call in the procedures, but it implements some business logic tied to an application we wrote that relies on the database, so I think you wouldn't be interested in the whole code.
Basically the program does this:
1. Select the procedures:
SELECT Name = CASE b.name WHEN 'dbo' THEN '' ELSE b.Name + '.' END + A.name
FROM sys.procedures as a
INNER JOIN sys.schemas as b
on a.schema_id = b.schema_id
WHERE is_ms_shipped = 0
ORDER BY a.name
2. Loop through the results and get the procedure code with:
EXEC sp_helptext @procedureName
3. Parse the beginning of the procedure to see if the audit statement is already there: use some kind of placeholder in a comment to do this (e.g. -- *** AUDIT *** --
4. If the placeholder isn't there, put the call in the text and execute it. Since sp_helptext returns a CREATE statement, place a DROP just before the modified statement.
-- Gianluca Sartori
February 12, 2009 at 4:23 am
That's very nice Gianluca
I'll be looking to do some sort of cleanup in the near future (our stored procedure population seems to grow at a rabbit rate...), and this will be very helpful
Many thanks
Maria
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply