• SQL Server Cast

    CA eTrust Antivirus reports infection by JS/Snz.A on sqlcast blog!











  • SQL Server Cast

    SQL Server Configuration Manager behavior in Clustered installation

    According to Books Online reference "SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to mange the network connectivity configuration".

    One of the greatest things about SQL Server Configuration Manager (SSCM for the sake of brevity) is that you can manage different SQL Server settings in one tool, as opposed to SQL Server 2000 where different tools are needed to perform the same action. The following table summarizes the different configuration options in SQL Server 2000 and 2005:


    SQL Server 2000

    SQL Server 2005

    Client Networking

    Client Network Utility


    Server Networking

    Server Network Utility



    Service Manager


    Startup parameters

    Enterprise Manager


    However if you have made use of SSCM in a clustered installation you have probably noticed something weird...

    Managing Network Components

    Let's start our test with a SQL Server 2005 cluster with two cluster nodes: NEWYORK and BOSTON sharing a single SQL Server 2005 virtual instance called "SQL-2005":

    Suppose we want to enable Named Pipes network protocol on "SQL-2005" virtual instance using SSCM, we will see the following result in NEWYORK node after performing the change and restarting virtual SQL Server instance:

    Oddly enough, if we check now Named Pipes configuration on BOSTON cluster node we will see this protocol as disabled, even after restarting SQL Server service:

    This information can be confusing but this is the expected behavior for SSCM in a clustered instance. The changed network parameter is stored on cluster Quorum and is only refreshed by the rest of cluster nodes during the virtual instance failover. This does not mean we need to move the SQL Server cluster group to each owner, we just need to remember the information will not persist on other cluster nodes until the service is moved to the rest of possible owners, and therefore, the configuration shown by local SSCM will not be accurate.

    Managing Services

    Let's now look at how managing clustered SQL Server services looks in SSCM. On the following image we can find Cluster Administrator and SSCM side by side running on "NEWYORK" node:

    As you can see SQL Server instance "YUKON" is shown as Online in both Cluster Administrator and SSCM. Let's move now SQL Server 2005 Cluster Group resources from NEWYORK node to BOSTON node. After refreshing SQL Server services under SQL Server Configuration Manager we will see the services as Stopped:

    This is happening because SSCM relies on Windows Service Control Manager (SCM) to show the services status and from a local (non-cluster) point of view, SQL Server service is stopped on NEWYORK and running on BOSTON.

    If we want to know the status of SQL Server service from a cluster point of view (non-local) we will need to open the SSCM Microsoft management console using the SQL Server Network Name as parameter:

    SQLServerManager.msc /computer:SQL-2005

    This can be performed from any cluster node and will open a new console where SQL Server services status appear as it is actually shown in Cluster Administrator. This can also be used to check for the virtual server network configuration settings at cluster level.

    Obviously, this will only inform of the status of the services under the specified virtual server after the /computer switch ("SQL-2005" in our example) but will not provide accurate information for other possible instances. In our example, BOSTON cluster node contains an additional non-clustered instance called "YUKON2" which is currently stopped. If we open SSCM in BOSTON using the abovementioned command, we will see the following information:

    As we can see, while the information shown for virtual instance YUKON is accurate while the services status for local instance YUKON2 is not.

    In this post I have tried to explain the limitations you should be aware of when configuring a SQL Server 2005 cluster using Configuration Manager. The behavior explained here can be easily understood keeping in mind how Windows Cluster registry information is replicated among nodes (more information on KB174070). Remember that only using the virtual SQL Server instance as SSCM startup parameter will guarantee accurate information from this tool.

  • SQL Server Cast

    Reducing tempdb size in a SQL Server Cluster (after ALTER DATABASE execution)


    Some weeks ago a colleague from another company called me for help in fixing a database performance problem. The root cause was located on a possible contention issue on tempdb that was causing database to perform badly from time to time. He did find a match between the times of bad performance and the following messages in the SQL Server ERRORLOG file:

    2007-12-03 09:34:31.07 spid1     SQL Server has encountered 2 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL$YUKON\Data\tempdb.mdf] in database [tempdb] (7).  The OS file handle is 0x00000530.  The offset of the latest long IO is: 0x0000008ef65a00

    2007-12-03 09:34:57.04 spid104   SQL Server has encountered 8186 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL$YUKON\Data\tempdb.mdf] in database [tempdb] (2).  The OS file handle is 0x000005FC.  The offset of the latest long IO is: 0x0000004d242000


    They have already moved tempdb SQL Server database to a different system partition as recommended in situations where contention problems exist. They did also changed tempdb size from 250 MB to 2,500 MB, which was the maximum size observed for this database during peak time usage. Although the overall database performance was better now, it was not brilliant and the "IO requests taking longer than 15 seconds" messages continued popping up on ERRORLOG. They wanted now to try splitting tempdb in several files, which is also a recommended procedure in tempdb contention problems.

    Unfortunately the partition where tempdb was moved was only 10 GB in size and he was in the need of splitting tempdb in 8 different files (8 was the number of logical processors on the server). Creating 8 tempdb files of 2,500 MB each was not only impossible in this cased but hard to justify from a logical point of view.

    As you probably know, a database cannot be reduced to a size that is smaller that the current size using a simple ALTER DATABASE ... MODIFY FILE instruction. If you try to change tempdb system database size from a Query window you will see the following error message:

    Msg 5039, Level 16, State 1, Line 1
    MODIFY FILE failed. Specified size is less than current size.

    Surprisingly, when reducing the database size from SQL Server Management Studio, the GUI does not report any error, but the database size is not changed. This seems like a step back from SQL Server 2000 where the same action in Enterprise Manager caused the following error to be raised:

    Error 21335: [SQL-DMO]The new DBFile size must be large than the current size.

    (UPDATE: An in depth explanation of this behavior can be found on this MSDN blog)

    This error message is expected. For data and log files the new size should be larger than the current size. If we want to reduce the database size we have to shrink it and return the freed up space to the operating system (providing the database files can be shrunk). My first though was to point my colleague to Knowledge Base article KB307487 where the shrinking process for tempdb is explained, but while reading the article I discovered the following paragraph:

    "There are limitations for use of the DBCC SHRINKDATABASE command on the tempdb database. The target size for data and log files cannot be smaller than the size specified when the database was created or the last size explicitly set with a file-size changing operation such as ALTER DATABASE with the MODIFY FILE option or the DBCC SHRINKFILE command."

    They indeed had executed and ALTER DATABASE command with the MODIFY FILE option when they changed the original tempdb database size. Shrinking tempdb was not an option in this case. The only option was to execute a new ALTER DATABASE command with a smaller size than the current one, as specified in the "Method 1" section in the KB.

    This requires the SQL Server service to be started in minimal configuration mode with -c and -f startup options, but how to do this in a cluster? For standalone servers we have to execute the following instruction:

    sqlservr -c -f [ -s%InstanceName% ]

    where -s will be a required parameter for a named instance. If we are using a default instance (MSSQLSERVER) -s switch is not needed. If we have installed a SQL Server named instance the instruction to use under a standalone server would be:

    sqlservr -c -f -sMSSQL$YUKON

    where "YUKON" corresponds with the instance name. On a cluster installation, the instance named is compound using the "SQL Server Network Name" cluster resource. In my example the Cluster Administrator shows the SQL Server resources as follows:

    so the instruction to startup the SQL Server service would be:

    sqlservr -c -f -sSQL-2005\YUKON

    This instruction should be executed from a command prompt window (cmd.exe), the SQL Server service will start logging the ERRORLOG information on the console window. Of course, we can also add the -f startup option to the SQL Server service in SQL Server Configuration Manager (2005) or Enterprise Manager (2000) and restart; in this case we do not need to use -c. In SQL Server Configuration Manager we have to go SQL Server properties on Advanced tab, Startup Parameters while on SQL Server Enterprise Manager we go to the instance properties, General tab, Startup Parameters button.

    Note: From a cmd.exe window use the full path to sqlservr.exe adding -c and -f (with hyphen). If you use the NET START command use the SQL Server service name with the /c /f (with slash)

    Once SQL Server service is started, we will be able to change the tempdb database size. You can use sqlcmd command-line utility (or osql if you are running under SQL Server 2000) opening a new cmd.exe windows to perform this change. The following example will configure the initial tempdb database size to 70 MB:

    C:\>sqlcmd -S SQL-2005\YUKON
    1> USE master
    2> GO
    Changed database context to 'master'.
    1> ALTER DATABASE tempdb
    2> MODIFY FILE ( NAME = tempdev, SIZE = 70MB )
    3> GO
    1> exit

    Alternatively you can execute this same ALTER DATABASE instruction from a Query window in SQL Server Management Studio or Enterprise Manager. If you have used the GUI to change the startup options do not forget to remove the -f startup parameter from the SQL Server service option in Configuration Manager or Enterprise Manager once the change is done.

  • SQL Server Cast

    How to check Fragmentation on SQL Server 2005








    • DBCC SWOWCONTING example:

    USE AdventureWorks;
    DBCC SHOWCONTIG ('HumanResources.Employee')

    DBCC SHOWCONTIG scanning 'Employee' table...
    TABLE level scan performed.

    • sys.dm_db_index_physical_stats DMV example:

    USE AdventureWorks
    SELECT object_id, index_id, avg_fragmentation_in_percent, page_count


    SELECT *





    Reference Values (in %) Action SQL statement

    avg_fragmentation_in_percent > 5 AND < 30


    avg_fragmentation_in_percent > 30




Page 1 of 1 (4 items)