• Replies 10 replies
  • Subscribers 336 subscribers
  • Views 53 views
  • Users 0 members are here
Related

Transaction log is full - how to fix this?

Former Member
Former Member $organization

Hi everyone,

It’s been a while since I used out Test system and upon trying to log in today I got the error “The transaction log for database ‘impresario’ is full”.

I can see allot of conversation in the forums about transaction logs but seems I know very little about this topic I don’t really know where to start. Can someone point me in the right direction or have any documentation on how to solve this? I don’t know what kind of backup our Test  database even has, or where to find this.

I downloaded the free trial of RedGate SQL Script Manager that I saw recommended by a few but not sure where to go from there.

Thanks for any help!

Natasha

Parents
  • $organization

    Since this is your test system. First create a SQL job with embedded code to truncate the log file. Then create a SQL alert with type of "SQL Performance Condition alert". For the alert, use the counter of "Percent Log Used". For the "Response", execute the job that truncates the log file.

    Here is an example of the code to truncate the log file:

    -- Change the database recovery model to SIMPLE.
    ALTER DATABASE impresario
    SET RECOVERY SIMPLE
    GO

    -- Truncated log file.
    DBCC SHRINKFILE (impresario_log, TRUNCATEONLY)
    GO

    -- Reset the database recovery model.
    ALTER DATABASE impresario
    SET RECOVERY full;
    GO

     

    Hope this helps.

     

Reply
  • $organization

    Since this is your test system. First create a SQL job with embedded code to truncate the log file. Then create a SQL alert with type of "SQL Performance Condition alert". For the alert, use the counter of "Percent Log Used". For the "Response", execute the job that truncates the log file.

    Here is an example of the code to truncate the log file:

    -- Change the database recovery model to SIMPLE.
    ALTER DATABASE impresario
    SET RECOVERY SIMPLE
    GO

    -- Truncated log file.
    DBCC SHRINKFILE (impresario_log, TRUNCATEONLY)
    GO

    -- Reset the database recovery model.
    ALTER DATABASE impresario
    SET RECOVERY full;
    GO

     

    Hope this helps.

     

Children
No Data