How to create multiple files of SQL Server database file to manage disk space

Question

5.00/5 (1 vote)

See more:

Hi All,
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.

Posted 4-Mar-14 2:29am

RahulMGunjal

Add a Solution

Comments

CHill60 4-Mar-14 8:36am

I presume you're managing your Transaction log? In any event you could move the transaction log to the other drive - have a look at these links http://www.mssqltips.com/sqlservertip/1774/move-sql-server-transaction-log-files-to-a-different-location-via-tsql-and-ssms/[^]
About transaction log and its truncation in SQL Server[^]

RahulMGunjal 4-Mar-14 8:43am

No, its not about transaction log. My Transaction log is of 630 MB only whereas database file is 498 GB. I don't have sufficient disk space to add new transactions. I want to split my mdf files and start using disk space available on another drive as I said in the question.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-03-04T02:39:00

Solution 1

Before you start doing it, please try to shrink database[^] and shrink transation log[^].
After that, check the space occupied by SQL database file.

[EDIT]
Have a look here:
Using Files and Filegroups to Manage Database Growth[^]
How to: Start the Create Partition Wizard (SQL Server Management Studio)[^]

By The Way: Please read this: http://stackoverflow.com/questions/5613898/storing-images-in-sql-server[^]. There is a reference to MS document called: "To Blob Or Not To Blob". I guarantee you: it's very interesting!
[/EDIT]

Posted 4-Mar-14 2:39am

Maciej Los

Updated 4-Mar-14 11:27am

v4

Comments

RahulMGunjal 4-Mar-14 8:45am

Shrinking is not helping me. Basically over the year my database will grow by another 150 GB.

Maciej Los 4-Mar-14 8:51am

Sounds like bad database design ;(
Could you, please, provide more details about your issue? What have you done till now?
See updated answer.

RahulMGunjal 4-Mar-14 8:59am

Database size is growing rapidly because I am uploading scanned image files in the database. There are already more than 6 million scanned images uploaded on the database. This is causing tremendous growth in database size.

RahulMGunjal 4-Mar-14 8:58am

Database size is growing rapidly because I am uploading scanned image files in the database. There are already more than 6 million scanned images uploaded on the database. This is causing tremendous growth in database size.