So, How can you maintain the efficiency of your computer without deleting these log files? The answer is the Shrink log file in SQL server database. Whenever you modify the SQL Server database, it creates a log file.
These files contain records of every logging process. It is a very important file for data recovery in the SQL server database. However, the log files are not filled in completely. They have some free space. Compressing fills up this free space in each file with available data and creates room for new information.
Also, shrunken log files can be used to recover the lost data. It is extremely helpful in saving useful information. Therefore, it is the ideal solution. Are you confused about truncating the transaction log? Think about why you are performing this action. A full database backup does not truncate the log. While truncating the log makes the space available for use again, it does not decrease the transaction log file size.
To reduce the size of the transaction log file, you need to shrink the log file. Keep in mind that following these steps might result in data loss. You should not need to manually truncate the log because regular log backups should automatically perform this task.
Selecting a different filegroup type changes the selections in the other fields accordingly. Filegroup Select a filegroup from the list of Filegroups associated with the selected File type above. Selecting a different filegroup changes the selections in the other fields accordingly.
File name Select a file from the list of available files of the selected filegroup and file type. Location Displays the full path to the currently selected file.
The path is not editable, but it can be copied to the clipboard. Currently allocated space For data files, displays the current allocated space. Release unused space Cause any unused space in the files to be released to the operating system and shrink the file to the last allocated extent, reducing the file size without moving any data.
No attempt is made to relocate rows to unallocated pages. When this option is selected, the user must specify a target file size in the Shrink file to box.
Shrink file to Specifies the target file size for the shrink operation. You have a SQL Server database with a table that stores a lot of records.
Occasionally, you want to dump some of the data in the table because you no longer need to keep the old data. For example, you only need to keep records for the last five years, but your database is holding records for the past twenty years. Anything older than five years needs to be dumped.
However, you noticed that when you dumped the old records which totaled 20GB, your file size on the hard drive did not change. You expect the file size to drop to 40GB, but instead it stays at 60GB. This also holds true if you "truncate" the table, the file size will remain the same as it was before you truncated it.
When you create a database, you initially set a base size for the mdf and ldf files or you rely on the default values set by SQL Server. In either case, this is just a minimum starting point and that file size will grow with the addition of more data in the table s.
After deleting a large amount of data from the database, you notice the mdf and ldf file sizes remain the same as they were before deleting the data.
This is due to SQL Server keeping that max file size as reserved space. Optionally, you can let SQL Server keep the reserved space for use when the amount of records again grows within the database. If you want to shrink the reserved space of the database after you delete data and the reserved space needs to be increased later as data is inserted again, then this procedure may create physical disk fragmentation and affect performance.
Be sure to run a disk defragmentation afterwards. Before we start inserting data, we need to look at and document the file size for the "mdf" and "ldf" files for the database.
This will provide us a baseline for comparison. We have a number of ways to accomplish this, I have listed two of them for your convenience. The results should look something like this, depending on your settings of minimum file size during the creation of the database.
Navigate to the folder that holds the "mdf" and "ldf" files and take a screen shot like in image below.
0コメント