Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: How to Shrink *.LDF database?

    Hi,

    I don't know much about SQL server databases and\or administering them so I hope someone with more knowledge can assist me in shrinking the log file*.LDF file which is about 15GB in size and taking up space.
    I was hoping someone could give me the correct commands so I can use Query Analyser to run the command.
    I'm running SQL Server 2000.
    Date File: Export_data
    Transaction log: Export_log

    Also, will running this command have any risks? Do I need to stop any service or take the database offline whilst I do this?
    Your help will be appreciated.

  2. #2
    Join Date
    Dec 2007
    Posts
    11
    BACKUP LOG <Your DB Name > WITH TRUNCATE_ONLY

    DBCC SHRINKFILE (Export_log,0)

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Firstly you should be certain WHY you are needing to shrink. What recovery model is your database using? Are you doing transaction log backups and how often are you doing them? Whoever is responsible for taking backups of your database ought to be able to answer those questions.

    A word of caution about the suggestion made by Sanoj. BACKUP with the TRUNCATE_ONLY option will invalidate your chain of log backups. That means you are at high risk of losing data until you do the next FULL BACKUP. This is not to be recommended on a production system.

    Similarly, if your database is set to auto-grow, DBCC SHRINK will probably cause unnecessary and expensive file growth operations to happen later on and will cause fragmentation of your data files. For those reasons database performance may be adversely affected by shrinking.

    The best policy is to avoid expensive file growing and shrinking operations. To do that, you need to determine what problem you have, if any, and fix that problem rather than just try to mask the symptoms.

    Hope this helps.

  4. #4
    Join Date
    Apr 2009
    Posts
    2
    Thanks guys, I've made a backup of the transaction log and database. There is currently a maintenance plan to backup the transaction log file and to purge it but the reason why the maintenance plan is not running is because there is not enough space.
    So I made a backup of the database and created another disk partition with additional space. Next I want to move the database and transaction file so that the maintenance plan can run and do it's bit, is there any easy way of doing this as apposed to what Microsoft recommend below?
    How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

    I really do not want to stop any service or take the database offline.

    Again your help is really appreciated.

  5. #5
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    The only way to move the underlying database files is to detach and reattach and that is an offline process. Follow Microsoft's advice.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Use ALTER DATABASE to add a new file.
    Use DBCC SHRINKFILE with the EMPTYFILE option to empty the existing file.
    Then you should be able to remove the old one. You can't remove the primary file though but you can turn off autogrow so that it isn't used.

    That shouldn't require any downtime but test it out for yourself on another server before you try it for real.

Posting Permissions

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