I made the mistake of creating my SQL server virtual disks (database and log disks, both comprised of RAID 1 arrays) so that they encompassed the entire datastore. I didn't think this would be much of a problem but if you use snapshots in any way, shape or form then you will run into problems because the snapshots must (I think) go on the same datastore. We use vSphere Data Protection and so when it was doing a backup the SQL server floundered due to lack of disk space.
So apparently you can't shrink a virtual disk, or at least I don't know how to.
I had to move all the databases and logs onto a third disk and then remove and reconfigure the log and database disks and then move all the data back onto the newly resized disks. Here is how:
For each user database run this query for each database, index and log:
ALTER DATABASE SUSDB MODIFY FILE ( NAME = SUSDB , FILENAME = 'H:\Data\SUSDB.mdf' );
ALTER DATABASE SUSDB MODIFY FILE ( NAME = SUSDB_log , FILENAME = 'H:\Logs\SUSDB_log.ldf' );
The "name" field maps to the logical name of the file. The "Filename" should map to where you want to move the file.
After running this query, stop the SQL Server service and then manually move the files. Restart the service (and the agent if necessary) and check that the DB starts and then the files are where they should be. This command also helps to verify this:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'SUSDB');
IMPORTANT: It appears that the destination folder must have the following user account present in its ACL with full rights:
SQLServerMSSQLUser$fwsql$MSSQLSERVER
The
Master DB is slightly different. You must edit the start up parameters in the Configuration Manager to the new locations. Config Manager > Services > MSSQLServer Properties > Advanced > Startup Parameters.
Within the parameters "-d" is the Master DB, "-e" is the error log and "-l" is the log.
Once you have changed these values, stop the service and move the files and then restart the service. Make sure the folder that you move it to has that unwieldy SQL account in its ACL.
The Model and the MSDB DBs are moved in the same way as the user databases.
Resources:
http://msdn.microsoft.com/en-us/library/ms345408.aspx
Talks of a "Startup Parameters" tab but there doesn't appear to be one.