How do you clear the SQL Server transaction log

Question

I m not a SQL expert  and I m reminded of the fact every time I need to do something beyond the basics  I have a test database that is not large in size  but the transaction log definitely is  How do I clear out the transaction log

User · Answer

Use the DBCC ShrinkFile   logicalLogName   TRUNCATEONLY  command  If this is a test database and you are trying to save reclaim space  this will help   Remember though that TX logs do have a sort of minimum steady state size that they will grow up to  Depending upon your recovery model you may not be able to shrink the log - if in FULL and you aren t issuing TX log backups the log can t be shrunk - it will grow forever  If you don t need TX log backups  switch your recovery model to Simple   And remember  never ever under any circumstances delete the log  LDF  file  You will pretty much have instant database corruption  Cooked   Done  Lost data  If left  unrepaired  the main MDF file could become corrupt permanently   Never ever delete the transaction log - you will lose data  Part of your data is in the TX Log  regardless of recovery model     if you detach and  rename  the TX log file that effectively deletes part of your database   For those that have deleted the TX Log you may want to run a few checkdb commands and fix the corruption before you lose more data   Check out Paul Randal s blog posts on this very topic  bad advice   Also in general do not use shrinkfile on the MDF files as it can severely fragment your data  Check out his Bad Advice section for more info   Why you should not shrink your data files    Check out Paul s website - he covers these very questions  Last month he walked through many of these issues in his Myth A Day series

User · Answer

Database   right click Properties   file   add another log file with a different name and set the path the same as the old log file with a different file name   The database automatically picks up the newly created log file

User · Answer

Some of the other answers did not work for me  It was not possible to create the checkpoint while the db was online  because the transaction log was full  how ironic   However  after setting the database to emergency mode  I was able to shrink the log file   alter database  lt database name gt  set emergency  use  lt database name gt   checkpoint  checkpoint  alter database  lt database name gt  set online  dbcc shrinkfile  lt database name gt  log  200

User · Answer

Try this   USE DatabaseName  GO  DBCC SHRINKFILE  TransactionLogName  1   BACKUP LOG DatabaseName WITH TRUNCATE ONLY  DBCC SHRINKFILE  TransactionLogName  1   GO

User · Answer

DB Transaction Log Shrink to min size    Backup  Transaction log Shrink files  Transaction log Backup  Transaction log Shrink files  Transaction log   I made tests on several number of DBs  this sequence works    It usually shrinks to 2MB   OR by a script   DECLARE  DB Name nvarchar 255   DECLARE  DB LogFileName nvarchar 255   SET  DB Name     lt Database Name gt                  --Input Variable SET  DB LogFileName     lt LogFileEntryName gt            --Input Variable EXEC     USE     DB Name         BACKUP LOG     DB Name    WITH TRUNCATE ONLY      DBCC SHRINKFILE       DB LogFileName      2       BACKUP LOG     DB Name    WITH TRUNCATE ONLY      DBCC SHRINKFILE       DB LogFileName      2     GO

User · Answer

Making a log file smaller should really be reserved for scenarios where it encountered unexpected growth which you do not expect to happen again  If the log file will grow to the same size again  not very much is accomplished by shrinking it temporarily  Now  depending on the recovery goals of your database  these are the actions you should take   First  take a full backup  Never make any changes to your database without ensuring you can restore it should something go wrong   If you care about point-in-time recovery   And by point-in-time recovery  I mean you care about being able to restore to anything other than a full or differential backup    Presumably your database is in FULL recovery mode  If not  then make sure it is   ALTER DATABASE testdb SET RECOVERY FULL    Even if you are taking regular full backups  the log file will grow and grow until you perform a log backup - this is for your protection  not to needlessly eat away at your disk space  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   just don t choose any of the shrink options in maintenance plans  they re awful    DECLARE  path NVARCHAR 255    N   backup share log testdb        CONVERT CHAR 8   GETDATE    112            REPLACE CONVERT CHAR 8   GETDATE    108                trn    BACKUP LOG foo TO DISK    path WITH INIT  COMPRESSION    Note that   backup share  should be on a different machine that represents a different underlying storage device  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  This does not mean running SHRINKFILE over and over again until the log file is 1 MB - even if you are backing up the log frequently  it still needs to accommodate the sum of any concurrent transactions that can occur  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   Note that you may need to back up the log twice before a shrink is possible  thanks Robert    So  you need to come up with a practical size for your log file  Nobody here can tell you what that is without knowing a lot more about your system  but if you ve been frequently shrinking the log file and it has been growing again  a good watermark is probably 10-50  higher than the largest it s been  Let s say that comes to 200 MB  and you want any subsequent autogrowth events to be 50 MB  then you can adjust the log file size this way   USE  master   GO ALTER DATABASE Test1    MODIFY FILE    NAME   yourdb log  SIZE   200MB  FILEGROWTH   50MB   GO   Note that if the log file is currently   200 MB  you may need to run this first   USE yourdb  GO DBCC SHRINKFILE yourdb log  200   GO   If you don t care about point-in-time recovery  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   ALTER DATABASE testdb SET RECOVERY SIMPLE    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   CHECKPOINT events will help control the log and make sure that it doesn t need to grow unless you generate a lot of t-log activity between CHECKPOINTs   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   USE yourdb  GO CHECKPOINT  GO CHECKPOINT  -- run twice to ensure file wrap-around GO DBCC SHRINKFILE yourdb log  200   -- unit is set in MBs GO   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    Some things you don t want to do   Back up the log with TRUNCATE ONLY option and then SHRINKFILE  For one  this TRUNCATE ONLY option has been deprecated and is no longer available in current versions of SQL Server  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  etc  Use the  shrink database  option  DBCC SHRINKDATABASE and the maintenance plan option to do the same are bad ideas  especially if you really only need to resolve a log problem issue  Target the file you want to adjust and adjust it independently  using DBCC SHRINKFILE or ALTER DATABASE     MODIFY FILE  examples above   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    Be proactive  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 worst possible settings here are 1 MB growth or 10  growth  Funny enough  these are the defaults for SQL Server  which I ve complained about and asked for changes to no avail  - 1 MB for data files  and 10  for log files  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 500 MB  first growth is 50 MB  next growth is 55 MB  next growth is 60 5 MB  etc  etc  - and on slow I O  believe me  you will really notice this curve    Further reading  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 2009  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

User · Answer

DISCLAIMER  Please read comments below carefully  and I assume you ve already read the accepted answer  As I said nearly 5 years ago      if anyone has any comments to add for situations when this is NOT an   adequate or optimal solution then please comment below      Right click on the database name  Select Tasks   Shrink   Database Then click OK    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  Normally I ve used DBCC before  but I just tried that and it didn t shrink anything  so I tried the GUI  2005  and it worked great - freeing up 17 nbsp GB in 10 seconds  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    thanks  onupdatecascade for this   --  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

User · Answer

To my experience on most SQL Servers there is no backup of the transaction log  Full backups or differential backups are common practice  but transaction log backups are really seldom  So the transaction log file grows forever  until the disk is full   In this case the recovery model should be set to  simple   Don t forget to modify the system databases  model  and  tempdb   too   A backup of the database  tempdb  makes no sense  so the recovery model of this db should always be  simple

User · Answer

First check the database recovery model  By default  SQL Server Express Edition creates a database for the simple recovery model  if I am not mistaken    Backup log DatabaseName With Truncate Only   DBCC ShrinkFile yourLogical LogFileName  50    SP helpfile will give you the logical log file name   Refer to   Recover from a full transaction log in a SQL Server database  If your database is in Full Recovery Model and if you are not taking TL backup  then change it to SIMPLE

User · Answer

It happened with me where the database log file was of 28 GBs    What can you do to reduce this   Actually  log files are those file data which the SQL server keeps when an transaction has taken place  For a transaction to process SQL server allocates pages for the same  But after the completion of the transaction  these are not released suddenly hoping that there may be a transaction coming like the same one  This holds up the space     Step 1   First Run this command in the database query explored  checkpoint  Step 2   Right click on the database  Task  Back up Select back up type as Transaction Log Add a destination address  and file name to keep the backup data   bak   Repeat this step again and at this time give another file name     Step 3  Now go to the database  Right-click on the database   Tasks  Shrinks  Files  Choose File type as Log Shrink action as release unused space    Step 4   Check your log file  normally in SQL 2014 this can be found at   C  Program Files Microsoft SQL Server MSSQL12 MSSQL2014EXPRESS MSSQL DATA  In my case  its reduced from 28 GB to 1 MB

User · Answer

Take a backup of the MDB file  Stop SQL services Rename the log file Start the service    The system will create a new log file    Delete or move the renamed log file

User · Answer

Here is a simple and very inelegant  amp  potentially dangerous  way     Backup DB Detach DB Rename Log file Attach DB New log file will be recreated Delete Renamed Log file    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

User · Answer

Below is a script to shrink the transaction log  but I   d definitely recommend backing up the transaction log before shrinking it   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    The transaction log is also a must when it comes to point in time recovery  so don   t just throw it away  but make sure you back it up beforehand   Here are several posts where people used data stored in the transaction log to accomplish recovery    How to view transaction logs in SQL Server 2008 Read the log file    LDF  in SQL Server 2008    nbsp   USE DATABASE NAME  GO  ALTER DATABASE DATABASE NAME SET RECOVERY SIMPLE  GO --First parameter is log file name and second is size in MB DBCC SHRINKFILE  DATABASE NAME Log  1    ALTER DATABASE DATABASE NAME SET RECOVERY FULL  GO   You may get an error that looks like this when the executing commands above        Cannot shrink log file  log file name  because the logical       log file located at the end of the file is in use      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

User · Answer

The SQL Server transaction log needs to be properly maintained in order to prevent its unwanted growth  This means running transaction log backups often enough  By not doing that  you risk the transaction log to become full and start to grow  Besides the answers for this question I recommend reading and understanding the transaction log common myths  These readings may help understanding the transaction log and deciding what techniques to use to  quot clear quot  it  From 10 most important SQL Server transaction log myths   Myth  My SQL Server is too busy  I don   t want to make SQL Server transaction log backups One of the biggest performance intensive operations in SQL Server is an auto-grow event of the online transaction log file  By not making transaction log backups often enough  the online transaction log will become full and will have to grow  The default growth size is 10   The busier the database is  the quicker the online transaction log will grow if transaction log backups are not created Creating a SQL Server transaction log backup doesn   t block the online transaction log  but an auto-growth event does  It can block all activity in the online transaction log  From Transaction log myths    Myth  Regular log shrinking is a good maintenance practice  FALSE  Log growth is very expensive because the new chunk must be zeroed-out  All write activity stops on that database until zeroing is finished  and if your disk write is slow or autogrowth size is big  that pause can be huge and users will notice  That   s one reason why you want to avoid growth  If you shrink the log  it will grow again and you are just wasting disk operation on needless shrink-and-grow-again game

User · Answer

-- DON T FORGET TO BACKUP THE DB  D  Check  here  1      USE AdventureWorks2008R2  GO -- Truncate the log by changing the database recovery model to SIMPLE  ALTER DATABASE AdventureWorks2008R2 SET RECOVERY SIMPLE  GO -- Shrink the truncated log file to 1 MB  DBCC SHRINKFILE  AdventureWorks2008R2 Log  1   GO -- Reset the database recovery model  ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL  GO   From  DBCC SHRINKFILE  Transact-SQL   You may want to backup first

User · Answer

Slightly updated answer  for MSSQL 2017  and using the SQL server management studio  I went mostly from these instructions https   www sqlshack com sql-server-transaction-log-backup-truncate-and-shrink-operations   I had a recent db backup  so I backed up the transaction log   Then I backed it up again for good measure  Finally I shrank the log file  and went from 20G to 7MB  much more in line with the size of my data  I don t think the transaction logs had ever been backed up since this was installed 2 years ago    so putting that task on the housekeeping calendar

User · Answer

Most answers here so far are assuming you do not actually need the Transaction Log file  however if your database is using the FULL recovery model  and you want to keep your backups in case you need to restore the database  then do not truncate or delete the log file the way many of these answers suggest   Eliminating the log file  through truncating it  discarding it  erasing it  etc  will break your backup chain  and will prevent you from restoring to any point in time since your last full  differential  or transaction log backup  until the next full or differential backup is made   From the Microsoft article onBACKUP     We recommend that you never use NO LOG or TRUNCATE ONLY to manually   truncate the transaction log  because this breaks the log chain  Until   the next full or differential database backup  the database is not   protected from media failure  Use manual log truncation in only very   special circumstances  and create backups of the data immediately    To avoid that  backup your log file to disk before shrinking it  The syntax would look something like this   BACKUP LOG MyDatabaseName  TO DISK  C  DatabaseBackups MyDatabaseName backup 2013 01 31 095212 8797154 trn   DBCC SHRINKFILE  N MyDatabaseName Log   200

User · Answer

This technique that John recommends is not recommended as there is no guarantee that the database will attach without the log file  Change the database from full to simple  force a checkpoint and wait a few minutes  The SQL Server will clear the log  which you can then shrink using DBCC SHRINKFILE

User · Answer

Backup DB Detach DB Rename Log file Attach DB  while attaching remove renamed  ldf  log file  Select it and remove by pressing Remove button  New log file will be recreated Delete Renamed Log file    This will work but it is suggested to take backup of your database first

User · Answer

If you do not use the transaction logs for restores  i e  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 2000  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

User · Answer

To Truncate the log file    Backup the database Detach the database  either by using Enterprise Manager or by executing   Sp DetachDB  DBName  Delete the transaction log file   or rename the file  just in case  Re-attach the database again using  Sp AttachDB  DBName  When the database is attached  a new transaction log file is created    To Shrink the log file    Backup log  DBName  with No Log Shrink the database by either   Using Enterprise manager  - Right click on the database  All tasks  Shrink database  Files  Select log file  OK   Using T-SQL  - Dbcc Shrinkfile   Log Logical Name     You can find the logical name of the log file by running sp helpdb or by looking in the properties of the database in Enterprise Manager

[sql-server] How do you clear the SQL Server transaction log?

Examples related to sql-server

Examples related to transaction-log