Unable to connect SQL Server 2008 after SP3 is installed (Microsoft SQL Server, Error: 18401)

I recently installed SP3 for SQL Server 2008. Before installing the SP as usual I stopped all SQL services. My understanding is if SQL services are running then it will show up in blocked files list while installing service pack.

The install ran fine and after installation it asked to reboot the server which I did.

However after server reboot I was unable to connect to SQL server with following error.

TITLE: Connect to Server

Cannot connect to MYSERVER.

ADDITIONAL INFORMATION:

Login failed for user 'domain\login'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18401&LinkId=20476

BUTTONS:

OK

I checked the SQL services and they were running fine. So why this error?

After installing Service pack SQL setup run some upgrade scripts namely “sqlagent100_msdb_upgrade.sql”

Following message was logged in SQL error log

2012-04-09 11:31:26.820 spid13s      ----------------------------------------------------------------

2012-04-09 11:31:26.820 spid13s      msdb_upgrade_discovery starting

2012-04-09 11:31:26.960 spid13s      MSDB format is: SQL Server 2008

2012-04-09 11:31:27.100 spid13s      User 'sa' is changing database script level entry 4 to a value of 2.

2012-04-09 11:31:27.120 spid13s      User 'sa' is changing database script level entry 5 to a value of 2.

2012-04-09 11:31:27.130 spid13s      User 'sa' is changing database script level entry 6 to a value of 2.

2012-04-09 11:31:27.130 spid13s      User 'sa' is changing database script level entry 6 to a value of 0.

2012-04-09 11:31:27.130 spid13s      Running SQL Server 2005 SP2 to SQL Server 2008 upgrade script

2012-04-09 11:31:27.130 spid13s      ----------------------------------------------------------------

What is the resolution?

Resolution is to just wait for few minutes till this upgrade script completes. I have seen some other blogs mentioning to turn of implicit transactions etc but in my case resolution was to WAIT and try connecting after few minutes

SQL Server Logshipping restore status

Following query can be used to check log shipping restore status


SELECT top 10 s3.physical_device_name
 , s1.restore_type
 , s2.first_lsn
 , s2.last_lsn
 , s2.checkpoint_lsn
 , s2.database_backup_lsn
 , s1.restore_date
 , s2.backup_start_date
 , s1.destination_database_name
 , s1.backup_set_id
FROM   msdb..restorehistory as s1 INNER JOIN msdb..backupset as s2
 ON s1.backup_set_id = s2.backup_set_id
 INNER JOIN msdb..backupmediafamily as s3
 ON s2.media_set_id = s3.media_set_id
 where
 s1.destination_database_name ='MyDB' -- the database restored
 and s1.restore_type in('D','L')  -- sl.restore_type in ('D','L') means diff or Transaction Log backups
 order by s1.restore_date desc

Hash Table for creating Dictionary

The code is ment for memory optimization and quick search of desired words. Such technique can be used for any type of efficient data search

//**************************************
//INCLUDE files for :Hash Table for creating Dictionary
//**************************************
# include <stdio.h>
# include <conio.h>
# include <stdlib.h>
# include <alloc.h>
# include <string.h>
//**************************************
// Name: Hash Table for creating Dictionary
// Description:The code is ment for memory optimization and quick search of desired words. Such technique can be used for any type of efficient data search
// By: Yogesh Ranade
//
//
// Inputs:words and their meanings
//
// Returns:searching facility for word search which will return appropriate meaning
//
//Assumes:None
//
//Side Effects:Nothing
//This code is copyrighted and has limited warranties.
//Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.6108/lngWId.3/qx/vb/scripts/ShowCode.htm
//for details.
//**************************************

/*
HASH TABLE FOR CREATING A WORD LIST AND ITS DEFINITION
AUTHOR : YOGESH
*/
# define HASHSIZE 100
# include <stdio.h>
# include <conio.h>
# include <stdlib.h>
# include <alloc.h>
# include <string.h>
/////////////////////////////////////////////////////
struct nlist


    {
    char *name;
    char *def;
    struct nlist *next;
};
/////////////////////////////////////////////////////
struct nlist *hashtab[HASHSIZE];
/////////////////////////////////////////////////////
struct nlist * nalloc(void)


    {
    struct nlist *np;
    np=(struct nlist *)malloc(sizeof(struct nlist));
    if(np==NULL)


        {
        printf("mem limit");
        exit(1);
    }
    return(np);
}
char * strsave(char *s)


    {
    char *p;
    p=(char *)malloc(strlen(s)+1);
    if(p==NULL)


        {
        printf("mem limit");
        exit(1);
    }
    strcpy(p,s);
    return(p);
}
int hash(char *s)


    {
    int hashval=0;
    for( ;*s!='\0';s++)
    hashval=hashval+(*s);
    // eprintf("\n%d",hashval%HASHSIZE);
    return(hashval%HASHSIZE);
}
struct nlist * lookup(char *s)


    {
    struct nlist *np;
    np=hashtab[hash(s)];
    for( ; np!=NULL;np=np->next)


        {
        if(strcmp(s,np->name)==0)
        return(np);
    }
    return(NULL);
}
struct nlist * install(char *n,char *d)


    {
    struct nlist *np;
    int hashval;
    np=lookup(n);
    if(np==NULL)


        {
        np=nalloc();
        np->name=strsave(n);
        np->def=strsave(d);
        hashval=hash(n);
        np->next=hashtab[hashval];
        hashtab[hashval]=np;
    }
    else


        {
        free(np->def);
        np->def=strsave(d);
    }
    return(np);
}
void main(void)


    {
    int n=0;
    char *word,*def;
    struct nlist *temp;
    clrscr();
    printf("HASH TABLE FOR CREATING A WORD LIST AND ITS DEFINITION\n\n");
    printf("Enter Word and it's meaning or Enter 'quit' to exit.\n");
    while(strcmp(gets(word),"quit")!=0)


        {
        //gets(word);
        gets(def);
        if(strcmp(def,"quit")==0)
        break;
        temp=lookup(word);
        if(temp!=NULL)


            {
            printf("Word '%s' is already entered",temp->name);
            continue;
        }
        else
        temp=install(word,def);
    }
    printf("\nWord list : \n");
    for(n=0;n<HASHSIZE;n++)


        {
        temp=hashtab[n];
        while(temp!=NULL)


            {
            printf("\nWords at index %d\n",n);
            printf("%s : %s\n",temp->name,temp->def);
            temp=temp->next;
        }
    }
    getch();
}

link list for accepting n number

program to create link list for accepting n number of lines from user and creating one node each for one line. only one global variable root is used,so while refering to list after creation, we will be refering from the last node towards the first node. dispayed in reverse order.

INCLUDE files:

//**************************************

//INCLUDE files for :Linked List for storing strings

//**************************************

# include <stdio.h>

# include <conio.h>

# include <alloc.h>

# include <string.h>

# include <stdlib.h>

//**************************************

// Name: Linked List for storing strings

// Description:program to create link list for accepting n number of lines from user

and creating one node each for one line.

only one global variable root is used,so while refering to list

after creation, we will be refering from the last node towards the

first node.

dispayed in reverse order.

// By: Yogesh Ranade

//

//

// Inputs:any number of strings

//

// Returns:all the strings

//

//Assumes:None

//

//Side Effects:no

//This code is copyrighted and has limited warranties.

//Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.6112/lngWId.3/qx/vb/scripts/ShowCode.htm

//for details.

//**************************************

/////////////////////////////////////////////////////////

typedef struct node

    {

    char *info;

    struct node *next;

}NODE,*NODEPTR;

/////////////////////////////////////////////////////////

NODEPTR root=NULL;

/////////////////////////////////////////////////////////

NODEPTR allocnode(void);

char * strsave(char *s);

void createlist(char *s);

void displist(NODEPTR np);

void freelist(void);

/////////////////////////////////////////////////////////

void main(void)

    {

    char s[100];

    clrscr();

    while(1)

        {

        gets(s);

        if((strcmp(s,"quit")==0) ||(strcmp(s,"QUIT")==0))

                break;

        createlist(s);

    }

    displist(root);

    freelist();

    getch();

}

NODEPTR allocnode(void)

    {

    NODEPTR p;

    p=(NODEPTR)malloc(sizeof(NODE));

    if(p==NULL)

        {

        printf("Memory limit");

        exit(1);

    }

    return(p);

}

char * strsave(char *s)

    {

    char *p;

    p=(char *)malloc(strlen(s)+1);

    if(p==NULL)

        {

        printf("Memory limit");

        exit(1);

    }

    strcpy(p,s);

    return(p);

}

void createlist(char *s)

    {

    NODEPTR np;

    np=allocnode();

    np->info=strsave(s);

    np->next=root;

    root=np;

}

void displist(NODEPTR np)

    {

    while(np!=NULL)

        {

        printf("\n%s",np->info);

        np=np->next;

    }

}

void freelist(void)

    {

    NODEPTR np=root,np1;

    while(np!=NULL)

        {

        free(np->info);

        np1=np->next;

        free(np);

        np=np1;

    }

    root=NULL;

}

Table Value Parameters (TVPs)

Table Value Parameters (TVPs):

Following is description from BOL

http://msdn.microsoft.com/en-us/library/bb510489.aspx
Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations.
Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data. You can create and execute Transact-SQL routines with table-valued parameters, and call them from Transact-SQL code, managed and native clients in any managed language.

Here is an example of how to use TVPs

/*CREATE A SAMPLE TABLE TO WORK WITH*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductType]') AND type in (N'U'))
DROP TABLE [dbo].[ProductType]
GO
CREATE TABLE [ProductType]
(
[ProductTypeID] INT IDENTITY(1,1) PRIMARY KEY,
[ProductTypeName] VARCHAR(30)
)
GO

/*USE CREATE TYPE TO CREATE A TVP WHICH WILL ACCEPT TABULAR DATA AND INSERT IT TO THE TABLE*/
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id
WHERE st.name = N'ProductType' AND ss.name = N'dbo')
DROP TYPE [dbo].[ProductType]
GO
CREATE TYPE [dbo].ProductType AS TABLE(
[ProdTypeName] [varchar](30) NULL
)
GO
/*
--CREATE STORED PROCEDURE WHICH WILL INPUT PARAMETER AS THE TVP WHICH WE CREATED
--THE DATA IN TVP WILL BE INSERTED INTO THE TABLE
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertProductType]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertProductType]
GO
CREATE PROCEDURE InsertProductType
@InsertProdType_TVP [ProductType] READONLY
AS
INSERT INTO [ProductType]([ProductTypeName])
SELECT * FROM @InsertProdType_TVP;
GO

/*
--THIS IS HOW YOU CAN USE A TVP TO PASS TABULAR DATA TO STORED PROCEDURE
--THE SP WILL INTURN INSERT IT TO THE TABLE
*/
DECLARE @InsertProdTypeTVP AS ProductType;

INSERT INTO @InsertProdTypeTVP([ProdTypeName])
VALUES ('Farming'),
('Sporting'),
('Software'),
('Arms'),
('Ammo');

--select * from @InsertProdTypeTVP

EXEC InsertProductType @InsertProdTypeTVP;
GO

/*RUN SELECT ON BASE TABLE TO CONFIRM THAT DATA IS INSERTED*/
select * from [ProductType]

Benefits:
1. They provide a simpler way to pass a result set to a stored procedure
2. Pass through allows us to avoid multiple calls to the stored procedure
3. TVPs save round trip to the server by passing all data in single call to the stored procedure
4. TVPs provide more efficient and scalable way to process strings of data
Restrictions:
1. TVPs can not be used in SELECT INTO or INSERT EXEC
2. TVPs are READONLY parameters
Drawbacks:
1. It works similar to table variables so there are no statistics associated with TVPs. This can result in occasional suboptimal plans
2. No Design time syntax checking
3. Run time error handling is minimal
4. In case table has identity columns and pass through query is inserting in to the table then we can’t get resulting identity value because they can’t return data

Note: TVPs are only available in SQL Server 2008 onwards

WmiPrvSE.exe blocking SQL Server 2008 R2 SP1 setup

Error while Installing Service Pack 1 for SQL Server 2008 R2

While installing SP 1 for SQL Server 2008 R2 i came across the following error during the installation “Check File In Use” page:

The ‘WmiPrvSE.exe’ process is not controlled by the update wizard. You have to manually stop this process to avoid a computer restart

This executable is a provider created to launch multiple instances of WMI (Windows Management Instrumentation) and if you kill it from Task Manager then each new request to the service will launch a new thread. Only way to stop this from happening is stop Windows Management Instrumentation service

Once stopped the setup runs perfectly fine

OLAP Processing timeout vs query timeout

DBAs in my company saw this error logged in Event Viewer on production OLAP server several times this week. Every time cube processing failed with same error.

A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLServerOLAPService service.

There was a change made to one of the cubes recently but that cube was not failing. So I started looking into server side if there is anything wrong there. This timeout setting is controlled by server property ForceCommitTimeout. Following is description from books online

http://msdn.microsoft.com/en-us/library/cc966526.aspx

ForceCommitTimeout is a server property that is used to control what happens when a processing operation is waiting to finish its operation to enter the commit phase. When this value is greater than zero, SSAS will start canceling prior transactions, but only after the specified value in milliseconds. However, if read locks become available before the ForceCommitTimeout period is reached, canceling will not occur. See discussion of read locks under CommitTimeout.

Property Name	General Page: ForceCommitTimeout
Default Value	30000 (30 seconds)
Unit of Measure	Milliseconds
Data Type	Integer
Minimum Value	0 – Will not force a commit timeout.
Maximum Value	2147483647
Requires Restart	No
Alternate GUI Tool	None
Special Notes	None
Security Implications	None

CommitTimeout: Analysis Server processing operations need to acquire a write lock before it can commit a transaction. In order to acquire a write lock, no other read locks can be taken by another process or query. Therefore, Analysis Services needs to wait until all read locks are released. The transaction will wait for a period of time to acquire a write lock, as specified by the CommitTimeout property before rolling back.

Property Name	General Page: CommitTimeout
Default Value	0
Unit of Measure	Milliseconds
Data Type	Integer
Minimum Value	0 – Indicates that Analysis Services will wait indefinitely to acquire a write lock in order to commit a transaction.
Maximum Value	2147483647 Milliseconds, or approximately 25 days.
Requires Restart	No
Alternate GUI Tool	None
Special Notes	None
Security Implications	None

So what exactly goes on while cube is processed?

There are multiple steps of processing an OLAP object

1. First a new object is created with new version. So if you look under the DATA directory for OLAP (In my case it is \\ServerName\D$\MSAS10_50.MSSQLSERVER\OLAP\Data\CubeName.0.db) you will see that there 2 versions of each file which holds the version information. This you can see when cube is getting processed. After cube is processed successfully latest version is maintained. Let’s say I am processing Vendor dimension then version 1 file will be Vendor.1.(all).astore. Here 1 stands for version number
2. Once file is processed the file name will change to new version number Vendor.2.(all).astore
3. DDL definition of each dimension which in Vendor.1.dim.xml file under the tag <ObjectVersion>2</ObjectVersion>
4. Successful completion of processing results in “pending commit” lock being placed on the object being processed along with dependent objects.
5. If there are queries running against the object then queries will be allowed to continue with older version of the object (In our case version 1 of Vendor dimension) and all new queries will be directed to new version (version 2 of Vendor)
6. Now since we have set default value for property ForceCommitTimeout to 30 seconds (30000 ms) then any running queries will be allowed to run for 30 seconds and once time has expired then queries will be cancelled and version will be swapped.
7. But any queries executed after “pending commit” lock will stall for 30 seconds before then start execution.
8. Another setting is CommitTimeout, which causes the cube processing operation to timeout and rollback allowing currently executing queries to continue to completion.

Conclusion: While cube is being processed the cube is accessible to users as I explained above. However the downside of it inconsistent performance of queries. So to troubleshoot above error you need to see what queries were running while the cube was being processed. Once you know that there are queries running while cube is being processed then you can change either of the 2 settings

ForceCommitTimeout to set timeout of queries

CommitTimeout to timeout and rollback cube processing queries