Running Low On Disk Space! Need solutions for newbie

[sab4you]

I am maintaining a customer SQL computer.

It has one IDE hard drive with OS and MS SQL 2000.
One IDE hard disk contains the swapfile and SQL puts backups here nightly for me to ZIP up and transfer elsewhere.

Then we have a 30GB SCSI hard drive which contains the SQL files.

This drive is running low on space and I was curious on best options to increase space.

Obviously I need to add another SCSI hard drive and due to costs I can probably buy another single drive - but from here do I:

1. Format both hard drives and use windows 2000 to make into a single drive and then restore databases on top.

2. Add another SCSI drive and add another file group pointing to the 2nd new hard drive? (If so I have questions about this)

The databases store stuff related to date. New stuff comes in every 15 minutes and customers usually only look at most recent daily, weekly and monthly data. Stuff over a month old is rarely looked at but still needs to be available.

The databases are looked at via software, which I do not have control over

 

[SQLBill]

Are you doing backups of the database and the log files?

How big is your database? How big is the log file for the database?

If you aren't doing backups of the log and your database isn't set to SIMPLE recovery mode; your size issue might be due to an extremely large transaction log.

-SQLBill

 

[sab4you]

There is about 20 SQL databases on the hard drive. The databases are set to simple recovery.

The total of all my transaction logs are 127 MB, while my databases themselves are about 20 GB.

My HD is 30 GB total which is why I need assistance with best methods of adding new hard drives in an easy fashion.

 

[Corran007]

For ease, i would get the new drive, backup all the databases and reformat the drives into one drive. It makes space managerment much simplier in the long run. Ive had to deal with multiple drives and it gets confuseing and you lose space in the long run by having to jugle databases between drives as space becomes an issue. We just said frack it and rebuilt the server from scratch one weekend.

 

[JayKusch]

First ... see if you can shrink some of your DBs.

Go to Enterprise Manager (EM) >> Open the Server(s) and drill down to the list of your DBs. Right-mouse click each database and choose "All Tasks" >> "Shrink Database. The initial screen will show how much space can be recaptured.

Once that is done, we are now ready to deal w/ adding new disk(s) to the server. Go ahead and add your new drive and start your server and SQL Server back up.

We can now do a couple of things. First lets move all or some of your current DBs and TLogs.

Go to Enterprise Manager (EM) >> Open the Server(s) and drill down to the list of your DBs. Right-mouse click each database and choose "All Tasks" >> "Detach Database.

Once this is accomplished, go to explorer and find the MDF and LDF of the DB you just detached. As an example, if you detached MyDatabase, you will find a MyDatabase.mdf and a MyDatabase.ldf. In some cases you will also find .ndf files and if so ... let us know. We will assume you have just mdfs and ldfs. Move the mdf and ldf to their new location on your new disk.

Once there, Go to Enterprise Manager (EM) >> Open the Server(s) and drill down to the list of your DBs. Right-mouse click the "Databases" folder and choose "All Tasks" >> "Attach Database".

The screen will ask where your mdf and ldf is, point it to the new location and you should be able to reattach it.

Do this for all the DBs you want to move

==== FOR FUTURE DBs You Create

you may want to point their creation directory to you new disk also. That way, any new DB created will go on the new disk, or another location of your choice.

SO ... go to EM

Right mouse click on the server name, which may just say local or show the name of the physical server.

Choose Properties >> Database Settings

There you will find 2 dialoge areas: "Default Data Directory" and "Default Log Directory". You can put the path in where you want all new DB data files to be put as well as where the tlogs are put.

Best practice is to put you data files on a seperate disk from your tlog files. So you may want to keep all tlogs on the disk you are having issues w/ now and all data files on your new disk.

Hope this helps somewhat.

Thanks

J. Kusch

 

[sab4you]

Thanks guys!

JayKusch, this is what I plan

I can detach all the databases and then simply copy them to my 2nd IDE drive.

Then I can add the new SCSI hard disk, and format both my old SCSI hard disk and new one. Then configure them as a striped volume.

Then I can copy back the databases from my 2nd IDE drive onto the striped new SCSI drive(s) and then re-attach.

I have never done this before so let me know if there is any problems I may run into with what I plan (above)

Thanks again guys for all your help - its very much appreciated!!!

 

[JayKusch]

Sounds ... well ... SOUND! LOL

Thanks

J. Kusch

 

[SQLSister]

It helps improve performance if indexes and transaction logs are on separate drives. Apparently transactions logs aren;t a big thing for you if all of them are set to simple, but indexes probably are. You might consider moving your indexes to a separter drive. It is also my understanding that it is better to have more small drives than one big one especially if you are running multiple databases with multiple users. Right now each request has to wait until the previous one is complete becasue you only have one drive controller. Different drives with differnt controllers can be processing simultaneously. AS long as you are buying more anyway, you should consider this.