Read Committed Isolation


IF Exists(Select 1 from sys.objects where name='foobar' and type='U')
Begin
Drop Table foobar
End
Create Table foobar (sno int,sname varchar(20))
Insert into foobar
Values(1,'Spurs'),(2,'Rockets'),(3,'Pelicans')

/*PRO: Read Committed does not allow Dirty Reads.*/
--Session 1

Begin Tran
Update foobar set sname='Bobcats' where sno=3
Waitfor Delay '00:00:05'
Commit

--Session 2

Set Transaction Isolation Level Read Committed
Begin Tran

--Data is not read until the transaction either committed or Rolled back on Session 1.

Select * from Foobar
Commit

/*CON: Read Committed allows Phantom Reads.*/
--Session 1

Set Transaction Isolation Level Read Committed
Begin Tran
Select * from Foobar
Waitfor Delay '00:00:05'

--Phantom Reads(Data read in the same transaction changes)

Select * from Foobar
commit

--Session 2

Set Transaction Isolation Level Read Committed
Begin Tran
Update foobar set sname='Celtics' where sno=3
Commit

Read UnCommitted Isolation

IF Exists(Select 1 from sys.objects where name='foobar' and type='U')
Begin
Drop Table foobar
End
Create Table foobar (sno int,sname varchar(20))
Insert into foobar
Values(1,'Spurs'),(2,'Rockets'),(3,'Pelicans')

–Session 1
Begin Tran
Update foobar set sname='Bobcats' where sno=3
Waitfor Delay '00:00:03'
rollback

–Session 2
Set Transaction Isolation Level Read Uncommitted
Select * from Foobar
–Dirty Read here.
Waitfor Delay '00:00:05'
Select * from Foobar

SQL Query to Find SQL Cluster Nodes

Below is the SQL Query to find the sql cluster nodes

If(serverproperty('IsClustered'))=1
Begin
SELECT @@servername as DatabaseServer,NodeName,
case when serverproperty('instancename') is null then 'Default Instance - MSSQLSERVER' Else serverproperty('instancename') End as InstanceName,
Case when serverproperty('Computernamephysicalnetbios')=NodeName then 'Active' else 'Passive' End as NodeStatus
FROM sys.dm_os_cluster_nodes
order by Case when serverproperty('Computernamephysicalnetbios')=NodeName then 'Active' else 'Passive' End
End
Else
Begin
Select 'This is database Server is not clustered.'
End


Sample Output :
NodeName

Finding LastDayofPreviousMonth, FirstDayofCurrentMonth, LastDayofCurrentMonth using a Given date

Using the below query we can get nth month’s Last Day of Previous Month,First Day of Current Month and Last Day of Current Month for any given date.
--Works in all SQL Versions
Select getdate() as CurrentDate,
dateadd(day,-day(getdate()),dateadd(month,n,getdate())) as [LastDayofPreviousMonth],
dateadd(day,-day(getdate())+1,dateadd(month,n,getdate())) as [FirstDayOfCurrentMonth],
dateadd(day,-day(getdate()),dateadd(month,n+1,getdate())) as [LastDayofCurrentMonth]

--Works in SQL 2012 and above
Select EOMonth(getdate(),n-1) as [LastDayofPreviousMonth],
DateAdd(day,1,EOMonth(getdate(),n-1)) as [FirstDayOfCurrentMonth],
EOMonth(getdate(),n) as [LastDayofCurrentMonth]

Example:
Select getdate() as CurrentDate,
dateadd(day,-day(getdate()),dateadd(month,2,getdate())) as [LastDayofPreviousMonth],
dateadd(day,-day(getdate())+1,dateadd(month,2,getdate())) as [FirstDayOfCurrentMonth],
dateadd(day,-day(getdate()),dateadd(month,2+1,getdate())) as [LastDayofCurrentMonth]

Dates#1

Select EOMonth(getdate(),2-1) as [LastDayofPreviousMonth],
DateAdd(day,1,EOMonth(getdate(),2-1)) as [FirstDayOfCurrentMonth],
EOMonth(getdate(),2) as [LastDayofCurrentMonth]

Dates#2

Numbered Stored Procedure

I was reading Msdn article on contained databases and found out that one of the restrictions on contained databases “database cannot have Numbered Stored Procedures”.

I honestly never heard of “Numbered Stored Procedures” and quick a Google search came up with couple of articles including this Msdn article. It was quite surprising to know something like existed for a while and I had no idea about. Microsoft does not recommend using feature and this is going to be deprecated in future versions(and rightly so,i believe)

Basically, Numbered Procedures are similar to regular stored procedures but they can be grouped together with ‘;’ followed by number.
The idea of this is so that similar procedures with slight variation of code can be combined.

create procedure usp_cdcproc(@a int)
as
begin
Select 'This is Numbered Stored Procedure '+try_convert(varchar(1),@a)
end
go
create procedure usp_cdcproc;2(@a int)
as
begin
Select 'This is Numbered Stored Procedure '+try_convert(varchar(2),@a)
end
go
create procedure usp_cdcproc;3(@a varchar(2))
as
begin
Select 'This is Numbered Stored Procedure '+@a
end

Select name,Object_ID,type_desc from sys.procedures
ProcCreation

Select * from sys.numbered_procedures
NumberedProc

Select object_name(object_Id) as [Procedure Name],* from sys.numbered_procedure_parameters
ProcParameters

While I do not know how prominently they are used(I do not think they are) but apparently we can create group of stored procedures but we cannot drop them individually. The entire group will be dropped.

Restore Script

A DBA’s main responsibility is to protect the organization’s databases and minimize data loss from any kind of disaster(nature\human\corruption). While there are several ways to achieve this,one common solution is database backups.Database backups are crucial to any organization and it is very important to have a defined RTO and RPO objectives before scheduling backups.It is equally important to verify that backup plan meets the RTO/RPO objectives and this can be done by restoring the backups. This also ensures the backups are,in fact,restoreable. We can validate backups by doing Restore VerifyOnly From Disk = 'BackupFilePath.bak'. Unfortunately, this will not provide complete guarantee on backup restorabality. The only way,we can make sure is by actually restoring the backups and this way, we validate not only the restorability of the backups but also confirm whether the backup\restore plan satisfies RTO/RPO objectives.

So,In order to accomplish this, I made a stored procedure that takes InstanceName, DatabaseName, DataFilePath, LogFilePath,BackupFromDate and BackupToDate as inputs and restores the database.The script can:
1.Restore only database level backups(Full/Diff/TLog).
2.Restore striped backups.
3.Restore latest full/diff/Tlog backups,if dates are supplied,it will restore backups taken within that timeframe.
4.Restores database by appending the Instance Name to the database name[InstanceName_DBName].
5.Restores all the files to one directory.
6.Restores database with multiple filegroups/files.
7.check if all the backup files exist,if not,it will restore only up to the available files.
8.Restore by replacing existing db,if @replace parameter is set to 1.

Scope for Future Improvements:
1.Ability to Restore filegroups and files.
2.Better Error Handling
3.Ability to restore on to multiple drives, as needed.

Syntax:
1.This replaces the existing restored database:
Exec usp_restoredb @instanceName = 'Anulu',@DbName= 'TlImports',@DbDataFilePath='G:\Data\',
@DbLogFilePath = 'F:\Log\',@BackupFromDate = null,@BackupToDate = null,@Replace=1

2.This simply restores the database(does not replace db)
Exec usp_restoredb @instanceName = 'Anulu',@DbName= 'SQL_DW',@DbDataFilePath='G:\Data\',
@DbLogFilePath = 'F:\Log\',@BackupFromDate = null,@BackupToDate = null,@Replace=0

1.The main procedure requires the below scalar function to check whether the backup file exists or not:
Create Function [dbo].[fnc_CheckFilePath](@FileName varchar(300))
RETURNS int
AS
BEGIN
Declare @File_Exists int
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
RETURN @File_Exists
END

2. Below is the main procedure that restores the database.
Create Procedure [dbo].[usp_RestoreDB] (@InstanceName varchar(200),@DbName varchar(200),@DbDataFilePath varchar(20),@DbLogFilePath varchar(20),
@BackupFromDate Datetime,@BackupToDate Datetime,@Replace bit)
AS
BEGIN
SET XACT_ABORT ON
/***Set the restored database name**/
Declare @RestoreDB varchar(200)
Set @RestoreDB = replace(@InstanceName,'\','_')+'_'+@DBNAME
IF (Select 1 FROM Master.dbo.Sysdatabases where name = @RestoreDB)=1 and (@Replace=0)
BEGIN
RAISERROR('Database already exists on the server.If you want to replace it,set @Replace parameter',20,1)WITH LOG
END
/***This Code below gets the backup history of the database from it's latest Full Backup and puts in a table variable************/
DECLARE @Temp TABLE(BackupSetID BIGINT,DatabaseName VARCHAR(200),PathDir VARCHAR(200),Media_set_id bigint,
BackupName varchar(200),BackupFinishDate datetime,[Type] char(1),Backup_Size bigint,Recovery_Model varchar(20),Position int,FileExists int)

DECLARE @Query NVARCHAR(max),@sqlParm nvarchar(max)
SET @sqlParm=N'@DbName varchar(200),@BackupFromDate datetime,@BackupToDate datetime'

SET @Query = 'select A.backup_set_id,A.database_Name,B.Physical_device_name,A.media_set_id,A.name,A.backup_finish_date,
A.type,A.backup_size,A.recovery_model,position'+
+' from '+QUOTENAME(@InstanceName)+'.msdb.dbo.backupset A INNER JOIN '+QUOTENAME(@InstanceName)+'.msdb.dbo.backupmediafamily B on A.Media_set_id = B.Media_set_id'+
+' where database_name =@DbName and is_copy_only = 0 and backup_set_id >='+
+' (select max(backup_set_id) from '+QUOTENAME(@InstanceName)+'.msdb.dbo.backupset where database_name =@DbName and type =''D'' and is_copy_only=0'
+' and Backup_finish_Date between coalesce(@BackupFromDate,''01/01/1900'') and coalesce(@BackupToDate,getdate()))'

Insert into @Temp(BackupSetID,DatabaseName,PathDir,Media_set_id,BackupName,BackupFinishDate,[Type],Backup_Size,Recovery_Model,Position)
EXECUTE SP_ExecuteSQL @Query,@sqlParm,@dbname=@dbname,@BackupFromDate=@BackupFromDate,@BackupToDate=@BackupToDate
Update @Temp SET FileExists=dbo.fnc_CheckFilePath(PathDir)
/***DELETING THE BACKUPS NOT REQUIRED FOR RESTORE**/
DELETE FROM @TEMP WHERE FileExists=0 and Type='I'
DELETE FROM @TEMP WHERE BACKUPSETID =(SELECT min(BackupSetID) FROM @TEMP WHERE FileExists=0 and Type='L') and Type='L'
DELETE FROM @TEMP WHERE BACKUPSETID <(SELECT MAX(BACKUPSETID) FROM @TEMP WHERE TYPE = 'I') AND TYPE = 'L'
/********* This code sets the logical name and filepath for both data and log files*********************/
DECLARE @DataFile_LogicalName NVARCHAR(200),@LogFile_LogicalName NVARCHAR(200),@FullBackupID1 BIGINT,@RestoreLocations VARCHAR(4000)
DECLARE @SQL1 NVARCHAR(MAX),@SQL2 NVARCHAR(MAX),@ParmDefinition NVARCHAR(200),@ParmDefinition1 NVARCHAR(200)
DECLARE @BackupLocation VARCHAR(500),@FullBackupId VARCHAR(20),@FULLRecoveryStatus VARCHAR(20),@FullPosition VARCHAR(4)
SELECT @FullBackupId = BackupSetId,@FullPosition=Position FROM @Temp WHERE TYPE = 'D' ORDER BY BackupSetId DESC

DECLARE @Temp2 TABLE (sno INT,pathdir VARCHAR(300))
SET @SQL2 ='select 1,''MOVE N''''''+Logical_Name+'''''' TO N''''''+
case when file_type = ''L'' then '''+@DbLogFilePath +'''else '''+@DbDataFilePath +''' END
+'''+REPLACE(@InstanceName,'\','_')+ '''+Reverse(Substring(REVERSE(Physical_name),1,charindex(''\'',
REVERSE(Physical_name),1)-1))+''''''''
from '+QUOTENAME(@InstanceName)+'.msdb.dbo.backupfile where backup_set_id ='+ @FullBackupID

INSERT INTO @Temp2
EXEC(@SQL2)

SELECT DISTINCT @RestoreLocations =
STUFF((SELECT ','+PathDir
FROM @Temp2 T1
WHERE T1.sno=T2.sno
FOR XML PATH('')),1,1,'') FROM @Temp2 T2
/***********************Code below does the FULL backup Restoration*************************************************************/
IF (@FullBackupID IS NOT NULL)
BEGIN TRY
-- this is to concatenate split backups into one string---
SELECT @BackupLocation =
STUFF((SELECT ',Disk ='''+pathDir+''''
FROM @Temp T1 where T1.BackupSetId=T2.BackupSetId and Type = 'D' for xml path('')),1,1,'') FRom @Temp T2 where Type = 'D'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'RESTORE DATABASE '+replace(@InstanceName,'\','_')+'_'+@DBNAME+ ' FROM '+@BackupLocation+
' WITH FILE ='+@FullPosition+','+@RestoreLocations+',NORECOVERY,NOUNLOAD'+case when @Replace=1 then ',REPLACE' end
EXECUTE sp_ExecuteSQL @SQL
END TRY

BEGIN CATCH
IF @@Error<>0
RAISERROR('The backup was not able to restore. Please check and run again.',20,1)WITH LOG
END CATCH
-------------------------------------Differential Restores----------------------------------------------------------------------------------------------------------
IF (Select max(backupsetID) from @Temp A where Type = 'I') is not null
BEGIN
/* This Code will restore the Latest Differential Backup taken after restored Full Backup for the database*/
DECLARE @DiffBackupLocation VARCHAR(MAX),@DiffBackupID BIGINT,@DiffSQL NVARCHAR(MAX),@DiffPosition VARCHAR(2)
-- Gets the Latest Differential Backup(taken after the latest Full Backup).
SELECT TOP 1 @DiffBackupId = A.BackupSetId,@DiffPosition =Position From @Temp A Where Type = 'I'

BEGIN TRY
-- this is to concatenate split backups into one string---
SELECT DISTINCT @DiffBackupLocation =
STUFF((SELECT ',Disk ='''+pathDir+''''
FROM @Temp T1 where T1.BackupSetId=T2.BackupSetId and Type = 'I' for xml path('')),1,1,'') FRom @Temp T2 where Type = 'I'

SET @DiffSQL = 'RESTORE DATABASE '+@RestoreDB+ ' FROM '+@DiffBackUpLocation+' WITH FILE ='+@DiffPosition+',NORECOVERY,NOUNLOAD'
EXECUTE Sp_ExecuteSQL @DiffSQL
END TRY

BEGIN CATCH
IF @@Error<>0
RAISERROR('The backup was not able to restored. Please check and run again.',20,1)WITH LOG
END CATCH
END
--------------------------------------Transaction Log Backups--------------------------------------------------------------
-- checks to see if there are any Tlog backups to be restored(taken after the latest Full and/or Differential Backups).
IF (Select Max(backupsetID) from @Temp A where Type = 'L') is not null
Begin
/*This code belows restores all the transaction logs taken after the lastest FULL and differential backups. */
Declare @LogBackupLocation varchar(200),@LogBackupID bigint,@LogSQL nvarchar(max),@LogRecoveryStatus varchar(20),@LogPosition varchar(4)
--Using Cursor to get the list of all the Tlogs to be restored(taken after the latest Full and/or Differential Backups).
Declare LogBackupID_Cursor CURSOR FOR
Select A.BackupSetId
from @Temp A where Type = 'L' order by BackupSetId asc

OPEN LogBackupID_Cursor
Fetch NEXT FROM LogBackupID_Cursor intO @LogBackupID

WHILE @@Fetch_status = 0
Begin

Select @LogPosition = Position From @Temp where BackupSetID=@LogBackupID
-- this is to concatenate split backups into one string---
SELECT DISTINCT @LogBackupLocation =
STUFF((SELECT ',Disk ='''+pathDir+''''
FROM @Temp T1 where T1.BackupSetId=T2.BackupSetId and Type = 'L' for xml path('')),1,1,'') FRom @Temp T2 where Type = 'L' and BackupSetID=@LogBackupID

BEGIN TRY
SET @LogSQL = 'RESTORE DATABASE '+@RestoreDB+ ' FROM '+@LogBackUpLocation+
' WITH FILE ='+@LogPosition+',NORECOVERY,NOUNLOAD'
EXECUTE Sp_Executesql @LogSQL
END TRY

BEGIN CATCH
IF @@Error<>0
RAISERROR('The backup was not able to restored. Please check and run again.',20,1)WITH LOG
END CATCH
Fetch Next from LogBackupId_Cursor into @LogBackupID
End
Close LogBackupID_Cursor
Deallocate LogBackupID_Cursor
End
/*********Apply Final Recovery on the Database******************/
IF @@ERROR=0 and (@FullBackupId is not null)
BEGIN
DECLARE @FinalSQL NVARCHAR(200)
SET @FinalSQL = 'RESTORE database '+@RestoreDB +' with recovery'
EXECUTE sp_executesql @FinalSQL
END

IF (Select min(BackupSetID) from @Temp where Type = 'D') is null and (@BackupFromDate is not null and @BackupToDate is not null)
BEGIN
RAISERROR('No full backup is available for the time range selected to restore.',20,1)WITH LOG
END

IF (Select min(BackupSetID) from @Temp where Type = 'D') is null and (@BackupFromDate is null and @BackupToDate is null)
BEGIN
RAISERROR('No full backup is available to restore.',20,1)WITH LOG
END
END

Clearing Wait Stats on the server.

Understanding wait stats and interpreting them correctly helps greatly in tuning the queries for better performance. Every now and then, we would want to clear the stats on the test server, so, we can check whether the new improvements have positive impact before rolling them into production. Sometimes,We would want to save this information into a table for comparison.

Obviously, one way to do this is by restarting the SQL Service.As these values are not stored physically,they will be cleared out. The problem here is,it is not always feasible to restart the server.

The other option,probably better one,is DBCC SQLPERF(‘sys.dm_os_Wait_Tasks’,CLEAR)
This command will reset the wait stats.
We can also clear latch stats using DBCC SQLPERF(‘sys.dm_os_latch_Tasks’,CLEAR)

What queries are currently running on my database server?

Whenever database server is not responding well, as a DBA, we need to find what’s going on the server.One of the common things, we would be interested in looking is the queries currently executing on the sql server. While checking this, capturing their execution plan is also a good idea.Below query returns the sql text and execution plan of queries currently executing on the server.
Query(Works on SQL2005 and above):
Select a.session_id,A.Status,A.Command,B.Text,c.query_plan,DB_Name(a.database_id) as DatabaseName,a.percent_complete,a.logical_reads,a.blocking_session_id,a.wait_type
From sys.dm_exec_requests a cross apply sys.dm_exec_sql_text(a.sql_handle) b
cross apply sys.dm_exec_query_plan(a.plan_handle) c
Where a.session_id<>@@SPID

Sample Output :
Capture

List of all the latest existing backups to be restored

When we need to restore a database, the very first thing we need to find is list of backups that needs to be restored.Of course, you might need to take tail log backup and restore at the very end based on the situation you are in. The query below will list all the EXISTING backups of a particular database to be restored

I have a similar post here,however, the difference between these two scripts is that the below query will list only the backups that needs to be restored where as the previous one will list all the backups after the latest full backup.

The query first checks for latest full backup and latest differential backup, if any, and all the log backups after the latest differential or full backup.I am eliminating the Copy_Only Backups, so the restore sequence chain is not interrupted.

Note:The query will only work on SQL 2005 and above versions.

declare @DatabaseName varchar(200)
set @DatabaseName = 'DatabaseName';

select backup_set_id,database_name,backup_size,type as BackupType,A.name as BackupName,Backup_Start_Date,Backup_Finish_date,Physical_device_name from msdb.dbo.backupset A INNER JOIN msdb.dbo.backupmediafamily B on A.media_set_id=B.media_set_id where database_name = @DatabaseName and backup_set_id=(Select max(backup_set_id) from msdb.dbo.backupset where database_name= @DatabaseName and type='D' and is_copy_only=0) and is_copy_only=0
union
select backup_set_id,database_name,backup_size,type as BackupType,A.name as BackupName,Backup_Start_Date,Backup_Finish_date,Physical_device_name
from msdb.dbo.backupset A INNER JOIN msdb.dbo.backupmediafamily B on A.media_set_id=B.media_set_id where database_name = @DatabaseName and
A.backup_set_id =
(Select max(backup_set_id) from msdb.dbo.backupset where database_name= @DatabaseName and type in ('I','D') and is_copy_only=0) and type = 'I'
union
select backup_set_id,database_name,backup_size,type as BackupType,A.name as BackupName,Backup_Start_Date,Backup_Finish_date,Physical_device_name from msdb.dbo.backupset A INNER JOIN msdb.dbo.backupmediafamily B on A.media_set_id=B.media_set_id where database_name = @DatabaseName and backup_set_id>
(Select max(backup_set_id) from msdb.dbo.backupset where database_name= @DatabaseName and type in ('I','D') and is_copy_only=0) and type = 'L' and is_copy_only=0

Report file sizes in proper units

Adding to my previous post about reporting time in proper units, I made a similar function to report file size in appropriate units. Again, the issue here is same as in my previous post (i.e. some backups are relatively small and some are really big).So,I was reporting all file sizes in bytes and it is kind of hard to understand the big numbers(It is easy understand 52428800 as 50.00 MB). So, to fix this, I made a scalar function which takes input in bytes and returns output in its nearest lower Bytes/KB/MB/GB/TB units.

Below is the function:

USE [msdb]
GO
CREATE FUNCTION [dbo].[fnBackupSize](@backupsize decimal(20,3))
RETURNS varchar(20)
AS
Begin
DECLARE @size varchar(20)
IF (@backupsize<1024)
Begin
Select @Size = cast(@backupsize as varchar(10)) +' Byte(s)'
End
IF (@backupsize>=1024 and @backupsize<=1048576)
Begin
Select @Size = cast(cast(@backupsize/1024 as decimal(20,3)) as varchar(10)) +' KByte(s)'
End
IF (@backupsize>=1048576 and @backupsize<=1073741824)
Begin
Select @Size = cast(cast(@backupsize/1048576 as decimal(20,3)) as varchar(10)) +' MByte(s)'
End
IF (@backupsize>=1073741824 and @backupsize<=1099511627776)
Begin
Select @Size =cast(cast(@backupsize/1073741824 as decimal(20,3)) as varchar(10)) +' GByte(s)'
End
IF (@backupsize>=1099511627776)
Begin
Select @Size =cast(cast(@backupsize/1099511627776 as decimal(20,3)) as varchar(10)) +' TByte(s)'
End

Return @size

End
Output: Select msdb.dbo.fnBackupSize(53687091200) as [Size]
Capture1
Select msdb.dbo.fnBackupSize(53680) as [Size]
Capture