SQL LDF logfile is full...

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

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

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 ?

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

:-(

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.

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

Select allOpen in new window

the recovery model is FULL

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

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.

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

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

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.

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.

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

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

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

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!)