31 May 2011

Reporting Services Configuration Manager: "Invalid namespace"

http://support.microsoft.com/kb/960374/EN-US

Above will work for 2005 as well.

Method 2: Add a WMI namespace
Add a WMI namespace that corresponds to the namespace that the SQL Server 2008 Reporting Services Configuration Manager tool is trying to use. To do this, follow these steps:

Note This procedure refers to a sample instance that is named "SQL2008."

Locate the Reportingservices.mof file for the SQL Reporting Services 2008 instance that is named "SQL2008." By default, this file is located in the following folder:
C:\Progam Files\Microsoft SQL Server\MSRS10.SQL2008\Reporting Services\ReportServer\bin
Save or copy the Reportingservices.mof to a file that has a unique name, such as Reportingservicesalt.mof.
Open Reportingservicealt.mof file by using a text editor, such as Notepad.
On the Edit menu, click Replace.
In the Find what area, type the changed instance name, such as RS_SQL2008.
In the Replace with area, type the unmodified instance name, such as SQL2008.
Click Replace All.
Save the file, and then exit Notepad.
Open a command prompt, and then move to the same folder that you used in step 1.
Type mofcomp reportingservicesalt.mof, and then press ENTER.

NOTE:- it may need not to be same as file name reportingservicesalt just check and execute mofcomp reportingservicesalt.mof from folder location.


Note If the reporting services instance name contains an underscore (_), a dollar sign ($) or a number sign (#), you have to use method 1. Or you have to reinstall the instance.

29 May 2011

Suggested Max Memory Settings for SQL Server 2005/2008

It is pretty important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the host OS that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure. This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems.



This is for x64, on a dedicated DB server.

Physical RAM MaxMem Setting

2GB 1500

4GB 3200

6GB 4800

8GB 6700

12GB 10600

16GB 14500

24GB 22400

32GB 30000

48GB 45000

64GB 59000



This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003)

Physical RAM Target Avail RAM in Task Manager

< 4GB 512MB – 1GB

4-32GB 1GB – 2GB

32-128GB 2GB – 4GB

> 128GB > 4GB



You can set this value with Transact-SQL like this:

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

-- See what the current value is for 'max server memory (MB)'
EXEC sp_configure

-- Set max server memory = 2300MB for the server
EXEC sp_configure 'max server memory (MB)', 2300
GO
RECONFIGURE
GO

26 May 2011

RESTORE DATABASE is terminating abnormally.

http://blog.sqlauthority.com/2007/04/30/sql-server-fix-error-msg-3159-level-16-state-1-line-1-msg-3013-level-16-state-1-line-1/

Workaround:-

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE AdventureWorks
FROM DISK = 'C\:BackupAdventureworks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Data\datafile.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\logfile.ldf',
REPLACE

20 May 2011

SQL Server Performance

http://sql-articles.com/articles/dba/performance-data-collector-part-1

http://sql-articles.com/articles/dba/performance-data-collector-part-2

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

17 May 2011

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”

04 May 2011

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: