Shrinking logs in your SQL Server is a great way of recovering storage space in your database and can sometimes be a better alternative than simply acquiring more storage space instead of optimizing what you already have. Shrinking your logs recovers space in your database by moving data from the end of a file to the unoccupied spaces at the front of the file and thus utilizing that otherwise ‘dead’ or wasted space. This will create unallocated space that can be returned to the file system for later use.
This process can be done by right clicking on your database, Tasks -> Shrink -> Files. select the type as log and select to ‘Reorganize pages before releasing unused space’. This is a fairly quick and effective method of better utilizing the storage space you already have.
What is fragmentation and why does it occur in your database? As data is modified in your database, its indexes will become increasingly fragmented, thus making ordered data retrieval less efficient and reduce overall database performance. For small scale databases this isn’t too much of an issue but for larger databases or even data warehouses, this is a serious concern where to acquisition of data quickly is of paramount importance.
There are a number of different forms of fragmentation such as:
File fragmentation at the operating system level – Deletes and inserts cause the page to become fragmented as the physical sequence of data pages no longer matches the logical order.
Logical order fragmentation – Similar to file fragmentation and caused by deletes and inserts, the index causes pages to be out of order.
Page density fragmentation – Also known as internal fragmentation, will occur as pages split to make room for information added to the page. This will in turn create new pages that may have free spaces on it. These free spaces make SQL Server think that the data is taking up more space that it actually is.
There are a number of ways of dealing with and correcting defragmentation such as using SQL Defrag Manager or by running specialized queries to locate the most fragmented tables and the defrag them.
