Error in SQL Server Management Studio Express when Attach DB

Hello

When I try to attach a Database in SSMSEE, I always receive the same error. After clicking on the "ADD"-Button in the "Attach Databases"-Window, the following error occurs:

TITLE: Locate Database Files - ORION\SQLEXPRESS


C:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

OK - Button

And directly after that error, when I click the OK-Button from the error above, I receive the "Locate Database Files"-Windows. But there I see only one partition G from my harddisk(but I have C, D, E, F, G).

I can not understand, why I should not have the necessary security privileges (The path exists at all events). In the Object Explorer I see all my DB's. And I can access on every DB without problems.

How can I solve this problem and attach an existing Database?

Mike50

Hi,

the service accoutn SQL Server is running on needs the appropiate priviledges to attach the database. Even if you have the rights to access the share, this doesn′t mean that SQL Server has, so make sure that the account which can be seen under the properties of the server (or in the service control panel) is priviledged.

(Make also sure that this isn′t a network drive you are using while loading something, if you want to load from the load you have to use the UNC syntac rather than the mapped drives, that much easier)

HTH, Jens Suessmeyer.

|||Hello,

Thanks for your quick answer.

It isn't a network drive, it's local. In the windows explorer, in the SQL Server folders under properties-->security, there I have acces to all folders for everybody (I changed all folders to give access to "everybody2)! But is this the right place to give the privileges? I tried a lot of things but nothing happend.

Can you please explain me, where exactly I have to look under properties of the server and change?

mike50
|||I solved the problem!

In the SQL Server properties, I had for log-in the account "NT AUTHORITY\NetworkService" and this NetworkService had no rights to access the program files folder! So I changed it to "Local Systemaccount" and now I can attach DB's.
But is this a good idea to start the SQL Server Service with this account?

mike50
|||Ok, wasn′t this what is said ?

HTH, Jens Suessmeyer.|||

Jens:

Yes, it was "close" to what you said. But I am still having this issue.

I have the SQL Server (MSSQLSERVER) account running under an account named ".\mssql".

I have the SQL Server Agent (MSSQLSERVER) account running under the same ".\mssql" account.

I have given this user account full rights on my D:\MsSql\ directory.

I have my default data and logs directory configured for D:\MsSql\Data\

I still get this error popping up when I click "add" in the Attach Database dialog window.

Any other ideas?

It is not wise to run the services under Local System account, as he asks above.

Error in SQL Server Management Studio Express when Attach DB

Hello

When I try to attach a Database in SSMSEE, I always receive the same error. After clicking on the "ADD"-Button in the "Attach Databases"-Window, the following error occurs:

TITLE: Locate Database Files - ORION\SQLEXPRESS


C:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

OK - Button

And directly after that error, when I click the OK-Button from the error above, I receive the "Locate Database Files"-Windows. But there I see only one partition G from my harddisk(but I have C, D, E, F, G).

I can not understand, why I should not have the necessary security privileges (The path exists at all events). In the Object Explorer I see all my DB's. And I can access on every DB without problems.

How can I solve this problem and attach an existing Database?

Mike50

Hi,

the service accoutn SQL Server is running on needs the appropiate priviledges to attach the database. Even if you have the rights to access the share, this doesn′t mean that SQL Server has, so make sure that the account which can be seen under the properties of the server (or in the service control panel) is priviledged.

(Make also sure that this isn′t a network drive you are using while loading something, if you want to load from the load you have to use the UNC syntac rather than the mapped drives, that much easier)

HTH, Jens Suessmeyer.

|||Hello,

Thanks for your quick answer.

It isn't a network drive, it's local. In the windows explorer, in the SQL Server folders under properties-->security, there I have acces to all folders for everybody (I changed all folders to give access to "everybody2)! But is this the right place to give the privileges? I tried a lot of things but nothing happend.

Can you please explain me, where exactly I have to look under properties of the server and change?

mike50
|||I solved the problem!

In the SQL Server properties, I had for log-in the account "NT AUTHORITY\NetworkService" and this NetworkService had no rights to access the program files folder! So I changed it to "Local Systemaccount" and now I can attach DB's.
But is this a good idea to start the SQL Server Service with this account?

mike50
|||Ok, wasn′t this what is said ?

HTH, Jens Suessmeyer.|||

Jens:

Yes, it was "close" to what you said. But I am still having this issue.

I have the SQL Server (MSSQLSERVER) account running under an account named ".\mssql".

I have the SQL Server Agent (MSSQLSERVER) account running under the same ".\mssql" account.

I have given this user account full rights on my D:\MsSql\ directory.

I have my default data and logs directory configured for D:\MsSql\Data\

I still get this error popping up when I click "add" in the Attach Database dialog window.

Any other ideas?

It is not wise to run the services under Local System account, as he asks above.

Error in Maintenance Plan (one of database)

Hello
When i execute the Maintenance Plan,
Database Maintenance Plan (Optimization):
(Check) Remove unused space from database files
Shrink database when it grows beyonds: 50 MB
Amount of free space to remain after shrink: 10 % of data space
Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
Sucess
[1] Database openview: Removing unused space from the database files (if
database size is more than 50 MB). Reducing free space to 10 percent of
data...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan openview Optimization' on
13-07-2006 15:04:00
SQLMAINT.EXE Process Exit Code: 0 (Success)
****************************************
********************
but when i add this,
Database Maintenance Plan (Optimization):
(Check) Reorganize data and index pages
with
Reorganize pages with the original amount of free space
or
Change free space per page percentage to 10 %
(Check) Remove unused space from database files
Shrink database when it grows beyonds: 50 MB
Amount of free space to remain after shrink: 10 % of data space
Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
Failed - error
[1] Database openview: Index Rebuild (leaving 100%% free space)...
Rebuilding indexes for table 'OV_MS_Annotation'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 9002: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]The log file for database 'openview' is full.
Back
up the transaction log for the database to free up some log space.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
** Execution Time: 0 hrs, 0 mins, 2 secs **
[2] Database openview: Removing unused space from the database files (if
database size is more than 50 MB). Reducing free space to 10 percent of
data...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan openview Optimization' on
13-07-2006 15:03:02
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Will it be that it doesn't let to execute everything in the same maintenance
Plan?
Regards,
José Júlio DuarteJosé Júlio Duarte wrote:
> Hello
> When i execute the Maintenance Plan,
> Database Maintenance Plan (Optimization):
> (Check) Remove unused space from database files
> Shrink database when it grows beyonds: 50 MB
> Amount of free space to remain after shrink: 10 % of data space
> Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
> Sucess
> [1] Database openview: Removing unused space from the database files (
if
> database size is more than 50 MB). Reducing free space to 10 percent of
> data...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan openview Optimization' on
> 13-07-2006 15:04:00
> SQLMAINT.EXE Process Exit Code: 0 (Success)
> ****************************************
********************
> but when i add this,
> Database Maintenance Plan (Optimization):
> (Check) Reorganize data and index pages
> with
> Reorganize pages with the original amount of free space
> or
> Change free space per page percentage to 10 %
>
> (Check) Remove unused space from database files
> Shrink database when it grows beyonds: 50 MB
> Amount of free space to remain after shrink: 10 % of data space
> Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
> Failed - error
> [1] Database openview: Index Rebuild (leaving 100%% free space)...
> Rebuilding indexes for table 'OV_MS_Annotation'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 9002: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]The log file for database 'openview' is full
. Back
> up the transaction log for the database to free up some log space.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> ** Execution Time: 0 hrs, 0 mins, 2 secs **
> [2] Database openview: Removing unused space from the database files (
if
> database size is more than 50 MB). Reducing free space to 10 percent of
> data...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan openview Optimization' on
> 13-07-2006 15:03:02
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> Will it be that it doesn't let to execute everything in the same maintenan
ce
> Plan?
>
> Regards,
> José Júlio Duarte
>
Reindexing generates a log of transaction log activity, and your
transaction log isn't big enough to handle the volume. Exactly what the
error message says.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hello Tracy
Where can i change to resolve this? and how?
Regards
"Tracy McKibben" wrote:

> José Júlio Duarte wrote:
> Reindexing generates a log of transaction log activity, and your
> transaction log isn't big enough to handle the volume. Exactly what the
> error message says.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||José Júlio Duarte wrote:
> Hello Tracy
> Where can i change to resolve this? and how?
>
Hmmmm... This is precisely why I DESPISE that maintenance plan wizard.
You should not be creating processes like this without understanding
their impact. The fact that you have to ask how to expand (or
auto-grow) a transaction log file tells me that you are in over your head.
Read about "Creating and Maintaining Databases" in Books Online to learn
how to configure automatic file growth.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> José Júlio Duarte wrote:
> Hmmmm... This is precisely why I DESPISE that maintenance plan wizard.
> You should not be creating processes like this without understanding
> their impact. The fact that you have to ask how to expand (or
> auto-grow) a transaction log file tells me that you are in over your head.
> Read about "Creating and Maintaining Databases" in Books Online to learn
> how to configure automatic file growth.
>
After re-reading this, I should clarify. I wasn't attacking you, my
apologies if it looks that way. Those maintenance plan wizards
frustrate me greatly, because they attempt to gloss over what are some
very critical and potentially dangerous processes. Reindexing is an
intensive process, and shouldn't be possible using a wizard, the
administrator should fully understand what's going on throughout the
process. Transaction log backups, if not done properly, can result in
unusable backups, full log files, all sorts of things.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Tracy McKibben wrote:
> After re-reading this, I should clarify. I wasn't attacking you, my
> apologies if it looks that way. Those maintenance plan wizards
> frustrate me greatly, because they attempt to gloss over what are some
> very critical and potentially dangerous processes. Reindexing is an
> intensive process, and shouldn't be possible using a wizard, the
> administrator should fully understand what's going on throughout the
> process. Transaction log backups, if not done properly, can result in
> unusable backups, full log files, all sorts of things.
>
Just to add to Tracy's comments. Why do you run this shrink job every
week? In my opinion it won't give you anything but trouble and poor
performance. Especially when you have a limit of 50 Mb which is next to
nothing for a database file it's really waste of time to shrink it.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator