Solved

tracking logins 2008 r2 auditing

Posted on 2015-01-21
10
53 Views
Last Modified: 2015-01-27
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.
0
Comment
Question by:jamesmetcalf74
  • 6
  • 4
10 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 40562404
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 500 total points
ID: 40562413
"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
 

Author Comment

by:jamesmetcalf74
ID: 40562439
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 25

Expert Comment

by:jogos
ID: 40562457
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
 

Author Comment

by:jamesmetcalf74
ID: 40562478
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
 

Author Comment

by:jamesmetcalf74
ID: 40562480
how can I get the query to display the name of the db  *correction from earlier post
0
 
LVL 25

Expert Comment

by:jogos
ID: 40562506
DB_NAME() gives the name of current db,  DB_NAME(database_id) gives name of database which id you are passing
0
 

Author Comment

by:jamesmetcalf74
ID: 40562514
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
 
LVL 25

Expert Comment

by:jogos
ID: 40562525
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
 
LVL 25

Expert Comment

by:jogos
ID: 40562544
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 rs - Field1 and Field2 add 1 to Total 4 21
PROPERCASE SCRIPT IN SQL 3 16
tempdb log keep growing 7 33
MS SQL Server select from Sub Table 14 23
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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