Добро пожаловать в форум, 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 Ответить