Best solution for you when your database is suspect mode due to insufficient space in filegroup?

SQL server uses filegroup for grouping all the database objects & files, there are two types of SQL server filegroup, one is primary & another is user-defined. Primary filegroup contains the primary data file and all the pages for system table while any other filegroups that are specified with Create or Alter statements are known as user-defined filegroups. No any file can be member of more than one filegroup and log file is not a part of any filegroup. It is managed from data space.

Note: One filegroup in each database is specified as a default filegroup. When you create a table or indexes in your database without specifying with any filegroup then it is assumed that all the pages are allocated from the default filegroup.

Consider a scenario wherein you are working on your SQL server 2000 database and suddenly unable to perform any operation on the database due to your database goes in suspect mode and got below error message:

Could not allocate space for object '%.*ls' in database '%.*ls' because the '%.*ls' filegroup is full

Cause: As specified by the above error message, your specified filegroup has insufficient space.

Possible Solution: There are two possible solutions for the above error message and the solutions are:

1. Free Disk Space
2. Add a Data File

1. Free Disk Space: You can gain free disk space by increasing the size of any disk drive that contains files for filegroup and allow filegriup to grow or can gain space by adding a data file with the specified database. If you cannot increase the size of disk drive that contains filegroup then move the filegroup to another disk drive. Follow the steps to free disk space on another drive:

• Move the data file in filegroup with insufficient free disk space to another drive.
• Now detach the database by executing stored procedure sp_detach_db.

Systax for detach stored procedure

sp_detach_db [ @dbname= ] 'database_name'

[ , [ @skipchecks= ] 'skipchecks' ]

[ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

• And finally attach the database by stored procedure sp_attach_db

Systax for attach stored procedure

sp_attach_db [ @dbname= ] 'dbname'

, [ @filename1= ] 'filename_n' [ ,...16 ]

2. Add a Data File: You can add a file to specified database by using ADD file clause of ALTER database statement or enlarge the size of data file by using Modify clause of Alter database statement.