SSMS(SQL Server Management Studio) Standard Reports and my Personal Favs!!

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!  


The file ‘microsoft.reportviewer.winforms version’ cannot be opened Error!!

In this Blogpost, Actually I thought of showing few hidden gems in our our Fabulous SSMS standard Reports, but ran into one of the interesting issues when i was trying to open REPORTS from SSMS. You can see the error Message below in the window.

Please note, I’m running on SQL Server 2008 SP2! So it has nothing to do with the Bugs or Service Packs or even any Cumulative updates installed!

How to Fix this Issue in my case?

I remember not Installing complete Client tools on this Server while I was installing SQL Server. You can see the below Screenshot where BIDS and other Client Components(I Installed only SSMS initially) being missing on this Server.

Let’s Install BIDS(Business Intelligence Development Studio) and see what SSMS say when we try to bring up Reports!

As you can see once I Installed BIDS, I was able to open Reports Successfully 🙂 Also, you can verify a folder called “ReportViewer” Being created in Microsoft Visual Studio Folder in “program files”  Directory as seen below.

Hope this helps when you get into a similar Issue!

Practice/Worst Practice

Have been to an amazing trip visiting few of my friends and was not able to spend some time in my technical blogging :-p

Okay! Anyways I’m back to business after a loong (No Pun Intended) trip!
Thought of sharing one of my sickest and weirdest experience happened today because of my recent practice of editing tables using SSMS GUI.
Do you love Altering a table using GUI in SSMS?? If you said “yes, I do” then it’s time to think about what you are really doing with your SQL Servers!! If you are learning T-SQL,  Unarguably this is a fabulous place to learn syntax-es. But what if you are lazy in typing or used to do it even though you’ve enough exp supporting SQL Servers? – No excuses, you’ve to change your practice!

You don’t love GUI for Altering your tables but are you forced to practice sooo? Well, that’s the case with me. I’ll try to change with this lesson learnt:)

I’m supporting an environment where application team wants columns to be added in middle of the table since past 12 months (I know what you are thinking, it doesn’t make any sense adding a column in middle of table, all it matters how we write our select statements.) I had enough argument with Application team and they are not willing to change the practice:( 😦
For that we need to use GUI thing of SSMS for editing table. Doing those requests since past 12 months i’m kind of get used to it even for increasing column width! I pity myself!!

Okay! coming to the scenario, we’ve a 9 GB table where the request is to change the width of a column from varchar(25) to varchar(40) and as usual i started using SSMS GUI instead of simple ALTER statement and it was timing out again and again. I changed timeout settings from 30 seconds to 120,240,300 seconds, nothing helped and i started to press PANIC button on my Keyboard😦
I wasted 30 mins of my time banging my head and at last i called one of the senior most DBA’s in my team and he was looking for how and what exactly i was trying to do. When i explained what i’m doing, he reminded me about there is something called “ALTER TABLE” in SQL Server!!!LOL….And I was like, what am i doing and i slapped myself saying “what an idiot am i”.
ALTER TABLE / ALTER COLUMN did the magic in matter of seconds.

Epitome: SSMS is very poor and it does lot of unnecessary stuff behind the scenes for altering any table.(look your self by scripting out when using GUI). Believe me you’ll be surprised how SSMS is handling those things and i hope Microsoft will change things in future versions of SSMS.

SSMS – Tweaks

SQL Server Management Studio – Outstanding UI provided by Microsoft for managing/Administering/Developing/manipulating  code in (for) SQL Server environments.
As a DBA how much time would you spend with SSMS on a daily basis? I would say 90-95%! Well, we will be using Configuration manager if needed sometimes to troubleshoot few things (Will be covered in another post).
As a SQL Server Developer how much time would you spend in SSMS on a daily basis? I would say close to 100%.
Note: I’ve also seen few Application teams still using TOAD to develop T-SQL. I’m not covering TOAD coz I never used TOAD (why would/Should I??? if I’m already using fabulous SSMSJ)
PS BI (Business Intelligence) developers spend more time in BIDS (Business intelligence development Studio).
A Quick Question– How many of you explored all the features (at least few of the available features) in SSMS?
Will the default settings of SSMS Installation suffice our daily requirements? Of course “Yes” in most of the cases. But I’m pretty sure, after reading this blog completely you’ll definitely try exploring those options (if you are not yet at that point).
Okay! Now it’s time to explore…..
I’m assuming, you’ve got SSMS 2008 already installed on your machine(s) and you’ve a brief knowledge on why and how SSMS is used. I’ll explain few scenarios where most of us will find interesting.
Scenario1: Selecting records using GUI.
Most of the times we’ll be using these two options just to get a sample result set. So do we really need 1000 rows? How about changing it to 100 or even 10 Records? Cool huh!
How to edit that?
Go to Tools – Options – SQL Server Object Explorer – Commands. Change the default values at the right pane to the desired values. That’s all we’ve to do!
In this example, I’ve changed select value from 1000 to 10.

Scenario 2:Have you ever felt including IF NOT EXISTS logic automatically whenever you script any Object out of your database. SSMS by default don’t do that, but we can change that setting as shown below.
Now if we script an object in SSMS, it looks something similar to below code snippet.
USE [AdventureWorks]
/****** Object:  Table [Person].[Address]    Script Date: 06/26/2011 12:43:40 ******/
                [AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
                [AddressLine1] [nvarchar](60) NOT NULL,
                [AddressLine2] [nvarchar](60) NULL,
                [City] [nvarchar](30) NOT NULL,
                [StateProvinceID] [int] NOTNULL,
                [PostalCode] [nvarchar](15) NOT NULL,
                [rowguid]……………………………………….. bla bla
Play and get familiarized with rest of the scripting options which you are seeing in above screenshot.
Scenario 3:
When editing your tables using SSMS.
Let’s say you are adding a new column to your table in the middle. (Useless in most of the cases, since all it matters to SQL is the order how you pull your columns in your select statement.By default new column will be added at the end of table.) We’ve to do that by right clicking on table and selecting DESIGN. But SSMS won’t let us to do and it’ll throw an error because this change requires the table to be dropped in general. So what to do?
Just un-check Prevent Changes that require table re-creation and that’s it! Go to books online for finding out all the changes that require table re-creation in SQL Server.
Caution:For huge tables, it may timeout and I’ve seen few cases where SSMS crashes. I recommend you to Script out changes (generate Script button on your top left while you are doing any change) and review code to look what SSMS is really doing for you behind the scenes instead of just clicking NEXT-NEXT-OK in your SSMS for making any table changes. Believe me, you’ll never regret doing so.
Scenario 4: Shortcut Keys in ur SSMS.
Most of we DBA’s will be regularly using a set of commands on a very regular basis. For example sp_who2, “wait_stats DMV”, “dm_exec_requests” DMV and so on…So how about saving those regularly used statements as keyboard shortcuts instead of typing entire query manually repeatedly…
You can define your Queries as keyboard Shortcuts in SSMS as shown below.
PS sp_who will be default for ctrl+1.
Scenario 5: Annoying tab width in Query Pane.
How may query panes can you see in your SSMS at once?? 4,5 or 6….at most I can say 7 if you get rid of Object explorer and registered servers etc. The rest of query panes will be appearing in a drop down menu at top right which is really annoying if you are dealing with many things at once.(which most of us do as part of our job)
See below for what I’m saying…

Correction – that’s just SPID not SPID Number in above screenshot – forgive me,I’m not going to edit that screenshot now.

So, how to decrease its width by removing all the unnecessary details provided by SSMS by default?
Once I clicked OK, now you can see my SSMS accommodating more number of TABS.
These are few options which you can tweak in your SSMS today. Please see there are many more Cool features and options which are hiding in your SSMS without your notice. It’s time to explore all those features which MSFT has added for all of us, making our lives easyJ