December 3, 2015 at 10:32 am
Comments posted to this topic are about the item Check FileSize and LogUsage for all DBs
December 4, 2015 at 11:21 am
Thanks for the script can it be changed to show result in MB and GB also?
December 14, 2015 at 6:12 am
Dear great script, I would like to show what I use, it is possible to send e-mail, very useful every day when I come I have a vision of how's database, below.:-)
Use master
declare @LogSpace table
(
DB varchar(255),
LogSizeMB int,
PercentUsed float,
Status int
);
insert into @LogSpace
execute('DBCC SqlPerf(logspace)');
SELECT *
FROM @LogSpace
ORDER By LogSizeMB desc;
----------------------------------------------
-- (I) Pegar as linhas que aparecero no email
----------------------------------------------
declare @LogSpace2 table
(
DB varchar(255),
LogSizeMB int,
PercentUsed float,
Status int,
nOrdem int identity(1,1)
);
insert into @LogSpace2 (DB, LogSizeMB, PercentUsed, Status)
SELECT DB, LogSizeMB, PercentUsed, Status
FROM @LogSpace
ORDER By LogSizeMB desc;
declare
@nOrdem int=1,
@nOrdemMax int=0,
@DB varchar(255),
@LogSizeMB varchar(255),
@PercentUsed varchar(255),
@status varchar(255),
@Dados NVARCHAR(MAX)=''
select @nOrdemMax = max(nOrdem) from @LogSpace2
while @nOrdem <= @nOrdemMax
begin
select @DB = t.DB,
@LogSizeMB = convert(varchar(200), t.LogSizeMB),
@PercentUsed = convert(varchar(200), t.PercentUsed),
@status = convert(varchar(200), t.Status)
from @LogSpace2 t
where t.nOrdem = @nOrdem
set @Dados = @Dados + '<tr><td>'+ @DB +'</td><td>'+ @LogSizeMB +'</td><td>'+ @PercentUsed +'</td><td>'+ @status +'</td></tr>'
set @nOrdem = @nOrdem +1
end
----------------------------------------------
-- (F) Pegar as linhas que aparecero no email
----------------------------------------------
Begin
DECLARE
@tableHTML NVARCHAR(MAX),
@Assunto NVARCHAR(MAX)
SET @Assunto= ' Status Log Diario!!!!!Tamanho de Log Diario'
SET @tableHTML =
N'<font color="red">' +
N'CONTROLE DO TAMANHO DE ARQUIVO DE LOG DAS BASES PORTAL!!!.
' +
N'<table>' +
N'<tr><td>DB</td><td>LogSizeMB</td><td>PercentUsed</td><td>Status</td></tr>' +
@Dados +
N'</table>' +
N'
' +
N'</font>';
-----------------
-- Enviar Email
-----------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'LogDiario',
@recipients = 'xxxx@xxxx.com.br;',
@copy_recipients = 'xxx@xxxxxxxx.com.br;xxxxx@xxxxx.com.br',
@subject = @Assunto,
@body = @tableHTML ,
@body_format = 'HTML',
@importance = 'High'
End
Go
December 14, 2015 at 6:27 am
Hi,
if you want to change values to MB, you have to modify the first Select clause like this :
"SELECT instance_name AS DatabaseName,
[Data File(s) Size (KB)]/1024 as [Data File(s) Size (MB)],
[LOG File(s) Size (KB)]/1024 as [LOG File(s) Size (MB)],
[Log File(s) Used Size (KB)]/1024 as [Log File(s) Used Size (MB)],
[Percent Log Used]
FROM ...."
And also replace 1024 by (1024*1024) for GB and so on...
December 14, 2015 at 6:34 am
Thanks for the reply with mail feature.
Probably it's easier to active Data Collection for a daily report of file usage, or to see the growth of user’s databases.
By the way, well done!
December 14, 2015 at 6:38 am
thank you! backbone57
December 14, 2015 at 6:45 am
Thanks for the nice script.
December 14, 2015 at 7:12 am
Great script!!! Thanks for sharing! : )
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply