1、数据库压缩日志
GO
ALTER DATABASE website
SET RECOVERY SIMPLE;--设置简单恢复模式
GO
DBCC SHRINKFILE (website_Log, 1);
GO
ALTER DATABASE website
SET RECOVERY FULL;--恢复为原模式
GO
2、重建所有索引
exec
sp_msforeachtable
'
DBCC DBREINDEX(
''
?
''
)
'
重建索引
DECLARE
@name
varchar
(
100
)
DECLARE
authors_cursor
CURSOR
FOR
Select
[
name
]
from
sysobjects
where
xtype
=
'
u
'
order
by
id
OPEN
authors_cursor
FETCH
NEXT
FROM
authors_cursor
INTO
@name
WHILE
@@FETCH_STATUS
=
0
BEGIN
DBCC
DBREINDEX (
@name
,
''
,
90
)
PRINT
'
当前重建索引表：
'
+
@name
FETCH
NEXT
FROM
authors_cursor
INTO
@name
END
deallocate
authors_cursor
3、定时作业
--
每月执行的作业
exec
p_createjob
@jobname
=
'
mm
'
,
@sql
=
'
select * from syscolumns
'
,
@freqtype
=
'
month
'
--
每周执行的作业
exec
p_createjob
@jobname
=
'
ww
'
,
@sql
=
'
select * from syscolumns
'
,
@freqtype
=
'
week
'
--
每日执行的作业
exec
p_createjob
@jobname
=
'
a
'
,
@sql
=
'
select * from syscolumns
'
--
每日执行的作业,每天隔4小时重复的作业
exec
p_createjob
@jobname
=
'
b
'
,
@sql
=
'
select * from syscolumns
'
,
@fsinterval
=
4
--
*/
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[p_createjob]
'
)
and
OBJECTPROPERTY
(id, N
'
IsProcedure
'
)
=
1
)
drop
procedure
[
dbo
]
.
[
p_createjob
]
GO
create
proc
p_createjob
@jobname
varchar
(
100
),
--
作业名称
@sql
varchar
(
8000
),
--
要执行的命令
@dbname
sysname
=
''
,
--
默认为当前的数据库名
@freqtype
varchar
(
6
)
=
'
day
'
,
--
时间周期,month 月,week 周,day 日
@fsinterval
int
=
1
,
--
相对于每日的重复次数
@time
int
=
170000
--
开始执行时间,对于重复执行的作业,将从0点到23:59分
as
if
isnull
(
@dbname
,
''
)
=
''
set
@dbname
=
db_name
()
--
创建作业
exec
msdb..sp_add_job
@job_name
=
@jobname
--
创建作业步骤
exec
msdb..sp_add_jobstep
@job_name
=
@jobname
,
@step_name
=
'
数据处理
'
,
@subsystem
=
'
TSQL
'
,
@database_name
=
@dbname
,
@command
=
@sql
,
@retry_attempts
=
5
,
--
重试次数
@retry_interval
=
5
--
重试间隔
--
创建调度
declare
@ftype
int
,
@fstype
int
,
@ffactor
int
select
@ftype
=
case
@freqtype
when
'
day
'
then
4
when
'
week
'
then
8
when
'
month
'
then
16
end
,
@fstype
=
case
@fsinterval
when
1
then
0
else
8
end
if
@fsinterval
<>
1
set
@time
=
0
set
@ffactor
=
case
@freqtype
when
'
day
'
then
0
else
1
end
EXEC
msdb..sp_add_jobschedule
@job_name
=
@jobname
,
@name
=
'
时间安排
'
,
@freq_type
=
@ftype
,
--
每天,8 每周,16 每月
@freq_interval
=
1
,
--
重复执行次数
@freq_subday_type
=
@fstype
,
--
是否重复执行
@freq_subday_interval
=
@fsinterval
,
--
重复周期
@freq_recurrence_factor
=
@ffactor
,
@active_start_time
=
@time
--
下午17:00:00分执行
--
添加目标服务器
EXEC
msdb.dbo.sp_add_jobserver
@job_name
=
@jobname
,
@server_name
=
N
'
(local)
'
go
