LDF Log file is 30.6 GB - Initial Size somehow set to 30 GB and it can't be changed

Posted on 2007-07-23
Last Modified: 2010-05-18
When one of my sql databases was set up, the default initial size got set to 30GB (!). I have tried shrinking the log file, but it won't shrink to below about 30GB. I have tried changing the default initial size to 50MB, but it reverts back to 30 GB when I take any actions. I have tried backing up the database and restoring it from the backup...and it re-incorporates the 30GB file.

I have tried the following SQL query but it gives the following error. Note that I tried limiting it to a single user.


USE [dbname]

SELECT @FILEID = file_id FROM sys.database_files WHERE type = 1



Cannot shrink log file 2 (dbname_log) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I'm stuck. I've had one SQL vendor try to help, but he's stuck. Help!
Question by:djlurch
    LVL 7

    Accepted Solution

    I'm not a DBA, however, I have done something like this long time ago. Try adding another log file and removing the existing one. An alternative is to try to truncate the logs (check google - I don't remember the syntax). This might free "empty" space, and should be run before attempting to shrink the logs.
    LVL 35

    Assisted Solution

    by:David Todd

    Before you try the below, check that Model doesn't have a 30GB log file.

    This is kind of drastic, but you can detach the database, rename the log data file, then reattach the database specifying only the databasename and the datafile (mdf). SQL should comment that it can't find the log file and recreate it for you.

    I imagine that you will end up with a log file of a few meg at most. On my SQL 2005, modellog.ldf is 1024K.

    Of course on a production system, do make sure that you have a full backup first.

    LVL 1

    Author Comment

    I ran the code in this thread:

    DBCC SHRINKFILE(MyDatabase_Log, 1)
    DBCC SHRINKFILE(MyDatabase_Log, 1)

    It worked like magic! It seemed to change the initial size to 1 MB. I cannot change it from the 1 MB size, but my issue seems to be resolved for the most part.

    LVL 7

    Expert Comment

    Let it autogrow, and it will grow. You can limit its maximal size.
    LVL 1

    Author Comment

    Thank you to both of you. dtodd gets a few points since his answer is probably another solution. Exaton helped me find the right search terms.
    LVL 7

    Expert Comment

    With pleasure :-)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Multiply and round down 8 36
    SQL Server DatePart HOUR 6 27
    Sql query 12 56
    SQL Select - Finding chars in a column 2 39
    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    731 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