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