BACKUP failed to complete the command sp_prepexec;1

One of my SQL Servers (SQL 2000 SP4) is reporting this error in the Maintenance Plan. But shortly after it shows this error is does successfully backup my Databases (per log history), however the plan is indicating "failure".

I have no idea how to resolve this nor why it is happening.

Rob.

Hi,

it seems, that an t-sql statement is blocking.

Use the profiler and filter by object name = sp_prepexec and SPID.

actual in the KB:

HOW TO: Troubleshoot Application Performance Issues

How to monitor SQL Server 2000 blocking

tosc

BACKUP failed to complete the command sp_prepexec;1

One of my SQL Servers (SQL 2000 SP4) is reporting this error in the Maintenance Plan. But shortly after it shows this error is does successfully backup my Databases (per log history), however the plan is indicating "failure".

I have no idea how to resolve this nor why it is happening.

Rob.

Hi,

it seems, that an t-sql statement is blocking.

Use the profiler and filter by object name = sp_prepexec and SPID.

actual in the KB:

HOW TO: Troubleshoot Application Performance Issues

How to monitor SQL Server 2000 blocking

tosc

Backup Error: 18210, Severity: 16, State: 1

Getting errors on my backups, SQL 2005 SP2. Backups do complete successfully
however I'm getting errors in my App logs on my SQL Server.
During the snapshot part of the backups, these for lines appear in my SQL
Logs:
03/17/2008 11:36:49,spid75,Unknown,Error: 18210<c/> Severity: 16<c/> State: 1.
03/17/2008 11:36:49,spid75,Unknown,BackupVirtualDeviceFile::PrepareToFreeze:
failure on backup device '{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1'.
Operating system error 995(The I/O operation has been aborted because of
either a thread exit or an application request.).
03/17/2008 11:36:49,Backup,Unknown,Error: 3041<c/> Severity: 16<c/> State: 1.
03/17/2008 11:36:49,Backup,Unknown,BACKUP failed to complete the command
BACKUP DATABASE SMS_DA1. Check the backup application log for detailed
messages.
These corresponding events appeared in the App log on the server:
Type: Error
Source: SQLVDI
Event ID: 1
Event Time: 3/17/2008 11:36:49 AM
User: n/a
Computer: SERVERNAME
Description:
SQLVDI: Loc=WaitForResource. Desc=Partner process aborted. ErrorCode=(0).
Process=2288. Thread=4324. Server. Instance=MSSQLSERVER.
VD=Global\{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1_SQLVDIMemoryName_0.
Type: Error
Source: SQLVDI
Event ID: 1
Event Time: 3/17/2008 11:36:49 AM
User: n/a
Computer: SERVERNAME
Description:
SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0). Process=2288.
Thread=4324. Server. Instance=MSSQLSERVER.
VD=Global\{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1_SQLVDIMemoryName_0.
Type: Error
Source: MSSQLSERVER
Event ID: 18210
Event Time: 3/17/2008 11:36:49 AM
User: NT AUTHORITY\SYSTEM
Computer: SERVERNAME
Description:
BackupVirtualDeviceFile::PrepareToFreeze: failure on backup device
'{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1'. Operating system error 995(The I/O
operation has been aborted because of either a thread exit or an application
request.).
Type: Error
Source: MSSQLSERVER
Event ID: 3041
Event Time: 3/17/2008 11:36:49 AM
User: NT AUTHORITY\SYSTEM
Computer: SERVERNAME
Description:
BACKUP failed to complete the command BACKUP DATABASE SMS_DA1. Check the
backup application log for detailed messages.
Type: Error
Source: SQLWRITER
Event ID: 24583
Event Time: 3/17/2008 11:36:49 AM
User: n/a
Computer: SERVERNAME
Description:
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14.
SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft SQL Native Client
Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3271
Error state: 1, Severity: 16
Source: Microsoft SQL Native Client
Error message: A nonrecoverable I/O error occurred on file
"{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1:" 995(The I/O operation has been
aborted because of either a thread exit or an application request.).
Type: Error
Source: SQLVDI
Event ID: 1
Event Time: 3/17/2008 11:36:49 AM
User: n/a
Computer: SERVERNAME
Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0).
Process=2876. Thread=29988. Client. Instance=.
VD=Global\{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1_SQLVDIMemoryName_0.
Should I be concerned with these errors?
--
Sandy Wood
Orange County District AttorneyIs there any info in the event log that would give you any more info?
Did you have this issue prior to SP2?
Have you recently made any changes to the databases?
Are you using any 3rd party backup tools?
--
~lb
"Sandy Wood" wrote:
> Getting errors on my backups, SQL 2005 SP2. Backups do complete successfully
> however I'm getting errors in my App logs on my SQL Server.
> During the snapshot part of the backups, these for lines appear in my SQL
> Logs:
> 03/17/2008 11:36:49,spid75,Unknown,Error: 18210<c/> Severity: 16<c/> State: 1.
> 03/17/2008 11:36:49,spid75,Unknown,BackupVirtualDeviceFile::PrepareToFreeze:
> failure on backup device '{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1'.
> Operating system error 995(The I/O operation has been aborted because of
> either a thread exit or an application request.).
> 03/17/2008 11:36:49,Backup,Unknown,Error: 3041<c/> Severity: 16<c/> State: 1.
> 03/17/2008 11:36:49,Backup,Unknown,BACKUP failed to complete the command
> BACKUP DATABASE SMS_DA1. Check the backup application log for detailed
> messages.
> These corresponding events appeared in the App log on the server:
> Type: Error
> Source: SQLVDI
> Event ID: 1
> Event Time: 3/17/2008 11:36:49 AM
> User: n/a
> Computer: SERVERNAME
> Description:
> SQLVDI: Loc=WaitForResource. Desc=Partner process aborted. ErrorCode=(0).
> Process=2288. Thread=4324. Server. Instance=MSSQLSERVER.
> VD=Global\{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1_SQLVDIMemoryName_0.
> Type: Error
> Source: SQLVDI
> Event ID: 1
> Event Time: 3/17/2008 11:36:49 AM
> User: n/a
> Computer: SERVERNAME
> Description:
> SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0). Process=2288.
> Thread=4324. Server. Instance=MSSQLSERVER.
> VD=Global\{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1_SQLVDIMemoryName_0.
> Type: Error
> Source: MSSQLSERVER
> Event ID: 18210
> Event Time: 3/17/2008 11:36:49 AM
> User: NT AUTHORITY\SYSTEM
> Computer: SERVERNAME
> Description:
> BackupVirtualDeviceFile::PrepareToFreeze: failure on backup device
> '{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1'. Operating system error 995(The I/O
> operation has been aborted because of either a thread exit or an application
> request.).
> Type: Error
> Source: MSSQLSERVER
> Event ID: 3041
> Event Time: 3/17/2008 11:36:49 AM
> User: NT AUTHORITY\SYSTEM
> Computer: SERVERNAME
> Description:
> BACKUP failed to complete the command BACKUP DATABASE SMS_DA1. Check the
> backup application log for detailed messages.
> Type: Error
> Source: SQLWRITER
> Event ID: 24583
> Event Time: 3/17/2008 11:36:49 AM
> User: n/a
> Computer: SERVERNAME
> Description:
> Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr => 0x80040e14.
> SQLSTATE: 42000, Native Error: 3013
> Error state: 1, Severity: 16
> Source: Microsoft SQL Native Client
> Error message: BACKUP DATABASE is terminating abnormally.
> SQLSTATE: 42000, Native Error: 3271
> Error state: 1, Severity: 16
> Source: Microsoft SQL Native Client
> Error message: A nonrecoverable I/O error occurred on file
> "{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1:" 995(The I/O operation has been
> aborted because of either a thread exit or an application request.).
> Type: Error
> Source: SQLVDI
> Event ID: 1
> Event Time: 3/17/2008 11:36:49 AM
> User: n/a
> Computer: SERVERNAME
> Description:
> SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0).
> Process=2876. Thread=29988. Client. Instance=.
> VD=Global\{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1_SQLVDIMemoryName_0.
> Should I be concerned with these errors?
> --
> Sandy Wood
> Orange County District Attorney|||The event log information is in my previous post. The backup is being
controlled by my Microsoft System Center Configuration Manager 2007
installation. It has a built-in Task that runs the backup. I've not had this
problem until I upgraded my SMS 2003 installation to System Center
Configuration Manager 2007 which uses VSS for backups now. Something in how
VSS behaves that's causing the problems. Funny thing, the backups are
sucessfull and my data is there. It's just disconcerting to see all the
warnings and errors everyday.
--
Sandy Wood
Orange County District Attorney
"lonnye" wrote:
> Is there any info in the event log that would give you any more info?
> Did you have this issue prior to SP2?
> Have you recently made any changes to the databases?
> Are you using any 3rd party backup tools?
> --
> ~lb
>
> "Sandy Wood" wrote:
> > Getting errors on my backups, SQL 2005 SP2. Backups do complete successfully
> > however I'm getting errors in my App logs on my SQL Server.
> >
> > During the snapshot part of the backups, these for lines appear in my SQL
> > Logs:
> >
> > 03/17/2008 11:36:49,spid75,Unknown,Error: 18210<c/> Severity: 16<c/> State: 1.
> > 03/17/2008 11:36:49,spid75,Unknown,BackupVirtualDeviceFile::PrepareToFreeze:
> > failure on backup device '{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1'.
> > Operating system error 995(The I/O operation has been aborted because of
> > either a thread exit or an application request.).
> > 03/17/2008 11:36:49,Backup,Unknown,Error: 3041<c/> Severity: 16<c/> State: 1.
> > 03/17/2008 11:36:49,Backup,Unknown,BACKUP failed to complete the command
> > BACKUP DATABASE SMS_DA1. Check the backup application log for detailed
> > messages.
> >
> > These corresponding events appeared in the App log on the server:
> >
> > Type: Error
> > Source: SQLVDI
> > Event ID: 1
> > Event Time: 3/17/2008 11:36:49 AM
> > User: n/a
> > Computer: SERVERNAME
> > Description:
> > SQLVDI: Loc=WaitForResource. Desc=Partner process aborted. ErrorCode=(0).
> > Process=2288. Thread=4324. Server. Instance=MSSQLSERVER.
> > VD=Global\{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1_SQLVDIMemoryName_0.
> >
> > Type: Error
> > Source: SQLVDI
> > Event ID: 1
> > Event Time: 3/17/2008 11:36:49 AM
> > User: n/a
> > Computer: SERVERNAME
> > Description:
> > SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0). Process=2288.
> > Thread=4324. Server. Instance=MSSQLSERVER.
> > VD=Global\{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1_SQLVDIMemoryName_0.
> >
> > Type: Error
> > Source: MSSQLSERVER
> > Event ID: 18210
> > Event Time: 3/17/2008 11:36:49 AM
> > User: NT AUTHORITY\SYSTEM
> > Computer: SERVERNAME
> > Description:
> > BackupVirtualDeviceFile::PrepareToFreeze: failure on backup device
> > '{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1'. Operating system error 995(The I/O
> > operation has been aborted because of either a thread exit or an application
> > request.).
> >
> > Type: Error
> > Source: MSSQLSERVER
> > Event ID: 3041
> > Event Time: 3/17/2008 11:36:49 AM
> > User: NT AUTHORITY\SYSTEM
> > Computer: SERVERNAME
> > Description:
> > BACKUP failed to complete the command BACKUP DATABASE SMS_DA1. Check the
> > backup application log for detailed messages.
> >
> > Type: Error
> > Source: SQLWRITER
> > Event ID: 24583
> > Event Time: 3/17/2008 11:36:49 AM
> > User: n/a
> > Computer: SERVERNAME
> > Description:
> > Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr => > 0x80040e14.
> > SQLSTATE: 42000, Native Error: 3013
> > Error state: 1, Severity: 16
> > Source: Microsoft SQL Native Client
> > Error message: BACKUP DATABASE is terminating abnormally.
> > SQLSTATE: 42000, Native Error: 3271
> > Error state: 1, Severity: 16
> > Source: Microsoft SQL Native Client
> > Error message: A nonrecoverable I/O error occurred on file
> > "{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1:" 995(The I/O operation has been
> > aborted because of either a thread exit or an application request.).
> >
> > Type: Error
> > Source: SQLVDI
> > Event ID: 1
> > Event Time: 3/17/2008 11:36:49 AM
> > User: n/a
> > Computer: SERVERNAME
> > Description:
> > SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0).
> > Process=2876. Thread=29988. Client. Instance=.
> > VD=Global\{7EDC34FF-1A81-4635-8D71-99A7FCFA8CA8}1_SQLVDIMemoryName_0.
> >
> > Should I be concerned with these errors?
> >
> > --
> > Sandy Wood
> > Orange County District Attorney

Backup Dependent file remove

In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
would not delete files unless the backup completes successfully. If the
backup fails for some reason, you still have the previous backup.
How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
only way to do it ?
Thanks.
Hi
If you look a Maintenance Plans then the maintenance cleanup task can be set
up to remove files older than a given age from a given directory. This can be
dependent on a the successes of a backup database task, although this would
leave files until the backup problem is fixed and you could end up with weeks
of files from failed backups. If you are going to use maintenance tasks then
there was some issues with upgrading in SP2 so starting on SP2 will remove
any issues with upgrading.
John
"DXC" wrote:

> In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
> would not delete files unless the backup completes successfully. If the
> backup fails for some reason, you still have the previous backup.
> How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
> only way to do it ?
> Thanks.

Backup Dependent file remove

In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
would not delete files unless the backup completes successfully. If the
backup fails for some reason, you still have the previous backup.
How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
only way to do it ?
Thanks.Hi
If you look a Maintenance Plans then the maintenance cleanup task can be set
up to remove files older than a given age from a given directory. This can be
dependent on a the successes of a backup database task, although this would
leave files until the backup problem is fixed and you could end up with weeks
of files from failed backups. If you are going to use maintenance tasks then
there was some issues with upgrading in SP2 so starting on SP2 will remove
any issues with upgrading.
John
"DXC" wrote:
> In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
> would not delete files unless the backup completes successfully. If the
> backup fails for some reason, you still have the previous backup.
> How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
> only way to do it ?
> Thanks.

BACKUP DATABASE successfully processed pages - MB/sec formula?

I am trying to find out how SQL is generating this result below
of 14.122 MB/sec? What is the formula being used?
If I do the math myself, I am doing the following:
2247732 x 8KB (since a page in SQL is 8KB) = 17981856KB
To make 17981856KB into a MB / 1024 = 17560.40625MB
17560.40625MB in 1303.799 seconds means divide these two numbers
to get the value of 13.47 MB/sec!
So what am I doing wrong here or what am I missing?
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 2247728 pages for database 'TestDB', file 'TestDB_Data' on file 1.
100 percent processed.
Processed 4 pages for database 'TestDB', file 'TestDB_Log' on file 1.
BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
(14.122 MB/sec).
Is the formula I am using wrong?
Thank you
>I am trying to find out how SQL is generating this result below
> of 14.122 MB/sec? What is the formula being used?
pages * page_size / seconds:
SELECT 2247732 * 8192.0 / 1303.799

> snip <
> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
> (14.122 MB/sec).
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:6204ABB3-D51F-4E1E-BB1E-E56547DBD75E@.microsoft.com...
>I am trying to find out how SQL is generating this result below
> of 14.122 MB/sec? What is the formula being used?
> If I do the math myself, I am doing the following:
> 2247732 x 8KB (since a page in SQL is 8KB) = 17981856KB
> To make 17981856KB into a MB / 1024 = 17560.40625MB
> 17560.40625MB in 1303.799 seconds means divide these two numbers
> to get the value of 13.47 MB/sec!
> So what am I doing wrong here or what am I missing?
> 10 percent processed.
> 20 percent processed.
> 30 percent processed.
> 40 percent processed.
> 50 percent processed.
> 60 percent processed.
> 70 percent processed.
> 80 percent processed.
> 90 percent processed.
> Processed 2247728 pages for database 'TestDB', file 'TestDB_Data' on file
> 1.
> 100 percent processed.
> Processed 4 pages for database 'TestDB', file 'TestDB_Log' on file 1.
> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
> (14.122 MB/sec).
> Is the formula I am using wrong?
> Thank you
>
|||Thanks Dan, however I'm having difficulty figuring out if
you made a mistake or I still don't understand the formula?
How do you reach the number of 14.122 MB/sec?
[vbcol=seagreen]
> pages * page_size / seconds:
> SELECT 2247732 * 8192.0 / 1303.799
|||> Thanks Dan, however I'm having difficulty figuring out if
> you made a mistake or I still don't understand the formula?
> How do you reach the number of 14.122 MB/sec?
I used the decimal value of 1 million rather than 2^20:
SELECT CAST(2247732 * 8192.0 / 1303.799 / 1000000 AS decimal(10,3))
SELECT CAST(2247732 * 8192.0 / 1303.799 / 1048576 AS decimal(10,3))
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:D9047C22-1AF9-4493-8419-79DFAF0A157A@.microsoft.com...
> Thanks Dan, however I'm having difficulty figuring out if
> you made a mistake or I still don't understand the formula?
> How do you reach the number of 14.122 MB/sec?
>
>
|||>> How do you reach the number of 14.122 MB/sec?
> I used the decimal value of 1 million rather than 2^20:
> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1000000 AS decimal(10,3))
> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1048576 AS decimal(10,3))
I can see now the first giving a result of 14.123 and the second giving a
result
of 13.469.
How can there be a 1000 x 1000 mixed in numbers that should be bytes, i.e.
1024?
If we use 1,024 in one part of the calculation, shouldn't the other part be
also 1,024?
We're dealing with bytes here everywhere, no?
Then wouldn't this be a MS SQL bug then?
Thanks again!
|||> How can there be a 1000 x 1000 mixed in numbers that should be bytes, i.e.
> 1024?
Disk storage MB are usually measured in decimal rather than binary so one
can make the argument that throughput should also be expressed in decimal
MB.

> If we use 1,024 in one part of the calculation, shouldn't the other part
> be also 1,024?
> We're dealing with bytes here everywhere, no?
There is no place in the actual calculation where 1,024 is used. The value
of 8,192 bytes is the constant page size in bytes. The number of bytes
transferred is the same in both calculations (18,413,420,544); it is only
the denominator in the conversion of bytes to MB that differs:
SELECT 18413420544 / 1303.799 / 1000000
SELECT 18413420544 / 1303.799 / 1048576

> Then wouldn't this be a MS SQL bug then?
Not in my opinion.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:EAB6BC87-E7D8-45BA-A3EB-5BE230095FBE@.microsoft.com...
> I can see now the first giving a result of 14.123 and the second giving a
> result
> of 13.469.
> How can there be a 1000 x 1000 mixed in numbers that should be bytes, i.e.
> 1024?
> If we use 1,024 in one part of the calculation, shouldn't the other part
> be also 1,024?
> We're dealing with bytes here everywhere, no?
> Then wouldn't this be a MS SQL bug then?
> Thanks again!
>
|||> Disk storage MB are usually measured in decimal rather than binary so one
> can make the argument that throughput should also be expressed in decimal
> MB.

> it is only the denominator in the conversion of bytes to MB that differs:
Thanks Dan for the explanation, now I understand.

