Category: SQL Server

  • SQL Server Agent Job Owner

    It is a best practice to keep the SQL Agent Job owner to SA or Service Account. Please don’t make the job owner to any user’s windows ID. If the employee left the company his ID will be terminated and access will be revoked.
    You can find out the jobs owner by executing the below query. 

    SELECT 
j.name,
l.[name] as [JobOwner]
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

    You can change the job owner by executing the below query.

    EXEC MSDB.dbo.sp_update_job 
@job_name = 'Job Name', 
@owner_login_name = 'sa'

  • How to Check whether SQL Instance Clustered or Standalone

    You can check the SQL Server is cluster or standalone using one of the below technique.

    Technique 1: Go to server properties from SSMS and check the Is Clustered property.

     

     

    Technique 2: Execute the below query, it will retun Boolean value. (1 = True and 0=False)

    SELECT
    CONVERT(char(20),
    SERVERPROPERTY(‘IsClustered’))

  • Steps to Save/Store file into Database

    You can use the below scripts to store/save the file into SQL Server database table. Please note it is not recommended to store file into database. You can store the file on file system and path in the database.

    use sqlDBPool
--documents table will store files into varbinary field
--drop table documents
create table documents
(
	documentID int identity(1,1),
	doctype char(5),
	document varbinary(max)
)


--script to store/save document into table
insert into documents 
 Select 'xls', (SELECT * FROM OPENROWSET(BULK N'C:\JSpace\book1.xls', SINGLE_BLOB) AS document) document
go

select * from documents

  • @@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

  • How to Capture DeadLock Graph Using SQL Profiler

    You can follow below steps to capture the deadlock graph using profiler. First we will setup the profiler and deadlock events and later on we will run the deadlock scenario.

    Step 1: Open the SQL Profiler. You can start the SQL Profiler from the SSMS.

    Step 2: Configure the trace, in General tab give the name to trace file.

    Step 3: Select the below events from the Event Selection tab and Run the trace.

    Deadlock Graph

    Deadlock Graph event captures deadlock in both XML format and graphically, a graph that shows us exactly the cause of the deadlock.

    Lock:Deadlock

    This event is fired whenever a deadlock occurs.

    Lock:Deadlock Chain

    This event is fired once for every process involved in a deadlock.

    Step 4: Run the deadlock scenario queries as per http://sqldbpool.com/2012/02/12/steps-to-create-the-deadlock-scenario/ article.

    Step 5: You can see the below graph once the deadlock occurred.

    2026 update: Profiler is deprecated — use Extended Events

    SQL Server Profiler is on Microsoft’s deprecated list: avoid it in new work and use Extended Events instead. For deadlocks you have two better options. The system_health session already captures deadlock graphs for free on every instance. Or create a dedicated session:

    CREATE EVENT SESSION [DeadlockCapture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
    (SET filename = N'DeadlockCapture.xel');
GO
ALTER EVENT SESSION [DeadlockCapture] ON SERVER STATE = START;

    The xml_deadlock_report event gives you the same graph Profiler did, at a fraction of the overhead — and it runs 24/7 without anyone remembering to start a trace.