Share via

  • Article

sp_add_log_file_recover_suspect_db (Transact-SQL)

Adds a log file to a file group when recovery cannot complete on a database due to insufficient log space (error 9002). After the file is added, sp_add_log_file_recover_suspect_db turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE database_name ADD LOG FILE.

Important

Before using this stored procedure, consider the alternatives described in Troubleshooting Insufficient Data Disk Space.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_add_log_file_recover_suspect_db [ @dbName= ] 'database', 
    [ @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.

  • [ @name= ] 'logical_file_name'
    Is the name used in the SQL Server 2005 Database Engine 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 the Database Engine is installed. os_file_name is nvarchar(260), with no default.

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

  • [ @maxsize= ] **'**max_size '
    Is the maximum size to which the file can grow. max_size is nvarchar(20), with a default of NULL. Specify a whole number; do not include a decimal. The MB and KB suffixes can be used to specify megabytes or kilobytes. The default is MB.

    If max_size is not specified, the file will grow until the disk is full. The Microsoft Windows application log warns an administrator when a disk is about to become full.

  • [ @filegrowth= ] **'**growth_increment '
    Is the amount of space added to the file each time new space is required. growth_increment is nvarchar(20), with a default of NULL. A value of 0 indicates no growth. Specify a whole number; do not include a decimal. The value can be specified in MB, KB, or percent (%). 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.

    If growth_increment is NULL, the default value is 10%, and the minimum size value is 64 KB. The size specified is rounded to the nearest 64 KB.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Permissions

Execute permissions default to members of the sysadmin fixed server role. These permissions are not transferable.

Examples

In the following example, the database db1 was marked suspect during recovery due to insufficient log space (error 9002).

USE master;
GO
EXEC sp_add_log_file_recover_suspect_db db1, logfile2,
'C:\Program Files\Microsoft SQL
    Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\db1_logfile2.ldf', 
    '1MB';