BACKUP DATABASE successfully processed pages - MB/sec formula?

I am trying to find out how SQL is generating this result below
of 14.122 MB/sec? What is the formula being used?
If I do the math myself, I am doing the following:
2247732 x 8KB (since a page in SQL is 8KB) = 17981856KB
To make 17981856KB into a MB / 1024 = 17560.40625MB
17560.40625MB in 1303.799 seconds means divide these two numbers
to get the value of 13.47 MB/sec!
So what am I doing wrong here or what am I missing?
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 2247728 pages for database 'TestDB', file 'TestDB_Data' on file 1.
100 percent processed.
Processed 4 pages for database 'TestDB', file 'TestDB_Log' on file 1.
BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
(14.122 MB/sec).
Is the formula I am using wrong?
Thank you>I am trying to find out how SQL is generating this result below
> of 14.122 MB/sec? What is the formula being used?
pages * page_size / seconds:
SELECT 2247732 * 8192.0 / 1303.799
> snip <
> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
> (14.122 MB/sec).
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:6204ABB3-D51F-4E1E-BB1E-E56547DBD75E@.microsoft.com...
>I am trying to find out how SQL is generating this result below
> of 14.122 MB/sec? What is the formula being used?
> If I do the math myself, I am doing the following:
> 2247732 x 8KB (since a page in SQL is 8KB) = 17981856KB
> To make 17981856KB into a MB / 1024 = 17560.40625MB
> 17560.40625MB in 1303.799 seconds means divide these two numbers
> to get the value of 13.47 MB/sec!
> So what am I doing wrong here or what am I missing?
> 10 percent processed.
> 20 percent processed.
> 30 percent processed.
> 40 percent processed.
> 50 percent processed.
> 60 percent processed.
> 70 percent processed.
> 80 percent processed.
> 90 percent processed.
> Processed 2247728 pages for database 'TestDB', file 'TestDB_Data' on file
> 1.
> 100 percent processed.
> Processed 4 pages for database 'TestDB', file 'TestDB_Log' on file 1.
> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
> (14.122 MB/sec).
> Is the formula I am using wrong?
> Thank you
>|||Thanks Dan, however I'm having difficulty figuring out if
you made a mistake or I still don't understand the formula?
How do you reach the number of 14.122 MB/sec?
> >I am trying to find out how SQL is generating this result below
>> of 14.122 MB/sec? What is the formula being used?
> pages * page_size / seconds:
> SELECT 2247732 * 8192.0 / 1303.799
>> snip <
>> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
>> (14.122 MB/sec).|||> Thanks Dan, however I'm having difficulty figuring out if
> you made a mistake or I still don't understand the formula?
> How do you reach the number of 14.122 MB/sec?
I used the decimal value of 1 million rather than 2^20:
SELECT CAST(2247732 * 8192.0 / 1303.799 / 1000000 AS decimal(10,3))
SELECT CAST(2247732 * 8192.0 / 1303.799 / 1048576 AS decimal(10,3))
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:D9047C22-1AF9-4493-8419-79DFAF0A157A@.microsoft.com...
> Thanks Dan, however I'm having difficulty figuring out if
> you made a mistake or I still don't understand the formula?
> How do you reach the number of 14.122 MB/sec?
>
>> >I am trying to find out how SQL is generating this result below
>> of 14.122 MB/sec? What is the formula being used?
>> pages * page_size / seconds:
>> SELECT 2247732 * 8192.0 / 1303.799
>> snip <
>> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
>> (14.122 MB/sec).
>|||>> How do you reach the number of 14.122 MB/sec?
> I used the decimal value of 1 million rather than 2^20:
> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1000000 AS decimal(10,3))
> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1048576 AS decimal(10,3))
I can see now the first giving a result of 14.123 and the second giving a
result
of 13.469.
How can there be a 1000 x 1000 mixed in numbers that should be bytes, i.e.
1024?
If we use 1,024 in one part of the calculation, shouldn't the other part be
also 1,024?
We're dealing with bytes here everywhere, no?
Then wouldn't this be a MS SQL bug then?
Thanks again!|||> How can there be a 1000 x 1000 mixed in numbers that should be bytes, i.e.
> 1024?
Disk storage MB are usually measured in decimal rather than binary so one
can make the argument that throughput should also be expressed in decimal
MB.
> If we use 1,024 in one part of the calculation, shouldn't the other part
> be also 1,024?
> We're dealing with bytes here everywhere, no?
There is no place in the actual calculation where 1,024 is used. The value
of 8,192 bytes is the constant page size in bytes. The number of bytes
transferred is the same in both calculations (18,413,420,544); it is only
the denominator in the conversion of bytes to MB that differs:
SELECT 18413420544 / 1303.799 / 1000000
SELECT 18413420544 / 1303.799 / 1048576
> Then wouldn't this be a MS SQL bug then?
Not in my opinion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:EAB6BC87-E7D8-45BA-A3EB-5BE230095FBE@.microsoft.com...
>> How do you reach the number of 14.122 MB/sec?
>> I used the decimal value of 1 million rather than 2^20:
>> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1000000 AS decimal(10,3))
>> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1048576 AS decimal(10,3))
> I can see now the first giving a result of 14.123 and the second giving a
> result
> of 13.469.
> How can there be a 1000 x 1000 mixed in numbers that should be bytes, i.e.
> 1024?
> If we use 1,024 in one part of the calculation, shouldn't the other part
> be also 1,024?
> We're dealing with bytes here everywhere, no?
> Then wouldn't this be a MS SQL bug then?
> Thanks again!
>|||> Disk storage MB are usually measured in decimal rather than binary so one
> can make the argument that throughput should also be expressed in decimal
> MB.
> it is only the denominator in the conversion of bytes to MB that differs:
Thanks Dan for the explanation, now I understand.

Backup check

Hello,
I want to create a stored procedure to
check if backups ended successfully at remote databases.
Which query can i run?
Many Thanks in Advance,
Galit.Gal
Assuming you are running your backups as jobs. The
information you need should be on sysjobhistory on the
msdb database. You can write a stored procedure to get the
information from there.
Regards
John