What is Database Shrink?

SQL Server is a powerful and popular database management system, especially preferred for large databases. A SQL Server log file keeps a record of almost every change made to a database. The log file can grow so large that it takes up a lot of hard disk space on the server. Knowing how to shrink the log file can help you manage a SQL Server more effectively and free up hard disk space for reuse. However, Database Shrink stands out with SQL, it is a process that is sometimes needed in all databases. What is Database Shrink? what is it for and how to do it? Here's what you need to know about shrink databases that make databases more efficient.

What is Shrink Database?

Databases occupy a certain area, regardless of the data in them. In short, the section reserved for your database is 300 MB, while the data contained in the database occupies 300 MB, even if the data contained within the database is 50 MB. This will cause some disruptions over time, or this occupied space may need to be used for a different reason. In such cases, database shrinkage is required. This process, known as database shrinking, is a form of compression. It is intended to reduce the overall space without interfering with the data. But it is not a highly recommended method. Because it can cause loss or distortion of data. Of course, with backup you can also solve such problems and make your data more organized, replace it with current ones and make much better use of the space.

What Does SQL Shrink Database Do?

It is important that SQL Server has enough free space so that it works properly. For this, it is possible to periodically do a Database Shrink. A database shrinkage performs reorganization of data at the data sheet level. Therefore, the data is less fragmented. The data sheets can be released after they have been narrowed down. The available free space is reduced, but the size of the physical data file can also be reduced at the same rate. If you do not do Database Shrink, SQL Server also uses available free space. But SQL Server can also work properly without shrinking. But the downsizing has a direct relationship with the Auto-Grow settings.

Shrink shrinks the physical data file, which increases the chance of automatic growth. Ultimately, downsizing means fewer data sheets are available and affects auto-growth performance. This, in turn, may sometimes be necessary due to the physical space on the disk. When creating a database, the default settings for automatic growth are configured. These settings need to be edited. By default, an automatic growth of 1 MB can be applied for data files and an automatic growth of 10% for log files. For example, you can set it to an automatic growth of 250 MB for data files and log files. This, in turn, prevents automatic growth from being repeated in small steps.

How to Shrink SQL Database?

The pages in use on a SQL Server database file are actually intended to reduce physical fragmentation (fragmentation) of the file by moving the pages in use to the end of the file by moving the pages that are counted (reserved) in use to the end of the file. Shrink frees up 'free' space at the end of the file that can be returned to the file system, thus reducing the physical size of the file. Otherwise, it can be much more challenging when you want to free up space for other files when you run out of space on the given disk. Since directory pages that previously belonged to a directory and are located next to each other will be scattered throughout the database file, moving data sheets can accidentally lead to fragmentation of directories and is not recommended for use. An Index Scan operation, for example, has to search the table's data sheets or index backwards and forwards. This, in turn, significantly slows down the execution of the given query. Therefore, increasing the speed during Database Shrink queries is important to ensure that data is not fragmented and to have healthier databases. So how to do database shrink?

Backup

It is best to back up just in case before doing Database Shrink. Because this process can also lead to the loss of fragmented data. Therefore, the first step is to back up.

Backup is always required before major changes. It is also the stage that needs the most attention, especially in the process of reducing the size of a database. Because a lot of things can go wrong and all data can be damaged. Fortunately, most database providers have a backup (and recovery) option built into their control panel. You can use this option because the backup method has already been tested by your provider and you have support for this method. If you do not have this option, you can always use a backup plugin. If you made a backup, you can proceed to step 2.

Viewing Blank Space

View free space: To view available free space later, right-click Database, select Tasks/Shrink/Database. The available space is the sum of the data file and the log file. Because data files and log files are located on different physical disks.

It is useful to place the data files in a separate physical location for the DTA database (Database Engine Tuning Advisor) and BAM database (Binary Alignment Map). Both databases can grow rapidly and are of secondary importance.

Finding the Causes of Growth

To find out the reasons, you first need to learn more about the size of the tables in the database. To do this, you can use the plugin that you can use on your server or database. Through the plugin, you can get an overview of how large each database table is. Thanks to this, it also becomes easier to find the reason for the growth.

Removing Unused Plugins and Data

Unused Add-ons and removing data: Here you should mainly think about old plugins that still leave garbage in the database. This makes a bit of difference, but it may not contain a lot of data, so it can make the database tidy, although it does not make a difference in MB. The final stages can be listed as follows:

• Launch “Microsoft SQL Server Management Studio” in the “Databases” map.
• Right-click on the database name, select “Tasks”, click “Shrink” and select “Files”.
• Select “Log” next to “File Type:” and then complete with “Minimize process”. But don't forget to select “Rearrange pages to free up unused space” next to “Shrink file:”.
• Here type the size as “MB” in the way you want to reduce the log file and click “OK”.

Reasons for Database Growth

Over the years, a database can become contaminated with (temporary) data that is no longer needed. You can then delete this data and optimize your database. You can also automate these tasks to ensure that these optimizations are made regularly. This way your database remains “lean and standard”. Also, some plugins create their own tables to store data. However, when you remove this plugin, the data remains in the database. If you are sure that these tables are no longer used, you can delete them with the plugin and thus save space.

For whatever reason, you may need Database Shrink over time. However, not everyone can do Database Shrink. Because it is necessary to have high-level administrator rights to perform a database downsizing operation, especially when it comes to SQL. Shared client account cannot perform MS - SQL database downsizing. Therefore, to free up disk space, you need to contact the support services. What should be considered here is to make sure that the database is always backed up, as shrinking can cause data loss, and not who will do the shrinking operation. Of course, if you work with professionals, you don't have to worry about it.

Database Shrink can lead to some losses if no action is taken, but it is necessary for the healthy operation of the database and its more efficient use. If you also want to keep your databases that take your organization one step ahead in the best way, you can take advantage of the data services offered by Komtaş, you can immediately contact us for data analysis, storage, cloud-based solutions.

‍