Showing posts with label separate. Show all posts
Showing posts with label separate. Show all posts

Monday, March 19, 2012

Data Partitioning (SQL Server 2005)

Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David

Data Partitioning (SQL Server 2005)

Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David

Data Partitioning (SQL Server 2005)

Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David

Thursday, March 8, 2012

Data mining licensing requirements

I am developing a data mining solution for a client using SQL 2005.

Do they have to purchase a separate license for a replicated environment? Like a hot site....

Thanks, ]

BI Joe

Data Mining is part of SQL Server and has the same licensing requirements. E.g. if it's on the web, than you need the per processor license, etc.

I'm not an expert on all of the licensing issues, but, in short, there's no difference between using the relational engine, reporting services, analysis services, etc - the licensing requirements are the same.

Saturday, February 25, 2012

Data lost after rebuilding new txn log after disk failure

Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UKJon
Have you performed LOG file backup?
When the disk is faild you was be able to run BACKUP LOG file WITH NO
TRUNCATE option. Restore the whole database and apply all log files with NO
RECOVERY option and the last one with RECOVERY option.
For more details you have to read BOL.
Sorry about losing the data.
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
pretty
> quickly by building a new log file for the DB. However, the last data in
the
> DB was written at 11:45. I'm not expecting to recover the data now but was
> confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
something
> here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||What exact steps did you take? How did you "build a new log file"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <jon@.pitstart.com> wrote in message news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB servers failed.
> The DB server was given separate physical disks to split the data and txn log files on different
> disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again pretty quickly by building a
> new log file for the DB. However, the last data in the DB was written at 11:45. I'm not expecting
> to recover the data now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto the point the DB failed
> because of the disk failure. Am I missing something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
Data are written only to the log upon commit and then written to data files
asynchronously. When you lose your log, the only way to guarantee a
consistent database is to restore from database backup and then apply
transaction log backups.
If you rebuilt your log using an unsupported method, run DBCCs to ensure the
database is physically consistent and be aware that logical consistency is
questionable. You may have lost committed data and may also have
uncommitted data in your database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
> pretty quickly by building a new log file for the DB. However, the last
> data in the DB was written at 11:45. I'm not expecting to recover the data
> now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||If you lost the disk with the active transaction log, then the only thing
you will be able to restore and recover is up to the last FULL and
DIFFERENTIAL database backups and the subsequent LOG transaction log
backups. You will have to recover off of the last LOG transaction log
backup. Only committed transactions are backed up from the log.
If the transaction log was still available, you could have backed that up
and recovered any committed transactions within that as well.
Regardless, uncommitted transactions will never be able to be recovered.
The system will rollback any uncommitted transactions as it goes through the
recovery process.
Sincerely,
Anthony Thomas
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK|||Thx for all your responses.
We rebuilt the log using the DBCC REBUILD_LOG command that needs the DB to
be put into "emergency mode" status
We got the DB back online OK which did not have data for the last 2-3 hours.
And yes - we had some data consistency problems on a number of tables which
we tried to fix using the DBCC fix commands. This fixed all the (mainly
allocation) errors on the tables except for something in one of the System
tables. This stopped certain queries running so I created a new DB and DTS
job to copy all schema and data from the corrupt DB into the new one which
worked OK.
We run a full backup of the DB and txn log each night. So, if I'd lost my
mdf/dat disk I'd be in a better position than losing the disk with the txn
log on.
I've worked with SQLServer since Version 6.0 and this is the first time I've
lost data but I'll certainly be re-thinking my DB set-up and bu strategy now
I think !
Rgds, Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ec9ZpPm8EHA.3592@.TK2MSFTNGP09.phx.gbl...
>> I thought the dat/mdf files should have contained nearly all the data
>> upto the point the DB failed because of the disk failure. Am I missing
>> something here?
> Data are written only to the log upon commit and then written to data
> files asynchronously. When you lose your log, the only way to guarantee a
> consistent database is to restore from database backup and then apply
> transaction log backups.
> If you rebuilt your log using an unsupported method, run DBCCs to ensure
> the database is physically consistent and be aware that logical
> consistency is questionable. You may have lost committed data and may
> also have uncommitted data in your database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon A" <jon@.pitstart.com> wrote in message
> news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Had a problem recently with some lost data after a disk in one of our DB
>> servers failed.
>> The DB server was given separate physical disks to split the data and txn
>> log files on different disks to guard against disk failure.
>> The disk with the txn log failed at 14:30. We got the DB online again
>> pretty quickly by building a new log file for the DB. However, the last
>> data in the DB was written at 11:45. I'm not expecting to recover the
>> data now but was confused to see the lost data.
>> I thought the dat/mdf files should have contained nearly all the data
>> upto the point the DB failed because of the disk failure. Am I missing
>> something here?
>> Is there anything I should be checking on the server ?
>> TIA,
>> Jon A
>> London, UK
>>
>

Friday, February 24, 2012

Data lost after rebuilding new txn log after disk failure

Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK
What exact steps did you take? How did you "build a new log file"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <jon@.pitstart.com> wrote in message news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB servers failed.
> The DB server was given separate physical disks to split the data and txn log files on different
> disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again pretty quickly by building a
> new log file for the DB. However, the last data in the DB was written at 11:45. I'm not expecting
> to recover the data now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto the point the DB failed
> because of the disk failure. Am I missing something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>
|||Jon
Have you performed LOG file backup?
When the disk is faild you was be able to run BACKUP LOG file WITH NO
TRUNCATE option. Restore the whole database and apply all log files with NO
RECOVERY option and the last one with RECOVERY option.
For more details you have to read BOL.
Sorry about losing the data.
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
pretty
> quickly by building a new log file for the DB. However, the last data in
the
> DB was written at 11:45. I'm not expecting to recover the data now but was
> confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
something
> here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>
|||> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
Data are written only to the log upon commit and then written to data files
asynchronously. When you lose your log, the only way to guarantee a
consistent database is to restore from database backup and then apply
transaction log backups.
If you rebuilt your log using an unsupported method, run DBCCs to ensure the
database is physically consistent and be aware that logical consistency is
questionable. You may have lost committed data and may also have
uncommitted data in your database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
> pretty quickly by building a new log file for the DB. However, the last
> data in the DB was written at 11:45. I'm not expecting to recover the data
> now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>
|||If you lost the disk with the active transaction log, then the only thing
you will be able to restore and recover is up to the last FULL and
DIFFERENTIAL database backups and the subsequent LOG transaction log
backups. You will have to recover off of the last LOG transaction log
backup. Only committed transactions are backed up from the log.
If the transaction log was still available, you could have backed that up
and recovered any committed transactions within that as well.
Regardless, uncommitted transactions will never be able to be recovered.
The system will rollback any uncommitted transactions as it goes through the
recovery process.
Sincerely,
Anthony Thomas

"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK
|||Thx for all your responses.
We rebuilt the log using the DBCC REBUILD_LOG command that needs the DB to
be put into "emergency mode" status
We got the DB back online OK which did not have data for the last 2-3 hours.
And yes - we had some data consistency problems on a number of tables which
we tried to fix using the DBCC fix commands. This fixed all the (mainly
allocation) errors on the tables except for something in one of the System
tables. This stopped certain queries running so I created a new DB and DTS
job to copy all schema and data from the corrupt DB into the new one which
worked OK.
We run a full backup of the DB and txn log each night. So, if I'd lost my
mdf/dat disk I'd be in a better position than losing the disk with the txn
log on.
I've worked with SQLServer since Version 6.0 and this is the first time I've
lost data but I'll certainly be re-thinking my DB set-up and bu strategy now
I think !
Rgds, Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ec9ZpPm8EHA.3592@.TK2MSFTNGP09.phx.gbl...
> Data are written only to the log upon commit and then written to data
> files asynchronously. When you lose your log, the only way to guarantee a
> consistent database is to restore from database backup and then apply
> transaction log backups.
> If you rebuilt your log using an unsupported method, run DBCCs to ensure
> the database is physically consistent and be aware that logical
> consistency is questionable. You may have lost committed data and may
> also have uncommitted data in your database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon A" <jon@.pitstart.com> wrote in message
> news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
>

Data lost after rebuilding new txn log after disk failure

Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UKWhat exact steps did you take? How did you "build a new log file"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <jon@.pitstart.com> wrote in message news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB s
ervers failed.
> The DB server was given separate physical disks to split the data and txn
log files on different
> disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again pret
ty quickly by building a
> new log file for the DB. However, the last data in the DB was written at 1
1:45. I'm not expecting
> to recover the data now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed
> because of the disk failure. Am I missing something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>[/vbcol]|||Jon
Have you performed LOG file backup?
When the disk is faild you was be able to run BACKUP LOG file WITH NO
TRUNCATE option. Restore the whole database and apply all log files with NO
RECOVERY option and the last one with RECOVERY option.
For more details you have to read BOL.
Sorry about losing the data.
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
pretty
> quickly by building a new log file for the DB. However, the last data in
the
> DB was written at 11:45. I'm not expecting to recover the data now but was
> confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
something
> here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
Data are written only to the log upon commit and then written to data files
asynchronously. When you lose your log, the only way to guarantee a
consistent database is to restore from database backup and then apply
transaction log backups.
If you rebuilt your log using an unsupported method, run DBCCs to ensure the
database is physically consistent and be aware that logical consistency is
questionable. You may have lost committed data and may also have
uncommitted data in your database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
> pretty quickly by building a new log file for the DB. However, the last
> data in the DB was written at 11:45. I'm not expecting to recover the data
> now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||If you lost the disk with the active transaction log, then the only thing
you will be able to restore and recover is up to the last FULL and
DIFFERENTIAL database backups and the subsequent LOG transaction log
backups. You will have to recover off of the last LOG transaction log
backup. Only committed transactions are backed up from the log.
If the transaction log was still available, you could have backed that up
and recovered any committed transactions within that as well.
Regardless, uncommitted transactions will never be able to be recovered.
The system will rollback any uncommitted transactions as it goes through the
recovery process.
Sincerely,
Anthony Thomas
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK|||Thx for all your responses.
We rebuilt the log using the DBCC REBUILD_LOG command that needs the DB to
be put into "emergency mode" status
We got the DB back online OK which did not have data for the last 2-3 hours.
And yes - we had some data consistency problems on a number of tables which
we tried to fix using the DBCC fix commands. This fixed all the (mainly
allocation) errors on the tables except for something in one of the System
tables. This stopped certain queries running so I created a new DB and DTS
job to copy all schema and data from the corrupt DB into the new one which
worked OK.
We run a full backup of the DB and txn log each night. So, if I'd lost my
mdf/dat disk I'd be in a better position than losing the disk with the txn
log on.
I've worked with SQLServer since Version 6.0 and this is the first time I've
lost data but I'll certainly be re-thinking my DB set-up and bu strategy now
I think !
Rgds, Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ec9ZpPm8EHA.3592@.TK2MSFTNGP09.phx.gbl...
> Data are written only to the log upon commit and then written to data
> files asynchronously. When you lose your log, the only way to guarantee a
> consistent database is to restore from database backup and then apply
> transaction log backups.
> If you rebuilt your log using an unsupported method, run DBCCs to ensure
> the database is physically consistent and be aware that logical
> consistency is questionable. You may have lost committed data and may
> also have uncommitted data in your database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon A" <jon@.pitstart.com> wrote in message
> news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
>

Data leaked a buffer with ID 1 of type 1

We are using a datareader component to retrieve data from a Pervasive 8.6 database. We have four separate datareader components in various packages retrieving data into our datawarehouse in SQL2005. One of the components has started to fail regularly with the following error.

Date 6/8/2007 3:05:00 AM
Log Job History (LoadMAXDailyBookings)

Step ID 1
Server US-CO-DEN-101
Job Name LoadMAXDailyBookings
Step Name Load Bookings Step
Duration 00:00:37
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Destination Write Bookings Detail" (121)" wrote 0 rows.
End Info
Log:
Name: PipelineBufferLeak
Computer: US-CO-DEN-101
Message: component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
End Log
Log:
Name: OnTaskFailed
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnPostExecute
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnWarning
Computer: US-CO-DEN-101
Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

End Log
Warning: 2007-06-08 03:05:36.92
Code: 0x80019002
Source: LoadMAXDailyBookings
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution...

The other components run without any problems as did this one up until we installed service pack 2. We then started getting these occasional failures. Any thoughts on what is happening here?

Thanks,

Phil

Do you have package logging turned on? The PipelineBufferLeak, I believe, is a warning, not an error, and hence would not stop execution.

Turning on package logging might help get a more clear error message....|||

I do have logging turned on and the error in the log is more vague than within the job history. I'm copying in the first rows indicating the error from the log. The execution is halted due to whatever error is happening.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookings,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

|||Make a copy of that package and strip everything out of the data flow except for the data reader source. Then hook it into a row count transformation. Re-run the package. What happens?|||The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.|||

pkdenver wrote:

The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.

The only reason I suggested this is because the error message returned is a DataReader failure message, so this takes out all processing that might unduly influence things. Can you use something other than a DataReader Source? OLE DB Source, for instance?|||

I see your point regarding isolating the datareader. As recommended last week, I deployed a package which performs the query and dumps to a rowcounter. We get the same failure as the regular job. Both jobs failed on one day (Sat). It ran fine on Sun and Mon. Here are the error rows from the logger: I'm wondering if we are getting a timeout on this query as it is a more complex query than the other three jobs running against the DB.

Regarding an alternate driver, the Pervasive OLE DB driver is very unstable in the SSIS environment and so it can't be used at all. We are looking at the timeout value to see if the Pervasive ODBC driver can be tweaked.

OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016268,0x,Execute phase is beginning.

OnPipelinePrePrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,PrimeOutput will be called on a component. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

OnPipelinePostPrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,A component has returned from its PrimeOutput call. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.

OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.

OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,50,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Post Execute
OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.

OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.

OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,50,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Cleanup
PipelineBufferLeak,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
OnTaskFailed,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnPostExecute,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnWarning,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

OnPostExecute,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
PackageEnd,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1,0x,End of package execution.

Sunday, February 19, 2012

Data in sysobject table

I understand that there are separate installables available for different languages (chinese, french etc.) in sql server. The question is, if I install sql server in say, chinese, does it mean that the schema catalog information stored in system tables (say sysobjects) will also be in chinese ? or the data rows in sysobjects is only in english.

Thanks for any helpEvery case I've ever seen has the system tables and columns named in English, although I've heard of a few (say maybe 3 out of several hundred thousand, over more than ten years) cases where the columns were named in the native language. All of those cases were more or less unbridled disasters, which I think were self-inflicted injuries (where someone had gone places and done things that they shouldn't).

-PatP