Reviewing SQL Server Error Log – A Better approach!

How many of you have a habit of reviewing Error Log(s) of the SQL Server(s) which you support on a daily basis(At least on regular basis, if not daily)? If you are not doing that, go ahead and start making it as a practice from “Right now”. You can thank me later for this suggestion 😀

If you’re already doing that,then you are awesome…! But, let me ask this. How many times you get annoyed reviewing your error log(s), especially if you are looking for a specific error. Well, you can apply filter or you can simply query your error log from T-SQL. But what if you are only looking for all the severe Errors, total error count, Errors by frequency all at one place? Yes, you can write your own query and use SSRS for generating a fancy report. But Wait……What if I say SSMS already has this cool report inbuilt for us?

In this blog post I will show you a better way to decipher your current Error Log for Errors by using that hidden report.
It’s called “Number Of Errors” Report and you can get to this report by right clicking on “Management” Node -> Reports -> Standard Reports->Number of Errors in your SSMS as shown below.a

How it looks? See below…

As you can see, at a glance I know I’ve got 6 Severe Errors(Sev 18 or above) and 5 Moderate errors in my current error log. See below screenshots for how neat the info is once I expand those nodes.

Severe Errors:

You can see I’ve got the same 824 Error repeated 6 times. ( Well, I tried attaching the same corrupted ldf file 6 times for this demo)

Moderate Errors:

All errors by Frequency:

Is in’t nice? Personally, I liked this report…and thought of sharing what I’ve discovered with you folks 😀
Please ignore if you are already aware of this nifty report, if not…well, you know it now!

Obtaining Pipe Delimited Results from SQL Server using SSMS

In this short Blog post let’s see how to fetch results in a Custom/pipe delimited format file directly from SSMS without any fancy SSIS Packages involved. In fact you can use SSMS for fetching result set as with any delimited value.

As you all know with all the default settings of your SSMS without any options enabled/disabled, we’ll get results of any SELECT Statement in a grid fashion as shown below.

In the above screenshot I pulled few columns from “AdventureWorks” Database with the default Settings. You can copy the results including column headers and paste into excel and create a delimited file. Or if you are looking for CSV or Tab Delimited file, you can achieve without much pain by just right click in the result set and choosing “Save results as” and selecting the required file format. Let’s assume the requirement is to create a Pipe(|) delimited file with column names as the first row in the file, or some other custom delimiter in the file. You can’t see those options while saving the results by default. But, it’s very simple to achieve this just by turning on few options in your SSMS.

Go to Tools->Options->Query Results->SQL Server->Results to Text as shown below

In the top right corner drop down the list and select Custom delimiter as shown below.

Once you choose Custom Delimiter, you’ll have an option to enter the Delimiter as you can see below.(I entered “|” for Pipe Delimited files in this example)

Click Ok and now the trick is to select “Results to Text” in your SSMS while executing the Query and you get a result set with a clean pipe delimiters as you can see below:)

Or choose “results to file” and execute the same to save the results directly to a file. You can see in the below Screenshot where I’ve selected the required option in my SSMS and saving the file as “Pipe”.

Now, I’ve navigated to that folder and opened the file with notepad where I got the file exactly as I was expecting to be as you can see below 🙂

As you can see we don’t need any fancy tools to get custom delimited results…All it takes is exploring our own SSMS  🙂

Hope this helps!




SQL Server 2012 RC0 on Windows 8

Today I got some leisure time and thought of Installing SQL 2012 on my Win8 machine and sharing my experiences at a very high level. Well,  I’ve Installed SQL Server 2012 RC0 on my Windows 8(Consumer Preview – Evaluation Copy, Build 8250) Machine.

To be honest, I was expecting few bugs and annoying errors. But let me tell you this, My Installation went Flawless. FYI, I’ve Installed only basic components(DB Engine – with Replication, SSIS,Complete Client Tools, SDK Connectivity).

Just a screenshot of how it looks like on Win8. Well, Technically there is no difference in how SSMS looks and how it behaves, but as most of you are already aware of…there is no START Button on my Menu bar on my Desktop! See below on the bottom Left corner.

So, where did I open SSMS from?? If you said “Metro Screen” yes, you are correct. See the below Screenshot for how Windows8 organized programs on my Metro Screen.

Hmm…this is something which really disappointed me. Basically, It created bunch of Apps for each exe! Imagine how ugly your Metro Screen gets if you Install all the components of SQL Server!! I Wish it was smart enough to create at least folders/Groups and place related exe’s accordingly.

Btw, if you want to Run SSMS as Admin, just right click on the App and you will see the available options in the bottom of your Screen as shown below. ( This applies to all the Executables)

How Intelligent is SQL 2012 IntelliSense??

I’m assuming whoever reading this post is already aware of SQL Server 2008 SSMS brand new Enhancement “IntelliSense”. Being said that, was “IntelliSense” really Intelligent enough upto SQL Server 2008R2? IMHO, It’s not really smart enough to read our minds! But WHY?? Because, It’s not smart enough to recognize a word in middle of your Object name! For Example, if you need information regarding DB Mirroring Endpoints, you’ve to actually start your typing with  “SYS.DATABASE_MIRRORING_*******”. With all the new hundreds and hundreds of DMO’s available, it’s really difficult to remember each and every starting word of a DMO. It would be really nice if SSMS is smart enough to return all the Objects if it finds a word anywhere in the Object name. I mean, in the above example It should display a list of all DMO’s even though we just type “SYS.MIRR” irrespective of where the word “MIRR” actually present.

You know what, with SQL 2012 that is how it works 🙂 This Simply Rocks!! It makes our DBA life so simple…Yeyyyyyy 🙂

See below Screenshots for better understanding what I meant in the above paragraph.

SSMS 2008R2:

As you can see in the above Screen cap, it’s no where close to Mirroring DMV’s 😦 In other words, I’ve to remember the actual starting words in that DMV!!

SSMS 2012:

Here you can see a list of all the DMO’s which contain a word “Mirr” any where in the Object! This is what I call it as really “Intelligent”. This is something which is really really Cooooooool!!!

Denali Databases Status in Object Explorer

Howdyyy…Hope all is well folks! It’s been a long time since I wrote something….Was quite busy with some crazy stuff going around!! Anyways this would be a another small/Quick Blog post regarding a small enhancement msft offered in DENALI SSMS.

Okay, let’s jump into the content! I’ve created few SUSPECT databases on one of my SQL Instances and let me show you how DENALI CTP3 SSMS is showing in it’s Object Explorer and how SQL Server 20008R2 SSMS shows in it’s Object Explorer.

SQL Server 2008R2 SSMS:


As you can see, in DENALI the Database(s) are appended with (Recovery Pending) status which is more self explanatory where as in 2008R2 SSMS, the Databases are shown as nothing! Even though this is not great enhancement, it’s good to see this in our OE 🙂