Volumetria Base de Dados

22/10/2013

0

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/

Simone

Simone

Responder

Posts

22/10/2013

Alex Lekao

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
Responder

22/10/2013

Roniere Almeida

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.
Responder

22/10/2013

Jefferson Santos

Verificar espaço em Unidade

begin   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 
Responder

22/10/2013

Jefferson Santos

Verifica Ultimo Restore

USE 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
Responder

22/10/2013

Jefferson Santos

Verifica Ultimo Backup retirado

select 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
Responder

22/10/2013

Simone

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.
Responder

22/10/2013

Alex Lekao

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.
Responder

05/11/2013

Roniere Almeida

muito bom os scripts, copiando para alguma eventualidade.
Responder

Que tal ter acesso a um e-book gratuito que vai te ajudar muito nesse momento decisivo?

Ver ebook

Recomendado pra quem ainda não iniciou o estudos.

Eu quero
Ver ebook

Recomendado para quem está passando por dificuldades nessa etapa inicial

Eu quero

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar