TSQL optimize database

Question

TSQL optimize database

Daryl AZ

Hall of Fame

Points: 3087
More actions
March 1, 2006 at 10:23 am

#167226

Greetings:
I have a nightly process that after pulling numerous data tables from another (dts) source, then kicks off a stored procedure to backup the database.
I think i am missing a fairly straight-forward TSQL command to also run an optimize step at this point. Basically I am looking for a TSQL command to optimize the database prior to backing it up.
All assistance appreciated.
thanks
Daryl

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

A DBCC DBREINDEX would probably be useful. If the tables that are pulled in are essentially readonly for reporting purposes, you could DBREINDEX with a 100% fillfactor, which would reduce the number of pages required for each table and reduce the backup time/size.

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 2

I have perused DBCC DBREINDEX. I have the database set to RESTRICTED_USER so would have sole access.

I'll do some research to see if DBCC DBREINDEX covers the tasks in the maintenance Optimization step.

Thanks

paramind SSCrazy Points: 2160 More actions · Answer 3

As for performance and large amounts of data it might even prove useful to first drop the index and recreate it afterwards, but that depends on the data ...

_/_/_/ paramind _/_/_/

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 4

Thanks all for feedback/assistance:

I created an additional sproc prior to my backup sproc that performs the optimization and transaction log sizing. (Note: i did NOT shrink the log as much as pick a common-sense size)

I am working in a multiple database (identical databases) area and using dynamic SQL

-- looped through an array of tables for index rebuilding

SET @inputstring = 'DBCC DBREINDEX (''target_database..target_table'')'

-- control transaction file size

-- Note the ' USE THIS_DATABASE ' prior to maintenance script

SET @inputstring = 'Use target_database DBCC SHRINKFILE ('+@log_file+', 100)'

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

Ummmm... why not just setup a standard maintenance plan from Enterprise Manager whick, I believe, will do all of that for you.

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

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 6

Errrr,

The separate databases are backed up and optimized after the (nightly)data pull/increment. The request from the customer was to have them available asap for any nightly scheduled reports against the latest data.

There was a standard maintenance plan running in Enterprise Manager. This is the replacement.

-- Daryl Smith