sqlserver数据库日常维护规范方案.docx

sqlserver数据库日常维护规范方案.docx

《sqlserver数据库日常维护规范方案.docx》由会员分享，可在线阅读，更多相关《sqlserver数据库日常维护规范方案.docx（10页珍藏版）》请在冰豆网上搜索。

sqlserver数据库日常维护规范方案

1.数据库服务器负载情况维护

查看CPU，IO，内存使用情况

●规则

Windows系统:

打开任务管理器，选择进程选项卡，可以查看CPU、内存和IO使用率。

2.数据库运行状态维护

●规则

1.实例运行状态

使用SQLSERVERManagementStudio能连接说明实例运行正常。

2.磁盘空间使用情况

直接登陆服务器进行磁盘使用率检查

3.查看错误日志:

方法一：

可以通过执行该命令来查看错误日志信息：

execxp_readerrorlog

方法二：

在SQLSERVERManagementStudio中连接该sqlserver实例，objectExplorer中查找Management->SQLServerlogs->右键选view-》选择你要看的日志信息（sqlserverlogorsqlserverandWindowslog）

方法三：

去安装路径下去找你的log文件，我的默认实例路径如下

driver:

\SQLServer2008R2\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

4．跟踪事件

企业管理里SQL2005\SQL2008在上方菜单—工具—SQLProfiler，按照选择进行跟踪。

3.数据库备份情况维护

●规则

数据库备份情况检查：

备份日志显示:

成功,说明备份成功。

备份日志显示：

失败,说明备份失败，需要进一步查找失败原因。

每天检查异地备份是否成功。

4.数据库对象的维护

●规则

1、查询各个磁盘分区的剩余空间：

Execmaster.dbo.xp_fixeddrives

2、查询数据库的数据文件及日志文件的相关信息（包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等）

select*from[数据库名].[dbo].[sysfiles]

转换文件大小单位为MB：

selectname,convert（float,size）*（8192.0/1024.0）/1024.from[数据库名].dbo.sysfiles

3、查询当前数据库的磁盘使用情况：

Execsp_spaceused

４、查询数据库服务器各数据库日志文件的大小及利用率

DBCCSQLPERF（LOGSPACE）

5、统计数据库中每张表的大小

createtabletmp

（

namevarchar（50）,

rowscountint,

reservedvarchar（50）,

datavarchar（50）,

index_sizevarchar（50）,

unusedvarchar（50）

）;

inserttmp（name,rowscount,reserved,data,index_size,unused）

execsp_MSforeachtable@command1="sp_spaceused'?

'";

select*fromtmpwherename<>'tmp'orderbyname

droptabletmp;

或者

SELECTa.name,b.rows

FROMsysobjectsASaINNERJOIN

sysindexesASbONa.id=b.id

WHERE（a.type='u'）AND（b.indidIN（0,1））

ORDERBYb.rowsDESC

6、检查数据库完整性　　

dbcccheckdb（Portal）

dbcccheckdb（Portal）withtablock

7、数据库重命名、修改恢复模式、修改用户模式

--数据库重命名

ALTERDATABASEWC

MODIFYNAME=test

--设置数据库为完整恢复模式

alterdatabasetest

setrecoveryfull

--只允许一个用户访问数据库

alterdatabasetest

setsingle_user

withrollbackafter10seconds--指定多少秒后回滚事务

--只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库

alterdatabasewc

setrestricted_user

withrollbackimmediate--立即回滚事务

--多用户模式

alterdatabasewc

setmulti_user

withno_wait--不等待立即改变，如不能立即完成，那么会导致执行错误

8、扩展数据库：

增加文件组、增加文件、修改文件大小、修改文件的逻辑名称

--添加文件组

ALTERDATABASEtest

ADDFILEGROUPWC_FG8

--添加数据文件

ALTERDATABASEtest

ADDFILE

（

NAME=WC_FG8,

FILENAME='D:

\WC_FG8.ndf',

SIZE=1mb,

MAXSIZE=10mb,

FILEGROWTH=1mb

）

TOFILEGROUPWC_FG8

--添加日志文件

ALTERDATABASEtest

ADDLOGFILE

（

NAME=WC_LOG3,

FILENAME='D:

\WC_FG3.LDF',

SIZE=1MB,

MAXSIZE=10MB,

FILEGROWTH=100KB

）

--修改数据文件的大小,增长大小,最大大小

ALTERDATABASEtest

MODIFYFILE

（

NAME='WC_FG8',

SIZE=2MB,--必须大于之前的大小,否则报错

MAXSIZE=8MB,

FILEGROWTH=10%

）

--修改数据文件或日志文件的逻辑名称

ALTERDATABASEtest

MODIFYFILE

（

NAME=WC_LOG3,

NEWNAME=WC_FG33

）

9、移动文件

--由于在SQLServer中文件组、文件不能离线,所以必须把整个数据库设置为离线

checkpoint

go

ALTERDATABASEWC

SETOFFLINE

go

--修改文件名称

ALTERDATABASEWC

MODIFYFILE

（

NAME=WC_fg8,

FILENAME='D:

\WC\WC_FG8.NDF'

）

go

--把原来的文件复制到新的位置：

'D:

\WC\WC_FG8.NDF'

--设置数据库在线

ALTERDATABASEWC

SETONLINE

10、设置默认文件组、只读文件组

--设置默认文件组

ALTERDATABASEWC

MODIFYFILEGROUPWC_FG8DEFAULT

--设为只读文件组

--如果文件已经是某个属性，不能再次设置相同属性

ALTERDATABASEWC

MODIFYFILEGROUPWC_FG8READ_WRITE

11、收缩数据库、收缩文件

--收缩数据库

DBCCSHRINKDATABASE（'test',--要收缩的数据库名称或数据库ID

10--收缩后，数据库文件中空间空间占用的百分比

）

DBCCSHRINKDATABASE（'test',--要收缩的数据库名称或数据库ID

10,--收缩后，数据库文件中空闲空间占用的百分比

NOTRUNCATE--在收缩时，通过数据移动来腾出自由空间

）

DBCCSHRINKDATABASE（'test',--要收缩的数据库名称或数据库ID

10,--收缩后，数据库文件中空间空间占用的百分比

TRUNCATEONLY--在收缩时，只是把文件尾部的空闲空间释放

）

--收缩文件

DBCCSHRINKFILE（wc_fg8,--要收缩的数据文件逻辑名称

7--要收缩的目标大小，以MB为单位

）

DBCCSHRINKFILE（wc_fg8,--要收缩的数据文件逻辑名称

EMPTYFILE--清空文件，清空文件后，才可以删除文件

）

12、删除文件、删除文件组

　1.要删除文件，必须要先把文件上的数据删除，或者移动到其他文件或文件组上

--删除数据后，必须要清空文件的内容

DBCCSHRINKFILE（WC_FG8,EMPTYFILE）

--删除文件，同时也在文件系统底层删除了文件

ALTERDATABASEtest

REMOVEFILEWC_FG8

　2.要删除文件组，必须先删除所有文件

--最后删除文件组

ALTERDATABASEtest

REMOVEFILEGROUPWC_FG8

13、重新组织索引

ALTERINDEX[idx_temp_lock_id]ON[dbo].[temp_lock]

REORGANIZE

WITH（LOB_COMPACTION=ON）

usetest

go

select'DBCCINDEXDEFRAG（'+db_name（）+','+o.name+','+i.name+'）;'

--,db_name（）,

--o.name,

--i.name,

--i.*

fromsysindexesi

innerjoinsysobjectso

oni.id=o.id

whereo.xtype='U'

andi.indid>0

andcharindex（'WA_Sys',i.name）=0

14、重新生成索引

ALTERINDEX[idx_temp_lock_id]ON[dbo].[temp_lock]

REBUILDPARTITION=ALL

WITH（PAD_INDEX=OFF,

STATISTICS_NORECOMPUTE=OFF,

ALLOW_ROW_LOCKS=ON,

ALLOW_PAGE_LOCKS=ON,

ONLINE=OFF,

SORT_IN_TEMPDB=OFF）

15、更新统计信息

--更新表中某个的统计信息

updatestatisticstemp_lock（_WA_Sys_00000001_07020F21）

updatestatisticstemp_lock（_WA_Sys_00000001_07020F21）

withsample50percent

updatestatisticstemp_lock（_WA_Sys_00000001_07020F21）

withresample,--使用最近的采样速率更新每个统计信息

norecompute--查询优化器将完成此统计信息更新并禁用将来的更新

--更新索引的统计信息

updatestatisticstemp_lock（idx_temp_lock_id）

withfullscan

--更新表的所有统计信息

updatestatisticstxt

withall

16、执行SQLServer代理作业

execmsdb.dbo.sp_start_job

@job_name=N'job_update_sql';