在 SQL 2008 可以用以下的 SQL

DECLARE @DBName VARCHAR(64) = 'CHIComp01'

 

DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

 

INSERT INTO @ErrorLog

EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName

 

SELECT TOP 5

     [LogDate]

    ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete

    ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining

    ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining

    ,[TEXT]

 

FROM @ErrorLog ORDER BY [LogDate] DESC

在 SQL 2012 可以用以下的 SQL:

DECLARE @DBName VARCHAR(64) = 'databasename'



DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))



INSERT INTO @ErrorLog

EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName



SELECT TOP 5

[LogDate]

,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete

,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining

,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining

,[TEXT]



FROM @ErrorLog ORDER BY [LogDate] DESC

結果如下:
 


資料來源:
http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/
 

1. ＂有疑問＂的 資料庫的修復:

ALTER DATABASE MyDB SET EMERGENCY;
DBCC checkdb('MyDB');
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckDB ('MyDB', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE MyDB SET MULTI_USER;

其中 "DBCC CheckDB ('MyDB', REPAIR_ALLOW_DATA_LOSS)" 可能要多執行幾次。

2. Restore Differential Backup 用的 SQL:
​
RESTORE DATABASE [MyDB] FROM  DISK = N'D:\BACKUP_DB\MyDB_backup.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10,
MOVE 'MyDB_Data' TO 'D:\SQLDB2008\MyDB.mdf', 
MOVE 'MyDB_Log' TO 'D:\SQLDB2008\MyDB.ldf'

RESTORE DATABASE [MyDB] FROM  DISK = N'D:\BACKUP_DIFF\MyDB_backup_2013_02_25_12.bak' WITH  FILE = 1,  NOUNLOAD, NORECOVERY,  STATS = 10

RESTORE DATABASE [MyDB] WITH RECOVERY (正在還原的資料庫也可以用這個指令來恢復成可使用的狀態)

declare @spid int,@bl int

DECLARE s_cur CURSOR FOR

select 0 ,blocked

from (select * from sysprocesses where blocked>0 ) a

where not exists(select * from (select * from sysprocesses where blocked>0 ) b

where a.blocked=spid)

union select spid,blocked from sysprocesses where blocked>0

OPEN s_cur

FETCH NEXT FROM s_cur INTO @spid,@bl

WHILE @@FETCH_STATUS = 0

begin

if @spid =0

select N'引起數據庫死鎖的是:

'+ CAST(@bl AS VARCHAR(10)) + N'進程號,其執行的SQL語法如下'

else

select N'進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ N'被進程號SPID：'+ CAST(@bl AS VARCHAR(10)) +N'阻塞,其當前進程執行的SQL語法如下'

DBCC INPUTBUFFER (@bl )

FETCH NEXT FROM s_cur INTO @spid,@bl

end

CLOSE s_cur

DEALLOCATE s_cur

刪除 procedure
Kill XXX

1. 在要匯出的 DB 上, 按右鍵, 選取工作 -->  產生令碼.

 

2. 歡迎頁

 


 3. 選取資料庫
 

4. 設定選項, 記得要選取 "編寫資料的指令碼"
 

5. 選取資料表
 

6. 選擇要匯出的資料表
 

7. 指定 Script 要儲存的位置
 

8. 檢視摘要
 

9. 完工

在 Select 指令後, 依據結果做一些工作, 即使有 Transaction, 但還是有可能發生一致性的問題:

Ex:

        Dim Conn As SqlConnection = UW.SQL.GetOpenedConnection

        Dim Tran As SqlTransaction = Conn.BeginTransaction

        Try

            Dim C As Int32 = UW.SQL.GetSingleValue("Select Counter From TableA Where Id = 1", Tran)

            C = C + 1

            UW.SQL.executeSQL("Update TableA Set Counter = " & C & " Where  Id = 1", Tran)

            Tran.Commit()

        Catch ex As Exception

            Tran.Rollback()

            UW.WU.DebugWriteLine(ex.ToString, True, True)

        Finally

            If Conn.State <> ConnectionState.Closed Then

                Conn.Close()

            End If

        End Try

以上的程式由兩個 Session 同時執行時, 並不能保証 Counter 會被加 2

若是要保証兩個 Session 都會把 Counter 加一(總共加 2), 要把以下這一行

Dim C As Int32 = UW.SQL.GetSingleValue("Select Counter From TableA Where Id = 1", Tran)

改為

Dim C As Int32 = UW.SQL.GetSingleValue("Select Counter From TableA With(XLock) Where Id = 1", Tran)

語法:

USE AdventureWorks;

GO

CREATE INDEX IX_Address_PostalCode      

ON Person.Address (PostalCode)      

INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

在建立索引時, 可以加入非鍵值欄位, 可以加速讀取速度, 避開索引大小限制, 可惜 SQL 2008 沒有 UI 介面可以使用, 但 SQL 2012 好像就有 UI 可以用了.

參考資料:  Create Indexes with Included Columns http://msdn.microsoft.com/en-us/library/ms190806.aspx

把主機的電腦名稱由 ServerA 改為 ServerB 時, SQL  的排程會失敗, 主要是登入的帳號有問題, 若是你的 SQL 是使用 administrator 安裝的, 此時要進入 Security > Logins , 把其中一個 ServerA\Administrator 改為 ServerB\Administrator