SelectEtoile : Base de connaissance SGBD

Posts Tagged 'espace'

Visiualisation du versionning.

Calcul du versioning IQ selon l'espace libre du Main.

 La procédure suivante permet de visualiser l'espace  l'espace de travaille occupé par les versions IQ dans l'espace des donnée. Il prend en compte également la taille de DATA/TMP de la base.

 

Code de la procédure

ALTER PROCEDURE "dbo"."sp_dba_version"()
result (Type varchar(50),
        Version_size_Mo int,
        Data_space int,
        Data_space_used int,
        Percent_free_space_used_by_version double,
    Free_space double,
        Percent_total_version_type double ,
        older_VerCreateTime varchar(26),
        TempWork int,
        TempWorkUsed int,
        Nbactivever  int,
        Nboldver  int
        )
BEGIN

Read more: Visiualisation du versionning.

Volumétrie Tables / Indexes / Bases SYBASE ASE

 

Liste de la volumétrie des tables en version 12.5 :

 

Use mabase
Go
select     DateJour               = convert(datetime,convert(char(10),getdate(),103),103),    
Base = db_name(),  
Tables = Object_name(id),       
NombreLigne = max(rowcnt(doampg)) ,       
Reserved = (sum(reserved_pgs(id,doampg))+ sum(reserved_pgs(id, ioampg)))*2,        
Data = (sum(data_pgs(id, doampg)))*2,       
Indexes = (sum(data_pgs(id, ioampg)))*2  
from sysindexes        
where id > 100       
group by id        
order by object_name(id)

 

 

 

 

Liste de la volumétrie des tables en version 15 :

use mabase
go
select top 10 t.DateJour,t.Base,t.Tables,Nb_row=convert(varchar(10),sum(t.rwct)),Reserved=sum(t.reserved), Data=sum(t.data), Indexes=sum(t.indexes)
from (select  DateJour        = convert(datetime,convert(char(10),getdate(),103),103),
        Base            = convert(varchar(15),db_name()),
        Tables          = convert(varchar(20),object_name(id)),
        case when indid < 2 then (row_count(db_id(),id)) when indid > 1 then 0 end as rwct,
        reserved        = (reserved_pages(db_id(),id,indid))*(@@maxpagesize/1024),
        case when indid < 2 then (data_pages(db_id(),id,indid))*(@@maxpagesize/1024) when indid > 1 then 0 end as data,
        case when indid > 1 then (data_pages(db_id(),id, indid))*(@@maxpagesize/1024) when indid < 2 then 0 end as indexes
from sysindexes
where id > 100) t
group by t.DateJour,t.Base,t.Tables
order by Reserved desc

 

 

 

 

 

 

 

Espace utilisé par les bases sous SQLServer

use master
go

CREATE TABLE #DBInfo
( ServerName VARCHAR(100),  
DatabaseName VARCHAR(100),  
FileSizeMB INT,  
LogicalFileName sysname,  
PhysicalFileName NVARCHAR(520),  
Status sysname,  
Updateability sysname,  
RecoveryMode sysname,
GrowthMode VARCHAR(20),
Collation sysname,
SpaceUsedMB INT,  
FreeSpaceMB INT,  
FreeSpacePct VARCHAR(7),  
FreeSpacePages INT,  
PollDate datetime)  

DECLARE @command VARCHAR(5000)  

SELECT @command = 'Use [' + '?' + '] SELECT  
@@servername as ServerName,  
' + '''' + '?' + '''' + ' AS DatabaseName,  
CAST(sysfiles.size/128.0 AS int) AS FileSize,  
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,  
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,  
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,  
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
GrowthMode =
  CASE Growth
   WHEN ''0'' then ''DISABLED''
   ELSE ''ENABLED''
  END,
CONVERT(sysname,DatabasePropertyEx(''?'',''Collation'')) AS Collation,
CAST(FILEPROPERTY(sysfiles.name, ' + '''' +  
       'SpaceUsed' + '''' + ' )/128.0 AS int) AS SpaceUsedMB,  
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +  
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,  
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,  
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))  
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,  
GETDATE() as PollDate FROM dbo.sysfiles'
 
