How to Reduce/Shrink the SQL File Size

Reducing or shrinking the size of an SQL file is beneficial for different reasons. It helps to optimize the storage space, improve the performance. SQL files, over the time, can grow large in size due to higher amounts of data storage, indexes and other elements.

When you’re using the SQL server with iBase, 2 SQL databases are created, one is for the data and the other is for the Logs. SQL server then creates a Log file for every database. This log file is an internal SQL server file, and does not impact the iBase.

But occasionally, the log file gets so big that it starts to impede the SQL server.
We will talk about how to shorten or minimize the size of a SQL file in this article.
It is possible that iBase will take longer to react or that you will get an error message stating that the disk is full.
This occurs for the reasons listed below:
SQL does not automatically generate a log file for a database when it creates one.
This database has a Recovery Mode option that fills up.

Environment

SQL Server Storage

How to Diagnose the Issue?

In Windows, click on the folder where the database files are stored. Then, search for the files with an LDF extension, and then check the size of the files.
If files are large in size, you might want to reduce or shrink them.

Steps to Reduce/Shrink the SQL File Size:

In order to shrink the Transaction log in the SQL Server Management Studio.

1. First, right-click on the database, and then, choose Properties, then Options.
2. Ensure that the “Recovery mode” is set to “Simple”, not “Full”, and then click on OK.
3. Right-click on the database again, choose Tasks > Shrink Files.
4. Now, change the file type to “Log.”
5. Set the log File size to 100 MB and then click OK.

Once you have performed these steps, check in the folder if the size has been reduced.

Alternatively, for SQL to do it successfully:

• SQL queries:
• ALTER DATABASE mydatabase SET RECOVERY SIMPLE
• DBCC SHRINKFILE (mydatabase_Log, 1)

Warning: In the first sql script, its the main database, and in the 2nd script, its the Log database. 
Once you have completed the steps, check the folder that has been reduced. 
Note: Revert the changes of “Recovery model” from Simple to Full once log file shrink is done
Hope you have found this article helpful!