sql 2005 tempdb full scenario

If tempdb is full, we restart SQL. Can we get by without an outage by killing transactions/spids without restarting SQL.

Could anyone please explain better the scenario when tempdb is full

Or is it required/best to kill transactions only if tempdb has growth restrictions for data and log files?
LVL 1
sqlserverdbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Very quck first thoughts

How often does this occur?
What is the size of tempdb vs the databases?
Is it tempdb that is full or the disk is full or both?
Have all disks got more than 20% free space?
Are the disks badly fragmented?

Instead of sp_who use sp_who2 for more detail - look for user processes that are marked suspened or runnable, with large cputtimes and diskio. That will give you some clues as to what process is causing this issue.

HTH
  David
0
sqlserverdbaAuthor Commented:
Hi David, thanks for the response. I think I have to divide this into two parts -
1. My scenario is, we had a problem yesterday where we got errors of 'tempdb log file is full'. All data files, the log file have growth restrictions. There was an associated outage for the app server. But even now the log file is still at its maximum growth value - 96 GB.

2. When I asked the question, I do not have an outage right now. I was wondering if killing the transactions/sessions was meant for when the tempdb has growth restrictions and reached the maximum size. Or is it applicable to tempdb irrespective of growth restrictions, i.e. even if it has no restrictions and has almost ran the disk out of space. I wanted to know if restarting SQL Server can be avoided.
0
David ToddSenior DBACommented:
Hi,

Without knowing more about your systems, I suggest that either: 1 you are in need of more disk space; or two the application has some long-running transactions, or overuse of row versioning - and the application needs some overhaul.

Killing the processes with the long transactions will free up some tempdb space (not log space) and so allow other processes to continue.

Which log file is at its max of 96GB? templog.ldf? I would have thought that a server restart would have reset this to a more default value. To avoid the cost of growth, I'd set templog.ldf value to at least a few GB, maybe even 20 - 30GB - assuming that this growth to 96GB is unusual and extreme!

Are you taking database backups and transaction log backups?

If you are careful and can identify processes that are using tempdb, then killing these processes will avoid a server restart. But what is the cost of that vs a server restart? How many processes are going to need to be rerun and recovered?

Regards
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

jogosCommented:
With that size I hope you are using multiple files for tempdb.

Tempdb is a performancebottleneck because all users of all databases are passing there, one big consumer can downgrade performance for all other sessions. So it's good to find those big consumers and see if (small) changes as avoiding an unneded sort/distinct.., spliting long transactions in different smaller can reduce this tempdb-consumption.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT top 20 CAST(SUM(su.user_objects_alloc_page_count
	+ su.internal_objects_alloc_page_count) * (8.0/1024.0) AS DECIMAL(20,3)) AS [SpaceUsed(MB)]
	, CAST(SUM(su.user_objects_alloc_page_count)
		* (8.0/1024.0) AS DECIMAL(20,3)) AS [AllocatedSpace(MB)]
	, CAST(SUM(su.user_objects_alloc_page_count
		- su.user_objects_dealloc_page_count
		+ su.internal_objects_alloc_page_count
		- su.internal_objects_dealloc_page_count)
		* (8.0/1024.0) AS DECIMAL(20,3)) AS [SpaceStillUsed(MB)]
	, su.session_id
	, ec.connection_id
	, es.login_name
	, es.host_name
	, st.text AS [LastQuery Of this session]
	, ec.last_read
	, ec.last_write
	, es.program_name
FROM sys.dm_db_session_space_usage su
INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
	ON su.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE su.session_id > 50
GROUP BY su.session_id, ec.connection_id, es.login_name, es.host_name
		, st.text, ec.last_read, ec.last_write, es.program_name
ORDER BY [AllocatedSpace(MB)] DESC

Open in new window

0
Scott PletcherSenior DBACommented:
>> If tempdb is full, we restart SQL. Can we get by without an outage by killing transactions/spids without restarting SQL.<<

Yes, assuming you have some diskspace available.

Add a file(s) to the tempdb as needed to provide SQL space to use.  SQL should be able to resume once more space is added.


>> Could anyone please explain better the scenario when tempdb is full <<

Tempdb is used by a lot of internal SQL Server processes.  When any db log gets full, SQL can't use the db, so tempdb's log being full basically stops all functions of SQL.


>> Or is it required/best to kill transactions only if tempdb has growth restrictions for data and log files? <<

Yes.  And remove the restrictions.  You cannot continually fight tempdb over space, because it will severely limit SQL's overall performance.

Get enough disk space and preallocate as much tempdb space as you need.

After that, you can work on tuning up processing and begin to reduce tempdb requirements.
0
sqlserverdbaAuthor Commented:
Hi All
That is right, I think it needs more space. And it does have many long running transition, and yes the log file is still at 96 GB. All the (user) databases are being backed up.

"Killing the processes with the long transactions will free up some tempdb space (not log space) and so allow other processes to continue."
If so, is a log file shrink the right thing to do? and would it be better to do a shrink after business hours?

"I would have thought that a server restart would have reset this to a more default value" - There was no restart of prod server. I assume there was some problems from the failing transactions
when tempdb logfile got full, reached max limit, and they ended up restarting the app server.

To answer both questions below -
"If you are careful and can identify processes that are using tempdb, then killing these processes will avoid a server restart. But what is the cost of that vs a server restart? How many processes are going to need to be rerun and recovered?"
"So it's good to find those big consumers and see if (small) changes as avoiding an unneded sort/distinct.., spliting long transactions in different smaller can reduce this tempdb-consumption."
We have many databases, replications including snapshot, and integration packages.

"Tempdb is used by a lot of internal SQL Server processes.  When any db log gets full, SQL can't use the db, so tempdb's log being full basically stops all functions of SQL."
Does it mean that when there are restrictions on all data, log files sizes and the log file gets full, stops all functions of SQL? When this issue occurred couple of days ago,
I don't think there was a services restart on the prod database server.
0
Scott PletcherSenior DBACommented:
>> and the log file gets full, stops all functions of SQL? <<

Yes.  On any (not read-only) db, a full log file will stop activity on the db, since SQL must write to the log file before it can write to the data file(s).


Ouch, 96G, didn't see that part.

Definitely needs shrunk.  You can try to do that live, but in tempdb that can cause all sorts of issues.

Shrinking in stages would give you the best chance for it work:

USE tempdb
DBCC SHRINKFILE ( templog , 92000 )
GO
DBCC SHRINKFILE ( templog , 88000 )
...
0
David ToddSenior DBACommented:
Hi,

Carefully shrinking in stages files that have excess free space, and allowing other database files that are too tight room to grow is a good step.

Be aware that doing this is going to fragment the tables inside each file (at the SQL Level) and the files themselves (at the filesystem level) but I don't see that you have much choice.

Unless you have a huge maintenance window - read office closed from midday friday through to ... - and you can work through that time, the initial little bits/first bite will get you the most gain ...

HTH
  David
0
jogosCommented:
"Ouch, 96G, didn't see that part."
That's why I stressed on fighting the big consumers and the script to identify the best to kill  is usable for both.

Shrinking in stages and fragmentation, for tempdb that is less an issue as it would be for a regular database.

The maintenanci window next weekend is quit large I think :)
0
Scott PletcherSenior DBACommented:
Shrinking the log file has no issues with fragmentation, of course; frag is only a concern with data files.
0
David ToddSenior DBACommented:
Hi Scott

Although not a SQL concern as such, too much fragmentation of the datafiles will slow down the OS and filesystem ... to me that would include the log files.

Regards
  David
0
Scott PletcherSenior DBACommented:
Disk file fragementation, true.  But if you have way too many log fragments, you have your log autogrowth set way too low.
0
David ToddSenior DBACommented:
Hi,

See this blog post by Kimberly Tripp for detail on what Scott referred to above.
http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

Regards
  David
0
sqlserverdbaAuthor Commented:
Actually, I let the log file remain, due to the amount of transactions, replications, that take place frequently, not wanting to interfere and shrink the log, while monitoring it. Was close to the reboot schedule too.
Thank you for the suggestions and clarifications. Please let me know if I should rather have shrunk the log file
0
Scott PletcherSenior DBACommented:
No, you should avoid a live shrink of tempdb log file it at all possible.

The next time SQL recycles, immediately after SQL comes back up, you can safely shrink the tempdb data and/or log files.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.