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

Read The Definition of a View or Sproc

sp_helpText ‘dbp.sp_MySproc’

Dead Locks and Blocks

sp_who2

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

;with ObjectHierarchy ( Base_Object_Id , Base_Cchema_Id , Base_Object_Name , Base_Object_Type, object_id , Schema_Id , Name , Type_Desc , Level , Obj_Path)
as
( select so.object_id as Base_Object_Id
, so.schema_id as Base_Cchema_Id
, so.name as Base_Object_Name
, so.type_desc as Base_Object_Type
, so.object_id as object_id
, so.schema_id as Schema_Id
, so.name
, so.type_desc
, 0 as Level
, convert ( nvarchar ( 1000 ) , N’/’ + so.name ) as Obj_Path
from sys.objects so
left join sys.sql_expression_dependencies ed on ed.referenced_id = so.object_id
left join sys.objects rso on rso.object_id = ed.referencing_id
where rso.type is null
and so.type in ( ‘P’, ‘V’, ‘IF’, ‘FN’, ‘TF’ )
union all
select cp.Base_Object_Id as Base_Object_Id
, cp.Base_Cchema_Id
, cp.Base_Object_Name
, cp.Base_Object_Type
, so.object_id as object_id
, so.schema_id as ID_Schema
, so.name
, so.type_desc
, Level + 1 as Level
, convert ( nvarchar ( 1000 ) , cp.Obj_Path + N’/’ + so.name ) as Obj_Path
from sys.objects so
inner join sys.sql_expression_dependencies ed on ed.referenced_id = so.object_id
inner join sys.objects rso on rso.object_id = ed.referencing_id
inner join ObjectHierarchy as cp on rso.object_id = cp.object_id and rso.object_id <> so.object_id
where so.type in ( ‘P’, ‘V’, ‘IF’, ‘FN’, ‘TF’, ‘U’)
and ( rso.type is null or rso.type in ( ‘P’, ‘V’, ‘IF’, ‘FN’, ‘TF’, ‘U’ ) )
and cp.Obj_Path not like ‘%/’ + so.name + ‘/%’ ) — prevent cycles n hierarcy
select Base_Object_Name
, Base_Object_Type
, REPLICATE ( ‘ ‘ , Level ) + Name as Indented_Name
, SCHEMA_NAME ( Schema_Id ) + ‘.’ + Name as object_id
, Type_Desc as Object_Type
, Level
, Obj_Path
from ObjectHierarchy as p
order by Obj_Path

https://stackoverflow.com/questions/10652746/tree-of-all-dependencies-in-a-sql-server-database

Dependencies

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;

RSS
LinkedIn
Close Bitnami banner
Bitnami