SQL Server Hands-on Tips collection

server| Collection | tips

This includes 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
(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 Ystem\currentcontrolset\control Ession 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

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 ...

Method One

In general, the contraction of the SQL database does not greatly reduce the size of the database, its primary role is to shrink the log size, you should do this periodically to avoid the database log too large
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)--> and then right-click to select Properties--> Select option--> in the failover mode select "Simple" and then press OK to save
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
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 an important basis for restoring the database in some unusual cases.

Method Two

SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT


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)

--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)


DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR (255)
SELECT @StartTime = GETDATE (),
@TruncLog = ' BACKUP LOG ' + db_name () + ' with Truncate_only '

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 off

[1] [2] [3] [4] [5] [6] [7] Next page