SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identifying unused tables and stored procedures since last six month


Identifying unused tables and stored procedures since last six month

Author
Message
hemant789
hemant789
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 135
How to know when the data of tables were viewed in mssql 2000 and mssql 2005? and also when the stored procedures were executed?

Basically I want to know since when tables and stored procedures are not used. Based on this I am going to drop the table and procedure which are not used since 6 months.

Identifying unused tables and stored procedures since last six month.

Please help me.
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69242 Visits: 40917
You can't automatically do it, some decisions have to be made. Also remember a functionality may exist that would use a table in the database, but just hasn't been used by your users YET. arbitrarily dropping tables without reviewing the code of your application is not a responsible way to tackle this kind of issue.

To at least identify potential tables to drop, though,
Here's how i would do it:
restore a database from 6 months ago.
compare the count(*) of all rows in OldDB to CurrentDB. and tables that have the same count potentially are not used (or are lookup tables that never change, like a list statuses,counties or states)

of those tables that had the same count, and also are not lookup tables,
I would examine their results from one of the many "Compare Two Databases" scripts from here on SSC.
those that don't have changes might meet the drop criteria after the application code is reviewed.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95627 Visits: 33013
This won't help you now, but another option available is to collect, and aggregate over time, the procedure calls by running a very lean server-side trace to capture queries run on the database in question. Then you can figure out which procedures are called, which queries are called, and by inference from those procs & queries, which tables are accessed.

But the data isn't built into the system, so you have to find a way to create it for yourself.

Another option for building the data would be regularly capture the information available in the cache by querying the DMV's such as sys.dm_exec_requests or sys.dm_exec_query_stats. You can then aggregate that information into a table somewhere and build up reports over time similar to what you would get from trace. However, since this information is only what is currently in the cache, you may miss queries or procedure calls depending on the frequency of your requests to the DMV and the volatility of the data in your cache.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
hemant789
hemant789
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 135
Thanks for reply.

I don't want to use profiler and create job for getting list of unused tables and stored
procedures.
Is there any other way to get list of tables and sps which are not being used since 6 months in mssql 2000 and mssql 2005?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95627 Visits: 33013
As Lowell and I both said... No.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
hemant789
hemant789
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 135
I got below mentioned query which will give list of unused table since the SQL services started.It is for MSSQL 2005. Is there any way to get list of unused stored procedures?


SELECT DISTINCT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),I.OBJECT_ID
FROM SYS.INDEXES AS I
INNER JOIN SYS.OBJECTS AS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.OBJECT_ID
NOT IN (SELECT DISTINCT I.OBJECT_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S ,SYS.INDEXES AS I
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = DB_ID(db_name()))
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95627 Visits: 33013
You can query the cache to see what procedures are in it and compare that against the list of procedures. However, just because a procedure is not currently in cache doesn't mean it doesn't get used. It just means it hasn't been used recently, but may have just aged out of the cache prior to your query.

If you think it'll help, with very serious caveats in place, I'd try going against sys.dm_exec_procedure_stats. This will give you the object ID and it marks whether or not it's a stored procedure.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
JacekO
JacekO
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1525 Visits: 616
One time I was faced with an old database system and had to figure out which SPs are not being used.
There is no easy and nice way of getting this information so I decided to create my own way of gathering this information.

I created log table which held the names of the stored procedures in my system, number of times used and the time last used. I also created the SP that updated this table whenever called. This SP took one parameter - the name of the SP it was called from and updated the proper record in the log table.

Then the messy part kicks in. Each SP in the database has to be modified to call the log SP. You can do it by hand if your database does not have many SPs or script all SPs and write simple code in VB or C# to modify the sripts to include the call to the log SP.

I don't know if this is going to work for you but I figured a suggestion can not hurt.

The database access was limited to SP calls only so I did not have to deal with the table usage directly.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Steve Jones
Steve Jones
SSC Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: Administrators
Points: 142368 Visits: 19424
A trace is the best way to figure out what's being used, and that won't tell you what isn't being used, but it will help. You do need to run it over time to make sure that you get most procs.

Even then, what's the point? Are you looking to remove procedures? They don't take up much space and don't impact the system if they're not being used.

If it's cleanup, I think you would be better off spending time doing something else. If you have other reasons, you can always rename some procs and see if anything breaks. That way you'll have the code around and can "restore" them if there's an issue.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
chileu17
chileu17
Right there with Babe
Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)

Group: General Forum Members
Points: 779 Visits: 401
I totally agree with Steve on that one. The best way is to set up a trace. About the information about the last six months, I don't think there is a way of getting this information either.
Maybe something that could be interesting for you is the "Enable Trace" option. It by default is set up to obtain DDL statements but you could add some specific events, like sp's execution.
The catch with this option is that you would have to see if you suffer any performance issues. It's not supposed to be as heavy as profiler, it's actually supposed to be a lot lighter but you would have to try it out.
Hope it helped in any way.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search