SQL Server Data Dictionary Query - Data table

Rédigé par Sozezzo - - Aucun commentaire

This query returns list of tables and their columns with details.

SELECT @@Servername AS ServerName     , DB_NAME() AS DatabaseName     , DB_ID() AS DatabaseId     , sc.name AS SchemaName     , tab.schema_id AS SchemaId     , tab.NAME AS TableName     , tab.object_id AS TableId     , col.NAME AS ColumnName     , col.column_id     , t.NAME AS DataTypeName     , CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN col.max_length/2 ELSE col.max_length END AS Length_Size     , t.max_length     , CAST(col.is_identity AS INT) AS is_identity     , col.scale     , t.precision AS Precision     , t.NAME + CASE WHEN t.is_user_defined = 0 THEN ISNULL('(' + CASE WHEN t.NAME IN('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary') THEN CASE col.max_length WHEN -1 THEN 'MAX' ELSE CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN CAST(col.max_length / 2 AS VARCHAR(4)) ELSE CAST(col.max_length AS VARCHAR(4)) END END WHEN t.NAME IN('datetime2', 'datetimeoffset', 'time') THEN CAST(col.scale AS VARCHAR(4)) WHEN t.NAME IN('decimal', 'numeric') THEN CAST(col.precision AS VARCHAR(4)) + ', ' + CAST(col.scale AS VARCHAR(4)) END + ')', '') ELSE ':' +             (             SELECT c_t.NAME + ISNULL('(' + CASE WHEN c_t.NAME IN('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary') THEN CASE c.max_length WHEN -1 THEN 'MAX' ELSE CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN CAST(c.max_length / 2 AS VARCHAR(4)) ELSE CAST(c.max_length AS VARCHAR(4)) END END WHEN c_t.NAME IN('datetime2', 'datetimeoffset', 'time') THEN CAST(c.scale AS VARCHAR(4)) WHEN c_t.NAME IN('decimal', 'numeric') THEN CAST(c.precision AS VARCHAR(4)) + ', ' + CAST(c.scale AS VARCHAR(4)) END + ')', '')             FROM sys.columns AS c             INNER JOIN sys.types AS c_t ON c.system_type_id = c_t.user_type_id             WHERE                 c.object_id = col.object_id                 AND c.column_id = col.column_id                 AND c.user_type_id = col.user_type_id             ) END AS [DataType]     , CASE WHEN col.collation_name IS NOT NULL THEN 1 ELSE 0 END has_collation_name     , col.collation_name     , col.is_nullable     , CASE WHEN col.is_nullable = 0 THEN 'N' ELSE 'Y' END AS nullable     , CASE WHEN def.DEFINITION IS NOT NULL THEN 1 ELSE 0 END AS has_default_value     , CASE WHEN def.DEFINITION IS NOT NULL THEN def.DEFINITION ELSE '' END AS default_value     , CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_primary_key     , CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END AS primary_key     , CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS is_foreign_key     , CASE WHEN fk.parent_column_id IS NOT NULL THEN 'FK' ELSE '' END AS foreign_key     , CASE WHEN uk.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_unique_key     , CASE WHEN uk.column_id IS NOT NULL THEN 'UK' ELSE '' END AS unique_key     , CASE WHEN ch.check_const IS NOT NULL THEN 1 ELSE 0 END AS is_check_contraint     , CASE WHEN ch.check_const IS NOT NULL THEN ch.check_const ELSE '' END AS check_contraint     , col.is_computed     , cc.DEFINITION AS computed_column_definition     , ep.value AS comments
FROM sys.computed_columns AS cc
RIGHT OUTER JOIN sys.tables AS tab
INNER JOIN sys.schemas AS sc ON tab.schema_id = sc.schema_id
LEFT OUTER JOIN sys.columns AS col ON tab.object_id = col.object_id
LEFT OUTER JOIN sys.types AS t ON col.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.default_constraints AS def ON def.object_id = col.default_object_id
LEFT OUTER JOIN (SELECT sys.index_columns.object_id     , sys.index_columns.column_id
FROM sys.index_columns
INNER JOIN sys.indexes ON sys.index_columns.object_id = sys.indexes.object_id
AND sys.index_columns.index_id = sys.indexes.index_id
WHERE     (sys.indexes.is_primary_key = 1)) AS pk ON col.object_id = pk.object_id
AND col.column_id = pk.column_id
LEFT OUTER JOIN (SELECT fc.parent_column_id     , fc.parent_object_id
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
GROUP BY fc.parent_column_id , fc.parent_object_id) AS fk ON fk.parent_object_id = col.object_id
AND fk.parent_column_id = col.column_id
LEFT OUTER JOIN (
SELECT parent_column_id     , parent_object_id     , 'Check' AS check_const
FROM sys.check_constraints AS c
GROUP BY parent_column_id , parent_object_id
) AS ch ON col.column_id = ch.parent_column_id
AND col.object_id = ch.parent_object_id
LEFT OUTER JOIN (
SELECT index_columns_1.object_id     , index_columns_1.column_id
FROM sys.index_columns AS index_columns_1
INNER JOIN sys.indexes AS indexes_1 ON indexes_1.index_id = index_columns_1.index_id
AND indexes_1.object_id = index_columns_1.object_id
WHERE     (indexes_1.is_unique_constraint = 1)
GROUP BY index_columns_1.object_id , index_columns_1.column_id
) AS uk ON col.column_id = uk.column_id
AND col.object_id = uk.object_id
LEFT OUTER JOIN sys.extended_properties AS ep ON tab.object_id = ep.major_id
AND col.column_id = ep.minor_id
AND ep.name = 'MS_Description'
AND ep.class_desc = 'OBJECT_OR_COLUMN' ON cc.object_id = tab.object_id
AND cc.column_id = col.column_id


sources : https://dataedo.com/blog/useful-sql-server-data-dictionary-queries-every-dba-should-have

Temporarily disable a trigger on a data table

Rédigé par Sozezzo - - Aucun commentaire


Disable all triggers on a table, you do what you have to do, after enable only the triggers that have been disabled.

Step 1 : Disable all triggers

--#region Disable triggers

DECLARE @triggers TABLE (SqlEnableTrigger NVARCHAR(MAX));
DECLARE @SchemaName VARCHAR(128) = 'mySchema'; – ** TO DO **
DECLARE @TableName VARCHAR(128) = 'mytable'; – ** TO DO **
DECLARE @SqlTrigger NVARCHAR(MAX);

DELETE FROM @triggers;

INSERT INTO @triggers (SqlEnableTrigger)
SELECT  'ENABLE TRIGGER '  + QUOTENAME(sc.name) + N'.' + QUOTENAME(tg.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(tb.name) + N';' AS SqlEnableTrigger
FROM sys.schemas AS sc
      INNER JOIN sys.tables AS tb ON sc.schema_id = tb.schema_id
      RIGHT OUTER JOIN sys.triggers AS tg ON tb.object_id = tg.parent_id
WHERE    (sc.name =@SchemaName)
      AND (tb.name = @TableName)
      AND (tg.is_disabled = 0);

SET @SqlTrigger = 'DISABLE TRIGGER ALL ON '+QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName);
PRINT @SqlTrigger;
EXEC (@SqlTrigger);

--#endregion Disable triggers

 

Step 2: You do what you have to do.

Your code... to update, to delete, to insert, or everything you want.


Step 3 : Enable all triggers that have been disabled.

--#region Enaable triggers

SET @SqlTrigger = '';
SELECT @SqlTrigger = @SqlTrigger + SqlEnableTrigger FROM @triggers;
PRINT @SqlTrigger;
EXEC (@SqlTrigger);

--#endregion Enaable triggers

 

List all triggers, the SchemaName is empty when it is a DDL trigger

SELECT ISNULL(sc.name,'') AS SchemaName,
        ISNULL(tb.name,'(Database)') AS TableName,
        tg.name,
        tg.object_id,
        tg.parent_class,
        tg.parent_class_desc,
        tg.parent_id,
        tg.type,
        tg.type_desc,
        tg.create_date,
        tg.modify_date,
        tg.is_ms_shipped,
        tg.is_disabled,
        tg.is_not_for_replication,
        tg.is_instead_of_trigger,
        OBJECTPROPERTY(tg.object_id, 'ExecIsUpdateTrigger') AS isupdate,
        OBJECTPROPERTY(tg.object_id, 'ExecIsDeleteTrigger') AS isdelete,
        OBJECTPROPERTY(tg.object_id, 'ExecIsInsertTrigger') AS isinsert,
        OBJECTPROPERTY(tg.object_id, 'ExecIsAfterTrigger') AS isafter,
        OBJECTPROPERTY(tg.object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof
FROM sys.schemas AS sc
      INNER JOIN sys.tables AS tb ON sc.schema_id = tb.schema_id
      RIGHT OUTER JOIN sys.triggers AS tg ON tb.object_id = tg.parent_id;

 

image


Source :
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-triggers-transact-sql?view=sql-server-ver15

Audit jobs

Rédigé par Sozezzo - - Aucun commentaire

A jobSql Job was changed. The questions are: When? by Who?

We suppose you always have a copy of yours jobs.

 

** Check the SQL Script for shared folder, your e-mail and SMTP server.

 

Delete old audit configuration – make sure you want to do that.


print @@servername

USE [master]

GO

BEGIN TRY ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP  SERVER AUDIT SPECIFICATION [ServerAuditSpecification];                    END TRY BEGIN CATCH END CATCH;

GO

BEGIN TRY ALTER SERVER AUDIT [ServerAudit] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP  SERVER AUDIT [ServerAudit];                    END TRY BEGIN CATCH END CATCH;

GO

--xp_cmdshell 'DIR L:Audit*'

exec xp_cmdshell 'MD C:Audit*';

exec xp_cmdshell 'DEL C:Audit*.sqlaudit';

GO

 

 

Create an Audit on a network share


PRINT '-- Step 1 - Create an Audit on a network share'

USE [master];

GO

CREATE SERVER AUDIT [ServerAudit]

TO FILE

(     FILEPATH  = N'C:Audit'

     ,MAXSIZE   = 100 MB

     ,MAX_FILES = 3500

     ,RESERVE_DISK_SPACE = OFF

)

WITH

(     QUEUE_DELAY = 1000

     ,ON_FAILURE  = CONTINUE

)

GO

Go

PRINT '-- Step 2 - Enable the Audit'

ALTER SERVER AUDIT [ServerAudit] WITH (STATE=ON);

Go

Go

-- Step 3 - Create a Server Audit Specification

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification] FOR SERVER AUDIT [ServerAudit]

--ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),

--ADD (AUDIT_CHANGE_GROUP),

--ADD (BACKUP_RESTORE_GROUP),

--ADD (BROKER_LOGIN_GROUP),

--ADD (DATABASE_CHANGE_GROUP),

--ADD (DATABASE_LOGOUT_GROUP),

--ADD (DATABASE_MIRRORING_LOGIN_GROUP),

--ADD (DATABASE_OBJECT_ACCESS_GROUP),

--ADD (DATABASE_OBJECT_CHANGE_GROUP),

--ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),

--ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),

--ADD (DATABASE_OPERATION_GROUP),

--ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),

--ADD (DATABASE_PERMISSION_CHANGE_GROUP),

--ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),

--ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),

--ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),

--ADD (DBCC_GROUP),

--ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),

--ADD (FAILED_LOGIN_GROUP),

--ADD (FULLTEXT_GROUP),

--ADD (LOGIN_CHANGE_PASSWORD_GROUP),

--ADD (LOGOUT_GROUP),

--ADD (SCHEMA_OBJECT_ACCESS_GROUP),

--ADD (SCHEMA_OBJECT_CHANGE_GROUP),

--ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),

--ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),

--ADD (SERVER_OBJECT_CHANGE_GROUP),

--ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),

--ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),

--ADD (SERVER_OPERATION_GROUP),

--ADD (SERVER_PERMISSION_CHANGE_GROUP),

--ADD (SERVER_PRINCIPAL_CHANGE_GROUP),

--ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),

--ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),

--ADD (SERVER_STATE_CHANGE_GROUP),

--ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),

--ADD (SUCCESSFUL_LOGIN_GROUP),

--ADD (TRACE_CHANGE_GROUP),

--ADD (USER_CHANGE_PASSWORD_GROUP),

ADD (USER_DEFINED_AUDIT_GROUP)

WITH (STATE = ON);

GO

 

Log or send e-mail when a job was changed.


 

PRINT '-- Create/Update trigger [trig_sysjobs_log] ON [dbo].[sysjobs]'
USE [msdb]
GO
BEGIN TRY DROP TRIGGER [dbo].[trig_sysjobs_log]; END TRY BEGIN CATCH END CATCH;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:    Audit/Log jobs changements
-- =============================================
CREATE TRIGGER [dbo].[trig_sysjobs_log]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
     SET NOCOUNT ON

--#region DECLARE variables

    DECLARE @bodyAudit nvarchar(4000);
     DECLARE @bodyEvent nvarchar(255);
     DECLARE @bodyEmail nvarchar(max);
     DECLARE @subjectEmail nvarchar(255);
    
     DECLARE @activity       varchar(20)
     DECLARE @jobName        varchar(255);
     DECLARE @jobNameDeleted varchar(255);
     SET @JobName = '';
     SET @jobNameDeleted = '';

    DECLARE @systemUser varchar(100)
     DECLARE @datelog          VARCHAR(50);
     DECLARE @SUSER_ID         varchar(20);
     DECLARE @originalLogin    nvarchar(256);
     DECLARE @hostname    nvarchar(128);
     DECLARE @programName nvarchar(max);

--#endregion DECLARE variables

--#region Create message

    SET @systemUser = SYSTEM_USER;
     SET @datelog    = GETUTCDATE();
     SET @SUSER_ID   = CAST(SUSER_ID() as varchar(20));
     SET @originalLogin   = ORIGINAL_LOGIN();
     SET @hostname   = HOST_NAME();
     SET @programName= PROGRAM_NAME();

    if exists(SELECT * from inserted)
     begin
         if exists (SELECT * from deleted)
         BEGIN
             SET @activity = 'UPDATE';
             SELECT @jobNameDeleted = name from deleted;
         END
         else
         BEGIN
             SET @activity = 'INSERT';
         END
         SELECT @jobName = name from inserted;
     end
     else
     begin
         SET @activity = 'DELETE';
         SELECT @jobName = name from deleted;
     end
    
     -- Audit
     SET @bodyAudit = 'AuditJob' + CHAR(13) + CHAR(10);;
     SET @bodyAudit = @bodyAudit + N'-Job : ' + @JobName + CHAR(13) + CHAR(10);
     IF ( @jobNameDeleted NOT IN ( @JobName , '') ) SET @bodyAudit = @bodyAudit + N'-Job Deleted : ' + @jobNameDeleted + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Activity : ' + @activity + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Original Login : ' + @originalLogin + CHAR(13) + CHAR(10);   
     IF (@originalLogin<>@systemUser) SET @bodyAudit = @bodyAudit + N'-System User : ' + @systemUser + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Date UTC : ' + @datelog + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Host name : ' + @hostname + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-SUSER_ID : ' + @SUSER_ID + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Program Name : ' + @programName + CHAR(13) + CHAR(10);
     BEGIN TRY
         EXEC sp_audit_write @user_defined_event_id =  169 , @succeeded =  0, @user_defined_information = @bodyAudit;
     END TRY BEGIN CATCH END CATCH;

--#endregion Create message

--#region Send email

    -- Check if database mail service is configurated with a default profil
     IF (EXISTS ( SELECT prinprof.is_default
                  FROM msdb.sys.database_principals dbprin, msdb.dbo.sysmail_principalprofile prinprof, msdb.dbo.sysmail_profile prof
                  WHERE dbprin.principal_id = msdb.dbo.get_principal_id(prinprof.principal_sid) AND
                  prof.profile_id = prinprof.profile_id    AND prinprof.is_default = 1
                )
        )
     BEGIN
         -- Send email
         BEGIN TRY

            SET @bodyEmail = @bodyAudit;
             SET @subjectEmail = @activity+ ' job ['+ @jobName + ']'
             EXEC msdb.dbo.sp_send_dbmail
                 @recipients        = 'Youremail@mydomain.com',
                 @subject        = @subjectEmail,
                 @body            = @bodyEmail,
                 @from_address = 'noreplay@mydomain.com',
                 @reply_to = 'noreplay@mydomain.com';

        END TRY BEGIN CATCH END CATCH;
     END
--#endregion Send email

--#region Create Event Log

    SET @bodyEvent = REPLACE(@bodyAudit, '   ',' ');
     SET @bodyEvent = REPLACE(@bodyAudit, '  ',' ');
     SET @bodyEvent = SUBSTRING(@bodyEvent,0, 255);

    RAISERROR (@bodyEvent, -- Message text.
            10, -- Severity,
            1 -- State,
            ) WITH LOG;
    
--#endregion Create Event Log

END
GO
GO

 

Check your audit


SELECT top 10 * FROM sys.fn_get_audit_file('C:Audit*', NULL, NULL) ORDER BY 1 DESC;

 

 

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-server-audit-specification?view=sql-server-ver15

Monitoring database size files before blow up

Rédigé par Sozezzo - - Aucun commentaire

you are already in trouble and it is too late when ...

  • The transaction log becomes full, SQL Server Database Engine issues a 9002 error.
  • The SQL Server Database Filegroup is Full.
  • Run out of disk space


You must have the answer to theses questions:

  • Who many times the database base can still growth?
  • Do we use the best Practices for Growing Database Files?

Growing database files by a percentage is relatively harmless when databases are small.
Once you’ve grown beyond a few gigabytes, a ten percent file growth can be a cause of performance problems while SQL Server has to wait for space to be allocated.

At the other extreme, for databases over a few gigabytes, it doesn’t make sense to keep growing in tiny 1 MB increments where you can run into extreme physical fragmentation.

Generally, it is rather to grow in increments than in percentages.


Script to list database files that use percent growth, you do not need to change, you just need to be sure it’s okay.

SELECT d.name as database_name,     mf.name as file_name,     mf.type_desc as file_type,     mf.growth as current_percent_growth
FROM sys.master_files mf (NOLOCK)
JOIN sys.databases d (NOLOCK) on mf.database_id=d.database_id
WHERE is_percent_growth=1



Script to monitoring size files.

DECLARE @growth_count INT= 3;  -- Who many time the database can growth ?
SET NOCOUNT ON;
--#region Local drives
-- DROP TABLE IF EXISTS #svr_drives;
CREATE TABLE #svr_drives
(     DriveLetter CHAR(1),      TotalSpace  BIGINT,      FreeSpace   BIGINT
);
-- Populate temp table with free space of all local drives
INSERT INTO #svr_drives
SELECT DISTINCT         SUBSTRING(volume_mount_point, 1, 1) AS DriveLetter,         total_bytes / 1024 / 1024 AS TotalSpace,         available_bytes / 1024 / 1024 AS FreeSpace
FROM sys.master_files AS f      CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
--select * from #svr_drives
--#endregion Local drives
--#region create sysaltfiles
-- DROP TABLE IF EXISTS #sysaltfiles;
CREATE TABLE #sysaltfiles
(     [dbname]               [NVARCHAR](128) NULL,      [fileid]               [SMALLINT] NULL,      [FileSize]             [BIGINT] NULL,      [TotalSpace]           [BIGINT] NULL,      [FreeSpace]            [BIGINT] NULL,      [maxsize]              [INT] NOT NULL,      [status]               [INT] NULL,      [dbid]                 [SMALLINT] NULL,      [filename]             [NVARCHAR](260) NOT NULL,      [Usage]                [VARCHAR](4) NOT NULL,      [DriveLetter]          [NVARCHAR](1) NULL,      [IsgrowthInMB]         [INT] NOT NULL,      [growth]               [INT] NOT NULL,      [growth_count]         [INT],      [WillGrowth]           [BIGINT] NULL,      [WillMissingSpaceDisk] INT,      [NearFileSizeLimit]    INT,      [Message]              NVARCHAR(MAX)
);
INSERT INTO #sysaltfiles
(dbname,   fileid,   FileSize,   TotalSpace,   FreeSpace,   maxsize,   [status],   [dbid],   [filename],   Usage,   DriveLetter,   IsgrowthInMB,   growth
)
SELECT DB_NAME(dbid) dbname,         s.fileid,         (CONVERT(BIGINT, s.[size]) * 8) / 1024 AS FileSize,         TotalSpace,         FreeSpace AS FreeSpace,         maxsize / 128 AS maxsize,         [status],         [dbid],         [filename],        CASE STATUS&0x40            WHEN 0x40            THEN 'log'            ELSE 'data'        END AS 'Usage',         SUBSTRING([filename], 1, 1) AS DriveLetter,        CASE STATUS&0x100000            WHEN 0x100000            THEN 0            ELSE-1        END AS IsgrowthInMB,         growth
FROM sys.sysaltfiles s      INNER JOIN #svr_drives d ON SUBSTRING([filename], 1, 1) = d.DriveLetter
WHERE DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'       AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Updateability') <> 'READ_ONLY'       AND DB_NAME(dbid) NOT IN('model'); -- we can add a filter.
--#endregion create sysaltfiles
--#region Message
UPDATE #sysaltfiles   SET        growth_count = @growth_count,        [WillGrowth] = CASE                          WHEN IsgrowthInMB = 0                          THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1)                          ELSE(growth / 128) * @growth_count                      END,        WillMissingSpaceDisk = CASE                                  WHEN CASE                                           WHEN IsgrowthInMB = 0                                           THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1)                                           ELSE(growth / 128) * @growth_count                                       END > FreeSpace                                  THEN-1                                  ELSE 0                              END,        NearFileSizeLimit = CASE                               WHEN [maxsize] > 0                                    AND CASE                                            WHEN IsgrowthInMB = 0                                            THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count))                                            ELSE FileSize + (growth / 128) * @growth_count                                        END > [maxsize]                               THEN-1                               ELSE 0                           END,        [Message] = ''
FROM #sysaltfiles;
UPDATE #sysaltfiles   SET        [Message] = [Message] + CASE                                   WHEN WillMissingSpaceDisk = -1                                   THEN 'The database [' + dbname + '] will missing space disk when the file [' + [filename] + '] will growth (' + CAST([WillGrowth] / 1024 AS NVARCHAR(20)) + ' GB) after ' + CAST(growth_count AS NVARCHAR(20)) + ' "growths" on disk (' + [DriveLetter] + ':) ' + CAST([FreeSpace] / 1024 AS NVARCHAR(20)) + ' GB Free of ' + CAST([TotalSpace] / 1024 AS NVARCHAR(20)) + ' GB.' + CHAR(13) + CHAR(10)                                   ELSE ''                               END; 
UPDATE #sysaltfiles   SET        [Message] = [Message] + CASE                                   WHEN NearFileSizeLimit = -1                                   THEN 'The database [' + dbname + '] is near of file size limit for the file [' + [filename] + ']. Using ' + CAST(FileSize AS NVARCHAR(20)) + ' GB of max ' +CAST(maxsize AS NVARCHAR(20)) + ' GB' + CHAR(13) + CHAR(10)                                   ELSE ''                               END;
DECLARE @Message NVARCHAR(MAX)= '';
SELECT @Message = @Message + [Message]
FROM #sysaltfiles
WHERE [Message] <> ''
ORDER BY dbname;
-- SELECT * FROM #sysaltfiles;
--#endregion Message
PRINT @Message;



If you are using a job to monitoring, you can add this script to send an e-mail.

--#region Send email
IF(@Message <> '')
BEGIN
    DECLARE @body NVARCHAR(MAX)= '<p>The database server [servername] will have space disk problem. </p><p/><p/><p>[Message]</p><p/>This message was sent by [JobName] AT [servername]';
    DECLARE @jobname SYSNAME, @jobid UNIQUEIDENTIFIER;
    SELECT @jobname = b.name,             @jobid = b.job_id     FROM sys.dm_exec_sessions a,           msdb.dbo.sysjobs b     WHERE a.session_id = @@spid           AND (SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)), 1, 10)) = SUBSTRING(a.PROGRAM_NAME, 30, 10);     PRINT 'Job Name : ' + @jobname;
    SET @body = REPLACE(@body, '[JobName]', @jobname);     SET @body = REPLACE(@body, '[servername]', @@SERVERNAME);     SET @body = REPLACE(@body, '[Message]', @Message);
    EXEC msdb.dbo.sp_send_dbmail           @recipients = 'user@yourdomain.com',           @subject = 'Warning : Monitoring size file database',           @body = @body,           @body_format = 'HTML',           @from_address = 'Sql Server <sender@yourdomain.com>',           @reply_to = 'sender@yourdomain.com';
END;
--#endregion Send email

Get job name on fly

Rédigé par Sozezzo - - Aucun commentaire

Get Job name when you are executing job. This is useful when you need to make reference to the job being executed.

 


DECLARE @jobname sysname, @jobid uniqueidentifier

SELECT @jobname=b.name,@jobid=b.job_id 
FROM sys.dm_exec_sessions a,msdb.dbo.sysjobs b
WHERE a.session_id=@@spid
AND
(SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)),1,10)) = SUBSTRING(a.PROGRAM_NAME,30,10)
PRINT 'Job Name : ' + @jobname

 

Instead, you can Use Tokens in Job Steps.
https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps


PRINT 'Job Name  : '+'$(ESCAPE_SQUOTE(JOBNAME))'
PRINT 'Step Name : '+'$(ESCAPE_SQUOTE(STEPNAME))'

 

Fil RSS des articles de cette catégorie