SQL Server database snapshot

What is the use of SQL Server database snapshot and how to create SQL Server database snapshot?

Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots.

A database snapshot is a read-only, static view of the source database.Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation.A snapshot persists until it is explicitly dropped by the database owner.

Snapshots can be used for reporting purposes.In the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot’s creation.

Creating a series of snapshots over time captures sequential snapshots of the source database. Each snapshot exist until it is dropped.Each snapshot will continue to grow as  pages in original database are updated, you may want to conserve disk space by deleting an older snapshot after creating a new snapshot.

Steps to create database snapshot in SQL Server:

1.Create a new database or use existing database to create the database snapshots.


USE master
— Create database
USE test

2.Create table  and Insert values into table.


–create a Table
—Insert values
declare @a nchar(10)
set @a=1
while (@a<5)
insert into test1 values (@a,’1′,’1′)
set @a=@a+1

3.Create a snapshot  for a source database.


— Create Snapshot Database
(Name =’test’,
FileName=’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\testsnap.ss’)


Snapshottest database is created successfully.

4.Now let us view both the source database and snapshot database.


SELECT * FROM test.dbo.test1;
SELECT * FROM Snapshottest.dbo.test1;

Data’s in tables of source and snapshot database are same.


Size on disk  of the testsnap .ss could be viewed by opening its properties window.Let us note the  size on disk  of the source database.


5.Let us update existing rows in the table.


update test1 set a=0,b=0,c=0

6.After updating rows ,let us view the source and snapshot database.The values are updated only in source database,not in snapshot database.Because snapshot database is consistent with the source database as of the moment of the snapshot’s creation.


— Select from test and Snapshottest Database
SELECT * FROM test.dbo.test1;
SELECT * FROM Snapshottest.dbo.test1;


But size on disk is increased,It clearly shows that when we update data in Source database, it copies the old/existing data pages to Snapshot database. This is the reason why the size of the snapshot database is increasing while updating the source database.


7.We could revert the source database.Reverting overwrites updates made to the source database since the snapshot was created by copying the pages from the sparse files back into the source database.


— Restore old data from Snapshot Database
USE master

8.View the data in the tables from the source and the snapshot database after the restore from snapshot.


SELECT * FROM test.dbo.test1;
SELECT * FROM Snapshottest.dbo.test1;


9.We drop the snapshot database like any other database using drop database command.


— drop snapshottest
DROP DATABASE [snapshottest];

10. We can also create database snapshot on mirror database for load balancing.


How to import/Export data in SQL Server

SQL Server Import and Export Wizard can be used to copy data from a source to a destination. source and destination can be sqlserver or any other connection.

Steps to export data in SQL Server 2008:

In Microsoft SQL Server Management Studio, expand the databases.

Right-Click on the database you want to copy to another database->Tasks->Export data/import data.


SQL Server Import and Export Wizard window will open.


Click  Next to continue. Now, you will need to choose a Data Source. You can leave the Data source as SQL Server Native Client 11.0. Enter the Server name. Check windows Authentication  for your database. From the Database dropdown menu, select the name of your database.


Click on Next to continue.

In the Choose a Destination window, you can select a different option to export to Microsoft Access, Microsoft Excel,SQL Server database,etc.,


Then select the destination database,where you want to export your data.


Specify whether to copy tables or to copy the results of a query from the data source.Click Next.


Select source tables and click Next.


Check Run immediately.Click Next.


Click Finish to complete the wizard.


Close the wizard after completing it successfully.


How to create backups using database maintenance plan

You can create a database maintenance plan to automate the SQL Server database backups. SQL Server backup maintenance plan can be scheduled to backup the databases automatically or executed manually.

Follow the steps below to create a database backup maintenance plan and schedule it to execute automatically

1.Open SQL Server Management Studio, expand the Management node, and then expand the Management Plans node.



2.Right-click Maintenance Plans, click Maintenance Plan Wizard.


3.SQL Server Maintenance Plan Wizard window will appear.click next.


4.Then type  a name for this database backup plan.



  • Select schedule according to your need.Generally daily,weekly,monthly or hourly.




5.Select  the maintenance tasks,which you wanted to plan.


6.select the order of your plan.Click Next.


7.On the Define Back Up Database (Full) Task dialog box, specify information about the full backup. Specify database where this plan has to be applied.Press ok.Then Click Next.



