Common Database Management statements

Kill the database process, which is often used in the case of separate operations and also for deadlocks.

Code Segment 3: Force disconnect a user from a process (also commonly used in deadlocks ).
-- Kill the connection process of the database.
Declare @ dbname sysname
Set @ dbname = 'adirectory '-- Name of the database for which the process is to be disabled
Declare @ s nvarchar (1000)
Declare TB cursor local
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 the database process and copy the generated text for execution.
Select coalesce ('Kill ', '') + Cast (spid as varchar)
From master .. sysprocesses
Where dbid = db_id (@ dbname)

4. view the database deadlock process.

Declare @ spid int, @ BL int
Declare s_cur cursor
Select 0, blocked
From (select * from Master .. sysprocesses where blocked> 0)
Where not exists (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) +' causes database deadlock. The SQL statement executed by 'process No. 'is as follows'
Else
Select 'process No. spid: '+ Cast (@ spid as varchar (10) + 'process No. spid:' + Cast (@ BL as varchar (10 )) + 'blocking, the SQL syntax executed by the current process is as follows'
DBCC inputbuffer (@ BL)
Fetch next from s_cur into @ spid, @ BL
End
Close s_cur
Deallocate s_cur
3. Change Database sorting. Change Database sorting rules (the existing data is not changed)-change to Chinese sorting

Alter database dbname collate chinese_prc_ci_as

-- View All sorting

Select * From fn_helpcollations ()

 

2. view database connection process information

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. What is the current database doing? 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. Obtain the physical path of the database:

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
Usage: Note: (by default, the database name is the same as the database file name) If there is a difference, you can modify some code and run it.

Select DBO. ufngetsysdbpath ('master ')

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2007/09/01/1768008.aspx