How to find the SQL Server databse Last used by db users ?
-
How to find the SQL Server databse Last used by db users ?
-
Last used information is not really stored anywhere; but you can infer the lastused, since the last time the SQL Server Service was Restarted, based on sys stats for index usage:
--based on the ideas from
--http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx
;;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'),
x.TheDatabase,
MAX(x.last_read) AS last_read,
MAX(x.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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell.
but i need to get the details of all the users of all the dbs with last logintime to the databases.
-
SQL Server Audit would be an option, this may help http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
-
charipg (4/11/2012)
Thanks Lowell.but i need to get the details of all the users of all the dbs with last logintime to the databases.
The logon event only occurs once;there no lo logging into a database per se.
it sounds like you might want to use a DML trace instead; that can give you whodunnit information and you can group by to get min/MAX event times
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Could Logon triggers be used?
Get them to write to an auditing table and then query that for the information you require?
As Lowell says though, after the initial login event you won't know if the database context is changed.
-
Gazareth (4/11/2012)
Could Logon triggers be used?Get them to write to an auditing table and then query that for the information you require?
As Lowell says though, after the initial login event you won't know if the database context is changed.
that was my first thought too, either a login trigger, or using the built in applications login tracking, which writes to the SQL log:
but the OP said he want's per database per user tracking , so depending on whether he wants whodunnit for insert/update/delete, or also whoodunnit for SELECT statements, it's going to require a lot more, which is why i thought he might be after a trace.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (4/11/2012)
that was my first thought too, either a login trigger, or using the built in applications login tracking, which writes to the SQL log:but the OP said he want's per database per user tracking , so depending on whether he wants whodunnit for insert/update/delete, or also whoodunnit for SELECT statements, it's going to require a lot more, which is why i thought he might be after a trace.
My thought too, but depending on the activity on the server, those log files are going to be horrific to read for non-login events 🙂
A trace could be the way to go!
-
To Lowell's point, if you are wanting a "who dunnit", then would it be possible to put all updates, deletes, inserts and selects into SPROCs, and log who runs what SPROC and when, with what input parameters? I do that with my databases, and just log to a DBActivity table.
It's according to what end-game you are trying to accomplish.
-
is it possible to find actual last accessed time though i restart the server every week?
-
charipg (4/10/2012)
How to find the SQL Server databse Last used by db users ?Are you including SELECTs in your "last used"?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Depending on what method you use, you may be able to query post restart.
Second question: Why restart every week?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply