Avatar of Eprs_Admin
Eprs_AdminFlag for Austria asked on

SQL LDF logfile is full...

Hello,

I have major problems with my SQL database.
The LDF file is full.
All the backup tools are not working for some reason.
I have no space anymore on disk.

Can you help me to shrink the file or truncate the logs manually ?
DatabasesStorage SoftwareSQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Suhaib

Hello,
according to this, you can backup the database and shrink the ldf file using the management studio.
http://support.pitneybowes.com/VFP05_KnowledgeWithSidebarHowTo?id=kA180000000CobmCAC
David Favor

If you're out of disk space, likely best to connect an external dock + drive to follow the procedure Suhaib provided above.
ASKER
Eprs_Admin

hi thanks.

backup was successful.
But when I use shrink file log, the gets bloody full again and all the rest of the space on my disk is full.
Why ?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Eprs_Admin

before I started with shrink, the LDF was filled with 90%
After the start of shrink, the LDF was filled with 99%

:-(
Scott Pletcher

It sounds as if the db is in FULL or BULK-LOGGED recovery mode.  If it is, you'll have to either:
1) Backup the log file
OR
2) Put the db in SIMPLE recovery model.

Then you can shrink the log file to the size you want it to be, using DBCC SHRINKFILE (do NOT use the gui for this):
DBCC SHRINKFILE(2, <desired_size_in_mb>)

If you have an additional drive available that has space on it, you could also add another log file to the db, and SQL will start writing to that new log file.
D B

What is the recovery model of the database? How often do you run backups (full and/or log)? The log will continue to grow and grow and grow until a log backup is done. See this article from Microsoft.

If you are in FULL or BULK recovery mode, and really need to shrink the log without respect to the data in it (e.g. you might lose data if you experience a crash) you can execute the following, changing the database name.
ALTER DATABASE myDB
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (myDB_log, 1)
GO
ALTER DATABASE myDB
SET RECOVERY FULL
GO

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Eprs_Admin

the recovery model is FULL
ASKER
Eprs_Admin

ok, lets say I shrink the LDF file, I just loose data to restore to, right ?
Scott Pletcher

Yes.  The idea would be to:

1) set the db recovery to simple;
2) shrink the log file;
3) as soon as possible, do a full db backup.

Since you can no longer use the log file to recover the db.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
David Todd

Hi,

For me the quesiton is WHAT is filling the log file? Is there an import that is overly large and keeps retrying, so as soon as the log file is empty it fills again?

And if in full recovery transaction log backups are a must - if your backup has failed then this is the reason your log file is filling up.

Safest course of action is to get your backups working, then after a transaction log backup or two (you'll have a back log of transactions waiting to get through of course) then you can reduce the size of your transaction log file - you don't need simple recovery mode here, just a little paitence and several transaction log backups

The question as to which way around to do things is determined by your business owner/users - can they do most of their work except enter new data/update data? Then follow the steps I've outlined. If the system is conceieved to be dead in the water, and because many data inputs are automated and will automatically retry, and because of that the loss of transaction log backups isn't a biggy, follow Scotts plan to get workign as soon as possible.

HTH
  David
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
David Todd

Hi Scott

Completely agree with your last statement - log backups you'll never ever use are a waste of time and space.

But as the backups have issues - that may have caused this situation - wouldn't the first step be to verify that it is possible to take a full backup, otherwise could be left hanging on step 3 for an undue amount of time.

Regards
  David
Scott Pletcher

The db is down.  That's what critical to me.

Backing up the log will take a lot of time before the db could then come back up.
Backing up the db itself will do nothing to bring it back up.

I want the db to come back up as rapidly as possible.
Typically I'll add another log file on a different drive -- that allows the db to work while I have time to do more checking.

But without that option, I'd "zap" the db to simple and clear the log just to get everything flowing again, then worry about db backups.

If I were going to leave the db in simple mode, I'd do a full back.

If I'm going back to full, I'd either do a differential backup or no backup while in simple, change to full, then do a full backup.  When you switch to full, you have to do a full backup anyway, so there's no need to do one beforehand.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Eprs_Admin

ok thanks, tonight I will change it.
maybe I just add another LOG or I just shrink the old one.

I let you know tomorrow.
ASKER
Eprs_Admin

Hi can you also show me how to add another log on another drive ?
David Todd

Hi,

alter database

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-2017

Which simplifies to something like this:
alter database mytroublesomedatabase add log file ( name = 'log2', filename = 'd:\newlogfile.ldf', size = 1GB, filegrowth = 64MB );

HTH
  David
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
David Todd

Or use SSMS. In the properties page, there is a tab for files and you can add a file there.
Scott Pletcher

Steps, in order:

1) LIst the current log file name and size and create a command to limit the log file size BUT DON'T RUN IT YET:
EXEC sp_helpfile
File#2 is the log file.  The first column, "name", is the logical file name; column "size" is the total current size.

Now create the command BUT DON'T RUN IT YET:
ALTER DATABASE <database_name>
    MODIFY FILE ( NAME = <logical_file_name_as_shown_by_helpfile_command>, MAXSIZE = ....KB ) --where ... is the total kb shown by the helpfile command

2) Run the command to add a new log file.  For the new log name, add a "2" to the first log name:

--initially allocate 1/2 the total log space, say 512MB, ...
ALTER DATABASE <database_name>
    ADD LOG FILE ( NAME = [<logical_file_name_as_shown_by_helpfile_command2>], SIZE = 512MB, FILEGROWTH = 50MB );
--... then allocate the other half of the space.  Doing it in two steps keeps the logical log files SQL uses from being too large
ALTER DATABASE <database_name> MODIFY FILE ( NAME = [<database_name_log2>], SIZE = 1024MB )

3) Run the command you created in step 1.

You've now "told" SQL that the first log file cannot grow.  When SQL "sees" the file is full, it will start writing to the 2nd log file.  As soon as that happens, if in SIMPLE mode, you can shrink the first log file with no interference at all from SQL.

Later you can limit the 2nd log file in the same way to force SQL to switch back to the 1st log file.  If the problem is cleared up, you can then completely the 2nd log file:
ALTER DATABASE <database_name> REMOVE FILE  [<logical_file_name_as_shown_by_helpfile_command2>]

This seems complicated, but it'll make sense once you done it once or twice, and with scripts it will be easy to do in the future, even if it's been awhile since you've done it (which hopefully it will be, you don't want full log files to happen a lot!)