SQL Server Performance

Issue of the The transaction log for database 'tempdb" is full.

Discussion in 'ALL SQL SERVER QUESTIONS' started by yatin baraiya, Mar 30, 2012.

  1. yatin baraiya New Member

    Hy all

    i am new member in this sql forum.

    my application is j2ee based web application, we have used the struts 2.0,jsp,xml,java, and database is sql server databse 2005.

    i am facing below issue wtih sql server databse 2005,

    com.microsoft.sqlserver.jdbc.SQLServerException: The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
    at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
    at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)

    when the "xyz. sql" sp procedure execute from the java code we get the above exception.

    i have find the log size using the below query

    dbcc sqlperf(logspace)

    DatabseName :-tempdb
    Log Size (MB):-0.7421875
    Log Space used(%):- 51.31579
    Status:-0.

    i want to regenerate the above issue in my computer,is there any way to full the transaction log of temdb log size in my db environment for reproduce the said issue.?

    also i want the specific solution for said issue. Pls help me anybody.
  2. Shehap MVP, MCTS, MCITP SQL Server

    To reproduce the same issue ,simply you have to run transactions that consuming intensively any of temp DB parts (User objects + Internal objects+ Version store) such as :

    • Any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;
    • DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;
    • DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;
    • large resultsets involving unions, order by / group by, Cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb;
    • Any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;

    For resolutions, you have 2 options :

    · Either to end any relevant transactions that impact on TempDB then starting shrinking DB

    · Or create new Tempdb File in a different path using the below script >>> Restart SQL Service >>>Delete old TempDB files

    usemaster

    go

    Alterdatabasetempdbmodifyfile (name=tempdev,filename='D:\Database\SQLPortal1\Tempdb\tempdb.mdf')

    go

    Alterdatabasetempdbmodifyfile (name=templog,filename='D:\Database\SQLPortal1\Tempdb\templog.ldf')

    go

Share This Page