SQL Server DBA维护常用语句

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 --指定多少秒后回滚事务 

--只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库 
alter database wc 
set restricted_user  
with rollback immediate        --立即回滚事务 

--多用户模式
alter database wc 
set multi_user 
with no_wait       --不等待立即改变，如不能立即完成，那么会导致执行错误

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

--添加文件组
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
)

3、移动文件

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

alter database wc
set offline
go

--修改文件名称
alter database wc
modify file
(
  name = wc_fg8,
  filename = 'd:\wc\wc_fg8.ndf'
)
go

--把原来的文件复制到新的位置：'d:\wc\wc_fg8.ndf'

--设置数据库在线
alter database wc
set online

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

--设置默认文件组
alter database wc
modify filegroup wc_fg8 default

--设为只读文件组
--如果文件已经是某个属性，不能再次设置相同属性
alter database wc
modify filegroup wc_fg8 read_write
5、收缩数据库、 收缩文件
--收缩数据库 
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 --清空文件，清空文件后，才可以删除文件 
                )

6、删除文件、 删除文件组

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

--删除数据后，必须要清空文件的内容
dbcc shrinkfile(wc_fg8,emptyfile)

--删除文件，同时也在文件系统底层删除了文件
alter database test
remove file wc_fg8

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

--最后删除文件组
alter database test
remove filegroup wc_fg8
7、重新组织索引

alter index [idx_temp_lock_id] on [dbo].[temp_lock]
reorganize
with ( lob_compaction = on )

8、重新生成索引

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 )

9、更新统计信息

--更新表中某个的统计信息 
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

10、执行sql server代理作业

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

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 )