SQL error message

Dear all,

I find the following information from SQL log:

Error 802: [Microsoft] [ODBC SQL Server Driver] [SQL Server] There is insufficient memory available in the buffer pool.

[Microsoft][ODBC SQL Server Driver] [SQL Server] Check terminated. A failure was detected while collecting facts. Possibly tempdb out of spce or a system table is inconsistent.

Do you all experiencing this and how you all solve it without restart/stopping the SQL server?

DBA100.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
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.

AnujSQL Server DBACommented:
Did you checked the tempdb filled up the disk space?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I will, I a thinking about that also. The other server havin this probme too few weeks ago and the tempdB will release some usage up 2 days later.

Except restart the SQL server, we knew that. But what else we can do? it seems that it can't even run dbcc dbcheck.

We need to control it, not the TempDB control us.
DBA100.
0
AnujSQL Server DBACommented:
Yes, Running DBCC Commands will fail if your tempdb run out of space this is because DBCC commands uses tempdb heavily.

The options are try to move tempdb to a location where you have more space, this should not share disk with other database files.
Or add files to temp so that it resides on location where plenty of disk space available.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jogosCommented:
With this query you can see what's troubling your tempdb now.
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


Also look for queries that have a high amount of reads or writes they stress the buffer pool. And the cause can be lots of reasons, bad indexes/statistics, to many columns, lacking where-filtering, sorting, bad joins, temp tables or table variables...
And now is the time many year-reporting is done or preparations for next year so this could be a temporary problem. All heavy untunned SQL comes alive.
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
marrowyungSenior Technical architecture (Data)Author Commented:
anujnb:

Thso one is good:
"Or add files to temp so that it resides on location where plenty of disk space available." but this also involving restart SQL server to make system recogonize it, right? this is what I don't want and right now the number of tempdB file is optimized, BASCIALLY can't change ti. or we break the standard.

0
jogosCommented:
Like any performance warning it's always good to start by checking the basics: backup/trn-backups still running, fragmentation, update statistics, ....

But wait with trowing in yourself heavy maintenance stuff until it's not harming to many sessions. Also don't do it blind. Try first to find out what can cause problems, and DMV's are a good point to start http://www.mssqltips.com/sqlservertip/1975/understanding-dynamic-management-views-and-functions-in-sql-server-2008/.  
0
marrowyungSenior Technical architecture (Data)Author Commented:
jogos:

This link:http://www.mssqltips.com/sqlservertip/1975/understanding-dynamic-management-views-and-functions-in-sql-server-2008/. , seems doesn't say anything about TEMPDB.

The script you gave me will list out the task/job/section ID of the query, right? then I have to type kill <id> to kill it?

But sometime we don't want to kill it as there must be something ongoing and we prefer to wait? what else you can do during this situtation ?

Enlarge the volume that holds the tempdb can be a long term solution I strongly believe.
0
jogosCommented:
Link is saying how you can see which DMV's are availlable: I/O, CPU, memory, tempdb .... it all boils down to find the query or setting that is causing trouble, ex missing index causes high IO and maybe more use of all resourses

For killing that's right. But purpose was more to identify the high consumers so you can more profound decide what your priority is. And if you want to kill a job you better kill the right one. But seeing a high consumer also will give you oportunity to change it to a smaller footprint.
0
marrowyungSenior Technical architecture (Data)Author Commented:
jogos:

"And if you want to kill a job you better kill the right one.", we will rise up our hand first and ask what it is.

"But seeing a high consumer also will give you oportunity to change it to a smaller footprint. " what is that mean ?
0
jogosCommented:
Performance tuning is started by or the users that complain or you that finds processes that are high consumers of CPU/IO/Memeory or locks and tune it so they consume less of the resources. So it  leaves more place for other sessions to do their thing as fast as possibe.

