Archive for November, 2014

I came across this scenario many times when you need to find the largest tables in the database, specially at the time of maintenance or support. Fortunately we do have a solution in the shape of different scripts that you can easily find in many blogs and sites. In fact, I have written a solution earlier. In this article, I will discuss how to find the largest object without writing a single line of script.

Let me explain it step by step by step.

Step 1 :
First, open SQL Server Management Studio (SSMS) and select the target database (where you need to find the largest object) and right click on it as shown below.

Find largest table 01

Step 2 :
Once you right click on the target database you will see a lot of options. Select reports \ standard reports \ Disk Usage by Top Tables as shown below. There are other very useful reports that give you the statistical data about your SQL Server.

Find largest table 02

Step 3 :
Once you click on reports \ standard reports \ Disk Usage by Top Tables, SSMS will open a report showing which tables are using more Disk spaces, reserved space & having highest record counts etc as shown below.

Find largest table 03

Step 4 :
Now you have the report and by default it is sorted by Reserved (KB), but you can sort it with any column (# Records (Actually record count), Reserved(KB), Data(KB), Indexes(KB), Unused(KB)) by clicking on up and down sign as shown below.

Find largest table 04

The beauty of this feature (Standard reports) is that you can generate the statistical report within few seconds, just by click of a menu. In addition you can export / print this report in a nice format without any extra effort as shown below.

Note : Please do not forget to refresh this report before export or print ;).

Let me know if you tried this solution and how did you like it ?

Read Full Post »

Today I had a very informative session in SQL Server community meet up at Microsoft Office in Dubai, UAE. This usually occurs every last Tuesday of the month. I must appreciate the SQL Server User group (www.uaessug.com) who have been organizing it very well for the last one year. This meet up usually consists of two comprehensive and informative sessions that focus on the SQL Server latest technology, best practice, user experience, tips & tricks. I usually attend this meet up because I get an opportunity to network with SQL Server experts face to face instead of Facebook chat :). I was the speaker in today’s meet up along with Mr. Mohammed Owais (CTO at CAZAR).

Following topics were covered in today’s session:

  • UDFs are evil and other execution plan nightmares presented by Mr. Mohammed Owais
  • The easy path to compliance with SQL Server Audit presented by me

We focused on delivering the latest technology enhancements, best practices and troubleshooting (the scariest part) throughout the event and hands on with live demos to demonstrate the real world challenges and its solution.

I hope today’s session was informative and believe that everybody got to add something to their knowledge-village 🙂
Our next meetup will be in January 2015.

Some pictures from today’s sessions.

Mr. Mohammed Owais busy explaining UDFs execution plans :


Me presenting SQL Server Auditing :


Group discussions :






Mr. Waqar discussing BI with Mr. Kevin :

Thank you guys.

Read Full Post »