April 2, 2009 at 12:36 pm
Hello there,
I have a database called Customers. This database has only one table - CustInfo. This CustInfo has 3.89 million records, so the initial size of this table is 2 GB (.mdf) and 35K KB (.ldf). I run an alter statement so as to set a primary key to the column. Once this command is executed, the size if .mdf file becomes 4 GB. Now, I right click on the Customers database, under Tasks->Shrink->Database->Once I execute this, the size of the .mdf file becomes 2GB and .ldf is 765KB. Now, I have to create indexes for 10 fields, once I am done creating those indexes, the database size is back to 4GB again. I again shrink the database but this time, only the log file is shrunk. The size for .mdf is still 4GB while the size of the log file 504KB. No matter how many times I try to shrink the database after creating indexes, the size still is 4GB. If I write any update statement against this database, I get the no space on the database error. I don't know what I am doing wrong here. The shrinking database seems to work fine until I add indexes. Any ideas why?
April 2, 2009 at 1:27 pm
Well ... your indexes also need space !
So depending on the size of the columns you used in the index ddl, they will be smaller or larger combined with the number of rows in your table.
Probably you created the primary key using its default , which is clustered !
So the primary key is added as a row pointer to the non clustering indexes (in stead of RIDs). This adds up to the length problem.
Read books online on Indexes !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 2, 2009 at 1:45 pm
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 1:56 pm
bladerunner148 (4/2/2009)
Now, I have to create indexes for 10 fields
That is where is the problem? Why do you have to create so many indexes? Is it that you are just testing?
April 2, 2009 at 3:45 pm
The reason i have to create 10 indexes is all these fields are used in a web application. The user selects the fields and gets a count of records based on the fields he selects.
I let the database grow initially, but when I run any update statements, then I get an error : could not allocate space....
Should I create primary key and indexes before I import the data? Will that solve the problem? I can import the data once the primary keys and indexes have been assigned. Please advise!
April 2, 2009 at 3:51 pm
bladerunner148 (4/2/2009)
The reason i have to create 10 indexes is all these fields are used in a web application. The user selects the fields and gets a count of records based on the fields he selects.
And you've tested that all 10 indexes are useful and are used?
Should I create primary key and indexes before I import the data? Will that solve the problem? I can import the data once the primary keys and indexes have been assigned. Please advise!
It doesn't matter if you create the indexes before or after importing the data. Indexes take up space. With the table and 10 indexes your database is 4 GB in size. Short of dropping indexes or deleting data you cannot change that.
Go into the properties of the database (from management studio) and increase the size of the data file. Then you won't get errors. Make sure that either autogrow is enabled or that you monitor and manually grow the DB as necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 4:08 pm
Hi SSCChampion,
This is what I have right now.
Data File : CustomerInfo.mdf
Filegroup: Primary
Initial Size (MB) : 4021
AutoGrowth : By 1MB, unrestricted growth
The Enable Autogrowth is checked.
I am trying to update a column. I get this error:
Could not allocate space for object 'dbo.CustomerInfo.'pk_custid' in database 'CUSTOMERS' 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.
April 2, 2009 at 4:15 pm
bladerunner148 (4/2/2009)
Hi SSCChampion,
No one here by that name
AutoGrowth : By 1MB, unrestricted growth
That's a stupid autogrow setting for a 4 GB file. Try 100MB. A lot more reasonable
Could not allocate space for object 'dbo.CustomerInfo.'pk_custid' in database 'CUSTOMERS' 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.
Is there space on the disk?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 4:23 pm
bladerunner148 (4/2/2009)
Could not allocate space for object 'dbo.CustomerInfo.'pk_custid' in database 'CUSTOMERS' 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.
And this is due to insufficient space in the disk. Remember if the Primary is full and does not find space it does not add to the sysfiles.
AutoGrowth : By 1MB, unrestricted growth
You should change that, make it a bit large. monitor the growth and set it to some large final value.
The Enable Autogrowth is checked.
And turn this off once you have confirmed on a figure
April 2, 2009 at 4:27 pm
Sorry GilaMonster, I by mistake didn't see your name. I have now changed the autogrowth by 100MB. There is enough size on the disk. I am still having the same problem.
April 2, 2009 at 4:44 pm
bladerunner148 (4/2/2009)
Sorry GilaMonster
You should be sorry again because its Gail Shaw not Gila :-D, it's her nick name.
I have now changed the autogrowth by 100MB. There is enough size on the disk. I am still having the same problem.
And also unrestricted growth option to restricted ?
April 2, 2009 at 5:05 pm
If you are running SQL Server Express, the database size limit is 4 GB.
April 2, 2009 at 5:10 pm
Michael Valentine Jones (4/2/2009)
If you are running SQL Server Express, the database size limit is 4 GB.
Oops good point :-D, forgot to ask that.
April 2, 2009 at 6:55 pm
Yes, I am running sql server express.
April 2, 2009 at 7:02 pm
bladerunner148 (4/2/2009)
Yes, I am running sql server express.
There you go !!!
MAximum limit for a database is 4GB , it does not support beyond that. If you want to store data more than 4GB then you should think of installing an upgrade version of SQL Server according to your requirements.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply