Невозможно удалить файл по причине наличия данных
TEMPDB представляет собой системную базу данных Microsoft SQL Server, в которой хранятся временные таблицы созданные как самим сервером, так и пользователями. Эта база данных создается заново при каждом перезапуске Microsoft SQL Server. По умолчанию размер этой базы данных неограничен и увеличение его осуществляется при необходимости автоматически, порциями по 10% от текущего размера TEMPDB, однако эти параметры могут быть переопределены пользователем. По умолчанию, минимальный размер этой базы данных, который устанавливается при старте Microsoft SQL Server, определяется размером системной базы данных MODEL. Очистка журнала транзакций в этой базе данных производится автоматически, при этом удаляются только неактивные записи журнала транзакций.
При работе 1С:Предприятия 8 в режиме клиент-сервер широко используются временные таблицы . Кроме того, TEMPDB используется Microsoft SQL Server при выполнении запросов, использующих операторы GROUP BY, UNION, DISTINCT и т.п.
Проблема
В процессе работы 1С:Предприятия 8 возможно значительное увеличение размера базы данных TEMPDB .
Причина
Причиной увеличения размера базы данных TEMPDB, как правило, является невозможность автоматической очистки журнала транзакций и повторного использования свободного пространства в базе данных TEMPDB из-за наличия активных транзакций, использующих объекты этой базы данных. Основные причины, вызывающие длительную блокировку работы этих механизмов базы данных TEMPDB, заключаются в следующем:
- "Большие" транзакции, использующие TEMPDB , выполнение которых занимает большой промежуток времени.
- Сетевые ошибки, из-за которых Microsoft SQL Server не получает уведомление о потере сетевого подключения. Если клиентская рабочая станция зависает, перезагружается, или будет выключена во время исполнения определяемой пользователем транзакции, то Microsoft SQL Server будет считать, что клиент продолжает работу, и выполняющаяся клиентская транзакция будет по-прежнему активна. Время обнаружения подобной ситуации зависит от настроек параметров сетевого протокола, используемого Windows . Например, при использовании протокола TCP/IP это время составляет 2 часа.
Если для завершения активных транзакций не хватает места в базе данных, Microsoft SQL Server автоматически увеличивает размер TEMPDB на величину, заданную в параметрах этой базы данных (по умолчанию – 10% от текущего размера).
Решение
Уменьшить размер базы данных TEMPDB до требуемой величины можно следующими способами:
В этом случае размер базы данных TEMPDB будет установлен по умолчанию или, если эта величина переопределена пользователем, размер будет установлен в соответствии с заданными параметрами.
DBCC SHRINKDATABASE (TEMPDB)
DBCC SHRINKFILE ( Имя_Файла_Данных, Желаемый_Размер_Файла_Данных )
go
DBCC SHRINKFILE ( Имя_Файла_Журнала_Транзакций, Желаемый_Размер_Файла_Журнала_Транзакций )
go
Следует отметить, что эти команды рекомендуется выполнять в период наименьшей активности пользователей, и для их выполнения необходимо обладать правами администратора.
Более подробное описание и рекомендации по использованию этих команд можно найти в документации по Microsoft SQL Server.
Системная база данных TEMPDB участвует в работе пользователей, подключённых ко всем пользовательским базам данных сервера СУБД.
TEMPDB используется при работе с временными таблицами и процедурами, в ней создаются внутренние (internal) и пользовательские объекты (user objects) промежуточных результатов запросов и т.п..
При запуске сервера, TEMPDB создаётся заново, если TEMPDB по каким то причинам не может быть создана, то сервер СУБД не запуститься. По умолчанию размер этой базы данных неограничен и увеличение его осуществляется при необходимости автоматически, порциями по 10% от текущего размера TEMPDB, однако эти параметры могут быть переопределены пользователем. По умолчанию, минимальный размер этой базы данных, который устанавливается при старте Microsoft SQL Server, определяется размером системной базы данных MODEL. Очистка журнала транзакций в этой базе данных производится автоматически, при этом удаляются только неактивные записи журнала транзакций.
При работе 1С:Предприятия 8 в режиме клиент-сервер широко используются временные таблицы. Кроме того, TEMPDB используется Microsoft SQL Server при выполнении запросов, использующих операторы GROUP BY, ORDER BY, UNION, SORT, DISTINCT и т.п.
Наиболее частой проблемой, с которой сталкиваются пользователи, является значительное увеличение размера базы TEMPDB. Причиной увеличения размера базы данных TEMPDB, как правило, является невозможность автоматической очистки журнала транзакций и повторного использования свободного пространства в TEMPDB из-за наличия активных транзакций, использующих объекты этой базы данных.
Какие могут быть решения данной проблемы:
1. Перезапустить MS SQL Server. В этом случае размер базы данных TEMPDB будет установлен по умолчанию.
2. Сжать базу данных TEMPDB. Для этого нужно в Query Analyzer выполнить следующую команду: DBCC SHRINKDATABASE (TEMPDB).
3. Уменьшить размер отдельных файлов. Для этого нужно в Query Analyzer выполнить команды:
DBCC SHRINKFILE (Логическое_Имя_Файла_Данных, Желаемый_Размер_Файла_Данных_В_Мегабайтах)
go
DBCC SHRINKFILE (Логическое_Имя_Файла_Журнала_Транзакций,
Желаемый_Размер_Файла_Журнала_Транзакций_В_Мегабайтах)
go
Пример.
Уменьшение размера файлов базу TEMPDB до 20 мегабайт
USE TempDB
DBCC SHRINKFILE (tempdev, 20)
go
DBCC SHRINKFILE (templog,20)
go
Пункты 2 и з также можно выполнить с помощью Management Studio
4. Переместить базу данных TEMPDB нас диск большего размера. Изменить месторасположение файлов базы данных TEMPDB можно с помощью команды ALTER DATABASE. Для этого нужно в Query Analyzer выполнить следующую последовательность команд и перезапустить сервер СУБД:
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘Новый_Диск:Новый_Каталог empdb.mdf’)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘Новый_Диск:Новый_Каталог emplog.ldf’)
GO
В завершении еще парочка советов по работе с базой TEMPDB:
1. Для оптимизации работы базы данных TEMPDB рекомендуется ее вынесение на отдельный жёсткий диск или RAM-диск и разбиение MDF файла на части (одинакового размера) по числу процессоров (ядер): если процессоров 8, то количество файлов для начала 8, а затем добавлять по мере необходимости.
2. При использовании временных таблиц используется кеширование, но это не относится к операциям создания индексов, сортировки, группировки и т.п. Например: создали таблицу, построили индекс (что разумно с точки зрения построения плана), то данная таблица кешироваться не будет. Но если таблица очень маленькая и почти наверняка она SQL-сервером будет сканироваться и создается она очень часто, то возможно имеет смыл операцию создания индекса опустить, в этом случае за счет кеширования таблица будет создаваться быстрее.
Заметки о сетях, администрировании и вообще
Иногда база TempDB может разрастись (например после выполнения долгих транзакций над большим количеством данных), если место в TempDB уже освободилось, то для освобождения места на диске можно выполнить ее сжатие (shrink). Сделать это можно либо запросом либо в SSMS студии (сжимать нужно файл данных tempdev — tempdb.mdf).
Если операция shrink не привела к уменьшению файла БД, значит необходимо произвести сброс буферов и кешей сервера и повторить shrink :
Создаем checkpoint и сбрасываем буферы страниц и индексов на диск:
Чистим кеш хранимых процедур:
Очищаем остальные типы кешей:
Чистим кеш сессий:
После этого можно повторно запустить сжатие файла — место на диске должно освободиться (способ чаще всего срабатывает и без первого пункта — без создания checkpoint и сброса буфера страниц).
Я хотел удалить некоторые неиспользуемые файловые группы /файлы в базе данных SQL Server, но застрял, потому что SQL Server считает, что файловые группы /файлы все еще используются.
- У нас были некоторые разделенные таблицы, которые мы преобразовали обратно в несегментированные.
- Все разделы-функции и -схемы были удалены
Я запросил представления dm для неиспользуемых файловых групп следующим образом:
Мне не удалось получить соединение с таблицами /индексами для этих файловых групп через sys.data_spaces и sys.indexes :
Я знаю, что файловая группа не может быть удалена, если она ссылается на некоторую схему разделов.
Но это не может быть причиной здесь, поскольку я удалил все схемы /функции разделов в db.
Любые подсказки, что я могу сделать, чтобы избавиться от файловых групп?
У вас могут быть некоторые данные LOB (текст /изображение /varchar (max) /nvarchar (max)), которые все еще находятся в файловой группе. Не так давно я был доволен этим. Восстановление таблицы /индекса в другой файловой группе /разделе не позволяет перемещать любые данные LOB.
Если это действительно то, что происходит, самый простой способ перемещения данных LOB - это воссоздание таблицы. Вы можете перемещать данные либо с помощью bcp out /in, либо путем вставки непосредственно в новую таблицу и переименования впоследствии (или любым другим предпочтительным способом перемещения данных, которые у вас есть). Убедитесь, что вы создали правильные файловые группы для строк и текстовых данных при создании новой таблицы.
Попробуйте использовать sys.allocation_units вместо sys.indexes. BOL говорит, что это для внутреннего использования, поэтому я бы не написал долгосрочный код, но для этого все должно быть хорошо. Он указывает на первую страницу IAM каждой единицы распределения. Я считаю, что он будет работать лучше для того, что вы пытаетесь сделать.
Также как @RobertGannon сказал, что вы можете щелкнуть правой кнопкой мыши по имени вашей базы данных, затем перейти к задачам, сжатию, файлам
>
Затем вы можете выбрать «Пустой файл, перенести данные в другие файлы в той же файловой группе», чтобы удалить все данные из данного файла. Это не поможет вам избавиться от самой файловой группы, но это позволит вам сконденсировать ее до одного файла.
ответил Kenneth Fisher 29 июля 2013, 19:04:54
Я действительно нашел легкое решение для этого исправления, так как у меня была такая же проблема.
Я пытался удалить файловую группу [fg_LMeterDetail_13] , но получил ошибку «не может быть удален, потому что она не пустая». Единственный грех этой файловой группы ассоциировался с схемой разделов. Эта файловая группа не имела данных.
Итак, после обнаружения нет противоположности NEXT USED , я экспериментировал и обнаружил, что вы можете выпустить другой NEXT USED , но указывая на предыдущую файловую группу:
После этого я смог выпустить REMOVE FILEGROUP , который затем автоматически удалял его из схемы разделов
Voila! Это сработало!
Сократите файлы и убедитесь, что они не являются файловой группой по умолчанию. Сокращение должно позволить вам проверить, что файл пуст, используя EMPTYFILE. SQL Server не позволит удалить группу файлов по умолчанию.
Мне удалось отбросить файловые группы после удаления схемы разделов и затем удалить функцию раздела.
Похожие вопросы
Популярные теги
В нашей производственной БД есть только 1 файл tempdb, и он разросся до 180 ГБ.
чтобы изменить размер основного файла, а затем запустил дополнительные:
чтобы добавить дополнительные файлы tempdb. Я столкнулся с ошибкой нехватки места на диске, так как основной не уменьшился до 30 Гб, и операция была остановлена на середине пути.
Теперь у меня остался один дополнительный файл tempdb размером 30 ГБ, в то время как основной файл имеет размер 180 ГБ, и я не могу запустить процесс избавления от него.
Я пробовал запускать
но получаю ошибку:
Could not locate file 'tempdev2' for database 'tempdb' in sys.database_files.
Файл либо не существует, либо был удален.
Мне кажется, что эта ошибка связана с тем, что операция не смогла завершиться успешно. (Есть ли у меня повреждение?)
И когда я пытаюсь запустить
я получаю следующую ошибку:
Файл "M:\SQLData\tempdb2.ndf" был изменен в системном каталоге. Новый путь будет использован при следующем запуске базы данных.
Msg 5042, Уровень 16, Состояние 1, Строка 35 Файл 'tempdev2' не может быть удален, поскольку он не пуст.
Файл sys.database_files выглядит следующим образом.
Результаты из tempdb.sys.all_objects
Edit: Я смог решить проблему 'file not found' с повреждением имени файла, которую я получал при попытке запустить EMPTYFILE Shrink. Я использовал ID файла вместо логического имени.
Еще один метод, который я нашел, - это переименование файла.
Итак, ваша текущая ситуация - это полный диск, где у вас есть две TempDB', которые вы хотите изменить размер старой, а вновь созданная испытывает трудности с удалением?
Существуют ограничения на использование команды DBCC SHRINKDATABASE на. базе данных tempdb. Целевой размер для файлов данных и журналов не может быть меньше размера, указанного при создании базы данных или меньше последнего размера, который был явно задан с помощью команды операции изменения размера файла, такой как ALTER DATABASE, которая использует функцию опцию MODIFY FILE или команду. Еще одно ограничение BCC SHRINKDATABASE является расчет параметра target_percentage (целевой_процент) и его зависимость от текущего используемого пространства.
Это должно установить ваш файл в ~54GB, что должно быть безопасным размером, учитывая, что я предполагаю, что ваш размер или модель была 30GB. Отрегулируйте % соответствующим образом.
Теперь для второго файла попробуйте следующее:
Если проблема сохранится, вам придется перезапустить SQL Server, чтобы удалить файл, поскольку TempDB используется.
И последнее замечание, вы напечатали это:
ALTER DATABASE tempdb ADD FILE (NAME='tempdev2', FILENAME='M:\SQLData**tempdb3**.ndf', Size=30GB);
Ваш скриншот показывает tempdb2, убедитесь в точности всех имен и проверьте их дважды. Просто на случай, если вы ошиблись и это вызывает головную боль.
Есть некоторые случаи, когда запуск
может облегчить боль и позволить вам уменьшить файл.
Если у вас все еще есть проблемы, посмотрите, что удалено в вашей tempdb:
Это позволит вам увидеть размер файлов tempdb.
Discusses three methods that you can use to shrink the tempdb database to a size smaller than the last configured size for the database.
As a SQL Server DBA you would know that sometimes it is possible to make mistakes with the database. Few of the time it can be corrected. Out of them, some are easy and some are difficult. I was running out of space on my VM and I started hunting for a bigger file on my machine. It was not difficult to find and recall that I added some files to the TempDB database while doing some testing. Here is the command to get details about the database file, which gave me Error Msg 5042.
В конце у меня есть несколько пустых файловых групп. Их файлы будут удалены . Также сами файловые группы удаляются. Это работает хорошо в большинстве случаев. Однако для двух баз данных я удалил файлы . у меня осталась файловая группа без связанного файла, но
выдает ошибку 5042:
Файловая группа 'xyz' не может быть удалена, потому что она не пуста.
Вопрос
Как я могу избавиться от этой пустой файловой группы . в чем может быть проблема?
Я уже прочитал некоторые распространенные проблемы, однако они не присутствуют в моей системе:
0 строк . в базе данных не осталось объектов разделения
UPDATE STATISTICS для всех объектов в базе данных
Проверяет наличие индексов в файловой группе:
Проверяет объекты в файловой группе:
Я также дал DBCC SHRINKFILE с параметром EMPTYFILE попытку до удаления файла из файловой группы. Это не имеет смысла для меня, однако я читаю решения, чтобы описать это как исправление. Все равно ничего не дало.
Я получил некоторую надежду, прочитав этот вопрос по вине сервера, и попробовал следующее:
- Обновить всю статистику
- Отбросьте всю статистику, не связанную с индексами
Не удается обработать набор строк с идентификатором 72057594712162304 объекта "STORY_TRANSLATIONSCCC" (ID 120387498), индекс "Ref90159CCC" (ID 2), поскольку он находится в файловой группе "CCC_APPLICATION_new" (ID 8), которая не была проверена.
Результаты DBCC для STORY_TRANSLATIONSCCC. Для объекта "STORY_TRANSLATIONSCCC" имеется 0 строк на 0 страницах.
Это нормально или это указывает на что-то необычное?
Этот вопрос может быть дубликатом, однако я не могу найти рабочее исправление для меня в других вопросах на dba.stackexchange. Пожалуйста, посмотрите на список того, что я уже пробовал. Это идентично решениям, описанным в разделе Невозможно удалить неиспользуемые файловые группы .
Подробнее
Может быть, это помогает понять, что я делаю, прежде чем ошибка произойдет. Я планирую переход на новый сервер. В настоящее время я тестирую это на тестовом экземпляре. Базы данных восстанавливаются с сервера prod, а модель восстановления переключается на простую. Моя цель - реструктурировать файловые группы и перейти от модели с одним файлом на файловую группу к модели с двумя файлами на файловую группу. Чтобы добиться этого, я создаю новые пустые файловые группы по два файла в каждой и перемещаю данные. К сожалению, большинство объектов имеют LOB-данные (XML и двоичные) . поэтому я использую разбиение как помощник для перемещения lob-данных. В конце все данные находятся в новых файловых группах, а старые файловые группы пусты. Затем я удаляю все файлы и удаляю соответствующую файловую группу. Основная файловая группа остается и просто добавляется другой файл.вопрос мой . Этот процесс работает нормально, но в двух базах данных файлы могут быть удалены, а файловая группа - нет. Удивительно, но структура этих баз данных должна быть такой же, как и структура других баз данных, где не возникало никаких проблем в процессе перемещения данных и удаления старых файловых групп.
Итак, вот список файловой группы и файлов двух баз данных, где возникает проблема:
Я надеюсь, что это немного помогает. Есть также вторая база данных, в которой имена файловых групп отличаются, но я оставлю это для краткости.
В этом разделе описывается удаление уничтоженных файловых групп в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL.
В этом разделе
Перед началом работы
Удаление уничтоженных файловых групп с использованием следующих средств:
Перед началом
Ограничения
Сведения в этом разделе относятся к базам данных SQL Server , содержащим несколько файлов или файловых групп (а для простой модели восстановления — к файловым группам, доступным только для чтения).
При удалении файловой группы вне сети все файлы группы помечаются удаленными.
Рекомендации
Если восстановление невосстановленной файловой группы не предполагается, необходимо сделать ее уничтоженной , удалив из базы данных. Такая файловая группа не может быть восстановлена в данной базе данных, однако при этом сохраняются ее метаданные. После того как файловая группа стала уничтоженной, базу данных можно перезапустить, и в процессе восстановления будет восстановлена согласованность базы данных между восстановленными файловыми группами.
Например, объявление файловой группы как нефункционирующей позволяет разрешить отложенные транзакции, возникшие из-за файловой группы вне сети, которая больше не нужна в базе данных. Транзакции, отложенные из-за того, что файловая группа находилась в режиме «вне сети», выходят из отложенного состояния после того, как эта файловая группа перестанет функционировать. Дополнительные сведения см. в разделе Отложенные транзакции (SQL Server).
безопасность
Permissions
Необходимо разрешение ALTER на базу данных.
Использование среды SQL Server Management Studio
Удаление уничтоженных файловых групп
В обозревателе объектов подключитесь к экземпляру компонента Компонент SQL Server Database Engine и разверните его.
Раскройте список Базы данных, щелкните правой кнопкой мыши базу данных, из которой удаляется файл, а затем выберите пункт Свойства.
Выберите страницу Файлы .
В списке Файлы базы данных выберите файлы для удаления, нажмите кнопку Удалить, а затем кнопку ОК.
Выберите страницу Файловые группы .
В списке Строки выберите файловую группу для удаления, нажмите кнопку Удалить, а затем кнопку ОК.
Использование Transact-SQL
Удаление уничтоженных файловых групп
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. (Примечание. В этом примере предполагается, что файлы и файловая группа уже существуют. Для создания этих объектов см. пример Б в разделе Параметры инструкции ALTER DATABASE для файлов и файловых групп.) В первом примере удаляются файлы test1dat3 и test1dat4 из уничтоженной файловой группы с помощью инструкции ALTER DATABASE с предложением REMOVE FILE . Во втором примере удаляется уничтоженная файловая группа Test1FG1 с помощью предложения REMOVE FILEGROUP .
Читайте также: