Shrink MSDE database at 2 Gig limit

[simian101]

I have a MSDE database that is at the 2 gig limit.

We removed alot of history. There should be pleanty of space if it were reduced.

I have tried sp_shrinkfile and it did not work.

What is the best way to reduce the .mdf.

Thanks

Simi

 

[simian101]

sp_spaceused says

reserved data index_size unused
------------------ ------------------ ------------------ ---------
2650712 KB 1565520 KB 1055544 KB 29648 KB

I guess the index size is a large part of the problem.

I tried to reindex it but it says no pages are available because it is out of space.

Thanks

Simi

 

[yelworcm]

In order to defragment these indexes, you will have to actually drop then rebuild (i.e. using DROP INDEX/CREATE INDEX). Remember to be sure what the exact index definition is, before you drop any, though. Also, as stated in another thread with the same problem, you will want to look into using SQL 2005 or even SQL 2008 Express.

 

[mutley1]

If 2005 / 2008 express is not available, run sp_updatestats on the user databases that you have deleted history from then try the following on said user databases.

DBCC SHRINKDATABASE (N'USERDBNAME', 0)

It will return available free space to the OS.

HTH,

M.