Truncate DNN logs in MS SQL Database – How we do it?
Looking for the steps to truncate DNN logs in MS SQL Database? We can help you.
Usually, we truncate the database when the log files consume more space.
At Bobcares, we often receive requests to truncate database in MS SQL as part of our Server Management Services.
Today, let’s discuss how our Support Engineers do it easily for our customers.
What is the difference between Truncate and Shrink?
Both the truncating and shrinking of the SQL database log file are different processes.
On truncating the log file, the status of the virtual log file will convert active to inactive status. The SQL Server internally manages the Logfile into multiple smaller chunks called Virtual Log Files or VLFs.
The VLF is stored inside the log file and contains the actual log records which are actively written inside them. When the new Virtual Log Files are created, they need space. So we truncate the unwanted log files to make the space.
The Shrinking process reduces the size of the physical log file to a smaller or manageable size. However, shrinking the log file is not a good option since the shrinking increases the fragmentation of the database. The fragmentation allows the data to store at the table level. But higher fragmentation reduces the performance of the database.
That’s why truncation is one of the best options to free up space in the database.
How to truncate DNN logs?
Recently, one of our customers approached us that on checking the Plesk Statistics he found that Database is taking more space and told us to truncate the unimportant files like event log, schedule history, site log, etc.
Let’s see how our Support Engineers truncate the database for our customers. We follow the below steps to truncate the DNN logs in MS SQL Database.
Before proceeding with the truncation, we always take a backup of the database.
1. Initially we log in to Microsoft SQL Server Management Studio.
2. Then right-click on the respective database.
3. Then we select the New Query option.
4. After that, we paste the following Script in the New Query page.
By running the below script in the database it truncates the site log, event log, and the schedule history log of the database to maintain the space.
The log file consumes a particular amount of space in the SQL database. By truncating the unwanted log files we can save some space.
For DNN version < 7.4.0 we run the below code.
truncate table sitelog truncate table eventlog truncate table schedulehistory
In DNN version >= 7.4.0, we perform the below three steps to perform the truncation.
1. Initially we drop the Foreign Key Constraints.
2. Then we truncate the tables.
3. After that, we recreate the Foreign Key Constraints
We create a script using SQL queries for each of these operations. Finally, we click the Run button to run the script.
Truncating is a faster operation but the disadvantage is that we cannot roll back the truncated data. If we have a full database backup, then we can recover the truncated data by overwriting the original database with the backup.
[Need more assistance in truncating the database? We’ll help you.]
In today’s writeup, we saw the difference between truncating and shrinking of the database. Also, we saw how our Support Engineers help the customers to truncate the database when the log files consume more space.