Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Compacting MS SQL Server 2000 database

Hi experts,
How to compact a MS SQL Server 2000 database from within VB6 using SQLDMO?
Thanks in advance,
Ganaa
0
Ganaa
Asked:
Ganaa
  • 2
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
There is no such thing. It is not like Access. SQL re-use its lost space itself.
0
 
kmorris1186Commented:
This is the closest you going to get to the "compact" feater of Access..

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp

also preforms the same function as "Shrink" from Enterprise Manager.
0
 
XgenXCommented:
The proper way to do this is to set up a database maintenance plan in the SQL Enterprise Manager. This is where you will set up a backup plan for the database. I find that it works best if you set up a database backup and a log file backup seperately. Depending on the strain on your database you could do something like backup the full database twice a day, and backup the log file every 20 minutes. In the database properties set the autoshrink property to true. The database files will be trimmed when the database is backed up but the log files are trimmed when the log is backed up. This is why you may see an enourmous log file and you may have to go into query analyzer and type 'backup log <databasename> with truncate_only'. There are other settings in the backup that will redo the indexes and allow you to set the freespace size to something like 20% or so so that it won't have to resize the database as soon as someone writes to it.
0
 
XgenXCommented:
By the way, if you want, you may be able to set up that maintenance plan in SQLDMO.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now