SQL Server database Practical Tips Collection

server| Collection | tips | data | Database includes installation prompts Hang UpOperations, shrink the database, compress the database, transfer the database to new users with existing user rights, check the backup set, repair the database, and so on.

(i) Suspend operation

When installing a SQL or SP patch, the system prompts for a pending installation operation, requiring a reboot, where it is often useless to restart, the solution:

To HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager

Delete PendingFileRenameOperations

(ii) Shrinking of the database

--Rebuilding indexes

DBCC REINDEX
DBCC Indexdefrag

--Shrink data and logs

DBCC SHRINKDB
DBCC Shrinkfile

(iii) Compressed database

DBCC SHRINKDATABASE (dbname)

(iv) Transfer of the database to new users with existing user rights

exec sp_change_users_login ' update_one ', ' newname ', ' oldname '
Go

(v) Checking backup sets

RESTORE verifyonly from disk= ' E:\dvbbs.bak '

(vi) repair of the database

ALTER DATABASE[Dvbbs] SET Single_user
Go
DBCC CHECKDB (' Dvbbs ', Repair_allow_ Data_loss) with TABLOCK
Go
ALTER DATABASE [Dvbbs] SET Multi_user
Go

--CHECKDB has 3 parameters:
--repair_allow_ DATA_loss

--Perform all repairs performed by repair_rebuild, including assigning and unassign rows and pages to correct assignment errors, structural rows or pages, and deleting corrupted text objects. These fixes can cause some data loss. A repair operation can be completed under a user transaction to allow the user to roll back the changes. If you roll back the repair, the database will still contain errors and should be recovered from the backup. If an incorrect fix is omitted due to the level of repair provided, any fixes that depend on the repair will be omitted. After the repair is complete, back up the database.

--repair_fast for small, time-consuming fixes, such as fixing additional keys in a nonclustered index. These fixes can be done quickly, and there is no risk of losing data.
--repair_rebuild performs all the repairs performed by Repair_fast, including the need for a longer period of repair, such as rebuilding an index. There is no risk of losing data when performing these repairs.

--DBCC CHECKDB (' Dvbbs ') with No_infomsgs,physical_only