Created
February 15, 2024 21:00
-
-
Save tcartwright/32eb9d6ed9d8743a0be3c463ccb719bd to your computer and use it in GitHub Desktop.
SQL SERVER: Simple maintenance scripts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*****************************************************************************************************************************************/ | |
/**** SHRINK LOG FILES *******************************************************************************************************************/ | |
/*****************************************************************************************************************************************/ | |
DECLARE @sql VARCHAR(max) = '' | |
SELECT @sql += CONCAT('USE [', DB_NAME([mf].[database_id]), ']', CHAR(13), CHAR(10), 'DBCC SHRINKFILE (''', [mf].[name], ''', 1) WITH NO_INFOMSGS;', CHAR(13), CHAR(10)) | |
FROM sys.[master_files] AS [mf] | |
WHERE [mf].[type_desc] = 'LOG' | |
AND [mf].[database_id] > 4 | |
AND [mf].[size] > 1024 | |
ORDER BY [mf].[name] | |
IF LEN(@sql) > 0 BEGIN | |
PRINT @sql | |
EXEC (@sql) | |
END ELSE | |
RAISERROR('NO RECORDS', 0, 1) WITH NOWAIT; | |
GO | |
/*****************************************************************************************************************************************/ | |
/**** SET RECOVERY TO SIMPLE *************************************************************************************************************/ | |
/*****************************************************************************************************************************************/ | |
USE [master] | |
DECLARE @sql VARCHAR(max) = '' | |
SELECT @sql += CONCAT('ALTER DATABASE [', [d].[name], '] SET RECOVERY SIMPLE WITH NO_WAIT', CHAR(13), CHAR(10)) | |
FROM sys.databases d | |
WHERE [d].[recovery_model_desc] <> 'SIMPLE' | |
AND [d].[database_id] > 4 | |
IF LEN(@sql) > 0 BEGIN | |
PRINT @sql | |
EXEC (@sql) | |
END ELSE | |
RAISERROR('NO RECORDS', 0, 1) WITH NOWAIT; | |
GO | |
/*****************************************************************************************************************************************/ | |
/**** SET DB OWNER TO SA *****************************************************************************************************************/ | |
/*****************************************************************************************************************************************/ | |
USE [master] | |
DECLARE @sql VARCHAR(max) = '' | |
SELECT @sql += CONCAT('ALTER AUTHORIZATION ON DATABASE::[', [d].[name], '] TO [sa]', CHAR(13), CHAR(10)) | |
FROM sys.databases d | |
WHERE [d].[owner_sid] <> 0x01 | |
IF LEN(@sql) > 0 BEGIN | |
PRINT @sql | |
EXEC (@sql) | |
END ELSE | |
RAISERROR('NO RECORDS', 0, 1) WITH NOWAIT; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment