You are not logged in or registered. Please login or register to use the full functionality of this Website SybaseTeam.Com...
Hello There, Guest! (LoginRegister) Remember Me? Current time: 09-14-2014, 10:59 PM
   Home  |  About Us  |  Sybase Training  |  Synergy  |  Consulting  |  Job Openings  |  Tech Videos  |  Rules and Disclaimer  |  Search
Post Reply 
Forum Tools
log space requirements for reorg rebuild
05-27-2009, 04:59 AM
Post: #1
Quote this message in a reply
log space requirements for reorg rebuild


Hi all
I want to know how much log space is required to rebuild a table. For example if I have a table of 10 GB then to run reorg rebuld on this table how much log space is required.(i guess 120% of data space is required for reorg rebuild a table)

And if my log space is not sufficient then is it possible to add some log device temporarily and remove it later from the database(which i guess is not possible in Sybase. What I know is that we can only add space to a database using alter database, but can't reduce it anyhow, Please correct me if I am wrong).

Please help me out.
Regards
Ishu Srivastava


Find all posts by this user
08-14-2010, 03:28 PM
Post: #2
Quote this message in a reply
RE: log space requirements for reorg rebuild


Ishu,

You are correct.
Sybase database (data/log) can be expanded but cannot be shrunk.

Not sure how much log.Whatever is allocated to the database already should be good enough to rebuild a table.Did you check what was holding up the log?

Kishor


Find all posts by this user
10-05-2011, 08:38 AM
Post: #3
Quote this message in a reply
RE: log space requirements for reorg rebuild


Hi all,

Now we are with the latest version Sybase ASE 15.7.

We have a option for shrinking log space like the way how we alter the log space.

alter database ... log on
alter database ... log off


Find all posts by this user
10-05-2011, 07:13 PM
Post: #4
Quote this message in a reply
RE: log space requirements for reorg rebuild


(05-27-2009 04:59 AM)ishu.cs Wrote:  I want to know how much log space is required to rebuild a table.
It depends on which command you use to rebuild the table; what data and log requirements that command incurs. Eg. DROP/CREATE CLUSTERED INDEX is quite different to REOG REBUILD. Eg. if you rebuild in place (do not re-write data pages) vs re-write data pages and reserve some space.
Quote:For example if I have a table of 10 GB then to run reorg rebuld on this table how much log space is required.(i guess 120% of data space is required for reorg rebuild a table)
No. And the previous response confirming that is incorrect.

120% of data (not log) space is required when you DROP/CREATE CLUSTERED INDEX, without the SORTED_DATA option, which rewrites all data pages, affecting "rebuilding" the table. You have the opportunity to reserve space for future interspersed INSERTS at the Page and Extent level.

REORG REBUILD rewrites all Pages in the table in place. It repatriates Forwarded rows; Reclaims unused space per Page; and orders the rows into the Placement Index sequence (which is just an initial placement, that gets disrupted quickly under DML activity). There is no data space require (just a small working set in tempdb and sort buffers, etc.)

REORG REBUILD is a minimally logged operation. That means the physical object changes are logged (OAM; AllocUnits; AllocPages) but not the data changes. The command is already very slow, if data changes were logged, it would be even slower. The log space requirement is very small, you definitely do not need more log space.

Quote:And if my log space is not sufficient then is it possible to add some log device temporarily and remove it later from the database(which i guess is not possible in Sybase. What I know is that we can only add space to a database using alter database, but can't reduce it anyhow, Please correct me if I am wrong).
That is correct up to 15.5. In 15.7 you can shrink the log (but not dat).



Ashirvad to my Shishyas, Cheers to the others
Derek Asirvadem
Information Architect / Sr Sybase DBA
Website
Selection of Useful Documents for the Sybase DBA
Visit this user's website Find all posts by this user
01-30-2013, 04:39 AM
Post: #5
Quote this message in a reply
RE: log space requirements for reorg rebuild


Hi,

I have upgraded my sybase to 15.7 which is the latest one.

Say i have assigned 8MB (2*256 pages = 512 pages)to device that holds log only.

When i performed transaction,log space keeps on increasing that the free space becomes=80 pages and used space = 432 pages.
When i tried to use ,
alter database "databasename" log off "device_name"=4
(tried removeing - 256 pages=4MB),but it was giving me error.

1> alter database db log off dev=4
2> go
Msg 5056, Level 16, State 1:
Server 'servername', Line 1:
You cannot remove 256 pages (4 MB) from database 'db'; only 88 unreserved
pages (2 MB) are available.

Is there any new feature which manages tempdb logspace well other than
1. enabling "log on check point"
2.Running threshold procedure for running dump transaction.

I hope log off parameter was not introduced for this purpose.

Thanks.


(10-05-2011 07:13 PM)DerekAsirvadem Wrote:  
(05-27-2009 04:59 AM)ishu.cs Wrote:  I want to know how much log space is required to rebuild a table.
It depends on which command you use to rebuild the table; what data and log requirements that command incurs. Eg. DROP/CREATE CLUSTERED INDEX is quite different to REOG REBUILD. Eg. if you rebuild in place (do not re-write data pages) vs re-write data pages and reserve some space.
Quote:For example if I have a table of 10 GB then to run reorg rebuld on this table how much log space is required.(i guess 120% of data space is required for reorg rebuild a table)
No. And the previous response confirming that is incorrect.

120% of data (not log) space is required when you DROP/CREATE CLUSTERED INDEX, without the SORTED_DATA option, which rewrites all data pages, affecting "rebuilding" the table. You have the opportunity to reserve space for future interspersed INSERTS at the Page and Extent level.

REORG REBUILD rewrites all Pages in the table in place. It repatriates Forwarded rows; Reclaims unused space per Page; and orders the rows into the Placement Index sequence (which is just an initial placement, that gets disrupted quickly under DML activity). There is no data space require (just a small working set in tempdb and sort buffers, etc.)

REORG REBUILD is a minimally logged operation. That means the physical object changes are logged (OAM; AllocUnits; AllocPages) but not the data changes. The command is already very slow, if data changes were logged, it would be even slower. The log space requirement is very small, you definitely do not need more log space.

Quote:And if my log space is not sufficient then is it possible to add some log device temporarily and remove it later from the database(which i guess is not possible in Sybase. What I know is that we can only add space to a database using alter database, but can't reduce it anyhow, Please correct me if I am wrong).
That is correct up to 15.5. In 15.7 you can shrink the log (but not dat).


Find all posts by this user
Post Reply 


Possibly Related Threads...
Thread: Author Replies: Views: Last Post
  log space full when transaction happens continuosly RUBIYA 6 2,960 03-07-2013 01:24 AM
Last Post: RUBIYA
  Sybase Tempdb space management and addressing tempdb log full issues zaadmin 3 24,355 08-07-2012 01:32 PM
Last Post: EngineNo9
  REORG REBUILD - How to determine space and/or move objects for mcnkcmo 0 1,799 07-27-2012 09:41 PM
Last Post: mcnkcmo
  How to Calculate Memory Usage and Disk Space Usage? rjvk 2 5,646 01-10-2012 10:51 AM
Last Post: Ugur Naciterhan
  Determining Free Log Space in Sybase ASE Joshi 0 5,447 12-04-2009 01:17 PM
Last Post: Joshi
  running reorg in sybase - reorg command usage zaadmin 0 8,417 06-18-2009 06:37 PM
Last Post: zaadmin
  monitor sybase tempdb log space albert 0 9,573 04-21-2009 05:28 PM
Last Post: albert

Options:
Forum Jump:


Contact Us | SybaseTeam | Disclaimer & Rules | Return to Top | Return to Content | Lite (Archive) Mode | RSS Syndication