USE
[msdb]
GO
/****** Object: Job [SHRINK DBS] Script Date: 8/20/2013 3:04:49 PM ******/
BEGIN
TRANSACTION
DECLARE
@ReturnCode
INT
SELECT
@ReturnCode
= 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 8/20/2013 3:04:49 PM ******/
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’SHRINK DBS’,
@enabled=1,
@notify_level_eventlog=0,
@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’DOBREVEENERGIA\vinicius.infoworker’,
@job_id
=
@jobId
OUTPUT
IF (@@ERROR <> 0 OR
@ReturnCode
<> 0) GOTO
QuitWithRollback
/****** Object: Step [SHRINK_DBS] Script Date: 8/20/2013 3:04:49 PM ******/
EXEC
@ReturnCode
=
msdb.dbo.sp_add_jobstep
@job_id=@jobId, @step_name=N’SHRINK_DBS’,
@step_id=1,
@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’DECLARE CDBCursor CURSOR
FOR
SELECT name FROM SYS.sysdatabases WHERE dbid > 4 ”
OPEN CDBCursor
DECLARE @DBNAME SYSNAME, @FILE SYSNAME, @FILEID INT
FETCH CDBCursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ”DATABASE:” + @DBNAME
EXEC(”ALTER DATABASE [” + @DBNAME + ”] SET RECOVERY SIMPLE”)
DECLARE CFILES CURSOR
FOR
SELECT name , fileid, DB_NAME(dbid) FROM SYS.sysaltfiles WHERE DBID = DB_ID(@DBNAME)
AND groupid = 0
OPEN CFILES
FETCH CFILES INTO @FILE, @FILEID, @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ”FILE :” + @FILE
EXEC(”USE [” + @DBNAME + ”];” + ”DBCC SHRINKFILE ( “” + @FILE + ””, 0 ) ”)
FETCH CFILES INTO @FILE, @FILEID, @DBNAME
END
CLOSE CFILES
DEALLOCATE CFILES
FETCH CDBCursor INTO @DBNAME
END
CLOSE CDBCursor
DEALLOCATE CDBCursor’,
@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_jobschedule
@job_id=@jobId, @name=N’SHRINK_DBS’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130820,
@active_end_date=99991231,
@active_start_time=210000,
@active_end_time=235959,
@schedule_uid=N’1021f0b5-05fb-4f55-81b7-aed874c39f76′
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
|