SQL Server Cluster Offline

Registry replication in Microsoft Cluster Server

http://support.microsoft.com/kb/174070

How to change SQL Server parameters in a clustered environment when SQL Server is not online

http://support.microsoft.com/kb/953504

server-side protocol initialization error codes

ERROR STATE DESCRIPTION

0x03 Error starting shared memory support

0x04 All protocols disabled

0x0A Unable to initialize the TCP/IP listener

0x1C Server configured to listen on a specific IP address in a cluster environment

0x1E Duplicate IP address detected in network

0x35 Error starting named pipe support

0x36 Error starting VIA support

0x38 Error obtaining or using the Certificate for SSL

0x3A Unable to initialize the communication listeners

0x40 Unable to initialize the Shared Memory listener

0x50 Unable to initialize the Named Pipe listener

0x60 Unable to initialize the VIA listener

0x70 Unable to initialize the HTTP listenerhttp://www.blogger.com/img/blank.gif

0x80 Unable to initialize SSL support

=============================================================
http://blogs.msdn.com/b/sql_protocols/archive/2006/01/10/511330.aspx

SQL Server Resources goes to Failed state

Help on :- http://support.microsoft.com/kb/883732
http://www.blogger.com/img/blank.gif

or

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/0http://www.blogger.com/img/blank.gif1/13/sql-server-2005-resource-fails-to-come-online-on-cluster-after-changing-the-san-drive-to-add-more-disk-space.aspx

1. Change these settings on both Nodes
2. Change this for SQL Server and Agent under the cluster resources with the respective GUID of SQL Server.
3. Bring SQL online.
4. Reboot the boxes SQL has to be switched over.
5. If not Check the regedit for the settings.
6. If values are missing then go for checkpoint of cluster from CMD mode before this take a backup of registry.

More help on http://support.microsoft.com/kb/912397

For a default instance of SQL Server, run the following command:
cluster res "SQL Server (Instancename)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"


cluster . resource "SQL Server (MSSQLSERVER)/addcheckpoints:”HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL Server\SQLServerAgent”

Some Cluster Issues

Event ID 1135 — Cluster Service Startup

http://technet.microsoft.com/en-us/library/dd353973(WS.10).aspx



Event ID 1069 — Clustered Service or Application Availability

http://technet.microsoft.com/en-us/library/dd353893(WS.10).aspx



Event ID 1177 — Quorum and Connectivity Needed for Quorum


http://technet.microsoft.com/en-us/library/dd353872(WS.10).aspx



Event ID 4201 — TCP/IP Network Interface Connectivity

http://technet.microsoft.com/en-us/library/dd392958(WS.10).aspx

Some Cluster Issues

Event ID 1135 — Cluster Service Startup

http://technet.microsoft.com/en-us/library/dd353973(WS.10).aspx



Event ID 1069 — Clustered Service or Application Availability

http://technet.microsoft.com/en-us/library/dd353893(WS.10).aspx



Event ID 1177 — Quorum and Connectivity Needed for Quorum


http://technet.microsoft.com/en-us/library/dd353872(WS.10).aspx



Event ID 4201 — TCP/IP Network Interface Connectivity

http://technet.microsoft.com/en-us/library/dd392958(WS.10).aspx

Cluster Failover Alert, When SQL Agent Restarts

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Administrative Job: Custom' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Administrative Job: Custom'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Service_Restart_Notification',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Notify key users of services restart',
@category_name=N'Administrative Job: Custom',
@owner_login_name=N'Sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email Alerts',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''DBA'',
@body = ''This is an informational message only: SQL services possibly restarted on INDBLRSQL. Please restart any dependent application services after verifying status of Databases.'',
@subject = ''SQL Services Restarted '' ;
',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'On Startup',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110405,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked

Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked, with error similar to the below,

“Cannot open backup device 'D... The package execution fa... The step failed.,00:00:01,0,0,,,,0”



Detailed Error Message
==================

Executed as user: MachineName\SYSTEM. ...9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:15:03 AM Progress: 2008-07-24 07:15:04.11 Source: {134957B2-5C5F-4D4F-BDB7-ECAC9C3D8E20} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-07-24 07:15:04.69 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Program F".: 100% complete End Progress Error: 2008-07-24 07:15:04.71 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [Mstest ] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak' WITH NOFORMAT, NOINIT, NAME = N'MStest _backup_20080724071504', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Cannot open backup device 'D... The package execution fa... The step failed.



Analysis
=======

When the “Create a sub-directory for each database” option is selected, the Maintenance task executes the xp_create_subdir procedure to create a directory with the Database Name. If you carefully look at the Detailed Error message above, you will see that there is a space character in the Database name.
“D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MStest \Mstest _backup_200807240715.bak”


If you try to run the Backup command extracted from the above error in the Query windows it gives a more informative error ,

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak'. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.



Cause:
======
The Operating System does not allow you to create a folder with trailing spaces. When xp_create_subdir creates the folder with the space, the OS creates the folder but without the trailing space.

For example you can try this command EXECUTE master.dbo.xp_create_subdir N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \'

Now when the backup command fires it fails to read the directory, since there is no directory with a space created.


Resolution
=========
Follow the below method to remove the trailing space from the Database Name

1.
Right click and rename the database to any name. (This is because Management Studio will not allow you to remove the trailing space, since it thinks there is no change made to the name and that a database already exist with the same name
2.
Now rename the database to the original name without the space.
3.
Reconfigure your Maintenance Plan