Have you ever wondered why your file system gets fragmented ?
What about allocating some things in 1 MB bits, and other things in 100 MB slices ?
Why not keep your database files uniform in sizes and uniform in extends ?
This will keep your file system without real fragmentation, even though Windows will probably tell you it is fragmented.
I made the script below a few months ago, as I was getting bores using the GUI to do the same.
And if you want the file system to be 100% defraged, run the script, move the files somewhere else, format the drive and copy the files back.
The script looks like this:
SET NOCOUNT ON IF object_id(‘tempdb.dbo.#StmtTab’) is NULL CREATE TABLE #StmtTab(Stmt nvarchar(MAX)) TRUNCATE TABLE #StmtTab — You have to modify the File sizes and growth to your needs DECLARE @DataFileSize int = 1000 DECLARE @DataFileGrowth int = 1000 DECLARE @LogFileSize int = 1000 DECLARE @LogFileGrowth int = 1000 DECLARE @DBName sysname DECLARE @Stmt nvarchar(MAX) DECLARE C_Databases CURSOR FOR SELECT name FROM master.sys.databases DECLARE C_Stmts CURSOR FOR SELECT Stmt FROM #StmtTab
OPEN C_Databases WHILE 1 = 1 BEGIN FETCH NEXT FROM C_Databases INTO @DBName IF @@FETCH_STATUS <> 0 BREAK; SELECT @Stmt = ‘INSERT INTO #StmtTab SELECT CASE WHEN (size / 128) < ‘ + CAST(@DataFileSize AS nvarchar) + ‘ THEN ”ALTER DATABASE [‘ + @DBName + ‘] MODIFY FILE(NAME=””” + name + ”””, SIZE=’+ CAST(@DataFileSize AS nvarchar) + ‘, FILEGROWTH=’ + CAST(@DataFileGrowth AS nvarchar) + ‘)” WHEN (size / 128) % ‘ + CAST(@DataFileGrowth AS nvarchar) + ‘ <> 0 THEN ”ALTER DATABASE ‘ + @DBName + ‘ MODIFY FILE(NAME=””” + name + ”””, SIZE=” + CAST((((size / 128 /’ + CAST(@DataFileGrowth AS nvarchar) + ‘) + 1) * ‘ + CAST(@DataFileGrowth AS nvarchar) + ‘) AS nvarchar) + ”, FILEGROWTH=’ + CAST(@DataFileGrowth AS nvarchar) + ‘)” END from [‘+ @DBName + ‘].sys.database_files WHERE type = 0’ EXEC (@Stmt) SELECT @Stmt = ‘INSERT INTO #StmtTab SELECT CASE WHEN (size / 128) < ‘ + CAST(@LogFileSize AS nvarchar) + ‘ THEN ”ALTER DATABASE [‘ + @DBName + ‘] MODIFY FILE(NAME=””” + name + ”””, SIZE=’+ CAST(@LogFileSize AS nvarchar) + ‘, FILEGROWTH=’ + CAST(@LogFileGrowth AS nvarchar) + ‘)” WHEN (size / 128) % ‘ + CAST(@LogFileGrowth AS nvarchar) + ‘ <> 0 THEN ”ALTER DATABASE ‘ + @DBName + ‘ MODIFY FILE(NAME=””” + name + ”””, SIZE=” + CAST((((size / 128 /’ + CAST(@LogFileGrowth AS nvarchar) + ‘) + 1) * ‘ + CAST(@LogFileGrowth AS nvarchar) + ‘) AS nvarchar) + ”, FILEGROWTH=’ + CAST(@LogFileGrowth AS nvarchar) + ‘)” END from [‘+ @DBName + ‘].sys.database_files WHERE type = 1’ EXEC (@Stmt) END
CLOSE C_Databases DEALLOCATE C_Databases
OPEN C_Stmts WHILE 1 = 1 BEGIN FETCH NEXT FROM C_Stmts INTO @Stmt IF @@FETCH_STATUS <> 0 BREAK; IF @Stmt is not NULL PRINT (‘Executing: ‘ + @Stmt) IF @Stmt is not NULL EXEC (@Stmt) END
CLOSE C_Stmts DEALLOCATE C_Stmts