Array
(
)

Volumetria Base de Dados

Dbajr
   - 22 out 2013

Oi pessoal,
Alguem já precisou fazer a estimativa de crescimento da base?
Pesquisei bastante, mas só achei algo meio genérico e não entendi muito bem.
Eu usei esses script's, mas não sei se isso é o suficiente.
-- Retorna o tamanho da base
SELECT DB.name, SUM(size) AS Tamanho FROM sys.databases DB
INNER JOIN sys.master_files
ON DB.database_id = sys.master_files.database_id
GROUP BY DB.name

-- Retorna a quantidade de coluna de cada tabela e o tamanho maximo.
select o.name
, count(*) Colunas
, sum(case when max_length = -1 then 0 else max_length end) TamanhoMax
from sys.objects o
join sys.columns c
on o.object_id = c.object_id
where o.type ='U'
and is_ms_shipped = 0
group by o.name
e criei essa procedure
https://rodrigoesilva.wordpress.com/2012/10/10/dica-como-calcular-tamanho-do-banco-de-dados/

Alex Lekao
   - 22 out 2013

Oi Simone, Bom dia!!!

Acho interessante acompanhar o crescimento sim.

Se nao me engano ja houve algumas discursoes aqui no forum a respeito de monitorar o tamanho das base e houveram algumas dicas.

Eu sugiro verificar os blog dos Junior Galvao http://pedrogalvaojunior.wordpress.com/ , muuito bom, e acho que deve ter alguma sugestao la, e do Gustavo Maia http://gustavomaiaaguiar.wordpress.com/, SQL From Hell, e outros.

Espero ter ajudado.

Abraco.

Alex - Lekao

Roniere Almeida
   - 22 out 2013

acompanhando o post, essa questão de volume do banco de dados me interessa. esse SQL From Hell ja tinha add nos meus favoritos, esse outro não conhecia.

Jefferson Santos
   - 22 out 2013

Verificar espaço em Unidade

#Códigobegin set nocount on
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#dbfileinfo')) begin drop table #dbfileinfo end
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#logsizestats')) begin drop table #logsizestats end
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#datafilestats')) begin drop table #datafilestats end
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#fixeddrives')) begin drop table #fixeddrives end
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#usados')) begin drop table #usados end
create table #fixeddrives ( DriveLetter varchar(10), MB_Free dec(20, 2) )
create table #datafilestats ( DBName varchar(255), DBId int, FileId tinyint, [FileGroup] tinyint, TotalExtents dec(20, 2), UsedExtents dec(20, 2), [Name] varchar(255), [FileName] varchar(400) )
create table #logsizestats ( DBName varchar(255) not null primary key clustered, DBId int, LogFile real, LogFileUsed real, Status bit ) create table #dbfileinfo ( [ServerName] varchar(255), [DBName] varchar(65), [LogicalFileName] varchar(400), [UsageType] varchar (30), [Size_MB] dec(20, 2), [SpaceUsed_MB] dec(20, 2), [MaxSize_MB] dec(20, 2), [NextAllocation_MB] dec(20, 2), [GrowthType] varchar(65), [FileId] smallint, [GroupId] smallint, [PhysicalFileName] varchar(400), [DateChecked] datetime )
declare @SQLString varchar(3000) declare @MinId int declare @MaxId int declare @DBName varchar(255) declare @tblDBName table ( RowId int identity(1, 1), DBName varchar(255), DBId int)
insert into @tblDBName (DBName, DBId) select [Name], DBId from master..sysdatabases where ( Status & 512 ) = 0 order by [Name]
insert into #logsizestats (DBName, LogFile, LogFileUsed, Status) exec ('dbcc sqlperf(logspace) with no_infomsgs')
update #logsizestats set DBId = db_id(DBName)
insert into #fixeddrives exec master..xp_fixeddrives
select @MinId = min(RowId), @MaxId = max(RowId) from @tblDBName
while ( @MinId <= @MaxId ) begin select @DBName = [DBName] from @tblDBName where RowId = @MinId
select @SQLString = 'SELECT ServerName = @@SERVERNAME,' + ' DBName = ''' + @DBName + ''',' + ' LogicalFileName = [name],' + ' UsageType = CASE WHEN (64&[status])=64 THEN ''Log'' ELSE ''Data'' END,' + ' Size_MB = [size]*8/1024.00,' + ' SpaceUsed_MB = NULL,' +
' MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE maxsize/1024.00*8 END,'+
' NextExtent_MB = CASE WHEN (1048576&[status])=1048576 THEN ([growth]/100.00)*([size]*8/1024.00) WHEN [growth]=0 THEN 0 ELSE [growth]*8/1024.00 END,'+ ' GrowthType = CASE WHEN (1048576&[status])=1048576 THEN ''%'' ELSE ''Pages'' END,'+ ' FileId = [fileid],' + ' GroupId = [groupid],' + ' PhysicalFileName= [filename],' + ' CurTimeStamp = GETDATE()' +
'FROM [' + @DBName + ']..sysfiles'
print @SQLString
insert into #dbfileinfo exec (@SQLString)
update #dbfileinfo set SpaceUsed_MB = Size_MB / 100.0 * (select LogFileUsed from #logsizestats where DBName = @DBName) where UsageType = 'Log' and DBName = @DBName
select @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'
insert #datafilestats (FileId, [FileGroup], TotalExtents, UsedExtents, [Name], [FileName]) execute(@SQLString)
update #dbfileinfo set [SpaceUsed_MB] = S.[UsedExtents] * 64 / 1024.00 from #dbfileinfo as F inner join #datafilestats as S on F.[FileId] = S.[FileId] and F.[GroupId] = S.[FileGroup] and F.[DBName] = @DBName
truncate table #datafilestats
select @MinId = @MinId + 1 end
select @@servername as servidor, substring(A.PhysicalFileName, 1, 1) as unidad, sum ([Size_MB]) as SqlTotalDB, sum([SpaceUsed_MB]) as SqlTotalUsedSpaceDB, sum (( [Size_MB] ) - ( [SpaceUsed_MB] ))as SQLTotalFreeSpaceDB into #usados from #dbfileinfo as A left join #fixeddrives as B on substring(A.PhysicalFileName, 1, 1) = B.DriveLetter group by substring(A.PhysicalFileName, 1, 1)
select servidor, DriveLetter, MB_Free as RealMb_free, MB_Free + SQLTotalFreeSpaceDB as MB_FreeNeto, SqlTotalDB, abs(( SqlTotalDB - SQLTotalFreeSpaceDB )) as SQLTotalUsedSpaceDB, SQLTotalFreeSpaceDB, ( 100 * abs(( SqlTotalDB - SQLTotalFreeSpaceDB )) ) / SqlTotalDB as Porcentaje_Uso_DB
from #fixeddrives as f inner join #usados as z on z.unidad = f.DriveLetter
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#dbfileinfo')) begin drop table #dbfileinfo end
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#logsizestats')) begin drop table #logsizestats end
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#datafilestats')) begin drop table #datafilestats end
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#fixeddrives')) begin drop table #fixeddrives end
if exists (select 1 from tempdb..sysobjects where [Id] = object_id('tempdb..#usados')) begin drop table #usados end
set nocount off
end

Jefferson Santos
   - 22 out 2013

Verifica Ultimo Restore

#CódigoUSE msdb ;
SELECT
DISTINCT
DBRestored = destination_database_name ,
RestoreDate = restore_date ,
SourceDB = b.database_name ,
BackupDate = backup_start_date
FROM RestoreHistory h
JOIN MASTER..sysdatabases sd ON sd.name = h.destination_database_name
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
GROUP BY destination_database_name ,
restore_date ,
b.database_name ,
backup_start_date
ORDER BY RestoreDate DESC
GO

Jefferson Santos
   - 22 out 2013

Verifica Ultimo Backup retirado

#Códigoselect a.name, case b.type
when 'D' then 'Full Database Backup'
when 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'Log Backup'
END AS Backup_Type,
max(b.backup_finish_date) LastSuccessfulBackup,
cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) as 'IntervalInDays',
case
--when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 1 then cast('Completed' as varchar(10))
--when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 7 then cast('Failed' as varchar(10))
when datediff(hh,max(b.backup_finish_date),getdate()) > 1 then cast('Completed Full BKP' as varchar(30))
when datediff(hh,max(b.backup_finish_date),getdate()) < 1 then cast('Failed Diff BKP' as varchar(30))
End as completion_Status,
case
when (max(b.backup_finish_date) is NULL )then 'Backup Failed-no Data Found'
end as backup_status_data_not_found
from master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b
ON a.name = b.database_name
where a.name not in ('tempdb') and b.type = 'D'
group by a.name, b.type
order by a.name, b.type

Dbajr
   - 22 out 2013

Alex, obrigada, vou dar uma olhada.

Jefferson, achei muito interessante esses script's, é sempre bom acompanhar essas coisas.
Mais não é bem isso que preciso, mas muito obrigada, já salvei os scripts e certeza que vou utiliza-los.

Alex Lekao
   - 22 out 2013


Citação:
Alex, obrigada, vou dar uma olhada.

Jefferson, achei muito interessante esses script's, é sempre bom acompanhar essas coisas.
Mais não é bem isso que preciso, mas muito obrigada, já salvei os scripts e certeza que vou utiliza-los.


Disponha...

precisando eh so falar.

Roniere Almeida
   - 05 nov 2013

muito bom os scripts, copiando para alguma eventualidade.