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 ?
Eprs_AdminSystem ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SuhaibCommented:
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 FavorLinux/LXD/WordPress/Hosting SavantCommented:
If you're out of disk space, likely best to connect an external dock + drive to follow the procedure Suhaib provided above.
Eprs_AdminSystem ArchitectAuthor Commented:
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 ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

:-(
Scott PletcherSenior DBACommented:
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.
Doug BishopDatabase DeveloperCommented:
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

Eprs_AdminSystem ArchitectAuthor Commented:
the recovery model is FULL
Eprs_AdminSystem ArchitectAuthor Commented:
ok, lets say I shrink the LDF file, I just loose data to restore to, right ?
Scott PletcherSenior DBACommented:
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.
David ToddSenior Database AdministratorCommented:
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
Scott PletcherSenior DBACommented:
Sorry, I did leave one step out of my earlier list of actions:

1) set the db recovery to simple [ALTER DATABASE database_name SET RECOVERY SIMPLE];
2) shrink the log file [USE database_name; DBCC SHRINKFILE (2, <size_in_mb_you_want_the_log_file_to_be>)];
2B) put the db back to full recovery, if you want it to be in full [ALTER DATABASE database_name SET RECOVERY FULL];
3) as soon as possible, do a full db backup.

I have a different priority in this type of situation.  My priority is to get a prod db functioning again as quickly as possible.  Then I'll go after the underlying causes.

As to the log backup, keep in mind that it could take quite a while since that file filled up a drive, and the db can't do any data mods without a log file available.  Besides, a log backup file is useful only if you need / actually intend to use it to recover to a point in between full or diff backups.  Would you really be willing to take the time to apply such a large log backup to a restore of your db?  If not, there's no point wasting time taking a log backup(s) you'll never apply anyway.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior Database AdministratorCommented:
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 PletcherSenior DBACommented:
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.
Eprs_AdminSystem ArchitectAuthor Commented:
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.
Eprs_AdminSystem ArchitectAuthor Commented:
Hi can you also show me how to add another log on another drive ?
David ToddSenior Database AdministratorCommented:
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
David ToddSenior Database AdministratorCommented:
Or use SSMS. In the properties page, there is a tab for files and you can add a file there.
Scott PletcherSenior DBACommented:
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!)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage Software

From novice to tech pro — start learning today.