bedbmsguru

CREATE procedure usp_get_mdf_size 
@dbname varchar(100)
AS
SET NOCOUNT ON;
DECLARE @sql_statement AS nvarchar(4000)

IF @dbname IS NULL  OR @dbname = ''
BEGIN 
SET @dbname = DB_NAME()
END

IF @dbname = 'ALL'
BEGIN
set @sql_statement = 
'
create TABLE #tab1 (
DBname VARCHAR(500) 
,filegroupname VARCHAR(500) 
,logical_filename VARCHAR(500) 
,physical_filename VARCHAR(500) 
,max_size_mb DECIMAL
,current_size_mb DECIMAL
,Space_Used_MB DECIMAL
,Available_Space_MB DECIMAL
);
DECLARE @sql_statement AS nvarchar(4000)
set @sql_statement =
' +CHAR(39) + '
USE [?]
IF ' +CHAR(39) +CHAR(39)+ '?' +CHAR(39) +CHAR(39) + ' NOT IN ('+CHAR(39) +CHAR(39) + 'master'+CHAR(39) +CHAR(39) + ',' +CHAR(39) +CHAR(39)+ 'tempDB'+CHAR(39) +CHAR(39) + ',' +CHAR(39) + CHAR(39)+ 'model'+ CHAR(39) +CHAR(39) + ',' +CHAR(39) +CHAR(39)+ 'msdb' +CHAR(39) +CHAR(39) + ')
insert  #tab1 (DBname, filegroupname ,logical_filename, physical_filename, max_size_mb, current_size_mb ,Space_Used_MB, Available_Space_MB)
SELECT 
db_name() as dbname
,b.groupname AS ' + char(39) + char(39) + ' File_Group' + char(39) + char(39) + Char(13) +'
,Name as logical_name
,[Filename]
,CONVERT(DECIMAL(15, 2), ROUND(a.maxsize / 128.000, 2)) [MAX_SIZE_MB]
,CONVERT(DECIMAL(15, 2), ROUND(a.Size / 128.000, 2)) [CURRENT_SIZE_MB]
,CONVERT(DECIMAL(15, 2), ROUND(FILEPROPERTY(a.Name, '+ CHAR(39) + char(39) + 'SpaceUsed'+ CHAR(39) + char(39) + ') / 128.000, 2)) AS [Space_Used_MB]
,CONVERT(DECIMAL(15, 2), ROUND((a.maxsize - FILEPROPERTY(a.Name, '+CHAR(39)+ char(39) + 'SpaceUsed'+CHAR(39) + char(39) + ')) / 128.000, 2)) AS [Available_Space_MB]
FROM dbo.sysfiles a(NOLOCK)
JOIN sysfilegroups b(NOLOCK) ON a.groupid = b.groupid
' + CHAR(39) +'
EXEC sp_MSforeachdb @sql_statement
select DBname,case max_size_mb when 0 then '+ char(39) + '제한없음'+ char(39) +' else CAST (max_size_mb AS VARCHAR(50)) END [MAX_SIZE_MB]
,  current_size_mb 
,Space_Used_MB
, CASE WHEN Available_Space_MB  < 1 THEN '+ char(39) + '제한없음'+ char(39) +' else CAST (Available_Space_MB AS VARCHAR(50)) END Available_Space_MB
, case max_size_mb when 0 then  -1  else CONVERT(DECIMAL(15, 2), ROUND((Available_Space_MB / max_size_mb) * 100, 2)) END Available_percent
, filegroupname 
,logical_filename
, physical_filename
 from #tab1 
 WHERE DBname NOT IN(select name from sys.databases where is_read_only=1)
 order BY Available_percent 
drop table #tab1
'

END
ELSE
BEGIN 
set @sql_statement = 
'
USE ' + @dbname + Char(13) +'
SELECT 
 db_name() as dbname
,case a.maxsize when -1 then '+ char(39) + '제한없음'+ char(39) +' else CAST (CONVERT(DECIMAL(15, 2), ROUND(a.maxsize / 128.000, 2)) AS VARCHAR(50)) END [MAX_SIZE_MB]
,CONVERT(DECIMAL(15, 2), ROUND(a.Size / 128.000, 2)) [CURRENT_SIZE_MB]
,CONVERT(DECIMAL(15, 2), ROUND(FILEPROPERTY(a.Name, ' + char(39) + 'SpaceUsed' + char(39) + ') / 128.000, 2)) AS [Space_Used_MB]
,CASE WHEN a.maxsize < 1 THEN '+ char(39) + '제한없음'+ char(39) +' else CAST(CONVERT(DECIMAL(15, 2), ROUND((a.maxsize - FILEPROPERTY(a.Name, '+CHAR(39) + 'SpaceUsed'+CHAR(39) + ')) / 128.000, 2)) AS VARCHAR(50)) END AS [Available_Space_MB]
,CASE WHEN a.maxsize < 1 THEN 100 ELSE CONVERT(DECIMAL(15, 2), ROUND((((a.MAXSize - FILEPROPERTY(a.Name, '+CHAR(39) + 'SpaceUsed'+ CHAR(39) + ')) / 128.000) / (a.maxsize / 128.000)) * 100, 2)) END AS Available_percent
,b.groupname AS ' + char(39)  + ' File_Group'  + char(39) + Char(13) +'
,Name as logical_name
,[Filename]
FROM dbo.sysfiles a(NOLOCK)
JOIN sysfilegroups b(NOLOCK) ON a.groupid = b.groupid
' 
END
EXECUTE sp_executesql @sql_statement
--PRINT @sql_statement

create procedure usp_get_ldf_size
AS 
SET NOCOUNT ON;
SELECT name
,db.log_reuse_wait_desc
,format(ls.cntr_value / 1024, '#,###') AS size_mb
,lu.cntr_value / 1024 AS used_mb
,format(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) * 100, '###0.##') AS used_percent
,CASE 
WHEN CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) > .5
THEN CASE 
/* tempdb special monitoring */
WHEN db.name = 'tempdb'
AND log_reuse_wait_desc NOT IN (
'CHECKPOINT'
,'NOTHING'
)
THEN 'WARNING'
/* all other databases, monitor foor the 50% fill case */
WHEN db.name <> 'tempdb'
THEN 'WARNING'
ELSE 'OK'
END
ELSE 'OK'
END AS log_status
FROM sys.databases db
JOIN sys.dm_os_performance_counters lu ON db.name = lu.instance_name
JOIN sys.dm_os_performance_counters ls ON db.name = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE 'Log File(s) Size (KB)%'
ORDER BY name

CREATE procedure usp_get_runsql 
as
SELECT TOP 1000 s.session_id,
           r.status,
    wt.wait_duration_ms,
            wt.wait_type,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           DB_NAME(r.database_id) dbname,
           s.login_name,
    SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
   s.host_name,
           s.program_name,
           s.last_request_end_time,
   r.open_transaction_count,
           s.login_time
  
           
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
 JOIN  sys.dm_os_waiting_tasks wt
 ON r.session_id = wt.session_id
 CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC