Steps to shrink your MSSQL database log file
Transaction log file (.IDF) keeps on increasing at all instances. When it exceeds after certain limit then you have to shrink the transaction log file like the database. There is a common question why the log file transaction keeps on increasing? We have so many reasons behind the transaction log file growth such as no proper log backup takes place, recovery model configuration incorrect usage and transactions runs without time limit.
If you shrink the log file transaction then you can use it up for anything. If you use SIMPLE recovery model then log shrinkage will result firstly. Before starting up the shrinkage process, kindly ensure the database is online or not and then check VLF (virtual log file).
Let us see exact cause of transaction log file:
Whenever you modify the database or transaction takes place it gives an update to database. For each and every transaction it results in database modification and so, the maintenance of log file transaction too takes place simultaneously logs will increase its growth. If the database has any anonymous activity then the transaction log file increases vastly.
Many users would have known about shrinking the database size. Moving the space from end of the file to any unoccupied space which is very near to file front-end then it is called as shrinking process. The same is used for transaction log file shrink.
After shrinking the log file, you can reuse it for allocation or file system return process takes place.
Here too, we are using SQL server management studio for shrinking the transaction log file:
Step 1: Use object explorer to get connected with SQL server management studio and try to expand the instance.
Step 2: Don’t hesitate to expand the database and search the database where you have to shrink the log file transaction.
Step 3: Select the task choice and you will get opened with another few choices added in task such as shrink something.. Click on the shrink and then select the files.
Step 4: Here, in this step you have selected the file name and file type. And then, you will be seeing a check box in name of release unused space. Click on the check box and this enables to reduce the transaction log files space but it doesn’t delete any files to reduce the space.
Step 5: You have another check box option called reorganize files before unused space. When you select this option, then you have to enter the value for shrinking specification. This option is permanent so by default your process will be cleared. This process will help the rows to relocate on unallocated pages.
Step 6: In this step, you can choose the percentage left out in database shrinkage. The percentage count is varied between 0 and 99. But this step is visible only when you choose reorganize files before releasing unused space check box is choose.
Step 7:You have another third option check box by listing empty file by migrating the data to other files in the same file group. From the same group, it moves the files to another location after then, the open space file can be deleted.
Note: This process same for EMPTYFILE for DBCC SHRINKFILE
Step 8: At last select ok.
Transaction log file (.IDF) keeps on increasing at all instances. When it exceeds after certain limit then you have to shrink the transaction log file like the database. There is a common question why the log file transaction keeps on increasing? We have so many reasons behind the transaction log file growth such as no proper log backup takes place, recovery model configuration incorrect usage and transactions runs without time limit.
If you shrink the log file transaction then you can use it up for anything. If you use SIMPLE recovery model then log shrinkage will result firstly. Before starting up the shrinkage process, kindly ensure the database is online or not and then check VLF (virtual log file).
Let us see exact cause of transaction log file:
Whenever you modify the database or transaction takes place it gives an update to database. For each and every transaction it results in database modification and so, the maintenance of log file transaction too takes place simultaneously logs will increase its growth. If the database has any anonymous activity then the transaction log file increases vastly.
Many users would have known about shrinking the database size. Moving the space from end of the file to any unoccupied space which is very near to file front-end then it is called as shrinking process. The same is used for transaction log file shrink.
After shrinking the log file, you can reuse it for allocation or file system return process takes place.
Here too, we are using SQL server management studio for shrinking the transaction log file:
Step 1: Use object explorer to get connected with SQL server management studio and try to expand the instance.
Step 2: Don’t hesitate to expand the database and search the database where you have to shrink the log file transaction.
Step 3: Select the task choice and you will get opened with another few choices added in task such as shrink something.. Click on the shrink and then select the files.
Step 4: Here, in this step you have selected the file name and file type. And then, you will be seeing a check box in name of release unused space. Click on the check box and this enables to reduce the transaction log files space but it doesn’t delete any files to reduce the space.
Step 5: You have another check box option called reorganize files before unused space. When you select this option, then you have to enter the value for shrinking specification. This option is permanent so by default your process will be cleared. This process will help the rows to relocate on unallocated pages.
Step 6: In this step, you can choose the percentage left out in database shrinkage. The percentage count is varied between 0 and 99. But this step is visible only when you choose reorganize files before releasing unused space check box is choose.
Step 7:You have another third option check box by listing empty file by migrating the data to other files in the same file group. From the same group, it moves the files to another location after then, the open space file can be deleted.
Note: This process same for EMPTYFILE for DBCC SHRINKFILE
Step 8: At last select ok.
