Increasing data space available in SQL 6.5 1

[mmagic]

I am running SQL 6.5 on NT4. I have a database device and database that are both 5GB. The current data space available is within the database is 1GB. I have deleted 50% of the records in the database and have confirmed by the record counts that they are deleted. I was anticipating the data space available to increase by 2GB leaving me with 3GB free, however, I only have 1.5GB free. This doesn't seem to be proportionately correct.

I also noticed that the backup device file size is 50% of the orginial also leading me to believe that the records were purged properly.

Is there a database command I need to run in order to recover the data space within the database? I plan on leaving the device and database sizes at 5GB due to hard drive limitations.

Thank you

 

[JNC73]

Hi mmagic,

I believe your DBA must be put to work to make sure the available space is really available.

I don't know exactly what must be done but the DBCC command must be used several times for different actions.

I believe one is DBCC DBREINDEX for each table.

Another command must be executed to make sure the database is correct ( this means that there are no lost clusters etc.) What the command for this action is I donnot know but I know the execution time of these commands can cost as much as 24 hours so these commands can only be executed when no one is working on the database.
Preferably execute them in the evening or in the weekends.

 

[JNC73]

Another command is I think

DBCC CHECKDB <database name>

or DBCC CHECKTABLE <tablename>

But for the correct syntax of these commands see transact SQL Help file.

JNC73

 

[mmagic]

Thank you so much for responding.

I am running the following database maint commands once a week via scheduled tasks and still haven't seen the data space I was expecting. Maybe I am missing something a command. Any ideas?

dbcc checkdb with no_infomsgs
go
dbcc checkcatalog with no_infomsgs
go
dbcc newalloc with no_infomsgs
go
smUpdateStatistics
go
smRecompile
go
REBUILDINDEXES
go

 

[JNC73]

The only command I can think of you haven't mentioned is

DBCC UPDATEUSAGE (<dbname>) WITH COUNT_ROWS

Any other command I know you allready executed.

JNC73

 

[jtl6713]

Try the following

dbcc checktable (syslogs)
dump tran <database name> with no_log
dbcc checktable (syslogs)
dbcc perflog

This seemas to work when all else fails!! Joseph Logan
Softsource, Inc.
Software Engineer
jlogan@softsource.net