#sql-server
#sql-сервер
Вопрос:
У нас есть база данных Microsoft SQL Server 2012, используемая для хранения статистических данных из системы массового обслуживания. Система очередей отправляет статистику в базу данных каждые 10 минут, а размер базы данных увеличивается примерно на 1 ГБ в неделю.
Нам не нужны данные старше 1 года.
Мы создали SQL-скрипт для удаления старых данных. После выполнения скрипта размер базы данных увеличивается.
use statdb
-- Execute as stat user
-- The following three settings are used to control what will be used
-- Amount of days of stat data which should be kept
DECLARE @numberOfDaysToKeep int=365
-- If set to 1, also the aggregated data will be removed, otherwise only the events will be removed
DECLARE @DeleteAggregateData int = 1
-- If set to 1, also the hardware monitoring data will be removed.
DECLARE @DeleteAgentDcData int = 1
-- Do not change anything below this line
DECLARE @DeleteBeforeDate int = (SELECT id FROM stat.dim_date WHERE full_date > DATEADD(day,-1-@numberOfDaysToKeep,GETDATE()) AND full_date < DATEADD(day,0-@numberOfDaysToKeep,GETDATE()))
-- Remove CFM events
DELETE FROM stat.fact_visit_events where date_key < @DeleteBeforeDate
DELETE FROM stat.fact_sp_events where date_key < @DeleteBeforeDate
DELETE FROM stat.fact_staff_events where date_key < @DeleteBeforeDate
-- ...continue to delete from other tables
Мы хотели бы сохранить размер базы данных на постоянном уровне. Использует ли MS SQL server свободное пространство (после удаления) или размер базы данных будет расти с той же скоростью, что и до удаления?
Или мне нужно запустить SHRINK после сценария? (основываясь на других обсуждениях, это не рекомендуется)
Комментарии:
1.
Does MS SQL server use free space (after delete)?ДА2. Ваши таблицы являются кучами или у них есть кластеризованные индексы?
3. Привет, Владимир, спасибо за ваш ответ. Так, например: — Размер базы данных теперь составляет 76 ГБ — после удаления она имеет размер 98 ГБ — новые данные будут сохранены в базе данных, но они не будут занимать новое место в операционной системе. Размер файла останется прежним 98 ГБ. Даже если раньше она росла на 1 ГБ в неделю. Это правда?
4. @MartinStarosta, да, по моему опыту, именно так это и работает. Размер базы данных стабилизируется. В нашей системе мы также регулярно перестраиваем индексы после удаления старых данных. Эта перестройка индексов дефрагментирует их и поддерживает статистику в актуальном состоянии. Все наши таблицы имеют кластеризованный индекс, т. Е. нет куч. Я не уверен, что кучи имеют какое-то особое поведение.
5. @VladimirBaranov, да, кучи имеют особое поведение при удалении, что вызвало мой вопрос
Ответ №1:
Вы не предоставляете информацию о том, находится ли ваша база данных в режиме ПОЛНОГО восстановления или в режиме ПРОСТОГО восстановления. Конечно, вам нужно уменьшить свой файл журнала, а также файл данных. В SSMS вы можете видеть, сколько свободного места доступно для файла данных и для файла журнала. Смотрите изображение ниже.
Существует возможность уменьшить файл данных и файл журнала с помощью T-SQL. Смотрите больше на DBCC SHRINKFILE (Transact-SQL)
Комментарии:
1. База данных находится в ПОЛНОМ режиме. Основываясь на обсуждениях, я подумал, что СЖАТИЕ не рекомендуется из-за проблем с производительностью. Я удалил и уменьшил только файл журнала, размер базы данных уменьшился. Правильный ли это процесс?
2. ДА. Если ваша база данных продолжает расти, вам также следует рассмотреть возможность сокращения файла данных. Эта задача должна быть выполнена в выходные дни. Как я показал, в SSMS есть приятная функция, позволяющая показать вам, сколько свободного места может быть освобождено.
3. @DarkoMartinovic Прекратите сжимать файлы вашей базы данных. Серьезно. Сейчас.
4. Да, я согласен. Это правило. Но есть исключения из. Вот причина, по которой существуют соответствующие команды для сжатия. Насколько я понимаю, этот пост посвящен исключениям.