Help with log file full error and than taking a sql server log backup or shrink log RRS feed

  • Question

  • Hello All,

    i am looking to find a way to run a script on demand.

    once i recieve a error message that my transaction lof file is full i would like to run a script that will take the transaction backup for that database only.

    now i know how to take transaction log backup and/or shirnk log DBCC but the issue is here

    i only want to take the log backup for the database that has generated the error through a script.

    i hope some one can help me out.

    we need this so if the log full error is issued so we can implement a temporary fix till figure out why log file were got so huge.

    Friday, September 16, 2011 4:43 PM

All replies

  • Don't wait till you'll get a message. It's a DBA's job to check this regularly depending on the growth. Also backup your database regularly.
    Friday, September 16, 2011 5:07 PM
  • Thanks for the response, we are doing it all what u mentioned..... this alert is basically for that 3:00 am  call

    so the job this start  , do a shirnk log commmand or transaction log backup, so in the morning we can resolved the issue long term.


    any help??? how can i isolate just the database that generated the error and run the command just for that database.

    Friday, September 16, 2011 5:28 PM
  • Use Following Query To Take Log Backup


    Backup Log DataBaseName
    To Disk='D:\LogBackup.Bak'
    With Stats

    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Friday, September 16, 2011 5:44 PM
  • Are u taking Log backup of DB at regular basis ??

    If yes then at what intervals ?  becoz if u will schedule log backup of DB at regular interval then it will automatically truncate log file every time and there will be no situation of full log file.

    If more transactions is running in your DB then Time interval of  taking log backup should be less so that it can truncate log file and create free space for new transactions. also you can define proper size of log file.



    Sunday, September 18, 2011 2:44 PM
  • What is the point of doing this ?

    If you take back up it's not to clean the log, but to keep the db in safe. If you need just to keep the log free set it to simple, still keeping an alert on the free space available.

    To understand why you cannot reuse the log run select * form sys.databases, there is a column where you can see what is keeping the log for freeing.

    I also spot something strange... you want to run this on many dbs, because you have many dbs in this situation ? I mean, you do not see why the log grows ? Which recovery model you have in place ? It must be simple, if it's not, you need to take back up on a regular basis.



    Sunday, September 18, 2011 2:54 PM