Solved

SQL Database Question

Posted on 2013-01-10
4
329 Views
Last Modified: 2013-01-21
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
Comment
Question by:Bransby-IT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
jacreji earned 400 total points
ID: 38763351
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
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38763830
Thanks for the Query, I cant get it to run, when I copy and paste it to notepad it goes funny.
0
 
LVL 1

Expert Comment

by:jacreji
ID: 38763859
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 38767925
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

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question