Linked Server Transaction error Msg 7395, Level 16, State 2, Line 273

If you are using nested transaction in your stored procedure you will get the same error. Solution to this error is to SET XACT_ABORT  TO ON;  in your stored procedure.

SQL Server 2016 public preview

SQL Server 2016 public preview coming this summer and most important feature Real-time Operational Analytics & In-Memory OLTP

http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx
 

Error: 17053, Severity: 16, State: 1. Restore failed

When you RESTORE database if you see the below SQL error Operation System 112

Then you must look at the first point to have a enough disk free space on specified drive where database is going to restore.

Message

Error: 17053, Severity: 16, State: 1.

E:\MSSQL \DATA\Adventrueres.mdf: Operating system error 112(error not found) encountered.

Cannot open user default database. Login failed. Login failed for user 'xx'

I have been very busy for last couple of months so i could not get chance to post earlier. I hope and continue to post more technical stuff on this blog this year 2014.

If you are sysadmin, Your default database set to one of the user database. If you decide to take offline for your default database before you set default to another database.

You will get the following error.
Cannot open user default database. Login failed. Login failed for user 'xx'

There are the steps below login to server to access master database and then you can set your default database.

Issue:
1. Connected as a sysadmin (windows authentication domain\username)
2. Connect to database server
3. Take offline  your database DB_WH_Report
-- I connected
4. Disconnect to Server
When you try to connect back
Error :
Cannot open user default database. Login failed. Login failed for user 'xx'

Solution:
1. Connect to Server
2. Click Option to expand  Connection Propertise
3. Connect to Database Type master
4. Click Connect button

Find SQL Server database backup compression

If you would like to know about the backup file compression on Sql Server 2008R2 and later version you can use the below scripts. 

Note: Systems databases are not compressed for backup (master,model and msdb)

select 
 server_name,
 database_name,
 [type] AS Backup_type,
 CAST(backup_size /1048576 AS DECIMAL (10,2))  AS [Backup_Size (MB)],
 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))  AS  [Compressed_Backup_Size (MB)],
 100- ((compressed_backup_size/backup_size)*100)   as "Compressed%",   
 backup_finish_date
from msdb.dbo.backupset
--where database_name ='db1' and [Type]='D'
order by backup_set_id desc

Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

When you try to insert rows to identity column , you should include set identity_insert is ON 

SET IDENTITY_INSERT Table_Name ON 
GO 
INSERT INTO dbo.Table_Name ( col1, col2, col3) 
SELECT col1, Col2 col3 FROM dbo.Table2 
GO 
SET IDENTITY_INSERT Table_Name OFF 
GO 

[264] An attempt was made to send an email when no email session has been established

1. In SQL Server Management Studio, right-click SQL Server Agent and then select "Properties."
2. Click "Alert System"
3. Click "Enable mail profile"
4. Click "OK"5. Restart "SQL Server Agent"

Now you can test your sql jobs to get a email notification.