June 24, 2011 at 12:50 pm
I know that this has been addressed before, but in the articles and discussions I've read, I've never exactly gotten the answer that I want. My problem is this:
I have a multiple database application installed in 1000+ sites, using SQL Server 2005 and 2000. Occasionally, our support group will create a temporary user table (select * into table_bkp from table) before they start adjusting values. However, those backup tables sit out there like a time bomb, blowing up upgrades when they occur. I'm charged with improving this portion of the upgrade process - in theory by providing a SQL script in a GUI tool for the upgrade team to use.
I do not have triggers or procedures to note the time changed, much less the last time a SELECT was run (not sure I could ever get that approved as a development change either). I need more history than since the last time SQL Server was started. We only audit certain tables. And, finally, (and stupidly, I might add) nearly none of the tables have change date fields in them.
Does anyone have any suggestions for my situation?
At this point, my best guess is to maintain a rather cumbersome list of "official" tables that I can match off of, but I'm hoping someone will have a better answer for me.
June 24, 2011 at 12:55 pm
run this and see if it gets you the info you are looking for...
if a table has not been acccessed at all, it's not on the list; read the article in the comments for the caveats and when it's really an accurate measure: i believe it's only since the last time the SQL service was started.
--based on the ideas from
--http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx
;WITH ServerStarted AS
(
SELECT
MIN(last_user_seek) AS first_seek,
MIN(last_user_scan) AS first_scan,
MIN(last_user_lookup) AS first_lookup
FROM sys.dm_db_index_usage_stats
),
ServerFirst AS
(
SELECT
CASE
WHEN first_seek < first_scan AND first_seek < first_lookup
THEN first_seek
WHEN first_scan < first_seek AND first_scan < first_lookup
THEN first_scan
ELSE first_lookup
END AS usage_start_date
FROM ServerStarted
),
myCTE AS
(
SELECT
DB_NAME(database_id) AS TheDatabase,
OBJECT_NAME(object_id,database_id) As TheTableName,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
)
SELECT
MIN(ServerFirst.usage_start_date) AS usage_start_date,
x.TheDatabase,
x.TheTableName,
MAX(x.last_read) AS last_read,
MAX(x.last_write) AS last_write
FROM
(
SELECT TheDatabase,TheTableName,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,TheTableName,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,TheTableName,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,TheTableName,NULL, last_user_update FROM myCTE
) AS x
CROSS JOIN ServerFirst
GROUP BY TheDatabase,TheTableName
ORDER BY TheDatabase,TheTableName
Lowell
June 24, 2011 at 1:01 pm
First things first modify model database to include a script to log table creations as well as modifications. The logic could be trigger based or job based doesn't really matter.
Now to check for historical tables that are created .
Chances are if the tables was created as a stand alone table then there will be no dependent objects.
There would be no logins ( application users ) with any kind of privileges on these tables.
if the table was recently used you might find some information in
select * from sys.dm_db_index_usage_stats
June 24, 2011 at 1:04 pm
Lowell, unfortunately, I believe you are correct. I've already looked that blog entry while looking for answers, and this method is accurate only since the SQL Server was last started. This would not be nearly enough time to get an accurate picture.
June 24, 2011 at 1:07 pm
james.massey (6/24/2011)
Lowell, unfortunately, I believe you are correct. I've already looked that blog entry while looking for answers, and this method is accurate only since the SQL Server was last started. This would not be nearly enough time to get an accurate picture.
yeah, that's one of those things you'd have to create a job for and let it pull that info periodically, and stick it into a tracking table;; doesn't help for history beyond that.
you probably have a handful of datetime columns in the database that has a default of getdate(), that at least might get you inserted last time, but not select/accessed.
Lowell
June 24, 2011 at 1:08 pm
Jayanth_Kurup (6/24/2011)
First things first modify model database to include a script to log table creations as well as modifications. The logic could be trigger based or job based doesn't really matter.
I'm already lobbying for that change, we'll see how it goes. 🙂
Jayanth_Kurup (6/24/2011)
Now to check for historical tables that are created .Chances are if the tables was created as a stand alone table then there will be no dependent objects.
There would be no logins ( application users ) with any kind of privileges on these tables.
This is a track I haven't thought of, thanks! I'll have to look into that.
Jayanth_Kurup (6/24/2011)
if the table was recently used you might find some information inselect * from sys.dm_db_index_usage_stats
Correct me if I'm wrong (occurs entirely often), but that only tracks changes and does not pay attention to selects.
June 24, 2011 at 1:11 pm
Lowell (6/24/2011)
you probably have a handful of datetime columns in the database that has a default of getdate(), that at least might get you inserted last time, but not select/accessed.
Sadly, there's not enough of those to help. I'm a big believer in the columns of created_on and modified_on, but the organization I've joined apparently never thought of that previously...
June 24, 2011 at 1:11 pm
June 24, 2011 at 1:13 pm
Jayanth_Kurup (6/24/2011)
selects would show up if the time stamp of last index seek or scan are closer to today
Thanks, I didn't know that, but sounds like that doesn't help my problem based on the "close to today".
June 24, 2011 at 1:14 pm
james.massey (6/24/2011)[hrCorrect me if I'm wrong (occurs entirely often), but that only tracks changes and does not pay attention to selects.
it has the last tiem an index had a SEEK or SCAN against it, which occurs even on selects..and of course on insert/update/delete to find the records as well....so it really is a decent indicator
Lowell
June 24, 2011 at 1:23 pm
Lowell (6/24/2011)
it has the last tiem an index had a SEEK or SCAN against it, which occurs even on selects..and of course on insert/update/delete to find the records as well....so it really is a decent indicator
Right you are. I just tested it. Unfortunately, it resets after you restart SQL Server, so it does not serve my purposes.
June 24, 2011 at 1:26 pm
can you create a job today, pull from that view, and log the changes for 6 months or something?
at least you'd be able to answer better with some time tested logging in the future.
Lowell
June 24, 2011 at 1:28 pm
another thing you could do is restore an old backup and a "today" backup, and compare the tables between the two databases; if any table is not the same as today, you could at least determine that it was insert/update/deleted since that backup...
Lowell
June 24, 2011 at 1:48 pm
Lowell (6/24/2011)
can you create a job today, pull from that view, and log the changes for 6 months or something?at least you'd be able to answer better with some time tested logging in the future.
I could, but this doesn't help me upgrade today, and it's not feasible to access 1000+ clients for the sole purpose of this problem.
Lowell (6/24/2011)
another thing you could do is restore an old backup and a "today" backup, and compare the tables between the two databases; if any table is not the same as today, you could at least determine that it was insert/update/deleted since that backup...
This is possible, but this reduces the "handy utility" aspect, since I'd be sending this to our upgrade team to assist them with upgrades.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply