By: Ashish Kumar Mehta | Updated: 2011-08-31 | Comments (6) | Related: More > Database Administration
Problem
One of the Junior SQL Server Database Administrators in my company wanted to know why he is unable to attach a SQL Server database using sp_attach_db. When he tried to attach the database he got this error message: "Procedure or function sp_attach_db has too many arguments specified". In this tip we look at what causes this issue and how to resolve it.
Solution
It turns out there were too many database files and therefore sp_attach_db fails, so how do you get the database attached?
To illustrate this, let's create a sample database named CoreDB which will have 16 Data (MDF / LDF) and one Log (LDF) file.
1. Execute the below script to create a sample database named CoreDB. (note this creates a database about 50MB)
USE master GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CoreDB') DROP DATABASE [CoreDB] GO
CREATE DATABASE [CoreDB] ON PRIMARY ( NAME = N'CoreDB1', FILENAME = N'D:\CoreDB\CoreDB1.mdf' , SIZE = 3072KB), ( NAME = N'CoreDB2', FILENAME = N'D:\CoreDB\CoreDB2.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB3', FILENAME = N'D:\CoreDB\CoreDB3.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB4', FILENAME = N'D:\CoreDB\CoreDB4.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB5', FILENAME = N'D:\CoreDB\CoreDB5.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB6', FILENAME = N'D:\CoreDB\CoreDB6.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB7', FILENAME = N'D:\CoreDB\CoreDB7.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB8', FILENAME = N'D:\CoreDB\CoreDB8.ndf' , SIZE = 3072KB), FILEGROUP [SECONDARY] ( NAME = N'CoreDB9', FILENAME = N'D:\CoreDB\CoreDB9.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB10', FILENAME = N'D:\CoreDB\CoreDB10.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB11', FILENAME = N'D:\CoreDB\CoreDB11.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB12', FILENAME = N'D:\CoreDB\CoreDB12.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB13', FILENAME = N'D:\CoreDB\CoreDB13.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB14', FILENAME = N'D:\CoreDB\CoreDB14.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB15', FILENAME = N'D:\CoreDB\CoreDB15.ndf' , SIZE = 3072KB), ( NAME = N'CoreDB16', FILENAME = N'D:\CoreDB\CoreDB16.ndf' , SIZE = 3072KB) LOG ON ( NAME = N'CoreDB_log', FILENAME = N'D:\CoreDB\CoreDB_log.ldf' , SIZE = 1024KB) GO
USE [CoreDB] GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [CoreDB] MODIFY FILEGROUP [PRIMARY] DEFAULT GO
2. Once the database is successfully created let's go ahead and detach the database using sp_detach_db system stored procedure.
USE [master] GO
ALTER DATABASE [CoreDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'CoreDB' GO
3. Once the database is successfully detached, then the next step will be to attach the CoreDB database using sp_attach_db system stored procedure.
USE master GO
sp_attach_db 'CoreDB', 'D:\CoreDB\CoreDB1.mdf', 'D:\CoreDB\CoreDB_log.ldf', 'D:\CoreDB\CoreDB2.ndf', 'D:\CoreDB\CoreDB3.ndf', 'D:\CoreDB\CoreDB4.ndf', 'D:\CoreDB\CoreDB5.ndf', 'D:\CoreDB\CoreDB6.ndf', 'D:\CoreDB\CoreDB7.ndf', 'D:\CoreDB\CoreDB8.ndf', 'D:\CoreDB\CoreDB9.ndf', 'D:\CoreDB\CoreDB10.ndf', 'D:\CoreDB\CoreDB11.ndf', 'D:\CoreDB\CoreDB12.ndf', 'D:\CoreDB\CoreDB13.ndf', 'D:\CoreDB\CoreDB14.ndf', 'D:\CoreDB\CoreDB15.ndf', 'D:\CoreDB\CoreDB16.ndf' GO
When the above attach command is run we get this error.
Error Message Msg 8144, Level 16, State 2, Procedure sp_attach_db, Line 0 Procedure or function sp_attach_db has too many arguments specified.
4. You could see that when using sp_attach_db you were unable to attach the "CoreDB" database. This problem occurs if there are more than 16 files within the database that you are trying to attach, including the data files and the log files in the database.
5. Now to attach the CoreDB database you need to use CREATE DATABASE... FOR ATTACH option. Execute the below T-SQL script to attach the CoreDB database.
USE master GO
CREATE DATABASE [CoreDB] ON ( FILENAME = N'D:\CoreDB\CoreDB1.mdf' ), ( FILENAME = N'D:\CoreDB\CoreDB_log.ldf' ), ( FILENAME = N'D:\CoreDB\CoreDB2.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB3.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB4.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB5.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB6.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB7.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB8.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB9.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB10.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB11.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB12.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB13.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB14.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB15.ndf' ), ( FILENAME = N'D:\CoreDB\CoreDB16.ndf' ) FOR ATTACH GO
Important Note: When the FOR ATTACH clause is specified along with the CREATE DATABASE statement, the new database inherits the database option settings of its original database.
These are also other issues encountered when using sp_attach_db to attach a SQL Server database:
- One cannot attach a database which includes more than 16 files (data and log files combined).
- An attach-and-detach operation disables cross-database ownership chaining option if used by the database.
- An attach-and-detach operation turns database TRUSTWORTHY setting OFF if it is used by the database.
- An attach-and-detach operation disables Service Broker setting, if it is used by the database.
Next Steps
- Going forward you should always use the CREATE DATABASE .... FOR ATTACH option instead of sp_attach_db which will be a Deprecated SQL Server Command
About the author
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-08-31
