Failed to notify ''operator name'' via email?...

First Check the value for Database mail profile is NUll

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile'

If So

select 'exec master.dbo.xp_instance_regwrite N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'', N''DatabaseMailProfile'', N''REG_SZ'', N''' + name + ''''
from msdb.dbo.sysmail_profile
where profile_id in (Select profile_id from msdb.dbo.sysmail_principalprofile )

Copy the output and Execute, it will be as below

exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DBA'

Now Restart the SQL Agent. mail will work now.


------------------------------ OR --------------------------------

Verify below Steps are configured properly or not

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

Configuring above steps will work make DB mail to work properly.

Connecting to a Remote Integration Services Server

To connect to Integration Services on a Remote Server

1.
Open SQL Server Management Studio.

2.
Select File, Connect Object Explorer to display the Connect to Server dialog box.

3.
Select Integration Services in the Server type list.

4.
Type the name of a SQL Server Integration Services server in the Server name text box.




Note
The Integration Services service is not instance-specific. You connect to the service by using the name of the computer on which the Integration Services service is running.


5.
Click Connect.







Eliminating the "Access Is Denied" Error

--------------------------------------------------------------------------------



When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

To configure rights for remote users on Windows Server 2003 or Windows XP

1.
If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

2.
Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

3.
Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

4.
Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

5.
Right-click on MsDtsServer and select Properties.

6.
In the MsDtsServer Properties dialog box, select the Security tab.

7.
Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

8.
In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

9.
Click OK to close the dialog box.

10.
Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

11.
Close the MMC snap-in.

12.
Restart the Integration Services service.


To configure rights for remote users on Windows 2000 with the latest service packs

1.
Run dcomcnfg.exe at the command prompt.

2.
On the Applications page of the Distributed COM Configuration Properties dialog box, select MSDTSServer and then click Properties.

3.
Select the Security page.

4.
Use the two separate dialog boxes to configure Access Permissions and Launch Permissions. You cannot distinguish between remote and local access - Access permissions include local and remote access, and Launch permissions include local and remote launch.

5.
Close the dialog boxes and dcomcnfg.exe.

6.
Restart the Integration Services service.




Connecting by using a Local Account

--------------------------------------------------------------------------------



If you are working in a local Windows account on a client computer, you can connect to the Integration Services service on a remote computer only if a local account that has the same name and password and the appropriate rights exists on the remote computer.



Delegation Is Not Supported

--------------------------------------------------------------------------------



SQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.








Thanks to :- http://msdn.microsoft.com/en-us/library/aa337083.aspx

Backup Log Database with truncate _only

It became deprecated Command and removed in 2008. You can achieve the same result by sending the file to the NUL blackhole

BACKUP LOG [DBNAME] TO DISK='NUL'
GO
DBCC SHRINKFILE("LOGFILE",0)


OR

alter database DBNAME set recovery simple
go
alter database DBNAME set recovery full
go
sp_helpdb 'DBNAME '
GO




use DBNAME
go
dbcc shrinkfile(LOGFILE,0)

Cluster node 'SQLSVRHOST00' was removed from the active failover cluster membership. The Cluster service on this node may have stopped.

= Event ID 1135 — Cluster Service Startup =
===========================================
http://technet.microsoft.com/en-us/library/dd353973(WS.10).aspx

Event Details

Product: Windows Operating System
ID: 1135
Source: Microsoft-Windows-FailoverClustering
Version: 6.1
Symbolic Name: EVENT_NODE_DOWN
Message: Cluster node '%1' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.


Resolve

Check network hardware and configuration

If you do not currently have Event Viewer open, see "Opening Event Viewer and viewing events related to failover clustering." After reviewing event messages, choose actions that apply to your situation:
Run the Validate a Configuration Wizard, selecting only the network and inventory tests. For more information, see "Using the Validate a Configuration Wizard to review the network configuration."
Check the system event log for hardware or software errors related to the network adapters on this node.
Check the network adapter, cables, and network configuration for the networks that connect the nodes.
Check hubs, switches, or bridges in the networks that connect the nodes.

To perform the following procedures, you must be a member of the local Administrators group on each clustered server, and the account you use must be a domain account, or you must have been delegated the equivalent authority.

Using the Validate a Configuration Wizard to review the network configuration

To use the Validate a Configuration Wizard to review the network configuration:
1.To open the failover cluster snap-in, click Start, click Administrative Tools, and then click Failover Cluster Management. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the Failover Cluster Management snap-in, in the console tree, make sure Failover Cluster Management is selected. Then under Management, click Validate a Configuration.
3.Follow the instructions in the wizard to specify the cluster you want to test.
4.On the Testing Options page, select Run only tests I select.
5.On the Test Selection page, clear all check boxes except those for the Network tests.
6.Follow the instructions in the wizard to run the tests.
7.On the Summary page, click View Report.

Opening Event Viewer and viewing events related to failover clustering

To open Event Viewer and view events related to failover clustering:
1.If Server Manager is not already open, click Start, click Administrative Tools, and then click Server Manager. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the console tree, expand Diagnostics, expand Event Viewer, expand Windows Logs, and then click System.
3.To filter the events so that only events with a Source of FailoverClustering are shown, in the Actions pane, click Filter Current Log. On the Filter tab, in the Event sources box, select FailoverClustering. Select other options as appropriate, and then click OK.
4.To sort the displayed events by date and time, in the center pane, click the Date and Time column heading.

Verify

To perform this procedure, you must be a member of the local Administrators group on each clustered server, and the account you use must be a domain account, or you must have been delegated the equivalent authority.

Verifying that the Cluster service is started on all the nodes in a failover cluster

To verify that the Cluster service is started on all the nodes in a failover cluster:
1.To open the failover cluster snap-in, click Start, click Administrative Tools, and then click Failover Cluster Management. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the Failover Cluster Management snap-in, if the cluster you want to manage is not displayed, in the console tree, right-click Failover Cluster Management, click Manage a Cluster, and then select or specify the cluster that you want.
3.If the console tree is collapsed, expand the tree under the cluster you want to manage, and then click Nodes.
4.View the status for each node. If a node is Up, the Cluster service is started on that node.

Another way to check whether the Cluster service is started is to run a command on a node in the cluster.

Using a command to check whether the Cluster service is started on a node

To use a command to check whether the Cluster service is started on a node:
1.On the node that you are checking, click Start, point to All Programs, click Accessories, right-click Command Prompt, and then click Run as administrator.
2.Type:
CLUSTER NODE /STATUS

If the node status is Up, the Cluster service is started on that node.

Tail-log Backup without the Primary Data File

While I was reading a post, written by Vinod Kumar (blog | twitter) about Files and FileGroups with SQL Server, I found an interesting quiz question: “Do we need the primary data file available to backup your transaction log after a crash?“

The answer to the above question is No, if the transaction-log file is not damaged, then we can initiate a log backup to backup the data till the time of the crash, since last log backup. To support my answer, I have created the following test script. In the first part of the script, I have created a test database named “TestDB” and change its recovery model to “Full”. The recovery model of the model database on my SQL 2008 R2 Express instance is set as Simple, so I manually change the recovery model of TestDB to Full.

use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go

Since I have created the database, its time to initiate a full backup of the database. Next I will add a demo tables with two columns and inserted some data into the table. After verifying the data in the table, initiate the first transaction-log backup.

-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go

Since the first log backup is completed, lets add some additional data into the table in the TestDB database. After inserting the additional row into the table, I will verify the same.

-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/

Now I am going to crash the TestDB database. I am going to shutdown my SQL Server Express instance and manually delete the data file of the TestDB database. Once the file is deleted, restart the SQL Server instance, and try to access data from the database. The following error will occur: “Database ‘TestDB’ cannot be opened due to inaccessible files or insufficient memory or disk space“

-- try to access the table, will get error as data file is missing
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go

As I am unable to access the database, I need to backup the transaction log to retrieve the data, which was inserted into the database after the last transaction-log backup, to minimize the data loss. The syntax to initiate the tail-log backup is as follows:

-- Initiate the tail log backup
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */

If the transaction-log file of the database is not damaged, then the above command will create the log backup (also known as tail-log backup). This completes the answer to the given question; however, a backup is never successful, if it can’t be restored. So I am going to test, whether I can restore the database to the same point, when the crash occurred, using the available backups. The next part of the script will restore the database and verify the data in the database after the restoration is completed.

use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go

The database is recovered successfully using the available backups and the complete data is also recovered. If we place the data file and the t-log files in different drives, in case, if the drive containing the data file is damaged, then we can retrive the data if we are able to backup the t-log. However, if the drive containing the t-log files is crashed, then we are going to face some data loss.

The complete script to reproduce the above scenario is mentioned below:

/* Create Tail-log backup after a crash, if the primary data file is missing
------------------------------------------------------------------------------
@skganguly
*/
use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go
-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go
-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/
-- initiate the crash, for this demo, I've shutdown the server and
-- delete the data file manually, to verify whether the tail-log backup is
-- possible or not if the data file is not available
-- try to access the table
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go
-- Database 'TestDB' cannot be opened due to inaccessible files
-- or insufficient memory or disk space
/* Initiate the tail log backup*/
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */
/* The script will not be successful, if we can't restore the tail log.
Thus, we are going to restore the database and verify the data before cleanup */
use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go
-- clean up the database
use [master]
drop database [TestDB]
go

=======================================
Thanks To :- http://sudeeptaganguly.wordpress.com/2011/04/15/taillogbackupwithoutdatafile/

Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)

Thanks to :- http://blog.sqlauthority.com/2011/04/13/sql-server-fix-error-msg-3201-level-16-cannot-open-backup-device-operating-system-error-5access-is-denied/



Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:\AdventureWorks.bak’. Operating system error 5(Access is denied.).

Fix/Resolution/Workaround:

I checked which user account my SQL Server services are running.

I gave full permission to that account to get read of the error.

NOTE: I have checked with my personal machine and not on production server. Please check with your network administrator for permission on production server.

Hosting a Report Server Database in a SQL Server Failover Cluster

SQL Server provides failover clustering support so that you can use multiple disks for one or more SQL Server instances. Failover clustering is supported only for the report server database; you cannot run the Report Server service as part of a failover cluster.

To host a report server database on a SQL Server failover cluster, the cluster must already be installed and configured. You can then select the failover cluster as the server name when you create the report server database in the Database Setup page of the Reporting Services Configuration tool.

Although the Report Server service cannot participate in a failover cluster, you can install Reporting Services on a computer that has a SQL Server failover cluster installed. The report server runs independently of the failover cluster. If you install a report server on a computer that is part of a SQL Server failover instance, you are not required to use the failover cluster for the report server database; you can use a different SQL Server instance to host the database.

=======================================================================
So on a Active passive node follow below steps,

1) On active node we already selected reporting services and installed
2) On passive node there is no Reporting services installed as its not a clustered feature
3) Install Reporting services as a named instance
4) configure RS with clustered name of SQL Server, by using existing URL and Report Server DB
5) Take a backup of encryption key and restore on passive node.
6) Now we can access the reports on Fail over.