<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
Looking to select last time tables were...
14 posts, Page 1 of 2
1
2
»»
Looking to select last time tables were queried
Rate Topic
Display Mode
Topic Options
Author
Message
dndaughtery
dndaughtery
Posted Friday, February 22, 2013 1:58 PM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:52 PM
Points: 234,
Visits: 515
I need a query to show all tables in a database sorted by last datetime queried ordered oldest first. Can someone help me with this?
Post #1423256
Sean Lange
Sean Lange
Posted Friday, February 22, 2013 2:04 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Friday, May 17, 2013 8:46 AM
Points: 8,547,
Visits: 8,204
dndaughtery (2/22/2013)
I need a query to show all tables in a database sorted by last datetime queried ordered oldest first. Can someone help me with this?
There is nothing by default in sql that tracks when tables are accessed.
Are you trying to find tables are likely to be no longer needed? This kind of thing can be really tricky because there are usually processes that run only once a year but are vital.
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1423261
dndaughtery
dndaughtery
Posted Friday, February 22, 2013 2:07 PM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:52 PM
Points: 234,
Visits: 515
Yes Sean thats exactly what m trying to do. Trying to find out which table can be dropped.
Post #1423262
dndaughtery
dndaughtery
Posted Friday, February 22, 2013 2:10 PM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:52 PM
Points: 234,
Visits: 515
But Im needing to do it on a weekly basis
Post #1423263
Sean Lange
Sean Lange
Posted Friday, February 22, 2013 2:22 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Friday, May 17, 2013 8:46 AM
Points: 8,547,
Visits: 8,204
How are you going to handle those once a year type of things? I can understand wanting to do this periodically but weekly? Are you really going to drop tables if they haven't been accessed within the last week? What about times when overall activity is low, like the holidays? This should not be an automated type of process. It requires manual intervention or you will end up dropping something that is needed.
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1423266
dndaughtery
dndaughtery
Posted Friday, February 22, 2013 2:27 PM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:52 PM
Points: 234,
Visits: 515
This is for an adhoc enviroment for report development.
Post #1423269
dndaughtery
dndaughtery
Posted Friday, February 22, 2013 2:55 PM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:52 PM
Points: 234,
Visits: 515
Found it!
USE MyDB
GO
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
where OBJECT_SCHEMA_NAME([object_id]) = 's012sod'
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY last_read asc;
Post #1423274
Sean Lange
Sean Lange
Posted Monday, February 25, 2013 7:10 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Friday, May 17, 2013 8:46 AM
Points: 8,547,
Visits: 8,204
dndaughtery (2/22/2013)
Found it!
Be careful now. That only shows index usage. If your queries are not sargable or you have a heap your results may not be exactly what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1423602
dndaughtery
dndaughtery
Posted Monday, February 25, 2013 8:01 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:52 PM
Points: 234,
Visits: 515
How can a correct that?
Post #1423623
GilaMonster
GilaMonster
Posted Monday, February 25, 2013 8:07 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 37,651,
Visits: 29,905
This kind of thing is incredibly difficult to do.
Probably best thing you can do is set up an extended events session for Object accessed (I think it's in the 2008 XE), and put that to a event bucketizer target (bucketize on the object id). Watch the overhead though, that's a frequent event
Now that won't tell you what tables aren't used, it will tell you which tables are known to be used. Run that for long enough (where long enough covers an entire business cycle) and you can use that as a place to start for deciding which tables are not known to be used. Doesn't mean they aren't, just means they might not be,
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 #1423628
« Prev Topic
|
Next Topic »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
