March 1, 2002 at 9:18 am
I have a problem, disk space on a server we have is 80G with SQL server on it original size of that db was 9GB. We bought a company and imported a table into that db on that server as CFO's wish...the table is 58 GB. Everyone's querying on that table and need to build a clustered index on that table.
Now my problem is, we have spent all our budget for our fiscal year and have no money to put more storage on that server till April 1, 2002. I don't have 1.5 times space needed for that table to build (or rebuild) the clustered index. Is there any way around this problem????
Raghu
Raghu
March 1, 2002 at 9:43 am
Tell the CFO that if he wants to add a table which makes the database 6 times the size then someone will have to pay for it.
What are the people querying?
Can you build a smaller agregate table to fulfill the query?
Have you now a 67G database and 80G space for it?
I would be seriously concerned about running a system like this - I hope this database is read only.
Cursors never.
DTS - only when needed and never to control.
March 1, 2002 at 11:38 am
What about non-clustered index? It will also help. What type of queries? Can you post a sample.
Steve Jones
March 1, 2002 at 4:39 pm
Try this (you will need to do this when the system can have some downtime).
First script all the database objects and permissions as you will need.
Now rename the old database and create a new database with the original name.
Open Query Anaylzer and run your script on the new database with the old name to setup exactly the same.
Open the table you need to have a clustered index (and any other indexes you are planning) on and save inside the new database.
Now do and export from the old database (the original with all the data) into the new database using a query to move say 1 million records at a time and between each move delete those records for the old database.
What in effect you are doing is expanding and shrinking in increments without the 1.5 size need especially since the new database indexes will insert the data in proper order.
Any questions about what I have stated let me know. But one note, test it elsewhere before you start and make a full backup before you begin (all for safety sake).
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 1, 2002 at 6:02 pm
Might also look at partitioning the data, putting some elsewhere. If you've got a large qty of data not likely to be used, split it out and load it on a server somewhere else, use a view to put it together or just do without it for two months. You should also try to remove as many indexes as you can - each uses space. Definitely watch the disk space, rebuild operatons can really build the log file sometimes, easy to run out space. I know its a hack but I like to keep an old 1g bak file on the box - if things get desperate that will buy me a little time.
Andy
March 11, 2002 at 2:34 am
Raghu,
I've once had a situation where I added one table, and something went wrong, and the row size was something like 8 kb. All of a sudden I had a server with very little disk space left. The table only had 2 integer columns, so I must have hit a bug in SQL Server.
Has the same thing happend to you, I wonder?
Are you sure that the table you've added has a row size and a record count that will use up 49 GB?
Kind regards
Henrik Staun Poulsen
Stovi Software
Denmark
March 14, 2002 at 7:58 am
I personally think that if a new disk is too expensive on a project where a table and not a database is 58gig in size, then you are asking the wrong questions. I know it is difficuilt at times when management or accounts don't understand the technicalities behind the expense but you will probably find that the budgets were drawn up before this table came along so, find out if there is a contingency plan built into the budget for instances like this. If not, then ask the question "why not?!?"
March 14, 2002 at 10:41 am
I agree. Disks are relatively cheap. you;ve probably wasted some serieous $$ in time trying to get around this.
Steve Jones
March 14, 2002 at 10:54 am
I do agree there, it is far better to ask for the space of another drive than to do all this, primamrily since you cannot make modifications without following some off the wall method. If they won't buy then explain that what they want done is not possible (and I know how it goes but $1000- is not a lot to pay for the needed equipment).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply