Thursday, January 14, 2010

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.

clip_image002

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

clip_image004

Monday, January 11, 2010

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,

image 
Schedule it on Windows Task Scheduler

image

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