Post reply

backup

  • June 20, 2013 at 4:16 am

    #1625797

    1) steps how i got Log_Backup for the simple

    .created new db with simple recovery

    .Created 20000 rows

    . took back up

    .till now it showslog_resue_wait_desc as Nothing

    created 20000 more records and now it shows as Log_backup

    2)how to check if active transaction log exsits?

  • June 20, 2013 at 4:29 am

    #1625800

    What do you mean by 'active transaction log'? There's always a portion of the log that's active.

    If you want to check log behaviour:

    Create one database switch it to simple recovery model, schedule backups (full) and make data changes

    Create a second database, switch it to full recovery model, schedule backups (full) and make data changes.

    You can't get log backup waits unless the DB has been in either full or bulk-logged recovery model and have just been switched to simple.

    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

  • June 20, 2013 at 4:52 am

    #1625810

    Hi Gail

    Sorry what i meant was "How to check active transaction?" (not log :))

    I have done the same steps as you mentioned for testing log behaviour. I had added attchemnt with my obervations. Please check the attchment and let me know what must have gone wrong.

    Also checked the log_reuse_wait_desc (select name, recovery_model_desc, log_reuse_wait_desc,* from sys.databases where name in( ..)) it shows Log_backup for simple recovery. ( in my previou reply i have mentioned how i managed to get this description for simple recovery model)

    Thanks and regards

  • June 20, 2013 at 5:07 am

    #1625815

    Krishna1 (6/20/2013)

    Sorry what i meant was "How to check active transaction?" (not log :))

    DBCC OPENTRAN

    Also checked the log_reuse_wait_desc (select name, recovery_model_desc, log_reuse_wait_desc,* from sys.databases where name in( ..)) it shows Log_backup for simple recovery. ( in my previou reply i have mentioned how i managed to get this description for simple recovery model)

    You cannot get a log_reuse_wait_desc of LOG_BACKUP for a database that is in simple recovery unless you've just switched out of full/bulk-logged. Double-check what you ran.

    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

  • June 20, 2013 at 5:36 am

    #1625831

    Hi Gail

    I still get the LOG_backup for simple recovery.

    This how i get it in sql 2012-

    .created new db with simple recovery

    .Created 20000 rows

    . took back up (Full)

    .till now it showslog_resue_wait_desc as Nothing

    created 20000 more records and now it shows as Log_backup

    Code used is as follows---------------

    create table a ( a1 char(1000) default 'a1',

    a2 char(1000) default 'a2',

    a3 char(1000) default 'a3',

    a4 char(1000) default 'a4',

    a5 char(1000) default 'a5',

    a6 char(1000) default 'a6',

    a7 char(1000) default 'a7',

    iinteger)

    insert into a (i) values (1)

    go 20000

  • June 20, 2013 at 6:24 am

    #1625847

    CREATE DATABASE TestingRecoveryModel

    GO

    ALTER DATABASE TestingRecoveryModel SET RECOVERY SIMPLE

    GO

    BACKUP DATABASE TestingRecoveryModel TO DISK = 'C:\Databases\Backups\Testing.bak'

    GO

    USE TestingRecoveryModel

    GO

    create table a ( a1 char(1000) default 'a1',

    a2 char(1000) default 'a2',

    a3 char(1000) default 'a3',

    a4 char(1000) default 'a4',

    a5 char(1000) default 'a5',

    a6 char(1000) default 'a6',

    a7 char(1000) default 'a7' ,

    i integer)

    GO

    insert into a (i) values (1)

    go 20000

    SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = 'TestingRecoveryModel'

    GO

    insert into a (i) values (1)

    go 20000

    SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = 'TestingRecoveryModel'

    GO

    Double check code and settings, you cannot get a log reuse wait of log backup on a database that's always been in simple recovery model.

    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

  • June 20, 2013 at 6:50 am

    #1625878

    Dear Gail

    Thank with the script given it working fine. I was trying through SSMS and it was creating the issue. Dont know whats going wrong when i try it from ssms

  • June 20, 2013 at 6:54 am

    #1625880

    Krishna1 (6/20/2013)

    Dear Gail

    Thank with the script given it working fine. I was trying through SSMS and it was creating the issue. Dont know whats going wrong when i try it from ssms

    I ran that from SSMS too. Either the DB was left in full recovery (which is the default) or you had some code somewhere that was changing the DB to full recovery.

    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

  • June 23, 2013 at 5:27 am

    #1626597

    Dear Gail

    I tried it again today. Attached documeent shows the step i took. But after taking full back up it makes the log_reuse_wait_desc as LOG_BACKUP.

    Not able to understand whats making it like this. Due to this log size just keeps increasing even though the recovery model is simple

    Did you try the same in sql2012 express edition? How do i check whats happning to recovery model? (becasue it still shows as simple)

    Regards

    Krishna.

  • June 24, 2013 at 1:22 am

    #1626670

    hi all

    Got the solution from folloing link . http://dba.stackexchange.com/questions/42068/sql-server-2012-simple-recovery-model-with-log-backup-log-reuse-wait-desc

    After creating a DB from SSSM GUI, immidiatly changed the recovery model to full. Then again made it as Simple. After this it works fine

Viewing 10 posts - 16 through 24 (of 24 total)

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