-by Ginger Keys
It is not uncommon for database files to grow to the point you need more disk space. But what if it’s not possible or not in the budget to add more space? There are several creative options you have to move or shrink files, or delete unneeded items from the disk. One solution we recently implemented with a client was to move the indexes of a large database to another drive.
Create File/Filegroup
Only non-clustered indexes can be moved, and they must reside in a different filegroup from the primary filegroup. If you don’t already have a separate file and filegroup created, you must do this first. Make sure you have created a folder path on your system to the drive that will be holding your index files.
Get Space Requirement for Indexes
Determine the non-clustered indexes you have in your database and their size by running this script to ensure you have enough space allocated on the target drive to house the index files.
USE AdventureWorks2016
GO
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
8 * SUM(a.used_pages) AS ‘Indexsize(KB)’
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
JOIN sys.tables AS t ON i.object_id = t.object_id
WHERE i.type_desc = ‘NONCLUSTERED’
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
Create and Execute Stored Procedure
The following script will create a stored procedure called MoveIndexToFileGroup that will move an index from one file group to another, and even supports the INCLUDE columns. Run this script against the master database to create the stored procedure: https://gist.github.com/dalenewman/6377911
In order to get a list of tables for your database, run the following script.
Use AdventureWorks2016 –use your database name
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
After the above stored procedure is created and you have gotten a list of schemas/tables/indexes you want to move, execute the following
EXEC MoveIndexToFileGroup
@DBName = ‘AdventureWorks2016’, –your database name
@SchemaName = ‘HumanResources’, –schema name that defaults to dbo
@ObjectNameList = ‘Shift,
Department,
EmployeeDepartmentHistory,
EmployeePayHistory,
Employee,
JobCandidate’, –a table or list of tables
@IndexName = NULL, –an index or NULL for all of them
@FileGroupName = ‘INDEX’; –the target file group
Your indexes will appear in the target destination you created.
Conclusion
There are many useful reasons for moving your non-clustered indexes to a separate drive…in this case ours was simply to create more space for the drive holding the data files. Whatever your reason, the steps above should give you a simplistic way to achieve this.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!