By admin MS Sql Server 0 Comments Table of Contents Now let’s understand why does this The transaction log for database is full error get occurred and how to deal with it. Increase the Transaction Log file size You May Also Like Microsoft is a widely used database in windows server, also its be default SQL server with a Plesk control panel which is used on windows base server in hosting industries. Every database has its own property and limitations set when it gets created, many times it happens that these resources get exhausted after a certain period of time. And one of the commonly reported issues about this is “The transaction log for database ‘xxx’ is full”. When you reach your limitation of transaction log size you get this issue Now let’s understand why does this The transaction log for database is full error get occurred and how to deal with it. What is a transaction log file, it’s simply the record of the activities performed on the database and a complete track recode of it, It also uses in database recovery so you know what’s the importance of it, a Transaction log file (.IDF) keeps on increasing by the period of time as the transactions keep growing and in most case, if the database has any unidentified transaction its cause the log fie filled rapidly. In this post, we will be going to show you step by step actions to overcome “The transaction log for database is full” issue. There is two way of solving this issue. Shrink the Transaction Log Increase the Transaction Log file size Shrinking the SQL server shrink log file Many of us know about the shrinking option now let’s see how we can shrink the transaction log file. We can easily perform this action by using the SQL server management studio for shrinking the transaction file. Step 1 : Login into the SQL server management studio with “SA” user Step 2: Expand the databases > and select the database name which is having transaction log size full issue Step 3: Right Click on database name and go to Task > Shrink > Files Step 4: Here, in this step, you have to select “File Type” as “Log” and “File Name” of the transaction log file. And then, you will be seeing a check box in name of release unused space. Click on the check box and this enables us to reduce the transaction log files space but it doesn’t delete any files to reduce the space. Step 5: You can find another check box option called “reorganize files before unused space”. When you select this option it will allow you to enter the value for shrinking the logs. If you use this option then this is permanent so by default, your process will be cleared. by doing this you mean to reduce the rows and to relocate on unallocated pages. Step 6: Here, you can choose the percentage left out for database shrinkage. The percentage count can be changed between 0 and 99. But this step is enabled only when you select 2nd option which is “reorganize files before releasing unused space”. Step 7: The third option you can go with is an empty file by migrating the data to other files in the same file group. From the same group, it moves the files to a different location after then, the open space file can be deleted. Increase the Transaction Log file size Another way to deal with this issue is to increase the actual size of the Log file to do so please follow the below steps Step 1 : Login into the SQL server management studio with “SA” user Step 2: Expand the databases > and select the database name which is transaction log size issue Step 3: Right click on the database and click on “Properties” Step 4: Go to the “Files” section from the left-hand menu list as shown below and update the LOG size on the right-Hand Side under the Size(MB) section as shown below. Both ways are suitable to tackle this issue and can be used to fix it. Alternatives for responding to a full transaction log include: Backing up the log. Freeing disk space so that the log can automatically grow. Moving the log file to a disk drive with sufficient space. Increasing the size of a log file. Adding a log file on a different disk. Completing or killing a long-running transaction. Previous Post Newer Post
