UnUsed Database and Tables finding

SQL Server Administration
1

Hello,
We are planning to upgrade the sql server from 2008 to 2017 which has multiple databases from few years back and don't have idea that really databases are much in use and those tables into databases are used lately or not, if not then we can obsolete the database or tables which ones are not used anymore.
What's the best way we can analysis Unused databases and tables to make decision before we drop it off. (I know we can take it offline for few days).
I tried to google it but some scripts giving different results and also not sure how i can store every day or twice a week running and store somewhere to analysis.

Thanks for your help!

2

The best way is to NOT do two major evolutions at once. Do the migration first, then whittle away at the obsolute stuff.

3

Thanks Jeff.
I like you idea but I think it's not better to migrate clean DB and tables which ones are good ones.

4

In that case (and I know you already know this but have to say it out loud), make sure that you have tested backups that you can restore from, just in case.

5

Thanks Jeff, We will make sure it, it's a good idea.

6 
I am trying to use the script to get unused Stored Procedure reports but having confusion as which one is the best approach as to look into cache or recompilation.
I don't want to run the script in each database and do the analysis.
Is it any way i can include in a one script and it's also gives me Database name, last access, scan or look up or not in cache to make easy for analysis.
Any Idea?

I tried this one but i have to run for each database
   WITH UnUsed (id)
AS
(
SELECT s.object_id
FROM sys.procedures AS s
EXCEPT
SELECT dm.object_id
FROM sys.dm_exec_procedure_stats AS dm
)
SELECT s.name, s.type_desc
FROM UnUsed
JOIN sys.procedures s ON unused.id = s.object_id

I also tried thiso one with hte counts but not sure to make decission

SELECT
DatabaseName = DB_NAME(st.dbid)
,SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid)
,StoredProcedure = OBJECT_NAME(st.objectid,dbid)
,ExecutionCount = MAX(cp.usecounts)
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 cp.objtype = 'proc'
AND DB_NAME(st.dbid) NOT IN ('tempdb', 'msdb', 'master', 'model')
GROUP BY
cp.plan_handle
,DB_NAME(st.dbid)
,OBJECT_SCHEMA_NAME(objectid,st.dbid)
,OBJECT_NAME(objectid,st.dbid)

Anyone has better solution really appreciate it?

Thanks for your help!

7

Please lookup this store procedure: sp_MSforeachdb . The other way of doing it is using powershell.

DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name' 
EXEC sp_MSforeachdb @command
1 Like
8

Thanks Yosiasz.
You meant to add this Stored Proc into my script? How?
When I ran this one it's giving me all the tables listing for each databases.

9

Yes by feeding it your custom script in the command variable. Dont forget to leave the USE ? and then add your script after the use ?

EXEC sp_MSforeachdb 'use ? [Your script here]'

10

Got it, Thanks.
Appreciate Yosiasz for your help!

11

Sure thing. Also another way of doing it is. I will let you figure it out

DECLARE @EmployeeID as nvarchar(256)
DECLARE @Title as nvarchar(50)

DECLARE Employee_Cursor CURSOR FOR  
SELECT LoginID, JobTitle   
FROM AdventureWorks2012.HumanResources.Employee  
WHERE JobTitle = 'Marketing Specialist';  
OPEN Employee_Cursor;  
FETCH NEXT FROM Employee_Cursor INTO @EmployeeID, @Title;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
      Print '   ' + @EmployeeID + '      '+  @Title 
      FETCH NEXT FROM Employee_Cursor INTO @EmployeeID, @Title;  
   END;  
CLOSE Employee_Cursor;  
DEALLOCATE Employee_Cursor;  
GO
12

Thanks, Yosiasz.
I will check it out.
Compare to unused tables, to find unused stored procedures looks tricky and little hard to make the decission.

13

Somehow throwing an error:

EXEC sp_MSforeachdb 'use ?
WITH UnUsed (id)
AS
(
SELECT s.object_id
FROM sys.procedures AS s
EXCEPT
SELECT dm.object_id
FROM sys.dm_exec_procedure_stats AS dm
)
SELECT s.name, s.type_desc
FROM UnUsed
JOIN sys.procedures s ON unused.id = s.object_id'

14

We are unable to see your screen. What is the error

15

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 2

16

Add a ; before the With

17

Yeah, Good Catch.
I totally missed that ones, So Silli!

Thanks for your quick help!
What do you think this sql will be more reliable to make the decision for UnUsed Procedures which this script retrieving UnUsed procedure list for all the Databases?
Another question, i have that how can i make it like:
Database Name Procedure Name in a one query?

18

Just a word of caution again...

If we read the info about sys.dm_exec_procedure_stats in "Books Online"...

Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

That "lifetime" can be measure in mere seconds.

I strongly recommend that you use this method to only determine what has been used for sure. It should not (must not) be used to confirm that a stored procedure is not used.

19

Jeff,
That's very good information and need to keep in mind and completely agreed with you.
Any suggestion, what's the best to method to use then?
Thanks once again for your useful guidelines and help.

20

google this

stored procedure usage history sql server