none
Diminuir arquivos de dados RRS feed

  • Pergunta

  • Pessoal bom dia 

    trabalho com uma base de dados que hoje já esta com quase 2TB, e tenho efetuado limpeza em algumas tabelas de registros mais antigos, mas percebo que quando faço o backup esse vem diminuindo, mas meus  arquivos de dados, não tem mostrado espaço liberado tipo se o disco ocupava os 2TB continua mostrando a mesma quantidade nesse disco e não tem diminuído em nada,  alguém poderia me da alguma dica como posso liberar/diminuir esse arquivos de dados???

    quarta-feira, 1 de dezembro de 2021 13:37

Todas as Respostas

  • dr. khan,

    Sim, é possível você aplicar esta divisão trabalhando com vários arquivos de dados.

    Você esta utilizando qual modelo de recuperação de banco de dados? Preferencialmente para que o backup possa "liberar" espaço você precisa utilizar o recovery model full.

    A sugestão que vou fazer aqui não é da melhores, mesmo assim vou apresentar:

    "Você já chegou a forçar um ShrinkDatabase ou ShrinkFile" aplicado a este banco de dados?

    Acredito que esta dúvida que você esta postando hoje, já havia sido questionada por você anteriormente!


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 2 de dezembro de 2021 12:16
    Moderador
  • Junior, boa tarde,

    Sim postei mas ainda não conseguir resolver,  sim ja fiz ShrinkFile dos logs, mas nao resolveu, tenho uma rotina que ficava efetuado limpeza nas tabelas maiores que tenho de logs erros, mas percebo que o backup ate diminui quando ele e efetuado, mas o espaço no disco da pasta de dados no sql, fica o mesmo volume e não diminui, 

    segunda-feira, 6 de dezembro de 2021 18:18
  • dr.Khan,

    O espaço em disco somente será liberado justamente quando o SQL Server enviar para o file system a orientação para liberar estes espaços que estavam marcados para uso.

    Este procedimentos de backup estão sendo realizados com o banco de dados configurado para o recovery model full?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 7 de dezembro de 2021 13:54
    Moderador
  • Oi Junior 


    sim modo recovery  full,  mas ai eu teria que fazer um recover??

    quarta-feira, 8 de dezembro de 2021 23:16
  • Olá dr.khan,

     

    Alguma novidade sobre sua consulta?

     

    A resposta foi dada pelo Junior?

     

    Obrigado por utilizar os fóruns MSDN.

     

    Jeremy Arias

    ____________________________

     

    Lembre-se de "Marcar como Respostas" respostas que resolveram seu problema, é uma forma comum de reconhecer aqueles que ajudaram, e torna mais fácil para os outros visitantes encontrar a solução mais tarde.

     

    Se você tiver algum elogio ou reclamação sobre o suporte MSDN, sinta-se à vontade para contatar MSDNFSF@microsoft.com.

    quarta-feira, 8 de dezembro de 2021 23:52
  • dr.khan,

    Não precisa fazer o recovery, sempre que o backup full é realizado o arquivo de log é zerado, mas isso não significa que o espaço em disco será liberado.

    Se possível faça um teste, pare o serviço do SQL Server, reinicie novamente e verifique se o espaço em disco é liberado, provavelmente isso vai acontecer.

    Se posse deste comportamento, você vai ter que definir uma estratégia para realizar o procedimento de ShrinkDatabase ou ShrinFile.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 9 de dezembro de 2021 13:19
    Moderador
  • Junior boa tarde

    Fiz e não deu certo,   e olha só que fiz em uma base de teste eu limei toda tabela que tinha quase 1TB,  mesmo assim a unidade de disco continua mostrando o mesmo tamanho da de dados da base, a pasta dados não diminuiu em nada.

    terça-feira, 14 de dezembro de 2021 15:05
  • Execute Isso para ver quanto você consegue pegar de dados utilizando compactação (shirink):

    USE master

    GO

    CREATE TABLE #TMPFIXEDDRIVES ( DRIVE CHAR(1), MBFREE INT)

    INSERT INTO #TMPFIXEDDRIVES

    EXEC xp_FIXEDDRIVES

    CREATE TABLE #TMPSPACEUSED ( DBNAME VARCHAR(50), FILENME VARCHAR(50), SPACEUSED FLOAT)

    INSERT INTO #TMPSPACEUSED

    EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

    SELECT   C.DRIVE,

             CASE 

               WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE)) AS DECIMAL(18,2)) AS VARCHAR(20)) 

               ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) 

               END AS DISKSPACEFREE,

             A.NAME AS DATABASENAME,

             B.NAME AS FILENAME,

             CASE B.TYPE 

               WHEN 0 THEN 'DATA'

               ELSE TYPE_DESC

               END AS FILETYPE,

             CASE 

               WHEN (B.SIZE * 8 / 1024.0) > 1000

               THEN CAST(CAST(((B.SIZE * 8 / 1024) ) AS DECIMAL(18,2)) AS VARCHAR(20)) 

               ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) 

               END AS FILESIZE,

             CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,

             B.PHYSICAL_NAME

    FROM     SYS.DATABASES A

             JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID

             JOIN #TMPFIXEDDRIVES C  ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE

             JOIN #TMPSPACEUSED D    ON A.NAME = D.DBNAME AND B.NAME = D.FILENME

    ORDER BY DISKSPACEFREE,

             SPACEFREE DESC

             

    DROP TABLE #TMPFIXEDDRIVES

    DROP TABLE #TMPSPACEUSED


    --------------------------------------------------

    Caso queira compactar todas as databases Utilizar o script abaixo:

    DECLARE @DBs TABLE(database_id NVARCHAR(max),name NVARCHAR(max))

    INSERT @DBs

    SELECT database_id,name FROM sys.databases

    WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')

    DECLARE                @DB_ID NVARCHAR(MAX) ,

                                                   @DB_NAME NVARCHAR(MAX),

                                                   @LOG_FILENAME NVARCHAR(MAX),

                                                   @DATA_FILENAME NVARCHAR(MAX)

    WHILE EXISTS(SELECT TOP 1 1 FROM @DBs)

    BEGIN

      SET @DB_ID = (SELECT TOP 1 database_id FROM @DBs)

      SET @DB_NAME = (SELECT TOP 1 name FROM @DBs) 

      SET @DATA_FILENAME = (SELECT TOP 1 name FROM sys.master_files WHERE database_id = @DB_ID   AND type = 0)

      SET @LOG_FILENAME = (SELECT TOP 1 name FROM sys.master_files WHERE database_id = @DB_ID   AND type = 1)

      EXEC('ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY SIMPLE')

      EXEC('USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @LOG_FILENAME + '], 1)')

      EXEC('USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @DATA_FILENAME + '], 1)')

      EXEC('ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY FULL')

      DELETE @DBs WHERE database_id = @DB_ID

    END

    terça-feira, 14 de dezembro de 2021 20:25
  • Olá dr.khan,

     

    Alguma novidade sobre sua consulta?

     

    A resposta foi dada pelo Christhian Yukio?

     

    Obrigado por utilizar os fóruns MSDN.

     

    Jeremy Arias

    ____________________________

     

    Lembre-se de "Marcar como Respostas" respostas que resolveram seu problema, é uma forma comum de reconhecer aqueles que ajudaram, e torna mais fácil para os outros visitantes encontrar a solução mais tarde.

     

    Se você tiver algum elogio ou reclamação sobre o suporte MSDN, sinta-se à vontade para contatar MSDNFSF@microsoft.com.

    quinta-feira, 16 de dezembro de 2021 00:06
  • dr. khan,

    Mesmo parando o serviço do SQL Server?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 17 de dezembro de 2021 11:26
    Moderador
  • Execute Isso para ver quanto você consegue pegar de dados utilizando compactação (shirink):

    USE master

    GO

    CREATE TABLE #TMPFIXEDDRIVES ( DRIVE CHAR(1), MBFREE INT)

    INSERT INTO #TMPFIXEDDRIVES

    EXEC xp_FIXEDDRIVES

    CREATE TABLE #TMPSPACEUSED ( DBNAME VARCHAR(50), FILENME VARCHAR(50), SPACEUSED FLOAT)

    INSERT INTO #TMPSPACEUSED

    EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

    SELECT   C.DRIVE,

             CASE 

               WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE)) AS DECIMAL(18,2)) AS VARCHAR(20)) 

               ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) 

               END AS DISKSPACEFREE,

             A.NAME AS DATABASENAME,

             B.NAME AS FILENAME,

             CASE B.TYPE 

               WHEN 0 THEN 'DATA'

               ELSE TYPE_DESC

               END AS FILETYPE,

             CASE 

               WHEN (B.SIZE * 8 / 1024.0) > 1000

               THEN CAST(CAST(((B.SIZE * 8 / 1024) ) AS DECIMAL(18,2)) AS VARCHAR(20)) 

               ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) 

               END AS FILESIZE,

             CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,

             B.PHYSICAL_NAME

    FROM     SYS.DATABASES A

             JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID

             JOIN #TMPFIXEDDRIVES C  ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE

             JOIN #TMPSPACEUSED D    ON A.NAME = D.DBNAME AND B.NAME = D.FILENME

    ORDER BY DISKSPACEFREE,

             SPACEFREE DESC

             

    DROP TABLE #TMPFIXEDDRIVES

    DROP TABLE #TMPSPACEUSED


    --------------------------------------------------

    Caso queira compactar todas as databases Utilizar o script abaixo:

    DECLARE @DBs TABLE(database_id NVARCHAR(max),name NVARCHAR(max))

    INSERT @DBs

    SELECT database_id,name FROM sys.databases

    WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')

    DECLARE                @DB_ID NVARCHAR(MAX) ,

                                                   @DB_NAME NVARCHAR(MAX),

                                                   @LOG_FILENAME NVARCHAR(MAX),

                                                   @DATA_FILENAME NVARCHAR(MAX)

    WHILE EXISTS(SELECT TOP 1 1 FROM @DBs)

    BEGIN

      SET @DB_ID = (SELECT TOP 1 database_id FROM @DBs)

      SET @DB_NAME = (SELECT TOP 1 name FROM @DBs) 

      SET @DATA_FILENAME = (SELECT TOP 1 name FROM sys.master_files WHERE database_id = @DB_ID   AND type = 0)

      SET @LOG_FILENAME = (SELECT TOP 1 name FROM sys.master_files WHERE database_id = @DB_ID   AND type = 1)

      EXEC('ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY SIMPLE')

      EXEC('USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @LOG_FILENAME + '], 1)')

      EXEC('USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @DATA_FILENAME + '], 1)')

      EXEC('ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY FULL')

      DELETE @DBs WHERE database_id = @DB_ID

    END

    Christian,

    Seu script é interessante, temos somente que tomar cuidado para não aplicar este tipo de procedimentos nos bancos de dados de sistemas, mesmo sabendo que o SQL Server não vai permitir.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 17 de dezembro de 2021 11:28
    Moderador
  • Obrigado Pedro, ótima observação.

    Tem um where no script, que você pode excluir os bancos de sistemas.

    WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')

    Caso queira pode adicionar as bases nessa lista.

    Ou caso queiram compactar, pode-se pegar os nomes da base e dos logs, corretamente, e compactar manualmente, segue exemplo:

    ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY SIMPLE

    USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @LOG_FILENAME + '], 1)

    USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @DATA_FILENAME + '], 1)

    ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY FULL

    Dúvidas a disposição.

    sexta-feira, 17 de dezembro de 2021 14:07
  • Obrigado Pedro, ótima observação.

    Tem um where no script, que você pode excluir os bancos de sistemas.

    WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')

    Caso queira pode adicionar as bases nessa lista.

    Ou caso queiram compactar, pode-se pegar os nomes da base e dos logs, corretamente, e compactar manualmente, segue exemplo:

    ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY SIMPLE

    USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @LOG_FILENAME + '], 1)

    USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @DATA_FILENAME + '], 1)

    ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY FULL

    Dúvidas a disposição.

    Christian,

    Perfeito! Eu que agradeço a sua participação e compreensão.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 17 de dezembro de 2021 17:30
    Moderador