Post reply

Log Shipping : the tran. log of both the Pri. & Second. servers are large

  • November 9, 2008 at 11:30 pm

    #126801

    I found the transaction log of both the Primary & Secondary Databases are very large : more than 1 GB.

    (1) Should I shrink them manually ?

    (2) And should I set their "Auto Shrink" options to TRUE ?

    (3) Do I need to keep the initial backup copy of the Primary database all the time ? Or it can be deleted once the log shipping has been started successfully ?

  • November 9, 2008 at 11:50 pm

    #896127

    Manually shirk DB.

    Don't enable Auto Shrik DB, it causes performance issues.

  • November 10, 2008 at 1:28 am

    #896146

    Don't shrink the databases. It causes massive fragmentation of indexes.

    You can, as a once off operation, shrink the log down. If, however the log file needs to be that size because of the frequency of the log backups and activity in the DB, it will just grow again. Growing the log will slow down transactions and may cause fragmentation of the file on disk.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 10, 2008 at 1:31 am

    #896147

    Thanks.

  • November 10, 2008 at 8:50 pm

    #896691

    Do I need to keep the initial backup copy of the Primary database all the time ? Or it can be deleted once the log shipping has been started successfully ?

  • November 11, 2008 at 3:24 am

    #896834

    onlo (11/10/2008)

    Do I need to keep the initial backup copy of the Primary database all the time ? Or it can be deleted once the log shipping has been started successfully ?

    you don't need the initial full backup to recover the secondary so yes it can be deleted. You do need A full backup of the primary though in case something happens to the primary database and you need to restore it.

    full backups do not break the log backup chain so you can take full backups as often as you like, so make sure you do frequent full backups of your database.

    ---------------------------------------------------------------------

  • November 11, 2008 at 3:31 am

    #896836

    Thanks

  • November 11, 2008 at 9:07 am

    #897007

    Hello,

    I have been reading this blog, and I am fearly new to SQL server DB. I am interested to know: SQL DB is on line and can you do a full back up?

  • November 11, 2008 at 9:46 am

    #897031

    marianne.schillings (11/11/2008)

    Hello,

    I have been reading this blog, and I am fearly new to SQL server DB. I am interested to know: SQL DB is on line and can you do a full back up?

    Please create new threads for new questions and don't hijack someone else's thread.

    Yes, SQL can do online backups. Look in Books Online for BACKUP DATABASE

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 11, 2008 at 10:00 am

    #897040

    Thank you!

Viewing 10 posts - 1 through 9 (of 9 total)

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