Log space used + Email Alert ability

use [model]

CREATE TABLE temp (

    [Database Name] varchar(100),

    [Log Size (MB)] decimal (10,2),

    [Log Space Used (%)] decimal (10,2),

    [Status] varchar(1)

    )

INSERT temp EXEC (''DBCC SQLPERF (LOGSPACE)'');

--List the result from temp table

DECLARE @Count AS INTEGER

SELECT @Count= COUNT(*) FROM temp WHERE [Log Space Used (%)]>50

IF (@Count > 0)

BEGIN

PRINT CAST(@Count AS VARCHAR) + '' are Over 50%''

SELECT * FROM temp WHERE [Log Space Used (%)]>50

/*

--Uncomment this section out and update your email settings (@profile_name and @recipients) to

--put into SQL Agent to run every n- number of minutes of your choice

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = ''<Your Maill Profile Here>'',

    @recipients = ''YourSQLAlertGroup@yourdomain.org'',

    @query = ''SELECT * FROM model.dbo.temp WHERE [Log Space Used (%)]>50'',

      @subject = ''Unusual Size for Log File (DB > 50% LOG Space Used) - Please investigate '',

       --@Body = ''See if this is causing an issue..... '' ,

    @attach_query_result_as_file = 0 ;

*/

END

drop table temp

Shrink Log Files [preserve CHECKPOINT]

Use [Database]

BACKUP LOG Database TO DISK='<Transaction Log Path><filename>.trn'
WITH COMPRESSION, STATS

DBCC SHRINKFILE('Database_Log', 500) -- Shrink to e.g. 1000 MB

--select * from sys.databases --In case you need to get the log file's name

use [model]
CREATE TABLE temp (
    [Database Name] varchar(100),
    [Log Size (MB)] decimal (10,2),
    [Log Space Used (%)] decimal (10,2),
    [Status] varchar(1)
    )

INSERT temp EXEC ('DBCC SQLPERF (LOGSPACE)');

select * from model.dbo.temp ORDER BY [Log Space Used (%)] desc

drop table model.dbo.temp

xShrink Log files too

** You must change the FILE NAME to be unique so that if it crashes you can play back the logs.

Increment 01 to 02 to 03 to 04 ….

Use ch_system

--

BACKUP LOG [ch_system] TO DISK='g:\sqlbackups\logs\EPSi_Distribute_Log_emerg01.trn'

WITH COMPRESSION, STATS

DBCC SHRINKFILE('EPSi_Distribute_Log', 20) -- Shrink to e.g. 1000 MB

Eventually, you’ll see a lower number in the UsedPages column. Once it gets down to a reasonable level, you’ll see disk space will be returned to OS

MERGE USING

   MERGE [Dest].[dbo].[Requests] T1 USING [Source].[dbo].[Requests_tmp] T2

  ON T1.[patfirstname] = T2.patfirstname AND t1.[patlastname]=t2.patlastname and t1.dtReceived=t2.dtreceived

     --AND T1.encPasswdHash = T2.encPasswdHash

     WHEN NOT MATCHED THEN

       INSERT (

      [RequestorLastName]

      ,[RequestorEmail]

      ,[Notify]

      ,[OID]

      ,[PatFirstName]

      ,[PatLastName]

      ,[PatDOB]

      ,[dtService]

      ,[RunNum]

      ,[ReqCategory]

      ,[Comments]

      ,[Other]

      ,[IPaddress]

      ,[SessionID]

      ,[dtReceived]

      ,[Status]

      ,[AssignedTo]

      ,[Active])

         VALUES(  T2.[RequestorLastName]

      ,T2.[RequestorEmail]

      ,T2.[Notify]

      ,T2.[OID]

      ,T2.[PatFirstName]

      ,T2.[PatLastName]

      ,T2.[PatDOB]

      ,T2.[dtService]

      ,T2.[RunNum]

      ,T2.[ReqCategory]

      ,T2.[Comments]

      ,T2.[Other]

      ,T2.[IPaddress]

      ,T2.[SessionID]

      ,T2.[dtReceived]

      ,T2.[Status]

      ,T2.[AssignedTo]

      ,T2.[Active]) ;

https://stackoverflow.com/questions/3599698/merge-when-not-matched-inserts-exception