INSERT INTO #DBInfo  
   (ServerName,  
   DatabaseName,  
   FileSizeMB,  
   LogicalFileName,  
   PhysicalFileName,  
   Status,  
   Updateability,  
   RecoveryMode,
   GrowthMode,
   Collation,  
   SpaceUsedMB,
   FreeSpaceMB,  
   FreeSpacePct,  
   PollDate)  

EXEC sp_MSForEachDB @command  

SELECT  
   ServerName,  
   DatabaseName,  
   FileSizeMB,  
   LogicalFileName,  
   PhysicalFileName,  
   Status,  
   Updateability,  
   RecoveryMode,
   GrowthMode,
   Collation,
   SpaceUsedMB,
   FreeSpaceMB,  
   FreeSpacePct,  
   PollDate  
FROM #DBInfo
ORDER BY  
   ServerName,  
   DatabaseName  
GO

DROP TABLE #DBInfo  
GO

Comment corriger un mauvais calcul de l'espace libre du journal de transaction

Comment corriger un mauvais calcul de l'espace libre du journal de transaction  ?

 

Symptomes :

1> sp_spaceused syslogs
2> go
 name            total_pages     free_pages      used_pages      reserved_pages
 --------------- --------------- --------------- --------------- ---------------
 syslogs         17920000        23365587        -5447337        1750

 Apparition de valeur négative dans used_pages. Cela correspond à un bug SYBASE

Pour info, voici la requête qui permet de calculer l'espace alloué,libre, occuppé du journale de transaction :

 select distinct convert(int,lct_admin('logsegment_freepages',db_id('msbdb_sumotc'))-lct_admin('reserved_for_rollbacks',db_id('msbdb_sumotc')))*(@@maxpagesize/1024.0/1024.0)

 

Methode 1

  • use master
  • go
  • sp_dboption dbname, 'single user', true
  • go
  • use dbname
  • go
  • dbcc tablealloc(syslogs, full, fix)
  • go
  • use master
  • go
  • sp_dboption dbname, 'single user', false
  • go

Methode 2

  • use master
  • go
  • sp_dboption dbname, 'single user', true
  • go
  • dbcc dbrepair(dbname, fixlogfreespace)
  • go
  • sp_dboption dbname, 'single user', false
  • go

 

 

La méthode 1 est préférable car supportée.

Visualisation de l'espace dispo FileGroup

Create table #AllDbSpace (
    DbName varchar(100),
    FgName varchar(100),
    NbFiles int,
    CurrentSizeMB int,
    FreeSpaceMB int,
    FreeSpacePct int
)

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SQLScript VARCHAR(6000)

DECLARE DatabaseCursor CURSOR FOR
                SELECT [name] FROM master..sysdatabases
                 where dbid > 4
                ORDER BY [name]

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
        BEGIN

                  SET @SQLScript = 'USE ' + @DatabaseName + ';
                                                  With dbSpace'+ @DatabaseName +' as
                                                    (
                                                        SELECT DB_NAME() AS DbName,
                                                        fg.name AS FgName,
                                                        count(f.name) AS NbFiles,
                                                        sum(f.size/128) AS CurrentSizeMB,
                                                        sum(f.size/128-CAST(FILEPROPERTY(f.name,''SpaceUsed'') AS INT)/128) AS FreeSpaceMB
                                                        FROM sys.database_files f
                                                        inner join sys.filegroups fg on fg.data_space_id = f.data_space_id
                                                        group by fg.name
                                                    )
                                                    INSERT INTO #AllDbSpace
                                                    Select DbName,FgName,NbFiles,CurrentSizeMB,FreeSpaceMB,FreeSpaceMB*100/CurrentSizeMB as ''%FreeSpace''
                                                    FROM dbSpace'+ @DatabaseName +';
                                                    '

                EXEC (@SQLScript)
                FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

        END

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

SELECT DbName,FgName,NbFiles,CurrentSizeMB,FreeSpaceMB,FreeSpacePct as '%FreeSpace' FROM #AllDbSpace
DROP TABLE #AllDbSpace