Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

transaction log space per transaction

5 posts in General Discussion Last posting was on 2010-11-18 15:23:31.0Z
mastar Posted on 2010-11-16 09:05:04.0Z
Sender: 6368.4ce248e3.1804289383@sybase.com
From: mastar
Newsgroups: sybase.public.ase.general
Subject: transaction log space per transaction
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ce24940.6371.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Nov 2010 01:05:04 -0800
X-Trace: forums-1-dub 1289898304 10.22.241.41 (16 Nov 2010 01:05:04 -0800)
X-Original-Trace: 16 Nov 2010 01:05:04 -0800, 10.22.241.41
Lines: 3
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29704
Article PK: 78932

Is there a way to determine the amount of transaction log
space
used by a specific transaction ?


"Mark A. Parsons" <iron_horse Posted on 2010-11-16 09:19:00.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: transaction log space per transaction
References: <4ce24940.6371.1681692777@sybase.com>
In-Reply-To: <4ce24940.6371.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ce24c84$1@forums-1-dub>
Date: 16 Nov 2010 01:19:00 -0800
X-Trace: forums-1-dub 1289899140 10.22.241.152 (16 Nov 2010 01:19:00 -0800)
X-Original-Trace: 16 Nov 2010 01:19:00 -0800, vip152.sybase.com
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29705
Article PK: 78934

You'll have to take into consideration:

- minimally logged vs fully logged operations

- amount of data affected by a DML operation (eg, a tinyint column will require less space than a 1000 character string)

- direct (less log usage) vs deferred (more log usage) operations

- presence of triggers

- follow-on DML operations generated by triggers

- extra space required for computed columns, functional index maintenance, column encryption,
and-I'm-sure-I'm-forgetting-some-other-features-that-use-log-space

Your best bet is to run some tests with realistic DML operations, take logsegment usage numbers before and after said
test, then use the log space delta as a rough estimate for your calculations.

mastar wrote:
> Is there a way to determine the amount of transaction log
> space
> used by a specific transaction ?


albm Posted on 2010-11-16 09:46:48.0Z
Sender: 6368.4ce248e3.1804289383@sybase.com
From: albm
Newsgroups: sybase.public.ase.general
Subject: Re: transaction log space per transaction
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ce25308.643c.1681692777@sybase.com>
References: <4ce24c84$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Nov 2010 01:46:48 -0800
X-Trace: forums-1-dub 1289900808 10.22.241.41 (16 Nov 2010 01:46:48 -0800)
X-Original-Trace: 16 Nov 2010 01:46:48 -0800, 10.22.241.41
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29706
Article PK: 78935

Ok, thanks for your quick response. This describes the
possible calculation of a known transaction (the data
affected is known). What I was looking for is how to monitor
the log space used by a open/running transaction. Looking at
lct_admin("reserved_for_rollbacks" or syslock I can only see
the overall log space but not log space per transaction.

> You'll have to take into consideration:
>
> - minimally logged vs fully logged operations
>
> - amount of data affected by a DML operation (eg, a
> tinyint column will require less space than a 1000
> character string)
>
> - direct (less log usage) vs deferred (more log usage)
> operations
>
> - presence of triggers
>
> - follow-on DML operations generated by triggers
>
> - extra space required for computed columns, functional
> index maintenance, column encryption,
> and-I'm-sure-I'm-forgetting-some-other-features-that-use-l
> og-space
>
> Your best bet is to run some tests with realistic DML
> operations, take logsegment usage numbers before and after
> said test, then use the log space delta as a rough
> estimate for your calculations.
>
> mastar wrote:
> > Is there a way to determine the amount of transaction
> > log space
> > used by a specific transaction ?


Bret Halford Posted on 2010-11-16 18:09:11.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: transaction log space per transaction
References: <4ce24c84$1@forums-1-dub> <4ce25308.643c.1681692777@sybase.com>
In-Reply-To: <4ce25308.643c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ce2c8c7$1@forums-1-dub>
Date: 16 Nov 2010 10:09:11 -0800
X-Trace: forums-1-dub 1289930951 10.22.241.152 (16 Nov 2010 10:09:11 -0800)
X-Original-Trace: 16 Nov 2010 10:09:11 -0800, vip152.sybase.com
Lines: 107
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29708
Article PK: 78936


On 11/16/2010 2:46 AM, albm wrote:
> Ok, thanks for your quick response. This describes the
> possible calculation of a known transaction (the data
> affected is known). What I was looking for is how to monitor
> the log space used by a open/running transaction. Looking at
> lct_admin("reserved_for_rollbacks" or syslock I can only see
> the overall log space but not log space per transaction.
>

As a rough measure of this, you could use the number of
log records per transaction. It is rough because log records
have varying sizes - an update of a char(1) field is smaller
than an update of a char(1000) field.

The syslogs table has a row in it for every log record, giving
the transaction id (xactid) and log record type (op).
The transaction id is a binary value containing the pagenumber
(4 byte int) and row number (2 byte smallint) of the BEGINXACT
log record for the transaction. The BEGINXACT log record
includes the spid, user id, and datetime the transaction began.

This query will return a list of the open transactions, the
number of log records in each, and the dbcc log command to use
to view the full BEGINXACT log record. Use "dbcc traceon(3604)"
to direct the output of dbcc log to the client screen.

select
xactid,
count(*) as "size",
"dbcc log( "
+ db_name()
+ ",1, "
+ str(convert(int,xactid),10,0)
+ ", "
+ str(convert(smallint, substring(xactid,5,2)),10,0)
+ ", 1, 0)" as "dbcc command"
from
syslogs
group by
xactid
having
xactid not in (
select
xactid
from
syslogs
where
op = 17 /*checkpoint is atomic, no commit*/
or op = 30 /*commit tran */
)
order by
count(*)

go


Sample output:

xactid

-------------- -----------

-------------------------------------------------------------------------
0x000003b6001b 2
dbcc log( tempdb,1, 950, 27, 1, 0)
0x000003a70015 978
dbcc log( tempdb,1, 935, 21, 1, 0)

(2 rows affected)


So the second row is the biggest with 978 log records.

--------------------------------------------------------------------
1>dbcc log( tempdb,1, 935, 21, 1, 0)
2> go
LOG SCAN DEFINITION:
Database id : 2
Forward scan: starting at beginning of log
Log records for session id 935,21,0
Log operation type BEGINXACT (0)
maximum of 1 log records.

LOG RECORDS:
BEGINXACT (935,21) sessionid=935,21,0
attcnt=1 rno=21 op=0 padlen=1 sessionid=935,21,0 len=76
odc_stat=0x0000 (0x0000)
loh_status: 0x0 (0x00000000)
masterxsid=(invalid sessionid)
xstat=XBEG_ENDXACT,
spid=19 suid=1 uid=1 masterdbid=0 dtmcord=0
name=$user_transaction time=Nov 16 2010 10:42:19:910AM


Total number of log records 1
DBCC execution completed. If DBCC printed error messages, contact a user
with
System Administrator (SA) role.

-------------------------------------------------------------------

So the biggest transaction is being run by spid 19, who is logged
in as suid 1 (sa). The transaction was an unnamed user transaction.
(note: here is where it is very helpful if the application code uses the
optional transaction names when doing BEGIN TRAN) - but you
can also now cross reference the MDA montitoring tables to view the
SQL Text spid 19 was executing.


mastar Posted on 2010-11-18 15:23:31.0Z
Sender: 4954.4ce52373.1804289383@sybase.com
From: mastar
Newsgroups: sybase.public.ase.general
Subject: Re: transaction log space per transaction
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ce544f3.4da9.1681692777@sybase.com>
References: <4ce2c8c7$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Nov 2010 07:23:31 -0800
X-Trace: forums-1-dub 1290093811 10.22.241.41 (18 Nov 2010 07:23:31 -0800)
X-Original-Trace: 18 Nov 2010 07:23:31 -0800, 10.22.241.41
Lines: 122
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29717
Article PK: 78947


> On 11/16/2010 2:46 AM, albm wrote:
> > Ok, thanks for your quick response. This describes the
> > possible calculation of a known transaction (the data
> > affected is known). What I was looking for is how to
> > monitor the log space used by a open/running
> > transaction. Looking at
> lct_admin("reserved_for_rollbacks" or syslock I can only
> > see the overall log space but not log space per
> transaction. >
>
> As a rough measure of this, you could use the number of
> log records per transaction. It is rough because log
> records have varying sizes - an update of a char(1) field
> is smaller than an update of a char(1000) field.
>
> The syslogs table has a row in it for every log record,
> giving the transaction id (xactid) and log record type
> (op). The transaction id is a binary value containing the
> pagenumber (4 byte int) and row number (2 byte smallint)
> of the BEGINXACT log record for the transaction. The
> BEGINXACT log record includes the spid, user id, and
> datetime the transaction began.
>
> This query will return a list of the open transactions,
> the number of log records in each, and the dbcc log
> command to use to view the full BEGINXACT log record. Use
> "dbcc traceon(3604)" to direct the output of dbcc log to
> the client screen.
>
> select
> xactid,
> count(*) as "size",
> "dbcc log( "
> + db_name()
> + ",1, "
> + str(convert(int,xactid),10,0)
> + ", "
> + str(convert(smallint, substring(xactid,5,2)),10,0)
> + ", 1, 0)" as "dbcc command"
> from
> syslogs
> group by
> xactid
> having
> xactid not in (
> select
> xactid
> from
> syslogs
> where
> op = 17 /*checkpoint is atomic, no
> commit*/
> or op = 30 /*commit tran */
> )
> order by
> count(*)
>
> go
>
>
> Sample output:
>
> xactid
>
> -------------- -----------
>
> ----------------------------------------------------------
> ---------------
> 0x000003b6001b 2
> dbcc log( tempdb,1, 950, 27, 1,
> 0)
> 0x000003a70015 978
> dbcc log( tempdb,1, 935, 21, 1,
> 0)
>
> (2 rows affected)
>
>
> So the second row is the biggest with 978 log records.
>
> ----------------------------------------------------------
> ---------- 1>dbcc log( tempdb,1, 935, 21, 1
> , 0) 2> go
> LOG SCAN DEFINITION:
> Database id : 2
> Forward scan: starting at beginning of log
> Log records for session id 935,21,0
> Log operation type BEGINXACT (0)
> maximum of 1 log records.
>
> LOG RECORDS:
> BEGINXACT (935,21)
> sessionid=935,21,0
> attcnt=1 rno=21 op=0 padlen=1 sessionid=935,21,0
> len=76
> odc_stat=0x0000 (0x0000)
> loh_status: 0x0 (0x00000000)
> masterxsid=(invalid sessionid)
> xstat=XBEG_ENDXACT,
> spid=19 suid=1 uid=1 masterdbid=0 dtmcord=0
> name=$user_transaction time=Nov 16 2010
> 10:42:19:910AM
>
>
> Total number of log records 1
> DBCC execution completed. If DBCC printed error messages,
> contact a user with
> System Administrator (SA) role.
>
> ----------------------------------------------------------
> ---------
>
> So the biggest transaction is being run by spid 19, who is
> logged in as suid 1 (sa). The transaction was an unnamed
> user transaction. (note: here is where it is very helpful
> if the application code uses the optional transaction
> names when doing BEGIN TRAN) - but you can also now cross
> reference the MDA montitoring tables to view the SQL Text
> spid 19 was executing.

Brilliant! Thanks a lot Bret !