Year: 2012

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!

SQL Server 2012 CU1 Released!!

Microsoft has released CU1 for SQL Server 2012 RTM within 3 weeks of the initial release of the product, which has more than 50 fixes! This is something really interesting to me which I’ve never seen(at least I don’t remember) where they had fixes for ~50 bugs in a Cumulative Update(not Service Pack).

You can get more details(what got fixed what are the enhancements) and download from http://support.microsoft.com/kb/2679368

Cheers!!

Windows Drive(s) Space Statistics – Few useful Tools!

Hey Folks! Let me introduce you to couple of my personal favorite tools for quickly understanding the space distribution on your Hard Drives on your Windows Servers. I Bet, almost all of us might have been in a “Drive running Out of space” situation at least once in our career. This is one ugly situation to be in, especially if that’s the very first time you logged onto that box during your On Call Shift and You’ve no idea what files are sitting there on Drives! So, what are the tools which helped me in gathering this info in just few minutes or even seconds, with just few Mouse Clicks??

1. WinDirStat ( Windows Directory Statistics)

2. SpaceMonger

Let’s see WinDirStat First, Let me tell you this, It’s a free tool from Sourceforge, which is aweesome 🙂 you can Just Bing it or google it or visit http://sourceforge.net/projects/windirstat/ for more info.

I’ve Installed this on one of my Test Servers. Let’s see how it works….As soon as you double Click on the executable, you’lll be welcomed with below Screen.

As you can see, it gives us options to Scan all Drives, Individual Drives or even at folder level. I’ve chosen “C:” for illustration purposes. Once the Scanning is complete, It gives a colorful report which is actually interactive(you can directly go to a file/folder just by clicking on a block whichever appears to be largest in the image).

Let’s explore the “Show FreeSpace Option” by looking at my SQL Data drive. You can enable this option by hitting F6 or enabling from Options Menu as shown below.

Before proceeding any further, I’ve a empty database named “space” which is 1.5 GB in size. See below how it looks from SSMS and from My Computer. Basically 1.5 Space has been reserved by SQL Server and OS sees it as 1.5 GB full.

From SSMS:

From Windows:

Now Let’s see from WinDirStat by enabling ShowFreeSpace Option.

As you can see, you can see freespace available for you and other large files as large blocks; visually which is much much easier to analyze, especially If you’ve option just to click on the Graphical Block and navigate to actual Folder/File.

What About Junction points?? Yes, This guy is smart enough to deal with Junction points as well! See below for all the options you’ve with this tool. ( you can configure this tool by navigating to Options->Configure Windirstat).

Navigate through the rest of Options to explore the rest yourself!

Now, let’s see spaceMonger tool….This is a similar tool which can serve almost the same purpose. See below snippet for how this tool looks like once installed on the server. You can see nice Folder hierarchy….

Statistics Tab gives us detailed info of the selected Drive as shown below

I see, SpaceMonger is Much Much Faster to collect the stats when compared to WinDirStat. SpaceMonger also has very good customization and very well integrated with Windows Inbuilt Tools as shown in the below Screenshot…

Hope, these tools will be useful for at least few of you in your day to day support or you can Install them on your Laptops! I’ve WinDirStat on my Laptop, since it’s an absolute free tool where as SpaceMonger costs a few bucks 😀

What’s my SSAS version??…

How to verify your SSAS Version? Remember, we can’t run Select @@Version connecting to SSAS. Let’s see couple of ways where we can get this info.

By Connecting to SSAS from SSMS:

Right Click on the Instance and navigate to Reports->General as shown below.

We can see version information as shown below.

Or…2nd Method: Simply navigate to the folder where SSAS binaries are Installed and locate “msmdsrv.exe” and simply hover your mouse!…In my case it’s “C:\Program Files\Microsoft SQL Server\MSAS11.SQL2012\OLAP\bin”. See below

You can use the 2nd method when SSAS Service is stopped.

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)