--Size Tables
create table #TableSize (
Name varchar(255),
[rows] int,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255))
create table #ConvertedSizes (
Name varchar(255),
[rows] int,
reservedKb int,
SizeKB FLOAT,
SizeMB FLOAT,
SizeGB FLOAT,
reservedIndexSize int,
reservedUnused int)
EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb,SizeKB,SizeMB, SizeGB, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
(CAST(SUBSTRING(data, 0, LEN(data)-2) AS FLOAT)/1024),
round((CAST(SUBSTRING(data, 0, LEN(data)-2) AS FLOAT)/1024)/1024,2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize
select * from #ConvertedSizes
--WHERE Size > 0.1
order by SizeGB desc
drop table #TableSize
drop table #ConvertedSizes
--- Size Bases
with fs
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
name as NomeDoBanco,
((select sum(size) from fs where type = 0 and fs.database_id = db.database_id)/1024) DataFileSizeGb,
((select sum(size) from fs where type = 1 and fs.database_id = db.database_id)/1024) LogFileSizeGB
into #tmp_database
from sys.databases db
select
*
from #tmp_database
order by DataFileSizeGb desc
drop table #tmp_database
-- Valida Discos do Servidor
SELECT DISTINCT
@@servername as "Servidor",
QUOTENAME(CAST(CONNECTIONPROPERTY('local_net_address') AS VARCHAR)) AS "IP",
VS.volume_mount_point [Montagem]
,VS.logical_volume_name AS [Volume]
,CAST(CAST(VS.total_bytes AS DECIMAL(19,2))/1024 /1024 /1024 AS DECIMAL (10,2)) AS [Total (GB)]
,CAST(CAST(VS.available_bytes AS DECIMAL(19,2))/1024 /1024 /1024 AS DECIMAL (10,2)) AS [Espaço Disponível (GB)]
,CAST((CAST(VS.available_bytes AS DECIMAL(19,2)) / CAST(VS.total_bytes AS DECIMAL(19,2)) * 100 ) AS DECIMAL(10,2)) AS [Espaço Disponível ( % )]
,CAST((100 - CAST(VS.available_bytes AS DECIMAL(19,2)) / CAST(VS.total_bytes AS DECIMAL(19,2)) * 100) AS DECIMAL (10,2)) AS [Espaço em uso ( % )]
FROM sys.master_files AS MF
CROSS APPLY [sys].[dm_os_volume_stats](MF.database_id, MF.FILE_ID) AS VS
-- Consulta Backups
SELECT TOP 100 s.database_name As NomeDobanco,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS TipoDeBackup,
m.physical_device_name AS CaminhoFisicoDoArquivo,
Cast(Cast(s.backup_size / 1000000 AS INT) AS VARCHAR(14))
+ ' ' + 'MB' AS TamanhoDoBackup,
Cast(Datediff(second, s.backup_start_date, s.backup_finish_date)
AS VARCHAR(4))
+ ' ' + 'Segundos' AS TempoDoBackup,
s.backup_start_date AS DataInicioDoBackup,
s.server_name AS Servidor,
s.recovery_model ,*
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
where s.[type] = 'D'
ORDER BY backup_start_date DESC,
backup_finish_date
-- Alocação TEMPDB
SELECT A.SESSION_ID,
B.HOST_NAME,
B.LOGIN_NAME ,
(USER_OBJECTS_ALLOC_PAGE_COUNT + INTERNAL_OBJECTS_ALLOC_PAGE_COUNT)*1.0/128 AS TOTALALOCADOMB,
D.TEXT
FROM SYS.DM_DB_SESSION_SPACE_USAGE A
JOIN SYS.DM_EXEC_SESSIONS B ON A.SESSION_ID = B.SESSION_ID
JOIN SYS.DM_EXEC_CONNECTIONS C ON C.SESSION_ID = B.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) AS D
WHERE (USER_OBJECTS_ALLOC_PAGE_COUNT + INTERNAL_OBJECTS_ALLOC_PAGE_COUNT)*1.0/128 > 10 -- OCUPAM MAIS DE 100 MB
ORDER BY TOTALALOCADOMB DESC
-- Tempo Ligado
SET NOCOUNT
ON DECLARE @crdate DATETIME,
@hr VARCHAR(50),
@min VARCHAR(5) SELECT
@crdate=crdate
FROM
sysdatabases
WHERE
NAME='tempdb' SELECT
@hr=(DATEDIFF ( mi,
@crdate,
GETDATE()))/60 IF ((DATEDIFF ( mi,
@crdate,
GETDATE()))/60)=0 SELECT
@min=(DATEDIFF ( mi,
@crdate,
GETDATE()))
ELSE SELECT
@min=(DATEDIFF ( mi,
@crdate,
GETDATE()))-((DATEDIFF( mi,
@crdate,
GETDATE()))/60)*60 PRINT 'O SQL Server "' + CONVERT(VARCHAR(20),
SERVERPROPERTY('SERVERNAME'))+'" esta online a '+@hr+' horas e '+@min+' minutos.'
-- Ratio de Memoria
select
(select cntr_value from sys.dm_os_performance_counters
where object_name like'%Memory Manager%'and counter_name like'Maximum Workspace Memory (KB)%')as'Maximum Workspace Memory (KB)',
(select cntr_value
from sys.dm_os_performance_counters
where object_name like'%Memory Manager%'and counter_name like'Target Server Memory (KB)%')as'Target Server Memory (KB)',
( select cntr_value from sys . dm_os_performance_counters
where object_name like'%Memory Manager%'and counter_name like'Maximum Workspace Memory (KB)%')*100.0/
( select cntr_value from sys . dm_os_performance_counters
where object_name like'%Memory Manager%'and counter_name like'Target Server Memory (KB)%')as Ratio
-- Consumo CPU
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
AS [SQLProcessUtilization], [timestamp]
FROM (SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%') AS x) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
