---數據庫當前版本
select @@version
---數據庫實例
select @@SERVERNAME
---數據庫字符集
sp_helpsort
---操作系統(tǒng)參數
exec master..xp_msver
---數據庫參數
exec sp_configure
SELECT * FROM sys.configurations;
---查看數據庫運行時間
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1;
---數據庫用戶的登錄信息
exec sp_helplogins
---生產庫數據庫大小
select 'use ' +name from sysdatabases;
exec sp_spaceused;
---數據庫狀態(tài)
exec sp_helpdb;
---事務日志
dbcc sqlperf(logspace);
---活動進程
exec sp_who 'active'
---鎖資源使用情況
select str(request_session_id, 4, 0) as spid,
resource_database_id as DB_Id,
case when resource_database_id = db_id() and resource_type = 'OBJECT'
then convert(char(20), object_name(resource_Associated_Entity_id))
else convert(char(20), resource_Associated_Entity_id)
end as object,
convert(varchar(12), resource_type) as resrc_type,
convert(varchar(12), request_type) as req_type,
convert(char(3), request_mode) as mode,
convert(varchar(8), request_status) as status
from sys.dm_tran_locks
order by request_session_id desc;
---檢查數據庫完整性
dbcc checkdb
SELECT? [total_physical_memory_kb]/1024 as [Physical Memory_MB],?
[available_physical_memory_kb]/1024 as [Available_Memory_MB]?
FROM? ? sys.dm_os_sys_memory;
select physical_io,cpu,* from sys.sysprocesses
---sqlserver I/O使用情況
select
? @@total_read as '讀取磁盤的次數',
? -@@total_write as '寫入磁盤的次數',
? @@ERROR as '磁盤寫入錯誤數',
? getdate() as '當前時間'
? ---sqlserver cpu使用率
? select
? @@cpu_busy,--自上次啟動以來的工作時間
? @@timeticks as '每個時鐘周期對應的微秒數', --每個時鐘周期對應的微秒數
? @@cpu_busy*cast(@@timeticks as float)/1000 as 'cpu工作時間(秒)',
? -@@IDLE*cast(@@timeticks as float)/1000 as 'CPU空閑時間(秒)',
? getdate() as '當前時間'
? ---查看sql server內存使用情況
? SELECT object_name,counter_name,cntr_value,cntr_value/1024/1024 AS [cntr_value(GB)]?
FROM sys.dm_os_performance_counters?
WHERE counter_name in('Target Server Memory (KB)','Total Server Memory (KB)','Database pages')
select *? FROM sys.dm_os_performance_counters?
where counter_name in('Max memory (KB)','Used memory (KB)','Target memory (KB)')
++++++++++++++++++++++++++++++++++++++++++++++++++++
----查看數據庫服務器各數據庫日志文件的大小及利用率/狀態(tài)
DBCC SQLPERF(LOGSPACE)
----查看日志：
exec xp_readerrorlog
