sql server管理常用指令

--查詢目前連接配接的執行個體名

select @@servername --察看任何資料庫屬性

sp_helpdb master

--設定單使用者模式，同時立即斷開所有使用者

alter database Northwind set single_user with rollback immediate --恢複正常

alter database Northwind set multi_user

--察看資料庫屬性

sp_helpdb --察看資料庫恢複模式

select databasepropertyex('Northwind','recovery') --設定自動建立統計

alter database Northwind set auto_create_statistics on/off --設定自動更新統計

alter database Northwind set auto_update_statistics on/off --檢視作業清單

select * from msdb..sysjobs --檢視作業詳細資訊

exec msdb..sp_help_job @job_name = 'HQCRM-CrmNew-CrmNew_2Dimension-NANJINGCRM-216' --修改作業資訊

exec msdb..sp_update_job @job_id = 0x4CA27521C9033C48954E7BFC6B965395, @enabled = 1 --察看伺服器角色

exec sp_helpsrvrolemember --添加伺服器角色

exec sp_addsrvrolemember 'member_name','sysadmin' --删除伺服器角色

exec sp_dropsrvrolemember 'member_name','sysadmin' --察看資料庫角色

exec sp_helprole --添加資料庫角色

exec sp_addrole 'role_name' --删除資料庫角色

exec sp_droprole 'role_name' --檢視使用者資訊

exec sp_helpuser --注意删除guest帳戶

use dbname

go

exec sp_dropuser guest

--修改對象擁有者

exec sp_changeobjectowner 'old_onwer.table_name', 'new_owner'

--檢視BUILTIN/Administrators帳号

exec master..xp_logininfo 'BUILTIN/Administrators', 'members' --修改預設資料庫

exec sp_defaultdb 'login', 'defaultdb' --建立新的登陸

exec sp_addlogin @loginame = 'esProgram',@passwd = 'h1J2P97vfdlK34',@defdb = 'career'

--更改登陸資料庫通路權限

use <dbname>

exec sp_grantdbaccess @loginame ='esProgram',@name_in_db = 'esProgram'

--把角色db_appuser添加使用者帳号中

use <dbname>

exec sp_addrolemember @rolename = 'db_appuser',@membername = 'esProgram' --預測增長

use master

create proc usp_databases

as

set nocount on

declare @name sysname

declare @SQL nvarchar(600)

create table #databases (

      DATABASE_NAME sysname NOT NULL,

      size int NOT NULL) declare c1 cursor for

   select name from master.dbo.sysdatabases

    where has_dbaccess(name) = 1 -- Only look at databases to which we have access open c1

fetch c1 into @name while @@fetch_status >= 0

begin

   select @SQL = 'insert into #databases

     select N'''+ @name + ''', sum(size) from '

     + QuoteName(@name) + '.dbo.sysfiles'

   execute (@SQL)

   fetch c1 into @name

end

deallocate c1 select

   DATABASE_NAME,

   DATABASE_SIZE = size*8,

   RUN_DT=GETDATE()

from #databases

order by 1 GO

create table DatabaseSizeReport

(Database_Name Varchar(32),

Database_Size int,

CreateDt datetime)

insert into DatabaseSizeReport exec usp_databases

select * from DatabaseSizeReport --快捷方式

ALT-F1   sp_help

CTRL-1   sp_who

CTRL-2   sp_lock --檢視對象空間

exec sp_spaceused <db_name>/<table_name> --察看檔案是否存在

exec xp_fileexist "c:/test.txt" --察看檔案詳細資訊

exec master..xp_getfiledetails "c:/test.txt"

--檢視系統出錯日志

set nocount on --執行一個查詢或者是存儲過程是要保證這個是開的。可以提高速度

create table #sunying_error_lg

(errortext varchar(500),

continuerow int)

insert into #sunying_error_lg exec master..xp_readerrorlog

select * from #sunying_error_lg

drop table #sunying_error_lg

--獲得dbcc指令的完整清單

dbcc help('?')

--檢測資料庫損壞情況

alter database <dbname> set single_user with rollback immediate

dbcc checkdb (<dbname>,repair_fast) --資料庫損壞處理步驟

1.檢查SQL SERVER和Windows NT錯誤日志，看是否能找出問題所在。例如，可能硬碟驅動器已滿。

2.以單使用者模式啟動SQL Server。

3.用@dbname參數執行sp_resetstatus(比如，sp_resetstatus @dbname="pubs")。來使資料庫擺脫損壞狀态

4.以單使用者模式重新啟動SQL Server。

5.如果資料庫仍處于置疑狀态，可将它重設回正常模式，并試着用下面指令轉儲置疑的資料庫的事務：dump transaction Northwind with no_log

6.再次以單使用者模式啟動SQL Server，如果資料庫出現了，可對其進行詳細的DBCC檢查(checkdb,checkalloc,等等)

7.運作一些随機查詢，看看是否會遇到問題。

8.如果沒有問題出現，可停止并重新啟動SQL Server，然後将資料庫投入生産應用。

--将資料庫置于緊急模式

sp_configure 'allow updates',1

reconfigure with override

go

update master..sysdatabases set status=-32768 where name='pubs'

go

sp_configure 'allow updates',0

reconfigure with override

go

--當資料庫處于這種模式時,你隻能從中讀取資料。 --檢視日志空間

dbcc sqlperf(logspace) --檢視高速緩存命中率

dbcc sqlperf(lrustats) --檢視活動事務(open transaction)

dbcc opentran

--如果有事務是活動的比如(SPID:54 UID:1)，還有看這個事務打開了多久了。如果有人忘了，可以用

--kill 54

--來幹掉他

--檢視使用者使用set指令的全部内容

dbcc useroptions

--比如可以檢視set nocount on --系統函數

app_name()函數傳回目前從SQL Server請求資料的應用程式名稱。

get_date()函數傳回SQL Server上的目前時間。

host_name()函數确定是哪台工作站正在連接配接到SQL Server。

system_user函數提供正在連接配接的使用者的登陸名

db_name()告訴你連接配接是哪個資料庫 --@@identity辨別列

drop table sunying_test

create table sunying_test (aid int identity,val varchar(500))

insert into sunying_test (val) select 'abc' --@@identity為1

insert into sunying_test (val) select 'abc' --@@identity為2

insert into sunying_test (val) select 'abc' --@@identity為3

select * from sunying_test

select @@identity

--在不同連接配接裡查@@identity她傳回null

select @@identity --和@@identity相同，不同點即使停止SQL Server并且重建立立連接配接，這個指令仍然會傳回正确的值

select ident_current('<table_name>') --檢視I/O，執行計劃

set statistics io on

select count(*) from sunying_test --斷開一個資料庫的所有使用者

sp_who create procedure usp_killusers @dbname varchar(50) as

set nocount on

declare @strSQL varchar(255)

print 'Killing Users'

print '---------------------'

create table #tmpUsers(

spid int,

eid int,

status varchar(30),

loginname varchar(50),

hostname varchar(50),

blk int,

dbname varchar(50),

cmd varchar(30))

insert into #tmpUsers exec sp_who

declare logincursor cursor

read_only

for select spid,dbname from #tmpUsers where [email protected]

declare @spid varchar(10)

declare @dbname2 varchar(40)

open logincursor

fetch next from logincursor into @spid,@dbname2

while (@@fetch_status<>-1)

begin

if (@@fetch_status<>-2)

begin

    print 'Killing '[email protected]

    set @strSQL='KILL '[email protected]

    exec (@strSQL)

end

fetch next from logincursor into @spid,@dbname2

end

close logincursor

deallocate logincursor

drop table #tmpUsers

print 'Done'

go

usp_killusers 'crmnew' --top的使用

select top n * from <tablename> --傳回表中前n行記錄

select top n percent * from <tablename> --傳回表中最前面的n%行記錄

--優化索引的充滿度,她對索引的性能影響很大 dbcc showcontig (T_Contract_Pdt)

--解決碎片問題，重建索引

dbcc dbreindex (<table_name>,'<index_name>',<fill factor>)

dbcc dbreindex (T_Contract_Pdt) dbcc showcontig (T_Contract_Pdt)

--重建索引開銷很大。可以用整理索引碎片來代替,但最徹底最好的方式是重建索引

dbcc indexdefrag (<db_name>,<table_name>,<index_name>) --backupset表提供有關備份的詳細資訊

select * from msdb..backupset

--檢視登陸使用者

select * from master..syslogins --檢視使用者資訊

select * from Northwind..sysusers --sysusers和syslogins表的sid相對應，但在還原或者附加資料庫可能出現不一緻的現象，可以使用比如：

sp_change_users_login 'report'

--檢視不比對的使用者

sp_change_users_login 'auto_fix', <username>

--糾正這個使用者名，如果這個使用者登陸不存在的話會建立他。也可以用Update_One參數修複如下

sp_change_users_login 'update_one', <username>,<loginname> --恢複損壞的master庫

1.使用/Program Files/Microsoft SQL Server/80/Tools/Binn目錄下的rebuildm.exe檔案重建master資料庫。重建master資料庫可以使用你的資料庫檔案完整無缺。記得把資料檔案和日志檔案備份到其他地方。

2.使用-m參數啟動SQL Server，這樣可以以單使用者模式重新啟動SQL Server。

3.從最近一次已知最好的備份來還原master資料庫。

4.核實master資料庫是不是已經成功還原，确認所有資料庫都已恢複正常并運作。從最近一次已知最好的備份來還原msdb資料庫。

5.以普通模式停止和啟動SQL Server

6.向生産使用者開放資料庫

--重建其他資料庫。

/Program Files/Microsoft SQL Server/MSSQL/Install/

instmsdb.sql--msdb資料庫

instnwnd.sql--Northwind資料庫

instpubs.sql--pubs資料庫

--注意重建msdb以後，作業，DTS包和其他關鍵資訊将會丢失。重建以後要還原她

--檢視執行計劃

set statistics profile on

select * from course_info

set statistics profile off --在查詢分析器裡選擇查詢--顯示執行計劃,顯示伺服器跟蹤，顯示客戶統計 --暫停伺服器後已經連接配接的客戶機連接配接不受影響，可以繼續執行，但拒絕新的客戶機連接配接請求。建議DBA在實際管理中，先選擇暫停，然後在選擇關閉

--SQL SERVER2000推薦使用動态配置設定記憶體的方法，執行個體會在Windows作業系統的排程下動态獲得記憶體。選中SQL Server保留實體記憶體複選框指定為SQLSERVER保留與記憶體設定相等的實體記憶體空間，這樣作業系統不能使用為SQL SERVER保留的記憶體空間。在最小查詢記憶體文本框裡可以設定配置設定給每個使用者執行查詢的最小記憶體，預設為1024KB --如果SQL SERVER伺服器非常重要，要确定足夠的記憶體來運作，可以選指定固定大小的記憶體。配置了固定記憶體并不表明一開始SQLSERVER2000伺服器就占用設定的記憶體，而是随着需要記憶體的增加不斷增加，最後隻能使用最多的記憶體。

--索引填充因子假如設定為60%那麼就規定了在向索引頁面中插入索引資料時最多可以占用的頁面空間是(60%*8KB=4.8KB)。剩下的約40%(40%*8KB=3.2KB)的空間保留供索引的資料更新使用。如果更新後索引資料超出了原有索引頁面的存儲空間後，SQL SERVER2000會自動将原索引頁面大緻一半的資料遷移到新的頁面中，稱為頁的分裂。頁的分裂操作将會導緻系統性能的嚴重下降。 --什麼是恢複間隔，預設是0這表示用于快速恢複的自動設定，表示由SQLSERVER2000自動決定什麼時間生成檢查點。

--什麼是檢查點,如果系統崩潰進行恢複時，需要從頭到尾掃描日志檔案的内容，執行重做或者復原的操作。如果沒有檢查點，每次恢複都要從頭開始掃描，這對于大容量的日志檔案來說，會導緻恢複的時間比較長。有了檢查點，就可以從距離發生故障點最近的檢查點開始做起就可以了。可以大大減少恢複的時間。

--恢複間隔其實就是檢查點發生頻率的參數

--手動執行檢查點

checkpoint; --查詢伺服器目前運作參數

select * from master..sysconfigures --伺服器出錯日志預設路徑

C:/Program Files/Microsoft SQL Server/MSSQL/LOG

--master庫是最重要的資料庫存儲系統資訊，磁盤空間，檔案配置設定和使用，系統級的配置參數，所有的登陸帳戶資訊，初始化資訊和其他資料庫資訊等。

--msdb庫是SQLSERVER代理服務使用的資料庫。為報警，作業，任務排程和記錄操作員操作提供存儲空間。

--tempdb庫是臨時資料庫，她為所有臨時表，臨時存儲過程以及其他的臨時操作提供空間。每次重起改資料庫裡的資訊就是丢失。

--model庫是存儲了所有使用者資料庫和tempdb資料庫的模闆。

--啟動

1.啟動master庫

2.啟動model庫

3.啟動msdb庫

4.清除tempdb庫

5.啟動tempdb庫 --DBCC（data base consistency checker）,資料庫控制台指令，也稱資料庫一緻性檢查是SQLSERVER2000提供的一類特殊的指令，用于執行特殊的資料庫管理操作 --查詢所有的DBCC指令

dbcc help('?') --查詢指定DBCC指令的文法說明

dbcc help('checkalloc') --傳回目前連接配接的活動設定

dbcc traceon(3604)

go

