Filegroup Space Issues

Question

question

Sagar Bhargava asked Feb 22, '10

Filegroup Space Issues

I can see the below error in the Event viewer and the SQL Server 2008 Express seem to be timing out when trying to login:

"Could not allocate space for object 'dbo.VerboseLogs'.'PK_Logs' in database 'XXXXX' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."

Any Ideas? There is plenty of space available on the drive. Also, Log file is set to Autogrow by 10 percent, restricted growth till 2,097,152 MB. MDF is set to Autogrow by 1 MB, unrestricted growth.

filegroup space

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-22T09:56:43Z

Matt Whitfield answered Feb 22, '10

Yes, SQL Server Express is limited to 4GB... so you can't exceed that limit no matter how much space you have on the disk.

If you want to have a look at how the space is used, and drill down, then try out my tool for that.

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield ♦♦ commented · Feb 22, 2010 at 11:00 AM

:) All the products are available free, so I don't feel guilty! Plus I only link to them where it's 100% relevant, and I always include links to the Red Gate / Apex equivalents where there are any... So yeah, guilt factor = 0.

1 ·

Fatherjack ♦♦ commented · Feb 22, 2010 at 10:54 AM

+1 - for ability to plug product shamelessly ;)

0 ·

TimothyAWiseman commented · Feb 22, 2010 at 02:35 PM

Its a good tool! Anyway, now may be a good time to look at SQL Server 2008 Standard. If that is not an option, consider splitting the data into multiple databases.

0 ·

Fatherjack ♦♦ commented · Feb 22, 2010 at 03:26 PM

agreed a very good tool, I use it and am pleased with the view it gives me. Sometimes pictures/images show the details more quickly than a screen full of numbers.

0 ·

Matt Whitfield ♦♦ commented · Feb 22, 2010 at 04:30 PM

Thanks guys :) glad you like it...

0 ·

Answer 2 · 2010-02-22T09:57:45Z

Fatherjack answered Feb 22, '10

How big is the log file on the hard drive, has it reached the 2GB limit?

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sagar Bhargava commented · Feb 22, 2010 at 10:14 AM

I have shrinked this log file as a means to create some space as this is not a heavily used production box. Can this issue cause Timeout issue during login to Management Studio. I noticed server team has installed Sophos Antivirus on this box. I have heard AV causing issues with DB servers.

0 ·

Fatherjack ♦♦ commented · Feb 22, 2010 at 10:56 AM

if the log file was full then no database activity would take place. Do you have regular jobs to backup the database? You may be able to switch to SIMPLE recovery mode to reduce the entries being stored in the logfile.

0 ·

Sagar Bhargava commented · Feb 23, 2010 at 02:56 AM

Can a space issue with one filegroup of a database cause a timeout during login to Management Studio?

0 ·

Fatherjack ♦♦ commented · Feb 23, 2010 at 06:54 AM

the fact is that the logfile was unable to log any database activity so would not allow connections. this needs to be managed, either by no logging EVERY transaction (ie SIMPLE recovery mode) or making more room. You could add extra logfiles (mylogfile.ndf) up to the maximum size of a SQLExpress database as mentioned by Matt. You also need to run regular backups in order to keep the logfile at a manageable size, whatever other options you choose.

0 ·

question