如何取得数据库自身的信息

[fffddd]

比如启动时间呀cpu使用率呀，内存使用情况呀等等，越多越好！

[dobear_0922]

学习，，，

[elvis_gao]

学习

[火星求索]

select suser_sname(),@@servername,host_name(),host_id()

[duanzhi1984]

系统中的表主要都是在master 表中，有表的结构，字段，系统信息表

你可以把这些表都看看，再看看联机帮助应当进步很快的

[-狙击手-]

嘿嘿，学习sp_monitor

[meiZiNick]

以后需再关注，现在先帮你顶一下

[林g]

up

[stromboy007]

up

[iamme81]

sysperfinfo 这个系统表 中的项众多,能不能挑重点的讲下

[iamme81]

[Quote=引用 3 楼 gc_ding 的回复:]
--得到数据库中所有表的空间/记录情况
exec sp_MSForEachTable
@precommand=N '
create table ##(
id int identity,
表名 sysname,
字段数 int,
记录数 int,
保留空间 Nvarchar(10),
使用空间 varchar(10),
索引使用空间 varchar(10),
未用空间 varchar(10)) ',
@command1=N 'insert ##(表名,记录数,保留空间,使用空间,索引使用空间,未用空间) exec sp_spaceused ' '? ' '

upda…
[/Quote]

这个好象不对

[正宗老冉]

强帖,收藏.

[Andy-W]

SELECT
S.session_id, R.blocking_session_id,
S.host_name, S.login_name, S.program_name,
S.status,
S.cpu_time, memory_usage_kb = S.memory_usage * 8, S.reads, S.writes,
S.transaction_isolation_level,
C.connect_time, C.last_read, C.last_write,
C.net_transport, C.client_net_address, C.client_tcp_port, C.local_tcp_port,
R.start_time, R.command, R.status,
R.wait_time, R.wait_type, R.last_wait_type, R.wait_resource,
R.open_transaction_count, R.transaction_id,
current_execute_sql = SUBSTRING(T.text,
R.statement_start_offset / 2 + 1,
CASE
WHEN statement_end_offset = -1 THEN LEN(T.text)
ELSE (R.statement_end_offset - statement_start_offset) / 2+1
END)
FROM sys.dm_exec_sessions S
LEFT JOIN sys.dm_exec_connections C
ON S.session_id = C.session_id
LEFT JOIN sys.dm_exec_requests R
ON S.session_id = R.session_id
AND C.connection_id = R.connection_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) T
WHERE S.is_user_process = 1 -- 如果不限制此条件，则查询所有进程（系统和用户进程）



--From 邹老大的《SQL2005深入浅出》

[Andy-W]

-- ===========================================
-- 获取阻塞的 session_id 及阻塞时间
DECLARE @tb_block TABLE(
top_blocking_session_id smallint,
session_id smallint,
blocking_session_id smallint,
wait_time int,
Level int,
blocking_path varchar(8000),
PRIMARY KEY(
session_id, blocking_session_id)
)
INSERT @tb_block(
session_id,
blocking_session_id,
wait_time)
SELECT
session_id,
blocking_session_id,
wait_time = MAX(wait_time)
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
GROUP BY session_id, blocking_session_id

-- ===========================================
-- 处理阻塞的 session_id 之间的关系
DECLARE
@Level int
SET @Level = 1

INSERT @tb_block(
session_id, top_blocking_session_id, blocking_session_id,
Level, blocking_path)
SELECT DISTINCT
blocking_session_id, blocking_session_id, 0,
@Level, RIGHT(100000 + blocking_session_id, 5)
FROM @tb_block A
WHERE NOT EXISTS(
SELECT * FROM @tb_block
WHERE session_id = A.blocking_session_id)
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level = @Level + 1
UPDATE A SET
top_blocking_session_id = B.top_blocking_session_id,
Level = @Level,
blocking_path = B.blocking_path
+ RIGHT(100000 + A.session_id, 5)
FROM @tb_block A, @tb_block B
WHERE A.blocking_session_id = B.session_id
AND B.Level = @Level - 1
END

-- ===========================================
-- 如果只要显示阻塞时间超过多少毫秒的记录，可以在这里做一个过滤
-- 这里假设阻塞时间必须超过 1 秒钟(1000毫秒)
DELETE A
FROM @tb_block A
WHERE NOT EXISTS(
SELECT * FROM @tb_block
WHERE top_blocking_session_id =A.top_blocking_session_id
AND wait_time >= 1000)

-- ===========================================
-- 使用 DBCC INPUTBUFFER 获取阻塞进程的 T-SQL 脚本
DECLARE @tb_block_sql TABLE(
id int IDENTITY,
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000),
session_id smallint)
DECLARE
@session_id smallint
DECLARE tb CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT DISTINCT
session_id
FROM @tb_block
OPEN tb
FETCH tb INTO @session_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @tb_block_sql(
EventType, Parameters, EventInfo)
EXEC(N'DBCC INPUTBUFFER(' + @session_id + ') WITH NO_INFOMSGS')
IF @@ROWCOUNT > 0
UPDATE @tb_block_sql SET
session_id = @session_id
WHERE IDENTITYCOL = @@IDENTITY

FETCH tb INTO @session_id
END
CLOSE tb
DEALLOCATE tb

-- ===========================================
-- 显示阻塞进程信息
;WITH
BLK AS(
SELECT
A.top_blocking_session_id,
A.session_id,
A.blocking_session_id,
A.Level,
A.blocking_path,
SQL = B.EventInfo
FROM @tb_block A
LEFT JOIN @tb_block_sql B
ON A.session_id = B.session_id
)
SELECT
-- BlockPath = REPLICATE(' ', Level * 2 - 2)
-- + '|-- '
-- + RTRIM(session_id),
BLK.top_blocking_session_id,
BLK.session_id,
BLK.blocking_session_id,
BLK.Level,
wait_type = P.waittype,
wait_time = P.waittime,
last_wait_type = P.lastwaittype,
wait_resource = P.waitresource,
P.login_time,
P.last_batch,
P.open_tran,
P.status,
host_name = P.hostname,
P.program_name,
P.cmd,
login_name = P.loginame,
BLK.SQL,
current_sql = T.text,
current_run_sql = SUBSTRING(T.text,
P.stmt_start / 2 + 1,
CASE
WHEN P.stmt_end = -1 THEN LEN(T.text)
ELSE (P.stmt_end - P.stmt_start) / 2+1
END)
FROM BLK
-- 简省代码起见，直接引用 sysprocess, 读者可以改为引用前述介绍的“查询进程"的脚本进行替换
INNER JOIN master.dbo.sysprocesses P
ON BLK.session_id = P.spid
OUTER APPLY sys.dm_exec_sql_text(P.sql_handle) T
ORDER BY BLK.top_blocking_session_id, BLK.blocking_path

--From 邹老大的《SQL2005深入浅出

[中国风]

05：sys.dm开头的动态视图，可看到当前数据库的使用状态

[ruihuahan]

操作系统的 performance monitor 也可以的。

[buttonzhou]

关注,学习,接分

[fangyu01060426]

UP

[duanzhi1984]

你这个帖子够牛，
已经一年多了， 还没结啊

[fcuandy]

sf

[chenjunsheep]

这个有意思了，马克了