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

Откуда:
Сообщений: 1574
Резко увеличивается размер tempdb, 200-300Гб, при перезагрузки сервера, размер становится 100Мб. Происходит переполнение диска. Из-за чего происходит увеличение.
Как исправить чтоб не было такого скачка?

Вот лог:
Date  24.01.2013 21:17:28
Log  SQL Server (Current - 25.01.2013 0:31:00)
Source  spid93
Message
Insufficient space in tempdb to hold row versions.  Need to shrink the version store to free up some space in tempdb. Transaction (id=6462692 xsn=310521 spid=99 elapsed_time=986) has been marked as victim and it will be rolled back if it accesses the version store. If the problem persists, the likely cause is improperly sized tempdb or long running transactions. Please refer to BOL on how to configure tempdb for versioning.

Date  24.01.2013 21:17:29
Log  SQL Server (Current - 25.01.2013 0:31:00)
Source  spid93
Message
Could not allocate space for object 'dbo.SORT temporary run storage:  140739757670400' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
25 янв 13, 07:41    [13825089]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Ну, дык, в мессагах же всё написано.
25 янв 13, 07:52    [13825103]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
dimakz
Member

Откуда:
Сообщений: 1574
а что именно?
25 янв 13, 08:00    [13825118]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
dimakz
Member

Откуда:
Сообщений: 1574
подскажите
25 янв 13, 10:24    [13825871]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
dimakz,

автор
Как исправить чтоб не было такого скачка?

переписать код более оптимально с точки зрения использования tempdb
25 янв 13, 10:59    [13826068]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
dimakz
Member

Откуда:
Сообщений: 1574
а как вычеслить какой запрос использует темпдб
25 янв 13, 11:00    [13826073]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
dimakz,

Profiler пользуйте...
Вообще для начала посмотрите сколько реально занято в темпдб места сколько не используется... типа

use tempdb;
exec sp_spaceused;

Может просто можно шринкануть файлы, хотя... если у вас забивается темпдб при работе... То именно что забивает его - трассой можно определить. Далее посмотрите используется ли у вас SNAPSHOT ISOLATION LEVEL или READ_COMMITTED_SNAPSHOT, если начали использовать эти уровни изоляций и пользователей к SQL большое количество то tempdb у вас будет расти по определению...
25 янв 13, 11:32    [13826306]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
.
Guest
Алексей Куренков
используется ли у вас SNAPSHOT ISOLATION LEVEL или READ_COMMITTED_SNAPSHOT


так про это и сообщается, собственно:
Insufficient space in tempdb to hold row versions
25 янв 13, 11:41    [13826398]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
dimakz
Member

Откуда:
Сообщений: 1574
database_name	database_size	unallocated space
tempdb	191900.00 MB	189490.63 MB


reserved	data	index_size	unused
9600 KB	8664 KB	784 KB	152 KB
25 янв 13, 11:45    [13826448]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
dimakz,

при версионности у Вас и будет рост темпдб... увеличивайте размер дискового пространства для этой базы или уходите от версионности... тут другого не придумать.
25 янв 13, 11:56    [13826562]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
.
Guest
вот еще раз. снэпшот юзаете,
какая-то долгоиграющая транзакция не отпускает данные снимка на момент своего начала.
или не открывать длиннющие транзакции, или место предоставить больше,
а то и версии строк часами желаете хранить, и место жмотите.

Determining the Amount Space Used by the Version Store

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;


Determining the Longest Running Transaction

If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;



взято отсюда:
25 янв 13, 11:59    [13826590]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
dimakz
Member

Откуда:
Сообщений: 1574
а что такое версионности ?
и как ее убрать?

да еще вопрос, опять вырос темпдб как его очистить, не перегружая сам сервер??
25 янв 13, 12:30    [13826812]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
.
Guest
вот тут читайте:
http://msdn.microsoft.com/en-us/library/ms189050%28v=sql.105%29.aspx

чтоб убрать, хватит ALTER permission на базу.
разыщите того, кто включил, у него ж были на то основания и права
(щас отключите, прибежит ведь и спасибо скажет).
и покажите результат последнего запроса из поста выше.
25 янв 13, 12:47    [13826933]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
dimakz
Member

Откуда:
Сообщений: 1574
version store pages used	version store space in MB
736	5.750000

transaction_id
3566884


ALTER permission на базу как сделать?
а сделать так? ALTER permission tempdb или ALTER permission cars1 (этих баз 5)
или ALTER permission sere1 (я когда подключаюсь через менеджмент студио вбиваю имя сервера sere1)
25 янв 13, 12:58    [13827019]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
dimakz
Member

Откуда:
Сообщений: 1574
как очистить темпбд?
25 янв 13, 13:00    [13827038]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
komrad
Member

Откуда:
Сообщений: 5252
dimakz
а что такое версионности ?
и как ее убрать?

да еще вопрос, опять вырос темпдб как его очистить, не перегружая сам сервер??


попробовать следующее:
dbcc shrinkfile(1,Нужный_размер_в_мегабайтах)

-- 10GB
dbcc shrinkfile(1,10000)



но 100% гарантии обрезки нет


выполни

use tempdb 
go
SELECT db_name() [DB],file_id,name,type_desc, physical_name,size/128. [Size (MB)],
		CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128. [Used Space (MB)],
	 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128. AS [Available Space (MB)]
	FROM sys.database_files
go
25 янв 13, 13:01    [13827046]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
komrad
Member

Откуда:
Сообщений: 5252
komrad
попробовать следующее:
dbcc shrinkfile(1,Нужный_размер_в_мегабайтах)

-- 10GB
dbcc shrinkfile(1,10000)



выполнять в tempdb естественно
25 янв 13, 13:02    [13827057]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
dimakz
Member

Откуда:
Сообщений: 1574
use tempdb 

dbcc shrinkfile(1,10000)

 DBCC SHRINKFILE: Page 1:34082384 could not be moved because it is a work table page.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
2	1	34082392	12800	1648	1600


а тот запрос это выдает

DB	file_id	name	type_desc	physical_name	Size (MB)	Used Space (MB)	Available Space (MB)
tempdb	1	tempdev	ROWS	F:\MSSQL\Data\tempdb.mdf	266268.687500	17.062500	266251.625000
tempdb	2	templog	LOG	F:\MSSQL\Data\templog.ldf	3500.000000	20.796875	3479.203125
25 янв 13, 13:13    [13827170]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
komrad
Member

Откуда:
Сообщений: 5252
dimakz
use tempdb 

dbcc shrinkfile(1,10000)

 DBCC SHRINKFILE: Page 1:34082384 could not be moved because it is a work table page.


это я и имел ввиду - активные сессии имеют объекты в конце файла данных tempdb, что мешает обрезке файла
в идеале нужно отстрелить сессии, использующие tempdb явно или неявно, и обрезать файл
однако это может быть нереализуемо и перезагрузка сиквела тогда единственное решение

dimakz
DB	file_id	name	type_desc	physical_name	Size (MB)	Used Space (MB)	Available Space (MB)
tempdb	1	tempdev	ROWS	F:\MSSQL\Data\tempdb.mdf	266268.687500	17.062500	266251.625000
tempdb	2	templog	LOG	F:\MSSQL\Data\templog.ldf	3500.000000	20.796875	3479.203125

это уже после попытки обрезания файла?
она у вас растет дальше что ли?
была же 191ГБ
25 янв 13, 13:21    [13827259]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
dimakz
Member

Откуда:
Сообщений: 1574
рост остановился, заметил что происходит рост при загрузки данных в базу..
теперь вопросы:
1) как сделать чтоб темпдб так не рос?
2) очистить хочу его ))
25 янв 13, 13:35    [13827435]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
komrad
Member

Откуда:
Сообщений: 5252
dimakz
рост остановился, заметил что происходит рост при загрузки данных в базу..
теперь вопросы:
1) как сделать чтоб темпдб так не рос?
2) очистить хочу его ))


про очистить уже было написано
про "не рос" :
самое простое - ограничить макс размер файла, но это не в вашем случае, т.к. встанут процессы заливки данных по переполнению tempdb. Следовательно, нужно выяснять какие именно процессы вызывают рост tempdb и оптимизировать их.

про размер версий уже писали
можно посмотреть, например, кто расширял tempdb (если включен default trace):

SELECT	top 100
	te.name, 
	t.SPID,
	t.LoginName,
	t.Hostname,
	t.ClientProcessID,
	t.DatabaseName, 
	t.FileName, 
	t.StartTime, 
	dateadd(ms,t.Duration/1000,t.StartTime) [FinishTime],
	t.ApplicationName ,
	t.ObjectID,
	t.Duration/1000/1000 [Duration (s)],
	t.TextData
FROM sys.traces st CROSS Apply
	::fn_trace_gettable(st.path, st.max_files) AS t 
INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id 
WHERE 
	te.name LIKE '%Auto Grow%'  
	and st.is_default=1 
	and st.status=1
ORDER BY StartTime desc
25 янв 13, 13:53    [13827632]     Ответить | Цитировать Сообщить модератору
 Re: Резко увеличивается размер tempdb  [new]
Гость333
Member

Откуда:
Сообщений: 3683
dimakz
а что такое версионности ?
и как ее убрать?

Гм. Если не знаете, то не отключайте. Лучше предварительно всё выясните, кто её включил и для каких целей. Иначе "прибежать и спасибо сказать" может не только этот человек, но и все пользователи, работающие с базой.

dimakz
заметил что происходит рост при загрузки данных в базу..

Во! При загрузке большого количества данных может происходить сортировка индексов в tempdb. На что и намекает упоминание "dbo.SORT temporary run storage".

Что за загрузка? Она идёт в пустые таблицы, или в уже заполненные? Какой размер таблиц? Сколько там индексов? По сколько данных вставляется за раз?
25 янв 13, 14:14    [13827894]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить