Could not allocate space for object [INDEX] 1

[sugarflux]

Hi all

Hoping someone may be able to help me out as I'm stumped with this one...

I have a 'hosted' MS SQL Server (meaning I can't access the DB directly in Management Studio, I use RazorSQL and/or MyLittleAdmin). There is a size restriction on the DB of 2GB - currently my DB stands at 1500 MB.

I have an import routine which simply dumps around 640k rows into an "import" table for the rest of the routine to manipulate before truncating the table for the next import. It is whilst I'm importing this table I'm getting the error:

Could not allocate space for object 'dbo.PK__XX_Imp__xxxxxxxx' in database 'MyDB' 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.

Now, the weird thing is that the XX_Import table is actually one of the smaller tables really and there are History tables that take up much more space.

Here's the breakdown of table XX_Import:

COLUMN BREAKDOWN:
ImportRowID (PK) int Not Null
10 x INT fields
2 x BIGINT fields (required as numeric data for these columns is often greater than 4bn)
2 x VARCHAR(40)
1 x VARCHAR(50)
1 x VARCHAR(140)

INDEXES:
PK__XX_Imp__xxxxxxxx (ImportRowID, UNIQUE)

CONSTRAINTS:
PK__XX_Imp__xxxxxxxx (PRIMARY KEY)

SPACE USED (sp_spaceused):
Rows: 108600 (this is where it stops out of the 640000)
Reserved: 9224 KB
Data: 9184 KB
Index_Size: 40 KB
Unused: 0 KB

I've tried recreating the table (and index) from scratch and dropping the old one. Same outcome.

So basically I need to know how to increase the allowed size for this table/index (through script and bearing in mind my access is completely locked down) or if this really is a disk-size restriction is there a way of reducing the size of my table...?

Any help or advice appreciated but please try to keep it simple as I'm fairly new to SQL Server Configuration!

Thanks

~S~

 

[gmmastros]

Can you run this in a query window and then post the results?

sp_helpfile

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sugarflux]

Thanks gmmastros

[pre]
name fileid filename filegroup size maxsize growth usage
db00000000 1 X:\MSSQL\Data\db00000000.mdf PRIMARY 894336 KB 1024000 KB 25% data only
db00000000_log 2 X:\MSSQL\Data\db00000000_log.ldf 673536 KB 2147483648 KB 20% log only
[/pre]

* Files renamed for privacy

 

[gmmastros]

I see some things that may be your problem.

First, let me say that having a 2GB limit is ridiculous. Storage space is dirt cheap so having such a small limit does not make sense. I would encourage you to call your 'host'. Tell them you want more storage space and you don't want to pay any more money for it. Tell them that if they cannot do this, you will begin looking at other host providers.

I see that your log file is nearly as big as your data file. In fact, your data file is only 900 megs. Depending on how you do things, I would expect a 900 meg data file to have less than 100 meg of log file. Since your log file is as big as it is, I would guess that your recovery model is not SIMPLE and you are not periodically backing up the log file. If your log file was smaller, you would have room to grow your data file. Recommendation: backup your log file or set recovery model to simple

I see that you have a max size for your data file set to 1024 megs. Your data file is already at 900 megs with a 25% growth. When your database runs out of free space, it will try to grow. When it does, it will try to grow by 25%. 25% of 900 megs is 225 megs. 900 megs + 225 megs = 1125 megs, but your max size is 1024 megs. This is why you are running out of space and getting the errors that you are. Recommendation: Increase the max size of your data file to 1500 megs.

You should also change your growth setting. Currently, it's at 25%. This may be fine for smaller databases, but doesn't really work well when you get in the gigabyte range. Instead of using a percentage, I recommend that you set it to a specific size. Recommendation: Set your data file growth to 100 megs instead of a percentage.





-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sugarflux]

Hi George

Many thanks for your advice - and I agree having a 2GB limit is bad.

However, there are, unfortunately, issues with all of the above!

Firstly, I don't think I actually have access to the log file - certainly not to change the backup method. I also don't believe I have access to change the growth settings - well I don't have an interface to do so but perhaps this could be achieved through a script?

My host is currently 1and1 - they only offer 1GB MS SQL databases unless you take a dedicated server. Even for larger packages the databases are restricted to 1GB - they just give you more of them. I don't make any money from this project and therefore don't have the funds to be upgrading the package for this purpose.

I could threaten to leave the host (as I have done several times before) but it would be terribly complicated and expensive (I have a LOT of domains/sites all hosted in the same place) and - unfortunately - they really don't seem to care whether I remain a customer or not(!).

Perhaps I really should be looking into new hosts but for the time being I still need to get this DB working one way or another. Perhaps I need to re-assess the other tables to try and reduce the size of the DB overall.

Thanks anyway for your help,

~S~

 

[sugarflux]

As an extra note I've started to try to convert some of my columns to reduce the size on disk. For example, when the database was built the majority of numeric columns were created as INT even when they would contain smaller numbers. So changing these into TINYINT or SMALLINT I would've expected to reduce the size - however the size retrieved with sp_helpfile does not seem to be affected...

Have I mis-understood?

Thanks again

~S~

 

[gmmastros]

There is a script you can use to change the growth setting. Something like...

ALTER DATABASE YourDatabaseName
  MODIFY FILE
  (NAME=YourDatabase_LOg,FILEGROWTH=50MB);

For the most part, databases will not get any smaller unless you actually shrink it. Changing data types will not usually save you too much space in the database. Think of it this way... an int takes 4 bytes. Small Int is 2 bytes and tinyint is 1 byte. If you change an int to a small int, the absolute best you can do is save 2 bytes per row. So, it you have a table with 1,000,000 rows, the absolute best you can do is save 2 megabytes. In reality, you will probably not save anything because of the way SQL Server stores the data on disk. All data is stored in 8 KB chunks (called a page). If each row takes 400 bytes, you will get approximately 20 rows per page (8192 / 400) = 20.48. If you change a single int to small int, this saves you 2 bytes per row, but now the average row is 398 bytes instead of 400. 8192 / 398 = 20.58, so you will still only get 20 rows per page because SQL server will keep all the data for a row on the same page. To see any space savings, you need to get more rows per page. Changing int to smallint will probably not save you any space. Imagine a phone book. It can only hold so many phone number entries on a given page. If you abbreviate certain names like "Matthew" to "Matt", this would not change the number of entries on a page. The phone book would not get any smaller.

If you are able to save space in your tables by reducing the number of required pages to store the data, you will still not see any file size reduction because SQL Server doesn't work that way. What you will see is an increase in available space. To make the file smaller, you would need to shrink the database. Shrinking the database is NOT something you should do regularly. In fact, the only time I recommend shrinking a database is if you've just deleted a lot of data and really want a smaller database size.

One thing you may be able to do is to reduce the storage space by investigating your indexes. Generally speaking, there are 2 types of indexes, Clustered and non-clustered. A clustered index actually stores all the data in the table (this it where it actually lives). Non-clustered indexes take up additional space in the database. Back to the phone book, imagine that you created an index on the phone number column. You would now have the original phone book (indexed on last name, first name) and another "book" indexed on phone number. The additional book would require more pages and your book would get bigger. My point is this, it is possible that there are indexes on your tables that don't get used (or are rarely used). If you drop those indexes, you will have more available space in your database.

To see the indexes on a table, run the command sp_helpindex 'TableNameHere' This will show you the indexes on the table and the columns that make up the indexes. If you find indexes that you can drop, then you will recover space and be able to continue the operation in your original question.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sugarflux]

George

This was awesome information - thanks!

This completely explains - very simply - why the database was not changing in size due to my lack of understanding. My indexing is actually very simple, generally just primary keys. The data is heavily normalised and there is very little searching going on outside of PKs. I could spend some time looking at indexing (although if I'm honest I have a hunch that I'd probably end up adding more indexes than are there currently).

For now, I have altered the MaxSize (2GB) and the Growth (100 MB). I managed to perform a backup and altered the recovery to SIMPLE. I then used DBCC shrinkfile on the log.

This has certainly put me on the right path and the import routines are now running - so far successfully - so thank you so much for all your help.

I know I need to come back to this DB and investigate the bloating a bit further, and I'm also aware that ideally I need to be looking for a better host. This has sorted the current problem and got me back up and running,

Thanks again,

~S~

 

[gmmastros]

Glad I could help!

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom