sp_add_data_file_recover_suspect_db (T-SQL)

Adds a data file to a file group when recovery cannot complete on a database due to an “insufficient space” (1105) error on the filegroup. After the file is added, this stored procedure turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE ADD FILE.

Important This stored procedure should only be used as described in the Troubleshooting Recovery section.

Syntax

sp_add_data_file_recover_suspect_db [@dbName =] 'database',
    [@filegroup =] 'filegroup_name',
    [@name =] 'logical_file_name',
    [@filename =] 'os_file_name',
    [@size =] 'size',
    [@maxsize =] 'max_size',
    [@filegrowth =] 'growth_increment'

Arguments

[@dbName =] 'database'

Is the name of the database. database is sysname, with no default.

[@filegroup =] 'filegroup_name'

Is the filegroup in which to add the file. filegroup_name is nvarchar(260), with a default of NULL, which indicates the PRIMARY file.

[@name =] 'logical_file_name'

Is the name used in Microsoft® SQL Server™ when referencing the file. The name must be unique in the server. logical_file_name is nvarchar(260), with no default.

[@filename =] 'os_file_name'

Is the path and file name used by the operating system for the file. The file must reside in the server in which SQL Server is installed. os_file_name is nvarchar(260), with no default.

[@size =] 'size'

Is the initial size of the file. The MB and KB suffixes can be used to specify megabytes or kilobytes. The default is MB. Specify a whole number; do not include a decimal. The minimum value for size is 512 KB, and the default if size is not specified is 1 MB. size is nvarchar(20), with a default of NULL.

[@maxsize =] 'max_size'

Is the maximum size to which the file can grow. The MB and KB suffixes can be used to specify megabytes or kilobytes. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file will grow until the disk is full. The Microsoft Windows NT® application log warns an administrator when a disk is about to become full. max_size is nvarchar(20), with a default of NULL.

[@filegrowth =] 'growth_increment'

Is the amount of space added to the file each time new space is needed. A value of 0 indicates no growth. The value can be specified in MB, KB, or %. Specify a whole number; do not include a decimal. When % is specified, the growth increment is the specified percentage of the size of the file at the time the increment occurs. If a number is specified without an MB, KB, or % suffix, the default is MB. The default value if growth_increment is not specified is 10%, and the minimum value is 64 KB. The size specified is rounded to the nearest 64 KB. growth_increment is nvarchar(20), with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Permissions

Execute permissions default to members of the db_owner and db_ddladmin fixed database roles, and to members of the sysadmin and dbcreator fixed server roles. These permissions are not transferable.

Example

In this example, database db1 was marked suspect during recovery due to insufficient space (error 1105) in file group fg1.

sp_add_data_file_recover_suspect_db db1, fg1, file2,
    'c:\mssql7\data\db1_file2.mdf', 1MB

See Also