Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.

Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.

Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.

OT: SQL Data on RAID5 Missing disk?

My SQL data is on a software Raid5 partition on my Windows Server 2003
Enterprise.
It appears one of my disks is not coming up and another disk is reported as
"Missing" so my SQL data is not being found!!
There is another disk that nows shows up as Unknown and uninitialized. Is
this my "missing" disk?
How do I recover from this without losing my data on the Raid?
Any help would be appreciated!!!
Attached is what it looks like in Disk Manager.
I tried to "Reactivate" the "Missing" disk but that didn't work and a bunch
of events in the System log
"The device detected a controller error on \device\harddisk1".
And there are alos informational events in the system log for harddisk3 such
as:
"dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
So it appears that one drive is just physcially dead I guess and another is
corrupted.
I'm starting to panick because our backups the the SQL are old...
Is there any safe way to recover from this situation?
"news.microsoft.com" <junk@.ijunk.com> wrote in message
news:eDwuW39DHHA.3520@.TK2MSFTNGP04.phx.gbl...
> My SQL data is on a software Raid5 partition on my Windows Server 2003
> Enterprise.
> It appears one of my disks is not coming up and another disk is reported
> as
> "Missing" so my SQL data is not being found!!
> There is another disk that nows shows up as Unknown and uninitialized. Is
> this my "missing" disk?
> How do I recover from this without losing my data on the Raid?
> Any help would be appreciated!!!
>
|||"news.microsoft.com" <junk@.ijunk.com> wrote in message
news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
> Attached is what it looks like in Disk Manager.
> I tried to "Reactivate" the "Missing" disk but that didn't work and a
> bunch of events in the System log
> "The device detected a controller error on \device\harddisk1".
> And there are alos informational events in the system log for harddisk3
> such as:
> "dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
> So it appears that one drive is just physcially dead I guess and another
> is corrupted.
> I'm starting to panick because our backups the the SQL are old...
> Is there any safe way to recover from this situation?
>
You full database backups are old. Do you have an unbroken sequence of
transaction log backups dating from your last full backup?
David
|||I don't think so, but I'm not sure.
The Raid that failed is my D drive where my SQL database was located.
Are you saying there might be transaction logs on the C drive somewhere?
The last backup was months ago.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ujOYcT%23DHHA.4680@.TK2MSFTNGP04.phx.gbl...
>
> "news.microsoft.com" <junk@.ijunk.com> wrote in message
> news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
> You full database backups are old. Do you have an unbroken sequence of
> transaction log backups dating from your last full backup?
> David
|||>>> On 11/24/2006 at 9:50 AM, in message
<#AWC5i#DHHA.4464@.TK2MSFTNGP06.phx.gbl>,
news.microsoft.com<junk@.ijunk.com> wrote:
> I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was
> located.
> Are you saying there might be transaction logs on the C drive
> somewhere?
> The last backup was months ago.
If the data truly is important, don't risk screwing things up more by
trying things yourself unless you are *extremely* confident in your own
skills.
This is the time to pay someone who knows what they are doing.
Otherwise you risk turning a possibly recoverable scenario into a
non-recoverable scenario.
MS PSS might be a good place to start, as might your hardware vendor.
Make sure you explain that you don't have backups, so you want to be
very careful not to do anything that is likely to cause more problems.
|||Check Tibor's article:
Minimizing data loss when accidents happens
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"news.microsoft.com" <junk@.ijunk.com> wrote in message
news:%23AWC5i%23DHHA.4464@.TK2MSFTNGP06.phx.gbl...
>I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was located.
> Are you saying there might be transaction logs on the C drive somewhere?
> The last backup was months ago.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:ujOYcT%23DHHA.4680@.TK2MSFTNGP04.phx.gbl...
>
|||news.microsoft.com wrote:
> I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was located.
> Are you saying there might be transaction logs on the C drive somewhere?
> The last backup was months ago.
>
Painful as it may be, I suggest you first get someone on site who knows
what they are doing. Obviously a lot of damage has already been done
and now your data is at risk.
Except for a read-only database, RAID5 is not a great choice for a
database server because of the performance and availability
constraints.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Well, I physically went to the server and found one disk on boot sounded
like two stones grinding on each other at 10k RPM...
The other failed disk just says "Media Error" on the Adeptec BIOS boot
discovery.
If I could find some temporary way of getting this drive to work for like 10
minutes then I could copy out the database.
Any ideas?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1164397255.045467.37770@.f16g2000cwb.googlegro ups.com...
> news.microsoft.com wrote:
> Painful as it may be, I suggest you first get someone on site who knows
> what they are doing. Obviously a lot of damage has already been done
> and now your data is at risk.
> Except for a read-only database, RAID5 is not a great choice for a
> database server because of the performance and availability
> constraints.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

OT: SQL Data on RAID5 Missing disk?

My SQL data is on a software Raid5 partition on my Windows Server 2003
Enterprise.
It appears one of my disks is not coming up and another disk is reported as
"Missing" so my SQL data is not being found!!
There is another disk that nows shows up as Unknown and uninitialized. Is
this my "missing" disk?
How do I recover from this without losing my data on the Raid'
Any help would be appreciated!!!"news.microsoft.com" <junk@.ijunk.com> wrote in message
news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
> Attached is what it looks like in Disk Manager.
> I tried to "Reactivate" the "Missing" disk but that didn't work and a
> bunch of events in the System log
> "The device detected a controller error on \device\harddisk1".
> And there are alos informational events in the system log for harddisk3
> such as:
> "dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
> So it appears that one drive is just physcially dead I guess and another
> is corrupted.
> I'm starting to panick because our backups the the SQL are old...
> Is there any safe way to recover from this situation'
>
You full database backups are old. Do you have an unbroken sequence of
transaction log backups dating from your last full backup?
David|||I don't think so, but I'm not sure.
The Raid that failed is my D drive where my SQL database was located.
Are you saying there might be transaction logs on the C drive somewhere?
The last backup was months ago.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ujOYcT%23DHHA.4680@.TK2MSFTNGP04.phx.gbl...
>
> "news.microsoft.com" <junk@.ijunk.com> wrote in message
> news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
>> Attached is what it looks like in Disk Manager.
>> I tried to "Reactivate" the "Missing" disk but that didn't work and a
>> bunch of events in the System log
>> "The device detected a controller error on \device\harddisk1".
>> And there are alos informational events in the system log for harddisk3
>> such as:
>> "dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
>> So it appears that one drive is just physcially dead I guess and another
>> is corrupted.
>> I'm starting to panick because our backups the the SQL are old...
>> Is there any safe way to recover from this situation'
> You full database backups are old. Do you have an unbroken sequence of
> transaction log backups dating from your last full backup?
> David|||> Are you saying there might be transaction logs on the C drive somewhere?
It depends on where you decided to store the transaction log file(s) for the database. With a (IMO)
sound backup strategy:
Backup database regularly, say every day.
Backup the transaction log regularly, say every hour.
You would now do the "final" log backup, assuming the transaction log file(s), the ldf file, for the
database is available. BACKUP LOG using the NO_TRUNCATE option.
Then restore the most recent database backup and all subsequent log backups. If you managed to do
the "final" log backup, you have zero data loss.
Assuming that your most recent backup is indeed one month ago:
My guess is that you don't do regular log backups. I also guess that the database in question is in
simple recovery mode (otherwise you will have large ldf file(s) for the database).
Based on above: If you cannot get the disk back, you will only get to the one month old backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"news.microsoft.com" <junk@.ijunk.com> wrote in message
news:%23AWC5i%23DHHA.4464@.TK2MSFTNGP06.phx.gbl...
>I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was located.
> Are you saying there might be transaction logs on the C drive somewhere?
> The last backup was months ago.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
> news:ujOYcT%23DHHA.4680@.TK2MSFTNGP04.phx.gbl...
>>
>> "news.microsoft.com" <junk@.ijunk.com> wrote in message
>> news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
>> Attached is what it looks like in Disk Manager.
>> I tried to "Reactivate" the "Missing" disk but that didn't work and a bunch of events in the
>> System log
>> "The device detected a controller error on \device\harddisk1".
>> And there are alos informational events in the system log for harddisk3 such as:
>> "dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
>> So it appears that one drive is just physcially dead I guess and another is corrupted.
>> I'm starting to panick because our backups the the SQL are old...
>> Is there any safe way to recover from this situation'
>>
>> You full database backups are old. Do you have an unbroken sequence of transaction log backups
>> dating from your last full backup?
>> David
>|||>> On 11/24/2006 at 9:50 AM, in message
<#AWC5i#DHHA.4464@.TK2MSFTNGP06.phx.gbl>,
news.microsoft.com<junk@.ijunk.com> wrote:
> I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was
> located.
> Are you saying there might be transaction logs on the C drive
> somewhere?
> The last backup was months ago.
If the data truly is important, don't risk screwing things up more by
trying things yourself unless you are *extremely* confident in your own
skills.
This is the time to pay someone who knows what they are doing.
Otherwise you risk turning a possibly recoverable scenario into a
non-recoverable scenario.
MS PSS might be a good place to start, as might your hardware vendor.
Make sure you explain that you don't have backups, so you want to be
very careful not to do anything that is likely to cause more problems.|||Check Tibor's article:
Minimizing data loss when accidents happens
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"news.microsoft.com" <junk@.ijunk.com> wrote in message
news:%23AWC5i%23DHHA.4464@.TK2MSFTNGP06.phx.gbl...
>I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was located.
> Are you saying there might be transaction logs on the C drive somewhere?
> The last backup was months ago.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:ujOYcT%23DHHA.4680@.TK2MSFTNGP04.phx.gbl...
>>
>> "news.microsoft.com" <junk@.ijunk.com> wrote in message
>> news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
>> Attached is what it looks like in Disk Manager.
>> I tried to "Reactivate" the "Missing" disk but that didn't work and a
>> bunch of events in the System log
>> "The device detected a controller error on \device\harddisk1".
>> And there are alos informational events in the system log for harddisk3
>> such as:
>> "dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
>> So it appears that one drive is just physcially dead I guess and another
>> is corrupted.
>> I'm starting to panick because our backups the the SQL are old...
>> Is there any safe way to recover from this situation'
>>
>> You full database backups are old. Do you have an unbroken sequence of
>> transaction log backups dating from your last full backup?
>> David
>|||news.microsoft.com wrote:
> I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was located.
> Are you saying there might be transaction logs on the C drive somewhere?
> The last backup was months ago.
>
Painful as it may be, I suggest you first get someone on site who knows
what they are doing. Obviously a lot of damage has already been done
and now your data is at risk.
Except for a read-only database, RAID5 is not a great choice for a
database server because of the performance and availability
constraints.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Well, I physically went to the server and found one disk on boot sounded
like two stones grinding on each other at 10k RPM...
The other failed disk just says "Media Error" on the Adeptec BIOS boot
discovery.
If I could find some temporary way of getting this drive to work for like 10
minutes then I could copy out the database.
Any ideas?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1164397255.045467.37770@.f16g2000cwb.googlegroups.com...
> news.microsoft.com wrote:
>> I don't think so, but I'm not sure.
>> The Raid that failed is my D drive where my SQL database was located.
>> Are you saying there might be transaction logs on the C drive somewhere?
>> The last backup was months ago.
>>
> Painful as it may be, I suggest you first get someone on site who knows
> what they are doing. Obviously a lot of damage has already been done
> and now your data is at risk.
> Except for a read-only database, RAID5 is not a great choice for a
> database server because of the performance and availability
> constraints.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

os partition problems

Hi,
I recently installed MSSQL 2000 and sp3a onto a windows 2003 server in a test lab. I configured one big c: partion on this os and installed the db in the default location.

I need to detach the db's on this server and re-attach them onto another MSSQL 2000/sp3a server running 2003 os with a partition scheme like this:

c: = 20 gigs for the os
e: = 600 gigs for the data

I could not re-attach the db's onto the e:\default path\to\database

Is there a work-around for this? This makes sense to me as to why it is not working and was an install oversight on my part but there has to be a way to overcome this delima?

Thanks in advance,
damnitYou can change the location of the files.

EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'