Common SQL Server DBA maintenance statements

1. Check database integrity
Copy codeThe Code is as follows: dbcc checkdb (test)
-- Increase the speed by adding tablock
Dbcc checkdb (test) with tablock
2. Rename the database, modify the recovery mode, and modify the user mode.
Copy codeThe Code is as follows:
-- Database rename
ALTER DATABASE WC
Modify name = test

-- Set the database to the 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 how many seconds to roll back the transaction

-- Only members of the sysadmin, dbcreator, and db_owner roles can access the database.
Alter database wc
Set restricted_user
With rollback immediate -- roll back 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, execution errors may occur.

2. extended database: Add a file group, add a file, modify the file size, and modify the logical name of the file
Copy codeThe Code is as follows:
-- Add a file group
Alter database test
Add filegroup WC_FG8

-- Add a data file
Alter database test
ADD FILE
(
NAME = WC_FG8,
FILENAME = 'd: \ WC_FG8.ndf ',
SIZE = 1 mb,
MAXSIZE = 10 mb,
FILEGROWTH = 1 mb
)
To filegroup WC_FG8

-- Add a log file
Alter database test
ADD LOG FILE
(
NAME = WC_LOG3,
FILENAME = 'd: \ WC_FG3.LDF ',
SIZE = 1 MB,
MAXSIZE = 10 MB,
FILEGROWTH = 100KB
)

-- Modify the size of the data file, increase the size, maximum size
Alter database test
MODIFY FILE
(
NAME = 'wc _ FG8 ',
SIZE = 2 MB, -- must be greater than the previous SIZE; otherwise, an error is returned.
MAXSIZE = 8 MB,
FILEGROWTH = 10%
)

-- Modify the logical name of a data file or log file
Alter database test
MODIFY FILE
(
NAME = WC_LOG3,
NEWNAME = WC_FG33
)
3. Move files
Copy codeThe Code is as follows: -- Because file groups and files in SQL Server cannot be offline
-- You must set the entire database to offline.
Checkpoint
Go

ALTER DATABASE WC
SET OFFLINE
Go

-- Modify the 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 the default file group and read-only file group
Copy codeThe Code is as follows:
-- Set the default file group
ALTER DATABASE WC
Modify filegroup WC_FG8 DEFAULT

-- Set as a read-only file group
-- If the file is already an attribute, you cannot set the same attribute again.
ALTER DATABASE WC
Modify filegroup WC_FG8 READ_WRITE
5. Shrink databases and files
-- Shrink Database
Dbcc shrinkdatabase ('test', -- name or ID of the database to be shrunk
10 -- percentage of space occupied by database files after shrinking
)


Dbcc shrinkdatabase ('test', -- name or ID of the database to be shrunk
10, -- percentage of idle space in database files after contraction
NOTRUNCATE-free space can be made by moving data during contraction
)


Dbcc shrinkdatabase ('test', -- name or ID of the database to be shrunk
10, -- percentage of space occupied by database files after shrinking
TRUNCATEONLY -- When shrinking, only the idle space at the end of the file is released
)


-- Shrink a file
Dbcc shrinkfile (wc_fg8, -- Logical name of the data file to be shrunk
7 -- target size to be reduced, in MB
)

Dbcc shrinkfile (wc_fg8, -- Logical name of the data file to be shrunk
EMPTYFILE -- clear the file. Only after the file is cleared can the file be deleted.
)

6. delete a file or a file group
Copy codeThe Code is as follows:
-- To delete a file, you must first Delete the data in the file or move it to another file or file group.

-- After deleting data, you must clear the File Content
Dbcc shrinkfile (WC_FG8, EMPTYFILE)

-- Delete the file and delete the file at the bottom of the file system.
Alter database test
Remove file WC_FG8

-- To delete a file group, you must first delete all files.

-- Delete the file group
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. regenerate the 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 the statistical information of a 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, -- use the latest sampling rate to update each statistical information
Norecompute -- the query optimizer updates the statistics and disables future updates.

-- Update index statistics
Update statistics temp_lock (idx_temp_lock_id)
With fullscan

-- Update all statistics of a table
Update statistics txt
With all
10. Execute an SQL Server proxy job

11. Back up the database (complete, differential, and log backup). This is already described in detail in other articles and will not be described 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)