Skip to Content
avatar image
Former Member

The transaction log for database 'tempdb' is full

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

SNAG-0748.jpg (34.3 kB)
SNAG-0746.jpg (32.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Dec 17, 2013 at 08:50 PM


    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 17, 2013 at 08:54 PM
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 08, 2014 at 08:26 PM

    Hi Raif

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

    Hope this helps

    -giri

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 09, 2014 at 08:04 AM

    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

    Add comment
    10|10000 characters needed characters exceeded