Get Indexfragmentation details for all databases in a SQL Server instance

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

DECLARE @Tbl TABLE (
	ServerName VARCHAR(128)
	,DBName VARCHAR(128)
	,SchemaName VARCHAR(128)
	,TableName VARCHAR(100)
	,IndexName VARCHAR(100)
	,FragPercent FLOAT
	,IndexType TINYINT
	,IsPrimaryKey BIT
	);

INSERT INTO @Tbl
EXEC SP_MSforeachdb @command1 = 'use [?];
                select  @@Servername, 
                        DB_NAME(),
                        sc.name as SchemaName,
                        object_name (s.object_id) as TableName, 
                        I.name, 
                        s.avg_fragmentation_in_percent, 
                        I.type, 
                        I.is_primary_key
                from sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, ''LIMITED'') as S
                    join sys.indexes as I on s.object_id = I.object_id and s.index_id = I.index_id
                    join sys.objects as O on s.object_id = O.object_id 
                    join sys.schemas as sc on O.schema_id = sc.schema_id
                where o.type = ''U'' and avg_fragmentation_in_percent > 20 and (I.name is not null) 
                ORDER BY  avg_fragmentation_in_percent DESC'

SELECT *
FROM @Tbl
GO

 

Script to send e-mail once transaction logfile usage reaches to 60%

 

USE msdb

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#MonitorTransactionLogFileUsage') IS NOT NULL
	DROP TABLE #MonitorTransactionLogFileUsage

CREATE TABLE #MonitorTransactionLogFileUsage (
	ID INT IDENTITY(1, 1)
	,DatabaseName SYSNAME
	,LogSizeInMB DECIMAL(18, 5)
	,LogSpaceUsedInPercentage DECIMAL(18, 5)
	,[Status] INT
	)

INSERT INTO #MonitorTransactionLogFileUsage (
	DatabaseName
	,LogSizeInMB
	,LogSpaceUsedInPercentage
	,[Status]
	)
EXEC ('DBCC SQLPERF(LOGSPACE)')

IF OBJECT_ID('tempdb..#LogfileGrowth') IS NOT NULL
	DROP TABLE #LogfileGrowth

CREATE TABLE #LogfileGrowth (LogGrowth VARCHAR(4000))

INSERT INTO #LogfileGrowth
SELECT 'Database ' + '' + cast(DatabaseName AS VARCHAR(50)) + 

 ' LogSpaceUsedInPercentage  has grown to ' + 

cast(LogSpaceUsedInPercentage AS VARCHAR(50)) + '' + '% ' + ' ' + '' +  

'Database recover model is ' + '' + cast(recovery_model_desc 

COLLATE Latin1_General_CI_AS_KS_WS AS VARCHAR(50)) + ' and it is growing due to ' 

+ cast(log_reuse_wait_desc AS VARCHAR(150)) LogGrowth
FROM #MonitorTransactionLogFileUsage a
INNER JOIN sys.databases b ON a.DatabaseName = b.name
	AND a.DatabaseName = 'DB Name you would like to monitor '
WHERE LogSpaceUsedInPercentage > 60

DECLARE @body1 VARCHAR(4000)

SET @body1 = (
		SELECT TOP 1 LogGrowth
		FROM #LogfileGrowth
		)

DECLARE @server_name VARCHAR(100);

SELECT @server_name = convert(VARCHAR(100), SERVERPROPERTY('servername'));

DECLARE @sub VARCHAR(1000);

SELECT @sub = 'Logfile is getting filled on  ' + @server_name + ' ';

DECLARE @rowcount INT

SELECT @rowcount = (
		SELECT count(1)
		FROM #LogfileGrowth
		HAVING count(1) > 0
		)

IF @rowcount > 0
BEGIN
	--database-notifications@accuratebackground.pagerduty.com
	EXEC msdb.dbo.sp_send_dbmail @profile_name = 'your db profile name'
		,@recipients = ' mention list of recipients you need to send mail'
		,@subject = @sub
		,@body = @body1
		,@body_format = 'HTML';
END