Thiago Zavaschi R2 www.zavaschi.com

12May/104

Verificando espaço em disco e o espaço ocupado por tabelas!

Olá pessoal!

Inspirado no post do Diego Nogare, o qual comentei (com os scripts deste post), resolvi postar aqui também os scripts sobre verificação de espaço usado em disco e por tabelas!

Verificar o tamanho ocupado por tabelas no banco (a.k.a achar maiores tabelas):

Ordenado por quantidade de linhas:

SELECT object_name(id), rowcnt, dpages * 8 AS [tamanho KB] FROM sysindexes
WHERE indid IN (1,0) AND objectproperty(id,'isusertable') = 1
ORDER BY rowcnt DESC

Ordenado por tamanho físico:

SELECT object_name(id), rowcnt, dpages * 8 AS [tamanho KB] FROM sysindexes
WHERE indid IN (1,0) AND objectproperty(id,'isusertable') = 1
ORDER BY [tamanho KB] DESC

E para mostrar o espaço em disco disponível (valor absoluto e percentual) e o espaço total dos discos, é possível utilizar (a fonte deste script é o SQLDBATips):

DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
                      FreeSpace int NULL,
                      TotalSize int NULL)

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0
BEGIN

        EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
        IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
        EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
        IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
        UPDATE #drives
        SET TotalSize=@TotalSize/@MB
        WHERE drive=@drive
        FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur
DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive,
       FreeSpace as 'Livre(MB)',
       TotalSize as 'Total(MB)',
       CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Livre(%)'
FROM #drives
ORDER BY drive

DROP TABLE #drives

RETURN
GO

A saída do script deverá ser semelhante a:

drive Livre(MB)   Total(MB)   Livre(%)
----- ----------- ----------- -----------
C     9541        49999       19
D     46002       317966      14
F     37549       99998       37
K     68581       476269      14

É isso pessoal, fazia tempo que não postava algo masi focado a área de DBA (a qual gosto muito também), espero que seja útil!

Abraços,
Thiago Zavaschi

Comments (4) Trackbacks (0)
  1. Foi, realmente MUITO útil. Obrigado!

  2. Maravilha. Muito bom. Parabéns!

  3. Fala Zava tudo bem, seguinte eu executei esse scritp porem recebi mensagem abaixo, e só mostra valores para os campos drive e livre(MB) os campos Total(MB) e Livre(%) ficam zerados, será que é necessário algum tipo de permissão? Abraços

    (2 row(s) affected)
    Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
    SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.
    Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1
    SQL Server blocked access to procedure ‘sys.sp_OAMethod’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.
    Msg 15281, Level 16, State 1, Procedure sp_OAGetProperty, Line 1
    SQL Server blocked access to procedure ‘sys.sp_OAGetProperty’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.

    (1 row(s) affected)
    Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1
    SQL Server blocked access to procedure ‘sys.sp_OAMethod’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.
    Msg 15281, Level 16, State 1, Procedure sp_OAGetProperty, Line 1
    SQL Server blocked access to procedure ‘sys.sp_OAGetProperty’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.

    (1 row(s) affected)
    Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1
    SQL Server blocked access to procedure ‘sys.sp_OADestroy’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.

    (2 row(s) affected)

  4. Aproveitando, consegui habilitar, para quer tiver o mesmo problema que eu tive acima, basta habilitar as opções de ‘Ole Automation Procedures’, segue link abaixo

    https://msdn.microsoft.com/pt-br/library/ms191188(v=sql.120).aspx


Leave a comment


No trackbacks yet.