Situatie
Mai jos este o procedura ce returneaza informatii despre serverul MSSQL pe care este rulata, cum ar fi:
- numele serverlui sql
- versiunea
- product level
- editia
- numele bazelor
- ownerul fiecarei baze
- informatii despre fisierele fiecarei baze
- numele instantei
- numele masinii
Solutie
create procedure CollectSqlServerInfo_prc
as
select getdate() Date_Collected
,serverproperty(‘MachineName’) ‘Machine_Name’
,isnull(serverproperty(‘InstanceName’),’mssqlserver’) ‘Instance_Name’
,@@SERVERNAME ‘Sql_Server_Name’
,SERVERPROPERTY(‘productversion’) Product_Version
,SERVERPROPERTY (‘productlevel’) Product_Level
,SERVERPROPERTY (‘edition’) ‘Edition’
,d.name ‘database_name’
,suser_sname(d.owner_sid) ‘owner’
,ls.cntr_value as [log_size_kb]
,lu.cntr_value as [log_used_kb]
,lp.cntr_value as [percent_log_used]
,ds.cntr_value as [data_files_size_kb]
from sys.databases d
left outer join sys.dm_os_performance_counters as lu on lu.instance_name=d.name and lu.counter_name like N’Log File(s) Used Size (KB)%’
left outer join sys.dm_os_performance_counters as ls on ls.instance_name=d.name and ls.counter_name like N’Log File(s) Size (KB)%’ and ls.cntr_value > 0
left outer join sys.dm_os_performance_counters as lp on lp.instance_name=d.name and lp.counter_name like N’Percent Log Used%’
left outer join sys.dm_os_performance_counters as ds on ds.instance_name=d.name and ds.counter_name like N’Data File(s) Size (KB)%’
order by d.name
Leave A Comment?