locked
log file RRS feed

  • Question

  • hi every one

    is shrink log file for small size  in sql server 2008

    my need log for restore

    my idea is

    USE dbName;
    GO
    backup log dbName TO  DISK = N'E:\test' 
    -- Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE dbName SET RECOVERY SIMPLE;
    GO
    -- Shrink the truncated log file to 1 MB.
    --DBCC SHRINKFILE (dbName_log, 1);
    GO
    -- Reset the database recovery model.
    ALTER DATABASE dbName  SET RECOVERY FULL;
    GO

    but message recive

    Msg 4214, Level 16, State 1, Line 2
    BACKUP LOG cannot be performed because there is no current database backup.
    Msg 3013, Level 16, State 1, Line 2
    BACKUP LOG is terminating abnormally.


    • Edited by ahmadiza Monday, October 15, 2012 8:29 AM
    • Changed type ahmadiza Monday, October 15, 2012 8:33 AM
    Monday, October 15, 2012 8:27 AM

Answers

  • my problem solved by this command
    BACKUP LOG [databaseName] 
    TO DISK = 'nul:' WITH STATS = 10
    
    :)
    • Marked as answer by Shulei Chen Wednesday, October 17, 2012 2:37 AM
    Tuesday, October 16, 2012 11:56 AM

All replies

  • this error has occured because you have changed the recovery model of the database from SIMPLE Recovery Mode to Full or vice versa,

    changing the recovery mode of the database will reset the lsn(log sequence Number) .after changing the recovery mode and shrinking the database you have to take the FULL Backup  of the database. and the log Backup of the database


    Ramesh Babu Vavilla MCTS,MSBI


    Monday, October 15, 2012 9:21 AM
  • Hello,

    The reason the BACKUP LOG command failed is exactly what is in the error text, you don't have a valid full database backup for that database. First take a full backup, then take a log backup if you so desire.

    The problem I am seeing is that we don't know if shrinking your log is actually going to succeed. I would start by investigating sys.databases for the log_reuse_desc column and see if the log is needed for anything first.

    Once the log had been shrunk, I would manually exand it to the size it needs to be or at the very least a size that shouldn't need to grow right away (if space is currently an issue).

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    • Proposed as answer by Shulei Chen Tuesday, October 16, 2012 8:40 AM
    Monday, October 15, 2012 12:28 PM
    Answerer
  • my problem solved by this command
    BACKUP LOG [databaseName] 
    TO DISK = 'nul:' WITH STATS = 10
    
    :)
    • Marked as answer by Shulei Chen Wednesday, October 17, 2012 2:37 AM
    Tuesday, October 16, 2012 11:56 AM
  • A few things:

    1. You have successfully broken the log chain, if one ever existed. You'll need to restart it by either taking a differential or full backup. This means your data is exposed and if you have data loss or corruption you won't be able to get it back.

    2. No root cause was found, this just happened to work. Since we don't know the root cause, we don't know if it will ever break again or what to do to fix it. This really isn't a solution.

    3. Truncating the log isn't a good strategy. If you don't need transaction log backups, switch to the simple recovery model.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    Tuesday, October 16, 2012 8:01 PM
    Answerer
  • Check command, what each commadn is doing

    USE dbName;
    GO

    ####You are trying to take log backup. But you need to have full backup to take log backup. You cna say full backup is to set the start lsn number & log backup use that lsn number to keep backup sequence for recovery. It seems that you have not taken the full backup or you have reset the lsn by truncating the logs or by changing the recovery model before log backup.
    backup log dbName TO  DISK
    = N'E:\test'
    -- Truncate the log by changing the database recovery model to SIMPLE.

    #####you have changes the recovery model to SIMPLE this will break you lsn chain.

    ALTER DATABASE dbName SET RECOVERY SIMPLE;
    GO

    ####Normall shrink operation. But try to avoid as most as possible to shrink files becuase it hammpers your performance.
    -- Shrink the truncated log file to 1 MB.
    --DBCC SHRINKFILE (dbName_log, 1);
    GO
    -- Reset the database recovery model.

    #####You have changed backup to full recovery model, You need fresh full backup after thsi to have log backup.
    ALTER DATABASE dbName  SET RECOVERY FULL;
    GO

    Note : You can plan only log backup on set interval & then plan to shrink the file (if it is utmost required). Regular log backups keeps yous log file size in control. No need ot changing recovery model again & again. It impact your recovery.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, October 16, 2012 10:08 PM