ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / База данных не уменьшается после удаления записей
28 сообщений из 28, показаны все 2 страниц
  все  
База данных не уменьшается после удаления записей
    #39761325
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Добрый день.

Помогите, пожалуйста, я скорее программист, чем администратор, но приходится решать проблему со сжатием базы данных. Поиском воспользовался, Shrink по логам сделан, Recovery Model - Simple, логи составляют всего 10 М. А база - 150 Gb. Удаляю старые данные, но при этом снижения размера базы не происходит. В Tasks->Shink пришет, что для DATA свободного пространства - 0%, минимальный размер файла равен текущему размеру (хотя треть записей удалена).

Версия:

Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64) Nov 8 2017 17:32:23 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

Если простым советом здесь не поможешь, ищу специалиста на разовую работу по снижению размера базы средствами администратора, в идеале - без реальной остановки.

Заранее признателен,
Сергей.
...
Рейтинг: 0 / 0
18.01.2019, 11:08
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761365
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
Sergey Gusev,

"Бытовая аналогия" (с) с соседней ветки форума
...
Рейтинг: 0 / 0
18.01.2019, 11:43
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761368
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
Sergey Gusev,

Тут если в двух словах и кратко, то:
1) Удалить все ненужные (на ваш взгляд) данные из таблиц
2) Устранить "фрагментацию" страниц на уровне таблиц/индексов (читать alter [table | index] rebuild)
3) Шринк файлов.

Как альтернативный вариант, вместо 2 и 3 создать новую ФГ, перенести всё в неё, старую ФГ удалить.

Все операции - на ваш страх и риск.
На стандартной редакции скуль сервера - перестроение таблиц и индексов - блокирующая операция, без фозможности выполнения с опцией ONLINE
...
Рейтинг: 0 / 0
18.01.2019, 11:49
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761388
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Sergey GusevShink пришет, что для DATA свободного пространства - 0%, минимальный размер файла равен текущему размеру
покажите результат sp_spaceused по данной базе
...
Рейтинг: 0 / 0
18.01.2019, 12:13
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761414
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Sergey Gusev,

зачем Вы ее сжимаете, в базе никогда не появятся новые данные?
...
Рейтинг: 0 / 0
18.01.2019, 12:33
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761420
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Владислав КолосовSergey Gusev,

зачем Вы ее сжимаете, в базе никогда не появятся новые данные?
да и не сжимается у него.
пишет же, свободного места нет.
и если это так,
то вопрос ТС законный, кто держит освободившееся после удаления место.
и например, это кучи.
или пустые LOB-страницы
и у них неплохо бы место забрать
...
Рейтинг: 0 / 0
18.01.2019, 12:36
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761424
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Yasha123,

не знаете ли - куча отдаст место, если удалить все записи?
...
Рейтинг: 0 / 0
18.01.2019, 12:40
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761428
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Владислав КолосовSergey Gusev,

зачем Вы ее сжимаете, в базе никогда не появятся новые данные?

Пока не сжимаю, рассматриваю как вариант. А что, сжатие доступно только для архивов? Вообще, база активно используется и в те таблицы, которые я собираюсь сжимать, будут идти записи.
...
Рейтинг: 0 / 0
18.01.2019, 12:46
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761437
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Yasha123Sergey GusevShink пришет, что для DATA свободного пространства - 0%, минимальный размер файла равен текущему размеру
покажите результат sp_spaceused по данной базе

Вот по трём самым большим таблицам -
...
Рейтинг: 0 / 0
18.01.2019, 12:55
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761439
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Владислав КолосовYasha123,
не знаете ли - куча отдаст место, если удалить все записи?
если с таблоком удалять, то отдает.
если без таблока, не отдает.
хоть все, хоть не все
...
Рейтинг: 0 / 0
18.01.2019, 12:57
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761442
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Sergey GusevYasha123пропущено...

покажите результат sp_spaceused по данной базе

Вот по трём самым большим таблицам -
вроде пишу русским языком:
результат sp_spaceused по базе .
без параметров sp_spaceused запустите
...
Рейтинг: 0 / 0
18.01.2019, 12:58
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761452
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Yasha123без параметров sp_spaceused запустите


database_name database_size unallocated space
RBCMoneyNew 161860.94 MB 3.40 MB

reserved data index_size unused
165730920 KB 148398584 KB 17289784 KB 42552 KB
...
Рейтинг: 0 / 0
18.01.2019, 13:05
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761465
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Sergey Gusev,

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
set nocount on;
declare @name sysname;

declare cur1 cursor for
	select ss.name + '.'+ tt.name obj
	from sys.tables tt
	inner join sys.schemas ss on tt.schema_id = ss.schema_id

create table #result1 (name sysname, rows bigint, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))
create table #result (name sysname, rows bigint, reserved bigint, data varchar(100), index_size varchar(100), unused bigint)

open cur1
fetch cur1 into @name
while @@FETCH_STATUS = 0
begin 
	insert #result1
		exec sp_spaceused @objname = @name;
	insert #result 
		select @name, rows, cast(replace(reserved, ' kb', '') as bigint), data, index_size, cast(replace(unused, ' kb', '') as bigint) from #result1
	delete #result1

	fetch cur1 into @name
end

close cur1
deallocate cur1

select *, iif(unused = 0, 0.01, unused)/iif(reserved = 0, 0.01, reserved) * 100.00 waste_pc from #result where reserved > 100000 order by 7 desc



С наибольшим waste_pc - кандидаты на alter table ... rebuild.
...
Рейтинг: 0 / 0
18.01.2019, 13:20
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761468
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Владислав КолосовС наибольшим waste_pc - кандидаты на alter table ... rebuild.

Спасибо!
...
Рейтинг: 0 / 0
18.01.2019, 13:23
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761471
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Sergey GusevYasha123без параметров sp_spaceused запустите


database_name database_size unallocated space
RBCMoneyNew 161860.94 MB 3.40 MB

reserved data index_size unused
165730920 KB 148398584 KB 17289784 KB 42552 KB
ну вы же видите, у вас нет свободного места в базе (unallocated space 3.40 MB).
поэтому шринк и не проходит, отрезать нечего.
если треть записей удалена, то место свободное есть,
но оно продолжает числиться за объектами, которые вы почистили.
скорее всего это ЛОБы, или же вы удаляли данные из куч без таблока.
вам виднее, какой случай ваш.
ЛОБы это varchar(max), varbinary(max), xml
...
Рейтинг: 0 / 0
18.01.2019, 13:26
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761473
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Sergey Gusev,

Покажите результат
Код: sql
1.
2.
3.
4.
5.
6.
select
 sum(case when index_id in (0, 1) then 1 else 0 end) as total_tables,
 sum(case when index_id = 0 then 1 else 0 end) as heap_tables,
 sum(case when index_id = 0 then page_count * (1 - avg_page_space_used_in_percent / 100.) else 0 end) / 128 as unused_heap_space
from
 [моя БД].sys.dm_db_index_physical_stats(0, null, null, null, 'detailed');
...
Рейтинг: 0 / 0
18.01.2019, 13:29
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761477
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Владислав КолосовSergey Gusev,

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
set nocount on;
declare @name sysname;

declare cur1 cursor for
	select ss.name + '.'+ tt.name obj
	from sys.tables tt
	inner join sys.schemas ss on tt.schema_id = ss.schema_id

create table #result1 (name sysname, rows bigint, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))
create table #result (name sysname, rows bigint, reserved bigint, data varchar(100), index_size varchar(100), unused bigint)

open cur1
fetch cur1 into @name
while @@FETCH_STATUS = 0
begin 
	insert #result1
		exec sp_spaceused @objname = @name;
	insert #result 
		select @name, rows, cast(replace(reserved, ' kb', '') as bigint), data, index_size, cast(replace(unused, ' kb', '') as bigint) from #result1
	delete #result1

	fetch cur1 into @name
end

close cur1
deallocate cur1

select *, iif(unused = 0, 0.01, unused)/iif(reserved = 0, 0.01, reserved) * 100.00 waste_pc from #result where reserved > 100000 order by 7 desc



С наибольшим waste_pc - кандидаты на alter table ... rebuild.
с кучей точно не катит.
допустим, удаляем 1млн строк из dbo.Nums (это все строки).
таблица пуста, по ней имеем (см.картинку)

ваш waste_pc = 0,14

с ЛОБами сейчас не могу проверить, может попозже
...
Рейтинг: 0 / 0
18.01.2019, 13:36
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761482
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Yasha123ну вы же видите, у вас нет свободного места в базе (unallocated space 3.40 MB).
поэтому шринк и не проходит, отрезать нечего

Собственно, об этом я и написал в самом первом сообщении. Удалил треть записей в самых больших таблицах, а уменьшить файлы не могу.
...
Рейтинг: 0 / 0
18.01.2019, 13:40
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761485
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
invm[моя БД].sys.dm_db_index_physical_stats( 0 , null, null, null, 'detailed');
это все равно полезет во все базы,
даже если из нужной вызвать.
надо
Код: sql
1.
sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed');
...
Рейтинг: 0 / 0
18.01.2019, 13:41
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761496
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Yasha123надо
Код: sql
1.
sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed');



Это вернуло

total_tables heap_tables unused_heap_space
112 0 0
...
Рейтинг: 0 / 0
18.01.2019, 13:57
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761497
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
LOB-ы удаляли?
...
Рейтинг: 0 / 0
18.01.2019, 13:59
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761519
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Yasha123LOB-ы удаляли?

Нет, пока даже не знаю, что это такое.
...
Рейтинг: 0 / 0
18.01.2019, 14:51
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761541
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Sergey GusevYasha123LOB-ы удаляли?

Нет, пока даже не знаю, что это такое.
вообще-то выше написано: это (n)text, image, varbinary(max), (n)varchar(max), xml.
если это добро было в удаляемых строках, по идее помогает alter index.. reorganize.
когда не помогает, остается только перелив в новую таблицу с последующим ее переименованием
...
Рейтинг: 0 / 0
18.01.2019, 15:13
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761565
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Yasha123вообще-то выше написано: это (n)text, image, varbinary(max), (n)varchar(max), xml.

То есть удаляя запись, MS SQL не удаляет связанные с ними большие данные? А как это тогда решать? Предварительное обнуление этих полей решит проблему?
...
Рейтинг: 0 / 0
18.01.2019, 15:32
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761572
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Yasha123если это добро было в удаляемых строках, по идее помогает alter index.. reorganize.

Это наверное, имеется в виду - кластерные индексы только?
...
Рейтинг: 0 / 0
18.01.2019, 15:42
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761602
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Sergey GusevYasha123вообще-то выше написано: это (n)text, image, varbinary(max), (n)varchar(max), xml.

То есть удаляя запись, MS SQL не удаляет связанные с ними большие данные? А как это тогда решать? Предварительное обнуление этих полей решит проблему?
типа удаляет,
но весьма криво.
данные этих типов не лежат вместе с остальными в строке данных,
они хранятся отдельно на т.н. LOB-страницах,
а в самой строке только ссылка на LOB-страницу.
короче, при удалении (у вас же все таблицы кластерные) строка помечается
как ghost record, потом при повторном чтении страницы с этой строкой
или при сканировании PFS pages Ghostcleanuptask удаляет эти ghost records.
ну и видимо на LOB-ы ему просто плевать.
так или иначе, LOB-страницы никто не высвобождает.
типа ALTER INDEX..REORGANIZE по умолчанию делает LOB COMPACTION,
но на деле и это не всегда помогает.
однозначно поможет перелив в новую таблицу.
про предварительное обнуление точно не скажу,
но по идее не поможет.
будет время (оч. сомневаюсь), проверю
...
Рейтинг: 0 / 0
18.01.2019, 16:17
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761603
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Sergey GusevYasha123если это добро было в удаляемых строках, по идее помогает alter index.. reorganize.

Это наверное, имеется в виду - кластерные индексы только?
где вы увидели слово "кластерный"?
реорганайзить можно как кластеные, так и некластерные индексы.
а куч у вас в базе просто нет, это скрипт invm показал.
...
Рейтинг: 0 / 0
18.01.2019, 16:21
| Ответить | Цитировать | Написать  
База данных не уменьшается после удаления записей
    #39761628
Sergey Gusev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Gusev
Гость
Всем большое спасибо, буду пробовать разные варианты
...
Рейтинг: 0 / 0
18.01.2019, 16:46
| Ответить | Цитировать | Написать  
28 сообщений из 28, показаны все 2 страниц
  все  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / База данных не уменьшается после удаления записей
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]