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
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
** 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