Shrink MSSQL Logs and Rebuild Database Table Indexes

Some quite useful MSSQL notes here.

Simple Recovery Model vs Full Recover Model

Simple Mode

No log backups. Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

Full Mode

Full Recovery Mode on a database means that we intend to be able to recover to a point in time in the event of a failure. This means we plan on using a combination of Full Backups and Transaction Log Backups (and possibly differentials).

SQL Server understands our intent, and it will not truncate (free up space within the file.. notice the file stays the same, I didn’t say shrink, I said truncate. Truncate frees space within a file, shrink removes that “free” space to make the physical file smaller) the log file(s) of our database (the .LDF files). Instead, they will continue to grow until such time as you take a transaction log backup.

It’s the best explanation I found taken from┬áhere.

Why Should One Shrink Logs Before Creating a Backup

As Microsoft (and life practice) say, it might take a long time to restore a database in Microsoft SQL Server 2008/R2 or in Microsoft SQL Server 2012 and to build the Virtual Log File (VLF) list if there are many VLFs in the database.

SQL Server cannot move log records from the end of the log file toward the beginning of the log file. This means that SQL Server can only cut down the file size if the file is empty at the end of the file. The end-most log record sets the limit of how much the transaction log can be shrunk. A transaction log file is shrunk in units of Virtual Log Files (VLF).

Get the Size of T-Logs for All Databases

To see the size of the transaction logs as well as how much space is being used:

DBCC SQLPERF(logspace)

Get Physical Paths of Databases

USE master 
SELECT name, physical_name, size
FROM sys.master_files

Get Databases Log File Name and Log Size

USE database_name;
EXEC sp_helpfile

Shrink Logs

USE database_name;
ALTER DATABASE database_name SET RECOVERY SIMPLE;
DBCC SHRINKFILE (database_log_name, 1);
ALTER DATABASE database_name SET RECOVERY FULL;

Rebuild Table Indexes

Rebuild process drops the existing index and recreates the index.

USE database_name;
ALTER INDEX ALL ON dbo.table_name REBUILD

Reorganise Table Indexes

Reorganise process physically reorganises the leaf nodes of the index.

USE database_name;
ALTER INDEX ALL ON dbo.table_name REORGANIZE

General Recommendation

Index should be rebuilt when index fragmentation is great than 40%. Index should be reorganised when index fragmentation is between 10% to 40%.

Index rebuilding process uses more CPU and it locks the database resources, unless SQL server supports ONLINE option. ONLINE option will keep index available during the rebuilding.

Check SQL Server a Specified Database Index Fragmentation Percentage

USE database_name;
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind 
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

SQL query taken from here.

Leave a Reply

Your email address will not be published. Required fields are marked *