The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

MSDN Webcast on Optimizing and Tuning SQL Server full-text Search (FTS)

Posted by Sudarshan Narasimhan on October 4, 2011


In May 2011, I had delivered a live webcast on the topic “Optimizing and Tuning Full Text Search for SQL Server”. You can download the slide deck (PPT) and Video from MSDN Event library. I am sharing it here since the webcast was before the birth of theSQLDude. Refer to the links below for downloading the presentation deck and webcast video.

Note: This talk covers using Full-text search only on SQL Server 2008 and above. Nerd smile

Watch the Webcast

SQL Server Full-Text Search: Tips for Optimizing and Tuning Search for large environments from sudarshan on Vimeo.


Presentation Slide Deck (PPT)

You can download the slide deck along with the sample/demo script from [HERE]. It is also available on [THIS POST] @ SQLServerFAQ.

Posted in Webcast | Tagged: , , , , , , , , , , | Leave a Comment »

The Dabbling Developer Series: Part1-Windows Service Management

Posted by Sudarshan Narasimhan on September 30, 2011

Now and then when I get some time I like to spend it writing code and I usually like to relate this to some work/activity that helps in my main work i.e. SQL Server. I have many small single-purpose applications/utilities written over time and I realized that having them saved on my PC is of no use. Don’t be surprised if you find C/C++ code samples posted on TheSQLDude. For the time being let me be the TheCDude and Long Live Dennis Ritchie & Ken Thompson!


Service Control Manager Test

I wrote some code to test if I can open the Windows Service Control Manager (SCM) for the local machine or remote machine with the current logged-in account. This helps me validate permissions or spot other errors when performing service related activities. For those of you who don’t know what SCM is read this article.

SCM is a system component that takes care of service management, service start-up and other service related activities.

You might ask, How did I come to care about SCM all of a sudden? What the hell does this have to do with SQL Server?

Let me explain. I came about SCM when a customer of mine came up with a SQL Server Setup issue. SmileWhen SQL Server setup goes about installing the various SQL Server components, one of the things it has to do is to create the SQL Server services like SQL Engine, RS, AS, Browser service, SQL Writer service etc. Obviously if setup is not able to do this, it will fail. For someone to create/delete/modify a Windows service, they need to talk to the Service Control Manager (SCM). How do you isolate whether the problem is with SQL Setup or some other component? This is how I ended writing this code to test SCManager.

If you want to perform windows service related activities, it works like this:-

1. You need to open the SCManager, which will let you access its database which has the list of registered services

2. If this succeeds, then depending on the operation you can either Open an existing service and modify/query its properties or create a new service and register it with SCM.


The code below is simple C code written on Visual Studio 2010. This has no copyright so feel free to re-distribute.

// scmantest.cpp : Defines the entry point for the console application.



#include "stdafx.h"

#include <windows.h>

#include <tchar.h>

#include <strsafe.h>

#include <stdio.h>

#include <conio.h>


#pragma comment(lib, "advapi32.lib")

#pragma comment(lib, "Kernel32.lib")


int _tmain(int argc, _TCHAR* argv[])


 if (argc < 2)


  _tprintf(_T("Usage : %s name_of_machine_to_test"), argv[0]);

  return 1;



     //CHAR lpMachine ;

    BOOL bReturn = FALSE;

    SC_HANDLE hScm;



    // Check that we can open the SCManager on the machine

    hScm = OpenSCManager(argv[1], NULL, SC_MANAGER_ALL_ACCESS);

    if (hScm)


        bReturn = TRUE;

        printf("OpenSCManager completed successfully\n");        





        _tprintf(_T("Could not OpenSCManager on %s. GLE = 0x%08x\n"),argv[1],GetLastError());

        return 1;




 return 0;



The above code tries to Open the SCM and if that fails then it reports the Win32 error. You can Bing! this error to find out what action to take. The application scmantest takes 1 parameter which is the Machine Name. So if you PC is called, then you would run this from the command prompt like this


Here is an output for a non-existent machine and you can see that the Win32 Error reported is 0x000006ba (The RPC server is unavailable).



This can help isolate permission issues when trying to open SCM database as you need to have Administrator rights. Sometimes certain permissions to create might be missing which you will have to grant.

If you are a Windows Admin reading this then here are some command-line instructions to operate on Windows Services

1. Query the state of an existing Windows Service – sc query ServiceName

e.g. Checking state of default instance of SQL Server.

        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

If you specified an invalid service name, you will get this error,
[SC] EnumQueryServicesStatus:OpenService Failed 1060:The specified service does not exist as an installed server.

2. Create a new Windows Service – sc create …

Let me take an example of creating a SQL Server service

e.g.  Creating a Default instance of SQL Server

sc create MSSQLSERVER type= own start= demand error= normal binPath= D:\MSSQL\MSSQL.1\MSSQL\Binn\sqlservr.exe obj= LocalSystem DisplayName= "SQL Server (MSSQLSERVER)"

type –> Type of service you want to create. Leave it at OWN.
start –> Start type for the service. You can have it at automatic, disabled. We choose demand which means manual start-up.
binpath –> Complete path to the sqlservr.exe
obj –> Startup account for the service.
DisplayName –> can be anything, this is what services.msc will show us.

a) There must be a space between ‘=’ and the value.
b) Double-quotes are mandatory is there is a space in the path.


For those of you who didn’t know this, the windows service entries are present in registry in HKLM\SYSTEM\CurrentControlSet\Services\. This is the same location from where services.msc reads and displays the list of services.


3. Using SUBINACL to check permissions for Windows Services:

SubInACL is a command-line tool that is available for download here and shows you security information about files, registry keys, and services.

e.g. Query permissions on the service for default SQL Server instance

subinacl /verbose /service MSSQLSERVER /display

/owner             =system
/primary group     =system
/audit ace count   =1
/aace =everyone         SYSTEM_AUDIT_ACE_TYPE-0x2
/perm. ace count   =6
/pace =system   ACCESS_ALLOWED_ACE_TYPE-0x0
        SERVICE_START-0x10                 SERVICE_STOP-0x20                  SERVICE_PAUSE_CONTINUE-0x40        SERVICE_INTERROGATE-0x80
        READ_CONTROL-0x20000               SERVICE_USER_DEFINED_CONTROL-0x0100
/pace =builtin\administrators   ACCESS_ALLOWED_ACE_TYPE-0x0

4. Granting permissions using SUBINACL

e.g. Grant Full Control to TestUser1 on the default SQL Server instance’s service.

subinacl /service MSSQLSERVER /Grant=Domain\TestUser1=F
MSSQLSERVER : new ace for anselm\administrator
MSSQLSERVER : 1 change(s)

Other useful Resources

Using SC.EXE to Develop Windows NT Services

How to create a Windows service by using Sc.exe

I hope this post was useful for both Developers as well as Administrators. Would appreciate any feedback on this post since I am thinking of posting other useful code samples in the future. As always stay tuned for more geeky info….


Posted in Code Samples | Tagged: , , , , , , , , , | Leave a Comment »

Getting legacy DTS Designer working with Management Studio for Pablo Picasso’s

Posted by Sudarshan Narasimhan on September 16, 2011

There are many of you SQL Developers and wizards out there still working on legacy DTS packages. Of course, we cannot live without SSMS (SQL Management Studio) and thus try to integrate the legacy SQL 2000 DTS Designer into it. But, life is not that easy and the entire procedure to get DTS Designer/Runtime working on SQL 2005/2008/R2 Management studio is quite cumbersome.

While I was working on setting this up myself, learnt quite a few tricks which I what this blog is going to talk about. At the end of this you should be able to both import, design and execute legacy SQL 2000 DTS packages (not SSIS) on a machine that has SQL 2005+ Management Studio installed. Read on and let free your picassonian designer skills with DTS packages.

Getting Started
First off, here is the link to the official documentation that talks about the support for SQL 2000 DTS packages on SQL 2008 R2. There are 2 types

1. Run-time support  (meaning I can execute DTS packages using dtsrun.exe)
2. Design-time support (meaning I can import, open & design DTS packages in SSMS)

How to: Install Support for Data Transformation Services Packages

Read the above article to get an understanding of what is possible and what is not (Don’t start/install anything yet) as this process can be quite tricky. My friends @ SQLServerFAQ have a nice post on this which is also something I would recommend reading here.

Ready, Set, GO!


Step-by-Step Instructions

1. You would need to install a few additional components, so please download the following packages and keep them ready.

a. Microsoft SQL Server 2000 DTS Designer Components

Download the package SQLServer2005_DTS.msi

b. SQL Server 2005 Backward Compatibility Components

Depending on the version of your SQL Server Management Studio (SSMS) you will have to download the corresponding package

SQL 2005 –

SQL 2008/R2 –

Download the package SQLServer2005_BC.msi. If you have a 64-bit machine, then you would need the x64 package because the x86 package will not let you install and you will get this error.


c. Make sure when you had installed Management Studio that you have chosen “Management Tools – Complete” during the setup for SQL 2008.


2. Install both the above packages in the same order as listed above. Once this is installed you will see that the following paths containing some DLL’s

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn \
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\

3. Now, you need to edit the system PATH to ensure that the path for “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\” comes before the ..\90\Tools or ..\100\Tools folders. You can do this by going to My Computer –> Properties –> Advanced –> Environment Variables and editing the value for "PATH” as shown below.


Here is a sample path value from my working machine.

PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Windows\System32\WindowsPowerShell\v1.0\

4. Okay, now comes the manual part. This step is the same as given in the MSDN article.


32-bit Machine (x86)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

64-bit Machine (x64)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

On a system with default settings,

%ProgramFiles(x86)% –> C:\Program Files (x86)\
%ProgramFiles% –> C:\Program Files\


5. After this is done I would suggest a reboot of the machine to ensure that if any pending file renames are present, they will be completed on a system restart. This is not mandatory, but just a suggestion. If you cannot reboot, then you need to close all instances of SSMS and re-open to test opening a DTS package.


From SQL Server Management Studio, navigate to the Legacy folder in Object Explorer as shown below


If all goes well, then when you open a SQL 2000 DTS package (test.dts), then you will see the legacy DTS designer open up as shown below.


Still Not Working
If things go wrong, then you will get this error when you tried to open a DTS package,


Error Text

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

If you have faithfully followed the above procedure step-by-step then you will need to perform this additional step as well on those systems where you receive the above error. This is why I have not added this step as default since it does not occur on all systems.

1. Navigate to C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn and see if there is a file called stardds.dll present there.

2. Note down the file version, date and size.

3. If your Operating System is Windows Server 2008/R2 or Windows Vista/7, then you will need to replace the file present with the one given below. This is because Windows 2008+ (including Windows 2008 R2) requires the 2005.90.4035.0 version of stardds.DLL. Windows 2003 or earlier requires the 2000.80.2151.0/2282.0 versions of stardds.dll

a. Rename the existing stardds.dll to stardds.dll.original

b. Download the version of stardds.dll that applies to your OS

For Windows 2008/2008 R2/Vista/Win7 –> Download stardds.dll.4035  from HERE
For Windows 2003/2003 R2 –> Download stardds.dll.2282  from HERE

c. Copy the downloaded DLL to the location C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\ (for 64-bit systems) OR C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ (for 32 bit systems) and name it as stardds.dll

4. Close SQL Management Studio and re-open it and repeat the process of opening the DTS package and it should work now.

Caveat for DTS Packages contains Analysis Service (AS) Tasks
Apart from this, if any of the DTS packages have Analysis Services Cubes then you will still not be able to open these packages in SSMS. You will be faced with this error message

Error Source : Microsoft Data Transformation Services (DTS) Package
Error Description : Invalid class string

The backward compatibility package do not include design-time support for modifying DTS packages that contain the SQL Server 2000 Analysis Services Processing task and also do not include support for the SQL Server 2000 Data Mining Prediction Query task. So, if your DTS package has either "AS Processing Task" or "Data Mining Prediction Query task", then we cannot open/design these packages in SSMS 2008/R2

If you want to be able to do so, then you will have to install the following SQL Server 2000 components from SQL Server 2000 media

1. Analysis Manager
2. Decision Support Objects
3. Client Components

You don’t the AS Server component, just the above will suffice. Once you install these components you then did the get the following DLL’s present on the machine,

msmdtsp.dll   –> for Analysis Services Processing Task
msmdtsm.dll  –> for Data Mining Prediction Task

Close and re-open SSMS and you should be able to open the package now.


Phew! This was quite a long post indeed but I think I have covered most issues one could run into. If you come across any other issues, leave a comment behind and I will try and see if that can be resolved.

As always stay tuned to the TheSQLDude for more….

Posted in Tools | Tagged: , , , , , , , | 42 Comments »

Did you know–SQL 2000 isn’t supported on Windows 2008+ ?

Posted by Sudarshan Narasimhan on September 1, 2011

I’m not sure how many of you are aware of this but I happened to talk to quite a few DBA’s who were hell-bent on installing SQL Server 2000 on Windows 2008 (don’t ask me why). But for those of you who are planning to do this, DON’T, because it isn’t supported.

SQL Server 2000 and below are not supported on Windows Vista/Windows Server 2008 and above (which includes Windows 7/Windows Server 2008 R2). Here is the official blog post by the SQL Releases team on this,

Stay out of trouble and upgrade Smile.

Posted in General Thoughts | Tagged: , , , , , | 1 Comment »

Trust No One and always ask “WHY”!

Posted by Sudarshan Narasimhan on August 13, 2011

Another good post by Paul Randal. It’s worth a read and I agree with his thoughts on Forums filled with “so-called” experts. The WWW can be a dangerous place for those looking for quick solutions. Don’t be misled into implementing something you will regret later or end up being someone else’s worst nightmare. Always ask yourself “WHY” instead of just focusing on the “WHAT TO DO” when looking for suggestions/solutions. This is a crucial part of logical troubleshooting which most people ignore. So do yourself a favour and ask “WHY” when someone asks you do to make this/that change. If they can’t explain “why”, well rest assured it is not 100% guaranteed to solve your issue. Even if it did, you still don’t know “why” it did.

Like the warrior in 300 says “Remember us, Remember WHY we died

Do yourself a favor… Trust No One

Posted in General Thoughts | Tagged: , , , , | Leave a Comment »

Demystifying SQL Server Servicing (Patching, Security Updates and Service Packs)

Posted by Sudarshan Narasimhan on August 11, 2011

Today I am going to talk about the different patching options that are available with SQL Server and how they work. Often people get confused and assume that there is only one single sequence of updates for SQL Server, starting from a lower build number to higher build. This is not true, because there are different branches for patching and different types of patches released. Let me try and clarify a few of those common assumptions/misconceptions today.

SQL Server is a large product (well, it’s a Database server after all) and so requires a different patching mechanism to other products like Windows.

Types of SQL Server Patches (Product Updates)

1. Service Packs (SP):

Consider this as a consolidated list of different updates bundled into one large package. Typically, there are 2-3 Service Packs for the lifetime of a product. A typical product is in mainstream support phase for roughly about 5-6 years after which it goes into extended support phase. So if a service pack is released once every 2 years, that makes it 2-3 SP’s for the lifetime of a product.

There are exceptions to this. E.g. SQL Server 2005. This has 4 Service Packs released and probably because it was so popular that people were comfortable using it even in extended support phase instead of upgrading to the next version. Hence, this required SP4 to be released to cater to the community base that was still using SQL 2005. Makes business sense, doesn’t it? 🙂

I don’t want to get into the details of the different support phases. But you read the simplified explanation of this in Bob Ward’s blog post here.

2. Cumulative Updates (CU):

To put it simply, Cumulative updates are released once every 2 months for each branch of the product that is in mainstream support [I’ll explain about “branches” shortly]. The SQL Server release team shifted to this “Incremental Servicing Model (ISM)” some years back to make it easier for administrators to predict and plan for updates. This fits in nicely with change control and downtime planning, to apply these cumulative updates.


This is where most people get confused. I said earlier that CU’s are released, but I wasn’t totally honest. Cumulative Update packages are release for a Service Pack. Still don’t see the big picture? Let me explain. After each Service Pack, a CU is released every 2 months and this CU is only applicable for that particular service pack branch. E.g., CU2 for SP1 != CU2 for SP2. You may ask, why is this stuff so complicated?

My answer: Think Linear, cause Time flows in only 1 direction. Smile

Let me explain this with a practical example. After Service pack 1 is released, lets says 10 cumulative updates were released which makes it roughly 2×10=20 months. Now, its time for a new Service Pack. Once Service pack 2 comes out, we have SQL Servers that are in RTM build, SP1 build and others somewhere between SP1 CU1 to SP1 CU10. Can all of these be upgraded to SP2 immediately? Obviously not! People do take time to test in QA/Dev/Test etc.. before a service pack is applied (all of us go through this as part of our jobs, don’t we). Now as time goes on and 2 more months are past, the next CU for SP2 comes out. And this is exactly what Branching is all about. We now have SQL Servers that are on the base RTM, some on SP1 branch and some on SP2 branch.

You might have noticed that once a service pack is released, cumulative updates continue to be released for older service packs. Why? Think back to the mainstream vs. extended support phase. Till a service pack goes into extended support phase, Microsoft has a legal obligation to continue to service that branch of the product. This tends to be the confusion for most people, but if you understood Branching, then you will understand why CU’s are released even when SP’s are also being released.

Want me to complicate things further? The strong minded may read on …

Quirky Scenario:

You have a SQL Server instance on SP1 CU5 build. You hit a known issue (Bug) which you find out is fixed in CU6. Great! You test CU6 on your test system and the fix does solve your issue, so you implement CU6 on your production server. Life is good!

A month later, a new service pack (SP2) is released. Your boss wants you to immediately apply this SP2 on Production (due to company policy, compliance blah blah blah…). The dutiful employee that you are on, you successfully apply SP2 on Production and find out that the old issue is back again. WTF!

Think about this carefully and you will see the answer.

Service Pack 2 released just a month after CU6 was released. I mentioned that a CU is released every 2 months, which means your CU6 must have been developed/tested 2 months + 30 days = 3 months back by Microsoft. A Service Pack is considered a major upgrade, so obviously the development/testing time would be more for a SP vs. a CU. Let me point you to a piece of documentation most of you would have missed.

image(Sample taken from KB 2285068)

So, the fix for your issue which was part of SP1 CU6 is not part of SP2 because of the testing time required for a service pack. What happens in such a scenario is, the next cumulative update for SP2 (which is CU1) will contain the fixes there were present in CU6. This is called a post service pack roll-up fix. As a DBA, you need to keep in mind that if you are following the regular CU schedule of patching your SQL Servers every 2 months and then applying a Service Pack, you must immediately apply CU1 for the newly released Service Pack. CU1 for Service Packs are released pretty soon after the SP is released (usually in a week’s time). Even this minute detail is documented.

image(Sample taken from KB 2289254)


3. General Distribution Release (GDR):

These are special patches that are released mainly for security issues or issues having a broad customer impact. I simply like to call them “security fixes”. These are not a scheduled fix and are released by Microsoft as appropriate. The important thing about these updates are that it impact every SQL Server and is applicable for most SQL Server versions. That is why, whenever a GDR is released it is available for public download in download centre, available through Windows Updates as a High/Critical fix and in all likeliness you IT team will push them to your machines for automatic update Smile.

The other thing to keep in mind with GDR releases it that even if your product is in extended support phase, you will still get these critical security fixes. For each GDR update, a security bulletin is posted which lists the severity rating, the affected software as well as the affected versions of the software.

If you have ever read the KB article for any SQL Server security bulletin, you may have noticed two different download options.

a. GDR Software Updates
b. QFE Software Updates

You need to choose the correct version based on the build number of your SQL Server instance. GDR updates are typically if you are on the base service pack build or earlier CU’s, whereas QFE updates are for those who have service pack + some cumulative update applied. In the latter, case the same GDR security fix is available for you with the existing CU fixes. In the former, you have the service pack + only the security fix applied.

True or False?

1. You need to apply each CU in sequence to be able to apply the latest CU: False

As the name says these are cumulative fixes, meaning the fixes from the previous CU’s are carried over to the next CU. You only need to apply the latest CU to have the fixes from the previous updates.

2. I have to apply each Service Pack to be able to apply the latest Service Pack: False

Same logic as CU. Each Service Pack is also cumulative, so only need the latest Service Pack to have all the fixes (The only exception is the Quirky Situation mentioned above).

3. I need a downtime to apply a CU or Service Pack: True

As the setup process goes on, it will automatically stop your SQL Server instance to replace the binaries (exe/DLL/other files), to apply upgrade scripts and start it back again. So you certainly need a business downtime pre-planned before running a SQL Server update. (This does not apply if you only patching the Tools or Setup Support Files).

4. I heard that SQL Express Edition comes with the Service Pack bundled?: True

SQL Express is unique in a way that for each Service Pack release, the SQL Express media itself can be used for a new install. This is called “Slipstreaming”. Slipstreaming is integrating a SQL Server update and the original installation media so that the original media and the update are installed at the same time. Starting with SQL Server 2008 this can be done manually even for other editions, but SQL Express has been this way for a long time and MS does this for you and puts the slipstreamed media in the download site. So if you install SQL Express SP4, you get a SQL Server instance that is at SP4 build right from the word Go.

5. It is not possible to uninstall a Service Pack!: False

Starting with SQL Server 2008, it is possible to uninstall Service Packs and cumulative updates. You can see each SQL update present in Add/Remove programs (or “View Installed Updates” in Vista+) and these can be uninstalled. Refer screenshot below.


For more information on this topic, please read the following MSDN article.

Overview of SQL Server Servicing Installation

Folks, I know this was a long post, but this was something that I wanted to talk about for a long time. If you have any questions leave a comment behind or drop me an email and I will definitely try and get back to you. Cheers!


Posted in Setup | Tagged: , , , , , , , , , , | 2 Comments »

Next version of SQL Server Code-Named “Denali” CTP3: Product Guide available for download

Posted by Sudarshan Narasimhan on August 11, 2011

Good news for all SQL enthusiasts today! The next version of SQL Server [codenamed “Denali”] reached the next milestone, i.e. CTP3 (Community Technology Preview 3) about a month back, but today the Product Team has released a complete product guide (with samples & videos) for public use. You can find the official confirmation from the SQL Server Product Team here,

If you haven’t downloaded the installation media for SQL Server Code Name "Denali" CTP3, you can find the link below,

I highly recommend you to download the comprehensive product guide which contains whitepapers, presentation, demos and sample videos to help you learn the new release. This will help you get started on the next version and stay one step ahead of the rest of the SQL community. The Product Guide is a ~450MB download.

Have fun playing with the new version. Remember, Information is Wealth Open-mouthed smile.

Posted in SQL News | Tagged: , , , , , , , , , | Leave a Comment »

PRB: Unable to remove secondary filegroup due to system service broker queues

Posted by Sudarshan Narasimhan on August 10, 2011

Delving a little into SQL Storage engine and Service Broker today. I had faced this problem sometime back and it presented me with an opportunity to see how the SSB queues/tables are represented internally by SQL Server.

My customer wanted to remove the secondary File Group “FG2_fg" from a database. But was unable to remove the filegroup since these service broker tables were present in filegroup FG2_fg.


All of the above objects are internal system tables which are placeholders for the default queues that are present for every database starting with SQL Server 2005.


Whenever we tried to remove the file/filegroup we got this error,

Alter database test3 remove file fil2

Msg 5031, Level 16, State 1, Line 1

Cannot remove the file ‘fil2’ because it is the only file in the DEFAULT filegroup.

We used the following queries to identify the objects present in the secondary filegroup:-

1) Find out the objects present in the secondary filegroup

select name, type, type_desc, is_ms_shipped from sys.objects where object_id in (select object_id from sys.indexes where data_space_id in (2))

— Here data_spaceid=2 represents the filegroup ID 2


SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = 2 — Filegroup ID

Background Info
1. SQL Server uses a proportional fill strategy as it writes to each file within the filegroup and NOT across filegroups. Read more about this in here.
2. The only exception to this could have been IF someone had changed the default filgroup to the secondary filegroup. If so, then any new objects created afterwards would have gone to the secondary instead of the primary.

Repro on SQL 2005
As any good engineer does, so did I – TESTING. To test my little theory I did the following repro on SQL 2005 :-

1) Created a new database with secondary filegroup and 1 file in that.
2) Made secondary FG as default FG for database "test".

*Note: This needs to be done as part of create database and not added later on.

3) What I found was that you DON’T NEED service broker to be enabled for the queue_messages tables to get created.
4) I found that on my test database I had 3 Internal queue_message tables which where for the following parent object


4) All of the 3 were were of type SERVICE_QUEUE. You can find this out from sys.objects view
5) Even a new database has the same object numbers and looking at my customer’s object numbers, they are higher, which means they must have had Service Broker implemented at some point.



6) I tested this by creating a service queue when my secondary filegroup was the default filegroup.

use test







7) This created the table queue_messages_2105058535 on FG 2 of type "QUEUE_MESSAGES" indicating its a SSB queue.


8)  To remove the internal table I did a DROP QUEUE.

drop queue TestQueue


9) This removed the associated internal table and the indexes. You can use the following query to identify internal tables which are for service broker queues.

select as [ChildName], a.object_id as [ChildObjectID], a.type_desc as [ChildType], a.internal_type_desc as [ChildTypeDesc],

a.parent_id as [Parent_ObjectID], as [Parent Name], b.type_desc as [Parent_Type]

from sys.internal_tables a

inner join sys.objects b

on a.parent_id = b.object_id


So the issue still does NOT reproduce on SQL 2005 (same for SQL 2008). I then did the following repro on a SQL Server 2000 instance.

Repro on SQL 2000
1. Created a database on SQL 2000
2. Added File-group (FG2) and made it as default.
3. Took a backup of the database in SQL 2000.
4. Restored this to SQL 2005/2008.
5. System Services/Queues got created on FG2.
*Note: We cannot modify them because they are system objects.

6) The remove File command gives us this error,

Alter database test remove file fil2

Msg 5031, Level 16, State 1, Line 1

Cannot remove the file ‘fil2’ because it is the only file in the DEFAULT filegroup.


7)  Next up, I ran these commands.


dbcc shrinkfile(3, EMPTYFILE)


alter database test remove file fil2


This worked so far and in sys.database_files I see the status of file2 as OFFLINE. But unfortunately I still cannot remove the filegroup. It still says that it is not empty. Even though the emptyfile commands worked, the objects (SSB queues) still exist on FG2.

I followed same steps for a database created on SQL 2008 and took a backup and restored it again on SQL 2008 and issue did not reproduce. i.e. objects were created on Primary and none of them went to the Secondary FG. I did the same test on SQL 2005 Database backed up and restored to SQL 2008. This also did NOT reproduce the issue.

So, what did I learn out of all my testing (and precious time). Read on…

My Theory
This database had to have been restored from SQL 2000 to a higher version or been upgraded in-place from SQL 2000 to higher version? Tracking down the source of the original database would help us figure this out.

If yes, we now know the answer and that this behaviour is ByDesign and understood, i.e. how the system tables went to the secondary filegroup.  This is because the non-primary filegroup FG2_fg was set as DEFAULT prior to backup from SQL 2000 or in-place upgrade. Once the upgrade was done, since SSB was newly introduced starting with SQL 2005, during the upgrade the upgrade scripts created these objects on the secondary filegroup since it was set as the default filegroup.

Verifying My Theory

To confirm if an in-place upgrade was done, we can look in sys.database_files DMV and look at the column file_GUID. The file_guid column will be NULL if the database was upgraded from an earlier version of Microsoft SQL Server. (Upgraded only, not for a Restore).

I have to thank the SQL Storage Engine folks for thinking about this kind of a scenario and capturing such detailed info in the catalog views. Thanks Folks!

I went back to my customer’s database and looked at the sys.databases output and I noticed this,














C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Data.mdf







C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Log.ldf







C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Data2.ndf







C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fil2.ndf







C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Data3.ndf


Notice that for data_space_id=2 which is for the secondary filegroup FG2_fg, the file_guid column has a value of NULL !!!

This confirms that this instance/database was indeed upgraded from an earlier version of SQL 2000/7.0 to SQL (2005/2008). This behaviour is by design and we now understand how the system SSB objects were created on secondary filegroup.


Q: Why cannot we remove the file and filegroup?
Ans: Because of the above mentioned queue_messages objects which are present in the secondary filegroup. If there were user-created service broker queues/services then they can be Dropped or Altered to move them to the primary filegroup. Since these came as part of the database during DB Creation, they cannot be modified/dropped/moved. Hence, we cannot remove the filegroup.

Q: How did these "system" objects get to the secondary file group?
Ans: These system objects became part of the database starting with SQL Server 2005 onwards. So, If you had a database in SQL 2000 and you upgraded that to SQL 2005/2008 these system objects will get created. But, in SQL 2000 if the secondary file-group FG2_fg was set as the DEFAULT file-group, then any new objects created without an explicit MOVE TO will go the default filegroup. So when you upgrade the database to SQL 2008 these automatically got created on the secondary file-group.

Q: What data do I have to prove that the theory above is true in your case?
Ans: We store the information about each database file in sys.database_files DMV. There is a column called file_guid which when NULL indicates that the database/file was upgraded from an earlier version on SQL Server. So when I looked at sys.database_files in the database test, I saw the value for the secondary file as NULL.

Reference –


I spent some serious time to arrive at this conclusion and I am sharing this with the SQL community, so that it saves you some time. As always, stay tuned to theSQLDude for more…

Posted in Storage Engine | Tagged: , , , , , , | Leave a Comment »

Guide to Troubleshooting SQL Server Service startup issues

Posted by Sudarshan Narasimhan on August 9, 2011

I did post on this same topic on MSDN SQLServerFAQ and I am re-posting this here for easy reference. This is a handy guide for every SQL DBA, as they would have come across this issue at one point or another in their DBA life. Read on…

This is one of those challenging situations when customers call us at times. I can understand the gravity of the situation – your boss is behind your back, your business is down, your application is down, your website cannot accept orders or your phone is ringing off the hook with calls from the helpdesk. Don’t worry; I will guide you through some basic steps & actions that you can take to make some sense amidst all this madness.

clip_image002[4] Warning:

This blog is only a basic guide and by no means covers all possible troubleshooting techniques and meant only as a starting point.


Nothing in life is to be feared. It is only to be understood. – Marie Curie


Startup Options
Have you ever called SQL CSS for a startup issue and seen the engineer use some cool trace flags or parameters to get SQL started. Well, it’s all documented (well, most of it that’s worth knowing)

Using the SQL Server Service Startup Options

The most common ones by far that I use on SQL startup issues are:-





Start as a console application, not as a service.



Tries to start the SQL service in single user mode, i.e. only a single user can connect. This single user connection can be either  a sysadmin or a regular user connection



Tries to start the SQL service in Minimal configuration mode. This implicitly puts SQL Server in single-user mode and this also means only the system databases master, model, tempdb & mssqlsystemresource are recovered and started.



Tries to start the SQL Server should be started with the specified trace flag which follows after –T. Again this is case sensitive.



Specifies the number of megabytes (MB) of memory that SQL Server leaves available for memory allocations within the SQL Server process, but outside the SQL Server buffer pool. The default value for this is 256MB.


-m”ClientApp Name”

You can limit the connections to the specified client application. For example, -m"SQLCMD" limits connections to a single connection and that connection must identify itself as the SQLCMD client program. You can use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available connection. Cool option J


-k  123

Limits the number of checkpoint I/O requests per second to the value specified e.g. 123 MB/sec. Refer for more info.


Trace Flags
For more information some common trace flags see here Few of the common ones that come handy during startup issues are:

1.       3608 Starts SQL and recovers only the master database.

2.       4010 Allows only shared memory connections to the SQL Server. Meaning, you will only be able to connect from the server machine itself. Client connections over TCP/IP or named pipes will not happen.

3.       4606 Disables password policy check during server startup.

4.       4022 Skips launching automatic stored procedures when SQL Server starts. This is equivalent to setting "scan for startup procs" configuration option to 0.


Here are the steps to start SQL Server from command prompt:-

1.      Right-click on the SQL Server service name in services.msc



2.  Copy the path that you see here.

3.  Open a Command Prompt window and navigate to the path from step2.

E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr.exe -c -m -sSQL2005



clip_image008[8] Note:

If you are using Windows Server 2008 or above, you need to open an elevated command prompt by launching cmd.exe with “Run as Administrator” option.

A common mistake that people sometime make is to try and start SQL using sqlservr.exe and you get this error,


Text: “Your SQL Server installation is either corrupt or has been tampered with. Please uninstall then re-run setup to correct this problem”

Okay, you see this message and you are thinking oh no, there goes my dinner plans!
The above message can be misleading at times. You will get the above error if you try to start SQL server from command prompt for a named instance and do not specify an instance name. Without the -s switch the default instance will try to start, but using the sqlservr.exe file from another instance binn folder. You will also get this error if you specify an invalid parameter to sqlservr.exe. So, the installation is not corrupt after all (Dinner plans back on?)

Here’s how to start a named instance,
sqlservr.exe -c -s INSTNAME

Here’s how to start a default instance,
sqlservr.exe -c

Here’s how to start a named instance recovering only master database
sqlservr.exe -c -sMATRIX –T3608

clip_image008[9] Note:

Only specify the instance name and not the servername\InstanceName. E.g. If your machine name is YODA and your instance is called MATRIX, you only specify MATRIX. Also the “-s” is case sensitive. It does not matter if there is/isn’t a blank space after the “-s” parameter.

Some Common Scenarios for Startup Failures

Scenario 1

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log.


“The service failed to respond in a timely fashion”.


“The service failed to start”.

1.      Locate the SQL Errorlog as that is the 1st place to look if a SQL service is not starting.

2.   This can be done by looking up the -e startup parameter value for the SQL Server instance in question.



3.  Verify that the files pointed to by -d (master data file location),  -e (Errorlog location) and  -l (master log file location) actually exist and have the correct file paths and that the SQL service startup account has proper permissions. Alternately you can also get this from the registry by looking at the values SQLArg0, SQLArg1 and SQLArg2 in the following locations.

SQL 2000
Default: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
Named: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\INSTANCENAME\MSSQLServer\Parameters

SQL 2005

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters

SQL 2008/R2


4.  Once you have located the errorlog open it in notepad and look for any errors. An example would be like this,

2010-11-20 07:50:58.250 Server Error: 17113, Severity: 16, State: 1.
2010-11-20 07:50:58.250 Server Error 2(The system cannot find the file specified.) occurred while opening file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

The above message clearly indicates that the master.mdf was not found in the location it was expecting it to be found.


Scenario 2

You can also look into the Windows Application log for any events related to SQL Server.

initerrlog: Could not open error log file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG1\ERRORLOG’. Operating system error = 3(error not found).

The above error indicates that SQL could not find the errorlog file in the above location. What does this mean? This means that since SQL could not find the errorlog it has no place to log messages, hence it did not start.

When you are faced with this error, then if you look into the errorlog location, you will notice log files whose date/time stamp is not current. Do not make the mistake of opening these files and troubleshooting based on messages that are not for the current startup failure. This is where looking into the application log gives you the current entries.

I hope cleared up any confusion you might have had regarding this rule, the warning status and the simple logic behind the rule. As always stay tuned for more SQL tips…

Scenario 3

2011-04-16 07:52:49.32 spid5s      Error: 17204, Severity: 16, State: 1.
2011-04-16 07:52:49.32 spid5s      FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf for file number 2.  OS error: 32(error not found).

OS Error 32 translates to “The process cannot access the file because it is being used by another process.” How did I know this? Simple. You can find the text for any windows error by running this from a command prompt à net helpmsg XXX, where XXX is the error number

In the above scenario, some other process/application has a lock on master.mdf and this isn’t allowing SQL to take access, hence the service fails to start. To find out which process it is you can use either Process Explorer or Handle.exe from sysinternals. The usual culprits are Anti-Virus or Anti-Malware services. Once you identify the process, after stopping that, attempt to start SQL again. If it fails, verify from the errorlog that the error code is still 32 and has not changed. Most people miss this trick and assume the error is the same after making some changes. Don’t take it for granted, since the error might have changed but the service still doesn’t start. This indicates that the changes done have been successful.


Some Common Causes for SQL Service startup failures

1.                   Access denied on the folders for the SQL Server Service Account, specifically on the DATA folder which contains the SQL Server system databases.

2.                   Due to some other processes like anti-virus holding a lock on the SQL database files.

3.                   Insufficient permissions granted to the SQL Server Service account.

clip_image008[10] Important:

Always make SQL service account changes via the SQL Configuration manager since only this tool sets the proper permissions or ACL’s to the new service account. Services.msc does not set ACL’s.


4.                   Server side protocols for SQL not set correctly or disabled.

5.                   Critical system database files not found due to either accidental deletion of files or disk failures.

6.                   System database files having inconsistencies preventing SQL Server startup.

7.                   Password of the service account was changed but not updated on the server that has the SQL instance installed.

8.                   Startup parameters have incorrect file path locations.

9.                   The SQL Server service is disabled in Service Control Manager.

clip_image014[4] Critical:

If you are not a SQL expert and have followed the steps above but still stuck, my personal advice would be to call SQL Support rather than doing something you will regret later. Better safe, than sorry, eh?

Additional Resources

Error Messages of SQL Server 2005 Start Up Failure

An error occurred during encryption


Posted in Startup | Tagged: , , , , , , | Leave a Comment »

How to find the Installation Date for an Evaluation Edition of SQL Server

Posted by Sudarshan Narasimhan on August 9, 2011

Well, this topic seems pretty simple, but many people forget that we can have the SQL Engine, Tools and other components like Reporting Services, Analysis Services in different versions or installed on different dates, so the expiration date may change accordingly. For e.g., you may have installed the Engine first and then decided to install Management Tools later on.

If you are using an Evaluation edition of SQL Server 2005/2008 then you must already know that Evaluation Edition works for 180 days from the date of installation. So, it’s very important for you to know:-

1. When was your SQL Server Instance Installed
2. When it is going to stop working. Once the 180 days are up, the service will not start.

Read on if you don’t want to see this message once the trial period is up Smile.


So I thought I’d post the various different way a DBA can go about determining the install date for SQL Server Evaluation Edition, thus calculating when the 180 day trial period expires. Here are some ways to go about finding this information:-

1. Run the following query on the evaluation instance (assuming the 180 days are still NOT over and SQL service is still running),

SELECT create_date as ‘SQL Server Install Date’,

DATEADD(dd,180,create_date) as ‘Instance will Stop Working ON’

FROM sys.server_principals WHERE name=‘NT AUTHORITY\SYSTEM’


-> ‘NT AUTHORITY\SYSTEM’ is SQL Server principal which gets created during the installation. So we are just checking the creation date of that security principal.


2. Look at the setup logs located in "%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\". Sort by Date Modified ascending and you should get the install date. This method doesn’t work if the log directory was cleaned up or moved or deleted.


3. Open the Management Studio on the SQL Server, click on the “Help” of Menu Bar and then on “About”. In the new window you will see some thing like this:
Microsoft SQL Server Management Studio (expires in 104 days)
Note: This is assuming that you installed SSMS along with Engine as part of the same original install at the same time.


4. You can check the windows installer for the SQL Product code to pull out the install date. In registry look under,

The "InstallDate" key will have the date stored in YYYYMMDD format.

For SQL 2008 you can search the registry for "Microsoft SQL Server 2008 Database Engine Services" to get the correct GUID.
For SQL 2005 you can search the registry for "Microsoft SQL Server 2005" only to get the correct GUID.

Note: Keep in mind the instance name if you have more than 1 instance installed on the same machine


While on this topic, keep in mind there is an issue when you perform an in-place edition upgrade of an evaluation edition to a licensed edition. You will still get the error for SSMS or any tools. This is a known issue. Please follow the following KB to fix this issue.

‘Microsoft SQL Server management studio’ and ‘SQL Server Profiler’ report ‘Evaluation period has expired’ message even after upgrading to a licensed edition

Posted in Setup | Tagged: , , , , , | 2 Comments »