Post reply

Can't shrink a DB that came from SQL 7 to 2000 to 2005 and now 2008

  • January 6, 2011 at 8:21 pm

    #232739

    OK first the history of the DB.

    It was originally a SQL 7 DB then upgraded to SQL 2000 then to 2005 then to SQL 2008. The compatibility is set for SQL 2000 (8.0).

    The owner of the DB has never done replication, clustering or anything, it's just always been a regular DB.

    For some crazy reason I/we can not shrink the TX log file (.ldf). It's twice the size of the DB file itself. The DB file is 27GB, the TX file is ~47GB.

    What I have tried is the normal that has always worked....

    Change Recovery mode to Simple then run the following:

    DBCC SHRINKFILE(<Filename_log>)

    Or

    DBCC SHRINKFILE(<Filename_log>, 2)

    Put recovery mode back to FULL.

    That always worked..........till now.

    I also did a SQL FULL backup first and ran the above and still no shrink.

    Doing a "BACKUP............WITH TRUNCATE ONLY" doesn't exist in SQL 2008.

    The DB owner does backups using Redgate software so we did a FULL backup then did another backup and put a check box in the "Truncate empty......something" (can't remember exactly the wording).

    Still the TX log file is big.

    Short of doing the ever so dangerous "shut down the DB, delete TX file then bring it back up and hope SQL creates a new one....", what else can I do?

    Anything will help.

    Thanks

  • January 6, 2011 at 10:29 pm

    #1270411

    Please run DBCC LOGINFO against that database and check if the status for any VLFs is 2. If yes, then issuing transaction log backup multiple times until all the VLFs' status becomes 0. Once it is 0, you will be able to shrink the transaction log.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • January 6, 2011 at 11:48 pm

    #1270435

    I also did a SQL FULL backup first and ran the above and still no shrink.

    Doing a "BACKUP............WITH TRUNCATE ONLY" doesn't exist in SQL 2008.

    Taking FULL backup of database will not truncate the log, so you wouldn't be able to shrink the log.

    You need to take t-log backup for that.

    M&M

  • January 7, 2011 at 11:30 am

    #1270809

    We will try that.

    I'll let you know if it worked.

    Thanks

  • January 7, 2011 at 11:31 am

    #1270810

    Is it safe to assume that when you say take a backup of the TX log, you mean using the SQL native backup and not RedGate?

    We'll try that and let you know if it works.

    Thanks

  • January 7, 2011 at 11:53 am

    #1270820

    Warren Peace (1/7/2011)

    Is it safe to assume that when you say take a backup of the TX log, you mean using the SQL native backup and not RedGate?

    If your regular backup schedule makes use of Redgate, then please continue to take it through RedGate.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • January 7, 2011 at 1:39 pm

    #1270901

    try:

    backup log <database> with no_log

Viewing 7 posts - 1 through 6 (of 6 total)

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