imajaydwivedi/space-capacity-automation

This automation has been designed to eliminate manual efforts on Space Capacity ESC tickets where DBA has to add new data or log files on new volume, and restrict data or log files on old volume. Apart from this, this procedure can be used for variety of tasks related to capacity management.

space-capacity-automation's Issues

Error while executing the script to modify tempdb files

@imajaydwivedi

In the first place this script is really awesome and safe. Couldn't imagine the work behind developing this. almost of the features are working as expected and posting below to let you know the issue I have faced. Also attached the screen shot of Verbose

After executing the function
EXEC tempdb..[usp_AnalyzeSpaceCapacity] @expandTempDBSize = 1 ,@output4IdealScenario = 1

I get error "Arithmetic overflow error for data type tinyint, value = -3.". unable to solve this in the code. May you please help here to come out if it.

@getVolumeSpaceConsumers - Add column [Depth]

Kindly make below modifications in resultset of @getVolumeSpaceConsumers:

• Get size of files & folder in format "<size_in_kb>(size in gb/mb/kb)"
  This would help in sorting of the data in excel
• Add a new column [Depth] to show depth of file or folder
• Change column name [size] to [Size_DirectItemsOnly]
  This would contain size of all direct items (files only)
• Add new column [Size_IncludingNestedChilds]
  This would contain size of all child items direct or indirectly nested for path
• Issue# Some folder items are not showing size

@optimizeLogFiles - Regrow Log files by Reducing High VLF Count

• Find VLF Counts
• Compare VLF Counts with Best Practices Guidelines
• Shrink the Log file to 0 MB
• Regrow File Size upto Current Size

@getVolumeSpaceConsumers - Show even Hidden Files & Folders

In output of @getVolumeSpaceConsumers, kindly include hidden Files & Folders as well.

Performance Optimization

• Analyze Query Plan for Each Feature
• Check Indexing Strategy
• Checks efficiency of Loops, Cursor, CTEs
• Check Any re-compilation factor
• Check usability of Statistics
• Evaluate performance with #tempTable Vs @tableVariable
• Reducing Code to remove any possibility of Blockings
• Check Dynamic SQL efficient usage with sp_executesql
• Check any kind of Execution Plan Warnings using Extended Events
• Introduce Standardization of naming convention, data types
• Check for Deprecated Features used in Automation(Take SQL2012 as Base Version)

Incorrect File Path for tempdb files with @expandTempDBSize

I am executing below code to get additional tempdb files, but code generated is not showing accurate file path.

EXEC tempdb..[usp_AnalyzeSpaceCapacity] @expandTempDBSize = 1, @tempDbMaxSizeThresholdInGB = 16, @output4IdealScenario = 1

Bug - @getVolumeSpaceConsumers - Invalid length parameter passed to the LEFT or SUBSTRING function.

Procedure is throwing below mentioned error when executed with @getVolumeSpaceConsumers = 1 for @OldVolume = 'C:'

@getVolumeSpaceConsumers - Add parameter to sort output by Size

Add new parameter like @sortBySize BIT parameter to sort the output of @getVolumeSpaceConsumers according to size of Files and folders.

@handleXPCmdShell - Not Working

This functionality is not working. Do the needful using Agent Job

• Verify if xp_cmdshell is not enabled
• Create a SQL Agent Job with TSQL code to enable xp_cmdshell configuration settings
• Execute Job Created to enable cmdshell "Before" execution of main code
• Execute Job Created to diable cmdshell "After" execution of main code
• Drop SQL Job Created after execution of Parameters

@getMail - Get output on email id @emailIDs

• Create CSS Stylesheet for All outputs
  (Table o/p, TSQL code, Warning Messages, Error Messages)
• @getinfo
• @volumeInfo
• @getVolumeSpaceConsumers
• @getLogInfo

@ShrinkLogFiles - Shrink Log Files on @oldVolume

• Step 01 - Check File Free Space
• Step 02 - Check [log_reuse_wait_desc]
• Step 03 - Check size of free VLFs
• Step 04 - Generate Shrink Query

Error - Not Supported - Volume configuration is not per standard. Kindly perform the activity manually.

When executing proc with default parameter of @getinfo, then receive below error:-

Volume configuration is not per standard. Kindly perform the activity manually.

Error - Not Supported - Volume configuration is not per standard. Kindly perform the activity manually.

When executing proc with default parameter of @getinfo, then receive below error:-

Volume configuration is not per standard. Kindly perform the activity manually.