Created
September 21, 2012 11:01
-
-
Save mattiaswolff/3760910 to your computer and use it in GitHub Desktop.
SQL: Database optimization script
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
USE [msdb] | |
GO | |
/****** Object: Job [MediusFlow optimization job] Script Date: 09/12/2012 18:24:14 ******/ | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/12/2012 18:24:14 ******/ | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MediusFlow optimization job', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'No description available.', | |
@category_name=N'[Uncategorized (Local)]', | |
@owner_login_name=N'sa', @job_id = @jobId OUTPUT | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
/****** Object: Step [Backup database] Script Date: 09/12/2012 18:24:16 ******/ | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup database', | |
@step_id=1, | |
@cmdexec_success_code=0, | |
@on_success_action=3, | |
@on_success_step_id=0, | |
@on_fail_action=2, | |
@on_fail_step_id=0, | |
@retry_attempts=0, | |
@retry_interval=0, | |
@os_run_priority=0, @subsystem=N'TSQL', | |
@command=N'DECLARE @strDBName VARCHAR(50) -- database name | |
DECLARE @strPath VARCHAR(256) -- path for backup files | |
DECLARE @strFileName VARCHAR(256) -- filename for backup | |
DECLARE @strFileDate VARCHAR(20) -- used for file name | |
DECLARE @intRetainDays INT -- used for number of days to retain old backups | |
SET @strDBName = ''MediusFlow'' | |
SET @intRetainDays = 5 | |
SET @strPath = ''C:\Backup\'' | |
SELECT @strFileDate = CONVERT(VARCHAR(20),GETDATE(),112) + ''_'' + REPLACE(CONVERT(VARCHAR,GETDATE(),108) , '':'', '''') | |
SET @strFileName = @strPath + @strDBName + ''_'' + @strFileDate + ''.BAK'' | |
BACKUP DATABASE @strDBName TO DISK = @strFileName | |
WITH RETAINDAYS = @intRetainDays | |
--INIT', | |
@database_name=N'master', | |
@flags=0 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
/****** Object: Step [Truncate IFLOW_LOG] Script Date: 09/12/2012 18:24:16 ******/ | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate IFLOW_LOG', | |
@step_id=2, | |
@cmdexec_success_code=0, | |
@on_success_action=3, | |
@on_success_step_id=0, | |
@on_fail_action=2, | |
@on_fail_step_id=0, | |
@retry_attempts=0, | |
@retry_interval=0, | |
@os_run_priority=0, @subsystem=N'TSQL', | |
@command=N'USE MediusFlow | |
GO | |
DECLARE @intNumberOfWeeksToRetain INT -- used for the number of weeks to retain data for | |
SET @intNumberOfWeeksToRetain = 4 | |
SET @intNumberOfWeeksToRetain = @intNumberOfWeeksToRetain * -1 | |
DELETE FROM IFLOW_LOG | |
WHERE SOURCE = ''MIG'' | |
AND TIMESTAMP < DATEADD(WEEK, @intNumberOfWeeksToRetain, GETDATE())', | |
@database_name=N'master', | |
@flags=0 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
/****** Object: Step [Truncate Log and Database] Script Date: 09/12/2012 18:24:16 ******/ | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate Log and Database', | |
@step_id=3, | |
@cmdexec_success_code=0, | |
@on_success_action=3, | |
@on_success_step_id=0, | |
@on_fail_action=2, | |
@on_fail_step_id=0, | |
@retry_attempts=0, | |
@retry_interval=0, | |
@os_run_priority=0, @subsystem=N'TSQL', | |
@command=N'USE MediusFlow | |
GO | |
ALTER DATABASE [MediusFlow] SET RECOVERY SIMPLE WITH NO_WAIT | |
--Two times is intentional | |
DBCC SHRINKFILE (IFLOW_LOG, 1) | |
DBCC SHRINKFILE (IFLOW_LOG, 1) | |
DBCC SHRINKDATABASE (''MediusFlow'') | |
DBCC SHRINKDATABASE (''MediusFlow'') | |
ALTER DATABASE [MediusFlow] SET RECOVERY FULL WITH NO_WAIT', | |
@database_name=N'master', | |
@flags=0 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
/****** Object: Step [Reindex database] Script Date: 09/12/2012 18:24:16 ******/ | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reindex database', | |
@step_id=4, | |
@cmdexec_success_code=0, | |
@on_success_action=3, | |
@on_success_step_id=0, | |
@on_fail_action=2, | |
@on_fail_step_id=0, | |
@retry_attempts=0, | |
@retry_interval=0, | |
@os_run_priority=0, @subsystem=N'TSQL', | |
@command=N'USE MediusFlow | |
GO | |
EXEC sp_msforeachtable ''ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)''', | |
@database_name=N'master', | |
@flags=0 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
/****** Object: Step [Truncate log and database again] Script Date: 09/12/2012 18:24:16 ******/ | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate log and database again', | |
@step_id=5, | |
@cmdexec_success_code=0, | |
@on_success_action=3, | |
@on_success_step_id=0, | |
@on_fail_action=2, | |
@on_fail_step_id=0, | |
@retry_attempts=0, | |
@retry_interval=0, | |
@os_run_priority=0, @subsystem=N'TSQL', | |
@command=N'USE MediusFlow | |
GO | |
ALTER DATABASE [MediusFlow] SET RECOVERY SIMPLE WITH NO_WAIT | |
--Two times is intentional | |
DBCC SHRINKFILE (IFLOW_LOG, 1) | |
DBCC SHRINKFILE (IFLOW_LOG, 1) | |
DBCC SHRINKDATABASE (''MediusFlow'') | |
DBCC SHRINKDATABASE (''MediusFlow'') | |
ALTER DATABASE [MediusFlow] SET RECOVERY FULL WITH NO_WAIT', | |
@database_name=N'master', | |
@flags=0 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
/****** Object: Step [Update statistics] Script Date: 09/12/2012 18:24:16 ******/ | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update statistics', | |
@step_id=6, | |
@cmdexec_success_code=0, | |
@on_success_action=1, | |
@on_success_step_id=0, | |
@on_fail_action=2, | |
@on_fail_step_id=0, | |
@retry_attempts=0, | |
@retry_interval=0, | |
@os_run_priority=0, @subsystem=N'TSQL', | |
@command=N'USE MediusFlow | |
GO | |
EXEC sp_UpdateStats', | |
@database_name=N'master', | |
@flags=0 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
COMMIT TRANSACTION | |
GOTO EndSave | |
QuitWithRollback: | |
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION | |
EndSave: | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment