Limitations when attaching a SQL Server Database

By:   |   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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta 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



Comments For This Article




Tuesday, November 24, 2015 - 8:28:52 AM - Greg Robidoux Back To Top (39128)

Hi Givemore,

you cannot delete the transaction log, but you can shrink the transaction log.

Take a look at these tips:

https://www.mssqltips.com/sqlservertip/2097/how-to-shrink-the-transaction-log-file-in-sql-server/

https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/


Tuesday, November 24, 2015 - 5:10:23 AM - Givemore Back To Top (39127)

I am having challenges with space on my sql server database .can i delete the transaction log so as to free space and restart the database without it ? and how do i go about it 

 


Wednesday, March 25, 2015 - 12:47:38 AM - Ritesh Back To Top (36675)

Attach/Detach will it be phased out in new version. Kindly share supporting link.


Wednesday, January 1, 2014 - 8:41:41 AM - Mayura Back To Top (27929)

Good and helped me a lot.


Wednesday, January 11, 2012 - 11:34:51 AM - Ashish Kumar Mehta Back To Top (15612)

Good to know that you found this tip so useful.

Cheers
Ashish Kumar Mehta
MSSQLTips - Author


Monday, January 9, 2012 - 4:39:22 PM - Smiley Back To Top (15570)
Never would have thunk I would find this so indispeansble.














get free sql tips
agree to terms