Unexpected lock in SQL Server

Unexpected lock in SQL Server

                                Yesterday I have an issue with unexpected lock in SQL Server. I have open MSDN forum post regarding this.  

My SQL Server Info,

 

First create table and insert some data as follows,

--Script 01

USE [DATABASEUSERGROUP]
GO
DROP TABLE [dbo].[tblChild];
GO
CREATE TABLE [dbo].[tblChild]([id] [int] IDENTITY(1,1) NOT NULL,[Parentid] [int] NULL,[category] [int] NULL,[Childname] [varchar](100) NULL,[cdate] [datetime] NULL,[statusof] [bit] NULL);
GO
INSERT INTO [dbo].[tblChild]([Parentid],[category],[Childname],[cdate],[statusof])
SELECT id,xtype,name,GETDATE(),0 FROM sys.syscolumns;
GO 2000
GO
CREATE CLUSTERED INDEX [CIX_tblChild_Parentid] ON [dbo].[tblChild]([Parentid] ASC);
--CREATE CLUSTERED INDEX [CIX_tblChild_category] ON [dbo].[tblChild]([category] ASC);
--CREATE CLUSTERED INDEX [CIX_tblChild_id] ON [dbo].[tblChild]([id] ASC);
GO

Next execute following Update command in one SPID(Ex: SPID=51),

--Script 02

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT session_id,Transaction_Isolation_Level FROM sys.dm_exec_sessions WHERE session_id=51;

BEGIN TRAN
SELECT  [id]
      ,[Parentid]
      ,[category]
      ,[Childname]
      ,[cdate]
      ,[statusof]
  FROM [DATABASEUSERGROUP].[dbo].[tblChild] WHERE id=1;

UPDATE [DATABASEUSERGROUP].[dbo].[tblChild] SET [Childname]='test' WHERE id=1;
--ROLLBACK TRAN
--COMMIT TRAN

Note : do not commit or rollback above Update and open another window and hit update again as follows,(Ex: SPID=54),

--Script 03

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT session_id,Transaction_Isolation_Level FROM sys.dm_exec_sessions WHERE session_id=54

BEGIN TRAN
SELECT  [id]
      ,[Parentid]
      ,[category]
      ,[Childname]
      ,[cdate]
      ,[statusof]
  FROM [DATABASEUSERGROUP].[dbo].[tblChild] WHERE id=2;

UPDATE [DATABASEUSERGROUP].[dbo].[tblChild] SET [Childname]='test1' WHERE id=2;

--ROLLBACK TRAN
--COMMIT TRAN

Hope now you will notice your second window is lock by first update.But after change Clustered Index to ID lock behavior totally different.  (Like First SQL script 01) still wondering why above lock behavior...

BACKUP RESTORE FILELISTONLY

BACKUP RESTORE FILELISTONLY

                                                   Today my friend ask me about backup and restore related question. While talking with him about the issue, I have decided to share this in my web log.
The Question was, 

“Is there have a method to RESTORE database with using T-SQL?, but you didn’t have any idea about how many data files or file groups available in given backup file.”

For my Answer was very simple,

Just open SSMS and then select database backup and change new database for your needed name and next select “Script -> New Query Editor Window” then it will generate T-SQL code for you given database backup. 

But again he says, nope I have to publish T-SQL script and it should be work any database backup. Well… well now I have understand his need and given him following T-SQL script and hope it will help others as well. 

USE master
GO
SET NOCOUNT ON

DECLARE @BackupPath varchar(1000)='E:\Backups\Database_user_group_19JUNE2015.bak';
DECLARE @NewDBLocation varchar(1000)='E:\MSSQL\2014\DATABASE_USER_GROUP\';
DECLARE @NewDBName varchar(1000)='[DATABASE_USER_GROUP]';

-------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @tblDataFiles TABLE (LogicalName varchar(128),[PhysicalName] varchar(2000), [Type] varchar(4),
                        [FileGroupName] varchar(128), [Size] bigint, [MaxSize] bigint,
                        [FileId] smallint, [CreateLSN] varchar(128), [DropLSN] varchar(128), [UniqueId] varchar(128),
                        [ReadOnlyLSN] varchar(128), [ReadWriteLSN] varchar(128), [BackupSizeInBytes] varchar(128),
                        [SourceBlockSize] varchar(128), [FileGroupId] smallint, [LogGroupGUID] varchar(128),
                        [DifferentialBaseLSN] varchar(128), [DifferentialBaseGUID] varchar(128),
                        [IsReadOnly] bit, [IsPresent] bit, [TDEThumbprint] varchar(128));


INSERT INTO @tblDataFiles
EXEC('RESTORE FILELISTONLY FROM DISK=''' +@BackupPath+ '''');

--SELECT * FROM @tblDataFiles;

DECLARE @maxFileId smallint=0;
SELECT @maxFileId=MAX([FileId]) FROM @tblDataFiles;

DECLARE @ssql    nvarchar(max)='';
SELECT @ssql=@ssql+CASE WHEN [FileId]=1 THEN 'RESTORE DATABASE '+@NewDBName+'  FROM  DISK =N'''+@BackupPath+''' WITH  FILE = 1,  ' ELSE '' END +CHAR(13)+CHAR(10)+
'MOVE N'''+LogicalName+''' TO N'''+@NewDBLocation+LogicalName+CASE [FileId]    WHEN 1 THEN '_'+CAST([FileId] as varchar(4))+'.mdf'
                WHEN 2 THEN '_'+CAST([FileId] as varchar(4))+'.ldf'
                ELSE '_'+CAST([FileId] as varchar(4))+'.ndf' END+''','+CASE WHEN [FileId]=@maxFileId THEN '  NOUNLOAD,  STATS = 5;' ELSE '' END
FROM @tblDataFiles ORDER BY [FileId];         

PRINT @ssql;
EXEC (@ssql);
GO

Cheers...
and Most welcome your comments and ideas...