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/

 

To avoid “msg 15151 because it does not exist or you do not have permission.” call the following statement before create procedure and grant permission.

use <database name>
go

create procedure <procedure name>
as

select 
	[filed name] '1979', 
	[filedname] '1097', 
from [database name].[schema].[table name]
return 0
go

grant exec on [database name].[schema].[table name] to <login/user>
go