July 5, 2021 at 11:56 am
Hi All,
We have legacy SQL and OS running on and looks like our OS drive is full and Infra team could not expand it further, there is some unallocated space available but could not add that into C:\. The solution the proposed is move files from E:\ drive to F:\ and make E: as empty, then they can use unallocated space to C:
From DBA PoV: The E drive is having both data and log file and some databases configured custom log shipping as well. It is about 3o files.
My plan is quick and easy one: Stop SQL service and cut / copy files from E to F.
Windows team expand C:\
Paste files from F to E.
Start SQL service.
We have downtime, hope technically this will work as along as they are cleanly shutdown.
Any thoughts?
July 5, 2021 at 2:12 pm
Have to admit that I don't understand why you have to move the files from E to F and then back when you want to enlarge disk C. If I understood correctly what you wrote, you are going to use the same amount of space in E drive before and after enlarging disk C. In any case regardless of my lack of understanding, assuming that you are talking about user databases and not system databases, you don't need to stop the server. You can use the alter database statement to take them offline, then move the files to drive F, do what you need to do, copy the files back to their original location and run alter database statement to take them online again. If you plan to do it for all the user databases, then it won't make a difference if you'll use the way that I wrote or the way that you planed, but if there are databases that you don't plan to move their files, then using my approach will let users still work with them during the whole process.
Adi
July 5, 2021 at 3:00 pm
As a weird question - are C, E, and F the same PHYSICAL disk with different partitions on them? If so, I would change that structure. I came to that conclusion (possibly incorrectly) based on that you are needing to empty E before you can extend C; based on what the infrastructure team told you, I suspect that the drive is a single partitioned out as C, E, and F in that order which is why they can't extend C - E is in the way.
What I would recommend doing is to put in 2 new disks, that are unrelated to your C drive in any way, with any drive letter you feel like, lets say G and H. Next, move everything from E to G and F to H. Now, delete the partitions E and F and extend C to use the whole disk OR have it as 2 partitions - one for the OS and one for the page file. Finally, change the drive letter for G to E and H to F.
The reason I recommend this is having your database on the same physical disk as your C drive gives you very little benefit and a lot of I/O cost. I/O per drive is has a peak performance and if the OS is using some of that, it means your SQL Server cannot.
The assumptions that I am making above are that C, E, and F are all on the same physical disk. IF E and/or F are not on the same physical disk as C, then you may not need to move that data to the new disk. I am also assuming that C and E (at a minimum) are on the same physical disk.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 6, 2021 at 6:42 am
Thank you for your reply. I have not asked why they said like that, yes it could be in same physical disk. I will check with them. It is win-2003 with SQL 2000.
Meanwhile, I hope the following is easy way. I missed the main point - Before it Alter the database change the file location logically
1. Disable restore log shipping job
2. Restore log as norecovery from standby -- only log shipping DBs
3. Alter database change file location logically - E to F
4. Stop SQL service and cut / cut and paste files from E to F
5. Windows team expand C:\
6. Start SQL service - Now files will be in F drive, nothing in E is empty.
7. Alter database change file to original location i.e - E
8. Stop SQL service Cut and paste from F to E
9. Start SQL service
Hope the log shipping will work normal and the standby mode will come again. Otherwise I need to reinitiate log shipping with standby mode. I do not think sp_change_log_shipping_secondary_database will support SQL 2000
We have downtime, hope technically this will work as along as they are cleanly shutdown. Since it has custom log shipping standby mode database as well.
July 7, 2021 at 2:51 am
This was removed by the editor as SPAM
July 8, 2021 at 5:02 am
Anyone can confirm.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply