Monthly Archives: April 2012

The unknown unknowns

I was spring cleaning some old scripts and I came across my first attempt at establishing a backup strategy. 12 hours later I’m still feeling a bit queasy. When I first put on the DBA hat, we were running with the simple recovery model and doing a full backup every night. I did some homework and thought I’d got it all figured out. My killer improvement? Use full recovery and every night do a log backup and then a full backup. That way we could recover to any point in time. Brilliant!*
Since then I’ve learned a lot. I know I’m much better than I was. But I can never quite shake that small nagging doubt: how much of what I’m doing now – believing it’s good practice – will I one day look back on and shudder? But this is a good thing. It keeps me on my toes, continually re-evaluating what I’m doing, and hungry to learn.
*Just to be clear, I’m being sarcastic. This NOT a good solution. If you have any doubts:
  1. Read Books Online about recovery models and backups.
  2. Read what Paul Randal has written about it in his blog and in TechNet Magazine.
  3. Check out Ola Hallengren’s database maintenance solution before you write your own.

Reporting on report usage revisited

Nearly three years ago I wrote about how to query the ReportServer database and see who is running which reports. Since then I’ve made it a bit more focused. Which reports are being used the most? Which users are the busiest and which reports are they looking at? Here’s what I came up with.

(NB: This is for 2008 or 2008 R2 Reporting Services in standalone mode. I’ve no idea what you get in SharePoint mode.)

Report Heatmap

Which reports are being used the most?

If you click on a report you see…

Report Usage

Who has been using that report?

Report User Heatmap

Who are the busy users?

If you click on a user you see…

Report User History

Which reports has that user has been running?

Feel free to download the RDL files and stored procedures here and use them yourself.