8.On the Define Back Up Database (Transaction Log) Task dialog box, configure the transaction log backup. Click Next.


9. Choose the backup destination


10.On the Define Maintenance Cleanup Task dialog box, configure the cleanup tasks.specify the folder name where you take backups.Then specify the backup folders extension.Click Next.


11.On the Select Report Options dialog box, select whether to write the report to text file or send the report through email. Click Next.


  • Plan is in progress




12.Maintenance plan wizard is successfully created.Click Finish


SQL Server monitoring

Every SQL Server DBA would have faced situations similar to SQL Server not accepting connections for few minutes, SQL Server not responding for few minute or Applications not able to connect with SQL Server for few minutes. Before DBA’s gets alerted about the situation and starts troubleshooting the issue. Everything becomes normal. Challenge in this situations is it becomes very difficult to understand where the underlying problem was, It could be a network connectivity, Application server problem or It might be an issue with SQL Server itself. How do we collect diagnostic data to prove that SQL Server was stable at the time of issue (or) If the issue is with SQL Server then how to collect data we need for diagnosing the issue when there is issue?

SQL Monitor to monitor SQL Server Services

SQL Monitor monitors the SQL Server services and creates log if SQL Server service is down (or) If SQL Server is not accepting (or) SQL Server is not responding to Queries

How it works?

SQL Monitor checks the SQL Server in 3-Phases

1. Check the status of SQL Server service through the windows service control manager

2. If the service is running then check if SQL Server is accepting connections

3. If SQL Server is accepting Connections then probe to perform a simple query and see if SQL Server is responding properly.

4. If SQL Server is not accepting Connections then connect to SQL Server using DAC and take a stack dump.

How to Configure

1. Create a folder call SQLMonitor in C:\

2. Create a Text file called serverlist.txt to fill all the SQLServer information in your account.


Servername [TAB] Servicename;


Server1 MSSQLServer;

Server2 MSSQL$Prod;

3. Invoke command prompt and open attached SQLmonitor.EXE.


1. Multi-threaded . Each server and service is verified using its own thread so retrieving information from one server will not affect the pooling interval to other server.

2. Single exe can be scaled to monitor more than 1000 servers and 1000 services.

3. Uses few MB of memory and system resources.

You can Download SQL Monitor from this link


When you run Profiler trace from client systems or on server with large number of events you will see below wait types.


There is no way to completely avoid this wait type without stopping all the traces. We can reduce this waitypes by configuring server side trace instead of client side trace.

select * from sys.traces will give you information about all the traces. ( Status column 0 stopped and 1 active)

For the traces collected using profiler you will find a NULL Path. Profiler traces can cause large number of above waittypes.

Thread which raises the trace event is responsible to Get buffers to write event and write event. So collecting the trace on network share or on slow disk or using profiler can slow down the trace write and make the threads wait, sometimes we may also end with dead locked schedulers.

So ideally you have to avoid running profiler when you see below waits and use sp_trace_create if you like to capture the trace and pass the local path for tracefile parameter.

XP_readerrorlog fails with Failed to open loopback connection.

We might get below error when we open sqlserver errorlog in SSMS or using xp_readerrorlog or sp_readerrorlog


Msg 22004, Level 16, State 1, Line 0

Failed to open loopback connection. Please see event log for more information.

Msg 22004, Level 16, State 1, Line 0

error log location not found


1. If you get this error in Clustered instance of SQL Server then disable “shared memory protocol” in client configuration.

2. Check if you have wrong alias created and fix them.

3. UAC can also cause this (You might see following message in sqlserver errorlog “Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors”).when you run SSMS run it in elevated mode (right click SSMS–>Run as administrator).

4. OOM condition in sqlsever can also cause this error

5. Executing xp_readerrorlog from DAC connection can also cause this error.

TCP Provider: The semaphore timeout period has expired

TCP Provider: The semaphore timeout period has expired error from SQL Server agent and other applications at times.

1. Disable TCP Chimney.Refer KB:942861

2. If you are in windows 2003 Change the value of the processor affinity to match the number of processors in the system.Follow KB:892100

