1

I have a 2 TB database and the Data Drive has filled up. My network team cannot expand the drive nor provide another drive so that the secondary data file can be pointed towards that. My question is can the secondary drive be pointed towards a network share .

2

You can do it, it doesn't mean you should. There is many things to take into account, like performances and reliability.

Putting the files on the network will probably cause you some performance problems as data needs to be moved across the network for every read, insert, update, delete.

If you have historic data, then maybe you could consider putting those older, less accessed data on the network. You would have less performance issue if you don't access the data often.

Then you might have some data corruption, for example if SQL Server is writing to the database, but the network fails. You have to keep that in mind.

That being said, if you want to do it anyways, you basically have to enable trace flag 1807 at the startup of your SQL Server and then you'll be able to do it.

I recommend you read those two references for a detail of the risks involved :

To transfer part of the database to the new file on the network, you should do the following operations:

  • You add another filegroup to your database and it's location will be on the network.
  • You need to recreate the clustered indexes or primary key clustered of the data that you wish to move to the network on the new filegroup.
4
  • Trace flag allows to attach the data and log files from the network share. Since the database is around 2.5 tb can I just restore the database to the network share – SQL_NoExpert Jan 14 at 16:14
  • Do you want to put the whole database on the network share? or just a part of it? do you have anyu downtime to do the operation? – Danielle Paquette-Harvey Jan 14 at 16:50
  • Whole database on the network share and j have downtime for that – SQL_NoExpert Jan 14 at 18:55
  • I've never restored a whole database on network share, but my guess is that once the traceflag is enabled (and server restarted), you'll be able to restore it. You might want to create a small test database just to test real quick. I don't see any reason why you couldn't. – Danielle Paquette-Harvey Jan 14 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.