sql 2005 and logshipping

  • anthony.green (8/6/2012)


    No, when you run a shrink with Truncate Only you reset the LSN's.

    If you just run DBCC SHRINKFILE you dont reset the LSN's

    in fact, because i'm lazy, i've just use right click on the database, then task, then shrink file, and choose file type log. choose by defaut "release unsued space"

    and that's all.

    in every time I tried to use this think, it wont work. In fact it did not do anything.

    But today, my LDF file will be smaller...

    I trie to understand why today it's work? And the only different thing I do, was the backup log to disk... ?

    I will try on my real server, and test that and pray to dont break the logshipping...

  • Today, I retest the same thing of yesterday always on my test server...

    i'm totaly lost

    what was working yesterday wont today...

    with this error message at the restore of the logshipping job :

    Message

    2012-08-07 10:17:04.25*** Error: Could not find a log backup file that could be applied to secondary database 'dbbis'.(Microsoft.SqlServer.Management.LogShipping) ***

    2012-08-07 10:17:04.25Deleting old log backup files. Primary Database: 'db'

    2012-08-07 10:17:04.25The restore operation completed with errors. Secondary ID: '88f06934-0122-4cf9-92a6-b8113015683e'

    2012-08-07 10:17:04.25----- END OF TRANSACTION LOG RESTORE -----

    I did the same thing... I'll be crazy...

  • could you tell me how to do that "You will need to query sys.databases and when it returns 'nothing' then you can shrink."

    because maybe the sql server was not finish his activity ?

  • didi77 (8/7/2012)


    Today, I retest the same thing of yesterday always on my test server...

    i'm totaly lost

    what was working yesterday wont today...

    with this error message at the restore of the logshipping job :

    Message

    2012-08-07 10:17:04.25*** Error: Could not find a log backup file that could be applied to secondary database 'dbbis'.(Microsoft.SqlServer.Management.LogShipping) ***

    2012-08-07 10:17:04.25Deleting old log backup files. Primary Database: 'db'

    2012-08-07 10:17:04.25The restore operation completed with errors. Secondary ID: '88f06934-0122-4cf9-92a6-b8113015683e'

    2012-08-07 10:17:04.25----- END OF TRANSACTION LOG RESTORE -----

    I did the same thing... I'll be crazy...

    There is no file which is valid for restore, either find all your transactions logs from backup devices and restore them, or reinitalise logshipping

  • didi77 (8/7/2012)


    could you tell me how to do that "You will need to query sys.databases and when it returns 'nothing' then you can shrink."

    Look back through the topic, I gave the query you needed in an earlier post.

  • thanks, i've got it,

    select log_reuse_wait_desc from sys.databases where name = ''

    it's return nothing, so I will re test again and again until a broke the logshipping sequence...

    I let you know

  • You are putting the database name inside the ''

  • oh 😀

    ok

    now I see "nothing"

    I let you know, I will make the same test than this morning.

  • I have to be faster than sql to work If I try to understand.

    here my step of the test.

    One database, one logshipping, one update query (to grow the LDF file), one backup log to disk to free the space into the ldf file (with or without shrink no matter).

    1-clear the LDF file (simple recovery, schrink ldf file (512ko).

    2- Pass the database into full recovery, and set up the logshipping.

    3- checking that everything works

    4- update query to grower the LDF file witch pass to 512ko to 300 000 mo.

    5- checking that the sync of the logshipping are ok

    6- use the query of the sys database to be sure that "nothing" happends on the both database (main and slave)

    7-backup log the main database

    8-check that the sync of the logshipping are ok : is it!!

    9- update again with a big query. LDF file dont grew up because the file are clear because of the backup log.

    10 - Check the sync! Working !

    Now I will try to broke the logshipping. If I resume, if I make a backup log when there is another thing than "nothing" i'm sure that it will break it, isnt it ?

    step to break the logshipping

    1- update the DB with a big query

    2- use the query of the sys database on the both database (main and slave), main database say "active transaction" and just after a refresh "backup log", slave are set to "nothing"

    4- backup log to disk the main database before the Lsrestore. It create a hudge .trn into the folder of my logshipping.

    5- use the query of the sys database "nothing" appears now.

    6- test the sync : it's BREAK the logshipping sequence with the error of this morning "error: Could not find a log backup file that could be applied to secondary database "

    So to resume, it's an impossible query "the backuplog to disk" to schedule for down the LDF file (or to stop grewing up), because I could not be sure that there is no transaction incomming.

    It come more clear to me now, but in my case, what will you do ?? Is there a schedule way, or it's just an "emergency way" and use it manualy will all precautions that implies?

  • didi77 (8/7/2012)


    I have to be faster than sql to work If I try to understand.

    here my step of the test.

    One database, one logshipping, one update query (to grow the LDF file), one backup log to disk to free the space into the ldf file (with or without shrink no matter).

    1-clear the LDF file (simple recovery, schrink ldf file (512ko).

    2- Pass the database into full recovery, and set up the logshipping.

    3- checking that everything works

    4- update query to grower the LDF file witch pass to 512ko to 300 000 mo.

    5- checking that the sync of the logshipping are ok

    6- use the query of the sys database to be sure that "nothing" happends on the both database (main and slave)

    7-backup log the main database

    8-check that the sync of the logshipping are ok : is it!!

    9- update again with a big query. LDF file dont grew up because the file are clear because of the backup log.

    10 - Check the sync! Working !

    Now I will try to broke the logshipping. If I resume, if I make a backup log when there is another thing than "nothing" i'm sure that it will break it, isnt it ?

    step to break the logshipping

    1- update the DB with a big query

    2- use the query of the sys database on the both database (main and slave), main database say "active transaction" and just after a refresh "backup log", slave are set to "nothing"

    4- backup log to disk the main database before the Lsrestore. It create a hudge .trn into the folder of my logshipping.

    5- use the query of the sys database "nothing" appears now.

    6- test the sync : it's BREAK the logshipping sequence with the error of this morning "error: Could not find a log backup file that could be applied to secondary database "

    So to resume, it's an impossible query "the backuplog to disk" to schedule for down the LDF file (or to stop grewing up), because I could not be sure that there is no transaction incomming.

    It come more clear to me now, but in my case, what will you do ?? Is there a schedule way, or it's just an "emergency way" and use it manualy will all precautions that implies?

    just an edit : when we set up a logshipping it create 3 step : Lsbackup, lsCopy, and lsrestore.

    What is the difference between the lsbackup and my backup log in a query ? Why did it lost the sequence when I made a manualy backup log? and why when the logshipping set it by is way, it's working ? Maybe (i'm sure of it) that it's not the same syntax ?

  • Nothing, Logshipping issues BACKUP LOG commands like you would do normally.

    You have missed one key point out of your process.

    When you change the database to SIMPLE, to shrink the log, the back to full, you will need to do a full backup to initialise the LSN's and be able to setup logshipping again.

  • anthony.green (8/7/2012)


    Nothing, Logshipping issues BACKUP LOG commands like you would do normally.

    You have missed one key point out of your process.

    When you change the database to SIMPLE, to shrink the log, the back to full, you will need to do a full backup to initialise the LSN's and be able to setup logshipping again.

    the logshipping make a full backup in his first step. So I didnt do it again, and it's work fine.

    So i'm sure there is a difference between the lsbackup and mine, or else why it's broke when I did when the sysdatabase are "logbackup" ? and work fine, when I let the logshipping made his job.

    Also, the backup log to disk that I set, stop growing the LDF file, witch is not true if I dont do it myself and let the logshipping work alone.

  • anthony.green (8/7/2012)


    Nothing, Logshipping issues BACKUP LOG commands like you would do normally.

    You have missed one key point out of your process.

    When you change the database to SIMPLE, to shrink the log, the back to full, you will need to do a full backup to initialise the LSN's and be able to setup logshipping again.

    Actually, a differential backup would serve the same purpose and probably be smaller.

  • Thanks Lynn, always forget about diff backups to reinitialise, force of habbit thing I think.

  • didi77 (8/7/2012)


    Why did it lost the sequence when I made a manualy backup log?

    Because the log backup went to a location outside of the log shipping backup folder??

    Can you post the exact command you used for the log backup?

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 15 posts - 16 through 30 (of 39 total)

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