SQL Server 管理

Microsoft SQL Server 管理（常用管理及維護命令）--查看數據庫版本Select @@version--查詢當前連接的實例名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 dbnamegoexec 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/* Procedure for 8.0 server */create proc usp_databasesasset nocount ondeclare @name sysnamedeclare @SQL nvarchar(600)/* Use temporary table to sum up database size w/o using group by */create table #databases (DATABASE_NAME sysname NOT NULL,size int NOT NULL)declare c1 cursor forselect name from master.dbo.sysdatabaseswhere has_dbaccess(name) = 1 -- Only look at databases to which we have accessopen c1fetch c1 into @namewhile @@fetch_status >= 0beginselect @SQL = 'insert into #databasesselect N'''+ @name + ''', sum(size) from '+ QuoteName(@name) + '.dbo.sysfiles'/* Insert row for each database */execute (@SQL)fetch c1 into @nameenddeallocate c1selectDATABASE_NAME,DATABASE_SIZE = size*8,/* Convert from 8192 byte pages to K */RUN_DT=GETDATE()from #databasesorder by 1GOcreate table DatabaseSizeReport(Database_Name Varchar(32),Database_Size int,CreateDt datetime)insert into DatabaseSizeReport exec usp_databasesselect * from DatabaseSizeReport--快捷方式ALT-F1 sp_helpCTRL-1 sp_whoCTRL-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_readerrorlogselect * from #sunying_error_lgdrop table #sunying_error_lg--獲得dbcc命令的完整列表dbcc help('?')--檢測資料庫損壞情況alter database <dbname> set single_user with rollback immediatedbcc 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_log6.再次以單使用者模式啟動SQL Server，如果資料庫出現了，可對其進行詳細的DBCC檢查(checkdb,checkalloc,等等)7.運行一些隨機查詢，看看是否會遇到問題。

8.如果沒有問題出現，可停止並重新開機SQL Server，然後將資料庫投入生產應用。

--將資料庫置於緊急模式sp_configure 'allow updates',1reconfigure with overridegoupdate master..sysdatabases set status=-32768 where name='pubs'gosp_configure 'allow updates',0reconfigure with overridego--當資料庫處於這種模式時,你只能從中讀取資料。

--查看日誌空間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_testcreate table sunying_test (aid int identity,val varchar(500))insert into sunying_test (val) select 'abc' --@@identity為1insert into sunying_test (val) select 'abc' --@@identity為2insert into sunying_test (val) select 'abc' --@@identity為3select * from sunying_testselect @@identity--在不同連接裡查@@identity她返回nullselect @@identity--和@@identity相同，不同點即使停止SQL Server並且重新建立連接，這個命令仍然會返回正確的值select ident_current('<table_name>')--查看I/O，執行計畫set statistics io onselect count(*) from sunying_test--斷開一個資料庫的所有使用者sp_whocreate procedure usp_killusers @dbname varchar(50) asset nocount ondeclare @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_whodeclare logincursor cursorread_onlyfor select spid,dbname from #tmpUsers where dbname=@dbname declare @spid varchar(10)declare @dbname2 varchar(40)open logincursorfetch next from logincursor into @spid,@dbname2while (@@fetch_status<>-1)beginif (@@fetch_status<>-2)beginprint 'Killing '+@spidset @strSQL='KILL '+@spidexec (@strSQL)endfetch next from logincursor into @spid,@dbname2endclose logincursordeallocate logincursordrop table #tmpUsersprint 'Done'gousp_killusers 'crmnew'--top的使用select top n * from <tablename> --返回表中前n行記錄select top n percent * from <tablename> --返回表中最前面的n%行記錄--優化索引的充滿度,她對索引的性能影響很大dbcc showcontig (WIP)/* --執行以後顯示如下DBCC SHOWCONTIG 正在掃描'WIP' 表...表: 'WIP'（124071728）；索引ID: 1，資料庫ID: 7已執行TABLE 級別的掃描。