<div>
Hello,<br />
according to this, you can backup the database and shrink the ldf file using the management studio.<br />
<a href="http://support.pitneybowes.com/VFP05_KnowledgeWithSidebarHowTo?id=kA180000000CobmCAC" rel="ugc">http://support.pitneybowes<wbr />.com/VFP05<wbr />_Knowledge<wbr />WithSideba<wbr />rHowTo?id=<wbr />kA18000000<wbr />0CobmCAC</a>
</div>


Fractional CTO

David Favor

Infrastructure admin

Ibrahim Kasabri

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


<div>
hi thanks.<br />
<br />
backup was successful.<br />
But when I use shrink file log, the gets bloody full again and all the rest of the space on my disk is full.<br />
Why ?
</div>


<div>
before I started with shrink, the LDF was filled with 90%<br />
After the start of shrink, the LDF was filled with 99%<br />
<br />
:-(
</div>


<div>
It sounds as if the db is in FULL or BULK-LOGGED recovery mode. &nbsp;If it is, you'll have to either:<br />
1) Backup the log file<br />
OR<br />
2) Put the db in SIMPLE recovery model.<br />
<br />
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):<br />
DBCC SHRINKFILE(2, &lt;desired_size_in_mb&gt;)<br />
<br />
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.
</div>


<div>
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 <a href="https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-2014" rel="ugc">this article</a> from Microsoft. <br />
<br />
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.<br />

<pre><code class="code-1 nolinks" id="code-20-42770492-1"><span>ALTER DATABASE myDB</span>
<span>SET RECOVERY SIMPLE</span>
<span>GO</span>
<span>DBCC SHRINKFILE (myDB_log, 1)</span>
<span>GO</span>
<span>ALTER DATABASE myDB</span>
<span>SET RECOVERY FULL</span>
<span>GO</span>
</code></pre>
</div>


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


<div>
Yes. &nbsp;The idea would be to:<br />
<br />
1) set the db recovery to simple;<br />
2) shrink the log file;<br />
3) as soon as possible, do a full db backup.<br />
<br />
Since you can no longer use the log file to recover the db.
</div>


<div>
Hi,<br />
<br />
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?<br />
<br />
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.<br />
<br />
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<br />
<br />
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.<br />
<br />
HTH<br />
&nbsp; David
</div>


System Architect

Eprs_Admin

<div>
Hi Scott <br />
<br />
Completely agree with your last statement - log backups you'll never ever use are a waste of time and space.<br />
<br />
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.<br />
<br />
Regards<br />
&nbsp; David
</div>


<div>
The db is <i>down</i>. &nbsp;That's what critical to me.<br />
<br />
Backing up the log will take a lot of time before the db could then come back up.<br />
Backing up the db itself will do <i>nothing</i> to bring it back up.<br />
<br />
I want the db to come back up as rapidly as possible.<br />
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.<br />
<br />
But without that option, I'd &quot;zap&quot; the db to simple and clear the log just to get everything flowing again, then worry about db backups.<br />
<br />
If I were going to leave the db in simple mode, I'd do a full back.<br />
<br />
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. &nbsp;When you switch to full, you have to do a full backup anyway, so there's no need to do one beforehand.
</div>


<div>
ok thanks, tonight I will change it.<br />
maybe I just add another LOG or I just shrink the old one.<br />
<br />
I let you know tomorrow.
</div>


<div>
Hi can you also show me how to add another log on another drive ?
</div>


<div>
Hi,<br />
<br />
alter database<br />
<br />
<a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-2017" rel="ugc">https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-2017</a><br />
<br />
Which simplifies to something like this:<br />
alter database mytroublesomedatabase add log file ( name = 'log2', filename = 'd:\newlogfile.ldf', size = 1GB, filegrowth = 64MB ); <br />
<br />
HTH<br />
&nbsp; David
</div>


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


<div>
Steps, in order:<br />
<br />
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:<br />
EXEC sp_helpfile<br />
File#2 is the log file. &nbsp;The first column, &quot;name&quot;, is the logical file name; column &quot;size&quot; is the total current size.<br />
<br />
Now create the command BUT DON'T RUN IT YET:<br />
ALTER DATABASE &lt;database_name&gt; <br />
&nbsp; &nbsp; MODIFY FILE ( NAME = &lt;logical_file_name_as_show<wbr />n_by_helpf<wbr />ile_comman<wbr />d&gt;, MAXSIZE = ....KB ) --where ... is the total kb shown by the helpfile command<br />
<br />
2) Run the command to add a new log file. &nbsp;For the new log name, add a &quot;2&quot; to the first log name:<br />
<br />
--initially allocate 1/2 the total log space, say 512MB, ...<br />
ALTER DATABASE &lt;database_name&gt;<br />
&nbsp; &nbsp; ADD LOG FILE ( NAME = [&lt;logical_file_name_as_sho<wbr />wn_by_help<wbr />file_comma<wbr />nd<b>2</b>&gt;], SIZE = 512MB, FILEGROWTH = 50MB );<br />
--... then allocate the other half of the space. &nbsp;Doing it in two steps keeps the logical log files SQL uses from being too large<br />
ALTER DATABASE &lt;database_name&gt; MODIFY FILE ( NAME = [&lt;database_name_log2&gt;], SIZE = 1024MB )<br />
<br />
3) Run the command you created in step 1.<br />
<br />
You've now &quot;told&quot; SQL that the first log file cannot grow. &nbsp;When SQL &quot;sees&quot; the file is full, it will start writing to the 2nd log file. &nbsp;As soon as that happens, if in SIMPLE mode, you can shrink the first log file with no interference at all from SQL.<br />
<br />
Later you can limit the 2nd log file in the same way to force SQL to switch back to the 1st log file. &nbsp;If the problem is cleared up, you can then completely the 2nd log file:<br />
ALTER DATABASE &lt;database_name&gt; REMOVE FILE &nbsp;[&lt;logical_file_name_as_sho<wbr />wn_by_help<wbr />file_comma<wbr />nd<b>2</b>&gt;]<br />
<br />
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!)
</div>


<div>
<div class="content wysiwyg-content">
Hello,<br />
<br />
I have major problems with my SQL database.<br />
The LDF file is full.<br />
All the backup tools are not working for some reason.<br />
I have no space anymore on disk.<br />
<br />
Can you help me to shrink the file or truncate the logs manually ?
</div>
</div>


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 ?

SQL LDF logfile is full...

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

The term "Backup" means the methods and processes involved to copy computer data (system data as well as application data) to media other than the ones where the data originally live (disk, tape, optical, cloud). "Restore" in turn means the methods and processes involved in data recovery, i. e., bringing back copied computer data to their original location. Backup/Restore primarily serves as a means of protection against data loss, be it due to disaster, corruption or sabotage. It can also be used for recovering data from an earlier point in time and even for cloning machines or applications. There is a wide variety of backup/restore software available, from expensive commercial products to free or open source tools.

Storage Software

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.