SQL 2000 Transaction Log and "DBCC SHRINKFILE"

Posted on 2008-11-17
Last Modified: 2012-05-05

I've got a problem with an SQL Server in that the transaction log has grown too large and filled the disk.

As such, I performed a backup of the transaction logs, and then tried to use the DBCC SHRINKFILE T-SQL command to shrink the file.

However, when I run the command it immidiately returns "The command(s) completed successfully" and nothing happens to the file.

I tried to run it using SQL Server Management Studio 2005 from another machine in the domain, and the graphical interface here confirms that the log file is 97% free space, but again when I run it it says "Executing" for a few seconds, and completes without error, again without changing the size of the log file.

There is nothing in the EventLog that suggests an error either.

This is the T-SQL command I ran (though I have tried several other permutations): -

What am I doing wrong???

Thanks in advance.
Question by:Safeserve
    LVL 7

    Accepted Solution

    run CHECKPOINT stetement before shrinking.
    then run DBCC SHRINKFILE (N'pacs_oltp_log1' , 1).

    but these measures will not effect when you initially created too large logfile. In that case you should move log to new file and delete old file.

    also, I can advice to change recovery model from full to simple to decrease Logfile growth.


    Author Closing Comment

    Hi, thanks for the help.

    No luck with CHECKPOINT either, but once I changed it to simple it shrank correctly.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    728 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

    18 Experts available now in Live!

    Get 1:1 Help Now