tracking logins 2008 r2 auditing

I need to identify which sql server db's are active in my instance of sql server.
the sql server is running sql 2008 r2 standard.

when I try to setup an audit through management studio, it err's out and tells me auditing is not a feature allowed
on this type of sql application "2008 r2 standard"
does anyone know away I can accurately track logins to sql server databases by the database name?  I have inherited a sql server that has 80 db's and I don't know which ones are being actively used.

My plan was to track logins over a week and see which db's are being utilized.
jamesmetcalf74Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jogosConnect With a Mentor Commented:
With the management views you can see different versions of active: active now, recent, most active ...
https://msdn.microsoft.com/en-us/library/ms188068.aspx
0
 
jogosConnect With a Mentor Commented:
"My plan was to track logins over a week and see which db's are being utilized."
With a login you don't see which database is being used.
0
 
jamesmetcalf74Author Commented:
I've done a query select * from sys.dm_db_db_index_usage_stats and it has a column called database_id

the database_id I would assume is tracking an actual db... please correct if im wrong.
so if I associate that with the database name.... isn't there another column in the output of this query that specifies high or no usage?

sample of output attached
output-of-query.xlsx
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jogosCommented:
Indeed mamanagent views return data from usage .... that is more exaclty since last restart or clean of that data.

db_Name(database_id) and you even got the name of that database
0
 
jamesmetcalf74Author Commented:
how can I get it to display the name of the db?  or is this something I need to cross reference?
and which columns to you think would indicate real usage of the db?
Thanks Jogos
0
 
jamesmetcalf74Author Commented:
how can I get the query to display the name of the db  *correction from earlier post
0
 
jogosCommented:
DB_NAME() gives the name of current db,  DB_NAME(database_id) gives name of database which id you are passing
0
 
jamesmetcalf74Author Commented:
im a little confused as I am new to being a dba.

are you saying that I can write a select statement that will give me the db name in the output.
the output from my first query

select * from sys.dm_db_db_index_usage_stats
was posted in the excel spreadsheet

I don't see any db names in the output
could you assist in query creation that would put that attribute in the output?
0
 
jogosCommented:
Not used is more difficult to track than certainly used
Some ways to track usage and an idea on why it can mislead you
http://www.brentozar.com/archive/2014/05/4-lightweight-ways-tell-database-used/
0
 
jogosCommented:
How to get db-name and example on why it is important to give an id as parameter to that function
select  DB_NAME(database_id),* from sys.dm_db_db_index_usage_stats

use master 
select  DB_NAME()
use msdb
select DB_NAME()

Open in new window

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.

All Courses

From novice to tech pro — start learning today.