?? After Rebuild!! I need to backlog with truncate and shinkfile but got the error "database in use". Why not???

  • I use the SQL 2005 SP2 Maintenance Plan to work with schedule.

    My Maintenance Plan has :-

    1. Rebuild Index

    2. T-SQL (with command as below) :-

    use DM_SCNYLDMS03_docbase

    backup log DM_SCNYLDMS03_docbase with truncate_only

    dbcc shrinkfile('DM_SCNYLDMS03_docbase',1)

    dbcc shrinkfile('DM_SCNYLDMS03_log',1)

    After MA start, Rebuild index step already done. But T-SQL step has got the error.

    dbcc shrinkfile('DM_SCNYLDMS01_log',1)

    " failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.

    How can i do??? Thank you.

  • See this: http://weblogs.sqlteam.com/tarad/archive/2007/11/08/60394.aspx

    You should not be truncating the transaction log. You should either be backing up your transaction log frequently, such as every 15 minutes, or switching your recovery model to SIMPLE.

    You should also not be shrinking the database. This is a very bad idea and is causing fragmentation. Shrinks should rarely be done and only be done manually.

    Tara Kizer
    Microsoft MVP for Windows Server System - SQL Server
    Ramblings of a DBA (My SQL Server Blog)[/url]
    Subscribe to my blog

  • @tara-2

    As per your blog, we have to disbale T-Log backup before starting Shirnk db or file operation.

    But in the above question, Tlog backup is being executed before shrink file operation.

    So, you say that when shrink file operation is started to being executed, Tlog backup was not completed and it thrown the error.

  • Frankly you should remove both the truncate and the shrink step. There's no need for either.

    If the rebuild is causing excessive log growth, switch to bulk-logged recovery before the rebuild and back to full after.

    Please read through this - Managing Transaction Logs[/url]

    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
  • Thanks for your advice.

    My meta-data size (.mdf) is 93.7 GB and log file (.ldf) is 30 GB. When i imported many images in sql per week. I have log file size around 30 GB per week. And then i will use maintenance plan to reduce sizing of .mdf and .ldf.

    Could you please advise me to maintenance for my all database sizing. Thank you.

  • nuengharit (7/22/2010)


    My meta-data size (.mdf) is 93.7 GB and log file (.ldf) is 30 GB.

    That sounds fairly reasonable.

    When i imported many images in sql per week. I have log file size around 30 GB per week. And then i will use maintenance plan to reduce sizing of .mdf and .ldf.

    Don't. Leave the files alone, all you're doing by shrinking is hindering performance, introducing fragmentation and forcing them to grow again next time data is added. You should not be regularly shrinking either. 120GB is small for a DB, the log file sounds reasonably sized for the data file size. Give them space to grow and leave them alone.

    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
  • Yep!!! I only use maintenance plan every month. But my space isn't enough.

    I don't have budget to buy the new one. Please advise if i need to reduce sizing every month.

    Thank you.

  • Why isn't the space enough? You need it to log the imports, regardless of whether you shrink.

    Also shrinking means that you are introducing fragmentation, and undoing much of the reindexing. The reindexing will use space as well, so you need to have free space.

    If the log is an issue, you can run more frequent log backups to help manage things, and break your updates into smaller transactions so the log space can be reused.

    Right now you need the space, you use it, then reclaim it with a shrink, and then use it again. A waste of time and resources.

  • Also consider that you may have a large number of indexes that aren't fragmented, and don't require rebuilding. You may have a situation where you're washing (rebuilding) your already clean car (indexes), then driving it straight through the mud (shrink/truncate). Of course, you're not starting from a clean car at the moment - it's constantly dirty due to the shrinks.

    Remember also that if your log grows to 30 GB each month, then you obviously must have room to accommodate this amount, unless you have multiple databases that want to grow in this fashion.

    If you have no room to perform the maintenance, then you have a choice - either secure funding for more disk space, or inform the business that performance may suffer due to an ability to perform maintenance.

  • nuengharit (7/22/2010)


    i imported many images in sql per week.

    Well in this case , you can opt for SIMPLE recover model and take log backup on 15 min interval.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (7/23/2010)


    Well in this case , you can opt for SIMPLE recover model and take log backup on 15 min interval.

    You cannot take log backups in simple recovery. Log backups are only allowed in full and bulk-logged recovery because in Simple the log is truncated every time a checkpoint runs.

    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
  • nuengharit (7/22/2010)


    Yep!!! I only use maintenance plan every month. But my space isn't enough.

    I don't have budget to buy the new one. Please advise if i need to reduce sizing every month.

    Thank you.

    How often are you taking full backups and logfile backups?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

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