SQL Server DBA maintains common statement _mssql

1, check the integrity of the database

Copy Code code as follows:

DBCC CHECKDB (TEST)
--increase speed by adding tablock
DBCC CHECKDB (test) with TABLOCK

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

Copy Code code as follows:

--Database renaming
ALTER DATABASE WC
MODIFY NAME = Test

--Set the database as a full recovery model
ALTER DATABASE Test
Set Recovery full

--Only one user is allowed to access the database
ALTER DATABASE Test
Set Single_user
With rollback after seconds--specify how many seconds to roll back the transaction

--Only members of the Sysadmin,dbcreator,db_owner role can access the database
ALTER DATABASE WC
Set Restricted_user
With rollback immediate--ROLLBACK TRANSACTION now

--Multi-user mode
ALTER DATABASE WC
Set Multi_user
With no_wait--does not wait for immediate change, such as failure to complete immediately, resulting in an execution error

2, expand the database: Increase the file group, add files, modify file size, modify the logical name of the file

Copy Code code as follows:

--Adding filegroups
ALTER DATABASE Test
ADD FILEGROUP Wc_fg8

--Adding data files
ALTER DATABASE Test
ADD FILE
(
NAME = Wc_fg8,
FILENAME = ' D:\WC_FG8.ndf ',
SIZE = 1MB,
MAXSIZE = 10MB,
FileGrowth = 1MB
)
To FILEGROUP Wc_fg8

--Adding log files
ALTER DATABASE Test
ADD LOG FILE
(
NAME = Wc_log3,
FILENAME = ' D:\WC_FG3. LDF ',
SIZE = 1MB,
MAXSIZE = 10MB,
FileGrowth = 100KB
)

--Modify data file size, growth size, maximum size
ALTER DATABASE Test
MODIFY FILE
(
NAME = ' Wc_fg8 ',
Size = 2MB,--must be greater than the previous size, otherwise the error
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, Mobile files

Copy Code code as follows:

--because filegroups and files are not offline in SQL Server
--so you have to set the entire database 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 up database online
ALTER DATABASE WC
SET ONLINE

4, set the default file group, read-only filegroups

Copy Code code as follows:

--Set the default file group
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 the database, shrink the file
--Shrinking the database
DBCC shrinkdatabase (' Test ',--database name or database ID to shrink
10--The percentage of space occupied in the database file after shrinking
)


DBCC shrinkdatabase (' Test ',--database name or database ID to shrink
10,--the percentage of free space in the database file after shrinking
Notruncate--when shrinking, free space is freed by data movement
)


DBCC shrinkdatabase (' Test ',--database name or database ID to shrink
10,--the percentage of space occupied in the database file after shrinking
Truncateonly--when shrinking, just release the free space on the tail of the file
)


--Shrink file
DBCC Shrinkfile (Wc_fg8,--The logical name of the data file to shrink
7-The target size to shrink, in megabytes
)

DBCC Shrinkfile (Wc_fg8,--The logical name of the data file to shrink
Emptyfile--Empty files, empty files before you can delete files
)

6, delete files, delete file groups

Copy Code code as follows:

-To delete a file, you must first delete the data on the file or move it to another file or filegroup

--After you delete data, you must empty the contents of the file
DBCC Shrinkfile (Wc_fg8,emptyfile)

--Deletes the file and also deletes the file at the bottom of the file system
ALTER DATABASE Test
REMOVE FILE Wc_fg8

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

--delete file group last
ALTER DATABASE Test
REMOVE FILEGROUP Wc_fg8
7. Re-organize the index

ALTER INDEX [idx_temp_lock_id] on [dbo]. [Temp_lock]
REORGANIZE
With (lob_compaction = ON)

8. Rebuild Index

Copy Code code 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 statistic Information

Copy Code code as follows:

--Update one of the statistics in the table
UPDATE STATISTICS Temp_lock (_WA_SYS_00000001_07020F21)

UPDATE STATISTICS Temp_lock (_WA_SYS_00000001_07020F21)
With sample percent

UPDATE STATISTICS Temp_lock (_WA_SYS_00000001_07020F21)
With Resample,--Update each statistic with the most recent sample rate
NoRecompute--The query optimizer will complete this statistic update and disable future updates

--Update the statistics for the index
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 (integrity, Variance, log backup), this in other articles have been described in detail, here no longer repeat.

Copy Code code 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)