January 20, 2015 at 12:34 pm
I have a database instance with about 80 db's on it.
I am trying to figure out which db's are actively used.
is there a way for me to see in an excel or grid format the following info.
database name last time used
last time used can be any attribute that would suggest the database had been used. date time would be nice
January 20, 2015 at 12:38 pm
jim.metcalf1974 (1/20/2015)
I have a database instance with about 80 db's on it.I am trying to figure out which db's are actively used.
is there a way for me to see in an excel or grid format the following info.
database name last time used
last time used can be any attribute that would suggest the database had been used. date time would be nice
By default sql server does not track the last time a connection was established or closed. You would have to have some auditing in place for this to happen.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2015 at 1:03 pm
You can create a daily/hourly/minutely job that queries sys.databases join sys.sysprocesses and inserts results into a some table, and run it for a while.
January 20, 2015 at 1:07 pm
SQL Guy 1 (1/20/2015)
You can create a daily/hourly/minutely job that queries sys.databases join sys.sysprocesses and inserts results into a some table, and run it for a while.
Could also add a logon trigger. Neither of these are perfect but might be a start in the right direction.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2015 at 7:08 pm
The default trace has *some* auditing of login information and object creation / deletion. You could use that if you have no jobs to track usage yet.
You can also check the your index usage stats dmv which show last_user_scan / seek / user_updates. Or check for last time stats were updated for large tables (unless you have a job doing this automatically)
SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id], ius.[database_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE 1=1
--AND ius.[database_id] = DB_ID()
--AND ius.[object_id] = OBJECT_ID('YourTableName')
GROUP BY ius.[database_id], ius.[object_id]
ORDER BY ius.[database_id], ius.[object_id];
January 20, 2015 at 11:17 pm
i can understand what kind of dilemma you are in right now. its great you want to know which is active db or not. If its a office environment, and you have a shared sql server for all the DEV teams working, the best approach you can use to email to all the DEV team to tell you which db instance they are currently working and which one they are not. if you try to stop an instance which might be idle for like several months, this happens to the DEMO related application db then it would hurt you in this case.
January 21, 2015 at 10:05 am
After some research I wanted to use the audit feature on one particular server and it says.
auditing is not a feature on this server. 2008 r2 standard sql server.
Next recommendation?
you guys were speaking of a trigger.
is this something within sql analyzer?
January 21, 2015 at 10:13 am
jim.metcalf1974 (1/21/2015)
After some research I wanted to use the audit feature on one particular server and it says.auditing is not a feature on this server. 2008 r2 standard sql server.
Next recommendation?
you guys were speaking of a trigger.
is this something within sql analyzer?
No a trigger is not something in analyzer. It is an event that is attached to something. In this case I was referring specifically about a logon trigger. That means, there is some code that fires when a logon event occurs.
https://msdn.microsoft.com/en-us/library/bb326598.aspx
This is not the be all end all solution here. Consider applications that only run once a year to support year end or the HR application that is only used for a month during review season. It also does not capture things like cross database queries.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply