[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2925
  • Last Modified:

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

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.

SQL:

USE [dbname]
DECLARE @FILEID int

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

DBCC SHRINKFILE(@FILEID)

ERROR:

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!
0
djlurch
Asked:
djlurch
  • 3
  • 2
2 Solutions
 
ezatonCommented:
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.
0
 
David ToddSenior DBACommented:
Hi,

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.

Regards
  David
0
 
djlurchAuthor Commented:
I ran the code in this thread:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22392154.html?sfQueryTermInfo=1+file+log+truncat

DBCC SHRINKFILE(MyDatabase_Log, 1)
Go
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
Go
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.

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
ezatonCommented:
Let it autogrow, and it will grow. You can limit its maximal size.
0
 
djlurchAuthor Commented:
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.
0
 
ezatonCommented:
With pleasure :-)
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now