Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
MSSQL 2008 on Windows 2008 Server.

Прошу прощения, что длинно, но не знаю как по-другому написать, чтоб была понятна суть проблемы. :(

В один момент накосячили с файлами, кончилось места, база грязно выругалась, что нет места расширять transaction_log, пришла не очень умная мысль создать еще один файл лога. Потом второй файл лога вроде удалили. Но как-то он не очень полностью удалился. К сожалению, всё это ранее происходившее сам не видел, потому всех деталей точно не знаю.

Что имеем сейчас:
есть база (пусть имя будет db)
Заходим в свойства базы, страница "Файлы". Список "Файлы базы данных".
Видим 2 строки:
логическое имя "db", тип "Данные строк", Файл.группа "PRIMARY", ну и далее объём-путь и т.д.
логическое имя "db_log", тип "Журнал", Файл.группа "Не применимо", ну и далее объём-путь и т.д.

Вроде всё ок.
Запускаем на всякий случай
DBCC CHECKDB(db)
получаем
CHECKDB обнаружил 0 ошибок размещения и 0 ошибок согласованности в базе данных "db".

Вроде всё ок.

Делаем полный бэкап базы средствами Studio в новое хранилище. Бэкап делается без ошибок, всё ок.
Далее тут же делаем восстановление БД с выбором "из базы данных". Из этой же. На странице "Общие" всё ок, а вот на странице "Параметры" в списке "Восстановить файлы базы данных как" список из ТРЁХ файлов, где третьим тот удалённый файл лога (условное имя путь у него будет db_log2). Причём в "Тип файла" у него почему-то "Данные строк".

При этом если (ничего не меняя) продолжить развёртывание бэкапа - он восстановится без ошибок, останется 2 файла (db + db_log) физически на диске.

Если бэкап развернуть в вообще новую базу с выбором "С устройства" - там будет опять же нормально файл данных + файл лога, но если сделать с новой базы бэкап и попробовать его развернуть - опять вылезет этот второй файл лога.

Если же сделать вот так:
SELECT TOP 10 * FROM [msdb].[dbo].[backupfile] ORDER BY backup_set_id DESC
то там получаются последние по id 3 строки имеют такие значения:

logical_name: db db_log db_log2
file_type: D L D
state: 0 0 0
state_desc: ONLINE ONLINE DROPPED
is_present: 1 1 0

Вопрос: как убить из бэкапов (и вообще убить!) этот db_log2? В самой базе он нигде вообще не виден, наблюдается только в бэкапах...

Всем заранее больше спасибо за ответ на дурацкий вопрос.
9 апр 15, 18:14    [17495191]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
Прошу прощения, опечатка и что-то не пойму как исправить:
В выводе запроса в конце стартового поста строку поля statу надо было вот так:
state:        0        0             8
9 апр 15, 18:17    [17495213]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
andysh888, тип у него D, то есть Data? В этом случае это вовсе не журнал.
9 апр 15, 18:34    [17495318]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
Меня это тоже смущает.
Те, кто натворил - утверждают, что добавляли файл лога.
И ему не присвоена файловая группа.

Я просто никак не могу понять - как его изничтожить совсем. :(
9 апр 15, 20:22    [17495765]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
o-o
Guest
andysh888
как убить из бэкапов (и вообще убить!) этот db_log2? В самой базе он нигде вообще не виден, наблюдается только в бэкапах...

а на диске он наблюдается?
вы вроде запрос привели со звездой,
но показываете только выборочные поля результата.
там ведь есть еще physical_name,
можно по тому пути посмотреть, что за файл
9 апр 15, 21:08    [17495939]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
Да, запрос со звездой.
Путь к файлу (physical_name) ведёт на D:\MSSQL_DATA\db_log2.ldf

Такого файла на диске нет.
9 апр 15, 21:27    [17495995]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
o-o
Guest
я сейчас попробую создать и удалить и второй файл данных, и второй файл лога.
посмотрю, пойдут ли они в бэкап.
может, файл присутствовал на момент бэкапа и запись о нем была,
а теперь его нет, запись о нем не удаляют, но прописывают DROPPED?
в хэлпе по backupfile почему-то нет никакого state 8 вообще, только 0..7.
равно как и нет state_desc DROPPED

еще приведите полную версию сервера, т.е. результат select @@version
9 апр 15, 21:56    [17496063]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
o-o
Guest
в общем, публикуйте версию,
какое-нибудь глючное явление непропатченного RTM.

вот у меня история бэкапов базы в порядке:
309: 2 ФГ, 2 файла данных, 2 файла лога
310: 2 ФГ, 2 файла данных, 1 файл лога
311: 2 ФГ, 1 файла данных, 1 файл лога
312: 1 ФГ, 1 файла данных, 1 файл лога.
никакого нигде DROPPED, никакого статуса 8.
ФГ уже нету, но в истории все еще есть, равно как и пути к файлам, к-ых нет

backup_set_id logical_name file_type state state_desc is_present physical_name filegroup_name
312 db_2_log L 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2_log.LDF NULL
312 db_2 D 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2.mdf PRIMARY
311 db_2_log L 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2_log.LDF NULL
311 db_2 D 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2.mdf PRIMARY
310 db_2_dat D 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2_dat.ndf FG1
310 db_2_log L 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2_log.LDF NULL
310 db_2 D 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2.mdf PRIMARY
309 db_2_log2 L 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2_log2.ldf NULL
309 db_2_dat D 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2_dat.ndf FG1
309 db_2_log L 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2_log.LDF NULL
309 db_2 D 0 ONLINE 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_2.mdf PRIMARY


Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
9 апр 15, 23:55    [17496321]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
Версия сервера:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

Попытался ставить опыты на копии базы.
Сделал следующее:
1. Добавил файл данных с таким же логическим именем и путём к файлу, как и у файла-невидимки. И удалил. Добавилось-удалилось. Добавлял в файловую группу PRIMARY.

2. Добавил файл лога с таким же логическим именем и путём. Добавилось. Но... Не удаляется:

USE db
DBCC SHRINKFILE('db_log2',EMPTYFILE)
ALTER DATABASE db REMOVE FILE db_log2

выдаёт:

Сообщение 8985, уровень 16, состояние 2, строка 2
Не удалось найти файл "3" для базы данных "db" в sys.database_files. Файл не существует или был удален.
Сообщение 5009, уровень 16, состояние 9, строка 3
Не удалось найти или инициализировать один или несколько файлов, перечисленных в инструкции.

При этом:
EXEC sp_helpfile
namefileidfilenamefilegroupsizemaxsizegrowthusage
db1D:\MSSQL_DATA\db.mdfPRIMARY32281600 KBUnlimited10%data only
db_log2D:\MSSQL_DATA\db_log.ldfNULL516480 KB2147483648 KB10%log only
db_log24D:\MSSQL_DATA\db_log2.ldfNULL1024 KB2147483648 KB10%log only


Вопрос - откуда лезет fileid 3?
10 апр 15, 05:01    [17496618]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
Да, забыл дописать - файл D:\MSSQL_DATA\db_log2.ldf на диске имеется.
10 апр 15, 05:06    [17496619]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
Обновился до SP3.
Удаление файла лога прошло (ошибка 5009 не лезет).

Сделал бэкап в новое хранилище, при попытке восстановить из базы - опять в списке db_log2 :(
10 апр 15, 06:21    [17496656]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
Не угадал :(
Это команда на удаление завершилась первый раз без ошибок.
Файл удалился с диска, но остался в SQL.

В результате:
select * from sys.master_files where database_id=DB_ID('db')
database_idfile_idfile_guidtypetype_descdata_space_idnamephysical_namestatestate_descsizemax_sizegrowthis_media_read_onlyis_read_onlyis_sparseis_percent_growthis_name_reservedcreate_lsndrop_lsnread_only_lsnread_write_lsndifferential_base_lsndifferential_base_guiddifferential_base_timeredo_start_lsnredo_start_fork_guidredo_target_lsnredo_target_fork_guidbackup_lsn
32101225E3C-5D85-437E-A410-2B6E7AADEB4A0ROWS1dbD:\MSSQL_DATA\db.mdf0ONLINE4035200-11000010NULLNULLNULLNULL650211000000123400040D99181BF-894E-41E5-994E-B64C791D3CE42015-04-10 03:16:16.473NULLNULLNULLNULLNULL
32255F0ED8C-F127-440F-91C2-272E3099FC261LOG0db_logD:\MSSQL_DATA\db_log.ldf0ONLINE645602684354561000010NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
324A9438BCC-F76F-41EE-AA8A-7F9562F2242B1LOG0db_log2D:\MSSQL_DATA\db_log2.ldf6OFFLINE12684354561000011650211000000079900001650211000000120300001NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL


Да, версия сервера после обновления:
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64) Aug 22 2012 19:25:47 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

При попытке
ALTER DATABASE db REMOVE FILE db_log2
получаю
Сообщение 5009, уровень 16, состояние 9, строка 1
Не удалось найти или инициализировать один или несколько файлов, перечисленных в инструкции.

Файла D:\MSSQL_DATA\db_log2.ldf на диске нет.
10 апр 15, 07:01    [17496693]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
o-o
Guest
andysh888,

а в выводе команды dbcc loginfo
есть ли fileid 4?
еще можно с копией базы произвести такое:
удалить все объекты, шринкануть до минимума, сделать бэкап и выложить на файлообменник.
я могу скачать и посмотреть, что это с базой
(пятница. фантазия на нуле. проще посмотреть, что имеем, чем придумать, как это воспроизвести)
10 апр 15, 10:25    [17497142]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
o-o
Guest
минуточку, а у вас модель не полная случайно?
с простой будет все как у меня, второй файл лога удалился начисто,
а с полной -- фокусы с оффлайном
10 апр 15, 10:35    [17497210]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
o-o
Guest
в общем, лечится это бэкапом лога.
при удалении файла лога (база в full recovery model), он физически удаляется,
но в базе "числится" в виде offline-файла.
бэкап лога затирает о нем последние воспоминания.
возможно, это даже где-то документировано
10 апр 15, 10:49    [17497293]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
Игры со сменой модели восстановления приводят к выпилу "лишнего" лога из самой базы (то есть решается проблема ошибки 5009), но в бэкапах как DROPPED он остаётся. :(
10 апр 15, 14:54    [17499038]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
o-o
Guest
andysh888,

действительно, в бэкапах файл остается с не отраженным в документации state = 8, state_desc = DROPPED.
заодно у удаленного файла лога заполнилось поле drop_lsn,
к-ое в документации описано так: Log sequence number at which the file was dropped. Can be NULL.
If the file has not been dropped, this value is NULL.

т.е. сами прямо-таки намекают, что собираются хранить инфо для дропнутых файлов.
будет время, поищу, зачем хранят

но вообще у них какой-то непорядок со статьей про этот backupfile,
на практике state бывает или ONLINE, или DROPPED.
а у них там в описании все, кроме этого,
лучше сказать, какие-то вообще "не совсем подходящие для бэкапа" состояния...
они что, хотят сказать, что на момент бэкапа файлы находятся вот в таких состояниях:
ONLINE RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT OFFLINE DEFUNCT
???
бардак какой-то
10 апр 15, 16:30    [17499610]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
o-o
Guest
вот про то, почему удаленный лог-файл так и таскался до бэкапа лога:
sys.database_files (Transact-SQL)

Column name Data type Description
is_name_reservedbit1 = Dropped file name (name or physical_name) is reusable only after the next log backup. When files are dropped from a database the logical names stay in a reserved state until the next log backup. This column is relevant only under the full recovery model and the bulk-logged recovery model.

а про то, зачем в бэкапе хранят удаленное, даже не знаю на что и искать
10 апр 15, 17:21    [17499865]     Ответить | Цитировать Сообщить модератору
 Re: Полностью удалить файл transaction_log  [new]
andysh888
Member

Откуда:
Сообщений: 10
Я пробовал менять на SIMPLE
Всё равно остаётся в бэкапах это всё :(
10 апр 15, 18:16    [17500119]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить