Month: May 2012

PetaBytes(6000 TB) SQL Baby – Single SQL Server DB Backup File!!!!!!

Well, Today will be one of my Most memorable days in my SQL Server Career! Anyone Curious Why?? We got a Database Backup from one of our Vendors sizing 6 PB!!!!@$@$$%$@!!!! Yes, You are reading it right. It’s 6000 Tera Bytes! Let me tell you guys the story in short…

It was  just an another typical day for our DBA team and we all were busy with some routine tasks and some ongoing projects…All of a sudden, one of our DBAs was all excited to see a Backup file on a Network Share with a size of 6PB and..we all went to his cube for double checking the size of file what windows is showing. Yes, he was correct…That’s the largest Backup file(Well, any file in that matter) which I never saw in my entire life so far!

See the Screenshot below….

Please Ignore the Ugly Black Spots and just look at the size 😀

Well, am not sure if we could ever restore that file with native methods. First of all, is it a valid Backup file? Am not sure! What in the world are they storing in that Database? I Don’t Know…Even I’m very very curious to see what it turns out ultimately. I’ll chase my fellow DBA for sure to see what is this all about 😀

I just couldn’t stop sharing this with you all before I get more info on this. I Bet, this is the largest Backup File you might’ve ever seen(for maximum viewers of this post)!

If this is a valid Backup file and If the Database Size is really 6 PB, Then, I can proudly shout, our team supported one of the worlds largest SQL Server Databases 😀

I can’t even imagine how to maintain such a huge Database with all routine Maintenance tasks which a typical SQL Server Database needs!!!

SQL Live Monitor – Great Free Monitoring tool from Codeplex!

How many times do you use DMV’s daily supporting your SQL Server environments? How many of you rely on 3rd party tools for looking at the current snapshot of your SQL Server? Let me say this, I use DMV’s a lot even though I’m provided with fancy 3rd party monitoring tools. Each approach has it’s own pros and cons! Let’s assume our shop has no 3rd party tools for monitoring and we are just relying on native SQL Server mechanisms for all our monitoring. In that case, I can say 90% of the monitoring/Gathering Info will be done just using DMV’s. What if we’ve a free tool which displays the DMV results(of course, not everything..but all the useful info at a single glance)  in a pictorial representation? That would be awesome right?? Well, most of the cases, I feel seeing something visually is much more self explanatory and efficient rather than just looking at figures and numbers. YMMV!

SQL Live Monitor can do the same for you. It’s a free tool available from CodePlex community. This tool don’t even require any Installation. It’s just an executable(222 KB) which runs on fly without installing anything on your Servers. Isn’t it cool??

Let me show you how it looks like when I run against one of my Lab Servers…I copied SQL Monitor exe to my Server as you can see below and I just double clicked on it….No other Fancy Stuff needed!

Immediately I was welcomed with the following Screen.

As you can see initially it’s not connected and It’s asking for SQL Instance Name, whether it’s a cluster or Standalone, If it’s clustered…what’s the current active node for your Instance….etc. In my case, It’s a standalone box. Once I selected my Driver(In my case it’s 2008 Native Client) and provided with Instance name and clicked Start…It is providing me all the cool info as you can see below…

Awesome….! Undoubtedly this is one of my favorite tools from codeplex 🙂 Yes, this is not as fancy as other 3rd party tools out there, but still it does the justice and serve the purpose with much much less pain!

I hope this tool will be helpful to atleast few of you while investigating your Servers….Btw, you can download this for free from

MAP(Microsoft Assessment and Planning Tool kit) 6.5 for SQL Server Discovery

Hello Folks! In this Blog Post let’s see how to make use of MAP tool for discovering all of your SQL Servers in your Environment. MAP is one of the wonderful tools(free) provided by Microsoft, which basically scans all over your Network and present you with a wonderful report on all of your Servers. They could be Windows Servers, Linux Servers, SQL Servers, Oracle, MySQL. Many companies use this fabulous tool for planning future road maps(Could Be Server Upgrades, Desktop Upgrades, MS Office Suite upgrade and what not), consolidations, upgrades, Licensing etc. In My Organization, we are planning to move on to SQL 2012 very soon and we need a report which basically gives us a clear and simple picture of our current SQL Server Environment and I was assigned to achieve this goal 🙂

Sooo….., let’s get started. First thing you’ve to do is download MAP ToolKit from below location

Once downloaded, choose a Machine in your Domain where you want to Install MAP tool and start Discovery.

Note: MAP tool Installs SQL Server Express by Default for storing it’s Results.


I’ve chosen “Mirror_Stand” machine in My Lab Domain to Install MAP. I copied over executable  to this box as the 1st step. Right click on the “Microsoft_Assessment_and_Planning_Toolkit_Setup.exe” and choose Run as Admin and you should be seeing the following Screen. (Make sure to Install all the Prereq’s. If not, you’ll be welcomed with an ugly X message as shown below!)

Once I finished meeting all the Prereq’s, I’ve opened the exe and now I’m welcomed with the below Screen 🙂

Click Next-> Accept License Terms and Next->Select your Installation Folder(I left it to default) and click Next. Now comes the interesting Screen where it asks for how do you want to Install SQL Express(see below Screenshot).

If your Box is connected to Internet, Just select the 1st Radio Button. If not, select 2nd option and provide it with the SQL express Installation media path.  I’ve chosen 2nd option and provided path for my SQL Express 2008R2 as shown below.

Once you read and accept the license terms, you should see a similar screen as below.(In My case, I already have a SQL Server Installed on this Machine, hence you can see “New Instance” being stated by the Installation wizard).

Click on Install and go and grab a cup of coffee :). It’ll take a while!  As you can see, my Installation is successful now.

It Installed SQL Express Edition Instance on my machine with Name “MAPS”. See below Screencap

We are done with the Installation. Now, let’s see how can we discover SQL Servers across our Domain using this Tool.

Step1: You will be welcomed with the below screen where you’ve to create an Inventory DB(I named it as “SQL_Inven”) for the First time. (You can create separate Databases for separate Discoveries, eg: One DB for SQL Servers, another for Exchange, another for VMWare Servers etc).

Step2: Select on Databases and Choose to perform an Inventory as shown below.

Step 3: Select SQL Servers to discover your SQL Servers as shown below.

Step 4: Choose a Discovery Method. I’ve left to default to AD as you can see below.

Step 5: Provide domain Credentials and select which parts of your AD Forest to scan for as shown below.

Once you proceed upto Finish, it automatically starts gathering info as per the options you’ve chosen. It will take a while for gathering all the required info…

Once it finished, you can see a neat and very useful summary of MAP assessment of your SQL Servers as shown below.

Don’t say it’s useless…It’s actually wonderful Summary of your Environment. For detailed Reports, gather the performance metrics as well and click on “Generate Report/Proposal” on your Tasks Pane to your Right. Once finished it’ll create couple of excel Reports which are very very detailed and useful…You’ll find those reports in your documents folder. In My case, it is “C:\Users\sreekanth\Documents\MAP\SQL_Inven”

Nice thing is, It’s agent less. All it needs is an Account name which has access to the Servers and the Servers should be Up and Running while It scans.

Hope this is informational and useful. Cheers!…

How to Truncate/Delete all Tables in a SQL Server Database?

How to Truncate/Delete all Tables in a SQL Server Database in three simple steps????

Howdy! In this post let’s see how to truncate/delete all the tables in a given SQL Server Database and few important considerations while doing so.

Today, I had a request from one of my Application teams to copy a database from Prod to QA and truncate all the tables. It was a very small Database(7.2 GB) but had 250+ tables with manyyyyyyy Foreign Keys. So, I had to deal with Constraints before SQL Server allows me to truncate any table which is being referenced by some other table. Basically I’ve to follow the child-parent hierarchy to do so. I started with scripting out all the Foreign Keys and thought of dropping all of them and do a truncate and then recreating. Well, immediately something came to my mind….”How about Disabling Constraints and do my work and then re-enabling Constraints”

Imp Note: We can’t Truncate tables with just Disabling Constraints. SQL allows only if you drop them physically.

So, what is the Option I’m left with. Well, Delete is my weapon now.

How I did this was..

Step 1:  Disable all the Constrains

Exec sp_msforeachtable “Alter Table ? NOCHECK Constraint all”

Step 2: Delete data from all the Tables

Exec sp_msforeachtable “Delete From ?”

Step 3: Re-enable all the constraints which we disabled in Step 1

Exec sp_msforeachtable “Alter Table ? With Check Check Constraint all”

I Know, I Know….sp_msforeachtable is old school technique and deprecated, but am Okay as long as I Know there’s no harm using it and making my life easy 😀

So, what should we keep in mind if we are considering this approach??

The Very First thing is your Transaction log. How much room do you’ve for your T-log to grow is single most important consideration for this approach. Because Step2 will run as a single Hugee…..transaction. Let me say this in other Words, My Database was in SIMPLE Recovery Mode and I was monitoring the T-Log constantly..It grew upto 15.4 GB for doing this Delete(and the entire Database Size was 7.2 GB). Just Make sure….you are good here before taking this approach for a Medium-Large Database. Once you are done with this, do not forget to bring down your T-Log size accordingly.

Hope this helps!