SQL Admin

The following are a selection scripts that have ‘mostly’ got me out of trouble when I have had SQL Server DBA roles. Be warned, occasionally they have got me into a whole heap more trouble!! Use at your own risk …

PS. I got these over time from many sources, if you were the original author then let me know and I will happily give you credit.

Free Space

SELECT DISTINCT CAST(dovs.logical_volume_name AS VARCHAR(20)) AS LogicalName
,CAST(dovs.volume_mount_point AS VARCHAR(5)) AS Drive
,CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC

Shrinking TempDb on RDS

exec msdb.dbo.rds_shrink_tempdbfile @temp_filename = N’XYZ’, @target_size = 100;

Validating Views after a schema change

DECLARE @Name NVARCHAR( MAX ),
@SQL NVARCHAR( MAX );

DECLARE @t_BindingErrors TABLE
(
ViewName NVARCHAR( MAX ),
ErrorMessage NVARCHAR( MAX )
);

DECLARE c CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT ‘[‘ + ss.name + ‘].[‘ + so.NAME + ‘]’
FROM sys.schemas ss
INNER JOIN sys.objects so
ON ss.schema_id = so.schema_id
WHERE so.type = ‘V’;
OPEN c;
FETCH NEXT FROM c
INTO @Name;

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @SQL = ‘
SELECT TOP ( 0 ) *
FROM ‘ + @Name + ‘;’;
BEGIN TRY
PRINT @SQL;
EXECUTE dbo.sp_executesql @statement = @SQL;
END TRY BEGIN CATCH
INSERT INTO @t_BindingErrors
VALUES ( @Name, ERROR_MESSAGE() );
END CATCH;

FETCH NEXT FROM c
INTO @Name;
END;

SELECT * FROM @t_BindingErrors

Refresh Views when they’re impacted by a schema change

SELECT ‘EXEC sp_RefreshView ”’ + TABLE_CATALOG +’.’ + TABLE_SCHEMA+’.’ + TABLE_NAME + ””
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE != ‘BASE TABLE’

Finding Missing Indexes

SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
‘CREATE INDEX [IX_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)
+ ‘_’ + LEFT (PARSENAME(mid.statement, 1), 32) + ‘]’
+ ‘ ON ‘ + mid.statement
+ ‘ (‘ + ISNULL (mid.equality_columns,”)
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END
+ ISNULL (mid.inequality_columns, ”)
+ ‘)’
+ ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,
migs.*
, DB_NAME(mid.database_id)
, mid.[object_id]
, statement
FROM
sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE 1=1
AND DB_NAME(mid.database_id) = ‘XYZ’

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Unused Indexes

SELECT
OBJECTS.NAME AS TABLE_NAME,
INDEXES.NAME AS INDEX_NAME,
DM_DB_INDEX_USAGE_STATS.USER_SEEKS,
DM_DB_INDEX_USAGE_STATS.USER_SCANS
FROM
SYS.DM_DB_INDEX_USAGE_STATS
INNER JOIN SYS.OBJECTS ON DM_DB_INDEX_USAGE_STATS.OBJECT_ID = OBJECTS.OBJECT_ID
INNER JOIN SYS.INDEXES ON INDEXES.INDEX_ID = DM_DB_INDEX_USAGE_STATS.INDEX_ID AND DM_DB_INDEX_USAGE_STATS.OBJECT_ID = INDEXES.OBJECT_ID
WHERE
INDEXES.IS_PRIMARY_KEY = 0
AND INDEXES.IS_UNIQUE = 0
AND DM_DB_INDEX_USAGE_STATS.USER_LOOKUPS = 0
AND DM_DB_INDEX_USAGE_STATS.USER_SEEKS = 0
AND DM_DB_INDEX_USAGE_STATS.USER_SCANS = 0
ORDER BY
DM_DB_INDEX_USAGE_STATS.USER_UPDATES DESC

Find which columns are indexed

