【SQL Server DBA】日常维护语句

学习数据分析，可考取SQL Server的DBA证书 #生活技巧# #工作学习技巧# #技能培训认证#

数据库的日常维护，可以通过SQL Server中的维护计划来自动实现，非常方便。

不过，也不是所有的维护，都可以通过维护计划来实现，下面将通过代码来实现。

1、检查数据库完整性

dbcc checkdb(test)

通过加tablock提高速度

dbcc checkdb(test) with tablock

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

ALTER DATABASE WC

MODIFY NAME = test

alter database test

set recovery full

alter database test

set single_user

with rollback after 10 seconds

alter database wc

set restricted_user

with rollback immediate

alter database wc

set multi_user

with no_wait

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

ALTER DATABASE test

ADD FILEGROUP WC_FG8

ALTER DATABASE test

ADD FILE

(

NAME = WC_FG8,

FILENAME = 'D:\WC_FG8.ndf',

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

)

4、移动文件

checkpoint

go

ALTER DATABASE WC

SET OFFLINE

go

ALTER DATABASE WC

MODIFY FILE

(

NAME = WC_fg8,

FILENAME = 'D:\WC\WC_FG8.NDF'

)

go

ALTER DATABASE WC

SET ONLINE

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

ALTER DATABASE WC

MODIFY FILEGROUP WC_FG8 DEFAULT

ALTER DATABASE WC

MODIFY FILEGROUP WC_FG8 READ_WRITE

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

DBCC SHRINKDATABASE('test',

10

)

DBCC SHRINKDATABASE('test',

10,

NOTRUNCATE

)

DBCC SHRINKDATABASE('test',

10,

TRUNCATEONLY

)

DBCC SHRINKFILE(wc_fg8,

7

)

DBCC SHRINKFILE(wc_fg8,

EMPTYFILE

)

7、删除文件、删除文件组

DBCC SHRINKFILE(WC_FG8,EMPTYFILE)

ALTER DATABASE test

REMOVE FILE WC_FG8

ALTER DATABASE test

REMOVE FILEGROUP WC_FG8

8、重新组织索引

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+','+i.name + ');'

from sysindexes i

inner join sysobjects o

on i.id = o.id

where o.xtype = 'U'

and i.indid >0

and charindex('WA_Sys',i.name) = 0

9、重新生成索引

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 )

10、更新统计信息

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

11、执行SQL Server代理作业

exec msdb.dbo.sp_start_job

@job_name =N'job_update_sql';

12、备份数据库(完整、差异、日志备份)，这个在其他文章中已有详细描述，这里不再赘述。

网址：【SQL Server DBA】日常维护语句 https://www.yuejiaxmz.com/news/view/242124

相关内容

SQL Server数据库性能优化（一）之 优化SQL 语句
Python实现数据转移：SQL server ==＞ SQL server
SQL Server大表如何快速删除数据
SQL Server 2005基础与提升实训视频教程【共25课时】
SQL Server DBA十大必备工具使生活轻松
SQL insert into 语句的写法
PowerBuilder连接SQLServer失败 SQL State：‘28000’
[黑客技术]SQL注入的新技巧
windows cmd 执行sql文件ERROR 1064 (42000):You have an error in your SQL syn报语法错误解决办法
Internal Server Error

随便看看