February 3, 2009 at 12:35 am
how can you attach more than 20 ldf files in sql server
February 3, 2009 at 12:47 am
Hari (2/3/2009)
how can you attach more than 20 ldf files in sql server
While attaching when you select the .mdf file If that .mdf file had 20 ldf files automatically it will appear.
If your .mdf file does not have 20 ldf file you need to add manually or try to use the T-SQL script rather than GUI.
Rajesh Kasturi
February 3, 2009 at 12:52 am
Hari (2/3/2009)
how can you attach more than 20 ldf files in sql server
One at a time?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 3, 2009 at 12:55 am
Hi Rajesh,
How can you do the manually.
I have only one mdf file,20 ldf files. how can you attach....please explan step by step
February 3, 2009 at 1:11 am
The old way would be by using sp_attach_db system storedproc but this is not the preferred method on 2005
You may want to...
1- alter database set offline
2- Issue one alter database modify file for each ldf file
3- alter database set online
Please check books online for details and test the procedure on a development environment.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 3, 2009 at 1:11 am
If I remember correctly sp_attach_db can work with less the 20 files (I think that the limit is about 16, but I'm not sure about it). You can use create table for attach. See more details on BOL (Sorry that I don't have the time to check it and even post a script).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 3, 2009 at 1:13 am
Adi Cohn (2/3/2009)
You can use create table for attach
Do you mean create database for attach?
sp_attach_db is deprecated
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 3, 2009 at 1:39 am
I hope you are talking about SQL Server 2005.
Here is the sample script in which I have used two log files, so in the same manner you can attach N log files............
I am assuming that you want to attach multiple log files while creating the database, but if you are trying to attach these .ldf files to an existing database then you will have to use ALTER database command instead of CREATE database command.
Sample Script :
USE [master]
GO
/****** Object: Database [MyTest] Script Date: 02/03/2009 13:58:26 ******/
CREATE DATABASE [MyTest] ON PRIMARY
( NAME = N'MyTest', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyTest.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyTest_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%),
( NAME = N'MyTest_log1', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyTest_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
I hope it will help you 🙂
February 3, 2009 at 1:43 am
I miss one thing in this whole discussion.
Why would you want to have 20 ldf files????
That makes no sense at all. 20 ndf's I can understand but having so many logfiles won't give you any advantage.
[font="Verdana"]Markus Bohse[/font]
February 3, 2009 at 4:02 am
PaulB (2/3/2009)
Adi Cohn (2/3/2009)
You can use create table for attachDo you mean create database for attach?
sp_attach_db is deprecated
Yes, I meant create database for attach and not create table for attach. Consider it as a typo;)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 3, 2009 at 5:01 am
As soon as you get the 20 .ldfs attached, drop 19 of them. Only reason you would want more than 1 log file is if you were totally out of disk space and had to extend onto another drive.
This is presuming the files suffixed .ldf are log files and are not in fact data files incorrectly suffixed. 🙂
---------------------------------------------------------------------
February 3, 2009 at 7:10 am
Hari (2/3/2009)
Hi Rajesh,How can you do the manually.
I have only one mdf file,20 ldf files. how can you attach....please explan step by step
Using GUI
--------
1. Open Enterprise manager or SSMS --> Connect to the SQL Server Instance
2. Right click on the databases --> All Tasks --> Attach database...
3. Select the .mdf file path (once you select the .mdf file, down you will see the .mdf files along with .ldf files, here you need to verify the path, if you are attaching the database on the same server where you were detached you will get the correct path or else you need to verify the path)
4. select ok your database attached with more than 20 files.
If you are using SQL Server 2005 steps may differ slightly
Using sp_attach_db
------------------------
BOL says as below,
sp_attach_db should only be executed on databases that were previously detached from the database server using an explicit sp_detach_db operation. If more than 16 files must be specified, use CREATE DATABASE with the FOR ATTACH clause.
Rajesh Kasturi
February 3, 2009 at 7:28 am
george sibbald (2/3/2009)
As soon as you get the 20 .ldfs attached, drop 19 of them. Only reason you would want more than 1 log file is if you were totally out of disk space and had to extend onto another drive.This is presuming the files suffixed .ldf are log files and are not in fact data files incorrectly suffixed. 🙂
First.. Why we should delete 1 log file we can attach more than 20 log files, From BOL we can have
Files per database 32,767 (sql server 2000)
Rajesh Kasturi
February 3, 2009 at 7:53 am
Rajesh kasturi (2/3/2009)
george sibbald (2/3/2009)
As soon as you get the 20 .ldfs attached, drop 19 of them. Only reason you would want more than 1 log file is if you were totally out of disk space and had to extend onto another drive.This is presuming the files suffixed .ldf are log files and are not in fact data files incorrectly suffixed. 🙂
First.. Why we should delete 1 log file we can attach more than 20 log files, From BOL we can have
Files per database 32,767 (sql server 2000)
the log file is a sequential write file, one log file at a time will be written to until it is full, then it will move on to the next file and so on. so there is absolutely no advantage to having multiple log files. At the most you would have two if you run out of log space and have no alternative but to add another file on anpther drive.
---------------------------------------------------------------------
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply