Post reply

SQL 7 Can not truncate Transaction Log

  • November 28, 2006 at 2:34 am

    #173615

    I have a database which has the following size properties;

    Data Space : 79.63Mb (79.13Mb used)

    Transaction Log Space: 1,330.24Mb (100.24Mb used)

    I have tried "truncate transaction log" and "shrink database", but neither made a significat difference. I also performed these two actions with the database in single user mode.

    Any help appreciated.

  • November 28, 2006 at 2:42 am

    #674267

    Simply backup the transaction log to truncate it.

    Pankaj Khanna
    Database Administrator - SQL Server 2000

    Keep hope to keep you intact...

  • November 28, 2006 at 2:55 am

    #674269

    I have tried as you suggested, but it only appeared to remeove a small percentage of the log file (maybe todays transactions ?).

    The log file still remains abnormally large.

    Thanks for your help.

  • November 28, 2006 at 3:20 am

    #674271

    if only 100mb of the 1.3gb is used you don't need to truncate the log - you need to shrink it.....

    trunctate log means to remove the inactive entries from within the log (clearing space within the file)

    what you need to do is either a dbcc shrinkfile or use enterprise manaager and right click on the database and choose the shrink option from the all tasks sub menu

    MVDBA

  • November 28, 2006 at 3:21 am

    #674272

     

    Can you tell me the recovery model set for the database, the initial size of the log file and its growth settings?

    Pankaj Khanna
    Database Administrator - SQL Server 2000

    Keep hope to keep you intact...

  • November 28, 2006 at 3:29 am

    #674274

    rcovery model is down to what recovery options you need.

    there are 2 basic modes in sql 2000/7

    simple mode (or in sql 7 truncate log on checkpoint on)

    in this mode you simply back up your database using the maintenance plan or a backup database command on a nightly ? basis - no srinking/truncating of the log files are needed unless you perform some extremely large operation

    FULL mode (truncate log on checkpoint off in sql 7) in this mode you backup nightly?? and then also shedule a backup log command every hour or so(can be done through maintenance plan wizard!!!)

    the benefit of full mode is that if you have a disaster you can restore the database and then restore the T-log files up to the point just before your disatser - you should really practice this though before assuming your DR strategy works

    initial size of the log is fairly easy - idepends on the size of the database, but for your database it's say set it to 100Mb and leave it at 10% growth - it might grow once or twice during defrags but that should be it - if the log has to grow then your database runs slow so don't bother reclaiming space that the log will grow into agian

    MVDBA

  • November 28, 2006 at 3:29 am

    #674273

    Also, please run the command:

    DBCC SQLPERF(LOGSPACE)

    and let me know the %used value for the database in question.

    Pankaj Khanna
    Database Administrator - SQL Server 2000

    Keep hope to keep you intact...

  • November 28, 2006 at 3:53 am

    #674279

    Pankaj;

    Reccovery Model - Full

    Initial size - not to sure how to identify this

    Growth - Automatic by 10% unrestricted,

    dbcc sqlperf(logspace)

        Database Name Log Size (MB) Log Space Used (%) Status     

        BondTracker   1253.9922     1.7391721          0

    Mike;

    I think the log file grew initially due to a number of data load operations prior to use, these will not happen again in the day to day operation of this application.

    Thanks for your interest and help.

  • November 28, 2006 at 4:07 am

    #674284

    Thanks for providing all the information.

    Please carry out a few checks:

    1) Check whether there is any open transaction on the database or not. To check this, use the following commands:

    use <database-name>

    go

    DBCC OPENTRAN

    If it returns any row, please check the SPID value, and see what that SPID is doing (for this, the command is :

    DBCC INPUTBUFFER(<SPID&gt

    However, if you don't see any output of DBCC OPENTRAN command, then procede as below.

     

    2) Issue a manual checkpoint. Simply type CHECKPOINT in the query analyser and press F5. It should give you a message "The command is successfully completed"

     

    3) Then take a backup of the transaction log.

     

    4) Issue DBCC SHRINKFILE (<fileID of the log file&gt

    I hope this will help.

    Good Luck

     

     

    Pankaj Khanna
    Database Administrator - SQL Server 2000

    Keep hope to keep you intact...

  • November 29, 2006 at 2:23 am

    #674606

    maybe this will get you on track ...

    INF: How to Shrink the SQL Server Transaction Log

    http://www.support.microsoft.com/kb/256650

     

    and yes, you may have to repeat this action a couple of times

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • November 29, 2006 at 10:22 am

    #674742

    I know this is going to cause 'Howls' of disbelief but if you are NOT using the transaction logs at this point to rollback transactions by the hour or whatever and find that they are just growing and getting in your way you can just delete them, here's how. Dettach the database, delete the log file and then reattach the database. A new empty log file will be created. Try it on a test database first to get the hang of it.

    Guerrilla Sql.

  • November 29, 2006 at 10:40 am

    #674746

    Thanks to everyone who replied.

    Using a combination of the suggestions, the log file has been successfully truncated to a more appropriate size.

  • November 29, 2006 at 1:05 pm

    #674785

    In the future:

    I have dealt with this in many ways.

    I am about 99.9% positive this will work.

    1. Backup Log File - Transaction Log
    2. Change recovery model to simple
    3. change recovery model back to Full
    4. Run DBCC SHRINKFILE

    This is a problem with SQL server. I have never had this method fail

    EXAMPLE:

    USE PUBS

    GO

    BACKUP LOG PUBS

     TO DISK = 'C:\Pubs.TRN'

    GO

    ALTER DATABASE PUBS

     SET RECOVERY SIMPLE

    GO

    ALTER DATABASE PUBS

     SET RECOVERY FULL

    GO

    DBCC SHRINKFILE (pubs_log, 0, TRUNCATEONLY)

    GO

  • November 30, 2006 at 3:33 am

    #674873

    what on earth are you doing jdixon?

    if you set recovery simple you risk breaking the LSN chain so that reverting back to recovery mode full will be useless

    you don't need to set recovery simple and then back to full in order to do a shinkfile

    if you want to clear the log then issue a checkpoint and packup the log. then do the shrinkile.

    never swap your recovery modes like that

    MVDBA

  • November 30, 2006 at 9:26 am

    #674972

    Then Backup the database when you are done. You have to do what you have to do.

Viewing 15 posts - 1 through 15 (of 17 total)

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