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

Откуда: Чебаркуль
Сообщений: 3466
У юзера есть две базы: одна - бекап конца 2015 года (), вторая нынешняя,

-- старая
-- db size = 40567.13 MB
-- Space available= 2505.19 MB
-- users = 15
-- Data = 27659.13 MB (free 2504.19 MB (9%))
-- Log = 12829.49 MB (99% free)

-- нвнешняя
-- db size = 231759.94 MB
-- Space available= 12635.14 MB
-- users = 15
-- Data = 228361.06 MB (free 12631.50 MB (5%))
-- Log = 3398.88 MB (3384.91 MB (99%) free)

разница в размере = 180 ГБ, пытаюсь понять - откуда такой объем.

Сделал две временные таблицы и запросыиз каждой базы

create table ##tsize_old
(
name nvarchar(128),
rows varchar(18),
reserved varchar(18),
data varchar(18),
indexsize varchar(18),
unused varchar(18)
)

insert into ##tsize_old exec sp_msforeachtable 'EXEC sp_spaceused [?]'

select old.*,new.*
,cast(replace(new.rows,N' KB','') as int) - cast(replace(old.rows,N' KB','') as int) as rows_diff,
cast(replace(new.reserved,N' KB','') as int) - cast(replace(old.reserved,N' KB','') as int) as reserved_diff,
cast(replace(new.data,N' KB','') as int)-cast(replace(old.data,N' KB','') as int) as data_diff,
cast(replace(new.indexsize,N' KB','') as int)-cast(replace(old.indexsize,N' KB','') as int) as indexsize_diff,
cast(replace(new.unused,N' KB','') as int)-cast(replace(old.unused,N' KB','') as int) as unused_diff
from ##tsize_old old left outer join ##tsize_new new on old.name=new.name
order by new.name


поля, в которые считаю разницу в сумме содержат от силы 10-20 гигабайт.

Вспоможыте: как понять, что и где растет?
19 июл 17, 17:45    [20658596]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30829
Ролг Хупин
Вспоможыте: как понять, что и где растет?
Посмотрите на список таблиц с размерами
SELECT 
	schema_name(o.schema_id) + '.' +  o.name as [Table Name], 
	fg.name as [File Group],
	SUM (p.reserved_page_count) as [reserved_page_count],
	convert(decimal(13,2), SUM (p.reserved_page_count) * 8. / 1024 / 1024) as [reservedpages GB],
	SUM (p.used_page_count) as [used_page_count],
	convert(decimal(13,2), SUM (p.used_page_count) * 8. / 1024 / 1024) as [usedpages GB],
	SUM (
		CASE
			WHEN (p.index_id < 2) THEN (p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count)
			ELSE p.lob_used_page_count + p.row_overflow_used_page_count
		END
		) as pages,
	SUM (
		CASE
			WHEN (p.index_id < 2) THEN p.row_count
			ELSE 0
		END
		) as [rowCount]
FROM sys.dm_db_partition_stats p (nolock)
	join sys.objects as o (nolock)
		on o.object_id = p.object_id
		and o.type = 'U'
	join sys.sysindexes i (nolock)
		on i.id = o.object_id
		and i.indid in (0,1)
	join sys.filegroups fg (nolock)
		on fg.data_space_id = i.groupid
group by o.name, o.schema_id, fg.name
order by [reservedpages GB] desc
19 июл 17, 22:38    [20659243]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Ролг Хупин,

ну кроме таблиц есть ещё индексы. Обслуживание делаете?
20 июл 17, 08:45    [20659687]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Вангую. Full Recovery Model + не делается бекап лога.
20 июл 17, 09:37    [20659935]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30829
AlanDenton
Вангую. Full Recovery Model + не делается бекап лога.
Размер файла лога нулевой.
20 июл 17, 09:38    [20659943]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
CrazyDr1v3r
Guest
Ролг Хупин,

exec sp_spaceused @updateusage = 'true' ?
20 июл 17, 10:12    [20660101]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
AlanDenton
Вангую. Full Recovery Model + не делается бекап лога.


Simple
20 июл 17, 10:38    [20660222]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
alexeyvg
Ролг Хупин
Вспоможыте: как понять, что и где растет?
Посмотрите на список таблиц с размерами
...


Посмотрел, а потом просуммировал, ну, не вижу 240 ГБ

select sum([usedpages GB]),sum([reservedpages GB])
from (
SELECT 
	schema_name(o.schema_id) + '.' +  o.name as [Table Name], 
	fg.name as [File Group],
	SUM (p.reserved_page_count) as [reserved_page_count],
	convert(decimal(13,2), SUM (p.reserved_page_count) * 8. / 1024 / 1024) as [reservedpages GB],
	SUM (p.used_page_count) as [used_page_count],
	convert(decimal(13,2), SUM (p.used_page_count) * 8. / 1024 / 1024) as [usedpages GB],
	SUM (
		CASE
			WHEN (p.index_id < 2) THEN (p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count)
			ELSE p.lob_used_page_count + p.row_overflow_used_page_count
		END
		) as pages,
	SUM (
		CASE
			WHEN (p.index_id < 2) THEN p.row_count
			ELSE 0
		END
		) as [rowCount]
FROM sys.dm_db_partition_stats p (nolock)
	join sys.objects as o (nolock)
		on o.object_id = p.object_id
		and o.type = 'U'
	join sys.sysindexes i (nolock)
		on i.id = o.object_id
		and i.indid in (0,1)
	join sys.filegroups fg (nolock)
		on fg.data_space_id = i.groupid
group by o.name, o.schema_id, fg.name
) t


--48.29 49.06

