Share via

Transaction log truncation

Padmanabhan, Venkatesh 246 Reputation points
2021-01-19T11:34:05.047+00:00

Hi.
I have an existing application which uses DB2. In the existing application, there is a process purging of logs in DB2 which is done using command PRUNE LOGS passed with necessary parameters.

I am now migrating this application to SQL. I am trying to do the similar approach for transaction log truncation in SQL. what are the ways which can be used to truncate log ?

Database performance is a criteria, so looking for methods which can be used which does not tamper with the performance of the database .

Thanks

SQL Server | Other

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shashank Singh 6,251 Reputation points
    2021-01-19T14:34:10.06+00:00

    We dont have command similar to PRUNE LOG in SQL Server AFAIK, you can ofcourse delete whole log file if you like but that is whole different discussion. As I read this command would delete contents while SQL Server after truncating the logs would overwrite its content with new information. You may consider this as similar thing but I just put it out so that you can see the difference.

    IN full recovery you would have to take transaction log backup to truncate logs

    In simple recovery model when log file grows 70% of its size it will automatically try to truncate the logs or when checkpoint is fired.

    In both cases above a long running transaction can block the truncation if it needs portion of the log.

    Read the-transaction-log-sql-server

    1 person found this answer helpful.
    0 comments
  2. tibor_karaszi@hotmail.com 4,316 Reputation points
    2021-01-19T13:21:49.837+00:00

    In SQL Server, you manage the transaction log by first decide whether you will use it for backu purposes:

    If not, set the database to simple recovery and the physical file will grow to the size it need to. SQL Server will empty the log by itself.

    If you do, you typically use full recovery model and it is your job to empty the log. You do this by performing transaction log backups. Do regular log backups and let the log file grow to whatever file it need to have. If you ran in full mode and didn't perform log backup, then nothing emptied the log and you might well have a "too large" log file which you can shrink using DBCC SHRINKFILE.

    See https://karaszi.com/large-transaction-log-file

    1 person found this answer helpful.
    0 comments
  3. Cris Zhan-MSFT 6,676 Reputation points
    2021-01-20T05:49:47.42+00:00

    Hi,

    In SQL Server, the transaction log records all transactions and the modifications made to the database by each transaction. SQL Server will log all changes for future resubmission or rollback. For log records that no longer need to be saved in the log file(such as transactions that have been committed or backup), SQL Server will perform a truncation action at each checkpoint, marking the space occupied by these records as reusable to free up space. The frequency of SQL Server checkpoints depends on the server option "Recovery Interval" and the database setting Target Recovery Time.

    The transaction Log truncation does not change the physical size of the log file, which requires a shrink operation.

    More details please refer to following documents.
    Manage the size of the transaction log file

    Database Checkpoints (SQL Server)

    0 comments
  4. Olaf Helper 47,616 Reputation points
    2021-01-19T12:07:01.527+00:00

    does not tamper with the performance of the database .

    Then don't truncate (shrink) the log file. If the processes/transactions requires the amount of space, then size the log file well and keep it as it is.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.