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
***********************************************************************************************
.jpg)
