Uniform file sizes

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

 

Advertisements

About me

After 15+ years of experience with Microsoft Dynamics AX (formerly know as Axapta) I have some thoughts to share, and those thoughts should be expressed in this Blog.
I will use the programmers first command “Steal with pride”, but I will when using other peoples material or quotes, always give them credit.

I have been doing installs and Infrastructure advice for Microsoft Dynamics Ax for some years, and I was the first to prove that you in a .NET language, could migrate Microsoft Dynamics data from an Oracle database to a Microsoft SQL Server database, years before Microsoft’s attempt to do so. 

I have dedicated the last 10-15 years to Microsoft Dynamics AX on a very technical level, bound to the Infrastructure, including the servers and the middleware.

The term the programmers first command “Steal with pride”, was first offered to me in 1995 by Torsten Seberg, a valued member of the Ax community.