madeiradata / databasefilesizemaintenance Goto Github PK

Let's call it @FileTypes which will accept the following values:

• ROWS
• LOG
• ALL

With the exception of ALL (which should also be the default), it should match the type_desc column of the sys.master_files and sys.database_files system tables.

It should be used for specifying the file types which we want to manage using the procedure.

This procedure should not be supported in Azure SQL editions.

We should also test this across all supported SQL server versions - SQL server 2012 and up to 2019. Should probably even test on SQL server 2008 just in case.

The snippet should be compatible with Ola Hallengren's "MaintenanceSolution.sql" script, which uses insertions into a temp table in its implementation.

For example:

  INSERT INTO @Jobs ([Name], CommandTSQL, DatabaseName, OutputFileNamePart01)
  SELECT 'DatabaseIntegrityCheck - SYSTEM_DATABASES',
         'EXECUTE [dbo].[DatabaseIntegrityCheck]' + CHAR(13) + CHAR(10) + '@Databases = ''SYSTEM_DATABASES'',' + CHAR(13) + CHAR(10) + '@LogToTable = ''' + @LogToTable + '''',
         @DatabaseName,
         'DatabaseIntegrityCheck'

Consider which parameters to include as default.

Please try conforming with Microsoft Docs standards.

• Syntax
• Arguments
• Return Code Values
• Remarks
• Permissions
• Examples

For more info:

• T-SQL Syntax Conventions
• Example

Add support to shrinking the databases by chunks, similar to the implementation here:

https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/Shrink_Database_File_in_Specified_Increments.sql

That also includes taking into consideration delays between iterations, error 5240, as well as replica recovery queue.

In order to make this solution similar to other stored procedures in Ola's solution, you need to allow to pass NULL values to relevant parameters, which means the corresponding operation or features should be ignored.

For example, I would like to pass NULL to the @MinFileSizeToShrinkMB parameter, because I don't want files to be shrunk at all. As far as I can tell, NULL values aren't currently handled gracefully, and also the documentation doesn't specify them as optional values.