Início / dba / Perguntas / 56883
Accepted
IT researcher
Asked: 2014-01-17 02:09:15 +0800 CST

Reduza o banco de dados apenas até seu tamanho inicial, definido após a criação do banco de dados

  • 772

No servidor SQL, todo banco de dados possui uma propriedade Initial Size (MB) que pode ser vista nas propriedades do banco de dados no SSMS. Por padrão, será 3 MB para arquivo mdf e 1 MB para arquivo ldf.

Portanto, agora, se criar um novo banco de dados, ele será definido com o tamanho padrão (ou seja, 3 MB para mdf e 1 mb para arquivo ldf). Mas depois de criar o banco de dados, altero o tamanho inicial para algum outro valor, digamos, para mdf 10 MB e ldf 5 MB. (Em tempo real, algum administrador de banco de dados pode querer alterar inicial após criar o banco de dados)

Mas agora, se reduzir o banco de dados, ele diminuirá além do tamanho inicial que defini após a criação (considere que não há dados no banco de dados, caso contrário, ele diminuirá para o tamanho real do conteúdo). Ele encolherá até o tamanho inicial que tinha durante a criação do banco de dados (ou seja, 3 MB para mdf e 1 mb para ldf). Eu esperava encolher até o tamanho inicial que defini (ou seja, 10 mb para mdf e 5 mb para ldf). Pode ser possível fazer? Se possível, então como?

Em alguns artigos que vi como dbcc SHRINKFILE pode ser usado para encolher além do tamanho inicial e dbcc SHRINKDATABASE não pode encolher além do tamanho inicial. Mas eu quero encolher apenas até o tamanho inicial que defini.

Nota: Eu sei que encolher é ruim e não deve ser feito. Mas eu quero saber como isso pode ser feito? Se o sql server considera apenas o tamanho inicial definido durante a criação do banco de dados, qual é a utilidade de definir o tamanho inicial após a criação do banco de dados?

EDITAR:

Do artigo relacionado à redução da Microsoft: Usando DBCC SHRINKDATABASE, você não pode reduzir o banco de dados abaixo de seu tamanho inicial.

Minha pergunta é Posso alterar o tamanho inicial do próprio banco de dados existente?

EDITAR:

Eu tenho cerca de 5 instâncias do servidor SQL. Todo o banco de dados foi criado com tamanho padrão de 3 mb para dados e 1 mb para log (cerca de 3 anos atrás). Agora todos os bancos de dados estão na faixa de 100-1000 mb. Não reduzirei o banco de dados normalmente, pois não é uma boa prática. Mas alguns bancos de dados crescem mais de 10 GB (o próprio arquivo mdf, pois insiro tantos dados nele). Portanto, quando o banco de dados for grande, mudarei o conteúdo sem importância e antigo desse banco de dados para outro servidor, pois não é necessário nesse servidor para mim. Assim, o tamanho do banco de dados reduz de 10 GB para 1 GB. Agora, os 9 Gb restantes não são usados. Agora, quero especificar que o tamanho do banco de dados seja reduzido para 2 GB para obter 1 GB de espaço livre (também 2 Gb é o tamanho ideal para este banco de dados), que pode ser usado para evitar o crescimento automático até que o próximo 1 Gb de dados seja preenchido . Também defini 100 MB de crescimento automático para esse tipo de banco de dados grande que possui mais gravações. Portanto, em vez de especificar o tamanho como 2 GB no comando shrinkfile, se fosse possível alterar o tamanho inicial para 2 GB (o que é considerado encolher), seria melhor. Durante a criação deste banco de dados, não havia nenhum plano sobre tamanho, crescimento automático, etc. Como foi criado há cerca de 3 anos. Agora estou planejando definir o crescimento automático, etc. Não posso criar novamente um novo banco de dados com o novo tamanho inicial e dados de turno. Então, eu estava perguntando se posso alterar o tamanho inicial do banco de dados existente. Como foi criado cerca de 3 anos atrás. Agora estou planejando definir o crescimento automático, etc. Não posso criar novamente um novo banco de dados com o novo tamanho inicial e dados de turno. Então, eu estava perguntando se posso alterar o tamanho inicial do banco de dados existente. Como foi criado cerca de 3 anos atrás. Agora estou planejando definir o crescimento automático, etc. Não posso criar novamente um novo banco de dados com o novo tamanho inicial e dados de turno. Então, eu estava perguntando se posso alterar o tamanho inicial do banco de dados existente.

sql-server sql-server-2008-r2

3 respostas

  1. Best Answer

    O tamanho inicial não é apenas 3 MB, ele é obtido do banco de dados modelo (se não for especificado durante a criação do banco de dados do usuário). Portanto, supondo que você não tenha especificado um tamanho inicial durante a criação do banco de dados do usuário e não tenha alterado os tamanhos de arquivo de banco de dados modelo depois de criar seu userdb, você pode fazer o seguinte:

    --Create testDB
CREATE DATABASE [TEST_100]
GO


--grow your database file size
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'Test', SIZE = 100MB )
GO


--switch context
USE [TEST_100]
GO



--Find size of modelDB mdf file, this is your initial file size used for the userdb
DECLARE @TargetFileSize int

SELECT  @TargetFileSize = (size * 8 / 1024)
FROM    sys.master_files 
WHERE   database_id = 3 --model database
AND     file_id= 1 --first file is mdf, assuming you have a model with just one mdf. If you have multiple files, change for the one you need to find.


--shrink the the first file of your current database to the target size that you just found.
DBCC SHRINKFILE (1,@TargetFileSize)

    EDITAR

    Ok, algumas informações extras são necessárias após suas edições e comentários.

    Em primeiro lugar. Eu sinto que o rótulo "Tamanho inicial" que você vê quando olha para as propriedades do arquivo no SSMS é um nome impróprio. Basicamente, seu tamanho inicial é apenas um conceito. É o primeiro tamanho usado durante a criação do banco de dados. Você pode especificar isso explicitamente na CREATE DATABASEinstrução ou pode fazer com que o SQL Server copie-o implicitamente do banco de dados modelo, omitindo essas informações durante a criação.

    No entanto, uma vez que o banco de dados é criado, do ponto de vista do DBA, não existe um "tamanho inicial", existe apenas uma propriedade visível para um DBA, que é: o tamanho real. Mesmo a propriedade "Tamanho inicial" no SSMS mostra apenas o tamanho real, não o tamanho inicial.

    Bem, como é que isso DBCC SHRINKFILEou DBCC SHRINKDATABASE"sabe" o tamanho inicial então? Mesmo depois de ter mudado o tamanho. Pergunta interessante.

    A primeira página de um arquivo de banco de dados é a página de cabeçalho do arquivo. Lá você tem, entre outras, 2 propriedades: size e minsize.

    Na criação do arquivo, ambas as propriedades do cabeçalho do arquivo são preenchidas com o valor inicial:

    DBCC TRACEON(3604)
--parameters for DBCC PAGE: (Dbname, fileID, pageID, outputTypeID)
DBCC PAGE('Test_100',1,0,3)with tableresults

    insira a descrição da imagem aqui

    Ambos os tamanhos estão na quantidade de páginas de dados. Nesse caso. 288 páginas de dados.

    Agora, se eu alterar o tamanho do arquivo:

    ALTER DATABASE [test_100] MODIFY FILE ( NAME = N'test', SIZE = 50MB )

    insira a descrição da imagem aqui

    Você pode ver que a propriedade "tamanho" foi alterada para refletir o novo tamanho. No entanto, a propriedade "MinSize" ainda contém o tamanho "Inicial". É o tamanho mínimo para o qual o comando encolher irá.

    No entanto, tendo dito tudo isso. Ainda não entendo por que você quer complicar as coisas alterando primeiro o tamanho inicial e depois encolhendo para esse tamanho inicial. Em vez de apenas encolher diretamente para um tamanho de destino.

    De qualquer forma, para responder à sua pergunta. O tamanho "inicial" não é exposto como uma propriedade para o usuário/dba.

    • 9

  2. @Edward tem uma ótima resposta sobre como reduzir seu arquivo com base no tamanho inicial definido no modelo. vou responder isso:

    qual é a utilidade de definir o tamanho inicial após a criação do banco de dados?

    E suponha que você quis dizer:

    qual é a utilidade de definir o tamanho inicial ao criar o banco de dados?

    Isso pode ser extremamente útil para arquivos de dados e de log quando você sabe com antecedência qual deve ser o tamanho do seu banco de dados. Se você estiver criando dois bancos de dados, um para dados de configuração e outro para dados transacionais, eles provavelmente terão padrões de crescimento muito diferentes. Portanto, em vez de usar os padrões terríveis, terríveis e terríveis (tanto de configurações de tamanho quanto de crescimento) do modelo, faz muito mais sentido sempre personalizar isso por banco de dados, dependendo de suas necessidades (mesmo se você tiver alterado os padrões terríveis para algo mais razoável, que geralmente não será de tamanho único). E você sempre pode aumentar manualmente um arquivo mais tarde, fora dos períodos de maior movimento, para acomodar as alterações em suas previsões iniciais. Em vez de apenas permitir que a natureza das mudanças na coleta de dados guie o crescimento usando suas configurações iniciais, agora incorretas.

    Um de seus objetivos de longo prazo para a criação de um banco de dados deve ser minimizar ou eliminar completamente todos e quaisquer eventos de alteração de tamanho de arquivo. Nem sempre é possível ser 100% preciso em termos de planejamento de capacidade, mas você deve ter em mente, digamos, quantos dados espera coletar no próximo mês, ano etc. Isso pode ser um pouco mais difícil para logs, mas uma vez que você sabe quantos dados você espera, você pode fazer um cálculo aproximado para o tamanho do log com uma fórmula como esta (essa ideia veio de Geoff Hiten e na verdade acabou de passar pela minha mesa ontem):

    (Largest index * 1.25) 
+ 
(largest amount of log created during reindex run * 2.1)

    Portanto, se seu maior índice for de 4 GB e sua última reindexação tiver criado 2 GB de log, então 4 * 1,25 + 2 * 2,1 = 9,2 GB de arquivo de log. Isso lhe dará um arquivo de log que pode lidar com sua reconstrução típica - incluindo uma reversão, caso ocorra - sem ter que aumentar o arquivo físico.

    As configurações ideais de autocrescimento são um pouco mais complicadas de prever, porque é um equilíbrio. Você deseja minimizar o número de vezes que os arquivos crescerão, mas também minimizar o tempo que leva para crescer cada vez. Isso significa que você nunca deseja um tamanho de crescimento de 1 MB. Vamos fingir que você tem uma transação que vai inserir 15 MB de dados. É muito melhor aumentar 50 MB uma vez para acomodar esse crescimento (e as próximas duas transações também) do que crescer em 15 eventos de crescimento independentes. E você provavelmente também não deseja um tamanho de crescimento de 20 GB, pois isso pode demorar muito por conta própria. Especialmente para arquivos de log, já que eles não podem se beneficiar da inicialização instantânea do arquivo, errar para o tamanho menor é melhor, mas não sei se existe algum número mágico ideal sem saber o tamanho médio de sua transação típica, bem como as características de desempenho do armazenamento em que o arquivo de log reside.

    Novamente, o objetivo é evitar o crescimento e encolher eventos como a praga.

    Embora às vezes seja necessário, os eventos de crescimento e redução são prejudiciais ao desempenho do seu banco de dados. Um crescimento - mesmo com a inicialização instantânea de arquivo habilitada, que só é possível para arquivos de dados, já que os arquivos de log precisam ser zerados de qualquer maneira - requer que todas as transações sejam pausadas enquanto o(s) arquivo(s) é(são) expandido(s). Um encolhimento geralmente causa fragmentação que - particularmente em E/S lenta - pode realmente ser sentida durante leituras de tabelas grandes. Com os SSDs, isso está se tornando cada vez menos relevante e não é tão importante quando todos os seus dados cabem na memória. Mas tirando essas duas coisas...

    O que eu não entendo sobre as pessoas constantemente encolhendo seus arquivos é - para quê? Se seus dados ou arquivo de log crescerem para esse tamanho uma vez, ele crescerá para esse tamanho novamente. Liberar o espaço nesse meio tempo parece inútil para mim. Para que você vai usar o espaço enquanto isso? Você não pode colocar nada lá porque precisa deixar o espaço livre para que o SQL Server possa aumentar o arquivo novamente - depois você pode reduzi-lo novamente - depois pode crescer novamente. E assim por diante.

    Para arquivos de dados, você precisa alocar o espaço necessário a longo prazo, não hoje. Para arquivos de log, você precisa gerenciar o espaço usado dentro deles estando no modelo de recuperação correto e implementando uma estratégia de backup adequada para esse modelo de recuperação .

    Se você está reduzindo arquivos fora de uma emergência ou evento anormal, você precisa mudar a maneira como está fazendo as coisas.

    • 2

  3. Presumo que seu arquivo de log foi criado com determinado tamanho inicial e agora você deseja diminuir o tamanho inicial do arquivo ldf?

    Por exemplo, o arquivo de log MyDatabase foi criado com tamanho inicial inicial de 10240 MB (10 GB), mas agora desejo diminuir esse tamanho inicial por algum motivo (talvez para utilizar mais espaço).

    Infelizmente... você não pode diminuir o tamanho inicial do arquivo Log (*.ldf) com a sintaxe DBCC SHRINKFILE ou ALTER DATABASE enquanto o estado do banco de dados estiver online.

    Mas você pode fazer esse trabalho com Excluir e recriar seu arquivo de log, e esse processo requer que seu banco de dados seja definido como offline. Essa é a única maneira de diminuir o tamanho inicial do log para menos do que o tamanho inicial quando o banco de dados foi criado pela primeira vez.

    Aqui algum link que você precisa de mais pesquisas http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

    http://www.mytechmantra.com/LearnSQLServer/How-to-attach-database-without-a-transaction-log-file-in-SQL-Server.html

    • 0

relate perguntas