none
SQL Server 2005 Express 4 GB limit passed

    Question

  • Hello,

    I'm doing some research for my study and I've installed an SQL Server Express 2005. Now the limit of 4 GB was exceeded and I also couldn't delete any data.

    I tried to truncate some data and that was successful (now I need only 1 GB), but the data file did not shrink. Now I tried to shrink the data file by calling SHRINKDATABASE and SHRINKFILE but the file did not shrink and there was an error message saying that there was not enough memory to do this.

    What can I do to shrink the data file so I can work with the database again?

    Thanks for your help.

    Thomas
    Tuesday, August 05, 2008 3:49 PM

Answers

  • Assuming you can still access the database, try using the DBCC command to shrink the file is smaller chunks; DBCC SHRINKFILE (<file_id>, <new size in mb> make sure you're in the correct database when you run the command.

    Run sp_helpdb <db name> to confirm the file id of the data file, although it will be 1 for a single file database.

    You may only be able to shrink it in very small chunks at a time (e.g. 50 MB), depending on resources and disk performance, although the advantage of this approach is each shrink should not take too long to complete.

     

    Tuesday, August 05, 2008 5:56 PM

All replies

  • Assuming you can still access the database, try using the DBCC command to shrink the file is smaller chunks; DBCC SHRINKFILE (<file_id>, <new size in mb> make sure you're in the correct database when you run the command.

    Run sp_helpdb <db name> to confirm the file id of the data file, although it will be 1 for a single file database.

    You may only be able to shrink it in very small chunks at a time (e.g. 50 MB), depending on resources and disk performance, although the advantage of this approach is each shrink should not take too long to complete.

     

    Tuesday, August 05, 2008 5:56 PM
  • That worked so far. I tried it with:

    DBCC SHRINKFILE(1, 4090)
    DBCC SHRINKFILE(1, 4050)
    DBCC SHRINKFILE(1, 4000)
    DBCC SHRINKFILE(1, 3000)
    DBCC SHRINKFILE(1)


    Now my database got back to normal size and I can work with it again.

    Thanks so far.

    Perhaps you could tell me something about this "auto shrink" option? How does it work and when and in which periods does it start?
    Thursday, August 07, 2008 12:25 PM
  • I wouldn't enable autoshrink on any production system.

    Dbs will be shrunk by a background process which checks each db every few minutes:

    http://support.microsoft.com/kb/315512

    You're better off assigning a fixed size for the database and (ideally) disabling autogrows/autoshrinks.

     

    Thursday, August 07, 2008 8:43 PM