Volumetria Base de Dados
22/10/2013
0
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
Posts
22/10/2013
Alex Lekao
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
22/10/2013
Roniere Almeida
22/10/2013
Jefferson Santos
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
22/10/2013
Jefferson Santos
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
22/10/2013
Jefferson Santos
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
22/10/2013
Simone
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.
22/10/2013
Alex Lekao
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.
05/11/2013
Roniere Almeida
Clique aqui para fazer login e interagir na Comunidade :)