July 24, 2012 at 11:24 am
I want to identify every database that is ‘referenced’ from say 5PM – 9AM, without:
1). Restarting SQL server to reference Index access.
2). Jamming up folders with huge trace file outputs.
Is there a DMV that can assist in my search? Prefer not to turn on auditing, if possible.
thx
July 24, 2012 at 11:35 am
What does 'referenced' really mean? Without defining that, nobody can help you.
Jared
CE - Microsoft
July 24, 2012 at 12:51 pm
I have a similar issue:
1). Track Databases that are read-only from nPM - nAM.
2). Track Databases that are updated from nPM - nAM.
So if that helps clarify, maybe this sheds some light on original question.
Thanks.
July 24, 2012 at 1:03 pm
I am not sure I understood it clear. But you log result of following query at nPM and nAM and compare the resultsets should give you what you want.
select DB_NAME(database_id) DBName,max(last_user_update) last_user_update from sys.dm_db_index_usage_stats
group by DB_NAME(database_id)
Edit: you can add last_user_seek,last_user_scan, and last_user_lookup for read operations
July 24, 2012 at 1:08 pm
Hate to say it, but I think audting is the best option for all requirements posted in this thread.
July 24, 2012 at 1:24 pm
Daxesh Patel (7/24/2012)
I am not sure I understood it clear. But you log result of following query at nPM and nAM and compare the resultsets should give you what you want.
select DB_NAME(database_id) DBName,max(last_user_update) last_user_update from sys.dm_db_index_usage_statsgroup by DB_NAME(database_id)
Edit: you can add last_user_seek,last_user_scan, and last_user_lookup for read operations
What if this DMV was not updated based on the operation that was run? Best bet for anything like this is a server-side trace.
Jared
CE - Microsoft
July 24, 2012 at 1:30 pm
Thanks for the DMV... that may work for what I'm doing.
Can you shed some light on the 'auditing' option ?
July 24, 2012 at 1:41 pm
Tim Hiller (7/24/2012)
Thanks for the DMV... that may work for what I'm doing.Can you shed some light on the 'auditing' option ?
What happens when a process is only run once a month, quarter, or year? You missed it, that's what. What is the business case for investigating this? If it is important to be extremely accurate, you will have different options than for quick and dirty for an estimated usage.
Jared
CE - Microsoft
July 24, 2012 at 1:50 pm
What if this DMV was not updated based on the operation that was run? Best bet for anything like this is a server-side trace.
Server-side trace can be the best option but I gave DMV option as OP wanted.
I am not aware of any chances of DMVs not updated for read/write operations, can you please share some reference?
Edit: got your point below...
What happens when a process is only run once a month, quarter, or year? You missed it, that's what. What is the business case for investigating this? If it is important to be extremely accurate, you will have different options than for quick and dirty for an estimated usage.
July 24, 2012 at 1:52 pm
One to start: http://www.sqlskills.com/blogs/joe/post/What-sysdm_db_index_usage_stats-may-not-tell-you.aspx
What if the table has no indexes? What if none of the indexes are used in the query? I understand this is unlikely, but it is possible. Auditing may help, but what are you auditing?
Also, the OP said "without huge trace files," not without trace files. So, design the server-side trace properly and they will not result in huge files. I run traces on extremely busy servers and design them to only gather what I need. This can be measured in 10's of kB at the end of 2 or 3 days if done correctly. Plus, in this case, the OP is suspecting a certain level of activity on the database. Now they are trying to verify it (as I would hope they would already know if it is really busy at night, why run a trace to see if it is not).
All-in-all... It depends on why the OP is doing this. What are you trying to prove to whom?
Jared
CE - Microsoft
July 24, 2012 at 1:57 pm
SQLKnowItAll (7/24/2012)
One to start: http://www.sqlskills.com/blogs/joe/post/What-sysdm_db_index_usage_stats-may-not-tell-you.aspx
Excellent article! I was really not aware of this. Thanks a lot for sharing
July 24, 2012 at 1:59 pm
Great followups - appreciate it. Just for background, we are getting ready to migrate to another data center. The detailed implementation task that I'm looking to qualify, is which databases can be backed up prior to the the end of batch cycle on the Friday, into Saturday of cutover weekend.
The batch cycle is 'known', so we're solid on what is the weeklies, monthly, etc jobs are (i.e., we run a weekly update of returns data on Sunday's - that database will be backed up and migrated during the week).
The goal is to backup/swing/restore databases as early as possible to new data center, and then followup with the backups affected by batch.
July 24, 2012 at 2:25 pm
If the recovery model for the database is full then you can also try running the following undocumented command to get a crude view of the transactions that may have been performed in the database.
SELECT [Begin Time], description FROM ::fn_dblog(NULL, NULL) where [Begin Time] is not null
you will have to ignore some of the records from the output but it will definitely help you confirm as to what you are looking for, i mean whether the databases were updated in a particular time frame.
The above query will only list information from the log file, you can query the individual backup files for earlier information that has already been truncated if an when log backups may have been performed.
Please read a related blog from Paul S. Randal on sqlskills.com for additional information.
July 24, 2012 at 2:40 pm
Tim Hiller (7/24/2012)
Great followups - appreciate it. Just for background, we are getting ready to migrate to another data center. The detailed implementation task that I'm looking to qualify, is which databases can be backed up prior to the the end of batch cycle on the Friday, into Saturday of cutover weekend.The batch cycle is 'known', so we're solid on what is the weeklies, monthly, etc jobs are (i.e., we run a weekly update of returns data on Sunday's - that database will be backed up and migrated during the week).
The goal is to backup/swing/restore databases as early as possible to new data center, and then followup with the backups affected by batch.
Ok, so it is the unknowns right? What if you check for a week and nobody uses the database between the times thought... Does that mean nobody will try to use it during those times on a different day or week? I say use your best judgement based on the index_stats dmv, and then send out a maintenance announcement. It will be too much trouble to audit and trace for what you are doing. Even with that data, someone could try to access the data during those times on another occasion.
Jared
CE - Microsoft
July 24, 2012 at 4:02 pm
Vikrant S Patil (7/24/2012)
If the recovery model for the database is full then you can also try running the following undocumented command to get a crude view of the transactions that may have been performed in the database.SELECT [Begin Time], description FROM ::fn_dblog(NULL, NULL) where [Begin Time] is not null
Except that will never tell you if the DB is read, and it may give false positives (system transactions)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply