Unable to get rid of Unused Space in SQL 2005 Database

[Ovatvvon]

Hi All,

I have a 270 GB database that has a lot of activity on it 24/7. Approximately 30% of the database is unused space. I've tried shrinking it every which way, but can't. It completes successfully, but the space remains. (I can get rid of unallocated space no problem, but unused remains.)

When I run sp_spaceused, I receive messages like this:

[COLOR=red]Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'al__dk_pr' does not exist in database '{database_name}' or is invalid for this operation.[/color]

(0 row(s) affected)
[COLOR=red]Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'al_rpt' does not exist in database '{database_name}' or is invalid for this operation.[/color]

(0 row(s) affected)
[COLOR=red]Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'GrHour' does not exist in database '{database_name}' or is invalid for this operation.[/color]

(0 row(s) affected)

I have run sp_updatestats and dbcc updateusage, but these do not help.

Does anyone have any ideas one what the problem is here? I've not run into this before, but it's been going on a for a while now, with no resolution. Any help would be greatly appreciated!



-Ovatvvon :-Q

 

[Ovatvvon]

Nevermind, I just realized the databases have a different owner name than dbo, and that's why they were coming up as non-existing. When I used "setuser '{user name}'" and ran it again, it worked.

Thanks anyway!


-Ovatvvon :-Q

 

[macleod1021]

Did you shrink the files? Within Management Studio, you can do a right mouse click over the database -> Tasks -> Shrink -> Files.

Before you do that, I would review your statistics. You stated that the database sees a lot of activity. If you shrink the files and the database needs to expand due to space needs, you're just adding overhead to the processes.

HTH

 

[Ovatvvon]

macleod1021,

As I've mentioned in the first post, I have tried several different methods to shrink the database, including the GUI methods, as well as DBCC.

I also mentioned in the first post that I ran update stats, usage, etc...to no avail.

Thank you for the post though. As for right now, I think I have to get down to the nitty-gritty and run some custom reports on the tables to find the ones with the greatest amount of unused space, and then check out column data types, as well as other things, to figure out where the space is being reserved at.

Thanks again!


-Ovatvvon :-Q