If you are already working on SQL Server 2008 since decent amount of time, you might have already known the inbuilt standard reports which we can popup Just by right clicking on an object in our Object Explorer.
In this Blog post, i would like to show some interesting In Built Reports which you(we) as a DBA might(Should) be interested! Before going any further, please see the below Screenshot for what i’m actually referring to if you are new to SQL Server.
Server Dashboard: This is a very useful report especially for a DBA to understand his Instance Configurations, Settings at a very high level in a single glance. I really like this guy and would recommend you to open this fab report as frequent as you can on your Server.
As you can see, at a single glance it is giving my Server(Well, It’s actually SQL Instance) startup time, Instance name, Version, Stand alone or clustered, AWE Settings, memory settings, CPU’s visible to SQL and many more….My personal favorite in this report is “Non Default Configuration Options“. Basically this tiny section is giving me a clear picture of settings which are deviating from default SQL Server settings, which is really reallyyyyyyyyyyy useful for a DBA. Let’s assume a scenario where a DBA from your team is leaving and he/she is doing a Knowledge Transfer to you. Just by pulling this info, you can ask him/her “Hey i’m seeing the following Non Default Configuration Options on this Instance, Can you pls. explain why??…”
Transaction Log shipping Status: This is a single place where you can get all the cool information regarding all the thresholds, last backed Up t-log, copied t-log backup, restored t-log backup(with File names and LSN details as well…Huhuuuuuuuuu That’s awesome!!) Note: For accurate and more meaningful info, you’ve to pull this report from both your Prod and DR Servers, to see if your DB is out of sync.
Schema Changes History: You can see the DDL Changes being made on your Databases. For example If some one dropped a Table and if you as a DBA are looking for an option to track him/her down….Well, this is one of the places 🙂
Note: The above information will be pulled from SQL Server Default Trace. So the Data will be not stay for ever! You can see only the info what has been captured by your DEF trace. very Imp….!
Sooo…are these the only Inbuilt Reports(which you are seeing in the first Screenshot) we have in SSMS?? Nope!! Â If we Right click on the Database in Object Explorer, you’ll get completely different set of reports! See the below Screenshot.
My personal favorites are
Backup and Restore Events: Please see the below Screenshot!
It gives me the Avg time it is taking for backup Operations! You can use this as a Benchmark for your backup duration (well, there is much better way to pull that info from MSDB, I’m Just talking about basic Benchmarking here ;))  If you expand Successful Backup/Restore  Operations, it tells you what was backed up/restored, the location of files, when it was performed and who(the Login) performed the operation…Well one more way to track these operations!!
Disk Usage:
You might think that it only Shows, the sizes of your Data and log files on your Disk. Well, there is one of the Most important hidden gem in this report. Auto Shrink/Auto Grow Events as you can see below. Tadaaaaaaaaaaaa!
This gives me a clear picture that my log file has auto grown several times on 9/7/2011, Which means as a DBA I’ve to consider changing my File Growth settings on my database. This is the Most important factor which might be hindering performance of your Database( For the sake of  this example I’ve changed the autogrowth options to 1 MB on data and 10% on my log file and i inserted 10000 rows. Well this is just my test lab, but imagine the same happening on your Live Production database!!!! Really Really bad).
Note: If you see any Auto Shrink Operations in the above report, ha ha you are gone. It’s time to take action immediately!! I’m not going to deal with what Shrinking does to a Database in this post, for now…Just remember, Setting Auto Shrink option turned ON on any database is the worst possible thing you could potentially do to your Database!!
I recommend to explore all these Inbuilt, completely free and most fabulous reports and get familiarize with what each report can do for you as a DBA. Hope this Post helps! Â
MSSQLTREK 
Nice post thanks for sharring