SQL Server MDF File Fragmentation

Issue: SQL reject writing with error ‘…because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup…’

Checks:

  1. disk space – 100GB free
  2. autogrow was set properly
  3. restart SQLSERVER / SQLAGENT
  4. contig –a xxx.mdf saying huge number of frags and biggest continuous space is 14GB
  5. run contig xxx.mdf but no enough space to defrag and it is slooooow and I/O intensive.

Solution: It is a DEVEL database and no one really cares about it, so we deleted table, rerun the schema and restored from backups

Thoughts:

  1. smaller database file
  2. reduce autogrow

http://www.mssqltips.com/sqlservertip/3008/solving-sql-server-database-physical-file-fragmentation/