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
'SQL SERVER' 카테고리의 다른 글
파티션 함수 소스 생성 (0) | 2024.02.03 |
---|---|
실행중인 SQL확인 (0) | 2023.09.11 |
Agent Job 실패확인 (0) | 2023.09.11 |
synonym의 base object 조회 (0) | 2022.12.15 |
전체Table Size 확인 (0) | 2022.11.08 |
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
'SQL SERVER' 카테고리의 다른 글
파티션 함수 소스 생성 (0) | 2024.02.03 |
---|---|
전체DB들의 LDF SIZE확인 (0) | 2023.09.11 |
Agent Job 실패확인 (0) | 2023.09.11 |
synonym의 base object 조회 (0) | 2022.12.15 |
전체Table Size 확인 (0) | 2022.11.08 |