Question on Tranaction Isolation Level.

Source:
SQL2K
SP3
Destination:
SQL2K5
SP2
I have requirements to import data from the source to the destination, and
once on the destination the data will be read only. I also have the
situation where the data owners (not me) may make schema changes on the
source whenever they want to, and do not need to tell me. Most of the time
this would only be widening an existing column, so I'm not all that worried.
This data is only to be grabbed once a night during a slow period.
Options:
Replication: This is out because the data owner will want to widen a column
without needing to consult with me first (I would need to drop replication
first). I'm aware of the changes to 2005 replication, but they won't be
upgrading anytime soon.
DTS/ SSIS: This is out because they will not want to tell me if they widen a
column. Just because they widen the source, that doesn't help my
destination.
Because of all these requirements/ circumstances, I want to just create a
Linked Server and do a Select...Into every night and drop/ re-create the
table on the destination. This will make schema changes on the source
transparent to me. Of course though, I don't want my Select...Into to block
users on the source while the import is occurring. That being said, I was
thinking about setting the Transaction Isolation Level (TIL) to Read
Uncommitted (RU) in the Select...Into. I don't really care if the
consistency is off a bit as it's only updated once a day anyways. This is
how it would look:
Set Transaction Isolation Level Read Uncommitted
select * into T48
from myLinkedServer.database.dbo.T48
So my question is: Will setting the TIL to RU like this actually set it for
the source (the Linked Server) or the destination (my server)? If it does in
fact set it for the source, would NOLOCK work?
TIA, ChrisR1) Can't you use SSIS and force it to drop/create tables each night? That
would probably be most efficient mechanism.
2) You pick up performance if you set the database to ReadOnly when you are
done moving data.
3) I don't believe the set (or other hints) will translate over to the
linked server.
"ChrisR" <ChrisR@.foo.com> wrote in message
news:Oet8c6dAIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Source:
> SQL2K
> SP3
> Destination:
> SQL2K5
> SP2
> I have requirements to import data from the source to the destination, and
> once on the destination the data will be read only. I also have the
> situation where the data owners (not me) may make schema changes on the
> source whenever they want to, and do not need to tell me. Most of the time
> this would only be widening an existing column, so I'm not all that
> worried. This data is only to be grabbed once a night during a slow
> period.
> Options:
> Replication: This is out because the data owner will want to widen a
> column without needing to consult with me first (I would need to drop
> replication first). I'm aware of the changes to 2005 replication, but they
> won't be upgrading anytime soon.
> DTS/ SSIS: This is out because they will not want to tell me if they widen
> a column. Just because they widen the source, that doesn't help my
> destination.
>
> Because of all these requirements/ circumstances, I want to just create a
> Linked Server and do a Select...Into every night and drop/ re-create the
> table on the destination. This will make schema changes on the source
> transparent to me. Of course though, I don't want my Select...Into to
> block users on the source while the import is occurring. That being said,
> I was thinking about setting the Transaction Isolation Level (TIL) to Read
> Uncommitted (RU) in the Select...Into. I don't really care if the
> consistency is off a bit as it's only updated once a day anyways. This is
> how it would look:
> Set Transaction Isolation Level Read Uncommitted
> select * into T48
> from myLinkedServer.database.dbo.T48
> So my question is: Will setting the TIL to RU like this actually set it
> for the source (the Linked Server) or the destination (my server)? If it
> does in fact set it for the source, would NOLOCK work?
> TIA, ChrisR
>|||1) I don't think SSIS will widen the columns on the destination
automatically if they have been widened on the source (perhaps it can
somehow as I don't have a lot of SSIS experience?). Select...Into will take
care of this for me. Sure I can bury my Select...Into code in a SSIS
Package, but I don't think that's what you were getting at.
2) Not really an option, but thanks.
3) This is what I was afraid of.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13fq948dgolb0a5@.corp.supernews.com...
> 1) Can't you use SSIS and force it to drop/create tables each night? That
> would probably be most efficient mechanism.
> 2) You pick up performance if you set the database to ReadOnly when you
> are done moving data.
> 3) I don't believe the set (or other hints) will translate over to the
> linked server.
>
> "ChrisR" <ChrisR@.foo.com> wrote in message
> news:Oet8c6dAIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Source:
>> SQL2K
>> SP3
>> Destination:
>> SQL2K5
>> SP2
>> I have requirements to import data from the source to the destination,
>> and once on the destination the data will be read only. I also have the
>> situation where the data owners (not me) may make schema changes on the
>> source whenever they want to, and do not need to tell me. Most of the
>> time this would only be widening an existing column, so I'm not all that
>> worried. This data is only to be grabbed once a night during a slow
>> period.
>> Options:
>> Replication: This is out because the data owner will want to widen a
>> column without needing to consult with me first (I would need to drop
>> replication first). I'm aware of the changes to 2005 replication, but
>> they won't be upgrading anytime soon.
>> DTS/ SSIS: This is out because they will not want to tell me if they
>> widen a column. Just because they widen the source, that doesn't help my
>> destination.
>>
>> Because of all these requirements/ circumstances, I want to just create a
>> Linked Server and do a Select...Into every night and drop/ re-create the
>> table on the destination. This will make schema changes on the source
>> transparent to me. Of course though, I don't want my Select...Into to
>> block users on the source while the import is occurring. That being said,
>> I was thinking about setting the Transaction Isolation Level (TIL) to
>> Read Uncommitted (RU) in the Select...Into. I don't really care if the
>> consistency is off a bit as it's only updated once a day anyways. This is
>> how it would look:
>> Set Transaction Isolation Level Read Uncommitted
>> select * into T48
>> from myLinkedServer.database.dbo.T48
>> So my question is: Will setting the TIL to RU like this actually set it
>> for the source (the Linked Server) or the destination (my server)? If it
>> does in fact set it for the source, would NOLOCK work?
>> TIA, ChrisR
>|||One other approach would be to import into a permanent staging table -
perhaps in a staging database - that matches your data SOURCE which I
take it is not changing. Then use SQL to INSERT to the target table
from staging.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Sep 2007 07:49:13 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>Source:
>SQL2K
>SP3
>Destination:
>SQL2K5
>SP2
>I have requirements to import data from the source to the destination, and
>once on the destination the data will be read only. I also have the
>situation where the data owners (not me) may make schema changes on the
>source whenever they want to, and do not need to tell me. Most of the time
>this would only be widening an existing column, so I'm not all that worried.
>This data is only to be grabbed once a night during a slow period.
>Options:
>Replication: This is out because the data owner will want to widen a column
>without needing to consult with me first (I would need to drop replication
>first). I'm aware of the changes to 2005 replication, but they won't be
>upgrading anytime soon.
>DTS/ SSIS: This is out because they will not want to tell me if they widen a
>column. Just because they widen the source, that doesn't help my
>destination.
>
>Because of all these requirements/ circumstances, I want to just create a
>Linked Server and do a Select...Into every night and drop/ re-create the
>table on the destination. This will make schema changes on the source
>transparent to me. Of course though, I don't want my Select...Into to block
>users on the source while the import is occurring. That being said, I was
>thinking about setting the Transaction Isolation Level (TIL) to Read
>Uncommitted (RU) in the Select...Into. I don't really care if the
>consistency is off a bit as it's only updated once a day anyways. This is
>how it would look:
>Set Transaction Isolation Level Read Uncommitted
>select * into T48
>from myLinkedServer.database.dbo.T48
>So my question is: Will setting the TIL to RU like this actually set it for
>the source (the Linked Server) or the destination (my server)? If it does in
>fact set it for the source, would NOLOCK work?
>TIA, ChrisR
>|||I think what you are saying is to first do a Select...Into a local table as
opposed to directly into the Linked Server? If so, I was already thinking
the same thing. If not, could you please elaborate?
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:jvjqf39vh2kut6smef1rg2dj33crnkstij@.4ax.com...
> One other approach would be to import into a permanent staging table -
> perhaps in a staging database - that matches your data SOURCE which I
> take it is not changing. Then use SQL to INSERT to the target table
> from staging.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 28 Sep 2007 07:49:13 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>>Source:
>>SQL2K
>>SP3
>>Destination:
>>SQL2K5
>>SP2
>>I have requirements to import data from the source to the destination, and
>>once on the destination the data will be read only. I also have the
>>situation where the data owners (not me) may make schema changes on the
>>source whenever they want to, and do not need to tell me. Most of the time
>>this would only be widening an existing column, so I'm not all that
>>worried.
>>This data is only to be grabbed once a night during a slow period.
>>Options:
>>Replication: This is out because the data owner will want to widen a
>>column
>>without needing to consult with me first (I would need to drop replication
>>first). I'm aware of the changes to 2005 replication, but they won't be
>>upgrading anytime soon.
>>DTS/ SSIS: This is out because they will not want to tell me if they widen
>>a
>>column. Just because they widen the source, that doesn't help my
>>destination.
>>
>>Because of all these requirements/ circumstances, I want to just create a
>>Linked Server and do a Select...Into every night and drop/ re-create the
>>table on the destination. This will make schema changes on the source
>>transparent to me. Of course though, I don't want my Select...Into to
>>block
>>users on the source while the import is occurring. That being said, I was
>>thinking about setting the Transaction Isolation Level (TIL) to Read
>>Uncommitted (RU) in the Select...Into. I don't really care if the
>>consistency is off a bit as it's only updated once a day anyways. This is
>>how it would look:
>>Set Transaction Isolation Level Read Uncommitted
>>select * into T48
>>from myLinkedServer.database.dbo.T48
>>So my question is: Will setting the TIL to RU like this actually set it
>>for
>>the source (the Linked Server) or the destination (my server)? If it does
>>in
>>fact set it for the source, would NOLOCK work?
>>TIA, ChrisR|||On Fri, 28 Sep 2007 13:03:23 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>I think what you are saying is to first do a Select...Into a local table as
>opposed to directly into the Linked Server? If so, I was already thinking
>the same thing. If not, could you please elaborate?
You have a source table somewhere on another server, call it Source.
You have a target table on the target server, call it Target. Target
is subject to change, but Source is not. Create a permanent table
Target_Imported, on the target server. Put it in the same database as
Target, or in a staging database. Make the columns match the Target
table in name and order, but make the column data types match the
related column in Source. Since the types match importing from Source
into Target_Import using BCP, DTS, SSIS or even INSERT/SELECT from a
linked server should be straight forward. Because the column names
match, copying the data between Target_Imported and Target requires a
simple INSERT/SELECT. You could add WITH RECOMPILE to the stored
procedure that does the copy so that the sort of minor datatype
changes you describe are handled without intervention.
Hope that is clearer. I don't know that it is any better than what
you proposed, just another idea.
Roy Harvey
Beacon Falls, CT|||Thanks, but you have it backwards. The source can change at any time,
without any notice. Therefore, if the source changes, I want the target to
change as well. Hence, Select...Into.
Thanks again!
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:c2oqf3p37k0ieg5n6j34120j662a0e319a@.4ax.com...
> On Fri, 28 Sep 2007 13:03:23 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>>I think what you are saying is to first do a Select...Into a local table
>>as
>>opposed to directly into the Linked Server? If so, I was already thinking
>>the same thing. If not, could you please elaborate?
> You have a source table somewhere on another server, call it Source.
> You have a target table on the target server, call it Target. Target
> is subject to change, but Source is not. Create a permanent table
> Target_Imported, on the target server. Put it in the same database as
> Target, or in a staging database. Make the columns match the Target
> table in name and order, but make the column data types match the
> related column in Source. Since the types match importing from Source
> into Target_Import using BCP, DTS, SSIS or even INSERT/SELECT from a
> linked server should be straight forward. Because the column names
> match, copying the data between Target_Imported and Target requires a
> simple INSERT/SELECT. You could add WITH RECOMPILE to the stored
> procedure that does the copy so that the sort of minor datatype
> changes you describe are handled without intervention.
> Hope that is clearer. I don't know that it is any better than what
> you proposed, just another idea.
> Roy Harvey
> Beacon Falls, CT

Question on SQL

Good Day,
I have a simple question on SQL 2000. Please note that I am no expert and
this is a basic question.
We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a remote
site that connects to this server via a T1 line using a program that we
created called popss.
What happens is that the program takes a long time getting the data from the
server and we know that it's a bandwidth issue. What I would like to know
is there a way to install a toned down version of SQL something like desktop
on another Windows 2003 server or do we need the another full version of SQL
at that location so that the program can read from the local instance
instead of going over the pipe.
I am looking for a way to sync up the two SQL databases say at night this
way there we be no traffic over the pipe during the day.
Thank You
Adam RaffYou could use Transactional Replication and set its schedule as running once
at night. SQL Server Express Edition can be used as a Subscriber in a
Replication topology and it's free. However, Express Edition has its
limitations. (4GB db, 1 CPU, 1GB RAM)
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
You would make your current SQL Server as Publisher \ Distributor (consider
workload on your current server) and the other one would be the Subscriber.
So, your new SQL Server would be updated daily and your app would query your
local SQL Server directly.
Try this in a test environment first. If your system and app works without
any problem then you could apply it to your production system.
--
Ekrem Önsoy
"Adam Raff" <araff@.newsgroup.nospam> wrote in message
news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> Good Day,
> I have a simple question on SQL 2000. Please note that I am no expert and
> this is a basic question.
> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a
> remote site that connects to this server via a T1 line using a program
> that we created called popss.
> What happens is that the program takes a long time getting the data from
> the server and we know that it's a bandwidth issue. What I would like to
> know is there a way to install a toned down version of SQL something like
> desktop on another Windows 2003 server or do we need the another full
> version of SQL at that location so that the program can read from the
> local instance instead of going over the pipe.
> I am looking for a way to sync up the two SQL databases say at night this
> way there we be no traffic over the pipe during the day.
> Thank You
> Adam Raff
>|||Ekrem,
Thanks for your help. The size of the database is small about 500MB so that
is not an issue. My only other question on this is Does this go both ways.
Meaning when they copy to the SQL Express will it update the SQL server
later during the Replication time since the Express is setup as Subscriber?
Thanks again for your help
Adam Raff
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
> You could use Transactional Replication and set its schedule as running
> once at night. SQL Server Express Edition can be used as a Subscriber in a
> Replication topology and it's free. However, Express Edition has its
> limitations. (4GB db, 1 CPU, 1GB RAM)
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
> You would make your current SQL Server as Publisher \ Distributor
> (consider workload on your current server) and the other one would be the
> Subscriber. So, your new SQL Server would be updated daily and your app
> would query your local SQL Server directly.
> Try this in a test environment first. If your system and app works without
> any problem then you could apply it to your production system.
> --
> Ekrem Önsoy
>
> "Adam Raff" <araff@.newsgroup.nospam> wrote in message
> news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I have a simple question on SQL 2000. Please note that I am no expert
>> and this is a basic question.
>> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a
>> remote site that connects to this server via a T1 line using a program
>> that we created called popss.
>> What happens is that the program takes a long time getting the data from
>> the server and we know that it's a bandwidth issue. What I would like to
>> know is there a way to install a toned down version of SQL something like
>> desktop on another Windows 2003 server or do we need the another full
>> version of SQL at that location so that the program can read from the
>> local instance instead of going over the pipe.
>> I am looking for a way to sync up the two SQL databases say at night this
>> way there we be no traffic over the pipe during the day.
>> Thank You
>> Adam Raff
>|||Dear Adam,
Thank you for posting here.
I would like to explain that the SQL Server 2000 does not support the
updatable Subscriptions for Transactional Replication and the SQL Server
2005 Express 2005 Edition can only be configured as the Subscriber. So, the
replication is done in only one way and the data written in the SQL Express
will not be transferred to the main SQL Server.
If your application "Popss" only reads the data from SQL Server, we can
choose to use Transactional Replication. If not, we can try the Merge
Replication so that the changes can be synchronized to the publisher.
For your reference, I have included some relevant articles below:
How Merge Replication Works
http://technet.microsoft.com/en-us/library/ms151329.aspx
How Transactional Replication Works
http://technet.microsoft.com/en-us/library/ms151706.aspx
If anything is unclear in my reply, please don't hesitate to let me know
and I will be glad to help.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Adam Raff" <araff@.newsgroup.nospam>
| References: <OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl>
<11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com>
| Subject: Re: Question on SQL
| Date: Wed, 12 Sep 2007 16:40:10 -0400
| Lines: 62
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
| X-RFC2646: Format=Flowed; Response
| Message-ID: <OyQZd0X9HHA.1212@.TK2MSFTNGP05.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 208-39-138-189.isp.comcastbusiness.net 208.39.138.189
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:25271
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Ekrem,
|
| Thanks for your help. The size of the database is small about 500MB so
that
| is not an issue. My only other question on this is Does this go both
ways.
|
| Meaning when they copy to the SQL Express will it update the SQL server
| later during the Replication time since the Express is setup as
Subscriber?
|
| Thanks again for your help
| Adam Raff
|
|
|
| "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
| news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
| > You could use Transactional Replication and set its schedule as running
| > once at night. SQL Server Express Edition can be used as a Subscriber
in a
| > Replication topology and it's free. However, Express Edition has its
| > limitations. (4GB db, 1 CPU, 1GB RAM)
| >
| > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
| >
| > You would make your current SQL Server as Publisher \ Distributor
| > (consider workload on your current server) and the other one would be
the
| > Subscriber. So, your new SQL Server would be updated daily and your app
| > would query your local SQL Server directly.
| >
| > Try this in a test environment first. If your system and app works
without
| > any problem then you could apply it to your production system.
| >
| > --
| > Ekrem Önsoy
| >
| >
| >
| > "Adam Raff" <araff@.newsgroup.nospam> wrote in message
| > news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
| >> Good Day,
| >>
| >> I have a simple question on SQL 2000. Please note that I am no expert
| >> and this is a basic question.
| >>
| >> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a
| >> remote site that connects to this server via a T1 line using a program
| >> that we created called popss.
| >>
| >> What happens is that the program takes a long time getting the data
from
| >> the server and we know that it's a bandwidth issue. What I would like
to
| >> know is there a way to install a toned down version of SQL something
like
| >> desktop on another Windows 2003 server or do we need the another full
| >> version of SQL at that location so that the program can read from the
| >> local instance instead of going over the pipe.
| >>
| >> I am looking for a way to sync up the two SQL databases say at night
this
| >> way there we be no traffic over the pipe during the day.
| >>
| >> Thank You
| >> Adam Raff
| >>
| >
|
|
||||Hi Adams,
If we upgrade to SQL 2005 lets say or the newer version of SQL 2007 will
this then work then?
Is the Express version limited in that it can never sync up with the full
version of SQL?
Thanks
Adam Raff
"Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
news:WwE9G5d9HHA.5532@.TK2MSFTNGHUB02.phx.gbl...
> Dear Adam,
> Thank you for posting here.
> I would like to explain that the SQL Server 2000 does not support the
> updatable Subscriptions for Transactional Replication and the SQL Server
> 2005 Express 2005 Edition can only be configured as the Subscriber. So,
> the
> replication is done in only one way and the data written in the SQL
> Express
> will not be transferred to the main SQL Server.
> If your application "Popss" only reads the data from SQL Server, we can
> choose to use Transactional Replication. If not, we can try the Merge
> Replication so that the changes can be synchronized to the publisher.
> For your reference, I have included some relevant articles below:
> How Merge Replication Works
> http://technet.microsoft.com/en-us/library/ms151329.aspx
> How Transactional Replication Works
> http://technet.microsoft.com/en-us/library/ms151706.aspx
> If anything is unclear in my reply, please don't hesitate to let me know
> and I will be glad to help.
> Have a nice day!
> Best regards,
> Adams Qu, MCSE, MCDBA, MCTS
> Microsoft Online Support
> Microsoft Global Technical Support Center
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> | From: "Adam Raff" <araff@.newsgroup.nospam>
> | References: <OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl>
> <11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com>
> | Subject: Re: Question on SQL
> | Date: Wed, 12 Sep 2007 16:40:10 -0400
> | Lines: 62
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <OyQZd0X9HHA.1212@.TK2MSFTNGP05.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: 208-39-138-189.isp.comcastbusiness.net 208.39.138.189
> | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:25271
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Ekrem,
> |
> | Thanks for your help. The size of the database is small about 500MB so
> that
> | is not an issue. My only other question on this is Does this go both
> ways.
> |
> | Meaning when they copy to the SQL Express will it update the SQL server
> | later during the Replication time since the Express is setup as
> Subscriber?
> |
> | Thanks again for your help
> | Adam Raff
> |
> |
> |
> | "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> | news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
> | > You could use Transactional Replication and set its schedule as
> running
> | > once at night. SQL Server Express Edition can be used as a Subscriber
> in a
> | > Replication topology and it's free. However, Express Edition has its
> | > limitations. (4GB db, 1 CPU, 1GB RAM)
> | >
> | > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
> | >
> | > You would make your current SQL Server as Publisher \ Distributor
> | > (consider workload on your current server) and the other one would be
> the
> | > Subscriber. So, your new SQL Server would be updated daily and your
> app
> | > would query your local SQL Server directly.
> | >
> | > Try this in a test environment first. If your system and app works
> without
> | > any problem then you could apply it to your production system.
> | >
> | > --
> | > Ekrem Önsoy
> | >
> | >
> | >
> | > "Adam Raff" <araff@.newsgroup.nospam> wrote in message
> | > news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> | >> Good Day,
> | >>
> | >> I have a simple question on SQL 2000. Please note that I am no
> expert
> | >> and this is a basic question.
> | >>
> | >> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a
> | >> remote site that connects to this server via a T1 line using a
> program
> | >> that we created called popss.
> | >>
> | >> What happens is that the program takes a long time getting the data
> from
> | >> the server and we know that it's a bandwidth issue. What I would
> like
> to
> | >> know is there a way to install a toned down version of SQL something
> like
> | >> desktop on another Windows 2003 server or do we need the another full
> | >> version of SQL at that location so that the program can read from the
> | >> local instance instead of going over the pipe.
> | >>
> | >> I am looking for a way to sync up the two SQL databases say at night
> this
> | >> way there we be no traffic over the pipe during the day.
> | >>
> | >> Thank You
> | >> Adam Raff
> | >>
> | >
> |
> |
> |
>|||Correction: There is no version 2007 of SQL Server. SQL Server 2008 is the
one that's going to be successor of SQL Server 2005.
If you want to transfer changes that you make in your second SQL Server to
be applied to your first SQL Server, then use Merge Replication. As you can
not use SQL Server Express Edition in a Updatable Subscriptions for
Transactional Replication topology, you may want to go with Merge
Replication. You'd be able to use Express Edition in a Transactional
Replication if you wanted to use your second SQL Server only for reporting
server purposes which means you would not want to transfer the updated
records from your second SQL Server to the First one.
Express Edition's limitation is just to be Subscriber in a Replication
Topology in this topic. It can not be a Publisher nor Distributor. But it
can be a Subscriber and for you situation can be used in a Merge Replication
topology.
Just give it a try Merge Replication on your test machine and see how it
works.
If you'd like to learn more about Transactional and Merge Replications, I
encourage you you to visit the links that Adams already mentioned.
>> How Merge Replication Works
>> http://technet.microsoft.com/en-us/library/ms151329.aspx
>> How Transactional Replication Works
>> http://technet.microsoft.com/en-us/library/ms151706.aspx
Ekrem Önsoy
"Adam Raff" <araff@.newsgroup.nospam> wrote in message
news:eVK6zng9HHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi Adams,
> If we upgrade to SQL 2005 lets say or the newer version of SQL 2007 will
> this then work then?
> Is the Express version limited in that it can never sync up with the full
> version of SQL?
> Thanks
> Adam Raff
>
> "Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
> news:WwE9G5d9HHA.5532@.TK2MSFTNGHUB02.phx.gbl...
>> Dear Adam,
>> Thank you for posting here.
>> I would like to explain that the SQL Server 2000 does not support the
>> updatable Subscriptions for Transactional Replication and the SQL Server
>> 2005 Express 2005 Edition can only be configured as the Subscriber. So,
>> the
>> replication is done in only one way and the data written in the SQL
>> Express
>> will not be transferred to the main SQL Server.
>> If your application "Popss" only reads the data from SQL Server, we can
>> choose to use Transactional Replication. If not, we can try the Merge
>> Replication so that the changes can be synchronized to the publisher.
>> For your reference, I have included some relevant articles below:
>> How Merge Replication Works
>> http://technet.microsoft.com/en-us/library/ms151329.aspx
>> How Transactional Replication Works
>> http://technet.microsoft.com/en-us/library/ms151706.aspx
>> If anything is unclear in my reply, please don't hesitate to let me know
>> and I will be glad to help.
>> Have a nice day!
>> Best regards,
>> Adams Qu, MCSE, MCDBA, MCTS
>> Microsoft Online Support
>> Microsoft Global Technical Support Center
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> --
>> | From: "Adam Raff" <araff@.newsgroup.nospam>
>> | References: <OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl>
>> <11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com>
>> | Subject: Re: Question on SQL
>> | Date: Wed, 12 Sep 2007 16:40:10 -0400
>> | Lines: 62
>> | X-Priority: 3
>> | X-MSMail-Priority: Normal
>> | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
>> | X-RFC2646: Format=Flowed; Response
>> | Message-ID: <OyQZd0X9HHA.1212@.TK2MSFTNGP05.phx.gbl>
>> | Newsgroups: microsoft.public.sqlserver.server
>> | NNTP-Posting-Host: 208-39-138-189.isp.comcastbusiness.net
>> 208.39.138.189
>> | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
>> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:25271
>> | X-Tomcat-NG: microsoft.public.sqlserver.server
>> |
>> | Ekrem,
>> |
>> | Thanks for your help. The size of the database is small about 500MB so
>> that
>> | is not an issue. My only other question on this is Does this go both
>> ways.
>> |
>> | Meaning when they copy to the SQL Express will it update the SQL server
>> | later during the Replication time since the Express is setup as
>> Subscriber?
>> |
>> | Thanks again for your help
>> | Adam Raff
>> |
>> |
>> |
>> | "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> | news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
>> | > You could use Transactional Replication and set its schedule as
>> running
>> | > once at night. SQL Server Express Edition can be used as a Subscriber
>> in a
>> | > Replication topology and it's free. However, Express Edition has its
>> | > limitations. (4GB db, 1 CPU, 1GB RAM)
>> | >
>> | > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
>> | >
>> | > You would make your current SQL Server as Publisher \ Distributor
>> | > (consider workload on your current server) and the other one would be
>> the
>> | > Subscriber. So, your new SQL Server would be updated daily and your
>> app
>> | > would query your local SQL Server directly.
>> | >
>> | > Try this in a test environment first. If your system and app works
>> without
>> | > any problem then you could apply it to your production system.
>> | >
>> | > --
>> | > Ekrem Önsoy
>> | >
>> | >
>> | >
>> | > "Adam Raff" <araff@.newsgroup.nospam> wrote in message
>> | > news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
>> | >> Good Day,
>> | >>
>> | >> I have a simple question on SQL 2000. Please note that I am no
>> expert
>> | >> and this is a basic question.
>> | >>
>> | >> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have
>> a
>> | >> remote site that connects to this server via a T1 line using a
>> program
>> | >> that we created called popss.
>> | >>
>> | >> What happens is that the program takes a long time getting the data
>> from
>> | >> the server and we know that it's a bandwidth issue. What I would
>> like
>> to
>> | >> know is there a way to install a toned down version of SQL something
>> like
>> | >> desktop on another Windows 2003 server or do we need the another
>> full
>> | >> version of SQL at that location so that the program can read from
>> the
>> | >> local instance instead of going over the pipe.
>> | >>
>> | >> I am looking for a way to sync up the two SQL databases say at night
>> this
>> | >> way there we be no traffic over the pipe during the day.
>> | >>
>> | >> Thank You
>> | >> Adam Raff
>> | >>
>> | >
>> |
>> |
>> |
>|||Thank You both for explaining this to me. I will sit down to read the
articles to get an idea. Now that I know that it is possible to do with
what I have.
Thanks again for your help
Adam Raff
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:8E4680AF-987C-4999-90A3-33BF3B6D5953@.microsoft.com...
> Correction: There is no version 2007 of SQL Server. SQL Server 2008 is the
> one that's going to be successor of SQL Server 2005.
> If you want to transfer changes that you make in your second SQL Server to
> be applied to your first SQL Server, then use Merge Replication. As you
> can not use SQL Server Express Edition in a Updatable Subscriptions for
> Transactional Replication topology, you may want to go with Merge
> Replication. You'd be able to use Express Edition in a Transactional
> Replication if you wanted to use your second SQL Server only for reporting
> server purposes which means you would not want to transfer the updated
> records from your second SQL Server to the First one.
> Express Edition's limitation is just to be Subscriber in a Replication
> Topology in this topic. It can not be a Publisher nor Distributor. But it
> can be a Subscriber and for you situation can be used in a Merge
> Replication topology.
> Just give it a try Merge Replication on your test machine and see how it
> works.
> If you'd like to learn more about Transactional and Merge Replications, I
> encourage you you to visit the links that Adams already mentioned.
>> How Merge Replication Works
>> http://technet.microsoft.com/en-us/library/ms151329.aspx
>> How Transactional Replication Works
>> http://technet.microsoft.com/en-us/library/ms151706.aspx
>
> --
> Ekrem Önsoy
>
> "Adam Raff" <araff@.newsgroup.nospam> wrote in message
> news:eVK6zng9HHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi Adams,
>> If we upgrade to SQL 2005 lets say or the newer version of SQL 2007 will
>> this then work then?
>> Is the Express version limited in that it can never sync up with the full
>> version of SQL?
>> Thanks
>> Adam Raff
>>
>> "Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
>> news:WwE9G5d9HHA.5532@.TK2MSFTNGHUB02.phx.gbl...
>> Dear Adam,
>> Thank you for posting here.
>> I would like to explain that the SQL Server 2000 does not support the
>> updatable Subscriptions for Transactional Replication and the SQL Server
>> 2005 Express 2005 Edition can only be configured as the Subscriber. So,
>> the
>> replication is done in only one way and the data written in the SQL
>> Express
>> will not be transferred to the main SQL Server.
>> If your application "Popss" only reads the data from SQL Server, we can
>> choose to use Transactional Replication. If not, we can try the Merge
>> Replication so that the changes can be synchronized to the publisher.
>> For your reference, I have included some relevant articles below:
>> How Merge Replication Works
>> http://technet.microsoft.com/en-us/library/ms151329.aspx
>> How Transactional Replication Works
>> http://technet.microsoft.com/en-us/library/ms151706.aspx
>> If anything is unclear in my reply, please don't hesitate to let me know
>> and I will be glad to help.
>> Have a nice day!
>> Best regards,
>> Adams Qu, MCSE, MCDBA, MCTS
>> Microsoft Online Support
>> Microsoft Global Technical Support Center
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> --
>> | From: "Adam Raff" <araff@.newsgroup.nospam>
>> | References: <OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl>
>> <11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com>
>> | Subject: Re: Question on SQL
>> | Date: Wed, 12 Sep 2007 16:40:10 -0400
>> | Lines: 62
>> | X-Priority: 3
>> | X-MSMail-Priority: Normal
>> | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
>> | X-RFC2646: Format=Flowed; Response
>> | Message-ID: <OyQZd0X9HHA.1212@.TK2MSFTNGP05.phx.gbl>
>> | Newsgroups: microsoft.public.sqlserver.server
>> | NNTP-Posting-Host: 208-39-138-189.isp.comcastbusiness.net
>> 208.39.138.189
>> | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
>> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:25271
>> | X-Tomcat-NG: microsoft.public.sqlserver.server
>> |
>> | Ekrem,
>> |
>> | Thanks for your help. The size of the database is small about 500MB
>> so
>> that
>> | is not an issue. My only other question on this is Does this go both
>> ways.
>> |
>> | Meaning when they copy to the SQL Express will it update the SQL
>> server
>> | later during the Replication time since the Express is setup as
>> Subscriber?
>> |
>> | Thanks again for your help
>> | Adam Raff
>> |
>> |
>> |
>> | "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> | news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
>> | > You could use Transactional Replication and set its schedule as
>> running
>> | > once at night. SQL Server Express Edition can be used as a
>> Subscriber
>> in a
>> | > Replication topology and it's free. However, Express Edition has its
>> | > limitations. (4GB db, 1 CPU, 1GB RAM)
>> | >
>> | > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
>> | >
>> | > You would make your current SQL Server as Publisher \ Distributor
>> | > (consider workload on your current server) and the other one would
>> be
>> the
>> | > Subscriber. So, your new SQL Server would be updated daily and your
>> app
>> | > would query your local SQL Server directly.
>> | >
>> | > Try this in a test environment first. If your system and app works
>> without
>> | > any problem then you could apply it to your production system.
>> | >
>> | > --
>> | > Ekrem Önsoy
>> | >
>> | >
>> | >
>> | > "Adam Raff" <araff@.newsgroup.nospam> wrote in message
>> | > news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
>> | >> Good Day,
>> | >>
>> | >> I have a simple question on SQL 2000. Please note that I am no
>> expert
>> | >> and this is a basic question.
>> | >>
>> | >> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have
>> a
>> | >> remote site that connects to this server via a T1 line using a
>> program
>> | >> that we created called popss.
>> | >>
>> | >> What happens is that the program takes a long time getting the data
>> from
>> | >> the server and we know that it's a bandwidth issue. What I would
>> like
>> to
>> | >> know is there a way to install a toned down version of SQL
>> something
>> like
>> | >> desktop on another Windows 2003 server or do we need the another
>> full
>> | >> version of SQL at that location so that the program can read from
>> the
>> | >> local instance instead of going over the pipe.
>> | >>
>> | >> I am looking for a way to sync up the two SQL databases say at
>> night
>> this
>> | >> way there we be no traffic over the pipe during the day.
>> | >>
>> | >> Thank You
>> | >> Adam Raff
>> | >>
>> | >
>> |
>> |
>> |
>>
>|||Dear Adam,
Thank you for your response.
If you have any other questions or concerns, please do not hesitate to
contact me. It is always my pleasure to be of assistance.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

question on dbcc opentran

sql2k sp3
Im trying to truncate a tlog on a replicated db. It wont
let me untill the results of dbcc opentran are empty. Im
the only one on this test box and can verify that all data
has made it to the subscriber. But still, even after 20
minutes of waiting, I run dbcc opentran and its not empty.
So my question is, how long after my last transaction is
committed will dbcc opentran be empty? Is there a way to
speed this up?
TIA, ChrisThis is a multi-part message in MIME format.
--=_NextPart_000_0322_01C3CE0C.82054A20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
How about finding out why there is an open transaction? Run DBCC
INPUTBUFFER on the SPID identified by DBCC OPENTRAN.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:018601c3ce35$3497d4a0$a101280a@.phx.gbl...
sql2k sp3
Im trying to truncate a tlog on a replicated db. It wont
let me untill the results of dbcc opentran are empty. Im
the only one on this test box and can verify that all data
has made it to the subscriber. But still, even after 20
minutes of waiting, I run dbcc opentran and its not empty.
So my question is, how long after my last transaction is
committed will dbcc opentran be empty? Is there a way to
speed this up?
TIA, Chris
--=_NextPart_000_0322_01C3CE0C.82054A20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

How about finding out why there is an =open transaction? Run DBCC INPUTBUFFER on the SPID identified by DBCC OPENTRAN.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"chris" wrote in message news:018601c3ce35$34=97d4a0$a101280a@.phx.gbl...sql2k sp3 Im trying to truncate a tlog on a replicated db. It wont =let me untill the results of dbcc opentran are empty. Im the only one on =this test box and can verify that all data has made it to the subscriber. But =still, even after 20 minutes of waiting, I run dbcc opentran and its not =empty. So my question is, how long after my last transaction is =committed will dbcc opentran be empty? Is there a way to speed this up?TIA, =Chris

---

--=_NextPart_000_0322_01C3CE0C.82054A20--|||DBCC Opentran doesnt return a spid. I run select * from
master..sysprocesses where open_tran > 0 and get
EventType Parameters
EventInfo
-- -- --
----
Language Event 0 sp_MSget_last_transaction
@.publisher_id = 0, @.publisher_db = N'adv084',
@.for_truncate = 0x1
In addition to this, I forgot to mention that I can clear
this up by running sp_repldone as describer in BOL but it
causes its own set of problems.
>--Original Message--
>How about finding out why there is an open transaction?
Run DBCC
>INPUTBUFFER on the SPID identified by DBCC OPENTRAN.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018601c3ce35$3497d4a0$a101280a@.phx.gbl...
>sql2k sp3
>Im trying to truncate a tlog on a replicated db. It wont
>let me untill the results of dbcc opentran are empty. Im
>the only one on this test box and can verify that all data
>has made it to the subscriber. But still, even after 20
>minutes of waiting, I run dbcc opentran and its not empty.
>So my question is, how long after my last transaction is
>committed will dbcc opentran be empty? Is there a way to
>speed this up?
>TIA, Chris
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0367_01C3CE11.6946A830
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Replication uses transaction log entries. Likely, it is waiting to take the
last transaction from the transaction log to the distribution database.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:01e701c3ce3a$5f3304f0$a101280a@.phx.gbl...
DBCC Opentran doesnt return a spid. I run select * from
master..sysprocesses where open_tran > 0 and get
EventType Parameters
EventInfo
-- -- --
----
Language Event 0 sp_MSget_last_transaction
@.publisher_id = 0, @.publisher_db = N'adv084',
@.for_truncate = 0x1
In addition to this, I forgot to mention that I can clear
this up by running sp_repldone as describer in BOL but it
causes its own set of problems.
>--Original Message--
>How about finding out why there is an open transaction?
Run DBCC
>INPUTBUFFER on the SPID identified by DBCC OPENTRAN.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018601c3ce35$3497d4a0$a101280a@.phx.gbl...
>sql2k sp3
>Im trying to truncate a tlog on a replicated db. It wont
>let me untill the results of dbcc opentran are empty. Im
>the only one on this test box and can verify that all data
>has made it to the subscriber. But still, even after 20
>minutes of waiting, I run dbcc opentran and its not empty.
>So my question is, how long after my last transaction is
>committed will dbcc opentran be empty? Is there a way to
>speed this up?
>TIA, Chris
>
--=_NextPart_000_0367_01C3CE11.6946A830
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Replication uses transaction log =entries. Likely, it is waiting to take the last transaction from the =transaction log to the distribution database.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"chris" wrote in message news:01e701c3ce3a$5f=3304f0$a101280a@.phx.gbl...DBCC Opentran doesnt return a spid. I run select * from =master..sysprocesses where open_tran > 0 and =getEventType Parameters EventInfo = &=nbsp; &n=bsp; &nb=sp; &nb=sp; &nbs=p; -- -- ----=-- Language Event =0 sp_MSget_last_transaction @.publisher_id =3D 0, @.publisher_db =3D =N'adv084', @.for_truncate =3D 0x1In addition to this, I forgot to =mention that I can clear this up by running sp_repldone as describer in BOL but it causes its own set of problems.>--Original Message-->How about finding out why there is an open transaction? Run DBCC>INPUTBUFFER on the SPID =identified by DBCC OPENTRAN.>>-->Tom>>--=----->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"chri=s" wrote in message>news:018601c3ce35$3497d4a0$a101280a@.phx.gbl...>=sql2k sp3>>Im trying to truncate a tlog on a replicated db. It wont>let me untill the results of dbcc opentran are empty. =Im>the only one on this test box and can verify that all data>has made =it to the subscriber. But still, even after 20>minutes of waiting, I run =dbcc opentran and its not empty.>So my question is, how long after my =last transaction is>committed will dbcc opentran be empty? Is there a =way to>speed this up?>>TIA, Chris>

---

--=_NextPart_000_0367_01C3CE11.6946A830--|||But its already been distributed. I have verified this by
rowcounts.
>--Original Message--
>Replication uses transaction log entries. Likely, it is
waiting to take the
>last transaction from the transaction log to the
distribution database.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01e701c3ce3a$5f3304f0$a101280a@.phx.gbl...
>DBCC Opentran doesnt return a spid. I run select * from
>master..sysprocesses where open_tran > 0 and get
>EventType Parameters
>EventInfo
>-- -- --
-
>----
>Language Event 0 sp_MSget_last_transaction
>@.publisher_id = 0, @.publisher_db = N'adv084',
>@.for_truncate = 0x1
>In addition to this, I forgot to mention that I can clear
>this up by running sp_repldone as describer in BOL but it
>causes its own set of problems.
>
>>--Original Message--
>>How about finding out why there is an open transaction?
>Run DBCC
>>INPUTBUFFER on the SPID identified by DBCC OPENTRAN.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"chris" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:018601c3ce35$3497d4a0$a101280a@.phx.gbl...
>>sql2k sp3
>>Im trying to truncate a tlog on a replicated db. It wont
>>let me untill the results of dbcc opentran are empty. Im
>>the only one on this test box and can verify that all
data
>>has made it to the subscriber. But still, even after 20
>>minutes of waiting, I run dbcc opentran and its not
empty.
>>So my question is, how long after my last transaction is
>>committed will dbcc opentran be empty? Is there a way to
>>speed this up?
>>TIA, Chris
>|||This is a multi-part message in MIME format.
--=_NextPart_000_03C8_01C3CE15.765C2050
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I'm surprised that DBCC OPENTRAN doesn't give you a SPID - unless the open
tran is started from another database. When you run:
select * from
master..sysprocesses where open_tran > 0
what database does it tell you?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:007f01c3ce3d$14e64cb0$a501280a@.phx.gbl...
But its already been distributed. I have verified this by
rowcounts.
>--Original Message--
>Replication uses transaction log entries. Likely, it is
waiting to take the
>last transaction from the transaction log to the
distribution database.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01e701c3ce3a$5f3304f0$a101280a@.phx.gbl...
>DBCC Opentran doesnt return a spid. I run select * from
>master..sysprocesses where open_tran > 0 and get
>EventType Parameters
>EventInfo
>-- -- --
-
>----
>Language Event 0 sp_MSget_last_transaction
>@.publisher_id = 0, @.publisher_db = N'adv084',
>@.for_truncate = 0x1
>In addition to this, I forgot to mention that I can clear
>this up by running sp_repldone as describer in BOL but it
>causes its own set of problems.
>
>>--Original Message--
>>How about finding out why there is an open transaction?
>Run DBCC
>>INPUTBUFFER on the SPID identified by DBCC OPENTRAN.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"chris" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:018601c3ce35$3497d4a0$a101280a@.phx.gbl...
>>sql2k sp3
>>Im trying to truncate a tlog on a replicated db. It wont
>>let me untill the results of dbcc opentran are empty. Im
>>the only one on this test box and can verify that all
data
>>has made it to the subscriber. But still, even after 20
>>minutes of waiting, I run dbcc opentran and its not
empty.
>>So my question is, how long after my last transaction is
>>committed will dbcc opentran be empty? Is there a way to
>>speed this up?
>>TIA, Chris
>
--=_NextPart_000_03C8_01C3CE15.765C2050
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I'm surprised that DBCC OPENTRAN =doesn't give you a SPID - unless the open tran is started from another database. =When you run:
select * =frommaster..sysprocesses where open_tran > 0
what database does it tell =you?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"chris" wrote in message news:007f01c3ce3d$14=e64cb0$a501280a@.phx.gbl...But its already been distributed. I have verified this by rowcounts.>--Original =Message-->Replication uses transaction log entries. Likely, it is waiting to take =the>last transaction from the transaction log to the distribution database.>>-->Tom>>--=----->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"chri=s" wrote in message>news:01e701c3ce3a$5f3304f0$a101280a@.phx.gbl...>=DBCC Opentran doesnt return a spid. I run select * =from>master..sysprocesses where open_tran > 0 and get>>EventType Parameters>EventInfo>>-- -- --->--=-->Language Event 0 sp_MSget_last_transaction>@.publisher_id =3D 0, @.publisher_db =3D N'adv084',>@.for_truncate =3D 0x1>>In addition to =this, I forgot to mention that I can clear>this up by running sp_repldone =as describer in BOL but it>causes its own set of problems.>>>>--Original Message-->How about finding out why there is an open transaction?>Run DBCC>INPUTBUFFER on the SPID =identified by DBCC OPENTRAN.>>-->Tom>>>=;----->--=-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"chris" wrote in>message>news:018601c3ce35$3497d4a0$a101280a@.phx.gbl.=..>sql2k sp3>>Im trying to truncate a tlog on a replicated =db. It wont>let me untill the results of dbcc opentran are empty. Im>the only one on this test box and can verify that all data>has made it to the subscriber. But still, even after =20>minutes of waiting, I run dbcc opentran and its not empty.>So my question is, how long after my last =transaction is>committed will dbcc opentran be empty? Is there a way to>speed this up?>>TIA, Chris>>

---

--=_NextPart_000_03C8_01C3CE15.765C2050--|||It tells me the Distribution DB. But FYI, I already tried
stopping the distribution and log reader agents to see if
that made a difference but it didnt help.
>--Original Message--
>I'm surprised that DBCC OPENTRAN doesn't give you a SPID -
unless the open
>tran is started from another database. When you run:
>select * from
>master..sysprocesses where open_tran > 0
>what database does it tell you?
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:007f01c3ce3d$14e64cb0$a501280a@.phx.gbl...
>But its already been distributed. I have verified this by
>rowcounts.
>
>>--Original Message--
>>Replication uses transaction log entries. Likely, it is
>waiting to take the
>>last transaction from the transaction log to the
>distribution database.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"chris" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:01e701c3ce3a$5f3304f0$a101280a@.phx.gbl...
>>DBCC Opentran doesnt return a spid. I run select * from
>>master..sysprocesses where open_tran > 0 and get
>>EventType Parameters
>>EventInfo
>>-- -- --
-
>-
>>----
-
>>Language Event 0 sp_MSget_last_transaction
>>@.publisher_id = 0, @.publisher_db = N'adv084',
>>@.for_truncate = 0x1
>>In addition to this, I forgot to mention that I can clear
>>this up by running sp_repldone as describer in BOL but it
>>causes its own set of problems.
>>
>>--Original Message--
>>How about finding out why there is an open transaction?
>>Run DBCC
>>INPUTBUFFER on the SPID identified by DBCC OPENTRAN.
>>--
>>Tom
>>---
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"chris" <anonymous@.discussions.microsoft.com> wrote in
>>message
>>news:018601c3ce35$3497d4a0$a101280a@.phx.gbl...
>>sql2k sp3
>>Im trying to truncate a tlog on a replicated db. It wont
>>let me untill the results of dbcc opentran are empty. Im
>>the only one on this test box and can verify that all
>data
>>has made it to the subscriber. But still, even after 20
>>minutes of waiting, I run dbcc opentran and its not
>empty.
>>So my question is, how long after my last transaction is
>>committed will dbcc opentran be empty? Is there a way to
>>speed this up?
>>TIA, Chris
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0407_01C3CE18.8CC84D20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You may want to open a ticket with MS Product Support Services (PSS). This
may be a bug. :-(
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:073201c3ce41$bf465700$a001280a@.phx.gbl...
It tells me the Distribution DB. But FYI, I already tried
stopping the distribution and log reader agents to see if
that made a difference but it didnt help.
>--Original Message--
>I'm surprised that DBCC OPENTRAN doesn't give you a SPID -
unless the open
>tran is started from another database. When you run:
>select * from
>master..sysprocesses where open_tran > 0
>what database does it tell you?
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:007f01c3ce3d$14e64cb0$a501280a@.phx.gbl...
>But its already been distributed. I have verified this by
>rowcounts.
>
>>--Original Message--
>>Replication uses transaction log entries. Likely, it is
>waiting to take the
>>last transaction from the transaction log to the
>distribution database.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"chris" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:01e701c3ce3a$5f3304f0$a101280a@.phx.gbl...
>>DBCC Opentran doesnt return a spid. I run select * from
>>master..sysprocesses where open_tran > 0 and get
>>EventType Parameters
>>EventInfo
>>-- -- --
-
>-
>>----
-
>>Language Event 0 sp_MSget_last_transaction
>>@.publisher_id = 0, @.publisher_db = N'adv084',
>>@.for_truncate = 0x1
>>In addition to this, I forgot to mention that I can clear
>>this up by running sp_repldone as describer in BOL but it
>>causes its own set of problems.
>>
>>--Original Message--
>>How about finding out why there is an open transaction?
>>Run DBCC
>>INPUTBUFFER on the SPID identified by DBCC OPENTRAN.
>>--
>>Tom
>>---
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"chris" <anonymous@.discussions.microsoft.com> wrote in
>>message
>>news:018601c3ce35$3497d4a0$a101280a@.phx.gbl...
>>sql2k sp3
>>Im trying to truncate a tlog on a replicated db. It wont
>>let me untill the results of dbcc opentran are empty. Im
>>the only one on this test box and can verify that all
>data
>>has made it to the subscriber. But still, even after 20
>>minutes of waiting, I run dbcc opentran and its not
>empty.
>>So my question is, how long after my last transaction is
>>committed will dbcc opentran be empty? Is there a way to
>>speed this up?
>>TIA, Chris
>>
>
--=_NextPart_000_0407_01C3CE18.8CC84D20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You may want to open a ticket with MS =Product Support Services (PSS). This may be a bug. :-(
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"chris" wrote in message news:073201c3ce41$bf=465700$a001280a@.phx.gbl...It tells me the Distribution DB. But FYI, I already tried stopping the distribution and log reader agents to see if that made a difference =but it didnt help.>--Original Message-->I'm =surprised that DBCC OPENTRAN doesn't give you a SPID - unless the =open>tran is started from another database. When you run:>>select =* from>master..sysprocesses where open_tran > =0>>what database does it tell you?>>>-->Tom>>--=----->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"chri=s" wrote in message>news:007f01c3ce3d$14e64cb0$a501280a@.phx.gbl...>=But its already been distributed. I have verified this by>rowcounts.>>>--Original Message-->Replication uses transaction log entries. =Likely, it is>waiting to take the>last transaction from the transaction log to the>distribution database.>>-->Tom>>>=;----->--=-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"chris" wrote in>message>news:01e701c3ce3a$5f3304f0$a101280a@.phx.gbl.=..>DBCC Opentran doesnt return a spid. I run select * from>master..sysprocesses where open_tran > 0 and get>>EventType Parameters>EventInfo>>-- =-- --->->--=---->Language Event 0 sp_MSget_last_transaction>@.publisher_id =3D 0, @.publisher_db ==3D N'adv084',>@.for_truncate =3D 0x1>>In =addition to this, I forgot to mention that I can clear>this up by running =sp_repldone as describer in BOL but it>causes its own set of problems.>>>>--Origina=l Message-->How about finding out why there is an open transaction?>Run DBCC>INPUTBUFFER on the SPID identified by DBCC OPENTRAN.>>-->Tom>>=;>>----=--->->-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, SQL Server =Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>&=gt;>>>"chris" wrote in>message>news:018601c3ce35$3497d4a0$a101280a@.=phx.gbl...>sql2k sp3>>Im trying to truncate a tlog on a =replicated db. It wont>let me untill the results of dbcc opentran =are empty. Im>the only one on this test box and can verify that all>data>has made it to the subscriber. But still, =even after 20>minutes of waiting, I run dbcc opentran and its not>empty.>So my question is, how long after my =last transaction is>committed will dbcc opentran be empty? Is =there a way to>speed this up?>>TIA, =Chris>>>

---

--=_NextPart_000_0407_01C3CE18.8CC84D20--|||:-(
Thanks for all the help.
>--Original Message--
>You may want to open a ticket with MS Product Support
Services (PSS). This
>may be a bug. :-(
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:073201c3ce41$bf465700$a001280a@.phx.gbl...
>It tells me the Distribution DB. But FYI, I already tried
>stopping the distribution and log reader agents to see if
>that made a difference but it didnt help.
>
>>--Original Message--
>>I'm surprised that DBCC OPENTRAN doesn't give you a
SPID -
> unless the open
>>tran is started from another database. When you run:
>>select * from
>>master..sysprocesses where open_tran > 0
>>what database does it tell you?
>>
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"chris" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:007f01c3ce3d$14e64cb0$a501280a@.phx.gbl...
>>But its already been distributed. I have verified this by
>>rowcounts.
>>
>>--Original Message--
>>Replication uses transaction log entries. Likely, it is
>>waiting to take the
>>last transaction from the transaction log to the
>>distribution database.
>>--
>>Tom
>>---
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"chris" <anonymous@.discussions.microsoft.com> wrote in
>>message
>>news:01e701c3ce3a$5f3304f0$a101280a@.phx.gbl...
>>DBCC Opentran doesnt return a spid. I run select * from
>>master..sysprocesses where open_tran > 0 and get
>>EventType Parameters
>>EventInfo
>>-- -- --
-
>-
>>-
>>---
-
>-
>>Language Event 0 sp_MSget_last_transaction
>>@.publisher_id = 0, @.publisher_db = N'adv084',
>>@.for_truncate = 0x1
>>In addition to this, I forgot to mention that I can
clear
>>this up by running sp_repldone as describer in BOL but
it
>>causes its own set of problems.
>>
>>--Original Message--
>>How about finding out why there is an open transaction?
>>Run DBCC
>>INPUTBUFFER on the SPID identified by DBCC OPENTRAN.
>>--
>>Tom
>>----
-
>-
>>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"chris" <anonymous@.discussions.microsoft.com> wrote in
>>message
>>news:018601c3ce35$3497d4a0$a101280a@.phx.gbl...
>>sql2k sp3
>>Im trying to truncate a tlog on a replicated db. It
wont
>>let me untill the results of dbcc opentran are empty.
Im
>>the only one on this test box and can verify that all
>>data
>>has made it to the subscriber. But still, even after 20
>>minutes of waiting, I run dbcc opentran and its not
>>empty.
>>So my question is, how long after my last transaction
is
>>committed will dbcc opentran be empty? Is there a way
to
>>speed this up?
>>TIA, Chris
>>
>