SQL Server practical experience tips [i]

Server| Tips This article is a collection of SQL Server practical tips, including installation prompts for pending operations, shrinking the database, compressing the database, transferring the database to new users for existing user rights, checking backup sets, repairing databases, and so on. Grassland chin ai tp owerftgjy

(i) Suspend operation Grassland chin AI tp owerftgjy

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
Remove pendingfilerenameoperations grassland chin ai tp owerftgjy

(ii) Shrinking database grassland chin AI tp owerftgjy

--Rebuilding indexes DBCC REINDEX DBCC Indexdefrag --Shrink data and logs DBCC SHRINKDB DBCC shrinkfile grassland chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

(iii) Compressed database grassland Chin AI tp owerftgjy

DBCC SHRINKDATABASE (dbname) grassland chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

(iv) Transfer of databases to new users with existing user rights grassland chin AI tp owerftgjy

exec sp_change_users_login ' update_one ', ' newname ', ' oldname ' Go Meadow chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

(v) Check backup set grassland chin AI tp owerftgjy

RESTORE verifyonly from disk= ' E:\dvbbs.bak ' Meadow chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

(vi) Restoration of database grassland chin AI tp owerftgjy

ALTER DATABASE [Dvbbs] SET Single_user Go DBCC CHECKDB (' Dvbbs ', repair_allow_data_loss) with TABLOCK Go ALTER DATABASE [Dvbbs] SET Multi_user Go Meadow chin ai tp owerftgjy --CHECKDB has 3 parameters: grassland chin ai tp owerftgjy --repair_allow_data_loss grassland chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

--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. Grassland chin ai tp owerftgjy

--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. Grassland chin ai tp owerftgjy

--DBCC CHECKDB (' Dvbbs ') with no_infomsgs,physical_only grassland chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

Two methods of SQL Server log cleanup
In the use of the process, we often encounter a very large database log, here are two ways to deal with ... Grassland chin ai tp owerftgjy

Methods a meadow chin AI tp owerftgjy

In general, the contraction of the SQL database can not greatly reduce the size of the database, its main role is to shrink the log size, you should do this periodically to avoid database log too large grassland chin ai tp owerftgjy

1, set the database mode to Simple mode: Open SQL Enterprise Manager, in the console root in turn, click the Microsoft SQL Server-->sql Server group--> double hit Open your server--> double-click to open the database directory--> Select your database name (such as Forum Database Forum)--> then right-click to select Properties--> Select option--> in the failover mode select "Simple" and then press OK to save. Grassland chin ai tp owerftgjy

2, in the current database point right, look at all tasks in the shrinking database, the general inside the default settings do not need to adjust, direct point to determine. Grassland chin ai tp owerftgjy

3. After the database is finished, it is recommended that you reset your database properties to standard mode, with the 1th, because the log is often the important basis for restoring the database in some unusual situations. Grassland Chin ai tp owerftgjy

Methods two grassland chin ai tp owerftgjy

SET NOCOUNT on DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT grassland chin ai tp owerftgjy Use TableName-the name of the database to manipulate SELECT @LogicalFileName = ' Tablename_log ',--log file name @MaxMinutes =--Limit on time allowed to wrap log. @NewSize = 1-the size of the log file you want to set (M) grassland chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

Grassland Chin AI tp owerftgjy

--Setup/initialize DECLARE @OriginalSize int SELECT @OriginalSize = size From Sysfiles WHERE name = @LogicalFileName SELECT ' Original Size of ' + db_name () + ' LOG is ' + CONVERT (VARCHAR, @OriginalSize) + ' 8K pages or ' + CONVERT (VARCHAR (@OriginalSize *8/1024)) + ' MB ' From Sysfiles WHERE name = @LogicalFileName CREATE TABLE Dummytrans (Dummycolumn char (8000) NOT NULL) Grassland chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR (255) SELECT @StartTime = GETDATE (), @TruncLog = ' BACKUP LOG ' + db_name () + ' with truncate_only ' Meadow chin ai tp owerftgjy DBCC shrinkfile (@LogicalFileName, @NewSize) EXEC (@TruncLog) --Wrap the log if necessary. While @MaxMinutes > DATEDIFF (MI, @StartTime, GETDATE ())--time has not expired and @OriginalSize = (SELECT size from sysfiles WHERE name = @LogicalFileName) and (@OriginalSize * 8/1024) > @NewSize BEGIN--Outer loop. SELECT @Counter = 0 while (@Counter < @OriginalSize/16) and (@Counter < 50000)) BEGIN--Update INSERT Dummytrans VALUES (' Fill Log ') DELETE Dummytrans SELECT @Counter = @Counter + 1 End EXEC (@TruncLog) End SELECT ' Final Size ' + db_name () + ' LOG is ' + CONVERT (VARCHAR (), size) + ' 8K pages or ' + CONVERT (VARCHAR (), (size*8/1024)) + ' MB ' From Sysfiles WHERE name = @LogicalFileName DROP TABLE Dummytrans SET NOCOUNT offGrassland chin ai tp owerftgjy

Grassland chin ai tp owerftgjy

[To be continued]