dbcc useroptions

go --檢查指定資料庫的磁盤空間配置設定結構的一緻性

dbcc checkalloc('Northwind',repair_allow_data_loss|repair_fast|repair_rebuild)

repair_allow_data_loss:完成所有的修複，有可能會導緻一些資料丢失

repair_fast:進行小的，不耗時的修複操作，不會有丢失資料

repair_rebuild:完成所有的修複，包括較長時間的修複比如重建索引

--檢查資料庫中系統表内及系統表間的一緻性

dbcc checkcatalog('Northwind') --用于檢查指定表上的限制完整性

use Northwind

go

dbcc checkconstraints('Customers')

go --檢查指定資料庫中的所有對象的配置設定和結構完整性

dbcc checkdb('Northwind') --檢查指定表或索引視圖的資料，索引及text,ntext和image頁的完整性

use Northwind

go

dbcc checktable('Customers')

go --檢查指定表的目前辨別值

use pubs

go

dbcc checkident(jobs,noreseed|reseed new_reseed_value) --回收alter table drop column語句删除可變長度列或text列後的存儲空間,注意不能對系統表或臨時表執行該操作

dbcc cleantable('Northwind','Customers') --重建指定資料庫中一個或多個索引

--create index語句比較優點

--1.允許動态重建表的索引或為表定義的所有索引

--2.允許重建強制PRIMARY KEY或者UNIQUE限制的索引，而不必除去并重新建立些限制。

--3.允許使用一條語句重建表的所有索引，這比多個DROP INDEX和CREATE INDEX語句進行編碼容易。

--4.可以優化SQL Server資料庫的性能

--注意不能在系統表使用dbcc dbreindex

dbcc dbreindex('Northwind..Customers')

go --通常懷疑sp_spaceused所傳回的值不正确時使用,對表和聚集索引中sysindexes表的rows,used,reserved和dpages列進行更正。

dbcc updateusage('pubs','authors')

go --對表或視圖上的索引和非聚集索引進行碎片整理

--上面已經說過了碎片影響性能已經處理方法，這裡就不在重複

dbcc indexdefrag(Northwind,Orders,CustomersOrders)

go

--将表駐留在記憶體中，和将表從記憶體中撤消。注意dbcc pintable适用于小表，使用時不要把大表駐留記憶體。這樣會嚴重影響性能

use Northwind

set @db_id=db_id('Northwind')

set @tb1_id=object_id('Northwind..Customers')

dbcc pintable(@db_id,@tb1_id) dbcc unpintable(@db_id,@tb1_id)

--收縮資料庫，不會把實體檔案大小進行收縮，對使用空間進行收縮，實體大小的收縮用dbcc shrinkfile

--Northwind資料庫中的檔案有10%的可以空間，

dbcc shrinkdatabase(Northwind,10)

--收縮實體檔案大小

use Northwind

sp_helpfile

dbcc shrinkfile(1,1000M) --用于從緩沖池中删除所有内容，即将記憶體中的所有資料庫有的資料全部清除

--可以用于性能測試，在不重起的情況下，清一下記憶體，在考察SQL語句的性能時就能得到SQL語句執行的實體IO執行情況

dbcc dropcleanbuffers --在記憶體中解除安裝指定的擴充存儲過程動态連接配接庫(DLL)

--1.查詢已經裝載的DLL

sp_helextendedroc

go

--2.從記憶體中釋放系統存儲過程xp_sendmail的DLL

dbcc xp_sendmail(free)

go --顯示使用者最後執行的語句，比較實用^_^

--1.顯示使用者連接配接的程序ID（SPID）

exec sp_who

--2.顯示語句

dbcc inputbuffer(spid) --查詢某個資料庫執行時間最久的事務，對調優很有用^_^

dbcc opentran('master') --顯示指定表上指定目标的目前分布統計資訊，主要是索引的統計資訊

use pubs

dbcc show_statistics(authors,UPKCL_auidind)

go

--顯示指定表的資料和索引的碎片資訊這個上面已經提過了

use <db_name>

dbcc showcontig(<table_name>)

go --傳回多中有用的統計資訊

--1.統計事務日志資訊

dbcc sqlperf(logspace)

--2.統計線程管理資訊

dbcc sqlperf(umsstats)

num user:目前UMS排程器使用的SQL Server線程數。

num runnable:實際上正在運作的SQL Server線程數。

num workers:線程池的大小，也就是實際上的工作者數量。

idle workers:目前空閑的工作者數量。

cntxt switches:在運作的線程間進行的上下文切換數。

cntxt switches(idle):空閑的線程間進行的上下文切換數。

