[DBA] 紀錄一下MSSQL常用到的管理指令與觀念

Hits: 3951

統整一下DBA常用到的指令：

檔案結構(Server/DB/欄位定義)相關

列出server內全部資料庫的「檔案」大小

SELECT 
    DB_NAME(database_id) N'資料庫',
    physical_name N'實體檔案',
    type_desc N'檔案類型', 
    state_desc N'檔案狀態', 
    size*8.0/1024 N'檔案大小(MB)',
    size*8.0/1024/1000 N'檔案大小(GB)',
    size*8.0/1024/1000/1000 N'檔案大小(TB)'
FROM sys.master_files
order by DB_NAME(database_id)

出處：德瑞克的SQL Server學習筆記

列出DB內所有表的列數與大小

USE [DB_NAME] -- Change your DB name here.
Go

SELECT 
    t.NAME AS TableName,
    --s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 /1024 AS UsedSpaceMB, 
    SUM(a.used_pages) * 8 /1024/1000 AS UsedSpaceGB, 
    SUM(a.used_pages) * 8 /1024/1000/1000 AS UsedSpaceTB
    --(SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    --t.NAME NOT LIKE 'dt%'
    t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.name

出處：Ryan的資訊備忘錄

取得所有欄位定義的方法

SELECT
    a.TABLE_NAME                as 表格名稱,
    b.COLUMN_NAME               as 欄位名稱,
    b.DATA_TYPE                 as 資料型別,
    b.CHARACTER_MAXIMUM_LENGTH  as 最大長度,
    b.COLUMN_DEFAULT            as 預設值,
    b.IS_NULLABLE               as 允許空值,
    (
        SELECT
            value
        FROM
            fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 
                                     a.TABLE_NAME, 'column', default)
        WHERE
            name='MS_Description' 
            and objtype='COLUMN' 
            and objname Collate Chinese_Taiwan_Stroke_CI_AS=b.COLUMN_NAME
    ) as 欄位備註
FROM
    INFORMATION_SCHEMA.TABLES  a
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON (a.TABLE_NAME=b.TABLE_NAME)
WHERE
    TABLE_TYPE='BASE TABLE'
ORDER BY
    a.TABLE_NAME, ordinal_position

出處：Will Will Web

備份/還原相關

查看所有備份紀錄

這段指令可以檢查備份檔案的型態，確定備份的設定。

SELECT 
    a.[database_name] as '資料庫名稱',
    CASE a.[type]
    WHEN 'D' THEN N'資料庫'
    WHEN 'I' THEN N'差異資料庫'
    WHEN 'L' THEN N'紀錄'
    WHEN 'F' THEN N'檔案或檔案群組'
    WHEN 'G' THEN N'差異檔案'
    WHEN 'P' THEN N'部分'
    WHEN 'Q' THEN N'差異部分'
    ELSE N'NULL'
    END as '備份類型',
    a.[name] as '備份組的名稱',
    a.[first_lsn] as '備份組中第一個或最舊的記錄序號',
    a.[last_lsn]  as '備份組之後下一個記錄的記錄序號',
    a.[database_backup_lsn] as '最近的完整資料庫備份之記錄序號',
    a.[differential_base_lsn] as '差異備份的基底 LSN',
    a.[backup_finish_date] as '備份作業完成的日期和時間',
    a.[backup_size] as '備份組的大小 (以位元組為單位)'

FROM
    msdb..backupset a INNER JOIN master..sysdatabases b ON 
        a.database_name COLLATE DATABASE_DEFAULT = 
        b.name COLLATE DATABASE_DEFAULT 
ORDER BY
    a.database_name, a.backup_finish_date

出處：Will Will Web

還原資料庫前的步驟

請先備份結尾紀錄(tail-log)
連結

SQL Server 作業管理

列出作業的擁有者與執行步驟等資訊

在管理公司的SQL Server帳號時，想了解目前Server排定的作業是由哪個帳號執行的，可透過將msdb底下的dbo.sp_help_job這支SP來查詢，以下為執行碼

use msdb
--exec dbo.sp_help_job --可查出每個作業的相關資訊

create table #job_info
(
  job_id uniqueidentifier,
  originating_server varchar(50),
  name varchar(max),
  enabled int,
  description varchar(max),
  start_step_id int,
  category varchar(50),
  owner varchar(50),
  notify_level_eventlog int,
  notify_level_email int,
  notify_level_netsend int,
  notify_level_page int,
  notify_email_operator varchar(50),
  notify_netsend_operator varchar(50),
  notify_page_operator varchar(50),
  delete_level int,
  date_created datetime,
  date_modified datetime,
  version_number int,
  last_run_date varchar(8),
  last_run_time varchar(10),
  last_run_outcome int,
  next_run_date varchar(10),
  next_run_time varchar(10),
  next_run_schedule_id varchar(10),
  current_execution_status int,
  current_execution_step varchar(50),
  current_retry_attempt int,
  has_step int,
  has_schedule int,
  has_target int,
  type int
)

