0

I've got a database on SQL Server 2008 R2 that has grown to 10GB (log file is tiny). Users cannot work because they get the message about not being able to allocate space, filegroup PRIMARY being full.

Database is in simple mode, the .mdf file is set to unlimited growth, the initial size says 10240MB and when I change it trough the manager it just goes back up.

Naturally shrinking trough Manager or T-SQL DBCC SHRINKFILE or SHRINKDATABASE does not work because of that (IIRC, shrink cannot go below minimal size).

What are my options now?

Create a new filegroup?

Why can't I reduce initial size?

SEE IMAGE

EDIT: Problem solved, thank you everyone.

EternallyConfused
  • 1
  • 3
  • 4
    Are you using Express, by any chance? Also, why are you still using SQL Server 2008? It has been *completely* unsupported for well over 3 years now; you really need to finalise and implement your upgrade path. Even SQL Server 2012, which was the next major release after 2008, is now completely unsupported. – Thom A Nov 29 '22 at 10:57
  • Also, the reason you can't shrink it (which is generally a bad idea) is because there is no free space; shrinking releases any free space in the database, so if there is no free space then there's nothing to release and thus the database can't get smaller. – Thom A Nov 29 '22 at 11:03
  • I checked, the database holds tons of ducumetns scans and images, so that's why it's that big and can't be shrunk. Well, I can make a copy of the database, empty it and make the full one the archive. – EternallyConfused Nov 29 '22 at 11:28
  • 2
    That's not really the "solution" in my mind. If you need databases larger than 10GB then you should be upgrading off of Express. There's also absolutely *no excuse* to be using 2008 R2 Express. – Thom A Nov 29 '22 at 11:33
  • I am aware, upgrade was recommended to the user. We have a lot of users and a lot of stuff is running on Legacy software still. The databases they use don't normally grow this large, this is a multi-year database that holds tons and tons of stuff. This question can be closed as my boss pretty much greenlit the "make archive database", empty current" idea. – EternallyConfused Nov 29 '22 at 12:09

0 Answers0