利用作业定时收缩SQL SERVER数据库

一、收缩数据库介绍

如果数据库设计的尺寸过大，即使删除了数据库中大量数据，这时数据库依旧会占用大量磁盘资源。因此，需要根据用户实际需求，对数据库进行收缩。

收缩的3种方式：

1、AUTO_SHRINK

基本格式：

ALTER DATABASE database_name SET AUTO_SHRINK ON 

将该选项设为ON后，数据库引擎会自动收缩具有可用空间的数据库。

2、DBCC SHRINKDATABASE

基本格式：

DBCC SHRINKDATABASE('database_name',target_percent)

该方式要求手动收缩数据库大小，比自动化收缩数据库更灵活，可对整个数据库进行收缩。

3、DBCC SHRINKFILE

基本格式：

DBCC SHRINKFILE ('file_name',target_percent)

该命令可以收缩指定的数据库文件，可将文件收缩至小于其初始创建的大小，并且重新设置当前的大小为其初始创建的大小。值得注意的是，该收缩语句只能将数据库收缩为 自数据库创建之初以来指定过的最小的初始文件大小，如果你的数据库第一次创建的时候初始大小是1024MB，并且之后都没有人为调动过初始大小，那么抱歉你的数据最小也就只能收缩到1024MB，尽管可能其中有1023MB都没有被实际用到。。。

注意：建议不要过频繁地使用“自动收缩”特性，最好禁用自动收缩，改为手工收缩或者是调度操作，每隔一段时间收缩一次。

二、定时执行作业，定时对数据库进行收缩

1、创建作业步骤

--创建作业的一般步骤
--1.执行sp_add_job创建作业
--2.执行sp_add_jobstep创建一个或多个作业步骤
--3.执行sp_add_schedule创建计划 -- sp_add_jobschedule是直接为指定的一个job添加一个schedule相当于3.4的合并
--4.添加目标服务器 msdb.dbo.sp_add_jobserver

--5.开始作业

2、具体实例

--创建作业定时计划；
--每周对数据库文件进行一次收缩
--//--------------------------------------------------------------------------------------------------------
USE DF17DataPro
 --判断是否存在该作业
IF EXISTS(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'ShrinkDB_JOB')
   EXEC msdb.dbo.sp_delete_job @job_name=N'ShrinkDB_JOB'
 GO

DECLARE @jobname SYSNAME,@dbname SYSNAME
DECLARE @SQL VARCHAR(100)
SELECT  @jobname='ShrinkDB_JOB',  @dbname=db_name()
SET @SQL ='DBCC SHRINKDATABASE (db_name(),20%)'
 --1.创建作业
exec msdb..sp_add_job @job_name=@jobname
--2.创建作业步骤
exec msdb..sp_add_jobstep 
   @job_name=@jobname,
   @step_name = '收缩数据库',
   @subsystem = 'TSQL',
   @database_name = @dbname,
   @command = @SQL,
   --@command = 'exec DateTableProc',
   @retry_attempts = 1,     --重试次数
   @retry_interval = 1      --重试间隔

--3 创建调度,创建作业计划
EXEC msdb..sp_add_jobschedule @job_name = @jobname, 
   @name = 'ShrinkDB_JOB',
   @freq_type = 8,     --freq_type指定作业执行时间的值,4代表间隔为每天,8代表每周,16代表每月
   @freq_interval = 2,    --freq_interval执行作业的日期,依赖于freq_type 的值,1代表每天执行一次
   @active_start_time = 080000,--凌晨执行。235959
   @freq_recurrence_factor =52    --已计划执行的作业之间的周数或月数,当@freq_type计划为8/16/32，必须添加该因子，默认为0

   --@freq_type = 8,@freq_interval = 2,代表每周一执行1次。
--4 添加目标服务器
EXEC msdb.dbo.sp_add_jobserver 
    @job_name = @jobname ,  
    @server_name = 'SONG-PC' 

--5 开始作业
exec msdb.dbo.sp_start_job 
    @job_name = @jobname--,@server_name='(local)'

 注：关于作业操作相关参数，可参考博文http://blog.csdn.net/gaoyunpeng/article/details/1534510