Results 1 to 10 of 10
  1. #1
    Join Date
    May 2014
    Posts
    7

    Unanswered: My sql 2005 database won't shrink.

    I have a 34gig .mdf file and a 28gig .ldf file on a database in Simple recovery mode. I mistakenly added another .ldf to the database that I can't delete because I can't empty it.

    I have tried shrinking the 28gig .ldf file but instead of getting smaller it gets bigger.

    I have autogrowth by 10% unrestricted. I have done backups.

    Any ideas on what I should do?

  2. #2
    Join Date
    May 2014
    Posts
    7
    Shrink is working fine on system databases.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Did the EMPTYFILE option fail, return an error, or do something different when you tried to clear the file so that you could drop it?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    May 2014
    Posts
    7
    DBCC SHRINKFILE (N'tempdblog2' , EMPTYFILE)
    ALTER DATABASE [db] REMOVE FILE [tempdblog2]

    It says it executes emptyfile successfully, but then remove gets

    The file 'tempdblog2' cannot be removed because it is not empty.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If the database is in FULL recovery mode, you may need to backup the transactions in the file to clear it. Run
    Code:
    dbcc loginfo()
    to see if there are any active VLFs in the second file. Acctive VLFs will have a non-zero status. FileID should be 3 for the second logfile, unless other datafiles were added along the way. you can check what the actual file id for the rogue log file by checking in the sys.database_files view.

  6. #6
    Join Date
    May 2014
    Posts
    7
    Quote Originally Posted by MCrowley View Post
    If the database is in FULL recovery mode, you may need to backup the transactions in the file to clear it. Run
    Code:
    dbcc loginfo()
    to see if there are any active VLFs in the second file. Acctive VLFs will have a non-zero status. FileID should be 3 for the second logfile, unless other datafiles were added along the way. you can check what the actual file id for the rogue log file by checking in the sys.database_files view.
    There are at least 20 active VLFs. Is there a way to stop them? This just went over my head.

    Thanks

  7. #7
    Join Date
    May 2014
    Posts
    7
    I should have mentioned this is the server ran out of space and I used the .mdf and .ldf files to move it to a server with more space. Could this be the issue?

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Run this query:
    Code:
    select name, recovery_model_desc, log_reuse_wait_desc
    from sys.databases
    where name = 'your database name here'
    This will tell you if the database is in FULL recovery mode, and if you need to backup the log, before you can remove the third file. If the log_reuse_wait_desc is not "Log_Backup", then you may need to look for active transactions in the database.

  9. #9
    Join Date
    May 2014
    Posts
    7
    MCrowley I really appreciate the help. It looks like it is

    dbname SIMPLE REPLICATION

    It is stuck in Replication

  10. #10
    Join Date
    May 2014
    Posts
    7
    MCrowley you set me in the right direction. Once I found out it was in replication I ran

    EXEC sp_removedbreplication db

    And now I was able to clear the growing log file issue.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •