A closer look at everything related to SQL Server


There is so much we miss out in this world and in our life by just not being present in present. We equate not doing anything to wasting time. In reality it is quite the opposite. I have experienced it myself and the feeling is so freeing and exhilarating that you will regret why you have not experienced it before. I think the old saying “idle mind is a Devil’s workshop” has misled us.

We are in this world for a short period of time and yet 50% of our time we are thinking about something that is not present. Weather we are listening to enticing music or watching a favorite TV show or reading an interesting book, we will not add these adjectives if we are not focused on what we are doing. ImageAn enticing music becomes irritable if some nagging thought in your mind keep bothering you. A favorite TV Show becomes boring if you constantly think about what you have to do next. An interesting reading becomes a chore if you are feeling anxious and stressful.

Actually I love all these 3 activities that I have used as an example here. So I have no problem on focusing on these activities. But there are other areas such as work and social life where I want to learn to apply this thinking. The benefit would be more satisfied business interaction which in turn will result in doing it again and again. It is just like a happiness cycle, once you start it it will get the momentum of its own.






Who absolutely look forward to annual performance reviews at work? I do not consider myself in this group. I hate the logistic part of it. Tons of rows to fill in. Sometimes questions are ambiguous or repetitive or just plain senseless. Long story short, I got my annual performance review and lets say it was above average but my manager suggested one development goal for me and it  was to get training on Advanced T-SQL queries.

Do not get me wrong. I am a DBA for more than 10 years and I can write tsql and keep myself updated with the addition and changes in tsql syntax with the new versions of sql server. I do lot of reading and researching in my areas of expertise (High Availability, Disaster Recovery and performance tuning) almost daily. I do not consider myself to be a DBA developer. I never have to write complex stored procedures or views or triggers etc. According to my manager, this is where I can grow more. Let’s keep the discussion of whether I agree with him or not for a later time. I planned to take this as a challenge. I have grown in so many areas, why not excel in this one too? Real challenge is to work on something which is outside of your comfort zone and you have to make yourself do it.

So I decided to blog my journey of Advanced T-SQL Learning. I searched the net but have not found any classroom training that meets my need. But there is lot of material (Blogs, YouTube videos, books, white papers, BOL etc ) available. And this was one of the reason, I never concentrated on learning development stuff because whenever I needed something I always find that T-SQL or PowerShell  script online written by MVPs and other very intelligent people of the SQL World (Wait this sounds like name of my Blog!)  available. With little tweaking I was able to use it and it definitely served the purpose.

Anyways, this is how I plan to learn and blog about Advance T-SQL. I will select one item such as advanced error handling or CTE or DMVs etc an d will work on each piece for a week. So I will write one blog per week for 4 weeks. Then I will summarize my 4 weeks of learning in the 5th week blog with examples or maybe little project or something fun. Then again I will start the cycle of learning to get to the next T-SQL concept.

As for now, my intention with this blog is that it is for my own eyes only. It will also serve as a reminder\repository\evidence etc of what I have learned and achieved. This will also be a tool down the road, when going gets tough, to remind me how persistent I was and as a result I will get new motivation to move forward.

Cheers to Learning and Blogging!



We know, that the TAJ MAHAL is a symbol of love, built by the Emperor Shahjahan because of his IMMENSE, UNBOUNDING LOVE, for his wife Mumtaz.

BUT, the lesser known facts are:-

1. Mumtaz was Shahjahan’s 4th Wife out of his 7 wives.
2. Shahjahan killed Mumtaz’s husband to marry her.
3. Mumtaz died during her 14th delivery.
4. After her death Shahjahan married Mumtaz’s sister.

Would you now look at Taj Mahal the same way as before knowing these facts?
Do History books ever tell you the facts ?

TSQL 2012 IFF Function

In a nutshell, IFF function is a shorthand for Case function. Here is the example to illustrate its use and syntax.

Create database myDB;
Use myDB;
Create table Employees (id int, name nvarchar(30), city nvarchar(30), region nvarchar(30), country nvarchar(30));

Insert some records to play with. Read the rest of this entry »

Here are the #TSQL2sDay prompts from Erin Stellato.

Here is how my day look on Thursday July 12th 2012.

7:00 AM

Came in 2 hours early to patch a critical sql server. Installation of 25 windows patches took 30 minutes.
Completing it with verifying everything another half hour. I used the waiting time to check my outlook inbox and my Hotmail emails.

8:00 AM

Replied to several emails that needed my attention. Spoke to my manager (offsite) about the issues
that happened yesterday as he and other Team members have to pitch in to patch and restart 12 servers yesterday. Those were my responsibility but I had an off day yesterday.

9:00 AM

2 days ago, I have ugraded 8 OLAP servers to SQL server 2008 R2 (SP1) from SQL server 2008. Today the team lead is saying that  they are experiencing and issue and it looks like a bug introduced by upgrade. He wanted to know how difficult it is to undo the upgrade. DBA’s life is always challenging in that way. So the normal looking day turned into an interesting one. I kicked myself into high gear and started my research.

10:00 AM

Attended a webcast on sql 2012 Availability Group from Pragmatic Works by Ross LaForte.

11:00 AM

 We recently had a new Dell SAN called Compllent attached to 3 servers each of them have a DB of 10 TB. They are exactly same and used for reporting. They are loaded from staging DB in parallel. Ops informed me they need to create a snapshot and I have to detach DB one by one on each serve. Steps involve: lien the server, detach db, stop sql server, ops did the snapshot, I have to start the sql server and attach DB. Verify that everything is fine. This database has monthly filegroups (for easier backup strategy) so need to make sure all FGs are attached. Let the attach create the new log file.

12:00 PM

Went to grab a sandwich and did my lunch at my desk. Logged my WebMD fitness and food log. Checked tweeter and followed couple of links to read if it fascinated me. Tweeted what I found interesting.

1:00 PM

 I have patched one server on Tuesday on which we use 3rd party high availability tool called Neverfail. It is very different then Microsoft failover cluster or Microsoft replication but both these terms are used in it. Neverfail cluster has 3 nodes namely Primary, Secondary and Tertiary. The replication Usually takes 20 hours (in our environment) to complete but it was still running after 60 hours. Opened ticket with them which they will Work with me tomorrow but in the mean time I gave 4 gb more to OS so replication can move faster. Reviewed Logs to find out any other hardware related issue. Also I have to do lot of communication with users of this server.

2:00 PM

 One development server was scheduled to be upgraded to sql server 2012 today. I had prepared by running upgrade advisor and informed the interested party about its report showing some issues with the code after the upgrade. Still they wanted to move forward with in place upgrade. But I have to postpone it after I received installation error because C: drive had only 2 GB free (total C: drive was 25 GB, old 2950 dell server). Requested Ops team to create a VM replacement for this server. There was nothing that can be deleted from C: drive. SQL 2008 R2 install was on C: drive and sql 2012 in place upgrade install needs atleast 6 GB of free space on C: to work. My plan is to install sql 2008 R2 on VM. Restore user databases and logins and jobs and linked server and whatever packages user need to migrate. Then do the in place upgrade to SQL 2012.

3:00 PM

Helped Junior DBA (remote), hired 2 months ago, to install sql server in a step by step fashion according to our 

SOP. Helped her via IM, phone and email.

4:00 PM

 There were few requests pending since morning to give permission on certain object to certain groups and users. I completed that and informed the users. Next I completed this log to record my day today.

5:00 PM

 Wrote email to ops to change the registry setting on one of our backup servers. I have researched the errors on Tuesday and had 2 documents prepared. Over the weekend most of our backup jobs run on most of our servers and goes to one backup server. Sometimes backups (usually transaction log backups) fail with OS error 87 ‘setendoffile’ that end of file not found. Apparently the error occurs when OS estimates certain space for backup but the compressed backup actually takes different amount of space so when OS tries to correct the end of file it sometimes times out. Solution we will try is to add a registry key of SessTimeout = 300 seconds. Default is 60 seconds.

Who does not want to accomplish more in a less time? If you don’t then I doubt you will be reading this. Trick is to be selective. If time is a constant and you want to accomplish more, here is the equation comes to my mind.

More work done = Time X Selectivity

Since Time is a limited quantity and you can only increase it so much and very soon it becomes a constant. So your only option (according to my equation) is to increase your selectivity. And what is selectivity? In my opinion, weeding the junk out of your life and really know what are few most important things in your life is the selectivity.

It is easier to say remove junk out of you life and choose only important things. But this is actually the crux of the problem. How we do that? It is not something you can do in an hour or a day. It requires deep thought and concentration. It requires you to go through your motives, pros, cons and values for each thing you are currently spending time on. This requires you to record them in some way such as on a sheet of paper, on iPad, smartphone or simple old spreadsheet. Also you need to go over it multiple times. Maybe you need to include and ask for advice from your significant other or someone important in your life. Beware, you don’t live in a vacuum. Your decision to select what is important is going to affect you as well as the people around you.

There are many books, blogs and tools available that can help you sort things out in your life. Here is the one simple tool http://www.mindtools.com/pages/article/newHTE_91.htm. I will not bore you by repeating what is written at this link. But I will assure you that it will help ease the process of beginning to identify junk in your life and once you get your focus, you may or may not need to use this tool.

So there is no trick or magic to get more work done in a limited amount of time. It is the same old focus and organization to do the task which are most important to you that will give you the most satisfaction.Once you have gone through this difficult process of selecting most important things and applying your time and energy towards them, you will feel more satisfaction and you will feel more accomplished.

Also note that in the above equation I have used the operator of multiplication instead of an addition. Because even if you increase your selectivity by a small number, the effect of satisfaction of getting work done will be much higher.

Wish you best of luck.


“Everyone knows what attention is. It is the taking possession by the mind in clear and vivid form, of one out of what seem several simultaneously possible objects or trains of thought…It implies withdrawal from some things in order to deal effectively with others.” (Principles of Psychology, 1890)


Get every new post delivered to your Inbox.