insert into #job_info exec dbo.sp_help_job
go

select
  originating_server as server, --server name
  name, --job name
  description,
  enabled,
  owner, --login name
  notify_email_operator, --operator name
  last_run_date
from #job_info
order by name
go

drop table #job_info

SQL Server記錄檔

列出SQL Server的記錄檔

根據SQL Server的記錄檔，查詢使用者的登入狀況。必須先在伺服器屬性開啟登入稽核，才可查詢到登入資訊。

file


--1. 將SQL Server記錄檔寫入暫存表
create table #log_table
(
  LogDate datetime,
  ProcessInfo varchar(50),
  Text varchar(max)
)
insert into #log_table exec xp_readerrorlog

--2. 撈出近一個月的登入資料與ip位置
select
  LogDate,
  ProcessInfo,
  SUBSTRING(Text, PATINDEX('%user %', Text) + 6, PATINDEX('% Connection%', Text) - 3 - PATINDEX('%user %', Text) - 6 + 1) as user_name,
  SUBSTRING(Text, PATINDEX('%CLIENT: %', Text) + 8, PATINDEX('%]%', Text) - 1 - PATINDEX('%CLIENT: %', Text) - 8 + 1) as ip
from #log_table
where ProcessInfo = 'Logon' and DATEDIFF(DAY, LogDate, GETDATE()) <= 31
order by LogDate desc, user_name

--2-1. 計算近一個月的登入次數與ip位置
select a.*, count(*) as count 
from (
  select
    SUBSTRING(Text, PATINDEX('%user %', Text) + 6, PATINDEX('% Connection%', Text) - 3 - PATINDEX('%user %', Text) - 6 + 1) as user_name,
    SUBSTRING(Text, PATINDEX('%CLIENT: %', Text) + 8, PATINDEX('%]%', Text) - 1 - PATINDEX('%CLIENT: %', Text) - 8 + 1) as ip
  from #log_table
  where ProcessInfo = 'Logon' and DATEDIFF(DAY, LogDate, GETDATE()) <= 31
)a
group by a.user_name, a.ip
order by a.user_name

--3. 刪除暫存表
drop table #log_table

查詢管理

在SSMS的查詢視窗切換連線

開啟SSMS的查詢視窗時，預設會在Server_A，若我要在此時切換連線至Server_B以便執行預存程序(store procedure)，可以藉由SSMS的視窗點選變更連結(但是要重新輸入一次帳號密碼，麻煩)，另外一個方法就是直接下指令，就可以在Server_A的查詢視窗切換至Server_B並針對Server_B底下的DB, SP進行查詢與使用，以下說明如何使用。

STEP1: 開啟SQLCMD模式[DBA] 紀錄一下MSSQL常用到的管理指令與觀念 1

STEP2: 執行下列語句後執行查詢:CONNECT SQL_Server_B -U LoginName -P MyPassword將LoginName與Password換成登入SQL_Server_B的帳號密碼即可

STEP3: 完整查詢範例

:CONNECT SQL_Server_B -U LoginName -P MyPassword
select * from SQL_Server_B.dbo.mytable

如此即可在不用手動切換連線的情況下，達到查詢另外一台SQL Server的辦法。

找出被鎖住的查詢，並且刪除

執行了一段跑了3天的query，但是SSMS當掉了，導致該DB被鎖起來，看不到裡面的表，得知是表被鎖起來了，錯誤訊息如下

鎖定要求的逾時期間已過。 (Microsoft SQL Server, 錯誤: 1222)

嘗試解法如下出處：

-- find the blocked spid
select distinct spid = convert( varchar(5), a.spid, 4 ), cmd = convert( varchar(16), a.cmd )
from master..sysprocesses a
where spid in ( select blocked from master..sysprocesses ) and blocked = 0
order by spid

-- another way to find the blocked spid
exec sp_who2

-- kill the blocked spid
kill <spid>

-- to check the rollback progress
kill <spid> with statusonly
Leave a Comment on [DBA] 紀錄一下MSSQL常用到的管理指令與觀念DBA, SQL
Tags , , ,

About the Author

swchen

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

You may also like these