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.

However, at some times, the size of the log file grows immensely and it starts blocking the SQL server.

In this article, we will discuss how to reduce/shrink the SQL file size.

iBase might take longer to respond, or you receive an error message saying that there is no more free space on the disk.

This happens, due to the below reasons:

By default, when SQL creates a database, it does not create a Log file for this database.

One option of this database is the Recovery Mode, which gets full.

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!