sql-server数据库日常维护规范

2023年8月3日发(作者：)

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

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

 规则

Windows系统:

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

2. 数据库运行状态维护

 规则

1。 实例运行状态

使用SQL SERVER Management Studio能连接说明实例运行正常。

2. 磁盘空间使用情况

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

3。 查看错误日志:

方法一:可以通过执行该命令来查看错误日志信息:exec xp_readerrorlog

方法二：在SQL SERVER Management Studio中连接该sql server实例，object Explorer中查找Management—〉SQL Server logs—>右键选view-》选择你要看的日志信息（sql server log

or sql server and Windows log）

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

driver:SQL Server 2008 R2MSSQL10_ERVERMSSQLLogERRORLOG

4．跟踪事件

企业管理里SQL2005SQL2008在上方菜单 —工具—SQL Profiler，按照选择进行跟踪.

3. 数据库备份情况维护

 规则

数据库备份情况检查：

备份日志显示： 成功，说明备份成功。

备份日志显示：失败,说明备份失败，需要进一步查找失败原因。

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

4. 数据库对象的维护

 规则

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

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

select * from ［数据库名］.［dbo].［sysfiles]

转换文件大小单位为MB：

select name， convert（float，size) * (8192.0/1024。0)/1024。 from ［数据库名］.es

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

Exec sp_spaceused

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

DBCC SQLPERF（LOGSPACE)

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

create table tmp

(

name varchar（50）,

rowscount int,

reserved varchar(50）,

data varchar(50），

index_size varchar（50），

unused varchar（50)

）；

insert tmp（name, rowscount, reserved， data, index_size， unused)

exec sp_MSforeachtable @command1=”sp_spaceused ’？’”;

select * from tmp where name 〈〉 'tmp’ order by name

drop table tmp ；

或者

SELECT ， b。rows

FROM sysobjects AS a INNER JOIN

sysindexes AS b ON =

WHERE (a。type = ’u’） AND （b。indid IN （0, 1））

ORDER BY b。rows DESC

6、检查数据库完整性

dbcc checkdb(Portal）

dbcc checkdb(Portal) with tablock

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

—-数据库重命名

ALTER DATABASE WC

MODIFY NAME = test

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

alter database test

set recovery full

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

alter database test

set single_user

with rollback after 10 seconds —-指定多少秒后回滚事务

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

alter database wc

set restricted_user

with rollback immediate --立即回滚事务

-—多用户模式

alter database wc

set multi_user

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

8、扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称

--添加文件组

ALTER DATABASE test

ADD FILEGROUP WC_FG8 —-添加数据文件

ALTER DATABASE test

ADD FILE

（

NAME = WC_FG8，

FILENAME = 'D:WC_'，

SIZE = 1mb,

MAXSIZE = 10mb，

FILEGROWTH = 1mb

）

TO FILEGROUP WC_FG8

--添加日志文件

ALTER DATABASE test

ADD LOG FILE

（

NAME = WC_LOG3，

FILENAME = 'D：WC_FG3。LDF’,

SIZE = 1MB,

MAXSIZE = 10MB,

FILEGROWTH = 100KB

）

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

ALTER DATABASE test

MODIFY FILE

(

NAME = ’WC_FG8'，

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

MAXSIZE= 8MB,

FILEGROWTH = 10％

)

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

MODIFY FILE

（

NAME = WC_LOG3,

NEWNAME = WC_FG33

）

9、移动文件

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

checkpoint

go

ALTER DATABASE WC

SET OFFLINE

go

--修改文件名称

ALTER DATABASE WC

MODIFY FILE

(

NAME = WC_fg8,

FILENAME = ’D:WCWC_FG8。NDF’

)

go

——把原来的文件复制到新的位置：'D:WCWC_'

—-设置数据库在线

ALTER DATABASE WC

SET ONLINE

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

—-设置默认文件组 ALTER DATABASE WC

MODIFY FILEGROUP WC_FG8 DEFAULT

-—设为只读文件组

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

ALTER DATABASE WC

MODIFY FILEGROUP WC_FG8 READ_WRITE

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

-—收缩数据库

DBCC SHRINKDATABASE('test'， ——要收缩的数据库名称或数据库ID

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

）

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

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

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

)

DBCC SHRINKDATABASE（’test’， -—要收缩的数据库名称或数据库ID

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

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

)

--收缩文件

DBCC SHRINKFILE(wc_fg8, —-要收缩的数据文件逻辑名称

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

DBCC SHRINKFILE（wc_fg8， --要收缩的数据文件逻辑名称

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

)

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

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

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

DBCC SHRINKFILE（WC_FG8，EMPTYFILE）

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

ALTER DATABASE test

REMOVE FILE WC_FG8

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

-—最后删除文件组

ALTER DATABASE test

REMOVE FILEGROUP WC_FG8

13、重新组织索引

ALTER INDEX [idx_temp_lock_id］ ON ［dbo].［temp_lock]

REORGANIZE

WITH （ LOB_COMPACTION = ON ）

use test

go

select ’DBCC INDEXDEFRAG('+db_name（)+’，'+o。name+','+ + '）;’ --，db_name(）,

—-o。name，

--i。name,

--i。*

from sysindexes i

inner join sysobjects o

on = o。id

where o。xtype = 'U’

and i。indid >0

and charindex（’WA_Sys’，i。name） = 0

14、重新生成索引

ALTER INDEX ［idx_temp_lock_id] ON [dbo］.［temp_lock]

REBUILD PARTITION = ALL

WITH ( PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF,

ALLOW_ROW_LOCKS = ON，

ALLOW_PAGE_LOCKS = ON,

ONLINE = OFF，

SORT_IN_TEMPDB = OFF )

15、更新统计信息

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

update statistics temp_lock（_WA_Sys_00000001_07020F21)

update statistics temp_lock(_WA_Sys_00000001_07020F21）

with sample 50 percent

update statistics temp_lock(_WA_Sys_00000001_07020F21)

with resample， -—使用最近的采样速率更新每个统计信息

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

—-更新索引的统计信息

update statistics temp_lock(idx_temp_lock_id)

with fullscan

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

update statistics txt

with all

16、执行SQL Server代理作业

exec msdb。dbo。sp_start_job

@job_name =N’job_update_sql'；

2023年8月3日发(作者：)

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

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

 规则

Windows系统:

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

2. 数据库运行状态维护

 规则

1。 实例运行状态

使用SQL SERVER Management Studio能连接说明实例运行正常。

2. 磁盘空间使用情况

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

3。 查看错误日志:

方法一:可以通过执行该命令来查看错误日志信息:exec xp_readerrorlog

方法二：在SQL SERVER Management Studio中连接该sql server实例，object Explorer中查找Management—〉SQL Server logs—>右键选view-》选择你要看的日志信息（sql server log

or sql server and Windows log）

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

driver:SQL Server 2008 R2MSSQL10_ERVERMSSQLLogERRORLOG

4．跟踪事件

企业管理里SQL2005SQL2008在上方菜单 —工具—SQL Profiler，按照选择进行跟踪.

3. 数据库备份情况维护

 规则

数据库备份情况检查：

备份日志显示： 成功，说明备份成功。

备份日志显示：失败,说明备份失败，需要进一步查找失败原因。

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

4. 数据库对象的维护

 规则

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

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

select * from ［数据库名］.［dbo].［sysfiles]

转换文件大小单位为MB：

select name， convert（float，size) * (8192.0/1024。0)/1024。 from ［数据库名］.es

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

Exec sp_spaceused

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

DBCC SQLPERF（LOGSPACE)

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

create table tmp

(

name varchar（50）,

rowscount int,

reserved varchar(50）,

data varchar(50），

index_size varchar（50），

unused varchar（50)

）；

insert tmp（name, rowscount, reserved， data, index_size， unused)

exec sp_MSforeachtable @command1=”sp_spaceused ’？’”;

select * from tmp where name 〈〉 'tmp’ order by name

drop table tmp ；

或者

SELECT ， b。rows

FROM sysobjects AS a INNER JOIN

sysindexes AS b ON =

WHERE (a。type = ’u’） AND （b。indid IN （0, 1））

ORDER BY b。rows DESC

6、检查数据库完整性

dbcc checkdb(Portal）

dbcc checkdb(Portal) with tablock

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

—-数据库重命名

ALTER DATABASE WC

MODIFY NAME = test

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

alter database test

set recovery full

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

alter database test

set single_user

with rollback after 10 seconds —-指定多少秒后回滚事务

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

alter database wc

set restricted_user

with rollback immediate --立即回滚事务

-—多用户模式

alter database wc

set multi_user

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

8、扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称

--添加文件组

ALTER DATABASE test

ADD FILEGROUP WC_FG8 —-添加数据文件

ALTER DATABASE test

ADD FILE

（

NAME = WC_FG8，

FILENAME = 'D:WC_'，

SIZE = 1mb,

MAXSIZE = 10mb，

FILEGROWTH = 1mb

）

TO FILEGROUP WC_FG8

--添加日志文件

ALTER DATABASE test

ADD LOG FILE

（

NAME = WC_LOG3，

FILENAME = 'D：WC_FG3。LDF’,

SIZE = 1MB,

MAXSIZE = 10MB,

FILEGROWTH = 100KB

）

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

ALTER DATABASE test

MODIFY FILE

(

NAME = ’WC_FG8'，

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

MAXSIZE= 8MB,

FILEGROWTH = 10％

)

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

MODIFY FILE

（

NAME = WC_LOG3,

NEWNAME = WC_FG33

）

9、移动文件

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

checkpoint

go

ALTER DATABASE WC

SET OFFLINE

go

--修改文件名称

ALTER DATABASE WC

MODIFY FILE

(

NAME = WC_fg8,

FILENAME = ’D:WCWC_FG8。NDF’

)

go

——把原来的文件复制到新的位置：'D:WCWC_'

—-设置数据库在线

ALTER DATABASE WC

SET ONLINE

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

—-设置默认文件组 ALTER DATABASE WC

MODIFY FILEGROUP WC_FG8 DEFAULT

-—设为只读文件组

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

ALTER DATABASE WC

MODIFY FILEGROUP WC_FG8 READ_WRITE

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

-—收缩数据库

DBCC SHRINKDATABASE('test'， ——要收缩的数据库名称或数据库ID

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

）

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

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

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

)

DBCC SHRINKDATABASE（’test’， -—要收缩的数据库名称或数据库ID

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

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

)

--收缩文件

DBCC SHRINKFILE(wc_fg8, —-要收缩的数据文件逻辑名称

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

DBCC SHRINKFILE（wc_fg8， --要收缩的数据文件逻辑名称

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

)

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

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

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

DBCC SHRINKFILE（WC_FG8，EMPTYFILE）

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

ALTER DATABASE test

REMOVE FILE WC_FG8

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

-—最后删除文件组

ALTER DATABASE test

REMOVE FILEGROUP WC_FG8

13、重新组织索引

ALTER INDEX [idx_temp_lock_id］ ON ［dbo].［temp_lock]

REORGANIZE

WITH （ LOB_COMPACTION = ON ）

use test

go

select ’DBCC INDEXDEFRAG('+db_name（)+’，'+o。name+','+ + '）;’ --，db_name(）,

—-o。name，

--i。name,

--i。*

from sysindexes i

inner join sysobjects o

on = o。id

where o。xtype = 'U’

and i。indid >0

and charindex（’WA_Sys’，i。name） = 0

14、重新生成索引

ALTER INDEX ［idx_temp_lock_id] ON [dbo］.［temp_lock]

REBUILD PARTITION = ALL

WITH ( PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF,

ALLOW_ROW_LOCKS = ON，

ALLOW_PAGE_LOCKS = ON,

ONLINE = OFF，

SORT_IN_TEMPDB = OFF )

15、更新统计信息

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

update statistics temp_lock（_WA_Sys_00000001_07020F21)

update statistics temp_lock(_WA_Sys_00000001_07020F21）

with sample 50 percent

update statistics temp_lock(_WA_Sys_00000001_07020F21)

with resample， -—使用最近的采样速率更新每个统计信息

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

—-更新索引的统计信息

update statistics temp_lock(idx_temp_lock_id)

with fullscan

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

update statistics txt

with all

16、执行SQL Server代理作业

exec msdb。dbo。sp_start_job

@job_name =N’job_update_sql'；