SQL Server 2005 Cluster: SQL Server Service not coming online, error on TCP port is already in use

 

SQL Server Error Log:

2010-06-04 15:19:37.520 Server Server is listening on [ 10.0.0.136 <ipv4> 2813].

2010-06-04 15:19:37.530 Server Error: 26023, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.

2010-06-04 15:19:37.530 Server Error: 17826, Severity: 18, State: 3.

2010-06-04 15:19:37.530 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2010-06-04 15:19:37.530 Server Error: 17120, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event Viewer Error

Event Type: Error

Event Source: MSSQL$PROD2K5ISD

Event Category: (2)

Event ID: 26023

Date: 4/06/2010

Time: 2:51:31 PM

User: N/A

Computer: PROD2K5ISD

Description:

Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

Resolution

The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid

SQL Server 2005 /2008 Express Edition Backup automation

SQL Database Backup Script

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)   select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB','AdventureWorks') while @IDENT is not null begin     SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT /*Change disk location here as required*/     SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''C:\SQLBACKUP\Backup\'+@DBNAME+'.BAK''WITH INIT' PRINT @SQL     EXEC (@SQL)     select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB','AdventureWorks') end

 

DOS Batch files to backup the databases

@echo off c: if not exist c:\SQLBackup\Logs MD C:\SQLbackup\Logs if not exist c:\SQLBackup\Backup MD C:\SQLbackup\Backup cd c:\SQLBackup Echo Backup Started >> c:\SQLBackup\Logs\SQLBAckup.log Date /T > c:\SQLBackup\Logs\SQLBAckup.log Time /T >> c:\SQLBackup\Logs\SQLBAckup.log Echo. echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log isql -S (local)\Express -E -i c:\sqlbackup\backup.sql >> c:\SQLBackup\Logs\SQLBAckup.log echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log echo. Date /T >> c:\SQLBackup\Logs\SQLBAckup.log Time /T >> c:\SQLBackup\Logs\SQLBAckup.log echo Backup Finished >> c:\SQLBackup\Logs\SQLBAckup.log echo. echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log

 

Folders will look like,

 
Schedule it on Windows Task Scheduler

That's it the SQL Server 2005 / 2008 Express Edition database backup is automated now!