October 2, 2014 at 1:42 pm
tempdb log has grown crazy large. It is 75GB and is running in simple recovery mode.
Does anyone know why the log would keep growing when in simple recovery mode?
I'm in need of suggestions, below are some details:
DBCC OPENTRAN('tempdb'):
Transaction information for database 'tempdb'.
Oldest active transaction:
SPID (server process ID): 149
UID (user ID) : -1
Name : user_transaction
LSN : (3978:123016:460)
Start time : Sep 29 2014 11:37:23:380AM
SID : 0xa4852ddf9f910442b573e7707e29e515
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbcc sqlperf (logspace):
tempdb71768.1286.27820
select name, log_reuse_wait, LOG_reuse_wait_desc, recovery_model_desc, database_id from sys.databases:
tempdb4ACTIVE_TRANSACTIONSIMPLE2
sp_who:
1490sleeping dccs_user STM5AP202 0 DCCS_Berea_Test AWAITING COMMAND0
select
-- *
spid, waittime, login_time, last_batch, status, hostname, cmd, PROGRAM_NAME, nt_username
from sys.sysprocesses
where hostname like 'stm5ap%' and
--status = 'suspended'
spid = '149':
1490 2014-09-18 09:23:35.5372014-10-02 13:39:13.727sleeping STM5AP202 AWAITING COMMAND DccsIE.exe
October 2, 2014 at 2:31 pm
Oldest active transaction:
SPID (server process ID): 149
UID (user ID) : -1
Name : user_transaction
LSN : (3978:123016:460)
Start time : Sep 29 2014 11:37:23:380AM
To be sure of the significance of that, we need to know the time you ran the DBCC OPENTRAN statement. For example, if you ran it Sep 29 @ 11:38AM, nothing to see here. If you ran it on Sep 30 @ 11:38AM, that's a whole different issue.
Btw, are you using snapshot isolation?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 4, 2014 at 8:18 am
Everything I pasted was all ran one after another on the day I posted this which I believe was 10/4.
Yes it looks like two db's have it enabled:
SELECT name, is_read_committed_snapshot_on FROM sys.databases
master0
tempdb0
model0
msdb0
DCCS_Berea_Prod1
DCCS_Berea_Test1
ADSI_Shipping0
ADSI_ConnectShip0
ADSI_FedEx0
ADSI_ShippingTEST0
ADSI_FedExTEST0
ADSI_ConnectShipTEST0
ReportServer0
ReportServerTempDB0
October 7, 2014 at 2:58 pm
This weekend the tempdb log file reduced after a reboot and settled around 15GB but is now up to 41GB and my users are complaining of slowness. We have been working on the production db but I'm starting to think our issue is in the temp db.
Any suggestions?
Grown twice already today up to 50GB.
October 8, 2014 at 11:43 am
ok found the offending EXE causing this issue but I have some questions on the tempdb. This will have to be looked at why this is happening by the vendor not me but...
1. I have read some users locking growth on the tempdb log file. Like setting it to say do not autogrow past 50GB. Is this recommended??? My concern is that this thing will and it will of course eat all the space up on the server.
2. What recommendations are to be made to make tempdb run as fast as it can?
October 8, 2014 at 12:58 pm
If you have to limit TempDB due to space constraints, you gotta do what you gotta do. This will result in failures on the app side if it cannot use the space that is already allocated and the DB or log cannot grow. You just have to be prepared to deal with that.
What I see as a big problem, making TempDB grow huge in my environment is poor programming. Big sorts and huge temp tables are the main sources of this sort of thing. In many cases it worked fine in Dev, where they don't have much data, but nobody though about the data volume in Prod, or that Prod would ever "get that big". This goes for third-party apps too, as their developers are no better than anyone else. Many times this is because they do not have a data architect that knows their stuff reviewing the code before it gets released.
To make TempDB speediest, put it on its own volume/hardware that is only used for TempDB, even going as far as DB one one volume and log on another.
Multiple TempDB data files can help too, if they exist on separate volumes, bbut all of these architectures need to be studied to determine if they are helping the performance, as throwing hardware at a programming problem does not fix the problem as fast as it empties the wallet.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 8, 2014 at 1:10 pm
lawson2305 (10/8/2014)
ok found the offending EXE causing this issue but I have some questions on the tempdb. This will have to be looked at why this is happening by the vendor not me but...1. I have read some users locking growth on the tempdb log file. Like setting it to say do not autogrow past 50GB. Is this recommended??? My concern is that this thing will and it will of course eat all the space up on the server.
2. What recommendations are to be made to make tempdb run as fast as it can?
1) There's no specific size that's an issue. Tempdb log space should be pre-allocated to the max size you'd ever expect to need; if that's 150GB, so be it. Ideally, you never want the tempdb log file to autogrow, because log growth pauses the db as the space is pre-formatted. Use multiple data files, but not too many, and set them all to the exact same size; again, allocate enough that you never expect it to need to grow. If IFI is allowed, the growth itself won't cause a delay.
2) SSD, if you can afford it :-). More generally, spread data across multiple drives (more accurately, spindles). The log should go to a drive that is RAID1, RAID10, or even RAID0, but never RAID5 (the write penalty is huge on RAID5 drives).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 9, 2014 at 7:10 am
I agree 100% with the statement of "poor programming". This software is the worst. I kill one issue and another just pops up. It is like they only know how to write code that keeps open transactions!
On another not the hardware is all in a VM that is on a RAID10 volume.
So let me ask you if I say move the db files to another volume (created on same raid 10) set the log files to be the max size and eliminate auto growth you state the apps will fail when the space runs out. What is the difference between running a DB out of space compared to running the volume out of space?
Scott I wanted to add what does multiple data files provide? Is there a read on this somewhere?
October 9, 2014 at 7:54 am
-- query allocated temp tables with record count and size.
use tempdb;
select o.type_desc
, substring(o.name,1,charindex('__',o.name)+1)+right(o.name,12) table_shortname
, si.name index_name
, case si.index_id when 0 then 'HEAP' when 1 then 'CLUSTERED' else 'NONCLUSTERED' end index_type
, row_count
, ((reserved_page_count * 8024) / 1024 / 1024)reserved_mb
, create_date
from sys.dm_db_partition_stats ps
left join sys.objects o on ps.object_id = o.object_id
left join sys.indexes si on si.object_id = o.object_id and si.index_id = ps.index_id
where is_ms_shipped = 0
order by reserved_page_count desc;
-- query tempdb allocation by session:
select *, (allocated_mb - deallocated_mb)reserved_mb from
(
select session_id
,(((sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)*8024)/1024)/1024) as allocated_mb
,(((sum(internal_objects_dealloc_page_count + user_objects_dealloc_page_count)*8024)/1024)/1024) as deallocated_mb
from sys.dm_db_task_space_usage
group by session_id
) x where allocated_mb > 0;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 9, 2014 at 9:43 am
lawson2305 (10/9/2014)
Scott I wanted to add what does multiple data files provide? Is there a read on this somewhere?
Yes, plenty of people have investigated and written on this. In (very) general terms, you want one data file per physical cpu/socket, up to 4. It helps with concurrency in tempdb.
[You do not want or need more than one log file, however. The log file is serial, so only one log is in use at any time anyway.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 9, 2014 at 10:36 am
I would like to ask if there is an issue with a open active transaction that is active but the log_reuse_wait_desc is Nothing (0) which means Currently there are one or more reusable virtual log files.
The big issue active transactions have all been set to Active_Transaction.
What is the difference in affecting performance when I run DBCC OpenTran(tempdb) and get the above differences??
October 9, 2014 at 11:06 am
The first thing I'm going to suggest is installing sp_WhoIsActive by Adam Machanic as it will show you the command and the tempdb usage by each command.
Second, Scott asked about Snapshot Isolation and I noticed that the transaction you posted in DBCC OPENTRAN was from a session using one of the databases with SNAPSHOT ISOLATION enabled. So I'd be thinking that there are some poorly written queries that are returning more rows than they really need which is causing more and more data to be written to the version store in tempdb, and that's being logged.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply