TransactionLog

MSSQL Check TransactionLog Backup

Check if the are database without recent (2 days) TransactionLog backups for all databases in FULL Recovery.

SELECT sys.databases.name
FROM sys.databases
WHERE sys.databases.name NOT IN
(SELECT DISTINCT sys.databases.name from sys.databases 
INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name
WHERE (msdb..backupset.type = 'L' AND msdb..backupset.backup_start_date > (GetDate() -2)))
AND sys.databases.recovery_model != 3 and sys.databases.state = 0

 

MSSQL Truncate TransactionLog Without Backup

With the following command you can truncate the transaction log withouot making a backup. Don’t forget to make a full backup afterwards.

ALTER DATABASE [DATABASENAME] SET RECOVERY SIMPLE WITH NO_WAIT

You can then maby do a shrink of the log file and then set the database back to FULL recovery.

ALTER DATABASE [DATABASENAME] SET RECOVERY FULL WITH NO_WAIT