MS SQL 日常維護管理常用指令碼(上)

SQL SERVER 資料庫日常維護，管理，巡檢過程中你可能經常需要用到一些SQL語句（亦或方法）來檢視資料庫伺服器環境（作業系統版本, 磁碟空間，CPU，RAM資訊），資料庫資訊（資料庫版本，例項名稱…）,資料庫物件等。

檢視資料庫資訊

檢視資料庫伺服器名稱

方法1：SQL指令碼查詢，可以通過下面指令碼來查詢。

預設例項

預設例項查詢

命名例項

命名例項查詢

方法2：在資料庫例項單擊右鍵，選擇“屬性”——》“常規”選項裡面，你可以看到伺服器名稱（劃紅線部分）

方法3：都不好意思說了，你懂的。

檢視資料庫例項名稱

方法1：去服務（services.msc）裡面查詢SQL Server（××××）這樣的服務，有多少個就就有多少資料庫例項，一般預設例項是SQL Server (MSSQLSERVER)

方法2：去SQL配置管理器的SQL Server服務配置裡面找上面描述的服務。

方法3：指令碼查詢，只是擷取資料庫服務名稱的例項名（其實這個還真沒必要，通過上面的指令碼就可檢視例項，注意預設例項）

Code Snippet

下面指令碼僅對命名例項有效，預設例項查詢處理的是計算機名稱

Code Snippet

檢視資料庫版本號

方法1：SQL 1：

Code Snippet

方法2：SQL 2： 看起來比較麻煩

Code Snippet

Microsoft SQL Server 2005 – 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

如何從 9.00.4035.00 (Intel X86) 讀取一些資訊呢？

第一個數字：8 代表2000版本， 9 代表2005版本， 10 代表2008 版本

第二個數字：50 代表R2版本， 00 代表非R2版本

第三個數字：如上所示4035中第一個數字4代表打了補丁SP3，其規則如下所示

1： 代表沒有打補丁

2： 代表打了SP1補丁

3： 代表打了SP2補丁，以此類推

035 ： 代表相關的hotfix版本，我們在進行升級，備份,恢復一定要看清楚這個版本，只有在同一版本下的相關資料才能進行恢復（同版本備份檔案），這點要注意。

方法3：在資料庫例項上單擊右鍵，選擇“屬性”——》“常規”選項裡面，你可以看到伺服器名稱，平臺，作業系統,資料庫版本資訊。

檢視資料庫已經打的補丁

方法1：如上所示，可以通過9.00.4035.00 (Intel X86) 來確定已經資料庫已經打的最新補丁SP3。

方法2：在資料庫例項上單擊右鍵，選擇“屬性”——》“常規”選項裡面，可以通過看到的版本資訊檢視補丁

方法3：在SQL配置管理器,找到相應的例項的SQL Server服務,單擊右鍵屬性.

 

檢視例項資料庫的相關資訊

檢視例項有哪些資料庫，建立時間、排序規則…….

方法1：SQL 查詢. 其實在檢視sys.databases裡面你可以檢視很多關於資料庫的資訊，例如，資料庫使用者訪問設定,資料庫的狀態……

SELECT * FROM sys.databases

方法2: 在Mircrosoft SQL Server Management Studio管理器裡面檢視。

檢視排序規則資訊

1：檢視例項排序規則

SELECT SERVERPROPERTY(N’Collation’)

2：檢視資料庫排序規則

SELECT name, collation_name FROM sys.databases

查詢當前資料庫的磁碟使用情況

如需要查詢其他資料庫，則需在前面指定資料庫名稱

EXEC sp_spaceused;

檢視資料庫啟動相關引數

EXEC sp_configure;

檢視資料庫啟動時間

方法1：

Code Snippet

檢視所有資料庫名稱及大小

方法1：

EXEC sp_helpdb;

方法2：

Code Snippet

檢視所有資料庫使用者登入資訊

EXEC sp_helplogins;

檢視所有資料庫使用者所屬的角色資訊

EXEC sp_helpsrvrolemember

更改某個資料物件的使用者屬主

sp_changeobjectowner [@objectname =] ‘object’, [@newowner =] ‘owner’

注意：更改物件名的任一部分都可能破壞指令碼和儲存過程。把一臺伺服器上的資料庫使用者登入資訊備份出來可以用add_login_to_aserver指令碼。檢視某資料庫下,物件級使用者許可權sp_helprotect

檢視連結伺服器

EXEC sp_helplinkedsrvlogin

檢視遠端資料庫使用者登入資訊

sp_helpremotelogin

檢視資料庫下某個資料物件的大小

sp_spaceused @objname

檢視某資料庫下某個資料物件的索引資訊

sp_helpindex @objname

檢視某資料庫下某個資料物件的的約束資訊

sp_helpconstraint @objname

檢視錶的相關資訊

方法1：

sp_help ‘TABLE_NAME’

方法2：

sp_desc 參考我的部落格MS SQL 模仿ORACLE的DESC

修復遷移伺服器時孤立使用者時

方法1：

USE {目標資料庫}

EXEC sp_change_users_login ‘Update_One’, ‘{目標資料庫已存在的使用者名稱}’, ‘{建立的登入使用者名稱}’

方法2

Code Snippet

檢視資料庫資料檔案情況

檢視資料庫例項各個資料庫的資料檔案資訊

方法1： 選擇某個資料庫，然後單擊右鍵屬性…(後面我就不說了，不知道的自己百度)

方法2：SQL

Code Snippet

檢視單個資料庫的資料檔案資訊：

SQL 1：上面SQL加上查詢條件

SQL 2：

Code Snippet

SQL 3：

Code Snippet

檢視資料庫伺服器各資料庫日誌檔案的大小及利用率/狀態

DBCC SQLPERF(LOGSPACE)

檢視當前資料庫的檔案狀態

EXEC (‘DBCC showfilestats’)

檢視資料庫儲存過程

檢視有哪些儲存過程

方法1：

EXEC sp_stored_procedures;

方法2：

SELECT * FROM sys.procedures;

方法3：

SELECT * FROM sys.sysobjects WHERE xtype=’P’;

檢視儲存過程基本資訊

EXEC sp_help ‘dbo.sp_who_lock’

檢視儲存過程原始碼：

方法1：

EXEC sp_helptext ‘procedureName’

方法2：

SELECT *

FROM SYS.SQL_MODULES

WHERE object_id = OBJECT_ID(N’procedureName’)

方法3：

Code Snippet

 

伺服器環境資訊

檢視資料庫所在機器作業系統引數

方法1：

EXEC master..xp_msver

詳解：xp_msver返回有關 Microsoft SQL Server 的版本資訊。xp_msver 還返回有關伺服器的實際內部版本號的資訊以及伺服器環境的有關資訊，例如處理器型別(不能獲取具體型號)， RAM 的容量等等。用指令碼基本上很難獲取詳細的硬體資訊。

方法2：登入伺服器，直接檢視伺服器資訊。

檢視資料庫伺服器磁碟分割槽剩餘空間。

方法1：

EXEC master.dbo.xp_fixeddrives;

方法2：登入伺服器直接檢視或用工具

方法3：請看MS SQL 監控磁碟空間告警裡面介紹的方法

檢視資料庫伺服器磁碟容量資訊

方法1：請看MS SQL 監控磁碟空間告警裡面介紹的方法

方法2：登入伺服器直接檢視或用工具

檢視資料庫伺服器CPU/記憶體的大概資訊

Code Snippet

小結：用SQL檢視伺服器硬體資訊，似乎不是個好主意，很難得到精確地資訊，例如CPU型號、記憶體條的頻率