Last week, I wrote a post on how to relocate Oracle data files from one drive to another. Today, let us see how simple it is to do the same in SQL Server. I am running SQL 2014 but it should not be too different
Say “No” to huge single .mdf file databases!
Space got tight on a drive and I knew that there was space on another drive. I had already set this particular database with multiple secondary file groups/files (.ndf files) instead of a huge and single .mdf file (which would have made the whole thing a lot harder).
The procedure to relocate data files is extremely simple
- Notify users that you need to do maintenance on the database
- Check the space situation on your host
- Identify the files that can be moved using [YourDB].sys.database_files or Get-DbaDatabaseFile from dbatools
- ALTER DATABASE MODIFY to relocate file pointers in the system database
- ALTER DATABASE [YourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
- Physically move the files to a new drive where there is adequate space
- ALTER DATABASE [YourDB] SET ONLINE;
Let us go over each step in a detailed fashion
1. Notify users that you need to do maintenance on the database
This step need no explanation. However, if you do not have a meta-data database with owner information, the best place to start is to notify the users that are currently connected. You can simply use sp_who2 or sp_whoisactive.
You could use PowerShell to notify users who are connected. This takes the hard-work out of identifying whose login is what and finding their email ID’s.
2. Check the space situation on your host
I use the Get-DBADiskSpace PowerShell function that is part of DBATools. It gives me a nice output of all the drives/volumes with free-space information (something like below)
PS C:\Windows\system32> Get-DbaDiskSpace -ComputerName MYSQLHOST | ft -AutoSize Server Name Label SizeInGB FreeInGB PercentFree BlockSize ------ ---- ----- -------- -------- ----------- --------- MYSQLHOST D:\ APP 500 160.79 32.16 4096 MYSQLHOST B:\ DATA 500 181.8 36.36 4096 MYSQLHOST E:\ DATA 50 49.89 99.79 4096 MYSQLHOST L:\ LOG 100 99.88 99.89 4096 MYSQLHOST T:\ TEMP 10 9.94 99.44 4096
3. Identify the files that can be moved using [YourDB].sys.database_files or Get-DbaDatabaseFile from dbatools
You could simply query YourDB.sys.database_files with TSQL to get the file list with the logical file name for each file and the physical location with size.
Alternatively, similar to the PowerShell call in the previous step, you could get the database files of a database and their size information using the dbatools function to understand which files can be moved around
PS C:\Windows\system32> Get-DbaDatabaseFile -SQLInstance MYSQLHOST -Databases YourDBToMaint | ft -AutoSize
4. ALTER DATABASE MODIFY to relocate file pointers in the system database
The database can still be ONLINE and ATTACHED when you do this because only the internal system tables will be updated. All we are doing is making a change to point to the new location of the files we intend to move.
ALTER DATABASE MY_DB MODIFY FILE (name='MY_DB_FILE_03' ,filename='E:\DATA\MSSQL12.MYINST\MSSQL\DATA\MY_DB_FILE_03.ndf');
The location referenced in the above command is the new location of the file. Based on the logical name MY_DB_FILE_03, it knows what you are referencing. You could re-point as many files as you want at this point. You just have to remember to physically move them later.
5. ALTER DATABASE [YourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
At this point, you need to OFFLINE your DB. Next-up, we physically move the files that you re-pointed in the previous step.
ALTER DATABASE [YourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
6. Physically move the files to a new drive where there is adequate space
The repointed files should now be moved to their new location. Make sure that files in their new location have full control permission for the SQL Server service account.
You could use the below SQL to get the service account information
SELECT DSS.servicename, DSS.startup_type_desc, DSS.status_desc, DSS.last_startup_time, DSS.service_account, DSS.is_clustered, DSS.cluster_nodename, DSS.filename, DSS.startup_type, DSS.status, DSS.process_id FROM sys.dm_server_services AS DSS;
If the service account does not have adequate permissions to the files, then you may get an error when you try to bring the database online in the next step.
Msg 5120, Level 16, State 101, Line 17
Unable to open the physical file “E:\DATA\MSSQL12.MYINST\MSSQL\DATA\MY_DB_FILE_03.ndf”. Operating system error 5: “5(Access is denied.)”.
7. ALTER DATABASE [YourDB] SET ONLINE;
At this point, all you have to do is bring your DB Online and everything should be fine. If you run into errors, please make sure that you set the permissions appropriately as noted in the previous step.
That’s it! You have now moved files to free-up space on a certain drive that was full.