Post reply

Transaction Logs growing too large

  • May 22, 2003 at 10:32 am

    #106157

    Hi all the SQL 2K Experts,

    Hope this is not too simplistic of a question. I have several databases that their LDF files are getting out-of-hand. I tried setting a limit, hoping that it will remove data and add new data while keeping the file within the size limit. But instead, it prevented DTS and other transactions from working with a message of Trans log if Full!!

    Shrinking, by definition (removing extra space) doesn't make sence and doesn't help much.

    What does everone else do with these files?

    Thanks

    A.

  • May 22, 2003 at 3:05 pm

    #458268

    If you don't backup trans log file, go to EM

    database-proterty-option-recovery mode, select simple. Then the trans log file will be truncated and shrinked automatically. If you want to back up trans log file, when it's growing too large, you can run

    dbcc shrinkfile(filename, truncateonly) first

    then run dbcc shrinkfile(filename).

    Try it.

    Robert

  • May 23, 2003 at 1:20 am

    #458303

    We keep the size of the log down and the recovey easy by setting up a database maintenance task to back up the transaction logs at regular intervals. Depending on the size and activity of the database, this interval varies between 15mins to an hour.

  • May 23, 2003 at 12:20 pm

    #458402

    Thanks for the feedback. I think I am going to detach the databases and reattach them without using the log file, so a new one will be created. I wonder if checking the auto-shrink option will prevent future growth.

    A.

  • May 23, 2003 at 1:18 pm

    #458410

    Hi,

    Run the following statements. This would reduce the size of the Log files. It removed all the unused space in the log file.

    Rename the databasenames from Northwind to ur corresponding database whose ldf file is huge.

    *********

    use Northwind

    go

    DBCC SHRINKFILE ( Northwind_Log , TRUNCATEONLY )

    BACKUP LOG Northwind WITH TRUNCATE_ONLY

    GO

    --drop table mytable

    CREATE TABLE MyTable (

    MyField VARCHAR(10)

    , PK INT )

    INSERT Mytable (PK) VALUES (1)

    go

    SET NOCOUNT ON

    DECLARE @Index INT

    SELECT @Index = 0

    WHILE (@Index < 200)

    BEGIN

    UPDATE MyTable

    SET MyField = MyField

    WHERE PK = 1 /* Some criteria to restrict to one row. */

    SELECT @Index = @Index + 1

    END

    SET NOCOUNT OFF

    GO

    DBCC SHRINKFILE ( Northwind_LOg , TRUNCATEONLY )

    BACKUP LOG Northwind WITH TRUNCATE_ONLY

    **********

    Regards

    Parasu Raman


    Regards
    Parasu Raman

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

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