2

What would the recommended best practices to keep a SQL Express 2005 database below the 4gb limit? I am willing to export and archive content out of the data tables, but am not sure this will help once they have expanded.

From what I read, it doesn't seem shrinking the database is what I want to be trying.

Trying to postpone the upgrade licensing a little.

datatoo
  • 351
  • 3
  • 12

1 Answers1

1

1) Delete data

If you have 1M records and want to keep only 10K of them I suggest you select last 10K in a temporary table, truncate entire table with TRUNCATE command, then insert 10K records back (you may need to switch off constraints and identity before the operation and switch them on again after it)

2) Make database backup

3) Shrink database file and transaction log.

Try to shrink individual files with DBCC SHRINKFILE if DBCC SHRINKDATABASE is not that you want (it cannot shrink more then initial database file, but I think this is not the point of your question)

I dont know why you think that shinking will not help you, pls give us some arguments.

There is also option AUTO_SHRINK but I never tried it with SQL Express

Also - if your database is growing because you have lot of digital content there (documents, pictures) consider redesining your system to store this content in file system and store only file names or hyperlinks in the database.

In most cases databases grow too fast if they have a lot of audit information (when each change in any table is recorded somewhere) or when there are files stored in the database. If your database grows to 4Gb and all this actual important data, like transactions, customers data etc., then SQL Express is just not right tool for you and the best choice will be to upgrade as soon as possible

Bogdan_Ch
  • 483
  • 1
  • 3
  • 12
  • You addressed most of what I am wondering. I had read that shrinking might cause file system fragmentation. I inherited this database from an app that uses it. There are audit trais on nearly every transaction, so that is what is expanding things. I want to develop a good maintenance plan and you have given me some good points.+1 – datatoo Dec 11 '09 at 07:42
  • I dont think file fragmentation will be an issue with databases of less then 4 Gb size is located on 120 Gb drive. File fragmentaion happen when file is growing, not when shrinking. What is more important is index (not file!) fragmentation. Read this article - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/ perhaps you will want to see if your indexes are defragmented and rebuild them after the database shrink. – Bogdan_Ch Dec 14 '09 at 22:31