Wednesday, October 30, 2013

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)

 [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%",   
from msdb.dbo.backupset
--where database_name ='db1' and [Type]='D'
order by backup_set_id desc

Friday, October 25, 2013

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 

INSERT INTO dbo.Table_Name ( col1, col2, col3) 
SELECT col1, Col2 col3 FROM dbo.Table2 

Friday, August 09, 2013

[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.

Tuesday, July 09, 2013

SQL Server 2014 CTP1

SQL Server 2014 delivers mission critical performance across all workloads with in-memory built-in, faster insights from any data with familiar tools, and a platform for hybrid cloud

Microsoft SQL Server 2014 builds on the mission-critical capabilities delivered in the prior release by providing breakthrough performance, availability and manageability for your mission critical applications. SQL Server 2014 delivers new in-memory capabilities built into the core database for OLTP and data warehousing, which complement our existing in-memory data warehousing and BI capabilities for the most comprehensive in-memory database solution in the market.
SQL Server 2014 also provides new disaster recovery and backup solutions with Windows Azure, enabling customers to use their existing skills with the on-premises product offerings to take advantage of Microsoft’s global data centers. In addition, SQL Server 2014 takes advantage of new Windows Server 2012 and Windows Server 2012 R2 capabilities to give you unparalleled scalability for your database application in a physical or virtual environment.

Friday, June 07, 2013

TechEd 2013: Eron Kelly talks about SQL Server 2014

At TechEd 2013 in New Orleans, Megan Keller, SQL Server Pro Editorial Director, and I met with Eron Kelly, General Manager for SQL Server Marketing, to talk about the upcoming release of SQL Server 2014. The new SQL Server 2014 release will provide several significant new features. Related: Microsoft Announces SQL Server 2014

In-Memory OLTP Engine

The biggest feature in the upcoming SQL Server 2014 release is undoubtedly the In-Memory OLTP Engine (formerly code named Hekaton). The new In-Memory OLTP Engine will help you you to choose which tables go in memory. It will also help you to choose the stored procedure that will be compiled into machine code for high performance execution. EdgeNet, an early adopter, saw a 7X performance increase with no code changes.

New Azure Integration Options

There will also be new Azure integration options for backup and AlwaysOn Availability Groups. The new backup option is integrated into SQL Server Management Studio (SSMS) and it allows you to back up a SQL Server database to Azure. You can also use it to quickly restore to an Azure virtual machine (VM). AlwaysOn Availability Groups have also been extended to Azure, providing AlwaysOn in the cloud. This enables you to create asynchronous Availability Group replicas in Azure for disaster recovery. Like the new Azure backup, the ability to create Azure Availability Groups is integrated in SMSS.

Better Resource Management for Big Data

Another improvement that Kelly discussed with Megan and I is the ability to provide better resource management for big data. There's improved integration with Windows Server 2012’s storage enhancements. SQL Server 2014’s Resource Governor can take advantage of the automated storage-tiering provided by Windows Server 2012.

BI Improvements

Kelly also demonstrated some of the upcoming business intelligence (BI) improvements. Kelly illustrated how Data Explorer can provide new data visualizations, as well as how GEOFlow was able to provide a visual mapping of all the different TechEd attendees. He also pointed out that in the SQL Server 2014 release, PowerView will be able to work against multi-dimensional models in addition to tabular data models.

Significant Changes to Database Engine

For those of you following the SQL Server 2014 release cycle, you might note that the SQL Server 2014 release has skipped the traditional R2 release that Microsoft normally releases between major releases. Kelly explained that this was due to the significant changes that Microsoft needed to make to the database engine to support the new In-Memory OLTP Engine.

SQL Server 2014 Preview Expected in June 2013

Kelly told us that we could expect to see a preview of the SQL Server 2014 released this month, with general availability expected to be available in early 2014.

Source :

Thursday, May 30, 2013

Find Backup history for one week

use msdb
SERVERPROPERTY('Servername') AS Server_Name,
CASE bs.type
WHEN 'I' THEN 'Differential '
END AS Backup_type,
(bs.backup_size/1048576) AS Backup_size_MB ,bmf.Logical_device_name,
bmf.Physical_device_name FROM backupmediafamily AS bmf INNER JOIN backupset AS bs ON bmf.media_set_id = bs.media_set_id WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY bs.database_name,
Reference :

Thursday, April 18, 2013

Operating system error 112(error not found) encountered

When you RESTORE database if error message says 112 then you must look at the first point to check enough disk free space on specific drive where database is going to restore.

Error: 17053, Severity: 16, State: 1.
E:\MSSQL \DATA\Adventrueres.mdf: Operating system error 112(error not found) encountered.

Monday, March 11, 2013

Plan Caching and Reuse

Dynamic Management Views and functions are useful when exploring plan reuse and the following objects are most helpful:

The following sql query is useful to exploring the current plan cache contents in buffer pool:

SELECT DB_NAME( st.dbid) AS DatabaseName,
st.dbid AS Database_ID,
cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.text AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
 sys.dm_exec_query_plan(cp.plan_handle) AS qp

 sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.[dbid] = DB_ID()

Tuesday, March 05, 2013

Rebuilding all indexes on a table and specifying options

If you are planning rebuild index  to use this statement below , you must run update statistics statement after rebuild index. Statistics are always updated when you rebuild index.  But STATISTICS_NORECOMPUTE=ON disable the auto update statistics from updating the specific statistics for an index (or column-level statistics)

-- Try to avoid this options
USE AdventureWorks2012;
ALTER INDEX ALL ON Production.Product

UPDATE STATISTICS (Production.Product)


Alternatively you can use below options


USE AdventureWorks2012;
ALTER INDEX ALL ON Production.Product

--  For single index options

Source: Microsoft needs to correct this page.

Thursday, February 14, 2013

Microsoft® SQL Server® 2012 Service Pack 1

Microsoft has released Service Pack 1 for SQL Server 2012.  You can download from the following link:

Wednesday, February 13, 2013

Schedule to Rebuild Index for your user database

Here is the script you can use to Rebuild Index job to be scheduled to run specific time.

USE [msdb]

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

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA Index Maintenance_MyDBTest',
  @category_name=N'Database Maintenance',
  @notify_email_operator_name=N'DBA_Mail', @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'ReIndex Maintenance_MyDBTest',
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'USE MyDBTest
-- Ensure a USE statement has been executed first.
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(, @schemaname = QUOTENAME(
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 40 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 40.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
        IF @frag >= 40.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD WITH ( ONLINE = ON )'';
        IF @partitioncount > 1
            SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N''Executed: '' + @command;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
exec sp_updatestats
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'ReIndex Maintenance_MyDBTest',
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
GOTO EndSave



Friday, January 11, 2013

How to remove Key Lookup on your query plan

Key Lookup was Introduced in SQL Server 2005 Service Pack 2, the Key Lookup operator is a bookmark lookup on a table with a clustered index. The Argument column(Predicate) contains the name of the clustered index and the clustering key used to look up the row in the clustered index. Key Lookup is always accompanied by a Nested Loops operator. Query performance can be improved by adding a covering index on nonclustered index.

When you found a key lookup for a query execution plan window. The easiest way to remove key lookup from the execution plan is to add covering index for the specific column into that non-clustered in the table.

use AdventureWorks
sp_helpindex [Sales.SalesOrderDetail]

--Find list of index currently on table  [Sales.SalesOrderDetail]

index_name index_description index_keys
AK_SalesOrderDetail_rowguid nonclustered, unique located on PRIMARY rowguid
IX_SalesOrderDetail_ProductID nonclustered located on PRIMARY ProductID
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID clustered, unique, primary key located on PRIMARY SalesOrderID, SalesOrderDetailID

-- Run the this two queries with display estimated execution plan
Select ProductID 
From Sales.SalesOrderDetail 
Where ProductID = 776
select ProductID, OrderQty 
from Sales.SalesOrderDetail 
where ProductID =776

As you can see Key Lookup operator for second query. How would you simply remove from the query plan.?  Add an QrderQty column into INCLUDE on non clustered Index idx_SalesOrderDetail.

As you can see the Key Lookup has been removed from display execution plan

Source : Craig Freedman thoughts from his blog
Bookmark lookup is not a cheap operation.  Assuming (as is commonly the case) that there is no correlation between the non-clustered and clustered index keys, each bookmark lookup performs a random I/O into the clustered index.  Random I/Os are very expensive.  When comparing various plan alternatives including scans, seeks, and seeks with bookmark lookups, the optimizer must decide whether it is cheaper to perform more sequential I/Os and touch more rows using an index scan or a seek with a less selective predicate that covers all required columns or to perform fewer random I/Os and touch fewer rows using a seek with a more selective predicate and a bookmark lookup.

Wednesday, January 09, 2013

How do you find SQL Server version info

There are five place you can find a SQL Server version information.

--1 select @@version
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11:57:00 Copyright
(c) Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--2 exec sp
exec sys.sp_MSgetversion

10.50.1617.0 1 3

 -- 3 SQL Server Logs
Date 09/01/2013 07:12:16Log SQL Server (Current - 09/01/2013 17:18:00)Source
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11
:57:00 Copyright
(c) Microsoft CorporationEnterprise Evaluation Edition
on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

SELECT SERVERPROPERTY('edition'),SERVERPROPERTY('ProductVersion'),SERVERPROPERTY('ProductLevel')Enterprise Evaluation Edition 10.50.1617.0 RTM

--5 exec sys.xp_msver

Still you can find sql server version information from Windows Registry but i do not want to expose more on it here.