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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy