Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Limitations when attaching a SQL Server Database


By:   |   Last Updated: 2011-08-31   |   Comments (6)   |   Related Tips: 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


Last Updated: 2011-08-31


next webcast button


next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

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


Wednesday, January 01, 2014 - 8:41:41 AM - Mayura Back To Top

Good and helped me a lot.


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

Good to know that you found this tip so useful.

Cheers
Ashish Kumar Mehta
MSSQLTips - Author


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

Learn more about SQL Server tools