April 6, 2017 at 7:51 pm
This might be a basic question,
I have an SQL 2012 Data drive of 220 GB and this rapidly running out of disk space (I'm down to 4GB). The underlying issues are that the test and prod DBs are on this (which I am fixing and it wasn't specced right). I need to increase the disk space (thats easy) for the short term fix until my test servers are up and running, but do I need shut down the SQL databases first or just wait for a quiet time? We don't have maintenace windows for that box(yet)
thanks
April 7, 2017 at 12:08 am
Hm,
is this a virtuell server, then you can some space to your harddrive without stopping sql server services. If this is real server, you had to stop sql server services , and shutdown your server.
If you run out of space with your mdf or log file, your database will stop working.
Did you performe regulary log-backups , to get your logfiles smaller?
Kind regards,
Andreas
April 7, 2017 at 1:14 am
Its a virtual server, so that sorted. Backups are performed , just our developers keep throwing test databases on it!
April 7, 2017 at 2:58 am
Hi,
please check if IFI is enabled (https://www.brentozar.com/blitz/instant-file-initialization/9
If you had to growth your databases, it would be faster on the mdf files, if IFI is enabled.
April 7, 2017 at 8:28 am
stephen.godwin - Friday, April 7, 2017 1:14 AMIts a virtual server, so that sorted. Backups are performed , just our developers keep throwing test databases on it!
I'm pretty happy that some developers actually do some testing. 😉 However, some common sense needs to come into play as to when to create a test database and when to drop older databases.
The other thing is that dev, test, staging, and prod should never share disks, virtual or physical. If the developers run the disk out of space even unintentionally, then prod dies. My recommendation would be to fix that problem immediately. Then, figure out why the developers think they need so many databases and correct that problem, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2017 at 12:10 pm
As is common, I inherited this mess. The test server is almost complete, i just need the spare disk to tide me over until i can get the test dbs across
April 8, 2017 at 5:57 pm
stephen.godwin - Thursday, April 6, 2017 7:51 PMThis might be a basic question,I have an SQL 2012 Data drive of 220 GB and this rapidly running out of disk space (I'm down to 4GB). The underlying issues are that the test and prod DBs are on this (which I am fixing and it wasn't specced right). I need to increase the disk space (thats easy) for the short term fix until my test servers are up and running, but do I need shut down the SQL databases first or just wait for a quiet time? We don't have maintenace windows for that box(yet)
thanks
Until you can get your test servers up and running this is what I would do,
Create new drive for test databases.
let the right ppl know that do developing and testing the test\dev databases will be down for a period of time.
detach the database and move (edit note; by copying the files) them to the new drive.
re-attach databases.
Prod should be on it's own drive in this case, which I am sure you know this setup with tes\dev on same prod server is not a good one but you like you I have inherited....
Once you move the test DB's to new sever you can give the space back to the SAN.
MCSE SQL Server 2012\2014\2016
April 8, 2017 at 6:42 pm
stephen.godwin - Friday, April 7, 2017 12:10 PMAs is common, I inherited this mess. The test server is almost complete, i just need the spare disk to tide me over until i can get the test dbs across
My apologies. You actually said that in your original post and I missed it.
To answer the original question...
Before you do anything (and you know this but I have to say it out loud), make sure you have good, viable, restorable backups for the databases involved.
As lkennedy76 suggested, once you have some extra disk space available, you'll need to detach the databases you want to move, copy the MDF and LDF files (and any other files... for example... if you have FULLTEXT search enabled, there will be at least one other file to copy for the database) to the new drive, and reattach them from the new drive. Once that's done, you can simply delete the files from the original drive. I recommend NOT "moving" the files. I recommend copying them so that if something goes horribly wrong, your way back is to simply reattach the original file on the original drive. Drop them from the original drive only when you're sure that the copy/attach actually works correctly.
If the databases are the 2008 version or greater and they're on the Enterprise Edition (or Developer Edition), then a faster alternative might be a compressed backup and then a restore to the new drive.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2017 at 7:08 pm
Jeff Moden - Saturday, April 8, 2017 6:42 PMI recommend NOT "moving" the files. I recommend copying them so that if something goes horribly wrong, your way back is to simply reattach the original file on the original drive.
Yes, thank you Jeff. Copy do not Cut, once they are done copying over to the new drives then delete them.
MCSE SQL Server 2012\2014\2016
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply