Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts

Thursday, March 29, 2012

Dreaded TEXT column

I need to setup repliaction between A and B. I have high transaction /min count. Some tables have TEXT columns. This is what i am planning to do:

Example: table name is CREDITS

vertical partition the table into two. First table (will be called CREDITS_PRI) holds PK column and non TEXT columns, second table (will be called CREDITS_SEC) holds PK column and TEXT columns.

Create a view called CREDITS with INSTEAD OF triggers for inserts, updates and deletes. So far so good. Then setup replication with immediate updating subscription between A and B for tables called ..._PRI and merge replication between A and B for tables called ..._SEC.

Would this work? How do other companies handle this? thank you in advance for pointing me into the right direction.

Lars

Yes, this would work, you could also leave your tables intact the way they were and implement bi-directional transactional replication.

However I assume you're on SQL 2000, correct? If you're on SQL 2005, immediate updating would work just fine if you replaced text datatype with varchar(max) datatype, it's also more optimal and efficient in terms of data storage.

|||

Thank you,

yes, we still are on SQL 2000 and won't upgrade until another 6 months. I have never heard of bi-directional replication since it wasn't mentioned anywhere. I found a great link on MS com and will take a look at it. Thanks again.

L

Drawbacks of putting transaction log on system drive

Hi,
I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
Would I come across any drawbacks if I put all transaction log files of a
database onto the system (OS installation) drive along with a page file and
maybe the tempdb? Most of the load in my database is due to read operations.
-- Many thanks, Oskar.Oskar wrote:
> Hi,
> I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
> Would I come across any drawbacks if I put all transaction log files of a
> database onto the system (OS installation) drive along with a page file an
d
> maybe the tempdb? Most of the load in my database is due to read operation
s.
> -- Many thanks, Oskar.
>
Hi Oskar
There will most likely be a performance hit the more files you put on
the same drive, but if it will be noticable or a problem is hard to say
without knowing your load and application. The best advice it to test it
as good as you can and then make your decicion based on your tests.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Drawbacks of putting transaction log on system drive

Hi,
I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
Would I come across any drawbacks if I put all transaction log files of a
database onto the system (OS installation) drive along with a page file and
maybe the tempdb? Most of the load in my database is due to read operations.
-- Many thanks, Oskar.Oskar wrote:
> Hi,
> I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
> Would I come across any drawbacks if I put all transaction log files of a
> database onto the system (OS installation) drive along with a page file and
> maybe the tempdb? Most of the load in my database is due to read operations.
> -- Many thanks, Oskar.
>
Hi Oskar
There will most likely be a performance hit the more files you put on
the same drive, but if it will be noticable or a problem is hard to say
without knowing your load and application. The best advice it to test it
as good as you can and then make your decicion based on your tests.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Monday, March 19, 2012

Doubts about Transaction Log

Hello to Everybody!
I have a doubts about Transaction Log. What's happen when I execute the
follow command
BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUN
CATE_ONLY
So, When should I use this command to avoid fill up the log?
Thanks a Lot
Juliano HortaDon't use it. Either have the db in simple recovery mode. Or do regular tran
saction log backups (the
log is emptied when you do a log backup).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juliano H via droptable.com" <forum@.droptable.com> wrote in message
news:541010E0E3B40@.droptable.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TR
UNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Only use this if you do not need to be recoverable via trans log, if so
maybe you should just use simple recovery mode
Below is from BOL
Note If backing up the log does not appear to truncate most of the log, an
old open transaction may exist in the log. Log space can be monitored with
DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Juliano H via droptable.com" <forum@.droptable.com> wrote in message
news:541010E0E3B40@.droptable.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
> TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Hi,
To add on to Tibor and David; Go ahead with SIMPLE recovery model if you do
not require a point in time recovery.
There is no difference between NO_LOG and TRUNCATE_ONLY. Both are same and
will truncate the inactive
portion of the log.
Thanks
Hari
SQL Server MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uYKNlZKtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Only use this if you do not need to be recoverable via trans log, if so
> maybe you should just use simple recovery mode
>
> Below is from BOL
>
> Note If backing up the log does not appear to truncate most of the log,
> an old open transaction may exist in the log. Log space can be monitored
> with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log
> Backups.
>
> NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it
> and truncates the log. This option frees space. Specifying a backup device
> is unnecessary because the log backup is not saved. NO_LOG and
> TRUNCATE_ONLY are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes,
> immediately execute BACKUP DATABASE.
>
> "Juliano H via droptable.com" <forum@.droptable.com> wrote in message
> news:541010E0E3B40@.droptable.com...
>

Doubts about Transaction Log

Hello to Everybody!
I have a doubts about Transaction Log. What's happen when I execute the
follow command
BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
So, When should I use this command to avoid fill up the log?
Thanks a Lot
Juliano Horta
Don't use it. Either have the db in simple recovery mode. Or do regular transaction log backups (the
log is emptied when you do a log backup).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juliano H via droptable.com" <forum@.droptable.com> wrote in message
news:541010E0E3B40@.droptable.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta
|||Only use this if you do not need to be recoverable via trans log, if so
maybe you should just use simple recovery mode
Below is from BOL
Note If backing up the log does not appear to truncate most of the log, an
old open transaction may exist in the log. Log space can be monitored with
DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Juliano H via droptable.com" <forum@.droptable.com> wrote in message
news:541010E0E3B40@.droptable.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
> TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta
|||Hi,
To add on to Tibor and David; Go ahead with SIMPLE recovery model if you do
not require a point in time recovery.
There is no difference between NO_LOG and TRUNCATE_ONLY. Both are same and
will truncate the inactive
portion of the log.
Thanks
Hari
SQL Server MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uYKNlZKtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Only use this if you do not need to be recoverable via trans log, if so
> maybe you should just use simple recovery mode
>
> Below is from BOL
>
> Note If backing up the log does not appear to truncate most of the log,
> an old open transaction may exist in the log. Log space can be monitored
> with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log
> Backups.
>
> NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it
> and truncates the log. This option frees space. Specifying a backup device
> is unnecessary because the log backup is not saved. NO_LOG and
> TRUNCATE_ONLY are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes,
> immediately execute BACKUP DATABASE.
>
> "Juliano H via droptable.com" <forum@.droptable.com> wrote in message
> news:541010E0E3B40@.droptable.com...
>

Doubts about Transaction Log

Hello to Everybody!
I have a doubts about Transaction Log. What's happen when I execute the
follow command
BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
So, When should I use this command to avoid fill up the log?
Thanks a Lot
Juliano HortaDon't use it. Either have the db in simple recovery mode. Or do regular transaction log backups (the
log is emptied when you do a log backup).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541010E0E3B40@.SQLMonster.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Only use this if you do not need to be recoverable via trans log, if so
maybe you should just use simple recovery mode
Below is from BOL
Note If backing up the log does not appear to truncate most of the log, an
old open transaction may exist in the log. Log space can be monitored with
DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541010E0E3B40@.SQLMonster.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
> TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Hi,
To add on to Tibor and David; Go ahead with SIMPLE recovery model if you do
not require a point in time recovery.
There is no difference between NO_LOG and TRUNCATE_ONLY. Both are same and
will truncate the inactive
portion of the log.
Thanks
Hari
SQL Server MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uYKNlZKtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Only use this if you do not need to be recoverable via trans log, if so
> maybe you should just use simple recovery mode
>
> Below is from BOL
>
> Note If backing up the log does not appear to truncate most of the log,
> an old open transaction may exist in the log. Log space can be monitored
> with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log
> Backups.
>
> NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it
> and truncates the log. This option frees space. Specifying a backup device
> is unnecessary because the log backup is not saved. NO_LOG and
> TRUNCATE_ONLY are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes,
> immediately execute BACKUP DATABASE.
>
> "Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:541010E0E3B40@.SQLMonster.com...
>> Hello to Everybody!
>> I have a doubts about Transaction Log. What's happen when I execute the
>> follow command
>> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
>> TRUNCATE_ONLY
>> So, When should I use this command to avoid fill up the log?
>> Thanks a Lot
>> Juliano Horta
>

Doubt to be Clarified

hi

I have a backup up strategy as follows
Differential - every 4 hrs(4am,8am,12pm,...)
transaction - every 10 min

I am using SQL SERVER 2000.

At Some point of time my Differential and Transaction backup clashes
at(4am,8am,...)

when i Check my Entriprise Manager(Locks/process id)
I find Spid (blocked).

1)spid = 661

Properties Window
BACKUP DATABASE [imcl] TO [DiffBkp_IMCL] WITH NOINIT , NOUNLOAD ,
DIFFERENTIAL , NAME = N'IMCL Device Differential Backup', NOSKIP ,
STATS = 10, NOFORMAT

2)spid 708 blocked by 661
Properties Window
BACKUP LOG [imcl] TO DISK =
N'F:\DatabaseBackups\IMCL\Tran\NewTranImcl\imcl\im cl_tlog_200505261200.TRN'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

What I want to know is - :
a)Should I ignore this Blocking as it is solved automatically.

b)Whether my backup plan is poor.

c)Will this effect my users who are connected to my Server.

Thank u in advanceThe blocking will be resolved, in that spid 708 is essentially 'on
hold' until 661 completes. Probably the easiest way to avoid the issue
is to set the log backups to run every ten minutes but starting at five
minutes past the hour instead of on the hour, which I guess is what you
currently have.

If the differential backup takes more than 5 minutes, then you'll still
get the blocking, though, so if this is the case, then you might want
to create multiple active schedules for your transaction log backups -
the first one from 00:00 to 03:50, the next from 04:20 to 07:50 (or
whatever interval allows the differential backup to complete without
blocking) etc.

Your backup plan is fine in principle - see "Reducing Recovery Time" in
Books Online (provided that you're also making full backups at some
point, of course).

Simon|||Thank you Simon for advice

Simon Hayes wrote:
> The blocking will be resolved, in that spid 708 is essentially 'on
> hold' until 661 completes. Probably the easiest way to avoid the issue
> is to set the log backups to run every ten minutes but starting at five
> minutes past the hour instead of on the hour, which I guess is what you
> currently have.
> If the differential backup takes more than 5 minutes, then you'll still
> get the blocking, though, so if this is the case, then you might want
> to create multiple active schedules for your transaction log backups -
> the first one from 00:00 to 03:50, the next from 04:20 to 07:50 (or
> whatever interval allows the differential backup to complete without
> blocking) etc.
> Your backup plan is fine in principle - see "Reducing Recovery Time" in
> Books Online (provided that you're also making full backups at some
> point, of course).
> Simon

Doubt in Transaction Scope

Hi ,

I have 2 packages and they get executed in SQL Job.

Each of these package will have seperate transactions with the Database and I am not sure of how to maintain the transactions between these 2 packages.

Can someone explain me the necessary setting to achieve this. If I set the TransactionOption property to Required (or) Supported in both these packages, will the Transaction Scope of the first package extend to the second one.

For me, If the second package ends up in error, the first package should also have to be rollbacked.

Any suggestions would be of great help.

Thanks

Evan

Are the two packages setup so that one is parent and the other is child?

Otherwise, I'm not sure how you can enforce transactions. If so, then set each to "Required" and be sure to set the FailParentOnFailure property to true in the child so that when it fails, so does the parent.

Sunday, February 19, 2012

doing transactions with linked server(ole db provider)

Hi all,

When i begin a distributed transaction..., do i need to always
set xact_abort on?

But when i tried to put custom error handling to roll back a transaction with a linked server , it seems its impossible (as set xact_abort on, already automatically rolls back the transaction)

Does anyone have any ideas on this?

Thanks in advance

Bennyset xact_abort on is not required for distributed transactions.|||But i get an error when i dont set that.
Do you know what has to be done instead?

Thanks for your feedback

Benny

Friday, February 17, 2012

Doing a Transaction Log Backup.

I am going to back up my databases using a Database Maintenence Plans (from
Enterprise Manager). I see there is a Complete Backup and a Transaction Log
Backup.
If I do a Complete Backup, should I do a Transaction Log Backup as well ?
What would you do a Transaction Log Backup ?
Thanks,
CraigHere is an excerpt from SQL Server Books OnLine:
Transaction Log Backups
The transaction log is a serial record of all the transactions that have
been performed against the database since the transaction log was last backe
d
up. With transaction log backups, you can recover the database to a specific
point in time (for example, prior to entering unwanted data), or to the poin
t
of failure.
When restoring a transaction log backup, Microsoft? SQL Server? rolls
forward all changes recorded in the transaction log. When SQL Server reaches
the end of the transaction log, it has re-created the exact state of the
database at the time the backup operation started. If the database is
recovered, SQL Server then rolls back all transactions that were incomplete
when the backup operation started.
Transaction log backups generally use fewer resources than database backups.
As a result, you can create them more frequently than database backups.
Frequent backups decrease your risk of losing data.
Note Sometimes a transaction log backup is larger than a database backup.
For example, a database has a high transaction rate causing the transaction
log to grow quickly. In this situation, create transaction log backups more
frequently.
Transaction log backups are used only with the Full and Bulk-Logged Recovery
models. For more information, see Using Recovery Models.
Using Transaction Log Backups with Database Backups
Restoring a database using both database and transaction log backups works
only if you have an unbroken sequence of transaction log backups after the
last database or differential database backup. If a log backup is missing or
damaged, you must create a database or differential database backup and star
t
backing up the transaction logs again. Retain the previous transaction logs
backups if you want to restore the database to a point in time within those
backups.
The only time database or differential database backups must be synchronized
with transaction log backups is when starting a sequence of transaction log
backups. Every sequence of transaction log backups must be started by a
database or differential database backup.
Usually, the only time that a new sequence of backups is started is when the
database is backed up for the first time or a change in recovery model from
Simple to Full or Bulk-Logged has occurred. For more information, see
Switching Recovery Models.
Let me know if it helps,
Edgardo Valdez
MCSD, MCDBA, MCSE
"Craig HB" wrote:

> I am going to back up my databases using a Database Maintenence Plans (fro
m
> Enterprise Manager). I see there is a Complete Backup and a Transaction L
og
> Backup.
> If I do a Complete Backup, should I do a Transaction Log Backup as well ?
> What would you do a Transaction Log Backup ?
> Thanks,
> Craig|||Thanks, Edgardo
That was helpful. It seems from that excerpt that I should do transaction
backups between database backups.
Just one more question on that:
When you restore a transaction log, do you restore it in the same way as
restoring a database ? i.e. Enterprise Manager / All Tasks / Retore / Select
the file and click OK.
Thanks,
Craig|||Sorry for not replying before.
I usually do Transaction Log Backups in between full backups, with a
frequency that varies from 10 min to 4 hours, depending of the type of
database (production, development, etc), disk space available, etc.
You can also refer to this article that can help you
(http://www.microsoft.com/technet/pr...st.mspx
)
, specially where it says "Performing Transaction Log Backups through
Enterprise Manager"
I hope it helps you.
"Craig HB" wrote:

> Thanks, Edgardo
> That was helpful. It seems from that excerpt that I should do transaction
> backups between database backups.
> Just one more question on that:
> When you restore a transaction log, do you restore it in the same way as
> restoring a database ? i.e. Enterprise Manager / All Tasks / Retore / Sele
ct
> the file and click OK.
> Thanks,
> Craig

Tuesday, February 14, 2012

Does transaction replication modify the DB

Hi,
I have used merge replication for SQL Server DBs before but not
transactional replication. We have a DB with an existing application
attached and I know that merge replication changes the DB and may break the
app (otherwise I would use it). I assume that transactional replication
does not change the user tables in the DB?
Are there any constraints on what DB designs transactional replication
supports?
Grateful for any info
Pete
Pete,
transactional replication will add a guid column if it involves upatable
subscribers, and triggers will be added. For non-updatable subscribers,
there is a schema requirement - PK on tables - but no such schema changes.
This is from the point of view of the publisher.
From the subscriber point of view, there may be some schema changes - eg
identity columns lose their identity attribute, although this is not true
for queued updating subscribers where the identity attribute may be created
with an alternative seed.
HTH,
Paul Ibison
|||Thanks that is really useful.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23yJ4u9caEHA.2812@.tk2msftngp13.phx.gbl...
> Pete,
> transactional replication will add a guid column if it involves upatable
> subscribers, and triggers will be added. For non-updatable subscribers,
> there is a schema requirement - PK on tables - but no such schema changes.
> This is from the point of view of the publisher.
> From the subscriber point of view, there may be some schema changes - eg
> identity columns lose their identity attribute, although this is not true
> for queued updating subscribers where the identity attribute may be
created
> with an alternative seed.
> HTH,
> Paul Ibison
>
|||I published sample 2 DBs with transaction replication as a test.
In one of them it added the msrepl_tran_version column to every table - when
I published another it didn't do this. I think the only difference is with
one I choose "display advanced options" in the replication wizard - then I
choose queued updating.
Can you use immediate updating if you are publishing accross an internet
(512 ADSL) connection?
Thanks
Pete
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23yJ4u9caEHA.2812@.tk2msftngp13.phx.gbl...
> Pete,
> transactional replication will add a guid column if it involves upatable
> subscribers, and triggers will be added. For non-updatable subscribers,
> there is a schema requirement - PK on tables - but no such schema changes.
> This is from the point of view of the publisher.
> From the subscriber point of view, there may be some schema changes - eg
> identity columns lose their identity attribute, although this is not true
> for queued updating subscribers where the identity attribute may be
created
> with an alternative seed.
> HTH,
> Paul Ibison
|||Pete,
imediate updating uses 2PC and DTC, ie it needs a direct and constant
connection to allow subscribers to change data. If you have this set up then
it'll be OK, otherwise you'll need queued updating subscribers.
HTH,
Paul Ibison

Does the transaction log on a publisher SQL server get truncated when the database is back

Hello All,
The setting is SQL server 7, on Windows NT.

If the distributor can not access the log on the publisher database ( but
subscribers are not deleted yet), will the full backup of the publisher
database truncate the transaction log? My guess is, it will not truncate the
log.
Can any one confirm please?

Thanks,
MoklesFull backups don't truncate the log; the log is truncated by BACKUP log.
From the Books Online:

<Excerpt href="http://links.10026.com/?link=\tsqlref.chm::/ts_ba-bz_35ww.htm">
Once the log is backed up, the space may be truncated when no longer
required by replication or active transactions.
</Excerpt
I believe the data is considered required for replication until retrieved by
the distributor

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mokles" <mokles@.rogers.com> wrote in message
news:ncWdnd5XiKzAojvfRVn-sA@.rogers.com...
> Hello All,
> The setting is SQL server 7, on Windows NT.
> If the distributor can not access the log on the publisher database ( but
> subscribers are not deleted yet), will the full backup of the publisher
> database truncate the transaction log? My guess is, it will not truncate
> the
> log.
> Can any one confirm please?
> Thanks,
> Mokles|||Dan Guzman,

Thanks for your confirmation and explanation.

Mokles

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ZSspe.1864$751.1493@.newssvr30.news.prodigy.co m...
> Full backups don't truncate the log; the log is truncated by BACKUP log.
> From the Books Online:
> <Excerpt href="http://links.10026.com/?link=\tsqlref.chm::/ts_ba-bz_35ww.htm">
> Once the log is backed up, the space may be truncated when no longer
> required by replication or active transactions.
> </Excerpt>
> I believe the data is considered required for replication until retrieved
by
> the distributor
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "mokles" <mokles@.rogers.com> wrote in message
> news:ncWdnd5XiKzAojvfRVn-sA@.rogers.com...
> > Hello All,
> > The setting is SQL server 7, on Windows NT.
> > If the distributor can not access the log on the publisher database (
but
> > subscribers are not deleted yet), will the full backup of the publisher
> > database truncate the transaction log? My guess is, it will not truncate
> > the
> > log.
> > Can any one confirm please?
> > Thanks,
> > Mokles

does the transaction log keep track of who did what?

Hello Guys,

Does any one know if the transaction log keep track of the user who did the operation or not?

coz i tried the command which have been written in the following post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1054474&SiteID=1

but it didn't show the user who do the operation

MagedSalah wrote:

Hello Guys,

Does any one know if the transaction log keep track of the user who did the operation or not?

coz i tried the command which have been written in the following post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1054474&SiteID=1

but it didn't show the user who do the operation

Yes...the information is logged. If the command you tried was DBCC LOGINFO...it won't show you who did what. As mentioned in the thread, you need a third party tool to view who did what command. Using DBCC LOGINFO isn't going to help you much in analyzing transactions and activity based on what is in the log. It's used more for checking VLFs and statuses.

-Sue
|||

It is best to NOT rely upon 'undocumented' functionality.

Using Profiler, or one of the many third party log tools, you can determine who caused an action. (Profiler must be running in advance, whereas many of the third party tools can extract that infomation from the logs. Here are some of the options:


Lumigent SQL Server log explorer
ApexSQL Log Navigator
Log P.I.
SQL Log Rescue from red-gate