Databases not available through QA for some users

Hey All Gurus,
Why on a large environment with many users, when you attach to QA the
systyem says on a pop up message that several databases are not avail
for viewing. These users are members and logins have the correct
permissions. This happens also when you hit f8 from inside of QA then
the databases do not show in the object browser list? The really wierd
this is you can query these unlisted objects?
does anyone know why this would happen
Bill
*** Sent via Developersdex http://www.examnotes.net ***if the databases are not ready to accept client connection (i.e. still in
recovery phase), they're not accessible.
-oj
"Bill Orova" <nospam@.devdex.com> wrote in message
news:OP4zvC$VFHA.2796@.TK2MSFTNGP09.phx.gbl...
>
> Hey All Gurus,
> Why on a large environment with many users, when you attach to QA the
> systyem says on a pop up message that several databases are not avail
> for viewing. These users are members and logins have the correct
> permissions. This happens also when you hit f8 from inside of QA then
> the databases do not show in the object browser list? The really wierd
> this is you can query these unlisted objects?
> does anyone know why this would happen
> Bill
> *** Sent via Developersdex http://www.examnotes.net ***

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.