Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

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.
Avatar of Anuj
Anuj
Flag of India image

Did you checked the tempdb filled up the disk space?
Avatar of marrowyung
marrowyung

ASKER

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.
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.

ASKER CERTIFIED SOLUTION
Avatar of jogos
jogos
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

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/.  
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.
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.
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 ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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.

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.
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
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
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.
should SpaceStillUsed + AllocatedSpace = SpaceUsed?

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.
may I know if the TOP 20 statement will return the most consume SQL statement in tempdB ?
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.
Avatar of Mark Wills
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.



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.






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 :)