Post reply

Backup Transaction Logs

  • March 29, 2004 at 4:11 am

    #161134

    Hi,

    I've read loads over the last 3 days about backing up databases/transaction logs and am little the wiser.

    My database's recovery model is set to FULL and the log is set to unrestricted growth.

    I perform a scheduled daily backup to a file using the following command:

    BACKUP DATABASE [ASWebDB] TO  DISK = N'D:\BACKUP\ASWEBDB_BACKUP' WITH  INIT ,  NOUNLOAD ,  NAME = N'ASWebDB backup',  NOSKIP ,  STATS = 10,  NOFORMAT

    The backup works great but the transaction log never shrinks. From what I've read, the transaction log should 'empty' all of the completed transactions out of itself. The log is now 9.5 Gig and growing.

    Am I misunderstanding what should happen or am I doing something wrong?

    If I set the transaction log to have a maximum size, does this mean that once that size is met it won't write anything new to it, or does it mean it will start overwriting old entries? I don't want to do that, but I was curious.

    Thanks for your time.

     

    Windows 2008 Server | SQL Server 2008

  • March 29, 2004 at 5:44 am

    #500849

    Full database backups and transaction log backups work independently.

    Your transaction log will continue to grow until you back it up using something like this:-

    BACKUP LOG [ASWebDB] TO DISK = N'D:\BACKUP\ASWEBDB_LOG_BACKUP'

    or truncate it using:-

    BACKUP LOG [ASWebDB] WITH TRUNCATE_ONLY

    I would definately recommend the former, otherwise you will only be able to recover to the last full backup in the event of a problem.

    If you set a maximum size for the log, when that is reached, your transactions will fail until the log is backed up or truncated or increased in size.

  • March 29, 2004 at 7:58 am

    #500857

    Thanks Ian,

    I suspected as much (about the capped log size).

    I'll give the BACKUP LOG function a try.

    Once again, thanks for your time.

    Windows 2008 Server | SQL Server 2008

  • March 30, 2004 at 7:10 am

    #501068

    HI,

    Well I had this problem ... SQL Server provides two DBCC commands 1. DBCC Shrinkfile 2. DBCC ShrinkDatabase ...

    for your full recovery ... take backup of transaction log .. and then close all connections to your Database ... execute this command using Query Analyser .. (Read Books Online for help) ... or you can use Enterprise Manager ... Right Click your database ... go to All Tasks ... go to Shrink Database ... click on Files button ... select your log file from the drop down list ... click OK ... if all goes fine ... you log will be cut short... other wise ... search for this ... i am in a hurry right now ... if you had any problems ... ask freely ...

    Regards,

    Rattu

    - Rattu

  • March 30, 2004 at 3:00 pm

    #501192

    If your main question is how to shrink the log file you could run the Backup Log ... With Truncate_only and then immediately run Backup Database. Once you have done that you should be able to issue the DBCC SHRINKDATABASE command and have it shrink as much as it is going to.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • March 31, 2004 at 12:49 am

    #501228

    Thanks, all. Managed to get it done.

    Backed up database, backed up transaction log, truncated the log then ran shrinkfile on the log.

    Worked a treat.

    Once again, thanks to everyone.

    Windows 2008 Server | SQL Server 2008

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

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