1.Click Start, click Run, type regedit, and then click OK.
2.Expand the following registry subkey:
3.Right-click ProcessorAffinityMask, and then click Modify.
4.In the Value data box, type one of the following values, and then click OK:
◦If you have two processors, use the binary value 0b11, or hex value 0x3.
◦If you have three processors, use the binary value 0b111, or hex value 0x7.
◦If you have four processors, use the binary value 0b1111, or hex value 0xF.
5.Quit Registry Editor.
Note The 0x0 or 0xFFFFFFFF values are used to disable the ProcessorAffinityMask entry.

3. Check if priority boost is enabled for SQL Server. If yes disable it.

4. Make sure there is no working set trim and system wide memory pressure. You can use second query in significant part of sql server process memory has been paged out to identify and follow the same blog to fix it)

5. Check if paged pool and non-paged is empty. (Event ID:  2019  in event log)

6. If you see this problem in cluster make sure you have set the network priority of “private heart beat” network higher than the “public” network.Refer KB:258750


Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction.

An error occurred while recovering database. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction.

You might get below errors when you try to restore database from SAN snapshot and see a Orphan Msdtc transaction Spid -2


Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.

An error occurred while recovering database Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:12345). Fix MS DTC, and run recovery again.


Change the sp_configure option
sp_configure ‘in-doubt xact resolution’,2
–Presume abort. Any MS DTC in-doubt transactions are presumed to have aborted.
DBCC dbrecover(‘Dbname’);

Addition details on above sp_configure option in http://msdn.microsoft.com/en-us/library/ms179586(v=sql.90).aspx

Note: Use above option with caution

SQL Server integration services fails to start.

If SQL Server integration services fails to start. Create a backup of DTExec.exe.config and MsDtsSrvr.exe.config file  in   “C:\Program Files\Microsoft SQL Server\90\DTS\Binn” and add  <generatePublisherEvidence enabled=”false”/> under runtime tag and the start the SSIS service.



Here is an example DTExec.exe.config file for SSIS 2005:
<requiredRuntime version=”v2.0.50727″/>
<generatePublisherEvidence enabled=”false”/>


Here is an example msdtssrvr.exe.config file for SSIS 2005:
<?xml version=”1.0″ encoding=”utf-8″ ?>
    <gcServer enabled=”true”/>
    <generatePublisherEvidence enabled=”false”/>
      <add name=”TraceClientConnections” value=”0″ />
      <add name=”TraceManagementCalls” value=”0″ />
      <add name=”ServerTraceSwitch” value=”0″ />

More details about the above tab is mentioned in http://blogs.msdn.com/b/amolravande/archive/2008/07/20/startup-performance-disable-the-generatepublisherevidence-property.aspx

How to view xml plan as graphical execution plan

Do you want to look XML execution plan as graphical execution plan?

It is simple save the xml plan with .sqlplan extension and then open with management studio.

Similarly to view graphical plan as XML execution plan. Save the file with .XML extension or rename the existing graphical plan with .xml extension

SSMS fails with below error when you connect (or) explore the databases

SQL Server management studio might fail with below error  when you connect (or) explore the databases.
Error in SQL Server 2012
TITLE: Microsoft SQL Server Management Studio ——————————
Object reference not set to an instance of an object. (Microsoft.VisualStudio.Platform.WindowManagement)
Error in SQL Server 2008 and SQL Server 2005
Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.VisualStudio.OLE.Interop.IServiceProvider’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{6D5140C1-7436-11CE-8034-00AA006009FA}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). (Microsoft.VisualStudio.OLE.Interop)

regsvr32 /u ieproxy.dll
regsvr32 ieproxy.dll

1. You would get above error if ieproxy.dll is not registered properly. Re register Ieproxy.dll.
C:\Program Files\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files\Internet Explorer>regsvr32 ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 ieproxy.dll
2 .Net framework corruption (Repair Dot Net)
3. regsvr32 actxprxy.dll
4. If the problem persists after doing above steps then search the registry for class-id mentioned  in error message (in this error it is 6D5140C1-7436-11CE-8034-00AA006009FA). Once you find the class in registry explore ProxyStubClsid32 and copy the GUID in default key. Search for the copied GUID again in the registry and locate the DLL for which we are failing and register that Dll.

System.OutOfMemoryException’ was thrown. (Microsoft.SqlServer.Management.Sdk.Sfc) error when we try to restore the database

When we try to restore database in SQL Server we get below error and restore database fails


TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
Exception of type ‘System.OutOfMemoryException’ was thrown. (Microsoft.SqlServer.Management.Sdk.Sfc)

We get above error when we have large number of records on backup history table.
Check the count of rows on below table

select count(1) from msdb.dbo.backupfile   with (nolock)
select count(1) from msdb.dbo.backupfilegroup  with (nolock)
select count(1) from msdb.dbo.restorefile  with (nolock)
select count(1) from msdb.dbo.restorefilegroup with (nolock)
select count(1) from msdb.dbo.restorehistory  with (nolock)
select count(1) from msdb.dbo.backupmediafamily with (nolock)

We may have to reduce the number of records in the above tables. We can manually delete records based on date (or) If we do not want backup history we can truncate all the above tables.


How to configure the size of SQLServer error log

When running SQL Server certain times we may have to limit the size and number of SQL server errorlogs

To limit the size of SQL Server errorlogs add a new registry key GetMaxErrorLogSizeKb under
By default, this key is absent. Modify the value to the size of errorlog(In Kb) you want to maintain.

To increase the number of log files, add a new registry key “NumErrorLogs” (REG_DWORD) under below location.
By default, this key is absent. Modify the value to the number of logs that you want to maintain.

To learn more about sp_readerrorlog and its parameters read Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG)

Deafault extended event system_health

The default extended event system health captures lot of useful information likeclr_allocation_failure

We can verify them using below scripts


CAST([target_data] as XML) [target_data]
FROM sys.dm_xe_session_targets a
INNER JOIN sys.dm_xe_sessions b ON b.[address] = a.[event_session_address]
--WHERE b.[name] = 'system_health'


ADD EVENT sqlclr.clr_allocation_failure(
ADD EVENT sqlclr.clr_virtual_alloc_failure(
ADD EVENT sqlos.memory_broker_ring_buffer_recorded,
ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded(
ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded,
ADD EVENT sqlos.wait_info(
    WHERE ([duration]>(15000) AND ([wait_type]>(31) AND ([wait_type]>(47) AND [wait_type]<(54) OR [wait_type]<(38) OR [wait_type]>(63) AND [wait_type]<(70) OR [wait_type]>(96) AND [wait_type]<(100) OR [wait_type]=(107) OR [wait_type]=(113) OR [wait_type]>(174) AND [wait_type]<(179) OR [wait_type]=(186) OR [wait_type]=(207) OR [wait_type]=(269) OR [wait_type]=(283) OR [wait_type]=(284)) OR [duration]>(30000) AND [wait_type]<(22)))),
ADD EVENT sqlos.wait_info_external(
    WHERE ([duration]>(5000) AND ([wait_type]>(365) AND [wait_type]<(372) OR [wait_type]>(372) AND [wait_type]<(377) OR [wait_type]>(377) AND [wait_type]<(383) OR [wait_type]>(420) AND [wait_type]<(424) OR [wait_type]>(426) AND [wait_type]<(432) OR [wait_type]>(432) AND [wait_type]<(435) OR [duration]>(45000) AND ([wait_type]>(382) AND [wait_type]<(386) OR [wait_type]>(423) AND [wait_type]<(427) OR [wait_type]>(434) AND [wait_type]<(437) OR [wait_type]>(442) AND [wait_type]<(451) OR [wait_type]>(451) AND [wait_type]<(473) OR [wait_type]>(484) AND [wait_type]<(499) OR [wait_type]=(365) OR [wait_type]=(372) OR [wait_type]=(377) OR [wait_type]=(387) OR [wait_type]=(432) OR [wait_type]=(502))))),
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)),
ADD EVENT sqlserver.error_reported(
    WHERE ([severity]>=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902)))),
ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)),
ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1)
    WHERE ([sqlserver].[is_system]=(1) AND [component]<>(4))),
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)),
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))

sp_send_dbmail blocked and waittype is preemptive_os_getprocaddress

This will happen if you use user transaction when calling sp_send_dbmail. Do not call sp_send_dbmail with in user transaction.If you open a transaction and
send a mail with some attachment in first connection and dont commit . Open a second connection and send a mail using sp_send_dbmail it will not be queued and wait
till the first connection commits.You will also see preemptive_os_getprocaddress wait for second connection