I recently saw a list of "possibly" bad practices which included the
following. Can anyone expand as to why ?
Using DTS packages because they may be scheduled.
Using Enteprise manager to make data and schema changes
Embedding sql in applications
Thanks !"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
>I recently saw a list of "possibly" bad practices which included the
>following. Can anyone expand as to why ?
> Using DTS packages because they may be scheduled.
> Using Enteprise manager to make data and schema changes
> Embedding sql in applications
> Thanks !
>
>
I can't speak to the DTS package issue, but using SEM to make data and
schema changes is not always in your best interest. While it will work, the
way SEM handles schema changes may not be the quickest or best way to make
those changes in your system. As an example, turn on SQL Profiler and see
what the SEM does when you rename a table.
Embedding SQL in applications is generally verboten for a variety of
reasons. The first and foremost is that you are now creating a tightly
coupled application. What happens if you have an INSERT statement embedded
in your front-end. If you now make a schema change to the table referenced
by that INSERT statement, the INSERT may now fail. (Wrong number of columns
for example.) In an n-tier system, you should generally be looking to
achieve a loosely coupled, highly cohesive system. A second issue with
embedded SQL is that you now have to fix two programs and deploy those
programs. A third item could be security. Depending on how you have things
set up, you may have fewer and less appetizing security options if you are
using embedded SQL.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick,
What about embedding SQL in VBScript within a DTS package ? Does your
opinion change ? Would you prefer that the VBScript execute a stored
proedure ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%232QTJepEFHA.3536@.TK2MSFTNGP15.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
> I can't speak to the DTS package issue, but using SEM to make data and
> schema changes is not always in your best interest. While it will work,
> the way SEM handles schema changes may not be the quickest or best way to
> make those changes in your system. As an example, turn on SQL Profiler
> and see what the SEM does when you rename a table.
> Embedding SQL in applications is generally verboten for a variety of
> reasons. The first and foremost is that you are now creating a tightly
> coupled application. What happens if you have an INSERT statement
> embedded in your front-end. If you now make a schema change to the table
> referenced by that INSERT statement, the INSERT may now fail. (Wrong
> number of columns for example.) In an n-tier system, you should generally
> be looking to achieve a loosely coupled, highly cohesive system. A second
> issue with embedded SQL is that you now have to fix two programs and
> deploy those programs. A third item could be security. Depending on how
> you have things set up, you may have fewer and less appetizing security
> options if you are using embedded SQL.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
> Thanks Rick,
> What about embedding SQL in VBScript within a DTS package ? Does your
> opinion change ? Would you prefer that the VBScript execute a stored
> proedure ?
>
It depends. ;-)
If the DTS package is a run-once type of deal, then I have no problems with
the embedded SQL.
If however, the package is something you will run often, I would rethink it
a bit and probably go with some sprocs that are called from your DTS
package.
Rick|||Thanks Rick,
Basically, the DTS packages simply create xml files from data stored in
tables, and as each file is created, an embedded sql call is made to update
the rows that were just used to create the xml file. No user intervention
is required. The packages are run once each day.
Sound OK ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:O9x2gj3EFHA.2828@.TK2MSFTNGP09.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
>
> It depends. ;-)
> If the DTS package is a run-once type of deal, then I have no problems
> with the embedded SQL.
> If however, the package is something you will run often, I would rethink
> it a bit and probably go with some sprocs that are called from your DTS
> package.
>
> Rick
>
>
Showing posts with label thefollowing. Show all posts
Showing posts with label thefollowing. Show all posts
Monday, March 19, 2012
Bad List ?
I recently saw a list of "possibly" bad practices which included the
following. Can anyone expand as to why ?
Using DTS packages because they may be scheduled.
Using Enteprise manager to make data and schema changes
Embedding sql in applications
Thanks !
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
>I recently saw a list of "possibly" bad practices which included the
>following. Can anyone expand as to why ?
> Using DTS packages because they may be scheduled.
> Using Enteprise manager to make data and schema changes
> Embedding sql in applications
> Thanks !
>
>
I can't speak to the DTS package issue, but using SEM to make data and
schema changes is not always in your best interest. While it will work, the
way SEM handles schema changes may not be the quickest or best way to make
those changes in your system. As an example, turn on SQL Profiler and see
what the SEM does when you rename a table.
Embedding SQL in applications is generally verboten for a variety of
reasons. The first and foremost is that you are now creating a tightly
coupled application. What happens if you have an INSERT statement embedded
in your front-end. If you now make a schema change to the table referenced
by that INSERT statement, the INSERT may now fail. (Wrong number of columns
for example.) In an n-tier system, you should generally be looking to
achieve a loosely coupled, highly cohesive system. A second issue with
embedded SQL is that you now have to fix two programs and deploy those
programs. A third item could be security. Depending on how you have things
set up, you may have fewer and less appetizing security options if you are
using embedded SQL.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks Rick,
What about embedding SQL in VBScript within a DTS package ? Does your
opinion change ? Would you prefer that the VBScript execute a stored
proedure ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%232QTJepEFHA.3536@.TK2MSFTNGP15.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
> I can't speak to the DTS package issue, but using SEM to make data and
> schema changes is not always in your best interest. While it will work,
> the way SEM handles schema changes may not be the quickest or best way to
> make those changes in your system. As an example, turn on SQL Profiler
> and see what the SEM does when you rename a table.
> Embedding SQL in applications is generally verboten for a variety of
> reasons. The first and foremost is that you are now creating a tightly
> coupled application. What happens if you have an INSERT statement
> embedded in your front-end. If you now make a schema change to the table
> referenced by that INSERT statement, the INSERT may now fail. (Wrong
> number of columns for example.) In an n-tier system, you should generally
> be looking to achieve a loosely coupled, highly cohesive system. A second
> issue with embedded SQL is that you now have to fix two programs and
> deploy those programs. A third item could be security. Depending on how
> you have things set up, you may have fewer and less appetizing security
> options if you are using embedded SQL.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
> Thanks Rick,
> What about embedding SQL in VBScript within a DTS package ? Does your
> opinion change ? Would you prefer that the VBScript execute a stored
> proedure ?
>
It depends. ;-)
If the DTS package is a run-once type of deal, then I have no problems with
the embedded SQL.
If however, the package is something you will run often, I would rethink it
a bit and probably go with some sprocs that are called from your DTS
package.
Rick
|||Thanks Rick,
Basically, the DTS packages simply create xml files from data stored in
tables, and as each file is created, an embedded sql call is made to update
the rows that were just used to create the xml file. No user intervention
is required. The packages are run once each day.
Sound OK ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:O9x2gj3EFHA.2828@.TK2MSFTNGP09.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
>
> It depends. ;-)
> If the DTS package is a run-once type of deal, then I have no problems
> with the embedded SQL.
> If however, the package is something you will run often, I would rethink
> it a bit and probably go with some sprocs that are called from your DTS
> package.
>
> Rick
>
>
following. Can anyone expand as to why ?
Using DTS packages because they may be scheduled.
Using Enteprise manager to make data and schema changes
Embedding sql in applications
Thanks !
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
>I recently saw a list of "possibly" bad practices which included the
>following. Can anyone expand as to why ?
> Using DTS packages because they may be scheduled.
> Using Enteprise manager to make data and schema changes
> Embedding sql in applications
> Thanks !
>
>
I can't speak to the DTS package issue, but using SEM to make data and
schema changes is not always in your best interest. While it will work, the
way SEM handles schema changes may not be the quickest or best way to make
those changes in your system. As an example, turn on SQL Profiler and see
what the SEM does when you rename a table.
Embedding SQL in applications is generally verboten for a variety of
reasons. The first and foremost is that you are now creating a tightly
coupled application. What happens if you have an INSERT statement embedded
in your front-end. If you now make a schema change to the table referenced
by that INSERT statement, the INSERT may now fail. (Wrong number of columns
for example.) In an n-tier system, you should generally be looking to
achieve a loosely coupled, highly cohesive system. A second issue with
embedded SQL is that you now have to fix two programs and deploy those
programs. A third item could be security. Depending on how you have things
set up, you may have fewer and less appetizing security options if you are
using embedded SQL.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks Rick,
What about embedding SQL in VBScript within a DTS package ? Does your
opinion change ? Would you prefer that the VBScript execute a stored
proedure ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%232QTJepEFHA.3536@.TK2MSFTNGP15.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
> I can't speak to the DTS package issue, but using SEM to make data and
> schema changes is not always in your best interest. While it will work,
> the way SEM handles schema changes may not be the quickest or best way to
> make those changes in your system. As an example, turn on SQL Profiler
> and see what the SEM does when you rename a table.
> Embedding SQL in applications is generally verboten for a variety of
> reasons. The first and foremost is that you are now creating a tightly
> coupled application. What happens if you have an INSERT statement
> embedded in your front-end. If you now make a schema change to the table
> referenced by that INSERT statement, the INSERT may now fail. (Wrong
> number of columns for example.) In an n-tier system, you should generally
> be looking to achieve a loosely coupled, highly cohesive system. A second
> issue with embedded SQL is that you now have to fix two programs and
> deploy those programs. A third item could be security. Depending on how
> you have things set up, you may have fewer and less appetizing security
> options if you are using embedded SQL.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
> Thanks Rick,
> What about embedding SQL in VBScript within a DTS package ? Does your
> opinion change ? Would you prefer that the VBScript execute a stored
> proedure ?
>
It depends. ;-)
If the DTS package is a run-once type of deal, then I have no problems with
the embedded SQL.
If however, the package is something you will run often, I would rethink it
a bit and probably go with some sprocs that are called from your DTS
package.
Rick
|||Thanks Rick,
Basically, the DTS packages simply create xml files from data stored in
tables, and as each file is created, an embedded sql call is made to update
the rows that were just used to create the xml file. No user intervention
is required. The packages are run once each day.
Sound OK ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:O9x2gj3EFHA.2828@.TK2MSFTNGP09.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
>
> It depends. ;-)
> If the DTS package is a run-once type of deal, then I have no problems
> with the embedded SQL.
> If however, the package is something you will run often, I would rethink
> it a bit and probably go with some sprocs that are called from your DTS
> package.
>
> Rick
>
>
Thursday, February 16, 2012
Backup yielding Operatin System Error 33?
A little help please?
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backu
p.
"Dave Smith" wrote:
> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
> Operating system error 33(The process cannot access the file because anoth
er
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQ
L
> Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:
> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>
>
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backu
p.
"Dave Smith" wrote:
> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
> Operating system error 33(The process cannot access the file because anoth
er
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQ
L
> Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:
> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>
>
Backup yielding Operatin System Error 33?
A little help please?
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.
I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>
|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backup.
"Dave Smith" wrote:
> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>
|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:
> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>
>
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.
I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>
|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backup.
"Dave Smith" wrote:
> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>
|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:
> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)