Create/Restrict Data Files – Automation

In my organization, as part of our strategy we keep Data files in 2 TB sized Mount Point Volumes named like ‘Data’,’Data01′,’Data02′ etc. on E:\ drive. Similary, for Log files also, we have 2 TB sized Mount Point Volumes named like ‘Logs’,’Logs01′,’Logs02′ etc. on E:\ drive. Now, it is quite often that mount point volumes for data or log files get full, and we need to add new mount point volume. Let’s just say that our server already has ‘E:\Data’, ‘E:\Data1’, ‘E:\Data02’ data volumes. Now, ‘E:\Data02’ volume is filled above threshold value of 80%. So, we need to add another mount point volume ‘E:\Data03’ for data files. Also, need to add data files in @newVolume (E:\Data03) for each combination of Db and FileGroup +nt on @oldVolume (E:\Data2). Once files added on @newVolume, we need to restrict growth of all data files on @oldVolume. Then, we have to shrink the size of files in @oldVolume in such a way so that they do not take more than threshold value of space(upto 79%). This kind of activity becomes very irritating when the no of databases gets considerably large, say more than 50.

I have created below script for such kind of activities only.

One can use this script to add new data/log files on @newVolume, restrict old data/log files on @oldVolume, un-restrict growth @oldVolume in case if required, get general data files distribution info, get general log files distribution info, and shrink the files to occupy upto 79% of volume space. This script creates a procedure [dbo].[usp_AnalyzeSpaceCapacity] in [TempDB] database. It does not modify any system tables, so we don’t need to worry about any issue. In order to understand better about procedure, one can execute the procedure with @help = 1 parameter value.

Friends, please feel free to customize and use this script as per your needs. Also, kindly suggest any update that you feel could make the script better and more useful. If you like the article, do Like & Share. Happy Coding 🙂

Leave a Reply