Есть еще FTC, но там 1 ГБ.
20 июл 17, 10:43    [20660261]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30829
Ролг Хупин
Посмотрел, а потом просуммировал, ну, не вижу 240 ГБ
А чем смотрите 240 ГБ?
exec sp_spaceused @updateusage = 'true' ?
Размер файла тоже соответствует 240 ГБ?
20 июл 17, 10:58    [20660403]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
alexeyvg
Ролг Хупин
Посмотрел, а потом просуммировал, ну, не вижу 240 ГБ
А чем смотрите 240 ГБ?
exec sp_spaceused @updateusage = 'true' ?
Размер файла тоже соответствует 240 ГБ?


240 или чуть меньше.

Руками в SSMS: database->Properties

Size: 231759.94 MB
Space Available: 12635.14 MB

На диске:
mdf: 233 841 728 КБ
ldf: 3 480 448 КБ
20 июл 17, 11:06    [20660474]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Ролг Хупин,

CLEANTABLE попробуйте сделать
20 июл 17, 11:12    [20660527]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
o-o
Guest
SELECT sch.[name], obj.[name], ISNULL(obj.[type_desc], N'TOTAL:') AS [type_desc],
       COUNT(*) AS [ReservedPages],
       (COUNT(*) * 8) AS [ReservedKB],
       (COUNT(*) * 8) / 1024.0 AS [ReservedMB],
       (COUNT(*) * 8) / 1024.0 / 1024.0 AS [ReservedGB]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
INNER JOIN sys.all_objects obj
        ON obj.[object_id] = pa.[object_id]
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = obj.[schema_id]
GROUP BY GROUPING SETS ((sch.[name], obj.[name], obj.[type_desc]), ())
ORDER BY [ReservedPages] DESC;
20 июл 17, 11:15    [20660562]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
WarAnt
Ролг Хупин,

CLEANTABLE попробуйте сделать


Сделал CLEANTABLE для всех таблиц
Сделал
exec sp_spaceused @updateusage = 'true'
-- reserved data index unused
-- 220903192 KB 210557160 KB 9522600 KB 823432 KB

после того
-- 217756248 KB 207381160 KB 9522600 KB 852488 KB

Практически то же осталось
20 июл 17, 13:21    [20661264]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36698
Ролг Хупин
Посмотрел, а потом просуммировал, ну, не вижу 240 ГБ
...
and i.indid in (0,1)
...


Еще как бы бывают некластерные инлдексы, они тоже место занимают.
20 июл 17, 13:27    [20661288]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
o-o
SELECT sch.[name], obj.[name], ISNULL(obj.[type_desc], N'TOTAL:') AS [type_desc],
       COUNT(*) AS [ReservedPages],
       (COUNT(*) * 8) AS [ReservedKB],
       (COUNT(*) * 8) / 1024.0 AS [ReservedMB],
       (COUNT(*) * 8) / 1024.0 / 1024.0 AS [ReservedGB]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
INNER JOIN sys.all_objects obj
        ON obj.[object_id] = pa.[object_id]
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = obj.[schema_id]
GROUP BY GROUPING SETS ((sch.[name], obj.[name], obj.[type_desc]), ())
ORDER BY [ReservedPages] DESC;


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

NULL NULL TOTAL: 27216168 217729344 212626.312500 207.642883300781
sys sysxmitqueue SYSTEM_TABLE 19140500 153124000 149535.156250 146.030426025390
dbo t1 USER_TABLE 3761179 30089432 29384.210937 28.695518493164
sys sysdercv SYSTEM_TABLE 1719187 13753496 13431.148437 13.116355895507
dbo x1 USER_TABLE 895021 7160168 6992.351562 6.828468322265
dbo n1 USER_TABLE 365480 2923840 2855.312500 2.788391113281
sys sysdesend SYSTEM_TABLE 282620 2260960 2207.968750 2.156219482421
dbo p1 USER_TABLE 125108 1000864 977.406250 0.954498291015
20 июл 17, 13:36    [20661331]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
Да, но эта таблица находится не в юзерской базе

sys sysxmitqueue SYSTEM_TABLE 19140500 153124000 149535.156250 146.030426025390
20 июл 17, 13:39    [20661360]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Ролг Хупин,
из гугла
очередь зависшего брокера
https://habrahabr.ru/post/273633/
20 июл 17, 13:46    [20661417]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
TaPaK
Ролг Хупин,
из гугла
очередь зависшего брокера
https://habrahabr.ru/post/273633/


это да, но это же
"История про msdb размером в 42 Гб"

А у меня юзерская база, даже восстановленная на другом сервере показывает размер, который я пытаюсь понять откуда он взялся.
Если бы брокерские сообщение жили в этой же базе - можно было бы считать что причина понятна.
С другой стороны - откуда в восстановленной на другом сервере базе невычитанные брокерские сообщения?
20 июл 17, 13:49    [20661441]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
CrazyDr1v3r
Guest
Ролг Хупин,

А почему в пользовательской базе не может быть сообщений брокера? Брокер - он per database.
20 июл 17, 13:54    [20661468]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Ролг Хупин
TaPaK
Ролг Хупин,
из гугла
очередь зависшего брокера
https://habrahabr.ru/post/273633/


это да, но это же
"История про msdb размером в 42 Гб"

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

вы только заголовок читаете? указанная таблица ysxmitqueue относится к сервер брокеру
20 июл 17, 13:59    [20661504]     Ответить | Цитировать Сообщить модератору
 Re: Почему растёт размер базы?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
TaPaK
Ролг Хупин
пропущено...


это да, но это же
"История про msdb размером в 42 Гб"

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

вы только заголовок читаете? указанная таблица ysxmitqueue относится к сервер брокеру


нервы ни в ***** (ц, анекдот)

Да, видимо это и есть причина, на ровном месте незабранные из очереди записи.
20 июл 17, 14:05    [20661533]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить