Hello,
I manage a SQL Server that requires frequent backups to insure not
much data is lost in the event of a failure. Here is how backups are
performed currently:
Sunday, 1:00am
== * Full database backup of ALL databases
* Transaction log backup WITH INIT
Mon-Sat, 1:00am
== * Differential database backup of ALL databases
Daily, Hourly
== * Transaction log backup
I am currently having 2 problems. The first problem is that sometimes
on Sunday mornings when the job attempts to backup the transactions
logs WITH INIT the job will fail because the Daily, Hourly process is
also backing up at the same time. The second problem is that the
transaction log backups seem to grow larger and larger and take longer
and longer to complete. Is it really necessary to store the entire
week of transaction log backups when I'll always have a differential I
can apply for each day? The only purpose of the transaction log
backups is to insure we can restore to the latest point in time before
a failure occurs. There isn't a need to revert back to more than the
previous day's transaction log backup.
Is what I'm doing the best way? How can this be improved upon?
Thank you in advance for your time and efforts.
ShawnHi,
> Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
Based on these requirements you do not need an entire week of transaction
log backups. You just need the transaction log backups taken after your last
differential backup.
But most important, test your strategy restoring your database to another
database server using this combination of full, differential and transaction
log backups.
Hope this helps,
Ben Nevarez
"ITistic" wrote:
> Hello,
> I manage a SQL Server that requires frequent backups to insure not
> much data is lost in the event of a failure. Here is how backups are
> performed currently:
> Sunday, 1:00am
> ==> * Full database backup of ALL databases
> * Transaction log backup WITH INIT
> Mon-Sat, 1:00am
> ==> * Differential database backup of ALL databases
> Daily, Hourly
> ==> * Transaction log backup
> I am currently having 2 problems. The first problem is that sometimes
> on Sunday mornings when the job attempts to backup the transactions
> logs WITH INIT the job will fail because the Daily, Hourly process is
> also backing up at the same time. The second problem is that the
> transaction log backups seem to grow larger and larger and take longer
> and longer to complete. Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
> Is what I'm doing the best way? How can this be improved upon?
> Thank you in advance for your time and efforts.
> Shawn
>
Showing posts with label failure. Show all posts
Showing posts with label failure. Show all posts
Thursday, March 29, 2012
Backup strategy
Hello,
I manage a SQL Server that requires frequent backups to insure not
much data is lost in the event of a failure. Here is how backups are
performed currently:
Sunday, 1:00am
==
* Full database backup of ALL databases
* Transaction log backup WITH INIT
Mon-Sat, 1:00am
==
* Differential database backup of ALL databases
Daily, Hourly
==
* Transaction log backup
I am currently having 2 problems. The first problem is that sometimes
on Sunday mornings when the job attempts to backup the transactions
logs WITH INIT the job will fail because the Daily, Hourly process is
also backing up at the same time. The second problem is that the
transaction log backups seem to grow larger and larger and take longer
and longer to complete. Is it really necessary to store the entire
week of transaction log backups when I'll always have a differential I
can apply for each day? The only purpose of the transaction log
backups is to insure we can restore to the latest point in time before
a failure occurs. There isn't a need to revert back to more than the
previous day's transaction log backup.
Is what I'm doing the best way? How can this be improved upon?
Thank you in advance for your time and efforts.
Shawn
Hi,
> Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
Based on these requirements you do not need an entire week of transaction
log backups. You just need the transaction log backups taken after your last
differential backup.
But most important, test your strategy restoring your database to another
database server using this combination of full, differential and transaction
log backups.
Hope this helps,
Ben Nevarez
"ITistic" wrote:
> Hello,
> I manage a SQL Server that requires frequent backups to insure not
> much data is lost in the event of a failure. Here is how backups are
> performed currently:
> Sunday, 1:00am
> ==
> * Full database backup of ALL databases
> * Transaction log backup WITH INIT
> Mon-Sat, 1:00am
> ==
> * Differential database backup of ALL databases
> Daily, Hourly
> ==
> * Transaction log backup
> I am currently having 2 problems. The first problem is that sometimes
> on Sunday mornings when the job attempts to backup the transactions
> logs WITH INIT the job will fail because the Daily, Hourly process is
> also backing up at the same time. The second problem is that the
> transaction log backups seem to grow larger and larger and take longer
> and longer to complete. Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
> Is what I'm doing the best way? How can this be improved upon?
> Thank you in advance for your time and efforts.
> Shawn
>
I manage a SQL Server that requires frequent backups to insure not
much data is lost in the event of a failure. Here is how backups are
performed currently:
Sunday, 1:00am
==
* Full database backup of ALL databases
* Transaction log backup WITH INIT
Mon-Sat, 1:00am
==
* Differential database backup of ALL databases
Daily, Hourly
==
* Transaction log backup
I am currently having 2 problems. The first problem is that sometimes
on Sunday mornings when the job attempts to backup the transactions
logs WITH INIT the job will fail because the Daily, Hourly process is
also backing up at the same time. The second problem is that the
transaction log backups seem to grow larger and larger and take longer
and longer to complete. Is it really necessary to store the entire
week of transaction log backups when I'll always have a differential I
can apply for each day? The only purpose of the transaction log
backups is to insure we can restore to the latest point in time before
a failure occurs. There isn't a need to revert back to more than the
previous day's transaction log backup.
Is what I'm doing the best way? How can this be improved upon?
Thank you in advance for your time and efforts.
Shawn
Hi,
> Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
Based on these requirements you do not need an entire week of transaction
log backups. You just need the transaction log backups taken after your last
differential backup.
But most important, test your strategy restoring your database to another
database server using this combination of full, differential and transaction
log backups.
Hope this helps,
Ben Nevarez
"ITistic" wrote:
> Hello,
> I manage a SQL Server that requires frequent backups to insure not
> much data is lost in the event of a failure. Here is how backups are
> performed currently:
> Sunday, 1:00am
> ==
> * Full database backup of ALL databases
> * Transaction log backup WITH INIT
> Mon-Sat, 1:00am
> ==
> * Differential database backup of ALL databases
> Daily, Hourly
> ==
> * Transaction log backup
> I am currently having 2 problems. The first problem is that sometimes
> on Sunday mornings when the job attempts to backup the transactions
> logs WITH INIT the job will fail because the Daily, Hourly process is
> also backing up at the same time. The second problem is that the
> transaction log backups seem to grow larger and larger and take longer
> and longer to complete. Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
> Is what I'm doing the best way? How can this be improved upon?
> Thank you in advance for your time and efforts.
> Shawn
>
Backup Strategy
Hi,
for backups, do we need to back up the Temp DB as well?
What restoration strategy is recommended in the event of Reporting Services
failure?
regards
MattYes, it is a good idea if you want to avoid have to install RS again to
recreate the database.
Backup the ReportServer database, the encryption keys, and the config files
for you report manager and server.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
http://support.microsoft.com/?kbid=842425
--
erik perez
www.solien.com
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uJhSpvIlEHA.536@.TK2MSFTNGP11.phx.gbl...
> Hi,
> for backups, do we need to back up the Temp DB as well?
> What restoration strategy is recommended in the event of Reporting
Services
> failure?
> regards
> Matt
>|||Thanks Erik. Great!!
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:OEmiesOlEHA.596@.TK2MSFTNGP11.phx.gbl...
> Yes, it is a good idea if you want to avoid have to install RS again to
> recreate the database.
> Backup the ReportServer database, the encryption keys, and the config
files
> for you report manager and server.
> See:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
> http://support.microsoft.com/?kbid=842425
> --
> erik perez
> www.solien.com
>
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:uJhSpvIlEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > for backups, do we need to back up the Temp DB as well?
> >
> > What restoration strategy is recommended in the event of Reporting
> Services
> > failure?
> >
> > regards
> >
> > Matt
> >
> >
>
for backups, do we need to back up the Temp DB as well?
What restoration strategy is recommended in the event of Reporting Services
failure?
regards
MattYes, it is a good idea if you want to avoid have to install RS again to
recreate the database.
Backup the ReportServer database, the encryption keys, and the config files
for you report manager and server.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
http://support.microsoft.com/?kbid=842425
--
erik perez
www.solien.com
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uJhSpvIlEHA.536@.TK2MSFTNGP11.phx.gbl...
> Hi,
> for backups, do we need to back up the Temp DB as well?
> What restoration strategy is recommended in the event of Reporting
Services
> failure?
> regards
> Matt
>|||Thanks Erik. Great!!
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:OEmiesOlEHA.596@.TK2MSFTNGP11.phx.gbl...
> Yes, it is a good idea if you want to avoid have to install RS again to
> recreate the database.
> Backup the ReportServer database, the encryption keys, and the config
files
> for you report manager and server.
> See:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
> http://support.microsoft.com/?kbid=842425
> --
> erik perez
> www.solien.com
>
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:uJhSpvIlEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > for backups, do we need to back up the Temp DB as well?
> >
> > What restoration strategy is recommended in the event of Reporting
> Services
> > failure?
> >
> > regards
> >
> > Matt
> >
> >
>
Sunday, March 25, 2012
backup should continue even after failure
I have created a stored procedure to backup databases,
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.
Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.
Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>
backup should continue even after failure
I have created a stored procedure to backup databases,
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>
backup should continue even after failure
I have created a stored procedure to backup databases,
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>sql
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>sql
Sunday, March 11, 2012
Backup problems
Hi,
I'm getting "BackupMedium::ReportIoError: write failure on
backup device 'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK'.
Operating system error 112(error not found)" messages in
the SQL 2000 Server logs when we try to backup our
databases. This is followed by "Internal I/O request
0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
Position: 21632512, UMS: Internal: 0x103, InternalHigh:
0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
112, BackupFile: d:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK" and
then "BACKUP failed to complete the command BACKUP
DATABASE [legal] TO DISK = N'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK' WITH
INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
messages. The database is around 300Mb and we have 40Gb of
free space on the drive.
The server is Dell PE2600 with a Perc Raid controller
handling 3 drives in Raid 5 config. These are split into
two drives; C: for OS and D: for applications and data.
The smaller system databases back up OK as do the
transaction logs. I've found a reference to writeback
cache but don't know if this is the root cause of the
problem. The OS is Windows 2003 Small Business Server with
SQL Server SP3a.
TIA
Trevor>
> Hi,
> I'm getting "BackupMedium::ReportIoError: write failure on
> backup device 'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK'.
> Operating system error 112(error not found)" messages in
> the SQL 2000 Server logs when we try to backup our
> databases. This is followed by "Internal I/O request
> 0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
> Position: 21632512, UMS: Internal: 0x103, InternalHigh:
> 0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
> 0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
> 112, BackupFile: d:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK" and
> then "BACKUP failed to complete the command BACKUP
> DATABASE [legal] TO DISK = N'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK' WITH
> INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
> messages. The database is around 300Mb and we have 40Gb of
> free space on the drive.
> The server is Dell PE2600 with a Perc Raid controller
> handling 3 drives in Raid 5 config. These are split into
> two drives; C: for OS and D: for applications and data.
> The smaller system databases back up OK as do the
> transaction logs. I've found a reference to writeback
> cache but don't know if this is the root cause of the
> problem. The OS is Windows 2003 Small Business Server with
> SQL Server SP3a.
> TIA
> Trevor
>
--
Hi Trevor,
Are you using a compressed volume? It is possible that you are out of
diskspace.
Compressed volumes are not supported. Also checkout the following article:
INF: SQL Server and Striped Backups
http://support.microsoft.com/?id=325334
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional|||Eric Cardenas wrote:
> >
> > Hi,
> > I'm getting "BackupMedium::ReportIoError: write failure on
> > backup device 'd:\select
> > legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK'.
> > Operating system error 112(error not found)" messages in
> > the SQL 2000 Server logs when we try to backup our
> > databases. This is followed by "Internal I/O request
> > 0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
> > Position: 21632512, UMS: Internal: 0x103, InternalHigh:
> > 0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
> > 0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
> > 112, BackupFile: d:\select
> > legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK" and
> > then "BACKUP failed to complete the command BACKUP
> > DATABASE [legal] TO DISK = N'd:\select
> > legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK' WITH
> > INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
> > messages. The database is around 300Mb and we have 40Gb of
> > free space on the drive.
> > The server is Dell PE2600 with a Perc Raid controller
> > handling 3 drives in Raid 5 config. These are split into
> > two drives; C: for OS and D: for applications and data.
> > The smaller system databases back up OK as do the
> > transaction logs. I've found a reference to writeback
> > cache but don't know if this is the root cause of the
> > problem. The OS is Windows 2003 Small Business Server with
> > SQL Server SP3a.
> > TIA
> > Trevor
> >
> --
> Hi Trevor,
> Are you using a compressed volume? It is possible that you are out of
> diskspace.
> Compressed volumes are not supported. Also checkout the following
> article:
> INF: SQL Server and Striped Backups
> http://support.microsoft.com/?id=325334
> Hope this helps,
> --
> Eric Cardenas
> SQL Server senior support professional
Eric,
We should have plenty of disk space. The drives aren't compressed as
far as I am aware.
The databases reside on the same drive that we are backing up to.
We are backing up the databases to a folder on the drive using the SQL
Server backup, and then to tape; the actual database files we exclude
from the tape backup.
The server reports around 40Gb of free space on a 60Gb drive, and the
databases that it fails to back up are our main data database which is
around 200Mb to 250Mb in size, and one of the SQL Server system
databases which looks to be only around 25Mb! The other smaller system
databases back up OK.
The drive is on a Raid 5 array which comprises three 60Gb drives.
We can copy and otherwise create large files on the drive without any
problems; we can copy the database files manually with out any problems
(when we shutdown the SQL Server)
It just seems to be the SQL Server backup of the databases that are
reporting an error.
I've seen some references to writeback cache causing a problem like
this, but nothing that I can say "this is how we fix the problem"
I can't believe that the problem is with the raid striping as I would
then expect loads of people to be reporting this.
Trevor
I'm getting "BackupMedium::ReportIoError: write failure on
backup device 'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK'.
Operating system error 112(error not found)" messages in
the SQL 2000 Server logs when we try to backup our
databases. This is followed by "Internal I/O request
0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
Position: 21632512, UMS: Internal: 0x103, InternalHigh:
0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
112, BackupFile: d:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK" and
then "BACKUP failed to complete the command BACKUP
DATABASE [legal] TO DISK = N'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK' WITH
INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
messages. The database is around 300Mb and we have 40Gb of
free space on the drive.
The server is Dell PE2600 with a Perc Raid controller
handling 3 drives in Raid 5 config. These are split into
two drives; C: for OS and D: for applications and data.
The smaller system databases back up OK as do the
transaction logs. I've found a reference to writeback
cache but don't know if this is the root cause of the
problem. The OS is Windows 2003 Small Business Server with
SQL Server SP3a.
TIA
Trevor>
> Hi,
> I'm getting "BackupMedium::ReportIoError: write failure on
> backup device 'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK'.
> Operating system error 112(error not found)" messages in
> the SQL 2000 Server logs when we try to backup our
> databases. This is followed by "Internal I/O request
> 0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
> Position: 21632512, UMS: Internal: 0x103, InternalHigh:
> 0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
> 0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
> 112, BackupFile: d:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK" and
> then "BACKUP failed to complete the command BACKUP
> DATABASE [legal] TO DISK = N'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK' WITH
> INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
> messages. The database is around 300Mb and we have 40Gb of
> free space on the drive.
> The server is Dell PE2600 with a Perc Raid controller
> handling 3 drives in Raid 5 config. These are split into
> two drives; C: for OS and D: for applications and data.
> The smaller system databases back up OK as do the
> transaction logs. I've found a reference to writeback
> cache but don't know if this is the root cause of the
> problem. The OS is Windows 2003 Small Business Server with
> SQL Server SP3a.
> TIA
> Trevor
>
--
Hi Trevor,
Are you using a compressed volume? It is possible that you are out of
diskspace.
Compressed volumes are not supported. Also checkout the following article:
INF: SQL Server and Striped Backups
http://support.microsoft.com/?id=325334
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional|||Eric Cardenas wrote:
> >
> > Hi,
> > I'm getting "BackupMedium::ReportIoError: write failure on
> > backup device 'd:\select
> > legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK'.
> > Operating system error 112(error not found)" messages in
> > the SQL 2000 Server logs when we try to backup our
> > databases. This is followed by "Internal I/O request
> > 0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
> > Position: 21632512, UMS: Internal: 0x103, InternalHigh:
> > 0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
> > 0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
> > 112, BackupFile: d:\select
> > legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK" and
> > then "BACKUP failed to complete the command BACKUP
> > DATABASE [legal] TO DISK = N'd:\select
> > legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK' WITH
> > INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
> > messages. The database is around 300Mb and we have 40Gb of
> > free space on the drive.
> > The server is Dell PE2600 with a Perc Raid controller
> > handling 3 drives in Raid 5 config. These are split into
> > two drives; C: for OS and D: for applications and data.
> > The smaller system databases back up OK as do the
> > transaction logs. I've found a reference to writeback
> > cache but don't know if this is the root cause of the
> > problem. The OS is Windows 2003 Small Business Server with
> > SQL Server SP3a.
> > TIA
> > Trevor
> >
> --
> Hi Trevor,
> Are you using a compressed volume? It is possible that you are out of
> diskspace.
> Compressed volumes are not supported. Also checkout the following
> article:
> INF: SQL Server and Striped Backups
> http://support.microsoft.com/?id=325334
> Hope this helps,
> --
> Eric Cardenas
> SQL Server senior support professional
Eric,
We should have plenty of disk space. The drives aren't compressed as
far as I am aware.
The databases reside on the same drive that we are backing up to.
We are backing up the databases to a folder on the drive using the SQL
Server backup, and then to tape; the actual database files we exclude
from the tape backup.
The server reports around 40Gb of free space on a 60Gb drive, and the
databases that it fails to back up are our main data database which is
around 200Mb to 250Mb in size, and one of the SQL Server system
databases which looks to be only around 25Mb! The other smaller system
databases back up OK.
The drive is on a Raid 5 array which comprises three 60Gb drives.
We can copy and otherwise create large files on the drive without any
problems; we can copy the database files manually with out any problems
(when we shutdown the SQL Server)
It just seems to be the SQL Server backup of the databases that are
reporting an error.
I've seen some references to writeback cache causing a problem like
this, but nothing that I can say "this is how we fix the problem"
I can't believe that the problem is with the raid striping as I would
then expect loads of people to be reporting this.
Trevor
Backup problems
Hi,
I'm getting "BackupMedium::ReportIoError: write failure on
backup device 'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK'.
Operating system error 112(error not found)" messages in
the SQL 2000 Server logs when we try to backup our
databases. This is followed by "Internal I/O request
0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
Position: 21632512, UMS: Internal: 0x103, InternalHigh:
0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
112, BackupFile: d:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK" and
then "BACKUP failed to complete the command BACKUP
DATABASE [legal] TO DISK = N'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK' WITH
INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
messages. The database is around 300Mb and we have 40Gb of
free space on the drive.
The server is Dell PE2600 with a Perc Raid controller
handling 3 drives in Raid 5 config. These are split into
two drives; C: for OS and D: for applications and data.
The smaller system databases back up OK as do the
transaction logs. I've found a reference to writeback
cache but don't know if this is the root cause of the
problem. The OS is Windows 2003 Small Business Server with
SQL Server SP3a.
TIA
Trevor>
> Hi,
> I'm getting "BackupMedium::ReportIoError: write failure on
> backup device 'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK'.
> Operating system error 112(error not found)" messages in
> the SQL 2000 Server logs when we try to backup our
> databases. This is followed by "Internal I/O request
> 0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
> Position: 21632512, UMS: Internal: 0x103, InternalHigh:
> 0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
> 0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
> 112, BackupFile: d:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK" and
> then "BACKUP failed to complete the command BACKUP
> DATABASE [legal] TO DISK = N'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK' WITH
> INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
> messages. The database is around 300Mb and we have 40Gb of
> free space on the drive.
> The server is Dell PE2600 with a Perc Raid controller
> handling 3 drives in Raid 5 config. These are split into
> two drives; C: for OS and D: for applications and data.
> The smaller system databases back up OK as do the
> transaction logs. I've found a reference to writeback
> cache but don't know if this is the root cause of the
> problem. The OS is Windows 2003 Small Business Server with
> SQL Server SP3a.
> TIA
> Trevor
>
--
Hi Trevor,
Are you using a compressed volume? It is possible that you are out of
diskspace.
Compressed volumes are not supported. Also checkout the following article:
INF: SQL Server and Striped Backups
http://support.microsoft.com/?id=325334
Hope this helps,
Eric Crdenas
SQL Server senior support professional|||Eric Cardenas wrote:
> --
> Hi Trevor,
> Are you using a compressed volume? It is possible that you are out of
> diskspace.
> Compressed volumes are not supported. Also checkout the following
> article:
> INF: SQL Server and Striped Backups
> http://support.microsoft.com/?id=325334
> Hope this helps,
> --
> Eric Cardenas
> SQL Server senior support professional
Eric,
We should have plenty of disk space. The drives aren't compressed as
far as I am aware.
The databases reside on the same drive that we are backing up to.
We are backing up the databases to a folder on the drive using the SQL
Server backup, and then to tape; the actual database files we exclude
from the tape backup.
The server reports around 40Gb of free space on a 60Gb drive, and the
databases that it fails to back up are our main data database which is
around 200Mb to 250Mb in size, and one of the SQL Server system
databases which looks to be only around 25Mb! The other smaller system
databases back up OK.
The drive is on a Raid 5 array which comprises three 60Gb drives.
We can copy and otherwise create large files on the drive without any
problems; we can copy the database files manually with out any problems
(when we shutdown the SQL Server)
It just seems to be the SQL Server backup of the databases that are
reporting an error.
I've seen some references to writeback cache causing a problem like
this, but nothing that I can say "this is how we fix the problem"
I can't believe that the problem is with the raid striping as I would
then expect loads of people to be reporting this.
Trevor
I'm getting "BackupMedium::ReportIoError: write failure on
backup device 'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK'.
Operating system error 112(error not found)" messages in
the SQL 2000 Server logs when we try to backup our
databases. This is followed by "Internal I/O request
0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
Position: 21632512, UMS: Internal: 0x103, InternalHigh:
0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
112, BackupFile: d:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK" and
then "BACKUP failed to complete the command BACKUP
DATABASE [legal] TO DISK = N'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK' WITH
INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
messages. The database is around 300Mb and we have 40Gb of
free space on the drive.
The server is Dell PE2600 with a Perc Raid controller
handling 3 drives in Raid 5 config. These are split into
two drives; C: for OS and D: for applications and data.
The smaller system databases back up OK as do the
transaction logs. I've found a reference to writeback
cache but don't know if this is the root cause of the
problem. The OS is Windows 2003 Small Business Server with
SQL Server SP3a.
TIA
Trevor>
> Hi,
> I'm getting "BackupMedium::ReportIoError: write failure on
> backup device 'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK'.
> Operating system error 112(error not found)" messages in
> the SQL 2000 Server logs when we try to backup our
> databases. This is followed by "Internal I/O request
> 0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
> Position: 21632512, UMS: Internal: 0x103, InternalHigh:
> 0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
> 0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
> 112, BackupFile: d:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK" and
> then "BACKUP failed to complete the command BACKUP
> DATABASE [legal] TO DISK = N'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404
292100.BAK' WITH
> INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
> messages. The database is around 300Mb and we have 40Gb of
> free space on the drive.
> The server is Dell PE2600 with a Perc Raid controller
> handling 3 drives in Raid 5 config. These are split into
> two drives; C: for OS and D: for applications and data.
> The smaller system databases back up OK as do the
> transaction logs. I've found a reference to writeback
> cache but don't know if this is the root cause of the
> problem. The OS is Windows 2003 Small Business Server with
> SQL Server SP3a.
> TIA
> Trevor
>
--
Hi Trevor,
Are you using a compressed volume? It is possible that you are out of
diskspace.
Compressed volumes are not supported. Also checkout the following article:
INF: SQL Server and Striped Backups
http://support.microsoft.com/?id=325334
Hope this helps,
Eric Crdenas
SQL Server senior support professional|||Eric Cardenas wrote:
> --
> Hi Trevor,
> Are you using a compressed volume? It is possible that you are out of
> diskspace.
> Compressed volumes are not supported. Also checkout the following
> article:
> INF: SQL Server and Striped Backups
> http://support.microsoft.com/?id=325334
> Hope this helps,
> --
> Eric Cardenas
> SQL Server senior support professional
Eric,
We should have plenty of disk space. The drives aren't compressed as
far as I am aware.
The databases reside on the same drive that we are backing up to.
We are backing up the databases to a folder on the drive using the SQL
Server backup, and then to tape; the actual database files we exclude
from the tape backup.
The server reports around 40Gb of free space on a 60Gb drive, and the
databases that it fails to back up are our main data database which is
around 200Mb to 250Mb in size, and one of the SQL Server system
databases which looks to be only around 25Mb! The other smaller system
databases back up OK.
The drive is on a Raid 5 array which comprises three 60Gb drives.
We can copy and otherwise create large files on the drive without any
problems; we can copy the database files manually with out any problems
(when we shutdown the SQL Server)
It just seems to be the SQL Server backup of the databases that are
reporting an error.
I've seen some references to writeback cache causing a problem like
this, but nothing that I can say "this is how we fix the problem"
I can't believe that the problem is with the raid striping as I would
then expect loads of people to be reporting this.
Trevor
Backup problems
Hi,
I'm getting "BackupMedium::ReportIoError: write failure on
backup device 'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK '.
Operating system error 112(error not found)" messages in
the SQL 2000 Server logs when we try to backup our
databases. This is followed by "Internal I/O request
0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
Position: 21632512, UMS: Internal: 0x103, InternalHigh:
0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
112, BackupFile: d:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK " and
then "BACKUP failed to complete the command BACKUP
DATABASE [legal] TO DISK = N'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK ' WITH
INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
messages. The database is around 300Mb and we have 40Gb of
free space on the drive.
The server is Dell PE2600 with a Perc Raid controller
handling 3 drives in Raid 5 config. These are split into
two drives; C: for OS and D: for applications and data.
The smaller system databases back up OK as do the
transaction logs. I've found a reference to writeback
cache but don't know if this is the root cause of the
problem. The OS is Windows 2003 Small Business Server with
SQL Server SP3a.
TIA
Trevor
>
> Hi,
> I'm getting "BackupMedium::ReportIoError: write failure on
> backup device 'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK '.
> Operating system error 112(error not found)" messages in
> the SQL 2000 Server logs when we try to backup our
> databases. This is followed by "Internal I/O request
> 0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
> Position: 21632512, UMS: Internal: 0x103, InternalHigh:
> 0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
> 0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
> 112, BackupFile: d:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK " and
> then "BACKUP failed to complete the command BACKUP
> DATABASE [legal] TO DISK = N'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK ' WITH
> INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
> messages. The database is around 300Mb and we have 40Gb of
> free space on the drive.
> The server is Dell PE2600 with a Perc Raid controller
> handling 3 drives in Raid 5 config. These are split into
> two drives; C: for OS and D: for applications and data.
> The smaller system databases back up OK as do the
> transaction logs. I've found a reference to writeback
> cache but don't know if this is the root cause of the
> problem. The OS is Windows 2003 Small Business Server with
> SQL Server SP3a.
> TIA
> Trevor
>
Hi Trevor,
Are you using a compressed volume? It is possible that you are out of
diskspace.
Compressed volumes are not supported. Also checkout the following article:
INF: SQL Server and Striped Backups
http://support.microsoft.com/?id=325334
Hope this helps,
Eric Crdenas
SQL Server senior support professional
|||Eric Cardenas wrote:
> --
> Hi Trevor,
> Are you using a compressed volume? It is possible that you are out of
> diskspace.
> Compressed volumes are not supported. Also checkout the following
> article:
> INF: SQL Server and Striped Backups
> http://support.microsoft.com/?id=325334
> Hope this helps,
> --
> Eric Cardenas
> SQL Server senior support professional
Eric,
We should have plenty of disk space. The drives aren't compressed as
far as I am aware.
The databases reside on the same drive that we are backing up to.
We are backing up the databases to a folder on the drive using the SQL
Server backup, and then to tape; the actual database files we exclude
from the tape backup.
The server reports around 40Gb of free space on a 60Gb drive, and the
databases that it fails to back up are our main data database which is
around 200Mb to 250Mb in size, and one of the SQL Server system
databases which looks to be only around 25Mb! The other smaller system
databases back up OK.
The drive is on a Raid 5 array which comprises three 60Gb drives.
We can copy and otherwise create large files on the drive without any
problems; we can copy the database files manually with out any problems
(when we shutdown the SQL Server)
It just seems to be the SQL Server backup of the databases that are
reporting an error.
I've seen some references to writeback cache causing a problem like
this, but nothing that I can say "this is how we fix the problem"
I can't believe that the problem is with the raid striping as I would
then expect loads of people to be reporting this.
Trevor
I'm getting "BackupMedium::ReportIoError: write failure on
backup device 'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK '.
Operating system error 112(error not found)" messages in
the SQL 2000 Server logs when we try to backup our
databases. This is followed by "Internal I/O request
0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
Position: 21632512, UMS: Internal: 0x103, InternalHigh:
0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
112, BackupFile: d:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK " and
then "BACKUP failed to complete the command BACKUP
DATABASE [legal] TO DISK = N'd:\select
legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK ' WITH
INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
messages. The database is around 300Mb and we have 40Gb of
free space on the drive.
The server is Dell PE2600 with a Perc Raid controller
handling 3 drives in Raid 5 config. These are split into
two drives; C: for OS and D: for applications and data.
The smaller system databases back up OK as do the
transaction logs. I've found a reference to writeback
cache but don't know if this is the root cause of the
problem. The OS is Windows 2003 Small Business Server with
SQL Server SP3a.
TIA
Trevor
>
> Hi,
> I'm getting "BackupMedium::ReportIoError: write failure on
> backup device 'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK '.
> Operating system error 112(error not found)" messages in
> the SQL 2000 Server logs when we try to backup our
> databases. This is followed by "Internal I/O request
> 0x53BE4B78: Op: Write, pBuffer: 0x0D6A0000, Size: 983040,
> Position: 21632512, UMS: Internal: 0x103, InternalHigh:
> 0x0, Offset: 0x14A1600, OffsetHigh: 0x0, m_buf:
> 0x0D6A0000, m_len: 983040, m_actualBytes: 0, m_errcode:
> 112, BackupFile: d:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK " and
> then "BACKUP failed to complete the command BACKUP
> DATABASE [legal] TO DISK = N'd:\select
> legal\MSSQL$LEGAL\BACKUP\legal_db_200404292100.BAK ' WITH
> INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
> messages. The database is around 300Mb and we have 40Gb of
> free space on the drive.
> The server is Dell PE2600 with a Perc Raid controller
> handling 3 drives in Raid 5 config. These are split into
> two drives; C: for OS and D: for applications and data.
> The smaller system databases back up OK as do the
> transaction logs. I've found a reference to writeback
> cache but don't know if this is the root cause of the
> problem. The OS is Windows 2003 Small Business Server with
> SQL Server SP3a.
> TIA
> Trevor
>
Hi Trevor,
Are you using a compressed volume? It is possible that you are out of
diskspace.
Compressed volumes are not supported. Also checkout the following article:
INF: SQL Server and Striped Backups
http://support.microsoft.com/?id=325334
Hope this helps,
Eric Crdenas
SQL Server senior support professional
|||Eric Cardenas wrote:
> --
> Hi Trevor,
> Are you using a compressed volume? It is possible that you are out of
> diskspace.
> Compressed volumes are not supported. Also checkout the following
> article:
> INF: SQL Server and Striped Backups
> http://support.microsoft.com/?id=325334
> Hope this helps,
> --
> Eric Cardenas
> SQL Server senior support professional
Eric,
We should have plenty of disk space. The drives aren't compressed as
far as I am aware.
The databases reside on the same drive that we are backing up to.
We are backing up the databases to a folder on the drive using the SQL
Server backup, and then to tape; the actual database files we exclude
from the tape backup.
The server reports around 40Gb of free space on a 60Gb drive, and the
databases that it fails to back up are our main data database which is
around 200Mb to 250Mb in size, and one of the SQL Server system
databases which looks to be only around 25Mb! The other smaller system
databases back up OK.
The drive is on a Raid 5 array which comprises three 60Gb drives.
We can copy and otherwise create large files on the drive without any
problems; we can copy the database files manually with out any problems
(when we shutdown the SQL Server)
It just seems to be the SQL Server backup of the databases that are
reporting an error.
I've seen some references to writeback cache causing a problem like
this, but nothing that I can say "this is how we fix the problem"
I can't believe that the problem is with the raid striping as I would
then expect loads of people to be reporting this.
Trevor
Saturday, February 25, 2012
Backup of active log after db failure
Greetings,
I am seeking information related to this subject.
I am seeking information related to this subject.
BOL suggests backing up the active transaction log immediately after a
failure, so that the backup can be used in a recovery scenario if necessary.
This is the relevant text from BOL "Transaction Log Backup":
--//
The transaction log backup created at 8:00 P.M. contains transaction log
records from 4:00 P.M. through 8:00 P.M., spanning the time when the
database backup was created at 6:00 P.M. The sequence of transaction log
backups is continuous from the initial database backup created at 8:00 A.M.
to the last transaction log backup created at 8:00 P.M. The following
procedures can be used to restore the database to its state at 10:00 P.M.
(point of failure).
Restore the database using the last database backup created.
1.. Create a backup of the currently active transaction log.
--// end of excerpt
If the failure results in loss of the instance and/or the log's parent db,
how would we back up that log? Even if we could, what confidence would we
have that the backed up log was not corrupted at failure time?
Thanks,
PSGThe standard configuration when loss of transactions is unacceptable:
put the log disk on a separate disk from the data. And mirror the log disk.
You would have to trust (hope) that your log disk didn't suffer loss of
data. That's the point of the mirroring.
If the instance was lost, then you'd have to rebuild/restore master before
you'd be able to issue the
BACKUP LOG WITH NO_TRUNCATE to harvest the tail of the log.
"JustaCowboy" <ppgoodingNOSPAM@.rocketmail.com> wrote in message
news:blj1rd$v1g$0@.pita.alt.net...
<snip
> If the failure results in loss of the instance and/or the log's parent db,
> how would we back up that log? Even if we could, what confidence would we
> have that the backed up log was not corrupted at failure time?
> Thanks,
> PSG|||"Anonymous" <noone@.anon.com> wrote in message
news:3f7f1411$1@.news.microsoft.com...
> The standard configuration when loss of transactions is unacceptable:
> put the log disk on a separate disk from the data. And mirror the log
disk.
Yes.
> You would have to trust (hope) that your log disk didn't suffer loss of
> data. That's the point of the mirroring.
Yes.
> If the instance was lost, then you'd have to rebuild/restore master before
> you'd be able to issue the
> BACKUP LOG WITH NO_TRUNCATE to harvest the tail of the log.
Will SQL backup an unattached log file? If not, to what is the harvested
log attached? I am having trouble understanding the steps that would lead
to being able to backup that log unless SQL will back it up unattached.
Has anyone actually tried the scenario described in BOL?
A local has suggested to me that we frequently back up the log and copy the
backups (along with the preceeding full db backup) to disk or device
external to the subject server. If this log backup/copy were done every 15
mins, then presumably no more than 15 mins' work would be lost after a
failure. Our assumption right now is that the BOL suggestion just won't
work. We'd try simulating it if we thought the simulation were valid, but
that seems very doubtful.
thanks,
PG
Friday, February 10, 2012
Backup hangs in wait-state MSSEARCH
I'm experiencing a very annoying failure when trying to do a backup - I hope you can help where others (including me) have failed.
The setup is a SharePoint Portal Server 2003 version 11.0.8126.0 running on Windows server 2003 Standard edition Service pack 1; it has a SQL-server 2005 version 9.0.2047 running on Windows server 2003 Standard edition Service pack as a back-end.
When I issue a backup of the SharePoint database XXX_SITE which holds round 4 gb. of data (mainly documents) the backup process hangs with a Wait Type 'MSSEARCH', it makes no difference whether I issue it as a single job, or through a maintenance plan.
I tried to stop the MsSearch service on the SharePoint-server, and disabled the Full-Text search on the database but it makes no difference.
The only way I can get a backup is to reboot the server on which SQL-server resides (restarting the SQL-server makes it rather unstable), and do a manual back-up shortly after.
Kim,
This is very likely a bug. I suggest you contact Microsoft support.
Regards,
Matt Hollingsworth
Sr. Program Manager
Microsoft SQL Server
|||That wait state indicates problems communicating between SQL and the MSSEARCH service via the COM interface.
There were several known problems that would lead to this situation which have been fixed in SP1, so that might be of help.
The quick solution is to cycle the MSSEARCH service, which should get backups moving.
If you're on SP1, and have this problem frequently, we can work with you to see what's going on. Event Viewer events about COM errors would be the first place to look.
|||Hi Kevin,
We are on SP1, and the oddest thing is that I've disabled MSSEARCH (stopped the process) on the SharePoint server.
The Occurrence is highly frequent i.e. if there has been any user activity on the database the Backup hangs.
|||I'm assuming that you have fulltext catalogs in your database?
So, for SQL 2005, the service is msftesql. If it is disabled, the backup should ignore the fulltext catalogs.
If we can get a dump for sqlservr.exe and msftesql.exe process together with all errorlog and SQLFT*.LOG file, I can take a look and see what is the problem. Feel free to contact me offline @. kevin.farlee@.microsoft.com
Hi! Yes we did have full text catalogues in the database, but since I had disabled full text search for the database, and disabled msftesql, I didn't suspect them. I got however an article from Microsoft support, showing me how I could test for catalogues not properly removed. So I discovered that there still existed an old catalogue, which I ,after and only after re-enabling full text search, were able to delete, since then my backup has worked :0}
Thanks for the all the good will shown in these forums.
Kim Brandt Jensen
Backup hangs in wait-state MSSEARCH
I'm experiencing a very annoying failure when trying to do a backup - I hope you can help where others (including me) have failed.
The setup is a SharePoint Portal Server 2003 version 11.0.8126.0 running on Windows server 2003 Standard edition Service pack 1; it has a SQL-server 2005 version 9.0.2047 running on Windows server 2003 Standard edition Service pack as a back-end.
When I issue a backup of the SharePoint database XXX_SITE which holds round 4 gb. of data (mainly documents) the backup process hangs with a Wait Type 'MSSEARCH', it makes no difference whether I issue it as a single job, or through a maintenance plan.
I tried to stop the MsSearch service on the SharePoint-server, and disabled the Full-Text search on the database but it makes no difference.
The only way I can get a backup is to reboot the server on which SQL-server resides (restarting the SQL-server makes it rather unstable), and do a manual back-up shortly after.
Kim,
This is very likely a bug. I suggest you contact Microsoft support.
Regards,
Matt Hollingsworth
Sr. Program Manager
Microsoft SQL Server
|||That wait state indicates problems communicating between SQL and the MSSEARCH service via the COM interface.
There were several known problems that would lead to this situation which have been fixed in SP1, so that might be of help.
The quick solution is to cycle the MSSEARCH service, which should get backups moving.
If you're on SP1, and have this problem frequently, we can work with you to see what's going on. Event Viewer events about COM errors would be the first place to look.
|||Hi Kevin,
We are on SP1, and the oddest thing is that I've disabled MSSEARCH (stopped the process) on the SharePoint server.
The Occurrence is highly frequent i.e. if there has been any user activity on the database the Backup hangs.
|||I'm assuming that you have fulltext catalogs in your database?
So, for SQL 2005, the service is msftesql. If it is disabled, the backup should ignore the fulltext catalogs.
If we can get a dump for sqlservr.exe and msftesql.exe process together with all errorlog and SQLFT*.LOG file, I can take a look and see what is the problem. Feel free to contact me offline @. kevin.farlee@.microsoft.com
Hi! Yes we did have full text catalogues in the database, but since I had disabled full text search for the database, and disabled msftesql, I didn't suspect them. I got however an article from Microsoft support, showing me how I could test for catalogues not properly removed. So I discovered that there still existed an old catalogue, which I ,after and only after re-enabling full text search, were able to delete, since then my backup has worked :0}
Thanks for the all the good will shown in these forums.
Kim Brandt Jensen
Subscribe to:
Posts (Atom)