checking latest activities on database

Question

checking latest activities on database

k.przewozniak

Ten Centuries

Points: 1133
More actions
September 4, 2008 at 6:59 am

#124399

How can I check latest activities done on database?
I know already that there is view sys.tables where I can see latest modifications, however users can do other activities like reading. Where I can check that?
I need that for cleanup purpose, if database has not been used for some time I would like to take it offline. Any ideas are welcome 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 1

Will auditing output suit your requirement? You can check the SQL Server Error log for the user login activity and if there is no user login then there is no activity and you can perform the cleanup... (SQL server auditing option can be found in SQLServer Property tab)

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

k.przewozniak Ten Centuries Points: 1133 More actions · Answer 2

I guess this option has influence on performance, hasn't it?

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 3

I thought you would have enabled this as corporate standards... If not then try this... Monitor the growth of the database over period by running some scheduled jobs and if there is no change in the database size then it means there is no INSERT/DELETE/UPDATE activity performed in the database?

Just thought process...

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

k.przewozniak Ten Centuries Points: 1133 More actions · Answer 4

insert/update/delete it is modification. As I wrote in first post, I know already how to check that. I would like to control also if anybody is viewing the date.

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 5

Ok my next ball...

Can you run scheduled profiler for Transactions/sec on each database and check over a period whether there are some transactions happening in DB?

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

k.przewozniak Ten Centuries Points: 1133 More actions · Answer 6

Ooo, that I think is a very good idea 🙂 It is not in 100% what I wanted but if there is no other way it will be very useful. Thank you 😀

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 7

Gr8.. So you will be sharing you experiences after completing this experiment?

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 8

If it's just to check if a database is still actively used you can also use the sytem view sys.dm_db_index_usage_stats to track database activity.

This view has columns to track the last time an index has been scanned, updated or seeked and should give a pretty reliable picture about when the database has been used the last time. Only thing to remember is that during a reboot , the data in this view will be flushed and it will take some time before it becomes usefull again.

Something like this shold work:

SELECT DB_NAME(database_id), MAX (LastAccess) As LastAccessed FROM

(

SELECT database_id, last_user_seek as LastAccess

from sys.dm_db_index_usage_stats

UNION

Select database_id, last_user_scan

from sys.dm_db_index_usage_stats

UNION

Select database_id, last_user_lookup

from sys.dm_db_index_usage_stats

UNION

Select database_id, last_user_update

from sys.dm_db_index_usage_stats ) as T

GROUP BY database_id

ORDER BY database_id

[font="Verdana"]Markus Bohse[/font]

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 9

Sorry, I forgot to exclude the system objects in my previous query. These are queried everytime you open Object Browser in SSMS it won't help you at all. So here's the improved version:

SELECT database_id,last_user_scan

FROM sys.dm_db_index_usage_stats

WHERE OBJECT_ID > 1000

UNION

SELECT database_id, last_user_lookup

FROM sys.dm_db_index_usage_stats

WHERE OBJECT_ID > 1000

UNION

SELECT database_id, last_user_update

FROM sys.dm_db_index_usage_stats

WHERE OBJECT_ID > 1000) AS T

GROUP BY database_id

ORDER BY database_id

[font="Verdana"]Markus Bohse[/font]

sodeep SSC Rookie Points: 34 More actions · Answer 10

sodeep

SSC Rookie

Points: 34

September 5, 2008 at 10:58 am

#867924

You can run sp_who2 active .

k.przewozniak Ten Centuries Points: 1133 More actions · Answer 11

hi MarkusB,

That was very useful! Do you have idea how to adjust it for SQL Server 2000? 😎

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 12

Sorry, but SQL 2000 doesn't have this functionality. Only options in 2000 would be running a trace or use a third party auditing.

[font="Verdana"]Markus Bohse[/font]

RPSQL Hall of Fame Points: 3229 More actions · Answer 13

Here is the script to find out last ran queries on sql server 2005

----------------------------------------------------------------------

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

-----------------------------------------------------------------------

You can also try this...

http://sqlblogcasts.com/blogs/jonsayce/archive/2008/01/14/Total-SQL-Recall.aspx