stored procedure edit - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure edit

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-14 : 00:52:43

We have so many stored procedures which we have no idea if they are still being used, so what i thought to do for now (other then using profiler) was to write a small script which took a few minutes to write... that adds a small tag to each sp whcih will add a timestamp value in a table i create...

the problem is this script isnt working, and id like to get it to work, even if we dont use it (if there is a better solution) just for knowledge purpose, i got stuck at this bit:

SET QUOTED_IDENTIFIER OFF
GO

create table #oldproc
(
id int IDENTITY,
text varchar(4000)
)

declare @text varchar(4000),@tsql nvarchar(4000)

INSERT INTO #oldproc
exec SP_HELPTEXT last_used

select @text = isnull(@text + ' ', '') + text
from #oldproc

select @text=replace(@text,"'","''")

-- insert the variable you would like to add to end of procedures
select @text=@text+'

select * from systables'
set @text = "'"+@text+"'"

set @tsql='ALTER PROCEDURE last_used
AS
print'+@text

--print @text
exec (@tsql)

exec SP_HELPTEXT last_used

drop table #oldproc

Kristen
Test

22859 Posts

Posted - 2007-02-14 : 13:23:57

I think you would be better off scripting out the Sprocs and then using a "Programmers Editor" to make the change globally, which you can then check, test on a DEV database, and then run on Production.

Altering the Sprocs and re-applying them AutoMagically strikes me as a recipe for problems!

Note that SP_HELPTEXT [under SQL 200 at least] will not preserve the linebreaks of the original Sproc correctly if the Sproc is "lengthy" (probably 8,000 characters, but I'm not sure)

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-14 : 18:11:31

okay i will do that, once again im very thank ful, and guys congrats im noticing quite a few more views on this site (might just be me), keep it up :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-14 : 18:57:25

I don't think what you are planning sounds like a good idea.

You are creating one table that will be used in all stored procedure calls, and making it a hot spot. If you have any poorly handled transactions or poor error handling, you will end up with massive blocking and deadlocks, and lots of unhappy users.

CODO ERGO SUM

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-14 : 19:09:23

we might try it briefly as a trial..

any other suggestions on finding the useless from usefull sps?

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-14 : 21:49:40

what i might actually do is just have the proc email me as soon as its executed...

rather then entering a table

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-14 : 23:44:04

would there be a way to put the generated scripts back in db, im using

bcp query out to put them into files, (but thats adding spaces which is irritating)

Kristen
Test

22859 Posts

Posted - 2007-02-15 : 04:00:14

"I don't think what you are planning sounds like a good idea."

I think its a fair idea, but I'd add a caveat:

which is to remove the "tracking code" from SProcs that are recorded as being "used", so that the code will in effect only be left in the Sprocs that are unused.

After a while (a month perhaps?) I would then rename the remaining SProcs - I would prefix them with "NU_yymmdd_" ("Not Used") so that they were all grouped alphabetically, and the date will tell you WHEN they were renamed to "NU"

If someone then "screams" that an Sproc is missing it will be simple enough to rename it back again.

In a month / quarter / year / decade

you can physically delete them as unused. If you need to do this again you will be using a different "yymmdd" of course, so that will start another "safety period"

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-15 : 09:18:32

I would start by using Profiler to find stored procedures that are being used. You could eliminate most or all of the most commonly used procedures and narrow the list down before you start looking at the ones you do not find in the trace output. Run a number of traces at different times of the day, days of the week, end of month, etc.

If you must log proc calls to a table, make sure it only does it once, and avoid doing it at all if there is an open transaction. Better yet, create one table for each stored procedure you want to monitor to avoid contention with other processes.

I would not use xp_sendmail. Too many things can go wrong with that. Your exchange admin may be irritated when you discover that a proc is called 50,000 times in a loop.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-02-15 : 12:13:27

"You could eliminate most or all of the most commonly used procedures"

Good plan!

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-15 : 17:54:37

would it be safe to leave profiler run for long periods of time? my concern is the overhead

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-15 : 18:28:05

im just trying several methods here but does anyone know other ways to generate scripts in tsql? other then the SP_HELPTEXT which seems to be double spacing everything

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-15 : 23:38:58

im using:

SET @CmdStr = 'Connect('+@ServerName+')'
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

--For login purpose (OLE setting)
EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE

--Script SP to file (OLE setting)
EXEC @hr = sp_OAMethod @object,@CmdStr
SET @CmdStr = 'Databases("' + @dbtouse + '").StoredProcedures("' + @procname + '").Script(5,"c:\minabackup\original_'+ @procname +'.sql")'
EXEC @hr = sp_OAMethod @object, @CmdStr
EXEC @hr = sp_OADestroy @object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

to dump stored proc to file.. but its adding :

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

at the end which is making it impossible to append to proc... any way i can get it to dump without this?

Kristen
Test

22859 Posts

Posted - 2007-02-16 : 08:19:26

"my concern is the overhead"

You should run Profiler on your workstation, NOT on the server itself. It should be OK to run for an extended period, particularly if you have a reasonably restrictive filter set up

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-18 : 18:28:39

okay so ill be doing this in two stages:

1) profiler
2) script create with your help earlier...

now for the profiler , we are only after those procedures we created and run so these are the filters set:

- dbid >=5
- loginname not like '%system%'

the only other thing im worried about is there is no , isMschipped = 0 field (as i dont want to monitor ms procedures)

under stored procedures ive ticked:

- RPC starting
- SP Starting

any input on these options would be great?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources