Find if SQL Server is clustered and which is the active node

SELECT
      SERVERPROPERTY('IsClustered') as _1_Means_Clustered ,
      SERVERPROPERTY('Edition') as Edition ,
      SERVERPROPERTY('ProductVersion') as Version  ,    
      SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ActiveNode

Attach database without LDF file

I came across a situation when a database was detached and accidentally the log (.LDF) file was deleted. I wanted to recover the database without having log file.

Here is how to do this.

First I am detaching the database for which files are located here

D:\MSSQL\Data\TestDB.mdf

E:\TransactionLogs\TestDB_log.ldf

USE [master]

GO

ALTER DATABASE [TestDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'TestDB'

GO

After detaching the database I manually deleted the LDF file

Now first attach the database without files.

USE master;

GO

EXEC sp_detach_db @dbname = 'TestDB';

GO

At this time you will not see the database in SSMS under databases

Once database is attached use sp_attach_single_file_db SP to attach the DB with just MDF file. LDF file will be automatically created at default location specified in server properties.

EXEC sp_attach_single_file_db @dbname = 'TestDB',

    @physname =

N'D:\MSSQL\Data\TestDB.mdf';

Now run following query to confirm both data and log files

USE TestDB

select * from sys.database_files

--D:\MSSQL\Data\TestDB.mdf

--E:\TransactionLogs\TestDB_log.ldf

Find more details about sp_attach_single_file_db here

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

There is another way to recover database without LDF files.

USE master;

GO

sp_detach_db TestDB;

GO

CREATE DATABASE TestDB

      ON (FILENAME = 'D:\MSSQL\Data\TestDB.mdf') FOR ATTACH ;

GO

Find more details about CREATE DATABASE FOR ATTACH here

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

SQL Server Maximum Concurrent connections and worker threads

SQL Server Maximum Concurrent connections and worker threads

Maximum number of concurrent user connections allowed by SQL Server 2008 and above is 32767

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

Number of worker threads is

Number of CPUs	32-bit computer	64-bit computer
<= 4 processors	256	512
8 processors	288	576
16 processors	352	704
32 processors	480	960

Source: http://technet.microsoft.com/en-us/library/ms187024(v=sql.90).aspx

You can also find current thread count by using either of the queries

select max_workers_count from sys.dm_os_sys_info

select count(*) from sys.dm_os_threads

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;

}