SQL Server 一些常用操作

• 查看数据库连接情况

SELECT * FROM master.dbo.sysprocesses WHERE  dbid = DB_ID('COMS');

 

• 查看数据库允许的最大连接

SELECT @@MAX_CONNECTIONS;

 

• 查看数据库启动时间

SELECT  sqlserver_start_time FROM    sys.dm_os_sys_info
SELECT  crdate FROM    master..sysdatabases WHERE name = 'tempdb'
SELECT  login_time FROM    sysprocesses WHERE spid = 1

 

• 查看数据库自上次启动以来的连接次数

SELECT @@CONNECTIONS;

          

• 关闭连接（上面的查询可以得到spid，根据spid，关闭进程就可以了。）

kill 54

 

• 查询死锁

SELECT  request_session_id spid ,
        OBJECT_NAME(resource_associated_entity_id) tableName
FROM    sys.dm_tran_locks
WHERE   resource_type = 'OBJECT'; 

 

• 杀死死锁进程

KILL 354; 

 

•  显示死锁相关信息

EXEC sp_who2 354;

 

• 数据库显示“正在还原”，解决方案

RESTORE DATABASE DBName  WITH RECOVERY

 

• 查询数据库表和表行数

SELECT  a.name ,
        b.rows
FROM    sysobjects AS a
        INNER JOIN sysindexes AS b ON a.id = b.id
WHERE   ( a.type = 'u' )
        AND ( b.indid IN ( 0, 1 ) )
ORDER BY b.rows DESC