Consistent Backup Failures - Please Help

I am not quite a DBA, and this issue has me at the end of my rope.
We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
This backup takes over 8 hours to complete. Each night toward the end, our
backup job fails with the following error:
BackupMedium::ReportIoError: write failure on backup device
'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
the file because another process has locked a portion of the file.).
For the life of me, I cannot determine what process is locking the file. I
have tried executing the backup with the database in Single User mode, and
it still fails.
Are there any utilities that I can use to spy on the access to the file, so
I can turn them off?
ANY help is really appreciated. Thanks.
Ive discovered the drives are all compressed (and the accompanying KBs that
say its bad to backup to compressed drives)...
"codejockey" <cj@.hotmail.com> wrote in message
news:#nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>
|||You might try FileMon from sysinternals (http://www.sysinternals.com).
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"codejockey" <cj@.hotmail.com> wrote in message
news:%23nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>
|||codejockey,
Annoying isn't it? I have had this a lot and 99% of the time it has been
a tape backup job that has the file locked copying it to tape. Quite
often the tape backup program is awaiting someone to change the tape as
the tape is full.
sysinternals.com is a great resource as Peter mentioned. FileMon should
highlight the culprit.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
codejockey wrote:
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file, so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>

Consistent Backup Failures - Please Help

I am not quite a DBA, and this issue has me at the end of my rope.
We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
This backup takes over 8 hours to complete. Each night toward the end, our
backup job fails with the following error:
BackupMedium::ReportIoError: write failure on backup device
'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
the file because another process has locked a portion of the file.).
For the life of me, I cannot determine what process is locking the file. I
have tried executing the backup with the database in Single User mode, and
it still fails.
Are there any utilities that I can use to spy on the access to the file, so
I can turn them off?
ANY help is really appreciated. Thanks.Ive discovered the drives are all compressed (and the accompanying KBs that
say its bad to backup to compressed drives)...
"codejockey" <cj@.hotmail.com> wrote in message
news:#nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>|||You might try FileMon from sysinternals (http://www.sysinternals.com).
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"codejockey" <cj@.hotmail.com> wrote in message
news:%23nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>|||codejockey,
Annoying isn't it? I have had this a lot and 99% of the time it has been
a tape backup job that has the file locked copying it to tape. Quite
often the tape backup program is awaiting someone to change the tape as
the tape is full.
sysinternals.com is a great resource as Peter mentioned. FileMon should
highlight the culprit.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
codejockey wrote:
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000
.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot acces
s
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file, s
o
> I can turn them off?
> ANY help is really appreciated. Thanks.
>

Consistent Backup Failures - Please Help

I am not quite a DBA, and this issue has me at the end of my rope.
We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
This backup takes over 8 hours to complete. Each night toward the end, our
backup job fails with the following error:
BackupMedium::ReportIoError: write failure on backup device
'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
the file because another process has locked a portion of the file.).
For the life of me, I cannot determine what process is locking the file. I
have tried executing the backup with the database in Single User mode, and
it still fails.
Are there any utilities that I can use to spy on the access to the file, so
I can turn them off?
ANY help is really appreciated. Thanks.Ive discovered the drives are all compressed (and the accompanying KBs that
say its bad to backup to compressed drives)...
"codejockey" <cj@.hotmail.com> wrote in message
news:#nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>|||You might try FileMon from sysinternals (http://www.sysinternals.com).
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"codejockey" <cj@.hotmail.com> wrote in message
news:%23nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>|||codejockey,
Annoying isn't it? I have had this a lot and 99% of the time it has been
a tape backup job that has the file locked copying it to tape. Quite
often the tape backup program is awaiting someone to change the tape as
the tape is full.
sysinternals.com is a great resource as Peter mentioned. FileMon should
highlight the culprit.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
codejockey wrote:
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file, so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>

connectivity issue

hey there

I hope I have posted in correct forum - please let me know

I have 1 XP box - SQL Server Standard 2005 - running IIS (Server 1)

I have 1 XP box - SQL Server Express - running IIS (Server 2)

script called (test)

Server 1 can host web pages eg aspx and collect data from Server 2 (noted in web.config)

http:\localhost\test or http:\Server 1\test - either collect data and display

Server 1 cannot go to http:\Server 2\test - error default settings do not allow remote connections.

Server 2 cannot host web pages and collect data from Server 1 - error default settings do not allow remote connections.

Is this because Server 2 has Express Edition?

thanks

Dianne

Hi JewelFire,

Try following the steps on this page to enable remote connections on your server: http://www.cryer.co.uk/brian/sqlserver/hresult_0x2%20npp_error_establishing_connection.htm

Thanks

Waseem

|||

thanks for this but at the moment

we have turned the firewall off on Server 2?

all services are enabled.

any thing else I should check?

Dianne

sqlsql

Connection to the database on MSDE

Hi,

I installed MSDE and the sample databases with it. They work fine.

Well I am using the server explorer of visaula studio standard 2002. I can see all the sample databases there and connect to them as well.

1.
---------------------
Now i created a new database named "testDb" and in a similar fashion i am using windows login thingy.

When i write my connection string in the program now it doesnt work

string connectionStr =@."Server=IMRAN\NetSDK;" + "Integrated Security=SSPI;" + "Connection Timeout=7;" + "Database=testDB;";

If i replace my Database clause to Northwind it works absolutely fine.

The ERROR it gives is:

Cannot open database requested in login 'testDB'. Login fails. Login failed for user 'IMRAN\ASPNET'.

Why is this happenning? :(.

============================================

2.
--------
How can i configure the MSDE to use the SQL Authentication.
It doesnt allow me to do that?

Thanx Heaps,
ImranHi Guys,

I got the solution for the first problem of mine.

We need to Grant Access to the ASPNET user

The ASP.NET process runs under the MACHINENAME\ASPNET account. In order for the data access samples to access the database, this user must be granted rights to the sample databases. To manually grant access to the ASPNET user, replace MACHINENAME with the name of your computer, and run the following commands from the command line. Note: The path to the osql.exe command must be located in your PATH environment variable.

osql -E -S (local)\NetSDK -Q "sp_grantlogin 'MACHINENAME\ASPNET'"

osql -E -S (local)\NetSDK -d DATABASENAME -Q "sp_grantdbaccess 'MACHINENAME\ASPNET'"
osql -E -S (local)\NetSDK -d DATABASENAME -Q "sp_addrolemember 'db_owner', 'MACHINENAME\ASPNET'"

and if you are using the Sql Server Enterprise then just open the Enterprise manager and give the database rights to the [machine_name]\ASPNET user in the database users

Hope that can be useful for someone there.

Thanx.
Imran|||Hi,
Glad you found an answer. BTW, ASPNET is the default user name for Windows 2000 and XP, but it's 'NETWORK SERVICE' on Windows 2003 Server running native mode with IIS 6 (default). I found that out the hard way. After adding 'NT AUTHORITY\NETWORK SERVICE' as a login to SQL Server (MSDE), I was able to have my application access the database using the VS and IE browsers - clearying a major roadblock for me.

Also, you can use the Web Data Administrator to create a new login, if you don't have Enterprise manager, or don't want to use OSQL.

MS sure didn't make ASP.NET appliaction database access clear and simple! Maybe ASP.NET 2.0 will, but at what cost?

Good luck with your projects.

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properl

I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

I have two SQL Server instances installed.

1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?

Hello,

Attach the files to your SQL server instance using for example the SQL management Studio and change your connectionstring to connect into this database.

|||

Well the thing is that I dont have a database created yet. I am trying to create a new database. If i create a database in SQL 2005 then my connectionstring will point to the database on sql server. I need the database to reside in the VS project like a stand alone .mdf file so that I can deply the database with my project.

Does that make sense? Thanks for your help.

|||

Hi,

You may open your Machine.Config file (the file is in %SystemRoot%\Microsoft.NET\Framework\ver. number\CONFIG ). Try to find the ConnectionString node, modify the setting and make it look like

<add name="LocalSqlServer" connectionString="data source=UserLT\SQL2005;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

Thanks.

Connection problem. Business Scorecard Manager 2005 and SSAS 2005.

Hello, I have a problem creating a ADOMD connection from BSM 2005 to SSAS 2005. (Choosing standard connection) It seems like it's connecting but I cant choose "database name", or it does'nt list any database names. No error messages. When I click Cube name I get the error stating: The business scorecard manager server could not connect to the database. Verify that all data has been entered in the boxes and the connection info is correct.

Any suggestions?

// Richard

I suspect this is security-related problem.

Try and see how BSM connecting to Analysis Server. Run SQL Profiler against Analysis Server and see what is the name of the user establishing connection and if this user has any rights to access databases. I suspect BSM establishing conection under Anonymous user.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I used the SQL Profiler and I can see that it is not me as the user who is trying to connect, so far so good but how can I change that. The NTUserName == Network Service.

Can you tell me how to change this?

Thank you,

Christian Lorenz

|||

I added NT AUTHORITY/Authenticated Users to cube reader-role and then it worked.

Connection problem, please

I have a server, in datacenter, that have installed a SQL server 2000
standard version
then another PC, in company, need to connect to that SQL server through the
Enterprise Manager.
Althrough I have created both window account or SQL account, I still cannot
connect to that SQL server, it prompt me connOpen(), then access deny or the
server not exist...how to solve it?
thx a lot~Hello.
I don't have an answer to your question but I would advise
that you look at the following.
1) Does the person have access rights to the server (not
SQL) ?
2) Are you using NT Authorisation or mixed mode.
3) Can you user "see" the server though enterprise
manager ? (if not then its a network problem)
4) Does your user have access rights to the database they
are trying to connect to ?
I'm sorry I can't be of more help but there is a lot that
can go wrong.
Can you give actual error messages ?
Peter
>--Original Message--
>I have a server, in datacenter, that have installed a SQL
server 2000
>standard version
>then another PC, in company, need to connect to that SQL
server through the
>Enterprise Manager.
>Althrough I have created both window account or SQL
account, I still cannot
>connect to that SQL server, it prompt me connOpen(), then
access deny or the
>server not exist...how to solve it?
>thx a lot~
>
>.
>