Сжатие базы данных

USE databasename;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE databasename 
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (databasename_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE databasename 
SET RECOVERY FULL;
GO

Проверка брокера

--Проверка брокера
select is_broker_enabled from sys.databases where name = DB_NAME()

--Проверка MASTER KEY
SELECT d.is_master_key_encrypted_by_server
FROM sys.databases AS d
WHERE d.name = DB_NAME()

--включить брокера
    DECLARE @S NVARCHAR(4000) 
    SET @S = 'ALTER DATABASE [' + DB_NAME() + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' 
    EXEC (@S) 
    SET @S = 'BEGIN TRY ALTER DATABASE [' + DB_NAME() + '] SET ENABLE_BROKER END TRY BEGIN CATCH ALTER DATABASE [' + DB_NAME() + '] SET NEW_BROKER END CATCH' 
    EXEC (@S) 
    SET @S = 'ALTER DATABASE [' + DB_NAME() + '] SET MULTI_USER' 
    EXEC (@S) 
    SET @S = 'ALTER AUTHORIZATION ON DATABASE::[' + DB_NAME() + '] TO [sa]' 
    EXEC (@S) 
    SET @S = 'IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.SYMMETRIC_KEYS WHERE NAME LIKE ''%MASTERKEY%'') CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''Pa$$w0rd'''
    EXEC (@S) 
GO 

-- дропнуть и создать заново мастер ключ
DROP MASTER KEY 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd' 
GO
Состояние suspect - починка
alter database БД set EMERGENCY, SINGLE_USER
dbcc checkdb('БД', REPAIR_ALLOW_DATA_LOSS)
dbcc checkdb('БД', REPAIR_REBUILD)
alter database БД set ONLINE, MULTI_USER