在SQL Server要修改DB參數或復原時,常常會因為DB有其他連線使用而出現
「資料庫無法獨佔鎖定來執行此作業。」的錯誤訊息
為此必須使用SET SINGLE_USER的指令來踢掉其他連線再處理
不過最近遇到踢掉他人後連線卡在上面,任何操作都表示連線已被人獨占而無法執行
遇到這種情形,雖然可能可以用重啟SQL Server解決
但實務上Server上有其他的DB正在運作,怎可能隨便讓你重開
不用擔心,SQL Server是可以把目前連線給踢掉的...只要你有sa權限

以下假設要操作的DB叫做MyDB

--踢掉其他連線的語法
USE master;
GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--查詢目前連線的語法
USE master;
GO
exec sp_who

--找到目前卡住的連線(可看dbname),記住spid再踢掉
--下方的(spid)請改成數字,如80
kill (spid)

--還原成多人使用模式的語法
ALTER DATABASE MyDB SET MULTI_USER;
GO

順便記錄我最近遇過的問題,免得未來忘記
SQL Server預設的定序(Collation)是「大小寫不分」(Chinese_Taiwan_Stroke_CI_AS)
也就是如果有資料叫做"ADMIN",用"admin"比對也會找到
但存入的時候是大小寫區分("ADMIN"跟"admin"兩筆資料可同時存在)
這會造成存入資料時若不先卡大小寫比對
就會有可產生Admin/admin等大小寫不同的帳號但比對時有誤的情形
(你說查詢的時候加入COLLATE關鍵字?Linq做不到啊)

原本我以為可以靠改DB的定序解決
沒想到原本定序為<資料庫預設值>的參數,修改DB定序後不會跟著改變
仍然是Chinese_Taiwan_Stroke_CI_AS
要改的話可能得針對指定欄位修改
但我也不希望一個DB有多種定序,太雷了
總之如果希望DB預設是大小寫區分的話,請記得修改定序為Chinese_Taiwan_Stroke_CS_AS

--查看DB定序
SELECT DATABASEPROPERTYEX('MyDB', 'Collation')
GO

--查看DB內所有欄位的定序
USE MyDB
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U')

使用SQL Command修改DB定序的語法如下
要注意如果DB有用到索引,可能要先把索引移除,不然無法修改定序

USE master;
GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE MyDB
COLLATE Chinese_Taiwan_Stroke_CS_AS;
GO

ALTER DATABASE MyDB SET MULTI_USER;
GO

最後提一個網路上已有很漂亮解法的問題
SQL Server設定某欄為唯一約束(Unique constraint)的情況下
如果該欄允許NULL,裡面的值也只能存在一個NULL
(例如NULL/NULL/1/2/3,這樣不允許,NULL/1/2/3才允許)

有沒有辦法設定某欄位的值在非NULL的情況下是唯一?
SQL 2005之前沒辦法
SQL 2008開始可以對索引下篩選運算式,這個問題就變得很簡單了(來源

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

要移除的話,可以用Drop
DROP INDEX [YourTable].idx_yourcolumn_notnull

arrow
arrow
    全站熱搜

    蕭雲 發表在 痞客邦 留言(0) 人氣()