SELECT ‘[‘ + s.NAME + ‘].[‘ + o.NAME + ‘]’ AS ‘table_name’
,+ i.NAME AS ‘index_name’
,LOWER(i.type_desc) + CASE
WHEN i.is_unique = 1
THEN ‘, unique’
ELSE ”
END + CASE
WHEN i.is_primary_key = 1
THEN ‘, primary key’
ELSE ”
END AS ‘index_description’
,STUFF((
SELECT ‘, [‘ + sc.NAME + ‘]’ AS “text()”
FROM syscolumns AS sc
INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id
AND ic.column_id = sc.colid
WHERE sc.id = so.object_id
AND ic.index_id = i1.indid
AND ic.is_included_column = 0
ORDER BY key_ordinal
FOR XML PATH(”)
), 1, 2, ”) AS ‘indexed_columns’
,STUFF((
SELECT ‘, [‘ + sc.NAME + ‘]’ AS “text()”
FROM syscolumns AS sc
INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id
AND ic.column_id = sc.colid
WHERE sc.id = so.object_id
AND ic.index_id = i1.indid
AND ic.is_included_column = 1
FOR XML PATH(”)
), 1, 2, ”) AS ‘included_columns’
FROM sysindexes AS i1
INNER JOIN sys.indexes AS i ON i.object_id = i1.id
AND i.index_id = i1.indid
INNER JOIN sysobjects AS o ON o.id = i1.id
INNER JOIN sys.objects AS so ON so.object_id = o.id
AND is_ms_shipped = 0
INNER JOIN sys.schemas AS s ON s.schema_id = so.schema_id
WHERE so.type = ‘U’
AND i1.indid < 255 AND i1.STATUS & 64 = 0 –index with duplicates AND i1.STATUS & 8388608 = 0 –auto created index AND i1.STATUS & 16777216 = 0 –stats no recompute AND i.type_desc <> ‘heap’
AND so.NAME <> ‘sysdiagrams’
ORDER BY table_name
,index_name;

Courtesy of https://stackoverflow.com/users/4606920/aeyjey

Read The Definition of a View or Sproc

sp_helpText ‘dbp.sp_MySproc’

Dead Locks and Blocks

SELECT session_id
,blocking_session_id
,wait_time
,wait_type
,last_wait_type
,wait_resource
,transaction_isolation_level
,lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0

sp_who2

SELECT session_id, start_time, status,
total_elapsed_time,
CASE transaction_isolation_level
WHEN 1 THEN ‘ReadUncomitted’
WHEN 2 THEN ‘ReadCommitted’
WHEN 3 THEN ‘Repeatable’
WHEN 4 THEN ‘Serializable’
WHEN 5 THEN ‘Snapshot’
ELSE ‘Unspecified’ END AS transaction_isolation_level,
sh.text, ph.query_plan, *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sh
CROSS APPLY sys.dm_exec_query_plan(plan_handle) ph

SELECT * from sys.dm_tran_locks
WHERE request_status != ‘GRANT’

Index Fragmentation

SELECT dbschemas.[name] as ‘Schema’,
dbtables.[name] as ‘Table’,
dbindexes.[name] as ‘Index’,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

List Backups

SELECT
CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN ‘D’ THEN ‘Database’
WHEN ‘L’ THEN ‘Log’
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= DATEADD(HH,-1,GETDATE()))
AND database_name IN(‘edw_100_staging’,’edw_175_helper’,’edw_300_consumption’,’edw_900_omd’)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date DESC

Dependencies

The first one is cheap and cheerful

SELECT referenced_entity_name AS table_name, referenced_minor_name AS column_name, is_selected, is_updated, is_select_all
FROM sys.dm_sql_referenced_entities( ‘Name’,’OBJECT’)

If you want some more bells and whistles

Procedure Definition

Extract the definition of a procedure

SELECT definition FROM sys.sql_modules WHERE object_id = object_id(‘XYZ’);

Age of Statistics

SELECT o.name, i.name AS [Index Name],
STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
WHERE o.[type] = ‘U’
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

Object Definition

Extract object definition for reporting

select name, type_desc, object_definition(object_id)
from sys.objects

Alternatively you can use a cursor and a temp table

DECLARE
@prcdr_nm VARCHAR(MAX)
,@Exec as VARCHAR(MAX)
DECLARE prcdr CURSOR
FOR SELECT NAME
FROM sys.all_objects
WHERE type = ‘P’
IF OBject_ID(‘tempdb..#Vals’) IS NOT NULL
DROP TABLE #Vals
CREATE TABLE #Vals (prcdr_nm VARCHAR(255),Line VARCHAR(MAX))
OPEN prcdr;
FETCH NEXT FROM prcdr INTO
@prcdr_nm;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Exec = ”’sp_helptext ‘ + @prcdr_nm + ””
SET @Exec = ‘INSERT INTO #Vals(Line) EXEC (”sp_helptext ‘ + @prcdr_nm + ”’)’
EXEC (@Exec)
UPDATE #Vals SET prcdr_nm = @prcdr_nm WHERE prcdr_nm IS NULL
FETCH NEXT FROM prcdr INTO
@prcdr_nm
END;
CLOSE prcdr;
DEALLOCATE prcdr;