Useful queries for DBA

Finding the blocking statement in Sql Server

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

***********************************************************************************************

Finding the user who running the query in the server

SELECT
s.session_id AS SessionID,
 s.login_time AS LoginTime,
 s.[host_name] AS HostName,
 s.[program_name] AS ProgramName,
 s.login_name AS LoginName,
 s.[status] AS SessionStatus,
 st.text AS SQLText,
 (s.cpu_time / 1000) AS CPUTimeInSec,
 (s.memory_usage * 8) AS MemoryUsageKB,
 (CAST(s.total_scheduled_time AS FLOAT) / 1000) AS TotalScheduledTimeInSec,
 (CAST(s.total_elapsed_time AS FLOAT) / 1000) AS ElapsedTimeInSec,
 s.reads AS ReadsThisSession,
 s.writes AS WritesThisSession,
 s.logical_reads AS LogicalReads,
 CASE s.transaction_isolation_level
 WHEN 0 THEN 'Unspecified'
 WHEN 1 THEN 'ReadUncommitted'
 WHEN 2 THEN 'ReadCommitted'
 WHEN 3 THEN 'Repeatable'
 WHEN 4 THEN 'Serializable'
 WHEN 5 THEN 'Snapshot'
 END AS TransactionIsolationLevel,
 s.row_count AS RowsReturnedSoFar,
 c.net_transport AS ConnectionProtocol,
 c.num_reads AS PacketReadsThisConnection,
 c.num_writes AS PacketWritesThisConnection,
 c.client_net_address AS RemoteHostIP,
 c.local_net_address AS LocalConnectionIP
 FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections c
 ON s.session_id = c.session_id
 CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS st
 WHERE s.is_user_process = 1 and [status]='running'
 ORDER BY ElapsedTimeInSec,LoginTime DESC

***********************************************************************************************

Log Removing Query

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Database_log], 1);
GO
-- Reset the database
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO

sp_helpdb 'DataabaseName'

***********************************************************************************************

LAST BACK UP Timings

SELECT db.name,

case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100),

MAX(b.backup_finish_date)) end AS last_backup_finish_date

FROM sys.databases db LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'  WHERE db.database_id NOT IN (2)  GROUP BY db.name ORDER BY 2 DESC

***********************************************************************************************

--PATH WHERE THE BACKUP HAVE BEEN SAVED

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily

***********************************************************************************************

--LIST check the current users, process and session information

sp_who

sp_who2

***********************************************************************************************

--Unlocking a login

--To unlock a SQL Server login, execute the following statement, replacing **** with the desired account password.

ALTER LOGIN [Mary5] WITH PASSWORD = ‘****’ UNLOCK ;

GO

--To unlock a login without changing the password, turn the check policy off and then on again.

ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;

ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;

GO

 --Enabling a disabled login

 ALTER LOGIN Mary5 ENABLE;

--Changing the password of a login

ALTER LOGIN Mary5 WITH PASSWORD = ”;

--Changing the name of a login

ALTER LOGIN Mary5 WITH NAME = John2;

***********************************************************************************************

--Query which has taken more time for executing

     

      SELECT TOP 10 obj.name, max_logical_reads, max_elapsed_time

FROM sys.dm_exec_query_stats a

CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd

INNER JOIN sys.sysobjects obj on hnd.objectid = obj.id

ORDER BY max_logical_reads DESC

***********************************************************************************************

--Restore SQL table backup using BCP (BULK COPY PROGRAM)

BULK INSERT AdventureWorks.Person.Contacts_Restore

    FROM 'C:\MSSQL\Backup\Contact.Dat'

    WITH (DATAFILETYPE='native');

***********************************************************************************************

/*find no. of stored procedures*/
Select count(*) from sysobjects where xtype = 'P'

***********************************************************************************************
--Database objects were they were changed last time

select name, crdate, refdate
from sysobjects
order by crdate desc
go

***********************************************************************************************

--List expensive queries

DECLARE @MinExecutions int;
SET @MinExecutions = 5

SELECT EQS.total_worker_time AS TotalWorkerTime
      ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO
      ,EQS.execution_count As ExeCnt
      ,EQS.last_execution_time AS LastUsage
      ,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS
      ,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count
       AS AvgLogicalIO
      ,DB.name AS DatabaseName
      ,SUBSTRING(EST.text
                ,1 + EQS.statement_start_offset / 2
                ,(CASE WHEN EQS.statement_end_offset = -1
                       THEN LEN(convert(nvarchar(max), EST.text)) * 2
                       ELSE EQS.statement_end_offset END
                 - EQS.statement_start_offset) / 2
                ) AS SqlStatement
      -- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!!
      --,EQP.[query_plan] AS [QueryPlan]
FROM sys.dm_exec_query_stats AS EQS
     CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST
     CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP
     LEFT JOIN sys.databases AS DB
         ON EST.dbid = DB.database_id    
WHERE EQS.execution_count > @MinExecutions
      AND EQS.last_execution_time > DATEDIFF(MONTH, -1, GETDATE())
ORDER BY AvgLogicalIo DESC
        ,AvgCPUTimeMiS DESC

***********************************************************************************************