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

Откуда: Железнодорожный
Сообщений: 1842
Блог
Доброго времени суток, Уважаемые.

Ситуация следующая. Переношу пользовательские данные в новую файловую группу (ФГ). После переноса в основном файле осталось почти 3 Гб данных. Откуда они взялись, самому интересно?

проверил очереди, ассембли и т.п. Репликации нет.
интересует где искать вот эти 2963.69 МБ.

FILEID FILE_SIZE_MB SPACE_USED_MB FREE_SPACE_MB FILE_GROUP
1 64838.56 2963.69 61874.88 PRIMARY
2 313.56 5.82 307.74 Log
3 512.00 9.51 502.49 Log
4 10240.00 9143.75 1096.25 FGDATA
total 86144.12 21266.33 64877.80

посмотрел все индексы и таблицы в PRIMARY
SELECT *
FROM dbatools.v_indexes
WHERE data_space = 'PRIMARY'
AND SCHEMA_NAME NOT IN ('sys')


выдал таблицы, но они пустые.

object_id object_name index_id index_name
2099048 rep_service_index_exclude 1 PK__rep_serv__26E1381E02084FDA
1861581670 sys_params 1 PK_sys_params
1893581784 sequence 1 PK_sequence_1
1989582126 rep_service_index 0 NULL
1989582126 rep_service_index 2 UQ__rep_serv__BFCFB4DC787EE5A0
1989582126 rep_service_index 3 ix_service_index_1
1989582126 rep_service_index 4 ix_service_index_2
2117582582 rep_errors 0 NULL

посмотрел, может системные таблицы ведут, какие-то логи (как в случае с sysssislog), но самая большая 1.3 Мб.
SELECT o.name,o.type_desc,i.name,si.indid,si.rowcnt,i.data_space_id,s.name,'sp_spaceused '''+SCHEMA_NAME(o.schema_id)+'.'+o.name+''''
FROM sys.sysindexes si
JOIN sys.all_objects o ON si.id = o.object_id
JOIN sys.indexes i ON i.object_id=si.id AND i.index_id= si.indid
JOIN sys.data_spaces s ON s.data_space_id = i.data_space_id
WHERE s.name='PRIMARY'
AND si.rowcnt > 0


name	type_desc	name	indid	rowcnt	data_space_id	name	(No column name)
sysrscols SYSTEM_TABLE clst 1 1512 1 PRIMARY sp_spaceused 'sys.sysrscols'
sysrowsets SYSTEM_TABLE clust 1 168 1 PRIMARY sp_spaceused 'sys.sysrowsets'
sysallocunits SYSTEM_TABLE clust 1 186 1 PRIMARY sp_spaceused 'sys.sysallocunits'
sysallocunits SYSTEM_TABLE nc 2 186 1 PRIMARY sp_spaceused 'sys.sysallocunits'
sysfiles1 SYSTEM_TABLE NULL 0 5 1 PRIMARY sp_spaceused 'sys.sysfiles1'
sysfgfrag SYSTEM_TABLE cl 1 4 1 PRIMARY sp_spaceused 'sys.sysfgfrag'
sysphfg SYSTEM_TABLE cl 1 2 1 PRIMARY sp_spaceused 'sys.sysphfg'
sysprufiles SYSTEM_TABLE clst 1 5 1 PRIMARY sp_spaceused 'sys.sysprufiles'
sysowners SYSTEM_TABLE clst 1 19 1 PRIMARY sp_spaceused 'sys.sysowners'
sysowners SYSTEM_TABLE nc1 2 19 1 PRIMARY sp_spaceused 'sys.sysowners'
sysowners SYSTEM_TABLE nc2 3 19 1 PRIMARY sp_spaceused 'sys.sysowners'
sysprivs SYSTEM_TABLE clust 1 163 1 PRIMARY sp_spaceused 'sys.sysprivs'
sysschobjs SYSTEM_TABLE clst 1 155 1 PRIMARY sp_spaceused 'sys.sysschobjs'
sysschobjs SYSTEM_TABLE nc1 2 155 1 PRIMARY sp_spaceused 'sys.sysschobjs'
sysschobjs SYSTEM_TABLE nc2 3 155 1 PRIMARY sp_spaceused 'sys.sysschobjs'
sysschobjs SYSTEM_TABLE nc3 4 155 1 PRIMARY sp_spaceused 'sys.sysschobjs'
syscolpars SYSTEM_TABLE clst 1 1442 1 PRIMARY sp_spaceused 'sys.syscolpars'
syscolpars SYSTEM_TABLE nc 2 1442 1 PRIMARY sp_spaceused 'sys.syscolpars'
sysnsobjs SYSTEM_TABLE clst 1 1 1 PRIMARY sp_spaceused 'sys.sysnsobjs'
sysnsobjs SYSTEM_TABLE nc 2 1 1 PRIMARY sp_spaceused 'sys.sysnsobjs'
sysxprops SYSTEM_TABLE clust 1 13 1 PRIMARY sp_spaceused 'sys.sysxprops'
sysscalartypes SYSTEM_TABLE clst 1 34 1 PRIMARY sp_spaceused 'sys.sysscalartypes'
sysscalartypes SYSTEM_TABLE nc1 2 34 1 PRIMARY sp_spaceused 'sys.sysscalartypes'
sysscalartypes SYSTEM_TABLE nc2 3 34 1 PRIMARY sp_spaceused 'sys.sysscalartypes'
sysidxstats SYSTEM_TABLE clst 1 325 1 PRIMARY sp_spaceused 'sys.sysidxstats'
sysidxstats SYSTEM_TABLE nc 2 325 1 PRIMARY sp_spaceused 'sys.sysidxstats'
sysiscols SYSTEM_TABLE clst 1 466 1 PRIMARY sp_spaceused 'sys.sysiscols'
sysiscols SYSTEM_TABLE nc1 2 466 1 PRIMARY sp_spaceused 'sys.sysiscols'
sysbinobjs SYSTEM_TABLE clst 1 23 1 PRIMARY sp_spaceused 'sys.sysbinobjs'
sysbinobjs SYSTEM_TABLE nc1 2 23 1 PRIMARY sp_spaceused 'sys.sysbinobjs'
sysobjvalues SYSTEM_TABLE clst 1 361 1 PRIMARY sp_spaceused 'sys.sysobjvalues'
sysclsobjs SYSTEM_TABLE clst 1 19 1 PRIMARY sp_spaceused 'sys.sysclsobjs'
sysclsobjs SYSTEM_TABLE nc 2 19 1 PRIMARY sp_spaceused 'sys.sysclsobjs'
sysrts SYSTEM_TABLE clst 1 1 1 PRIMARY sp_spaceused 'sys.sysrts'
sysrts SYSTEM_TABLE nc1 2 1 1 PRIMARY sp_spaceused 'sys.sysrts'
sysrts SYSTEM_TABLE nc2 3 1 1 PRIMARY sp_spaceused 'sys.sysrts'
syssingleobjrefs SYSTEM_TABLE clst 1 158 1 PRIMARY sp_spaceused 'sys.syssingleobjrefs'
syssingleobjrefs SYSTEM_TABLE nc1 2 158 1 PRIMARY sp_spaceused 'sys.syssingleobjrefs'
sysmultiobjrefs SYSTEM_TABLE clst 1 346 1 PRIMARY sp_spaceused 'sys.sysmultiobjrefs'
sysmultiobjrefs SYSTEM_TABLE nc1 2 346 1 PRIMARY sp_spaceused 'sys.sysmultiobjrefs'
sysguidrefs SYSTEM_TABLE cl 1 1 1 PRIMARY sp_spaceused 'sys.sysguidrefs'
sysguidrefs SYSTEM_TABLE nc 2 1 1 PRIMARY sp_spaceused 'sys.sysguidrefs'
sysqnames SYSTEM_TABLE clst 1 97 1 PRIMARY sp_spaceused 'sys.sysqnames'
sysqnames SYSTEM_TABLE nc1 2 97 1 PRIMARY sp_spaceused 'sys.sysqnames'
sysxmlcomponent SYSTEM_TABLE cl 1 99 1 PRIMARY sp_spaceused 'sys.sysxmlcomponent'
sysxmlcomponent SYSTEM_TABLE nc1 2 99 1 PRIMARY sp_spaceused 'sys.sysxmlcomponent'
sysxmlfacet SYSTEM_TABLE cl 1 112 1 PRIMARY sp_spaceused 'sys.sysxmlfacet'
sysxmlplacement SYSTEM_TABLE cl 1 18 1 PRIMARY sp_spaceused 'sys.sysxmlplacement'
sysxmlplacement SYSTEM_TABLE nc1 2 18 1 PRIMARY sp_spaceused 'sys.sysxmlplacement'
sysbinsubobjs SYSTEM_TABLE clst 1 3 1 PRIMARY sp_spaceused 'sys.sysbinsubobjs'
sysbinsubobjs SYSTEM_TABLE nc1 2 3 1 PRIMARY sp_spaceused 'sys.sysbinsubobjs'
syssoftobjrefs SYSTEM_TABLE clst 1 58 1 PRIMARY sp_spaceused 'sys.syssoftobjrefs'
syssoftobjrefs SYSTEM_TABLE nc1 2 58 1 PRIMARY sp_spaceused 'sys.syssoftobjrefs'
rep_service_index_exclude USER_TABLE PK__rep_serv__26E1381E02084FDA 1 6 1 PRIMARY sp_spaceused 'dbatools.rep_service_index_exclude'
sys_params USER_TABLE PK_sys_params 1 1 1 PRIMARY sp_spaceused 'dbatools.sys_params'
sequence USER_TABLE PK_sequence_1 1 1 1 PRIMARY sp_spaceused 'dbatools.sequence'

Если кто знает подскажите куда копать?
DBCC CHECKDB - все ок.

Кто-то из Гуру постил скрипт который выводит, какая инфа лежит на станицах. Датасет примерно такой (укороченный вариант).
Position object_id
1:1:0 IAM
1:1:1 ....
1:1:4000 12312332
27 янв 15, 15:45    [17177520]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
а вы решили свою проблему, нашли съеденное пространство?
я просто к чему.
вы вот тут фильтруете по si.rowcnt > 0, а может, не стОит?
gds
посмотрел, может системные таблицы ведут, какие-то логи (как в случае с sysssislog), но самая большая 1.3 Мб.
SELECT o.name,o.type_desc,i.name,si.indid,si.rowcnt,i.data_space_id,s.name,'sp_spaceused '''+SCHEMA_NAME(o.schema_id)+'.'+o.name+''''
FROM sys.sysindexes si
JOIN sys.all_objects o ON si.id = o.object_id
JOIN sys.indexes i ON i.object_id=si.id AND i.index_id= si.indid
JOIN sys.data_spaces s ON s.data_space_id = i.data_space_id
WHERE s.name='PRIMARY'
AND si.rowcnt > 0


кучи -- они коварные.
они не отдают свободное место не только при DELETE.
еще и при ROLLBACK.
обнаружилось сие случайно, при заливании 11 гигов в таблицу с последующим роллбэком.
роллбэк не высвободил ничего, имеем "пустую" кучу в 0 строк и 11 гиг.
так что 0 строк в куче -- это не показатель.

К сообщению приложен файл. Размер - 41Kb
20 фев 15, 17:34    [17294722]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
для уставших от картинок прилагаю мини-репро.
при ребилде еще и больше отжирает.
может это все и документировано, но у меня тут полный интернет-капец,
из дома поищу, ну вдруг это так и задумано
+ репро "прожорливая куча"
create table dbo.t (v char(8000));
go

exec sp_spaceused 'dbo.t', 'true';
go

--name	rows	reserved	data	index_size	unused
--t	0                   	0 KB	0 KB	0 KB	0 KB

begin tran;
insert into dbo.t default values;
insert into dbo.t default values;
insert into dbo.t default values;
insert into dbo.t default values;
rollback;
go

exec sp_spaceused 'dbo.t', 'true';
go

--name	rows	reserved	data	index_size	unused
--t	0                   	40 KB	32 KB	8 KB	0 KB

alter table dbo.t rebuild;
go

exec sp_spaceused 'dbo.t', 'true';
go

--name	rows	reserved	data	index_size	unused
--t	0                   	72 KB	8 KB	8 KB	56 KB

select object_name(p.object_id) as name, au.total_pages, au.used_pages, p.index_id, p.rows
from sys.allocation_units au join sys.partitions p 
        on container_id = p.hobt_id
where object_name(p.object_id) = 't';

--name	total_pages	used_pages	index_id	rows
--t	9	2	0	0      


drop table dbo.t;
go
20 фев 15, 17:56    [17294822]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
mag2000
Member

Откуда:
Сообщений: 189
gds,
Команда
DBCC UPDATEUSAGE 
не поможет в вашем случае?
https://technet.microsoft.com/ru-ru/library/ms188414(v=sql.90).aspx
На больших объемах может работать долго !!!
Лучше попробовать на копии базы
24 фев 15, 08:07    [17303161]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
o-o,

про heap + delete все верно. это документировано (high water mark)

+ по поводу rebuild и увеличения места. думаю в этом дело.

create table dbo.t (v char(8000));
go

exec sp_spaceused 'dbo.t', 'true';
go

--name	rows	reserved	data	index_size	unused
--t	0                   	0 KB	0 KB	0 KB	0 KB

begin tran;
insert into dbo.t default values;
insert into dbo.t default values;
insert into dbo.t default values;
insert into dbo.t default values;
rollback;
go

-- У кого 2008 добавьте DBCC IND вместо слежующего SELECT'а
-- 4 DATA_PAGE + 1 IAM_PAGE. Так как мы лили данные по одной строке
-- то страницы выделялись из MIXED экстентов
select *
from
	sys.dm_db_database_page_allocations(db_id(N'db3'), object_id('t'), 0, null, 'DETAILED');
go

exec sp_spaceused 'dbo.t', 'true';
go

--name	rows	reserved	data	index_size	unused
--t	0                   	40 KB	32 KB	8 KB	0 KB

alter table dbo.t rebuild;
go

-- У кого 2008 добавьте DBCC IND вместо слежующего SELECT'а
-- 8 DATA_PAGE + 1 IAM_PAGE. 
-- REBUILD выделил сразу НОВЫЙ экстент под таблицу. а старые страницы освободил.
-- Если добавить больше строк в начале теста, то станет ясно, что
-- место не увеличивается при REBUILD, а при сильно поюзаном HEAP,
-- даже уменьшится по понятным причинам.
select *
from
	sys.dm_db_database_page_allocations(db_id(N'db3'), object_id('t'), 0, null, 'DETAILED');
go

exec sp_spaceused 'dbo.t', 'true';
go

--name	rows	reserved	data	index_size	unused
--t	0                   	72 KB	8 KB	8 KB	56 KB

select object_name(p.object_id) as name, au.total_pages, au.used_pages, p.index_id, p.rows
from sys.allocation_units au join sys.partitions p 
        on container_id = p.hobt_id
where object_name(p.object_id) = 't';

--name	total_pages	used_pages	index_id	rows
--t	9	2	0	0      


drop table dbo.t;
go

24 фев 15, 09:51    [17303336]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+

я хотел сказать, что поскольку в тесте вставляется по одной строке, то SQL Server'ом используется логика выделения по мере необходимости первых 8 страниц для объекта из MIXED-экстентов.

но rebuild этим не заморачивается. и выделяет сразу extent'ами и тупо копирует в выделенные страницы содержимое старых (для HEAP).

потому видна маленькая разница в размере. с ростом количества данных она такой маленькой и останется (до 8 страниц, судя по-всему).
24 фев 15, 10:13    [17303406]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
churupaha,

про DELETE FROM HEAP я в курсе, вот моя цитата по этому поводу 17293414
ок про ребилд.
но почему роллбэк оставляет мне пустую кучу в 11 Гиг, например?
без всякого ребилда, лейте в кучу сколько нравится, потом роллбэк, в результате пустая таблица в 0 строк,
но 11 гигив держит

вчера снова руки не дошли поискать из дома, а тут разве что форум еще пока открывает :(
24 фев 15, 10:43    [17303553]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
o-o
но почему роллбэк оставляет мне пустую кучу в 11 Гиг, например?


сдается, с индексами будет тоже самое. надо поглядеть в transaction log, работа с GAM/SGAM/PFS/IAM идет не отдельными ли системными транзакциями?
24 фев 15, 10:47    [17303585]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
с индексами все то же самое, вот кластерная таблица в гигабайт,
заполнению этой таблицы был сделан роллбэк
(я заливаю, пока не станет гигабайт, потом говорю роллбэк, результат на картинке)
create table dbo.t (id int identity primary key clustered, col char(8000));
go

begin tran;
insert into dbo.t(col) 
select 'a'
from sys.all_columns s1 cross join sys.all_columns s2

-- wait for 1 Gb --

rollback


К сообщению приложен файл. Размер - 66Kb
25 фев 15, 16:32    [17311219]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
теперь этому кластерному ребилд:

К сообщению приложен файл. Размер - 69Kb
25 фев 15, 16:34    [17311236]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
про ребилд:
оно конечно, остается немного захваченного места в случае кучи,
но оно "больше прежнего" только в случае маленькой кучки.
во многогигабайтном случае все гигабайты при ребилде уходят,
что в случае кучи, что в случае кластерного.

у меня все руки не доходили проверить на приличном объеме, сегодня вот дошли.
последующий шринк базы вернет ей исходные размеры.
т.е. трагедия только в случае "заливали, сделали роллбэк, ребилд не делали"
---
про GAM/SGAM/PFS/IAM, а также SplitPage смотрим в логе:
(моя транзакция вообще одна, та самая мега-заливка, spid 58, Transaction ID = 0000:0000020e)

К сообщению приложен файл. Размер - 103Kb
25 фев 15, 18:04    [17311839]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
в общем, на моей же картинке в случае индекса видно,
что размер-таки ушел почти в 0.
на сервере 2008 R2 что роллбэк заполнения кучи, что кластерной таблицы,
место высвобождает, хотя и по-разному.
в случае кучи сразу, т.к. он удаляет и потом сразу DEALLOCATE всему экстенту из PFS,
в логе так и пишут.
в случае индекса он помечает GHOST records, потом все равно идет DEALLOCATE.
но с маленькой задержкой, если сразу смотреть размер кластерной таблицы, то сперва покажет все гигабайты,
а через некоторое время тоже почти 0.
---
а вот те 11 Гиг с первой картинки -- они так на месте и лежат.
это все 2014-ый Экспресс.
поменяли ли они в нем сам процесс роллбэка или что,
но в логе больше нет ни одной записи с DEALLOCATE
(в 2008-ом еще как, весь роллбэк почти из этого и состоит)

так что кучи в 0 строк гагабайтных размеров -- это подрок 2014-ого

К сообщению приложен файл. Размер - 146Kb
26 фев 15, 18:23    [17317474]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
o-o,

Что выглядит логично. Ведь механизмы, обеспечивающие изоляцию транзакций (блокировки строк, страниц, за исключением блокировки таблицы) никак не защищают структуры отвечающие за распределение свободного места (IAM/PFS/GAM/SGAM). Латч захватывается кратковременно, только на период самой модификации страницы. И две параллельные транзакции могут, например написать как в одну и туже страницу, выделение, которой было инициировано одной транзакцией, так и в разные страницы одного и того же экстента, выделение которого было инициировано одной из транзакций (а пишут обе). И тут rollback никак не сделать с откатом изменений в allocation-структурах, потому как, что делать с данными конкурирующих транзакций, они вроде и есть, а место было бы освобождено... Потому, видимо, они вмостырили изменения PFS/GAM/SGAM отдельными системными транзакциями. Единственное вопрос это только при DML. Или если сделать DROP TABLE в явной транзакции, а затем rollback, то все таки это уже будет одна транзакция?... надо будет поглядеть на все это.
26 фев 15, 19:47    [17317724]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
o-o
в случае индекса он помечает GHOST records, потом все равно идет DEALLOCATE.


забыл про GHOST_RECORDS и про cleanup thread. :)
26 фев 15, 19:53    [17317739]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
o-o
в случае кучи сразу, т.к. он удаляет и потом сразу DEALLOCATE всему экстенту из PFS,


странно. сам тоже попробую попозже.
26 фев 15, 19:58    [17317767]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
показываю разницу между 2008 R2 и 2014, отлично видно, кто делает deallocate, и кто нет.

представляю участников теста:
1.
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (Intel X86)   Aug 19 2014 12:21:07   Copyright (c) Microsoft Corporation  Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) 

2.
Microsoft SQL Server 2014 - 12.0.2480.0 (Intel X86)   Jan 28 2015 19:03:03   Copyright (c) Microsoft Corporation  Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) 

во вновь созданную таблицу кладу 10000 строк в одной транзакции,
они же 10000 страниц, т.е. примерно 80 Мб данных.
замеряю кучу, засекаю последний LSN.
делаю роллбэк,
смотрю, что пошло в лог после засеченного LSN.
замеряю получившуюся таблицу.

+ test1: 2008 R2
-- 2008 R2 --
create table dbo.t(col char(8000));
checkpoint;

begin tran
insert into dbo.t(col)
select top 10000 'a'
from sys.all_columns c1 cross join sys.all_columns c2;

exec sp_spaceused 'dbo.t', 'true';
--name	rows	reserved	data	index_size	unused
--t	10000      	80072 KB	80000 KB	8 KB	64 KB

select MAX([Current LSN])
from sys.fn_dblog(null, null)
--0000015a:0000081f:0020

rollback;

select Operation, context, COUNT(*) as cnt
from sys.fn_dblog(null, null)
where [Current LSN] > '0000015a:0000081f:0020'
group by Operation, context
order by 3 desc

--Operation	context	cnt
--LOP_SET_FREE_SPACE	LCX_PFS	10000
--LOP_DELETE_ROWS	LCX_HEAP	10000
--LOP_MODIFY_ROW	LCX_PFS	7736
--LOP_HOBT_DELTA	LCX_NULL	6876
--LOP_SET_BITS	LCX_GAM	860
--LOP_SET_BITS	LCX_IAM	860
--LOP_ABORT_XACT	LCX_NULL	1

select Operation, context, [Transaction ID], AllocUnitName, [Page ID], [Slot ID], [Offset in Row], Description
from sys.fn_dblog(null, null)
where [Current LSN] > '0000015a:0000081f:0020'
order by [Current LSN]

exec sp_spaceused 'dbo.t', 'true';
--name	rows	reserved	data	index_size	unused
--t	0          	25032 KB	24992 KB	8 KB	32 KB

куча до роллбэка:
name rows reserved data index_size unused
t 10000 80072 KB 80000 KB 8 KB 64 KB

куча после роллбэка:
name rows reserved data index_size unused
t 0 25032 KB 24992 KB 8 KB 32 KB

ниже этого размера не уйдет, но если даже вы сложите гигабайты в кучу,
после роллбэка останутся все те же неизменные 24,406 Mb.

на заливку ушло 7 секунд, на роллбэк тоже 7.

вот картинкой как он делает роллбэк:
deallocate странице в куче, отметка об этом в PFS, так 8 страниц(у меня 3 отмечено, но смотрите сами, там все 8),
отметка о deallocate экстенту в PFS

итого по операциям:
Operation context cnt
LOP_SET_FREE_SPACE LCX_PFS 10000
LOP_DELETE_ROWS LCX_HEAP 10000
LOP_MODIFY_ROW LCX_PFS 7736
LOP_HOBT_DELTA LCX_NULL 6876
LOP_SET_BITS LCX_GAM 860
LOP_SET_BITS LCX_IAM 860
LOP_ABORT_XACT LCX_NULL 1


К сообщению приложен файл. Размер - 149Kb
27 фев 15, 14:39    [17321289]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
За експеременты и потраченное время - честно жму руку. Спасибо
27 фев 15, 14:51    [17321384]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
+ test2: 2014
-- 2014 --
create table dbo.t(col char(8000));
checkpoint;

begin tran
insert into dbo.t(col)
select top 10000 'a'
from sys.all_columns c1 cross join sys.all_columns c2;

exec sp_spaceused 'dbo.t', 'true';
--name	rows	reserved	data	index_size	unused
--t	10000               	80072 KB	80000 KB	8 KB	64 KB


select MAX([Current LSN])
from sys.fn_dblog(null, null)
--00000021:0000479c:0004

rollback;

select Operation, context, COUNT(*) as cnt
from sys.fn_dblog(null, null)
where [Current LSN] > '00000021:0000479c:0004'
group by Operation, context
order by 3 desc;

--Operation	context	cnt
--LOP_SET_FREE_SPACE	LCX_PFS	10000
--LOP_DELETE_ROWS	LCX_HEAP	10000
--LOP_ABORT_XACT	LCX_NULL	1

select Operation, context, [Transaction ID], AllocUnitName, [Page ID], [Slot ID], [Offset in Row], Description
from sys.fn_dblog(null, null)
where [Current LSN] > '00000021:0000479c:0004';

exec sp_spaceused 'dbo.t', 'true';

--name	rows	reserved	data	index_size	unused
--t	0                   	80072 KB	80000 KB	8 KB	64 KB

куча до роллбэка:
name rows reserved data index_size unused
t 10000 80072 KB 80000 KB 8 KB 64 KB

куча после роллбэка:
name rows reserved data index_size unused
t 10000 80072 KB 80000 KB 8 KB 64 KB

на заливку ушло 7 секунд, на роллбэк 4.

итого по операциям:
Operation context cnt
LOP_SET_FREE_SPACE LCX_PFS 10000
LOP_DELETE_ROWS LCX_HEAP 10000
LOP_ABORT_XACT LCX_NULL 1

и если вы надеетесь, что он фоново что-то там потом подчистит, то зря.
все он откатил.
причем необязательно непустая куча.
можно сперва ее заполнить, откачено будет как и положено,
"увидите" только данные, что положили до теста.
но вот размер кучи изменится в случае 2014.
ну и теперь представьте, это не вы роллбэк сделали,
это какой-то disaster.
crash recovery вам откатит неудачную заливку, при этом в 2008 R2 вернетесь к исходному размеру, а в 2014
получите кучу, увеличенную на столько гигов, сколько успели залить.

не, ну понятно, можно, зная об этом, самому ручками перелить кучу в новое место(или ребилд выполнить, все одно),
время, сэкономленное на роллбэке, потратите на переливку, чтобы пустые гигабайты ликвидировать.
но почему нигде не написано о таком нововведении как "облегченный вариант роллбэка для куч"?

К сообщению приложен файл. Размер - 148Kb
27 фев 15, 14:52    [17321396]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
кстати, оказалось, что 2012 ведет себя как 2014,
тогда может это и написано где-то, просто и искать надо было на изменения в 2012-ом.
продолжу поиски, может и откопаю
27 фев 15, 15:48    [17321843]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
o-o
Guest
o-o
кстати, оказалось, что 2012 ведет себя как 2014,
тогда может это и написано где-то, просто и искать надо было на изменения в 2012-ом.
продолжу поиски, может и откопаю

не, не откопаю.
искать надоело, оказалось проще спросить.

sepupic says:
March 6, 2015 at 6:26 am
So did they change the implementation of rollback for heaps and is this fact documented regularly?

Paul Randal says:
March 6, 2015 at 10:10 am
I guess they did – haven’t looked at that case for a while. And it wouldn’t be documented.

полностью здесь, последний сегодняшний пост:
Ghost cleanup redux
7 мар 15, 00:33    [17355505]     Ответить | Цитировать Сообщить модератору
 Re: найди object с данными.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9163
o-o,
а что им мешает использовать тот же механизм возврата удаленных страниц, что и для truncate?
7 мар 15, 16:57    [17356490]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить