Datafile limitations per database

How many mdf files can you use in comprising one database. I know one can
setup primary & secondary but how far down the list can it go? Can you have
30 files (each limited to 50GB)?You are well within the limits of SQL Server. One database can have over 32,
000 files 4 TB each.
You can look-up 'maximum capacity specifications' in BOL.
DeeJay
"kisstene" wrote:

> How many mdf files can you use in comprising one database. I know one can
> setup primary & secondary but how far down the list can it go? Can you ha
ve
> 30 files (each limited to 50GB)?

Datafile limitations per database

How many mdf files can you use in comprising one database. I know one can
setup primary & secondary but how far down the list can it go? Can you have
30 files (each limited to 50GB)?You are well within the limits of SQL Server. One database can have over 32,
000 files 4 TB each.
You can look-up 'maximum capacity specifications' in BOL.
DeeJay
"kisstene" wrote:
> How many mdf files can you use in comprising one database. I know one can
> setup primary & secondary but how far down the list can it go? Can you have
> 30 files (each limited to 50GB)?

Databases in read-only

We have a client with several databases in read-only mode. When we try and take the dbs out of read-only, we get the path to the mdf files may be incorrect, which they are not. SQL 2000, SP3a. Has anybody seen this? The files themselves are not read-only, I gave the network services account full control and still nothing. The issue started when the client lost power, lasst week, the dbs came up suspect. Detach/Attach brought them back up in single user.

Any hep is appreciated.

What was the exact sequence of events after the power failure last week? What were the error log errors when the databases came back as suspect? Why did you/they attempt to detach/attach?

Can you post the T-SQL you're using, and the errors you get back?

Thanks

|||It turned out to be a permissions issue. The account used to start sql server didn't have the correct permissions on the data/log/mssql directories. They had changed domains and I don't think they new exactly what they were doing. I suspect the account was a domain admin before the domain change.

I used detach/attach because sp_resetstatus didn't take them out of suspect mode.

Thanks

databases disappeared but MDF, LDF still there...

I have been using SQL Server 2000 but not am not an expert on it. I recently
copied databases into a new server using Database Copy Wizard. The previous
databases were copied with no problems. The last one however, still copied
ok but the databases on the "source" server disappeared! I tried
"re-copying" same databases back into the source server but I am getting a
'name conflict' error. From my research, this meant the mdf and ldf files
are still there. Note that I "copied" and NOT "moved" these databases. Can
you please tell me how I can restore these databases? I also tried
re-creating the missing database so I can export data back into it but I got
"Error 5170: Cannot create file 'e:\data\MSSQL\...\.MDF' because it already
exists. etc" Thank you for helping.
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)
One of the reason could be, the source and the destination database file
names could be same.
When you are using copy database wizard, try to modify destination file name
to something else and then try to copy.
Hopefully, it should work.
Thanks
GYK

databases disappeared but MDF, LDF still there...

I have been using SQL Server 2000 but not am not an expert on it. I recently
copied databases into a new server using Database Copy Wizard. The previous
databases were copied with no problems. The last one however, still copied
ok but the databases on the "source" server disappeared! I tried
"re-copying" same databases back into the source server but I am getting a
'name conflict' error. From my research, this meant the mdf and ldf files
are still there. Note that I "copied" and NOT "moved" these databases. Can
you please tell me how I can restore these databases? I also tried
re-creating the missing database so I can export data back into it but I got
"Error 5170: Cannot create file 'e:\data\MSSQL\...\.MDF' because it already
exists. etc" Thank you for helping.
--
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)One of the reason could be, the source and the destination database file
names could be same.
When you are using copy database wizard, try to modify destination file name
to something else and then try to copy.
Hopefully, it should work.
Thanks
GYK

database with the same name exists

Hi guys,

What is this error

An attempt to attach an auto-named database for file C:\inetpub\wwwroot\Media\App_Data\Library.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.Have u checked the availabily of database in your server. Or any another DB has its mdf file as library.mdf|||Hi,Yes of course other files are ok and working very well|||

SQLExpress creates a directory per user in "c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" to store information. Deleting this directory will fix your problem

Hope this will help you

Database will not attach.

Hi,

We are trying to attach a Database from SQL7 to SQL2K SP3.

On SQL7

The Database was detached and the MDF copied to the New Server.

On SQL2K

We used the attach Db (in Enterprise Manager).

We get a red 'x' as normal against the logfile and noramlly it would create a new one when u proceed, but this time we are getting an error after the dialogue box asking to create a new one:

Error 1813: Could not open new database 'dbname'. CREATE DATABASE is aborted.

Device activation error. The physical file name 'd:\mssql7\data\dbname.ldf' maybe incorrect.

The path is where the logfile resided on the old server.

Regards

John.

Hi,

It seems your old database have mutiple LDF files. If you have mutilple LDF files missed out you will not be able to use the procedure sp_attach_single_file_db (or through Enterprise Manager) to attach the MDF file alone.

A solution for this is:

1. Create a new database with the same name and same MDF and LDF files

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete

the LDF files.

3. STart SQL Server

4. Now your database will be marked suspect

5. Update the sysdatabases to update to Emergency mode. This will not use LOG files

update sysdatabases set status=32768 where name ='dbname'

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILDLOG(dbname,'c:\dbname.ldf')

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.

|||Check if this path "'d:\mssql7\data\dbname.ldf'" exists in your new server or else point this one to a valid path.
|||

This usually happens when SQL Server is unable to create the log file with the same full name. Is 'd:\mssql7\data\dbname.ldf' a valid path in the new machine? I recommend always copying the log file with the database, otherwise the database may not be able to recover. You can use the following SP to attach the database in the new location:

sp_attach_db 'dbname', 'new_path_to_mdf', 'new_path_to_ldf'

Thanks,

Fabricio.

Database will not attach.

Hi,

We are trying to attach a Database from SQL7 to SQL2K SP3.

On SQL7

The Database was detached and the MDF copied to the New Server.

On SQL2K

We used the attach Db (in Enterprise Manager).

We get a red 'x' as normal against the logfile and noramlly it would create a new one when u proceed, but this time we are getting an error after the dialogue box asking to create a new one:

Error 1813: Could not open new database 'dbname'. CREATE DATABASE is aborted.

Device activation error. The physical file name 'd:\mssql7\data\dbname.ldf' maybe incorrect.

The path is where the logfile resided on the old server.

Regards

John.

Hi,

It seems your old database have mutiple LDF files. If you have mutilple LDF files missed out you will not be able to use the procedure sp_attach_single_file_db (or through Enterprise Manager) to attach the MDF file alone.

A solution for this is:

1. Create a new database with the same name and same MDF and LDF files

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete

the LDF files.

3. STart SQL Server

4. Now your database will be marked suspect

5. Update the sysdatabases to update to Emergency mode. This will not use LOG files

update sysdatabases set status=32768 where name ='dbname'

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILDLOG(dbname,'c:\dbname.ldf')

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.

|||Check if this path "'d:\mssql7\data\dbname.ldf'" exists in your new server or else point this one to a valid path.
|||

This usually happens when SQL Server is unable to create the log file with the same full name. Is 'd:\mssql7\data\dbname.ldf' a valid path in the new machine? I recommend always copying the log file with the database, otherwise the database may not be able to recover. You can use the following SP to attach the database in the new location:

sp_attach_db 'dbname', 'new_path_to_mdf', 'new_path_to_ldf'

Thanks,

Fabricio.