How to shrink the log file

Posted on 2004-10-22
Last Modified: 2008-02-01

Can anyone advise me how to shrink the log file of a SQL Server database?
I have tried to follow the Microsoft article 272318 "Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE" but to no avail.
When I run the code:
the system tells me that the command has been run successfully but the log file size does not change.  Then if I run the code
 DBCC SHRINKFILE(Projects_log,2)
I get the error message:
"Server: Msg 8985, Level 16, State 1, Line 2
Could not locate file 'Projects_log' in sysfiles."

Can anyone please help?
Question by:IAmTheEggman
    LVL 14

    Accepted Solution


    USE xxxxx                              
    DBCC SHRINKFILE(fileName, 20)  

    Note: xxxx - Database Name
    fileName: *.ldb file name of your database... not "Project_log".  
    FileName, you can find from properties of your database, select Transaction Log Tab in that you can find ....

    LVL 14

    Expert Comment

    by:Thandava Vallepalli
    sorry its not *.ldb file,   its *.ldf file  

    LVL 14

    Expert Comment

    by:Thandava Vallepalli
    This is not for u, but others who have same problem in future...

    LVL 8

    Expert Comment

    itsvtk seems to describe what you already did. The commands both of you used will truncate your log file ONLY if your database recovery mode is set to SIMPLE. Otherwise, any backup will only reduce the log size to the last checkpoint (MinLSN). If you want more details about this, see Books Online (BOL). Find the BACKUP LOG index entry and then select 'truncating the transaction log' topic. You can check this option in Enterprise Manager, DB > Properties > Options > Recovery Model.

    see also
    LVL 15

    Expert Comment

    Hi IAmTheEggman,

    I believe you need to run the DBCC SHRINKFILE from within the database you are shrinking, this may be the problem with not finding the file (this is as itsvtk mentioned).  The below should confirm the name you need to use with the dbcc command:

    exec sp_helpdb Projects

    Author Comment

    Many thanks itsvtk, your solution worked just fine.
    I appreciate the swift response.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Suggested Solutions

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now