0

I have around 500GB disk space on D drive. I created my database file on D Drive. After few years of transactions, the disk space is almost full (around 25 MB disk space remaining).

I have around 300GB disk space remaining on E Drive. Can I use disk space available on E Drive for the existing database which will enable me to grow my database up to 800GB (500 GB on D Drive & 300 GB on E Drive).

Any help would be really appreciable.

Thanks.

Rahul
  • 21
  • 4
  • Which edition of SQL Server are you running? Do you have multiple database or just single databases with multiple tables? –  Mar 04 '14 at 13:31
  • SQL Server 2005. Currently it is single database containing multiple table and with single mdf file. – Rahul Mar 04 '14 at 13:33

1 Answers1

0

First choice would be to purchase bigger driver and to move entire file to the new drive.

Create new file group and add files on the E: Drive (not recommended, to split your table data across multiple drives.) Creating file groups is very simple process, you can do it with SSMS (not sure if it was the same in 2005) you would just right click on Database, go to properties and go to File Groups. Or you can use T-SQL ALTER DATABASE to add files. Full syntax at http://technet.microsoft.com/en-us/library/ms174269(v=sql.90).aspx

Create new file group and add files on E: Drive, once complete identify large tables and move to new file group. This will keep entire tables on same drive to keep good read/write performance. This question How do i move a table to a particular FileGroup in SQL Server 2008 already has syntax for moving tables to another file group.

Note: SQL-Server 2005 has reached end of life.

We would like to remind all customers that Mainstream Support for SQL Server 2005 Service Pack 3 and SQL Server 2005 Service Pack 4 will end on April 12, 2011, and Service Pack Support for SQL Server 2008 Service Pack 1 will end on October 11, 2011. http://blogs.msdn.com/b/sqlreleaseservices/archive/2011/01/27/end-of-mainstream-support-for-sql-server-2005-and-end-of-service-pack-support-for-sql-server-2008-sp1.aspx

Several other tips:

  • Check size of your tempdb and transaction logs, you might be able to reduce some space there.
  • Archive data you don't need.
  • Keep data and log files on separate drives.
  • Create more than multiple data files for database to reduce allocation contention more about it here(http://support.microsoft.com/kb/2154845).
Community
  • 1
  • 1