March 22, 2011 at 10:36 am
I need to find out when is the last time some of my databases were used.What is the best way to find out? Thank you
March 22, 2011 at 10:44 am
Do you mean the last time somebody logged into the database, or the last time somebody inserted or updated records in the database?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2011 at 10:44 am
you posted the same question in a SQL 2000 forum here:
http://www.sqlservercentral.com/Forums/Topic1080006-5-1.aspx
in that thread I had posted a link to aaron bartrands blog on how to read the DMV's to find out the last time since the server was restarted; something that works great in SQL 2005/2008
did that script not work for you?
Lowell
March 22, 2011 at 10:47 am
Oh thank you, for some reason never saw replied I will check
March 22, 2011 at 10:54 am
Ok, so do we need to know the last time the server was restarted or do we need to know the last time somebody accessed a database?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2011 at 11:02 am
I am interested to know last time a database was accessed Pleas.e
March 22, 2011 at 11:11 am
Based on that blog by Aaron, I put the script below together.
My only issue is whether i'm using the right date for when the server was restarted...i was just assuming the youngest date in the DMV;s
is there a better date that someone might recommend for whent he server 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
/*--Results
usage_start_date TheDatabase last_read last_write
----------------------- -------------------------------------------------- ----------------------- -----------------------
2011-03-16 10:52:00.193 master 2011-03-22 08:25:47.737 NULL
2011-03-16 10:52:00.193 msdb 2011-03-22 10:20:09.830 2011-03-22 02:18:13.010
2011-03-16 10:52:00.193 ReportServer 2011-03-22 13:09:36.270 2011-03-22 13:09:36.270
2011-03-16 10:52:00.193 ReportServerTempDB 2011-03-22 13:01:09.717 2011-03-22 13:01:09.717
2011-03-16 10:52:00.193 SandBox 2011-03-21 14:18:45.003 NULL
2011-03-16 10:52:00.193 tempdb NULL NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
*/
;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,
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,
TheDatabase,
MAX(last_read) As last_read,
MAX(last_write) AS last_write
FROM
(
SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,NULL, last_user_update FROM myCTE
) AS x
CROSS JOIN ServerFirst
GROUP BY TheDatabase
ORDER BY TheDatabase
Lowell
March 22, 2011 at 11:33 am
Lowell (3/22/2011)
Based on that blog by Aaron, I put the script below together.My only issue is whether i'm using the right date for when the server was restarted...i was just assuming the youngest date in the DMV;s
is there a better date that someone might recommend for whent he server 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
/*--Results
usage_start_date TheDatabase last_read last_write
----------------------- -------------------------------------------------- ----------------------- -----------------------
2011-03-16 10:52:00.193 master 2011-03-22 08:25:47.737 NULL
2011-03-16 10:52:00.193 msdb 2011-03-22 10:20:09.830 2011-03-22 02:18:13.010
2011-03-16 10:52:00.193 ReportServer 2011-03-22 13:09:36.270 2011-03-22 13:09:36.270
2011-03-16 10:52:00.193 ReportServerTempDB 2011-03-22 13:01:09.717 2011-03-22 13:01:09.717
2011-03-16 10:52:00.193 SandBox 2011-03-21 14:18:45.003 NULL
2011-03-16 10:52:00.193 tempdb NULL NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
*/
;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,
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,
TheDatabase,
MAX(last_read) As last_read,
MAX(last_write) AS last_write
FROM
(
SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,NULL, last_user_update FROM myCTE
) AS x
CROSS JOIN ServerFirst
GROUP BY TheDatabase
ORDER BY TheDatabase
Ok, that makes perfect sense. This script should give you both the server start and the last time a user performed an action in the database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2011 at 11:50 am
ok I'm poking around trying to find what might be the most accurate server start time; I'm not sure the first date i find in the DMV's would be perfect..i'd think it's possible the server is up but un-accessed for some period of time before the first seek occurs.
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime
gives me a date, but (thank you microsoft), the MSDN just tells you the obvious: that date is the last time the resource database was updated.
does the resource database get updated every time you reboot? every time you add something to master?
THAT info i cannot find with my Google-fu skills.
Lowell
March 22, 2011 at 12:06 pm
ok answering my own question, I definitely do not want the serverproperty;
what i foudn that since one of the first things SQL does on startup is create a new tempdb, i can use that databases create_date;
this is the best i've got so far:
;WITH myCTE AS
(
SELECT
DB_NAME(database_id) AS TheDatabase,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
)
SELECT
ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
TheDatabase,
MAX(last_read) As last_read,
MAX(last_write) AS last_write
FROM
(
SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,NULL, last_user_update FROM myCTE
) AS x
GROUP BY TheDatabase
ORDER BY TheDatabase
Lowell
March 23, 2011 at 9:11 am
Lowell (3/22/2011)
ok answering my own question, I definitely do not want the serverproperty;what i foudn that since one of the first things SQL does on startup is create a new tempdb, i can use that databases create_date;
this is the best i've got so far:
;WITH myCTE AS
(
SELECT
DB_NAME(database_id) AS TheDatabase,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
)
SELECT
ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
TheDatabase,
MAX(last_read) As last_read,
MAX(last_write) AS last_write
FROM
(
SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,NULL, last_user_update FROM myCTE
) AS x
GROUP BY TheDatabase
ORDER BY TheDatabase
Thanks. I tried this script , in the last_read column it shows me current date with few hours back. Does it take into account a read done by a system process or a user-based read? I would be surprised if each of my dev database is read few hours back;-)
March 23, 2011 at 10:26 am
Thanks to this question I decided to write a blog post on the topic. I presented three different methods to get the same data.
You can read it at: http://jasonbrimhall.info/2011/03/23/sql-server-startup/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 24, 2011 at 9:23 am
CirquedeSQLeil (3/23/2011)
Thanks to this question I decided to write a blog post on the topic. I presented three different methods to get the same data.You can read it at: http://jasonbrimhall.info/2011/03/23/sql-server-startup/
Thanks Jason, but my question is still not answered. From your blog the in the second script the last_read column gives me data saying a read happened on each db few mins back( which i am very positive is not true) and the third script fails with error "Msg 567, Level 16, State 5, Line 8
File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
Warning: Null value is eliminated by an aggregate or other SET operation.". Any idea?
March 24, 2011 at 10:03 am
sqldba_icon (3/24/2011)
CirquedeSQLeil (3/23/2011)
Thanks to this question I decided to write a blog post on the topic. I presented three different methods to get the same data.You can read it at: http://jasonbrimhall.info/2011/03/23/sql-server-startup/
Thanks Jason, but my question is still not answered. From your blog the in the second script the last_read column gives me data saying a read happened on each db few mins back( which i am very positive is not true) and the third script fails with error "Msg 567, Level 16, State 5, Line 8
File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
Warning: Null value is eliminated by an aggregate or other SET operation.". Any idea?
First question - are all of the timestamps different for each database? They should be. And that column is user activity. If you are getting activity in it, it is quite possible that there is an app that may be doing a heartbeat check on the database. That app activity would be considered user activity.
Second question - what version of SQL Server? It is quite possible that the default trace has been turned off on your database server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 24, 2011 at 1:13 pm
default trace is enabled
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply