December 23, 2009 at 11:41 pm
Hi all,
We need identify abandoned database instances on our servers.
I want to use System Performance Monitor to monitor SQL Server->user connections. but this one uses for all of dbs in sql server.
Any good idea?
I appreciate for your help.
Wish you a Merry Christmas & great holidays. 🙂
December 24, 2009 at 12:23 am
How about System Center Operations Manager 2007?
Can we use it for us? I didn't use it much before.
December 24, 2009 at 2:59 am
/*
When was my table last accessed ??
Note: If SQL Server is restarted, the information from DMV's is reset.
*/
--For all DB's in Server
CREATE TABLE #USEDDB
(
dbname varchar(50),
[object_name] varchar(50),
user_seeks int,
user_scans int,
user_lookups int,
user_updates int,
last_user_seek datetime,
last_user_scan datetime,
last_user_lookup datetime,
last_user_update datetime
)
go
INSERT INTO #USEDDB
exec sp_msforeachdb 'USE [?]; select DB_NAME(DATABASE_ID) AS dbname, t.name AS object_name,user_seeks ,user_scans ,user_lookups ,user_updates ,last_user_seek ,last_user_scan ,last_user_lookup ,last_user_update
from [?].sys.dm_db_index_usage_stats i JOIN [?].sys.tables t ON (t.object_id = i.object_id)
where database_id = db_id()
Order by i.database_id'
--FIND USED DATABASES
SELECT DISTINCT DBNAME as Used_DB FROM #USEDDB
--FIND UNUSED DATABASES
SELECT [NAME] as Unused_DB FROM SYS.DATABASES
EXCEPT
SELECT DISTINCT DBNAME FROM #USEDDB
--Drop Temporay table
drop table #useddb
This is just a beginning.
December 24, 2009 at 3:00 am
The table access in a database can give you an idea when your database was last used. Make sure the server has not been restarted recently.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply