SQL Server 日常巡检 1

 

1. dbcc checkdb(test)  

 

通过加tablock提高速度

 

[sql] view plain copy  

1. dbcc checkdb(test) with tablock  

 

 

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

 

[sql] view plain copy  

1. --数据库重命名    
2. ALTER DATABASE WC    
3. MODIFY NAME = test   
4.   
5.   
6. --设置数据库为完整恢复模式  
7. alter database test  
8. set recovery full  
9.   
10.   
11. --只允许一个用户访问数据库    
12. alter database test    
13. set single_user     
14. with rollback after 10 seconds --指定多少秒后回滚事务    
15.     
16.     
17. --只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库    
18. alter database wc    
19. set restricted_user     
20. with rollback immediate        --立即回滚事务    
21.     
22.     
23. --多用户模式   
24. alter database wc    
25. set multi_user    
26. with no_wait       --不等待立即改变，如不能立即完成，那么会导致执行错误    

 

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

 

[sql] view plain copy  

1. --添加文件组  
2. ALTER DATABASE test  
3. ADD FILEGROUP WC_FG8  
4.   
5.   
6. --添加数据文件  
7. ALTER DATABASE test  
8. ADD FILE  
9. (  
10.     NAME = WC_FG8,  
11.     FILENAME = ‘D:\WC_FG8.ndf‘,  
12.     SIZE = 1mb,  
13.     MAXSIZE = 10mb,  
14.     FILEGROWTH = 1mb  
15. )  
16. TO FILEGROUP WC_FG8  
17.   
18.   
19. --添加日志文件  
20. ALTER DATABASE test  
21. ADD LOG FILE  
22. (  
23.     NAME = WC_LOG3,  
24.     FILENAME = ‘D:\WC_FG3.LDF‘,  
25.     SIZE = 1MB,  
26.     MAXSIZE = 10MB,  
27.     FILEGROWTH = 100KB  
28. )  
29.   
30.   
31. --修改数据文件的大小,增长大小,最大大小  
32. ALTER DATABASE test  
33. MODIFY FILE  
34. (  
35.     NAME = ‘WC_FG8‘,  
36.     SIZE = 2MB,      --必须大于之前的大小,否则报错  
37.     MAXSIZE= 8MB,  
38.     FILEGROWTH = 10%  
39. )  
40.   
41.   
42. --修改数据文件或日志文件的逻辑名称  
43. ALTER DATABASE test  
44. MODIFY FILE  
45. (  
46.     NAME = WC_LOG3,  
47.     NEWNAME = WC_FG33  
48. )  

4、移动文件

 

 

[sql] view plain copy  

1. --由于在SQL Server中文件组、文件不能离线  
2. --所以必须把整个数据库设置为离线  
3. checkpoint  
4. go  
5.   
6. ALTER DATABASE WC  
7. SET OFFLINE  
8. go  
9.   
10.   
11. --修改文件名称  
12. ALTER DATABASE WC  
13. MODIFY FILE  
14. (  
15.     NAME = WC_fg8,  
16.     FILENAME = ‘D:\WC\WC_FG8.NDF‘  
17. )  
18. go  
19.   
20.   
21. --把原来的文件复制到新的位置：‘D:\WC\WC_FG8.NDF‘  
22.   
23.   
24. --设置数据库在线  
25. ALTER DATABASE WC  
26. SET ONLINE  

 

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

[sql] view plain copy  

1. --设置默认文件组  
2. ALTER DATABASE WC  
3. MODIFY FILEGROUP WC_FG8 DEFAULT  
4.   
5.   
6. --设为只读文件组  
7. --如果文件已经是某个属性，不能再次设置相同属性  
8. ALTER DATABASE WC  
9. MODIFY FILEGROUP WC_FG8 READ_WRITE  

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

 

[sql] view plain copy  

1. --收缩数据库    
2. DBCC SHRINKDATABASE(‘test‘,    --要收缩的数据库名称或数据库ID    
3.                     10         --收缩后，数据库文件中空间空间占用的百分比    
4.                     )    
5.     
6.     
7. DBCC SHRINKDATABASE(‘test‘,    --要收缩的数据库名称或数据库ID    
8.                     10,        --收缩后，数据库文件中空闲空间占用的百分比    
9.                     NOTRUNCATE --在收缩时，通过数据移动来腾出自由空间    
10.                     )    
11.                  
12.                         
13. DBCC SHRINKDATABASE(‘test‘,      --要收缩的数据库名称或数据库ID    
14.                     10,          --收缩后，数据库文件中空间空间占用的百分比    
15.                     TRUNCATEONLY --在收缩时，只是把文件尾部的空闲空间释放    
16.                     )    
17.                         
18.     
19. --收缩文件    
20. DBCC SHRINKFILE(wc_fg8,   --要收缩的数据文件逻辑名称    
21.                 7         --要收缩的目标大小，以MB为单位    
22.                 )    
23.                     
24. DBCC SHRINKFILE(wc_fg8,   --要收缩的数据文件逻辑名称    
25.                 EMPTYFILE --清空文件，清空文件后，才可以删除文件    
26.                 )   

 

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

 

[sql] view plain copy  

1. --要删除文件，必须要先把文件上的数据删除，或者移动到其他文件或文件组上  
2. --可以清空文件的内容  
3. DBCC SHRINKFILE(WC_FG8,EMPTYFILE)  
4.   
5.   
6. --删除文件，同时也在文件系统底层删除了文件  
7. ALTER DATABASE test  
8. REMOVE FILE WC_FG8  
9.   
10.   
11. --要删除文件组，必须先删除所有文件  
12.   
13.   
14. --最后删除文件组  
15. ALTER DATABASE test  
16. REMOVE FILEGROUP WC_FG8  
17.   
18. /*  
19. drop database www  
20. go  
21.   
22. create database www  
23. on primary  
24. (  
25. name = ‘www_data01‘,  
26. filename = ‘c:\www_data01.mdf‘  
27. ),  
28. (  
29. name = ‘www_data02‘,  
30. filename =‘c:\www_data02.ndf‘  
31. )  
32.   
33. log on  
34. (  
35. name = ‘www_log‘,  
36. filename = ‘c:\www_log.ldf‘  
37. )  
38. go  
39.   
40. use www  
41. go  
42.   
43. create table a(id int ,v varchar(10)) on [primary]  
44. go  
45.   
46. insert into a  
47. select OBJECT_ID,left(name,10) from sys.objects   
48. go  
49.   
50.   
51. insert into a  
52. select * from a  
53. go 10  
54.   
55.   
56. DBCC SHRINKFILE(www_data02,EMPTYFILE)   
57. go  
58.   
59. ALTER DATABASE www    
60. REMOVE FILE www_data02   
61. */  

 

 

8、重新组织索引

 

[sql] view plain copy  

1. ALTER INDEX [idx_temp_lock_id] ON [dbo].[temp_lock]   
2. REORGANIZE   
3. WITH ( LOB_COMPACTION = ON )  

 

批量生成重组索引的语句

 

[sql] view plain copy  

1. use test  
2. go  
3.   
4. select ‘DBCC INDEXDEFRAG(‘+db_name()+‘,‘+o.name+‘,‘+i.name + ‘);‘  
5.         --,db_name(),  
6.         --o.name,  
7.         --i.name,  
8.         --i.*  
9.   
10. from sysindexes i  
11. inner join sysobjects o  
12.         on i.id = o.id  
13. where o.xtype = ‘U‘  
14.       and i.indid >0  
15.       and charindex(‘WA_Sys‘,i.name) = 0  

 

 

9、重新生成索引

 

[sql] view plain copy  

1. ALTER INDEX [idx_temp_lock_id] ON [dbo].[temp_lock]   
2. REBUILD PARTITION = ALL   
3. WITH ( PAD_INDEX  = OFF,   
4.        STATISTICS_NORECOMPUTE  = OFF,   
5.        ALLOW_ROW_LOCKS  = ON,   
6.        ALLOW_PAGE_LOCKS  = ON,   
7.        ONLINE = OFF,   
8.        SORT_IN_TEMPDB = OFF )  

 

 

10、更新统计信息

 

[sql] view plain copy  

1. --更新表中某个的统计信息    
2. update statistics temp_lock(_WA_Sys_00000001_07020F21)    
3.     
4.     
5. update statistics temp_lock(_WA_Sys_00000001_07020F21)    
6. with sample 50 percent    
7.     
8.     
9. update statistics temp_lock(_WA_Sys_00000001_07020F21)    
10. with resample,    --使用最近的采样速率更新每个统计信息    
11.      norecompute  --查询优化器将完成此统计信息更新并禁用将来的更新    
12.     
13.     
14.     
15. --更新索引的统计信息    
16. update statistics temp_lock(idx_temp_lock_id)    
17. with fullscan            
18.     
19.     
20. --更新表的所有统计信息    
21. update statistics txt    
22. with all     

 

 

11、执行SQL Server代理作业

 

[sql] view plain copy  

1. exec msdb.dbo.sp_start_job   
2.     @job_name =N‘job_update_sql‘;  

 

 

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

SQL Server 日常巡检 1

标签：max   文件组   恢复   默认   size   相同属性   alt   toc   csdn