Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Find tables unused since last SQL Server restart Expand / Collapse
Author
Message
Posted Thursday, January 28, 2010 7:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:30 PM
Points: 182, Visits: 145
Comments posted to this topic are about the item Find tables unused since last SQL Server restart


Ryan
Post #855714
Posted Thursday, January 28, 2010 10:25 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 41,530, Visits: 34,446
It's not since the last SQL start, it's since the last database start. If the DB is closed, taken offline, detached or restored, those stats are also cleared. Watch out for DBs set to autoclose


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #855767
Posted Tuesday, February 16, 2010 6:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:04 PM
Points: 73, Visits: 234
I’m trying to run this t-sql but it gives me this error:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ' '.
Post #866046
Posted Tuesday, February 16, 2010 7:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:56 AM
Points: 566, Visits: 1,582
Same error here.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible.
Post #866104
Posted Tuesday, February 16, 2010 7:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 41,530, Visits: 34,446
It looks like there are some non-printable characters that aren't spaces but look like spaces. Edit the whole thing onto a single line then re-format. Seems to work


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #866109
Posted Tuesday, February 16, 2010 7:45 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:56 AM
Points: 566, Visits: 1,582
Using http://extras.sqlservercentral.com/prettifier/prettifier.aspx


WITH LastActivity (ObjectID, LastAction) AS
(
SELECT OBJECT_ID AS TableName,
last_user_seek AS LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = DB_ID(DB_NAME())
UNION
SELECT OBJ
ECT_ID AS TableName,
last_user_scan AS LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = DB_ID(DB_NAME())
UNION
SELECT OBJECT_ID AS TableName,
last_user_lookup AS LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = DB_ID(DB_NAME())
)
SELECT OBJECT_NAME(so.OBJECT_ID) AS TableName,
MAX(la.LastAction) AS LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
ON so.OBJECT_ID = la.ObjectID
WHERE so.TYPE = 'U'
AND so.OBJECT_ID > 100
GROUP BY OBJECT_NAME(so.OBJECT_ID)
ORDER BY OBJECT_NAME(so.OBJECT_ID)



-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible.
Post #866122
Posted Tuesday, February 16, 2010 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 2:51 PM
Points: 2, Visits: 25
Is there a version of this script that would show the last time a SP was executed?? I'm really in need of that one :)
Post #866129
Posted Tuesday, February 16, 2010 12:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:04 PM
Points: 73, Visits: 234
Thanks!!!

Post #866308
Posted Tuesday, February 16, 2010 3:04 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:30 PM
Points: 182, Visits: 145
Thanks for re-formatting the query - I'll see if I can get that fixed on the posted version of the script.

As far as Stored procs, it looks like you can do the same kind of thing as long as it's still in the cache, but not if the server has been restarted or the cache flushed since it was last run (much as with this script):

SELECT OBJECT_NAME(sys.dm_exec_sql_text.objectid),
sys.dm_exec_query_stats.*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_query_stats.sql_handle)
WHERE sys.dm_exec_sql_text.dbid = db_id()
AND OBJECT_NAME(sys.dm_exec_sql_text.objectid) = 'yoursp'



Ryan
Post #866499
Posted Tuesday, February 16, 2010 3:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:38 PM
Points: 22, Visits: 158
copy the script onto Notepad. Remove all those tiny rectangular shapes (which were interpreted as spaces which cause errors), then copy the whole script back onto SSMS, parse again. You should get no error this time.
Post #866506
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse