Clear sql log file

You should be performing these log backups quite frequently, according to your recovery objectives. For example, if you have a business rule that states you can afford to lose no more than 15 minutes of data in the event of a disaster, you should have a job that backs up the log every 15 minutes. Here is a script that will generate timestamped file names based on the current time but you can also do this with maintenance plans etc.

Backing these up to the same machine or to a different machine that uses the same underlying disks, or a different VM that's on the same physical host does not really help you, since if the machine blows up, you've lost your database and its backups. Depending on your network infrastructure it may make more sense to backup locally and then transfer them to a different location behind the scenes; in either case, you want to get them off the primary database machine as quickly as possible.

Now, once you have regular log backups running, it should be reasonable to shrink the log file to something more reasonable than whatever it's blown up to now. Log file autogrow events are expensive, since SQL Server has to zero out the files unlike data files when instant file initialization is enabled , and user transactions have to wait while this happens.

You want to do this grow-shrink-grow-shrink routine as little as possible, and you certainly don't want to make your users pay for it. So, you need to come up with a practical size for your log file. Let's say that comes to MB, and you want any subsequent autogrowth events to be 50 MB, then you can adjust the log file size this way:. If this is a test database, and you don't care about point-in-time recovery, then you should make sure that your database is in SIMPLE recovery mode. Putting the database in SIMPLE recovery mode will make sure that SQL Server re-uses portions of the log file essentially phasing out inactive transactions instead of growing to keep a record of all transactions like FULL recovery does until you back up the log.

Next, you should make absolute sure that this log growth was truly due to an abnormal event say, an annual spring cleaning or rebuilding your biggest indexes , and not due to normal, everyday usage.

If you shrink the log file to a ridiculously small size, and SQL Server just has to grow it again to accommodate your normal activity, what did you gain?

Were you able to make use of that disk space you freed up only temporarily? If you need an immediate fix, then you can run the following:. Otherwise, set an appropriate size and growth rate. As per the example in the point-in-time recovery case, you can use the same code and logic to determine what file size is appropriate and set reasonable autogrowth parameters.

Second, if you are in FULL recovery model, this will destroy your log chain and require a new, full backup. Detach the database, delete the log file, and re-attach. I can't emphasize how dangerous this can be.

Your database may not come back up, it may come up as suspect, you may have to revert to a backup if you have one , etc. Use the "shrink database" option. Shrink the log file to 1 MB. This looks tempting because, hey, SQL Server will let me do it in certain scenarios, and look at all the space it frees! Unless your database is read only and it is, you should mark it as such using ALTER DATABASE , this will absolutely just lead to many unnecessary growth events, as the log has to accommodate current transactions regardless of the recovery model.

What is the point of freeing up that space temporarily, just so SQL Server can take it back slowly and painfully? Create a second log file. This will provide temporarily relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid.

You should deal with the problematic log file directly instead of just adding another potential problem. Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you unlike a second data file , since only one of the files can ever be used at a time.

Paul Randal also explains why multiple log files can bite you later. Instead of shrinking your log file to some small amount and letting it constantly autogrow at a small rate on its own, set it to some reasonably large size one that will accommodate the sum of your largest set of concurrent transactions and set a reasonable autogrow setting as a fallback, so that it doesn't have to grow multiple times to satisfy single transactions and so that it will be relatively rare for it to ever have to grow during normal business operations.

The former is much too small in this day and age, and the latter leads to longer and longer events every time say, your log file is MB, first growth is 50 MB, next growth is 55 MB, next growth is Please don't stop here; while much of the advice you see out there about shrinking log files is inherently bad and even potentially disastrous, there are some people who care more about data integrity than freeing up disk space.

A blog post I wrote in , when I saw a few "here's how to shrink the log file" posts spring up. A blog post Brent Ozar wrote four years ago, pointing to multiple resources, in response to a SQL Server Magazine article that should not have been published.

A blog post by Paul Randal explaining why t-log maintenance is important and why you shouldn't shrink your data files, either. Mike Walsh has a great answer covering some of these aspects too, including reasons why you might not be able to shrink your log file immediately. As I said nearly 5 years ago:. I usually open the Windows Explorer directory containing the database files, so I can immediately see the effect.

I was actually quite surprised this worked! In Full recovery mode this might not work, so you have to either back up the log first, or change to Simple recovery, then shrink the file. PS: I appreciate what some have commented regarding the dangers of this, but in my environment I didn't have any issues doing this myself especially since I always do a full backup first.

So please take into consideration what your environment is, and how this affects your backup strategy and job security before continuing. All I was doing was pointing people to a feature provided by Microsoft! If you just shrink the file you are going to lose a ton of data that may come as a life saver in case of disaster.

The transaction log contains a lot of useful data that can be read using a third-party transaction log reader it can be read manually but with extreme effort though. Here are several posts where people used data stored in the transaction log to accomplish recovery:. How to view transaction logs in SQL Server This means that TLOG is in use. In this case try executing this several times in a row or find a way to reduce database activities. I'm guessing that you are not doing log backups.

Which truncate the log. My advice is to change recovery model from full to simple. This will prevent log bloat. If you do not use the transaction logs for restores i. You only ever do full backups , you can set Recovery Mode to "Simple", and the transaction log will very shortly shrink and never fill up again. If you are using SQL 7 or , you can enable "truncate log on checkpoint" in the database options tab.

This has the same effect. This is not recomended in production environments obviously, since you will not be able to restore to a point in time. Unfold Databases and then right-click the database that you want to shrink. Turn to Tasks - Shrink , and then click Files. Generally, log file will be shrunk after you click OK now. But if you want to set it in more detailed, options below can be selected.

If this is selected, the Shrink file to value must be specified. By default, the option is cleared. Option 3: Enter the maximum percentage of free space to be left in the database file after the database has been shrunk. The value can be between 0 and Related Articles. How to rename a SQL Server database. How to determine SQL Server database transaction log usage. Understanding how SQL Server stores data in data files. Copy a SQL Server database with just the objects and no data. How to stop and start SQL Server services.

Popular Articles. Rolling up multiple rows into a single row and column for SQL Server data. How to tell what SQL Server versions you are running. Resolving could not open a connection to SQL Server errors. Ways to compare and find differences for SQL Server tables and data. Searching and finding a string value in all columns in a SQL Server table. Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips. Back To Top In the Log File Viewer , select the job for which you want to clear history, and then do one of the following:. Click Delete , and then click Delete all history in the Delete History dialog. You can delete all job history or only history that is older than a specified date. If you want to remove all job history, click Delete all history.