SQL Server實用經驗技巧集 [一]

　（一）掛起操作

　　在安裝Sql或sp補丁的時候系統提示之前有掛起的安裝操作，要求重啟，這裡往往重啟無用，解決辦法：

　　到HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Control/Session Manager
　　刪除PendingFileRenameOperations

　　（二）收縮資料庫

 　　--重建索引
　　DBCC REINDEX
　　DBCC INDEXDEFRAG
　　--收縮資料和日誌
　　DBCC SHRINKDB
　　DBCC SHRINKFILE
 

　　（三）壓縮資料庫

 　　dbcc shrinkdatabase(dbname)
 

　　（四）轉移資料庫給新使用者以已存在使用者權限

 　　exec sp_change_users_login 'update_one','newname','oldname'
　　go
 

　　（五）檢查備份組

 　　RESTORE VERIFYONLY from disk='E:/dvbbs.bak'
 

　　（六）修複資料庫

 　　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 有3個參數:

　　--REPAIR_ALLOW_DATA_LOSS
 

　　--  執行由 REPAIR_REBUILD 完成的所有修複，包括對行和頁進行分配和取消分配以改正分配錯誤、結構行或頁的錯誤，以及刪除已損壞的文字物件。這些修複可能會導致一些資料丟失。修複操作可以在使用者事務下完成以允許使用者復原所做的更改。如果復原修複，則資料庫仍會含有錯誤，應該從備份進行恢複。如果由於所提供修複等級的緣故遺漏某個錯誤的修複，則將遺漏任何取決於該修複的修複。修複完成後，備份資料庫。

　　--REPAIR_FAST 進行小的、不耗時的修複操作，如修複非叢集索引中的附加鍵。這些修複可以很快完成，並且不會有遺失資料的危險。

　　--REPAIR_REBUILD 執行由 REPAIR_FAST 完成的所有修複，包括需要較長時間的修複（如重建索引）。執行這些修複時不會有遺失資料的危險。

 　　--DBCC CHECKDB('dvbbs') with NO_INFOMSGS,PHYSICAL_ONLY
 

　　SQL SERVER日誌清除的兩種方法
　　在使用過程中大家經常碰到資料庫日誌非常大的情況，在這裡介紹了兩種處理方法……

　　方法一

　　一般情況下，SQL資料庫的收縮並不能很大程度上減小資料庫大小，其主要作用是收縮日誌大小，應當定期進行此操作以免資料庫日誌過大

　　1、設定資料庫模式為簡單模式：開啟SQL企業管理器，在控制台根目錄中依次點開Microsoft SQL Server-->SQL Server組-->雙擊開啟你的伺服器-->雙擊開啟資料庫目錄-->選擇你的資料庫名稱（如論壇資料庫Forum）-->然後點擊右鍵選擇屬性-->選擇選項-->在故障還原的模式中選擇“簡單”，然後按確定儲存。

　　2、在當前資料庫上點右鍵，看所有任務中的收縮資料庫，一般裡面的預設設定不用調整，直接點確定。

　　3、收縮資料庫完成後，建議將您的資料庫屬性重新設定為標準模式，操作方法同第一點，因為日誌在一些異常情況下往往是恢複資料庫的重要依據

　　方法二

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

　　USE     tablename             -- 要操作的資料庫名
　　SELECT  @LogicalFileName = 'tablename_log',  -- 記錄檔名
　　@MaxMinutes = 10,               -- Limit on time allowed to wrap log.
　　@NewSize = 1                  -- 你想設定的記錄檔的大小(M)
 

 

 　　-- Setup / initialize
　　DECLARE @OriginalSize int
　　SELECT @OriginalSize = size
　　FROM sysfiles
　　WHERE name = @LogicalFileName
　　SELECT 'Original Size of ' + db_name() + ' LOG is ' +
　　CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
　　CONVERT(VARCHAR(30),(@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 of ' + db_name() + ' LOG is ' +
　　CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
　　CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
　　FROM sysfiles
　　WHERE name = @LogicalFileName
　　DROP TABLE DummyTrans
　　SET NOCOUNT OFF