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 memory

Win2K Advanced Server
SQL2K Enterprise
I need to set up 1 big fat Disaster Recovery box for my 6 production boxes.
I will do this by putting 6 instances of SQL on the DR box. I know that
there is an 8 gig max (using AWE) limit. But what I don't know is can each
instance have its own 8 gigs, or do all 6 instances need to share the same 8
gigs? Also, would switching to Win2K3 help me to be able to use more RAM?
Basically, I dont want to tell anyone to order 48 gigs or RAM (6 instances
with 8 gigs each) when Im only going to be able to use 8.
TIA, ChrisRChris,
Each instance can use it's own block of memory but I don't think Win2K Adv
server can use more than 8GB. I believe you need Data Center for more than
8GB. Certainly for 48GB. Do you need 8GB for each instance? Do each of
the 6 Prod boxes have 8GB now? Does your DR plan say you need to run all 6
instances on the one box at the same time? Seems kind of silly to have 6
individual boxes in prod and expect to run all 6 instances at once on one
box. In any case Win2K3 will not allow you to use any more ram but it does
have certain performance enhancements over 2K that you should take advantage
of.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:euDyuWiYFHA.2380@.tk2msftngp13.phx.gbl...
> Win2K Advanced Server
> SQL2K Enterprise
> I need to set up 1 big fat Disaster Recovery box for my 6 production
> boxes. I will do this by putting 6 instances of SQL on the DR box. I know
> that there is an 8 gig max (using AWE) limit. But what I don't know is can
> each instance have its own 8 gigs, or do all 6 instances need to share the
> same 8 gigs? Also, would switching to Win2K3 help me to be able to use
> more RAM? Basically, I dont want to tell anyone to order 48 gigs or RAM (6
> instances with 8 gigs each) when Im only going to be able to use 8.
>
> TIA, ChrisR
>|||I typed out a reply yesterday but I guess I forgot to hit the reply button.
No I don't need 8 gigs per instance, but 6 boxes sharing 8 gigs wont do
either. Yes, all of them may need to be on at once.
Thanks Andrew.
CR
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23MHGH$jYFHA.1152@.tk2msftngp13.phx.gbl...
> Chris,
> Each instance can use it's own block of memory but I don't think Win2K Adv
> server can use more than 8GB. I believe you need Data Center for more
> than 8GB. Certainly for 48GB. Do you need 8GB for each instance? Do
> each of the 6 Prod boxes have 8GB now? Does your DR plan say you need to
> run all 6 instances on the one box at the same time? Seems kind of silly
> to have 6 individual boxes in prod and expect to run all 6 instances at
> once on one box. In any case Win2K3 will not allow you to use any more
> ram but it does have certain performance enhancements over 2K that you
> should take advantage of.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:euDyuWiYFHA.2380@.tk2msftngp13.phx.gbl...
>> Win2K Advanced Server
>> SQL2K Enterprise
>> I need to set up 1 big fat Disaster Recovery box for my 6 production
>> boxes. I will do this by putting 6 instances of SQL on the DR box. I know
>> that there is an 8 gig max (using AWE) limit. But what I don't know is
>> can each instance have its own 8 gigs, or do all 6 instances need to
>> share the same 8 gigs? Also, would switching to Win2K3 help me to be able
>> to use more RAM? Basically, I dont want to tell anyone to order 48 gigs
>> or RAM (6 instances with 8 gigs each) when Im only going to be able to
>> use 8.
>>
>> TIA, ChrisR
>

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
>>
>

Question on Application roles.

sql2k
Im comparing a username/ password to an App role/ password and Im just not
seeing the logic here. An App either needs to supply a username/ password or
a "sp_setapprole @.rolename = 'TestRole' ,@.password ='test'". Either way they
are granted access to the DB. Either way they can only execute what I allow
them too(A user cant run a SELECT if I dont grant him access.). How is this
any safer?
TIA, ChrisREven in the case of using an app role, you still need a login to the server.
The benefit of an app role is that you can share server credentials amongst
a variety of apps, while still keeping data security partitioned. It's just
another way of slicing and dicing from a security point of view. I don't
see one method as any safer or less safe than any other method...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:427310C9-210E-4A9F-A893-F8AE89A60E94@.microsoft.com...
> sql2k
> Im comparing a username/ password to an App role/ password and Im just not
> seeing the logic here. An App either needs to supply a username/ password
> or
> a "sp_setapprole @.rolename = 'TestRole' ,@.password ='test'". Either way
> they
> are granted access to the DB. Either way they can only execute what I
> allow
> them too(A user cant run a SELECT if I dont grant him access.). How is
> this
> any safer?
> TIA, ChrisR|||Users do not have the password of the application role, only the application
has it.
One example is, users can have write access only thru the application role
but not using their Windows account. If they run the application they can
change data. If they use other tools like Query Analyzer or Access they would
not have write permissions.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
> sql2k
> Im comparing a username/ password to an App role/ password and Im just not
> seeing the logic here. An App either needs to supply a username/ password or
> a "sp_setapprole @.rolename = 'TestRole' ,@.password ='test'". Either way they
> are granted access to the DB. Either way they can only execute what I allow
> them too(A user cant run a SELECT if I dont grant him access.). How is this
> any safer?
> TIA, ChrisR