How to get SQL 2000 to use all of the drive space available?

How to get SQL 2000 to use all of the drive space available?

My database is saying that it has 0.00mb available; but the drive that the database resides on has about 28.5 GB free.  How can I utilize all of the drive space?

Thanks - RM
rcjaxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MangusDatabase AdministratorCommented:
You need to expand the database.  You can either set it up to do it automatically, or you can expand it manually.
0
dportasCommented:
Use sp_spaceused or the Taskpad view in Enterprise Manager to see what space is used and unallocated.

Then either add new data/log files or extend the existing files:
http://msdn2.microsoft.com/en-us/library/aa933083(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/aa933089(SQL.80).aspx
0
bradleys40Commented:
alter database your db name
modify file (name = filename, size = 10 gb)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what edition of sql server are you using?
depending on the edition, you are limited to 2GB/database...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
alternatively, check out the "autogrow" property of the database, is it enabled or disabled?
if enabled -> see my above comment
if disabled -> see comment above from the other experts
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rcjaxAuthor Commented:
I am using sql 2000 (80).  I tried growing the database from the properties; it seems to be growing there but when I look at the space available it still reads 0.00mb.  I also grew the transactions logs.  Oh, and I did a shink database afterwards.

Still not working.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> Oh, and I did a shrink database afterwards.
which shrinks the database to have 0mb free.

redo the steps, but skip the "shrink database"
0
rcjaxAuthor Commented:
I've done it again without the shrink; still reading 0.00 mb... any other suggestions?
0
rcjaxAuthor Commented:
Here is what I have:

Reserved: 150312912 KM
Data: 9474088 KB
Index_size: 4106728 KB
Unused: 136732096 KB

After running sp_spaceused:
Database_Size: 17553.19 MB
Unallocated: -129739.77 MB
The total space on the drive is 28.0 GB. How can I get SQL to use what's left?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>My database is saying that it has 0.00mb

the sp_spaceused output clearly indicates that there IS free space.
where exactly do you see the 0.00mb (screenshot?)
0
rcjaxAuthor Commented:
When I right click the properties of my database, under the General tab it give the size of the db and space avalable it says 0.00 mb.
0
rcjaxAuthor Commented:
It also I am getting this information from my database taskpad.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
those 2 "sources" of information are actually not 100% reliable, there are some issues with them.
what service pack is your sql server?
support.microsoft.com/kb/321185
0
rcjaxAuthor Commented:
Service PAck 4
0
rcjaxAuthor Commented:
Thank You.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.