SP_COLUMNS take longer in SQL Server 2005 SP3 compared to SQL Server 2000

Recently we encountered a case wherein one of the user application which internally calls sp_column stored procedure in SQL server. This application is taking longer time to execute in SQL server 2005 SP3 when compared to SQL server 2000. This application uses sp_columns stored procedure to enumerate the column names. We noticed that there is…


Unable to open the configuration manager in SQL server 2005

Problem Description in our scenario: ============================ We have a SQL server 2005 (9.00.4035 Enterprise Edition X64) , when we try to open the configuration manager we get the below error : ————————– SQL Server Configuration Manager ————————— Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you…


Sql Server 2005 Resource Taking long time to come online on Windows Cluster with Resource Manager Creation Failed reported in Errorlog

Recently we faced an issue where in clustered instance of sql server 2005 was taking long time to come online on a windows 2008 cluster.         We checked the Error log and found the following   2009-10-05 10:58:27.41 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No…


Script to find the Objects Owned by Orphaned Users

Below is the script that would first identify and display Orphaned users. Then this output is passed to the next level which identifies the objects owned by this User in all the databases   I have implemented temporary tables hence the entire script should be executed every time.   You can convert them to tables…


Receive a warning about the network binding order on the Setup Support Rules page when install SQL Server 2008 in a failover cluster

While installing sql server 2008 setup we might face following warning in the installation window  Receive a warning about the network binding order on the Setup Support Rules page when  install SQL Server 2008 in a failover cluster  The domain network is not the first bound network. This will cause domain operations to run slowly…


How to identify Orphaned Logins and Objects owned by Oraphaned Logins

  Below is the script that would first identify and display Orphaned users. Then this output is passed to the next level which identifies the objects owned by this User in all the databases   I have implemented temporary tables hence the entire script should be executed every time.   You can convert them to…


How to change the Service account and restart the Service using WMI.

Often we required the service account to be changed and this requires a restart of the service for the change to come in to effect.   Below is a vbscript that uses WMI to achieve this. This script has not been tested on Services that depend on other services. Will update this once those tests…


Performance Benefits of using Expression over User Defined Functions

Summary No matter how simple a UDF is, there’s a large performance penalty paid when they’re used.  This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more.  The penalty is incurred because the SQL Server database engine must create its…

4

Cannot Alter Schema of Replicated Article

Suppose you encounter an error message wherein you have a replication setup and want to change the schema of the article. You will try using Alter Table command on the replicated table and it will fail. The error message will be following:   Msg 4928, Level 16, State 1, Line 1 Cannot alter column ‘ColumnName’…


Behavior of WITH NOWAIT option with RAISERROR in SQL Server

  In some situations you would like to use WITH NOWAIT option in SQL Server RAISERROR statement, but somehow you usually have to wait until the procedure is complete before seeing messages. You search through MSDN links but end up reading several different articles. Here I am trying to put all the things under one…