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

Откуда:
Сообщений: 20
Добрый день.

Помогите, пожалуйста, я скорее программист, чем администратор, но приходится решать проблему со сжатием базы данных. Поиском воспользовался, 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: )

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

Заранее признателен,
Сергей.
18 янв 19, 11:08    [21788549]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1466
Sergey Gusev,

"Бытовая аналогия"(с) с соседней ветки форума
18 янв 19, 11:43    [21788599]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1466
Sergey Gusev,

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

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

Все операции - на ваш страх и риск.
На стандартной редакции скуль сервера - перестроение таблиц и индексов - блокирующая операция, без фозможности выполнения с опцией ONLINE
18 янв 19, 11:49    [21788605]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Sergey Gusev
Shink пришет, что для DATA свободного пространства - 0%, минимальный размер файла равен текущему размеру

покажите результат sp_spaceused по данной базе
18 янв 19, 12:13    [21788627]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
Sergey Gusev,

зачем Вы ее сжимаете, в базе никогда не появятся новые данные?
18 янв 19, 12:33    [21788661]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Владислав Колосов
Sergey Gusev,

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

да и не сжимается у него.
пишет же, свободного места нет.
и если это так,
то вопрос ТС законный, кто держит освободившееся после удаления место.
и например, это кучи.
или пустые LOB-страницы
и у них неплохо бы место забрать
18 янв 19, 12:36    [21788668]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
Yasha123,

не знаете ли - куча отдаст место, если удалить все записи?
18 янв 19, 12:40    [21788672]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 20
Владислав Колосов
Sergey Gusev,

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


Пока не сжимаю, рассматриваю как вариант. А что, сжатие доступно только для архивов? Вообще, база активно используется и в те таблицы, которые я собираюсь сжимать, будут идти записи.
18 янв 19, 12:46    [21788679]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 20
Yasha123
Sergey Gusev
Shink пришет, что для DATA свободного пространства - 0%, минимальный размер файла равен текущему размеру

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


Вот по трём самым большим таблицам -

К сообщению приложен файл (SpaceUsed.xlsx - 8Kb) cкачать
18 янв 19, 12:55    [21788691]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Владислав Колосов
Yasha123,
не знаете ли - куча отдаст место, если удалить все записи?

если с таблоком удалять, то отдает.
если без таблока, не отдает.
хоть все, хоть не все
18 янв 19, 12:57    [21788694]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Sergey Gusev
Yasha123
пропущено...

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


Вот по трём самым большим таблицам -

вроде пишу русским языком:
результат sp_spaceused по базе.
без параметров sp_spaceused запустите
18 янв 19, 12:58    [21788697]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 20
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
18 янв 19, 13:05    [21788710]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
Sergey Gusev,

+
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.
18 янв 19, 13:20    [21788727]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 20
Владислав Колосов
С наибольшим waste_pc - кандидаты на alter table ... rebuild.


Спасибо!
18 янв 19, 13:23    [21788730]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
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

ну вы же видите, у вас нет свободного места в базе (unallocated space 3.40 MB).
поэтому шринк и не проходит, отрезать нечего.
если треть записей удалена, то место свободное есть,
но оно продолжает числиться за объектами, которые вы почистили.
скорее всего это ЛОБы, или же вы удаляли данные из куч без таблока.
вам виднее, какой случай ваш.
ЛОБы это varchar(max), varbinary(max), xml
18 янв 19, 13:26    [21788734]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9265
Sergey Gusev,

Покажите результат
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');
18 янв 19, 13:29    [21788735]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Владислав Колосов
Sergey Gusev,

+
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

с ЛОБами сейчас не могу проверить, может попозже

К сообщению приложен файл. Размер - 35Kb
18 янв 19, 13:36    [21788739]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 20
Yasha123
ну вы же видите, у вас нет свободного места в базе (unallocated space 3.40 MB).
поэтому шринк и не проходит, отрезать нечего


Собственно, об этом я и написал в самом первом сообщении. Удалил треть записей в самых больших таблицах, а уменьшить файлы не могу.
18 янв 19, 13:40    [21788744]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
invm
[моя БД].sys.dm_db_index_physical_stats(0, null, null, null, 'detailed');

это все равно полезет во все базы,
даже если из нужной вызвать.
надо
sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed');
18 янв 19, 13:41    [21788747]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 20
Yasha123
надо
sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed');


Это вернуло

total_tables heap_tables unused_heap_space
112 0 0
18 янв 19, 13:57    [21788764]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
LOB-ы удаляли?
18 янв 19, 13:59    [21788765]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 20
Yasha123
LOB-ы удаляли?


Нет, пока даже не знаю, что это такое.
18 янв 19, 14:51    [21788796]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Sergey Gusev
Yasha123
LOB-ы удаляли?


Нет, пока даже не знаю, что это такое.

вообще-то выше написано: это (n)text, image, varbinary(max), (n)varchar(max), xml.
если это добро было в удаляемых строках, по идее помогает alter index.. reorganize.
когда не помогает, остается только перелив в новую таблицу с последующим ее переименованием
18 янв 19, 15:13    [21788821]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 20
Yasha123
вообще-то выше написано: это (n)text, image, varbinary(max), (n)varchar(max), xml.


То есть удаляя запись, MS SQL не удаляет связанные с ними большие данные? А как это тогда решать? Предварительное обнуление этих полей решит проблему?
18 янв 19, 15:32    [21788849]     Ответить | Цитировать Сообщить модератору
 Re: База данных не уменьшается после удаления записей  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 20
Yasha123
если это добро было в удаляемых строках, по идее помогает alter index.. reorganize.


Это наверное, имеется в виду - кластерные индексы только?
18 янв 19, 15:42    [21788859]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить