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

Откуда:
Сообщений: 19
Доброго времени суток!

Имеется база данных на MS SQL 2005 Express. Работа с ней осуществляется через службу, написанную на C++.
Проблема заключается в том, что база стала быстро заполняться.

Как выяснилось, всё дело в поле типа image и откате транзакции.

Провели эксперимент.

Имеется таблица:
CREATE TABLE [dbo].[test_table](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[text1] [varchar](50) NULL,
	[file] [image] NULL,
	[text2] [varchar](500) NULL,
	[integer] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


Последовательность действий:
- начало транзакции
- вставка записи в таблицу, которая содержит поле типа image
- откат транзакции

После этих действий, в таблице [test_table] нет записей.
Вот этот запрос:
select
SUM
(
	DATALENGTH( [id] ) +
	DATALENGTH( [text1] ) +
	DATALENGTH( [file] ) +
	DATALENGTH( [text2] ) +
	DATALENGTH( [integer] )
) / 1024
from [test_table] with( nolock )

возвращает NULL.
НО! Файл транзакций вырос. И поле [sys].[allocation_units].[total_pages] для таблицы [test_table] выросло ( type_desc = LOB_DATA ).

DBCC SHRINKFILE для файла лога уменьшает его практически до 0. Если после этого выполнить DBCC SHRINKDATABASE, то значение в поле [sys].[allocation_units].[total_pages] для таблицы [test_table] уменьшится, но совсем незначительно.

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

Подскажите, в чём может быть дело? Заранее спасибо!

ЗЫ Эксперименты проводились и на других версиях MS SQL. Также проверяли на полной версии сервера. Результат тот же - база "растёт".

ЗЗЫ Такое же поведение замечено и при использовании поля типа text.
5 авг 14, 16:09    [16402126]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
artsb
НО! Файл транзакций вырос.

А вы думали, что транзакции бесплатны ?

artsb
В итоге, после цикла вставки/отката, мы имеем "переполненную" базу данных,

Что это значит ? Какое сообщение вы получаете ? для какой команды ?
5 авг 14, 16:13    [16402156]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Glory
А вы думали, что транзакции бесплатны ?

Нет, конечно) Но мы используем модель Simple. А насколько мне известно, после отката транзакции, эти данные должны быть удалены.

Glory
Что это значит ? Какое сообщение вы получаете ? для какой команды ?

В MS SQL 2005 Express стоит ограничение на размер базы данных. Вот об этом "переполнении" я и говорю - база достигает "потолка". Какое именно сообщение возвращает сервер, я не помню, но речь идёт об отсутствии свободного места.
Одна из таблиц, судя по [sys].[allocation_units] занимает практически 9 Гб. Хотя, запрос:
select SUM( DATALENGTH( [file] ) ) / 1024
from [test_table] with( nolock )

говорит, что там "лежит" всего на всего около 300 Мб. Остальные поля не в счёт, т.к. они занимают мизер.
5 авг 14, 16:27    [16402277]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
artsb
Нет, конечно) Но мы используем модель Simple. А насколько мне известно, после отката транзакции, эти данные должны быть удалены.

Осовобождение места файле ужрнала не означает автоматическое уменьшение его физического рамера.

artsb
Вот об этом "переполнении" я и говорю - база достигает "потолка".

"Достигаю потолка" - это сообщение об ошибке ?

artsb
Одна из таблиц, судя по [sys].[allocation_units] занимает практически 9 Гб. Хотя, запрос:
select SUM( DATALENGTH( [file] ) ) / 1024
from [test_table] with( nolock )


говорит, что там "лежит" всего на всего около 300 Мб.

А кто тогда "говорит" про 9Гб ?
5 авг 14, 16:34    [16402356]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Glory
"Достигаю потолка" - это сообщение об ошибке ?

artsb
Какое именно сообщение возвращает сервер, я не помню, но речь идёт об отсутствии свободного места.

Сейчас, к сожалению, не могу выложить скрин, т.к. таблица вылечена и это сообщение больше не появляется. Пока...

Glory
А кто тогда "говорит" про 9Гб ?

select  t.name as TableName, Min(t.create_date) as CreateDate, ds.name as FileGroupName, SUM(u.total_pages) * 8 as SizeKB 
from sys.tables as t
inner join sys.partitions as p on t.object_id = p.object_id
inner join sys.allocation_units as u on p.partition_id = u.container_id
inner join sys.data_spaces as ds on u.data_space_id = ds.data_space_id
where t.name = 'test_table'
group by t.name, ds.name
order by SizeKB desc
5 авг 14, 16:51    [16402451]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
Место в журнале освобождается не сразу, как и в таблицах.
Если Вы будете массово вставлять/удалять/откатывать большие объемы, то позаботьтесь о дисковом пространстве.
5 авг 14, 17:12    [16402575]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
artsb
ейчас, к сожалению, не могу выложить скрин, т.к. таблица вылечена и это сообщение больше не появляется. Пока...

Вы же уверяли, что все можно воспроизвести на раз

"Эксперименты проводились и на других версиях MS SQL. Также проверяли на полной версии сервера. Результат тот же - база "растёт"."
5 авг 14, 17:15    [16402593]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Владислав Колосов
то позаботьтесь о дисковом пространстве.

Спасибо! Но проблема не в дисковом пространстве, а в ограничении на размер базы данных.

Похоже, что у нас вот эта проблема.
Тут пишут о версии 2005, но у нас тот же эффект и в 2008 версии. Похоже, что до сих пор не вылечили...
5 авг 14, 17:17    [16402606]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Glory
Вы же уверяли, что все можно воспроизвести на раз

"Эксперименты проводились и на других версиях MS SQL. Также проверяли на полной версии сервера. Результат тот же - база "растёт"."

Совершенно верно. Видать я не совсем корректно выразился. Я имел ввиду именно то, что размер базы растёт. Ошибок при этом никаких не появляется.
5 авг 14, 17:19    [16402615]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Glory, ошибка появляется только на версии Express и только тогда, когда размер базы достигает "потолка".
5 авг 14, 17:21    [16402624]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
То есть вам кто-то технически урезал размер и вы натыкаетесь на границу? Непонятна проблема.
5 авг 14, 17:22    [16402631]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
artsb
Glory, ошибка появляется только на версии Express и только тогда, когда размер базы достигает "потолка".

И что в других редакциях нельзя задать ограничение на размер базы ?
5 авг 14, 17:26    [16402644]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Владислав Колосов
То есть вам кто-то технически урезал размер и вы натыкаетесь на границу? Непонятна проблема.

Не только нам, а всем) Это ограничение бесплатной версии Express.
5 авг 14, 17:31    [16402667]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Glory
И что в других редакциях нельзя задать ограничение на размер базы ?

Можно. Как не странно, я этого никогда не делал. Но суть не в этом. Выше по ссылке такая же проблема. Оказывается она была известна уже в 2012 году...
5 авг 14, 17:33    [16402688]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
artsb
Выше по ссылке такая же проблема.

Там нет ничего про image и постоянные отмены транзакций
5 авг 14, 17:35    [16402700]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Glory
Там нет ничего про image и постоянные отмены транзакций

Видать, автор опустил детали... ) Завтра скачаю версию 2014 и протестирую на ней. Потом отпишусь.
5 авг 14, 17:38    [16402716]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
artsb
Завтра скачаю версию 2014 и протестирую на ней. Потом отпишусь.

Вы бы лучше кластернывй индекс построили
Чтобы хотя бы можно было сделать ALTER INDEX WITH (LOB_COMPACTION=ON)
5 авг 14, 17:41    [16402732]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
Изменение типа на VARBINARY(max) пробовали? И повысить версию сервера.
Также если откат - это часть бизнес-логики, а не обработки ошибки, то сначала чекайте, потом начинайте транзакцию.
5 авг 14, 17:41    [16402733]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Извиняюсь за опоздание. Хорошие новости! В версии 2014 (а может и в 2012) всё починили: ни на грамм не вырастает база после отката.

Glory
Вы бы лучше кластернывй индекс построили
Чтобы хотя бы можно было сделать ALTER INDEX WITH (LOB_COMPACTION=ON)

Попробовал. Не помогло.

Владислав Колосов
Изменение типа на VARBINARY(max) пробовали?

Нет. Этот вариант не пробовал.
Владислав Колосов
И повысить версию сервера.

Скорее всего, так и поступим)
Владислав Колосов
Также если откат - это часть бизнес-логики, а не обработки ошибки, то сначала чекайте, потом начинайте транзакцию.

Да, к сожалению, код был написан именно в таком виде, что сначала начиналась транзакция, а потом происходили некоторые проверки. По сути, ошибки в этом нет. Очевидно, что проблема была с SQL-сервером.
7 авг 14, 12:58    [16411251]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
автор
сначала начиналась транзакция, а потом происходили некоторые проверки


Советую переписать, т.к. вы, возможно, зря блокируете таблицы и заполняете журнал, впустую наращивая время выполнения.
7 авг 14, 13:16    [16411385]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение базы данных  [new]
artsb
Member

Откуда:
Сообщений: 19
Владислав Колосов
Советую переписать, т.к. вы, возможно, зря блокируете таблицы и заполняете журнал, впустую наращивая время выполнения.

Согласен с вами. Но это не я писал. А информацию передам, конечно) Спасибо!
7 авг 14, 14:06    [16411745]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить