Post reply

HELP: Huge Transaction Log

  • April 22, 2005 at 2:46 am

    #88729

    I think I have upset one of my databases! The data file is 7M (of which 7M is used), but the transaction log is 902M (of which 523M is used).

    I have tried shutting down SQL Server and rebooting the server.

    I have tried backing up the transaction log, entire database etc and shrinking the database, but it will not budge.

    Restoring to a new database and shrinking resulted in the following:

    • Data: 7M (7M used)
    • Log: 468M (19M used)

    Any suggestions on how to find what is using the log space, how to stop it and how to recover it.

    If it helps, I am really a programmer not a DBA

    .... just an update ...

    Since a backup and restore appeared to help, I thought I would try again. So I backed up the new copy of the database and restored it to a third copy. After shrinking it is now:

    • Data: 7M/7M
    • Log: 1M/1M

    So it looks like it has cured the problem, but is it a reasonable thing to have done? I would also still appreciate some tips on how to examing the original log file to try to find the case of the problem.

  • April 22, 2005 at 3:13 am

    #554296

    You can read the following regarding to your problem:

    http://www.support.microsoft.com/?id=110139

  • April 22, 2005 at 3:49 am

    #554303

    Thanks, but I did that. I'm not sure I understood it as I don't think any of the causes it suggests apply to my database. Hence the request for help trying to find what is the cause. Are there any tools to 'look into' a log file to see what is clogging it up?

  • April 22, 2005 at 4:54 am

    #554308

    Well I know one tool but never used.

    http://www.sql-server-performance.com/log_explorer_spotlight.asp

    Are you looking this one?

    Hope this helps

  • April 22, 2005 at 5:08 am

    #554311

    It depends if you're interested in what is in your transaction log?

    If you don't need transaction log restores then make your database simple recovery and the logs will clear themselves out automatically. 900Mb is pretty small to be honest - I have a log which grows 6gb each time I re-index the database < grin >

    Issuing a checkpoint may help clear the log.

    bear in mind that nearly all data changes are logged so if you dts data in , rebuild indexes and so on - then you need free space in your log for this work.

    If you need your transaction logs, database is in full recovery then you need to schedule regular log backups to clear the log.

    Check out the MS SQL 2000 Admin companion or Inside sql 2000 ( I'd suggest these as essential reading anyway )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • April 22, 2005 at 7:55 am

    #554346

    Many thanks for the replies. Yes, I agree 900M is small, but >100x the data file size struck me as a bit odd. One of my concerns is that I will want to move the DB to a web hosting company when the application is finished, so I don't want to end up paying to an excessive log file whe the DB should be quite small.

    My other concern was that the log would not shrink (there is a regular log backup and I thied various mauual steps). Hence my concern that something was wrong.

    Not surprisingly everything is also now working much faster. One process that took over an hour yesterday (which was why I started investigating problems) completed in 5 minutes this morning

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply