I got several times the same questions around index and fragmentation on SQL2008. So I decided to add one more item on Internet to discuss about the reconstruction and reorganization of the index and how detect index fragmentation.

Starter
As fragmentation can have a negative impact on the efficiency of data access; one of the main tasks of a DBA is to maintain database indexes.

In the life cycle of a database, fragmentation is expected behavior and natural. If the database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time.
 

Main course
Since SQL Server 2005 the sys.dm_db_index_physical_stats Dynamic Management Function returns the size and fragmentation information for the data and indexes of the specified table or view.

1. There are 3 mode in the syntax of sys.dm_db_index_physical_stats:

2. There are 2 kind of fragmentation on indexes.

In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing and rebuilding depends on defragmentation values. The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. The value should be as close to zero as possible. A value between 5-30% indicates moderate fragmentation, while any value over 30% indicates high fragmentation.

The avg_page_space_used_in_percent is another value that it is worth to look closely. This value represents the amount of spaced used in the indexes. A value below 75% is usually associated to internal fragmentation (more blank pages on our book than recommended).

Dessert
The script will work for both SQL 2005 and higher versions. The aim is to reduce index fragmentation by recreating, reorganizing, or rebuilding the index:

 DOWNLOAD THE SCRIPT SAMPLE HERE

To run the stored procedure without execute the rebuild : EXECUTE handdleFragmentationIndexes @debugMode = 1

To run the stored procedure and defragement indexes :  EXECUTE handdleFragmentationIndexes

To run the stored procedure and defragement indexes on a specific database : EXECUTE handdleFragmentationIndexes @databaseName = 'myDatabaseName'

This script sample is extract in part from the following BOL : sys.dm_db_index_physical_stats (Transact-SQL)

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

Symptom
I was not able to shrink the transaction log of my database. I got the following error message when I ran an DBCC SHRINKFILE (N'LogicalName' , NOTRUNCATE)

Cannot shrink log file 2 (XXLogicalNameXX) because all logical log files are in use.
Cannot shrink log file 2 (XXLogicalNameXX) because the logical log file located at the end of the file is in use.

Environment: my database was a publisher of my transactional replication and the recovery model was in SIMPLE.

Troubleshooting step
Step 1: if your recovery model is FULL, be ensuring that you made backup log. BACKUP LOG databaseName TO DISK='C:\fileName.TRN'

Step 2: Check the log space used with the command dbcc SQLperf(logspace). Do you have a free space?

Step 3: use the DBCC OPENTRAN Transact-SQL to verify if there is an active transaction in a database at a particular time. If yes kill it.

Step 4: check the value of log_reuse_wait_desc

select name, database_id,recovery_model_desc,log_reuse_wait_desc from sys.databases where name LIKE 'yourDatabaseName'

Cause
In my case, column log_reuse_wait_desc returned REPLICATION (e.g. BOL Factors That Can Delay Log Truncation). So the log was not truncated because records at the beginning of the log are pending replication.

Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. Typically the Log Reader agent will parse the entire log and then mark each log record as replicated by executing sp_repldone.

Resolution
When I tried doing the same manually, my issue was fixed:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

With sp_repldone, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log.

If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions. So if you are not aware with her impact, I recommend you to drop your Publication, Subscription and Disabled Replication. Then run the shrink command and recreate the replication.

Reference
- A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
- How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server
- Transaction Log Truncation
- Shrinking the Transaction Log
- How to use the DBCC SHRINKFILE statement to shrink the transaction log file

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |