4

We have a database running under MSDE (SQL 2000, service pack 4) that is reserving massive amounts of excess space. Using sp_spaceused for each table gives a total reserved size of 2102560 KB, a data size of 364456 KB and an unused size of 1690760 KB (i.e. reserving nearly 4 times the used space). The worst culprits are tables that are frequently written to but never deleted from (transaction logging). Generally, deletes are very infrequent and very small in terms of size and number of records.

The database files on disk are at the 2 gb limit and this is causing problems with backups etc.

I have tried DBCC SHRINKDATABASE, DBCC SHRINKFILE and DBCC REINDEX with no effect on the file size used on the disk

2 questions - How can I shrink the database file size and how can I stop SQL Server from reserving the excess space ?

Thanks

Paul

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Paul G
  • 61
  • 1
  • 2
  • 3

4 Answers4

4

USE < DBNAME >
GO
BACKUP LOG < DBNAME > WITH TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE ( < DatabaseName > )
GO
DBCC SHRINKFILE (< logfile >, 5)
GO
DBCC SHRINKFILE (< datafile >, 5)
GO

if you don't know the file paths exec sp_helpfile

Russell Steen
  • 6,494
  • 6
  • 38
  • 56
  • Tried this but it did not shrink the reserved space by 1 byte – Paul G Sep 03 '09 at 07:56
  • between your backup log en the shrinkfile use a DBCC SHRINKDATABASE ('DatabaseName') – Coentje Sep 03 '09 at 09:59
  • That worked a treat for me. Thanks @Russell! Just a quick note: For the DBCC SHRINKFILE commands, I used SELECT * FROM sys.database_files in the DBNAME database and used the "Name" field. – Ev. Jul 05 '11 at 07:48
0

what you could do is take a full db backup, reindex the db, incrementaly shrink it, then reindex it again. that way you'll have the db in it's current size.

also you should move your logging tables to another table.

Mladen Prajdic
  • 15,457
  • 2
  • 43
  • 51
  • Unfortunately (and I don't know why), backup was impossible. It would time out when trying to backup to disk. – Paul G Sep 03 '09 at 07:57
0

Create separate files for worst culprits and place them in separate filegroups. Moving tables to another file itself will compress them. Will also make shrinkfile more effective. If needed you can create more then one file per table.

  • Problem is that there is a 2 gig limit with MSDE. Creating a new file group is impossible as the limit is over the total of the file groups, not just each one. – Paul G Sep 03 '09 at 07:56
0

Thanks for all the suggestions. In the end, I had to create a new empty database, copy the data from the massive database and then rename the databases.

I will be keeping an eye out on the reserved sizes. Hopefully, there was something wrong with the database setup that caused this. None of our other customers using identical software / MSDE are having this problem.

Paul G
  • 61
  • 1
  • 2
  • 3