The transaction log for database 'tempdb' is full

[Former Member]

Hello,

Can you help me to understand and solve one problem related with tempdb in SQL Server 2008!?

Our SAP system sometimes shows some short-dumps: START_CALL_SICK (Database inconsistency: Start Transaction SICK). In SM21 I see the following error:

Database error 9002 at OPC

> The transaction log for database 'tempdb' is full. To fin

> out why space in the log cannot be reused, see the

> log_reuse_wait_desc column in sys.databases

Database error 9002

When I try to open the properties of the database through SQL Management Studio I see the following error:

I checked the the tempdb disk (5Gb) and in fact it´s full, but I can´t change the disk size...:

Can you tell me why this templog.ldf increased a lot? It´s possible to change the size of this templog.ldf file? How can I disable the option of autogrowth of tempdb? It´s possible?

Kind regards,

samid raif

[Former Member]

Hi,

5GB partition for your tempdb and log file is too small.

Just move your tempdb to some other drive where you have space OR extend the drive to sufficient size.

Regards,

Nick Loy

[Former Member]

Hi Raif

check this Microsoft KB http://technet.microsoft.com/en-us/library/ms175495.aspx

Hope this helps

-giri

[Sriram2009]

Hi Samid

Kindly refer the SAP KBA 1951819 - How to check which process makes the transaction log get full in SQL Server tempdb.

Regards

Sriram

[Former Member]

Hello Sriram,

Hum... I read the note that you recommended but what I have to do to solve this problem? Can you help me please? What is your recomendations?! (Because I can´t enlarge the tempdb disk where is created the tempdb.mdf / templog files.ldf)

Kind regards,

samid raif

[Sriram2009]

Hi Samid

In that case you can shift the TEMP DB to some other folder where you are having more disk space. Kindly refer the below link before that stop the SAP system.

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive. | Journey to SQL...

Regards

sriram

[Former Member]

Hello Sriram,

I don´t know if you can explain me or not this... but why this happened? Because all others systems that I have in this landspace have the same SO arquitecture (the tempdb disk has the same space - 5Gb) and only this system has this increase of space in tempdb.mdf and templog.ldf files!! Why? This is related with some setup in SQL Server that writes all the transactions/commits on database? It´s related with recovery model settings (in this case is already in Simple option)?! I don´t get it!! Why this happened...!?

regards,

samid raif

[Sriram2009]

Hi Samid

Kindly refer the Microsoft link -   Troubleshoot a Full Transaction Log (SQL Server Error 9002)

How to make transaction log full error 9002 go away??

Steps to in YouTube video MS SQL 2012 - How to Fix Error ERROR 9002 LOG FILE IS FULL.avi - YouTube

Regards

Sriram

[hasanajsh]

Hi Samid,

Please right click on the DB and check Properties>>Options.

What is the recovery model?

Regards,

Shkelzen

[Former Member]

Hi,

tempdb usage will depend on the application, gotta be something on the SAP side which is causing high tempdb transactions. Next time the log gets full, check the steps in the note :

1951819  - How to check which process makes the transaction log get full in SQL Server tempdb.      ...

Can you give me what SAP system is this, is this a BW system. It has more tempdb requirements. Check this note. for SQL Error 9002

1174635  - TempDB sizing in SAP BW systems on Microsoft SQL Server   

regards

Yogesh        

[Former Member]

Hi Samid,

The tempdb database is one of the system databases of SQL server, it is available for all users connected to the instance. This database stores only temporary information and is wiped clean after the transaction completes or instance restarts.

E.g if you are running a search or sort operation, data gets stored in the tempdb database. Now if a single transaction does similar operation and manipulates a large amount of data which is in the tempdb database, the transaction log for tempdb will get big. I have seen this in our environment in solution manager and BI systems.

At the time it is full, you can do a Select * from sys.databases and read the Col. log_reuse_wait_desc to get what is causing it to be full. usually you will see "active_transaction". If it is something else, you may need to look into it, let me know and I can help you diagnose it.

You can go to SSMS, Right Click, properties of the Database and increase the initial size to whatever you seem appropriate. Also keep the autogrow to some value so even if it fills up, it extends. Removing autogrowth is not a good idea. It will dump if it fills up. Be careful to provide sufficient storage space for tempdb, be it log or data especially in OLAP systems like NW BI, solution manager, APO etc.

if it is going abnormally large, it is usually a bad code, you can take help from SAP. to see what is causing this to happen, you will have to use some SQL diagnostic utilities or do some analysis in the SAP system. Note the time you get the error, check SAP system traces, maybe you can track down what is happening.

~Yogesh