SQL Server 2000 running out of space.

[jimfkennedy]

Hi, we have a SqlServer instance in production containing around 10
databases.

It has just been realised that all the db's are held on the small c:
partition with only a gig or so of space left.

On the server there is another partition and another hard disk both
will ample space (few hundred gigs).

What would be the best way of getting the data onto the other
partitions with minimal impact on the applications. Can we move the
primary data files for each db? Should we just create secondary data
files on the big partitions for all the db's? Is there a method of
moving all the data at once?

Any ideas on how we should approach this?

(ps we dont have control of the sqlserver its outsourced, so simpler
the better).

Thanks,

Jim

[rcamarda]

IMO the quickest and easist way is to detach the database, move the
data via windows, and attach the database again.
This can be done via enterprise manager (in SQL Server 2000) or via QA.
See sp_detach_db command.
If you have a maintenace window where you can shutdown the application
(necessary since the database will be unavailable while detached) this
is the quickest and simplest. Once the database is attached, the
application wont know nor care that it was on the C: drive and now
somewhere else
HTH
Rob

[Erland Sommarskog]

(ji*********@gmail.com) writes:

Hi, we have a SqlServer instance in production containing around 10
databases.

It has just been realised that all the db's are held on the small c:
partition with only a gig or so of space left.

On the server there is another partition and another hard disk both
will ample space (few hundred gigs).

What would be the best way of getting the data onto the other
partitions with minimal impact on the applications. Can we move the
primary data files for each db? Should we just create secondary data
files on the big partitions for all the db's? Is there a method of
moving all the data at once?

It appears that your databases are small in size. That talks against
creating a secondary datafile.

I would suggest that you use either detach/attach or backup/restore the
move the database. My own preference is for the latter.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx