SQL Server DBA maintenance common statements

Time:2021-12-29

1. Check database integrity

Copy codeThe code is as follows:
dbcc checkdb(test)
–Speed up by adding tablock
dbcc checkdb(test) with tablock

2. Rename database, modify recovery mode, modify user mode

Copy codeThe code is as follows:
–Database rename
ALTER DATABASE WC 
MODIFY NAME = test

–Set database to full recovery mode
alter database test
set recovery full

–Only one user is allowed to access the database
alter database test 
set single_user  
With rollback after 10 seconds — specifies the number of seconds to rollback the transaction

–Only sysadmin, dbcreator, DB_ Members of the owner role can access the database
alter database wc 
set restricted_user  
With rollback immediate — rollback the transaction immediately

–Multi user mode
alter database wc 
set multi_user 
with no_ Wait — do not wait for immediate changes. If it cannot be completed immediately, it will lead to execution errors

2. Extended database: add file groups, add files, modify file sizes, and modify logical names of files

Copy codeThe code is as follows:
–Add filegroup
ALTER DATABASE test
ADD FILEGROUP WC_FG8

–Add data file
ALTER DATABASE test
ADD FILE
(
  NAME = WC_FG8,
  FILENAME = ‘D:\WC_FG8.ndf’,
  SIZE = 1mb,
  MAXSIZE = 10mb,
  FILEGROWTH = 1mb
)
TO FILEGROUP WC_FG8

–Add log file
ALTER DATABASE test
ADD LOG FILE
(
  NAME = WC_LOG3,
  FILENAME = ‘D:\WC_FG3.LDF’,
  SIZE = 1MB,
  MAXSIZE = 10MB,
  FILEGROWTH = 100KB
)

–Modify the size, growth size and maximum size of the data file
ALTER DATABASE test
MODIFY FILE
(
  NAME = ‘WC_FG8’,
Size = 2MB, — must be greater than the previous size, otherwise an error will be reported
  MAXSIZE= 8MB,
  FILEGROWTH = 10%
)

–Modify the logical name of the data file or log file
ALTER DATABASE test
MODIFY FILE
(
  NAME = WC_LOG3,
  NEWNAME = WC_FG33
)

3. Move file

Copy codeThe code is as follows:
–Because filegroups and files cannot be offline in SQL server
–Therefore, the entire database must be set offline
checkpoint
go

ALTER DATABASE WC
SET OFFLINE
go

–Modify file name
ALTER DATABASE WC
MODIFY FILE
(
  NAME = WC_fg8,
  FILENAME = ‘D:\WC\WC_FG8.NDF’
)
go

–Copy the original file to the new location:’d: \ WC \ WC_ FG8. NDF’

–Set database online
ALTER DATABASE WC
SET ONLINE

4. Set default filegroup, read-only filegroup

Copy codeThe code is as follows:
–Set default filegroup
ALTER DATABASE WC
MODIFY FILEGROUP WC_FG8 DEFAULT

–Set as read-only filegroup
–If the file is already a property, you cannot set the same property again
ALTER DATABASE WC
MODIFY FILEGROUP WC_FG8 READ_WRITE
5. Shrink database, shrink file
–Shrink database
DBCC shrinkdatabase (‘test ‘, — database name or database ID to shrink
10 — percentage of space occupied in database file after shrinkage
                    ) 

 
DBCC shrinkdatabase (‘test ‘, — database name or database ID to shrink
10, — percentage of free space in the database file after shrinking
Notruncate — when shrinking, free space is made by moving data
                    ) 

                     
DBCC shrinkdatabase (‘test ‘, — database name or database ID to shrink
10, — percentage of space occupied in the database file after shrinking
Truncateonly — when shrinking, only the free space at the end of the file is released
                    ) 

 
–Shrink file
DBCC ShrinkFile (wc#u fg8, — logical name of data file to shrink
7 — target size to shrink, in MB
                ) 

DBCC ShrinkFile (wc#u fg8, — logical name of data file to shrink
Emptyfile — empty the file. The file can be deleted only after the file is empty
                )

6. Delete file, delete filegroup

Copy codeThe code is as follows:
–To delete a file, you must first delete the data on the file or move it to another file or filegroup

–After deleting data, you must empty the contents of the file
DBCC SHRINKFILE(WC_FG8,EMPTYFILE)

–Deleting files also deletes files at the bottom of the file system
ALTER DATABASE test
REMOVE FILE WC_FG8

–To delete a filegroup, you must first delete all files

–Last delete filegroup
ALTER DATABASE test
REMOVE FILEGROUP WC_FG8
7. Reorganize index

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

8. Rebuild index

Copy codeThe code is as follows:
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

Copy codeThe code is as follows:
–Update statistics for a in the table
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, — update each statistic with the most recent sampling rate
Norcompute — the query optimizer will complete this statistics update and disable future updates

–Update index statistics
update statistics temp_lock(idx_temp_lock_id) 
with fullscan         

–Update all statistics for the table
update statistics txt 
with all

10. Execute SQL server agent job

11. Backup database (complete, differential and log backup), which has been described in detail in other articles, will not be repeated here.

Copy codeThe code is as follows:
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 )