• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

SQL Database Question

SQL 2008

(Getting there slowly team, thank for all the help so far!)

OK I would like to try and see if over half of my database are actualy being used.
My plan is to put them off line and see if someone shouts. Then when they do ask them what they were doing and then I know what its doing.

But looking I can see I can do a search on the database and look at the modify date.

Example

I have a database "NSMRulestat"
I found a command or Query is that what you SQL guys call it.

USE NSMRulestat
GO
SELECT name, modifydate
FROM sys.objects
GO

It comes back with this

Query
Are these tables within the database?
How would you find out what they are?
0
Bransby-IT
Asked:
Bransby-IT
  • 2
2 Solutions
 
jacrejiCommented:
Try using this query to get the last accessed date for the database :

SELECT DatabaseName, MAX(LastAccessDate) LastAccessDateFROM    (SELECT        DB_NAME(database_id) DatabaseName        , last_user_seek        , last_user_scan        , last_user_lookup        , last_user_update    FROM sys.dm_db_index_usage_stats) AS PivotTableUNPIVOT     (LastAccessDate FOR last_user_access IN        (last_user_seek        , last_user_scan        , last_user_lookup        , last_user_update)    ) AS UnpivotTableGROUP BY DatabaseName  HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')ORDER BY 2

The query that you are using is for all objects including the system tables and procedures as well.
0
 
Bransby-ITAuthor Commented:
Thanks for the Query, I cant get it to run, when I copy and paste it to notepad it goes funny.
0
 
jacrejiCommented:
Try it now.. this should work

SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate FROM    
(SELECT DB_NAME(database_id) DatabaseName ,
 last_user_seek ,
 last_user_scan ,
 last_user_lookup  ,
 last_user_update    
 FROM sys.dm_db_index_usage_stats) AS PivotTable UNPIVOT
 (LastAccessDate FOR last_user_access IN
 (last_user_seek,
  last_user_scan,
  last_user_lookup,
  last_user_update))
  AS UnpivotTable GROUP BY DatabaseName  
  HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb') ORDER BY 2
0
 
Scott PletcherSenior DBACommented:
The objects listed are all system tables/views.

To look for activity, it's better to query "sys.tables", since it shows only user tables:

SELECT name, modifydate
FROM sys.tables
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now