Category: Database

  • @@MICROSOFTVERSION Function

    Problem: Take an example you are writing a script which is going to be deployed on the all the versions of SQL Servers and you want to check the SQL Server version details using T-SQL code. Below solution will guide you how to check the SQL Server version in stored procedure/t-sql batch.

    Solution: You can use the @@MICROSOFTVERSION to get the SQL Server version information. If the output of the below script is 9 than its SQL 2005, if 10 than SQL Server 2008 and if 11 than SQL Server 2011

    select @@VERSION

--method - 1
select @@MICROSOFTVERSION as MSVersion, CAST (@@MICROSOFTVERSION as BINARY(5)) as MsVersionInBinary
-- Remove the first non-zero character after 0x0 from binary output here it is A and divide the @@MicrosoftVersion outout 
select substring(cast(@@MICROSOFTVERSION/0x000000640 as varchar(10)),1,2) as MsSQLVersion

--Method 2
select @@MICROSOFTVERSION / POWER(2,24) as usingPowerFunctionMSSQLVersion

  • Steps to create the deadlock scenario

    A deadlock occurs when two or more processes permanently block each other by each process having a lock on a resource which the other process are trying to lock.

    Please execute the below queries as per the mentioned comments to produce a deadlock.

    --turning on the traceflag to record deadlock info into error log
dbcc traceon(1204,-1)
dbcc tracestatus(1204)

--creating test database
create database sqlDBPool
--Connecting to SQLDBPool database
use sqldbpool
--table creation
create table tb1 (col1 int)
create table tb2 (col1 int)
--inserting dummy records
insert into tb1 values(1),(2),(3)
insert into tb2 values(1),(2),(3)

--Open first connection to update table explicit transaction
begin transaction
  update tb1 set col1 = 5
  
--Open second connection to update table explicit transaction
use sqlDBPool
begin transaction
  update tb2 set col1 = 6
  update tb1 set col1 = 6

--Open first connection to update table explicit transaction
  update tb2 set col1 = 5

    You can see the one of the transaction will fail with the below error message.

    Msg 1205, Level 13, State 45, Line 3
    Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    As we have turned on the deadlock trace flag, you can see the below information in the SQL Server error log.

    Starting up database 'sqlDBPool'.
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1  
RID: 9:1:153:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 1:
   Owner:0x05684480 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x065F82A8
   SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1
   Input Buf: Language Event: update tb2 set col1 = 5
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)
NULL
Node:2  
RID: 9:1:155:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 2:
   Owner:0x067679A0 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x05A8CC38
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 3
   Input Buf: Language Event: begin transaction    update tb2 set col1 = 6    update tb1 set col1 = 6
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x065F8280 Mode: U SPID:52 BatchID:0 ECID:0 TaskProxy:(0x0941A354) Value:0x6a943a0 Cost:(0/432)
NULL
Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)

  • Steps to Attach a SQL Server database without transaction log file

    Problem: There could be situation where you missed the database transaction log file(.LDF) and you have only data file (.MDF). You can attach the database using below solution.

    Solution: In the below script I have created the database,dropped its log file and created the database with the .mdf file.

    --created database with .mdf and .ldf file
CREATE DATABASE [singleFileDemo] ON  PRIMARY 
( NAME = N'singleFileDemo', FILENAME = N'L:\singleFileDemo.mdf' , SIZE = 2048KB , FILEGROWTH = 10240KB )
 LOG ON 
( NAME = N'singleFileDemo_log', FILENAME = N'F:\singleFileDemo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 5120KB )
GO

--inserting data into database
use singleFileDemo
create table tb1 (name varchar(10))

--inserting records
insert into tb1 values('Jugal')
go 10;

--deleting the log file
--detaching the database file
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'singleFileDemo'
GO

-- now next step is delete the file manually or you can do it from command prompt
EXEC xp_cmdshell 'del F:\singleFileDemo_log.ldf'

-- script to attach the database 
USE [master]
GO
CREATE DATABASE [singleFileDemo] ON 
( FILENAME = N'L:\singleFileDemo.mdf' )
FOR ATTACH
GO

    When you will execute the CREATE DATABASE FOR Attach script you will get the below warning message.

    File activation failure. The physical file name "F:\singleFileDemo_log.ldf" may be incorrect.
    New log file 'F:\singleFileDemo_log.LDF' was created.

    Once the database is ready execute the DBCC CHECKDB for any error.

  • Script to Enable/Disable Database for Replication

    You can enable the database for replication using below script.

    use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'true'
go

    If you have restore the database on test environment and you are getting the error that “Database is part of Replication”, you can clear/disable it by executing below query.

    use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'false'
go

  • Estimating the size of the Database backup file

    Problem: Often we would like to check how much size backup file will take on the disk? You can follow below simple steps to estimate the backup file size?

    Solution:
    Execute the SP_SPACEUSED procedure to get the database size statistics.
    USE [sqldbpool]
    GO
    EXEC sp_spaceused @updateusage = ‘true’

    Now you have to subtract the ununsed pages from reserved pages and divided by 1024 to get the output in MB.

    In our scenario we have 2592 (KB) – 96 (KB) = 2496/1024 = 2.4374(MB) will be the backup size.