資料庫常用管理語句

KILL資料庫進程，常用於需要單獨操作的情況下，也用於死結情況。

程式碼片段3：強制斷開使用者串連進程(也常用於死結）。
--kill資料庫的串連進程.
DECLARE @dbname sysname 
SET @dbname='adirectory' --要關閉進程的資料庫名
declare @s nvarchar(1000)
declare tb cursor local for
select 'kill '+cast(spid as varchar)
from master..sysprocesses 
where dbid=db_id(@dbname)
open tb 
fetch next from tb into @s
while @@fetch_status=0
BEGIN
--PRINT @s
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
go

--2 kill 資料庫進程,需要複製產生的文本來執行。
select COALESCE('kill ','')+cast(spid as varchar)
from master..sysprocesses 
where dbid=db_id(@dbname)

4、查看資料庫死結進程。

DECLARE @spid int,@bl int
DECLARE s_cur CURSOR FOR 
select  0 ,blocked
from (select * from master..sysprocesses where  blocked>0 ) a 
where not exists(select * from (select * from master..sysprocesses where  blocked>0 ) b 
where a.blocked=spid)
union select spid,blocked from master..sysprocesses where  blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0 
  select '引起資料庫死結的是: '+ CAST(@bl AS VARCHAR(10)) + ' 進程號,其執行的SQL文法如下'
else
  select '進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ ' 被進程號SPID：'+ CAST(@bl AS VARCHAR(10)) +' 阻塞,其當前進程執行的SQL文法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur
3、更改資料庫排序.更改資料庫定序（不會改變原已有的資料）--更改為中文排序

ALTER DATABASE dbName COLLATE Chinese_PRC_CI_AS

--查看所有排序

SELECT * FROM fn_helpcollations()

 

2、查看資料庫連接進程資訊

select name,count(0)as conn,
hostname,program_name,loginame,
s.login_time,s.net_address,nt_domain,s.cmd
from master.dbo.sysprocesses s join master.dbo.sysdatabases d
on s.dbid=d.dbid and d.name in (select name from master.dbo.sysdatabases)
group by name,hostname,program_name,loginame,s.login_time,s.net_address,nt_domain,s.cmd 
order by name
1、當前資料庫在做什嗎？for SQL 2005

use master
select sys.dm_exec_sessions.session_id,
sys.dm_exec_sessions.host_name,
sys.dm_exec_sessions.program_name,
sys.dm_exec_sessions.client_interface_name,
sys.dm_exec_sessions.login_name,
sys.dm_exec_sessions.nt_domain,
sys.dm_exec_sessions.nt_user_name,
sys.dm_exec_connections.client_net_address,
sys.dm_exec_connections.local_net_address,
sys.dm_exec_connections.connection_id,
sys.dm_exec_connections.parent_connection_id,
sys.dm_exec_connections.most_recent_sql_handle,
(select text from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as sqlscript,
(select db_name(dbid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as databasename,
(select object_id(objectid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as objectname
from sys.dm_exec_sessions inner join sys.dm_exec_connections
on syssys.dm_exec_connections.session_id=sys.dm_exec_sessions.session_id
0、擷取資料庫的實體路徑：

CREATE FUNCTION dbo.ufnGetSysDBPath(
    @dbName VARCHAR(100)
) RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @returnValue VARCHAR(200)
IF SERVERPROPERTY ('BuildClrVersion') is not null
    BEGIN 
    --PRINT 'Sql Server 2005 '
SET @returnValue= (SELECT SUBSTRING(physical_name, 1, CHARINDEX(@dbName+'.mdf', LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id = DB_ID(@dbName) AND file_id = 1)
    END 
ELSE 
    BEGIN 
    --PRINT 'Sql Server 2000'
SET @returnValue= (SELECT SUBSTRING(filename, 1, CHARINDEX(@dbName+'.mdf', LOWER(filename)) - 1) FROM master..sysaltfiles WHERE dbid = DB_ID(@dbName) AND fileid = 1)    
    END 
RETURN @returnValue     
END
使用： 注意項: (預設情況下,資料庫名與資料庫檔案相同名) 如有不同,可修改部分代碼後運行

SELECT dbo.ufnGetSysDBPath('master')

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/zhou__zhou/archive/2007/09/01/1768008.aspx