Table level reorg or Shrinking

Question

Table level reorg or Shrinking

sriram.sbg

SSC Enthusiast

Points: 177
More actions
February 28, 2010 at 6:35 am

#137447

Hi All
We are having SAP-BW with Ms sql 2005, when every we are doing any table maintenance job, disk space not increasing, everyday we are doing truncate log backup. Still the database level table size not getting reduces. My query
Is it possible to do the table level reorg job in Mssql? or any tools are available?
Kindly help us
Regards
Sriram

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

To do a table level reorg, you would be doing a clustered index reorg. You can do a reorg on your indexes in a job in MSSQL on a daily basis.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

My question would be... why are you truncating the log to begin with? Aren't backups and restorability important?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Two things that you may want to read:

Managing Transaction Logs[/url]

http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Jeff Moden (2/28/2010)
My question would be... why are you truncating the log to begin with? Aren't backups and restorability important?

I was hoping that it was simply a mis-type and that he meant he was doing a backup.

sriram.sbg

Gail's articles are worth reading!!!!

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Probably some language barriers here, but it sure sounds like you need an expert to review your maintenance practices, ASAP.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

dbychen SSCommitted Points: 1889 More actions · Answer 6

Your description seems that your working database does not have a lot data growth in permanent tables but experiences a lot of expansion in the transaction log file each day. The transaction log file grows again and again after each truncate.

If this is true, it indicates large amount of transient data generated in the log file when the application runs against the database. If the same/similar application repeats running on the system, the log file will grow back. This growth is determined by the nature of the application. You should leave it since the application requires the space to run anyway.

If you want to minimize the log file size, you should look into how your application accesses the database and how it manipulates the data within. You either tune the codes or split the codes (if it is well tuned) into small transaction blocks to bring the log file growth under control.