Use the following T-SQL script to get the transaction log size and log space used of your database.
SELECT DB_NAME(database_id) AS DatabaseName ,
CAST(( total_log_size_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))
AS TotalLogSizeMB ,
CAST(( used_log_space_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))
AS LogSpaceUsedMB ,
CAST(used_log_space_in_percent AS DECIMAL(10, 1)) AS LogSpaceUsedPercent
FROM sys.dm_db_log_space_usage;
Below is a script that you can use to check the constraint status of all your tables in the database.
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
Output:
When the database that I’m handling was experiencing excessive blockings, below is the script I use too see what are being blocked and which stored procedure is the blocker. Blocking is when one connection needs access to a piece of data and has to wait for another connection’s lock to clear.
SELECT blocking.session_id AS blocking_session_id, blocked.session_id AS blocked_session_id , waitstats.wait_type AS blocking_resource , waitstats.wait_duration_ms , waitstats.resource_description , DB_NAME(tl.resource_database_id) AS DatabaseName, blocked_cache.text AS blocked_text , blocking_cache.text AS blocking_text FROM sys.dm_exec_connections AS blocking INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_cache CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_cache INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id INNER JOIN sys.dm_tran_locks tl ON tl.lock_owner_address = waitstats.resource_address WHERE waitstats.wait_duration_ms >= 2000
The above query is okay but we were able to enhanced the script by adding columns to see what objects that are being blocked. Below is the enhanced version of the script.
;WITH T1 AS ( SELECT blocking.session_id AS blocking_session_id , blocked.session_id AS blocked_session_id , waitstats.wait_type AS blocking_resource , waitstats.wait_duration_ms , waitstats.resource_description , DB_NAME(tl.resource_database_id) AS DatabaseName, blocked_cache.text AS blocked_text , blocking_cache.text AS blocking_text FROM sys.dm_exec_connections AS blocking INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_cache CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_cache INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id INNER JOIN sys.dm_tran_locks tl ON tl.lock_owner_address = waitstats.resource_address WHERE waitstats.wait_duration_ms >= 2000 ), T2A AS ( SELECT blocking_session_id, resource_description, CHARINDEX('hobtid', resource_description) AS StartPos, SUBSTRING(resource_description, CHARINDEX('hobtid', resource_description), LEN(resource_description)) AS StartText FROM T1 ), T2B AS ( SELECT blocking_session_id, resource_description, StartPos, StartText, SUBSTRING(StartText, 0, CHARINDEX(' ', StartText)) AS hotbid_text FROM T2A ), T2C AS ( SELECT blocking_session_id, resource_description, CAST(SUBSTRING(hotbid_text, CHARINDEX('=', hotbid_text)+1, LEN(hotbid_text)) AS BIGINT) AS hobt_id FROM T2B ), T2D AS ( SELECT T2C.blocking_session_id, T2C.resource_description, OBJECT_SCHEMA_NAME(object_id) AS schema_name, object_name(object_id) as object_name, object_id, partition_id, index_id, partition_number, p.hobt_id, rows FROM sys.partitions p INNER JOIN T2C ON p.hobt_id = T2C.hobt_id ) SELECT GETDATE() AS DateTimeCaptured, T1.blocking_session_id, T1.blocked_session_id, T1.blocking_resource, T1.wait_duration_ms, T1.resource_description, T1.DatabaseName, T1.blocking_text, T1.blocked_text, T2D.schema_name + '.' + T2D.object_name AS blocked_object_name, T2D.object_id AS blocked_object_id, T2D.index_id as blocked_index_id, i.name AS blocked_index_name, i.type_desc AS blocked_index_type FROM T1 INNER JOIN T2D ON T1.blocking_session_id = T2D.blocking_session_id AND T1.resource_description = T2D.resource_description INNER JOIN sys.indexes i ON i.object_id = T2D.object_id AND i.index_id = T2D.index_id
Cheers!
SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame AS LoginName,
nt_domain AS NT_Domain,
nt_username AS NT_UserName,
hostname AS HostName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid,
hostname,
loginame,
nt_domain,
nt_username
ORDER BY NumberOfConnections DESC;
--DMV to view missing indexes
SELECT
*
FROM
sys.dm_db_missing_index_group_stats AS igs
JOIN
sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle
JOIN
sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY [Indexsize(KB)] desc
--Stored procedure to display current locks/process
exec sp_lock
--Stored procedure to display current activity/process
exec sp_who2
--DMV to view locking information
SELECT * FROM sys.dm_tran_locks
GO
--DMV to view blocked transactions
SELECT * FROM sys.dm_exec_requests WHERE status = 'suspended'
GO
--Trace flag to log deadlocks
DBCC TRACEON (1222, -1)
GO
select name, definition, type_desc FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
where type_desc like '%function%'
ORDER BY Name
--OR
SELECT *
FROM sys.objects
WHERE RIGHT(type_desc, 8) = 'FUNCTION'