--3.統計資源等待類型資訊

dbcc sqlperf(waitstats)

--4.統計I/O資源消耗資訊

dbcc sqlperf(iostats)

--5.統計先讀資訊

dbcc sqlperf(rastats)

--6.統計每個線程的資源消耗資訊

dbcc sqlperf(threads) --顯示記憶體的統計資訊,記憶體的細分資訊

dbcc cachestats

dbcc memorystatus --顯示遊标的統計資訊

dbcc cursorstats

--顯示緩存中先讀和預先準備的SQL語句

dbcc sqlmgrstats Memory Used(8k pages):如果記憶體頁面數量很大，該值表示一些使用者連接配接先讀了許多Transact-SQL語句。

Number CSql Objects:緩存中的Transact-SQL語句總的數量

Number False Hits:到記憶體中去查找已經緩存的Transact-SQL語句而沒有命中的值。該值應該盡可能低

--用于清除記憶體中的某個資料庫的存儲過程緩存内容

declear @intDBID integer

set @intDBID=(select dbid from master..sysdatabases where name='TESTDB01')

dbcc flushprocindb(@intDBID)

go --顯示緩沖區的頭部資訊的頁面資訊

dbcc traceon(3604)

go

dbcc buffer(master,'sysobjects',2)

go --顯示資料庫的結構資訊

dbcc traceon(3604)

go

dbcc dbinfo(master)

go --顯示管理資料庫的表（資料字典）資訊

dbcc traceon(3604)

go

dbcc dbtable(master)

go --檢視某個索引使用的頁面資訊

dbcc traceon(3604)

go

dbcc ind(master,sysobjects,0)

go

--檢視某個資料庫使用的事務日志資訊

dbcc traceon(3604)

go

dbcc log(crmnew,-1)

go

--顯示過程緩沖池

dbcc traceon(3604)

go

dbcc procbuf(master,'sp_help',1,0)

go

--用于輸出某個頁面的每行指向的頁面号

dbcc traceon(3604)

go

declare @dbid int,@objectid int

select @dbid=DB_ID('master')

select @objectid=object_id('sysobjects')

dbcc prtipage(@dbid,@objectid,1,0)

go --顯示目前連接配接到伺服器的程序

dbcc traceon(3604)

go

dbcc pss(ADMINISTRATOR,54,1)

go --顯示目前伺服器資源情況

dbcc traceon(3604)

go

dbcc resource

go

--用于檢視資料頁面的結構

dbcc traceon(3604)

go

declare @dbid int,@objectid int

select @dbid=DB_ID('master')

select @objectid=object_id('sysdatabases')

dbcc tab(@dbid,@objectid)

go

--查詢SQL語句的讀寫代價

set statistics io on

go

select count(1) from Northwind..employees

go

set statistics io off

go

（所影響的行數為 1 行） 表 'Employees'。掃描計數 1，邏輯讀 1 次，實體讀 0 次，預讀 0 次。

--查詢SQL語句的執行時間

set statistics time on

go

select count(1) from Northwind..employees

go

set statistics time off

go

SQL Server 執行時間:

   CPU 時間 = 0 毫秒，耗費時間 = 0 毫秒。

SQL Server 分析和編譯時間:

   CPU 時間 = 0 毫秒，耗費時間 = 0 毫秒。 （所影響的行數為 1 行）

SQL Server 執行時間:

   CPU 時間 = 0 毫秒，耗費時間 = 0 毫秒。

SQL Server 分析和編譯時間:

   CPU 時間 = 0 毫秒，耗費時間 = 0 毫秒。

--查詢SQL語句的執行計劃

set showplan_text on

go

select count(1) from Northwind..employees

go

set showplan_text off

go |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))

       |--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))

            |--Index Scan(OBJECT:([Northwind].[dbo].[Employees].[PostalCode]))

--更加詳細的執行計劃

set showplan_all on

go

select count(1) from Northwind..employees

go

set showplan_all off

go EstimateIO和EstimeCPU是主要的評估SQL語句的執行代價的标準

--成批SQL語句的執行時間

create table #save_time(start_time datetime not null)

insert #save_time values(getdate())

go

--批處理的腳本

select * from Northwind..customers

go

select * from Northwind..products

go

--批處理語句結束

select 'SQL語句的執行時間(ms)'=datediff(ms,start_time,getdate()) from #save_time

drop table #save_time

go --密碼檔案儲存在哪裡？

select * from master..sysxlogins

--事務操作

begin tran <事務名>

update t_a set val='123'

commit tran <事務名>

或者rollback tran <事務名>