When you start, take every week (or when you have time) the top 3 consumers and try to fix their problem. The good side is the system gradualy becomes more efficient. The downside is  if you do your job good nobody will notice so make reports about it to get credit.
0
marrowyungSenior Technical architecture (Data)Author Commented:
jogos:

For the query above, it is for all query in the memory pool, the tempbDB, right?

Anyway to know the execution plan on the query shown in the "LastQuery Of this session" colume ?

If we need to drop the query that use most of the tempdB resource, we should do kill "session_id" ? not kill <id>, right? I think I type something wrong.

What is high consumers ? expensive query you mean?


DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
jogos:

However, is the script list the real time query that existing in TempDB or is it cached ?

Any way to see real time information ?

DBA100.
0
jogosCommented:
It's  space used until the last completed task, What's included see http://msdn.microsoft.com/en-us/library/ms187938.aspx.  Don't think you get more recent than this.

To see query plan add
CROSS APPLY sys.dm_exec_query_plan(ec.most_recent_sql_handle) qp

Open in new window


To kill a session you use the session_id, but kill is a thing you avoid as long as possible.

High consumers is indeed expensive query.

0
marrowyungSenior Technical architecture (Data)Author Commented:
jogos:

After I add that cross apply, I see no result return any more:

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
      --, qp.query_plan
FROM sys.dm_db_session_space_usage su --, sys.dm_exec_query_stats qs
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
--CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_query_plan(ec.most_recent_sql_handle) qp
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE su.session_id > 50 and su.database_id=2
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


is that what you mean ?

I add "and su.database_id=2" as I am only focus on TEmpdB only , is it making sense of this script change in this way ?

Right now I need to understand all field of your script, checking the link is a bit confuse.

The SpaceUsed(MB) should be the disk space of TempDB that query is using?
AllocatedSpaced(MB) should be the same as SpaceUsed ? when will they different ?
SpaceStilUsed(MB) seems to be the most accurate used tempdB space of that query, right?
we can ignore connection_id?
Login_name is the login name the application used to connect to the database?
hostname is the machine from which the query send from ?
program_name is the application that use the login_name to send SQL query to the SQL server?
what is the last_read and last _write use for ?

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
will this a script that return the real time script that using tempdB space?

SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated
      , t3.sql_handle, t3.statement_start_offset
      , t3.statement_end_offset, t3.plan_handle
from sys.dm_db_session_space_usage as t1,
            sys.dm_exec_requests t3,
(select session_id,
   sum(internal_objects_alloc_page_count) as task_alloc,
   sum (internal_objects_dealloc_page_count) as task_dealloc
      from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
and t1.database_id = 2  
and t1.session_id = t3.session_id
order by allocated DESC
0
jogosCommented:
Sessions that use tempdb are not registered as running in tempdb.   the db_id is that of the  one of the connection of that session (connect string or USING XXXX).
-> you don't see results because you normaly sessions are not on tempdb

plan: it's indeed the plan handle and an outer apply  

Maybe it's better explained at "B. Obtaining the space consumed by internal objects in the current session for both running and completed tasks" on this link
http://msdn.microsoft.com/en-us/library/ms176029.aspx

The SpaceUsed(MB) should be the disk space of TempDB that query is using?
      What is allocated
AllocatedSpaced(MB) should be the same as SpaceUsed ? when will they different ?
     internal objects (used by cursors ...)
SpaceStilUsed(MB) seems to be the most accurate used tempdB space of that query, right?
     when as high as allocated then no space is freed yet (ex create #tab , use it in next 5 statements and don't drop it -> stays allocated until end of procedure)  

Login_name is the login name the application used to connect to the database? ok
hostname is the machine from which the query send from ? ok
program_name is the application that use the login_name to send SQL query to the SQL server? ok
what is the last_read and last _write use for ?
    timestamps that can tell you if your session is still reading or writing , if not could just be blocked by another session
0
marrowyungSenior Technical architecture (Data)Author Commented:
jogos:

What I can see is last_read and last _write is the same, right ? what is your experience on these 2 x value, it seems always the same, anyway to know the start running time of the query ?

Right now it seems a bit different from what I expected, I need to check what is holding in tempdB that make that tempdB big, and this script seems not telling me which process is using the tempdB as you said "-> you don't see results because you normaly sessions are not on tempdb"

Am I right ?

Your script don't use sys.dm_db_task_space_usage  and sys.dm_db_file_space_usage. Will it be better and how can I include this to show information on object use space in TempDB.

By the way, if I kill a query using session_id, will the tempdB smaller?

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
should SpaceStillUsed + AllocatedSpace = SpaceUsed?

0
marrowyungSenior Technical architecture (Data)Author Commented:
IT seems that all views listed in here: http://msdn.microsoft.com/en-us/library/ms176029.aspx only apply for tempDB.  

But result return by sys.dm_db_session_space_usage and sys.dm_db_task_space_usage is the same. Do you know why ?

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
may I know if the TOP 20 statement will return the most consume SQL statement in tempdB ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
jogos:

Right now, we ran your query and it seems that it stil record down the query that consume space on tempdB but last_write and last_read are 2 days ago.

after we get the session_id and use sys.dm_db_session_space_usage to find out the related information, we would like to know if the information from "sys.dm_db_session_space_usage" is the space still using?

We need more tools for this.

Thanks.
0
Mark WillsTopic AdvisorCommented:
I know this has been answered, but said I would offer advice anyway...

Error 802 is a bit of a nuisance...  I think there are a few more questions you need to ask yourself / find out about what is happening on the machine...

- Are you sure it is a tempdb space allocation error ?  Not so concerned if it is holding space as much as it still has room to grow (maybe hit by "max" size), even to the point of allocating space to tempdb (ie reserving empty space on disk) so that it doesnt have to try to auto-grow all the time - especially if doing reindexing or other maint tasks at the time.
- Have you set max memory in SQL Server ?
- Do you have lock pages for the SQL Server service account ?
- Do you have the latest SP's (e.g. CU2 for SP1 included updates for "Lock Pages in Memory")
- When does this happen - are there any big DDL tasks running / maintenance jobs (the error does say check terminated)
- There probably should be more information in the error log, did you check for more information (the statement that crashed and burned is sometimes listed) for example, in 2008 before tempdb runs out of space, sql will try to force version stores to shrink and the hapless victim will generate a 3967 error...
- What else is (concurrently) running on that Server and allow some memory for those processes as well as OS (a few gig) before looking to set max memory - it could be that there is a lot of contention on that machine and so the (manually) calculated max memory might seem to be very ordinary.

There is a good word doco to download from Microsoft which does cover some of these types of questions and an approach to trouble shooting in 2008 : http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx  (it will try to start downloading).

If you are having challenges with tempdb then there are also a couple of links worth wading through... First is http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx and the reson for that being the first is so you head the warnings raised by Paul.

Second is : http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

So, try to answer those initial questions, then read a bit more - that should give you those additional tools you are seeking.



0
marrowyungSenior Technical architecture (Data)Author Commented:
mark_wills:

Basically I found that tempDB file can be shrink in anytime and no reboot requir.

But as long as we don't shrink it less than its intial size, then it will be ok.

After upgradeing to SP2 we keep experiencing this problem. Can't see why !

- Have you set max memory in SQL Server ? seems not related right? Yes, we did.

- Do you have lock pages for the SQL Server service account ? lock memory page you mean so that this account can use more than 4GB of RAM ? it seems we don't need to.






0
Mark WillsTopic AdvisorCommented:
Was assuming you had SQL 2008 and 64bit - thought I had read that elsewhere - the lock pages in memory is a Windows attribute for the account, and no, it is not for the (older 32 bit variants / AWE) to use more than 4GB.

Yes, you can shrink tempdb anytime, but, that also means you havent read those other links yet :)
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

From novice to tech pro — start learning today.