Thanks for keep reading and motivating me to share as much as solution.
Today I will be discussing solution that we have setup infrastructure as per available device in infrastructure but suddenly we start seeing that database size is rapid increase as normal. What we do if such scenarios and how to bring in to solutions.
By seeing reported error, below are the action taken to bring in to the solutions.
We have validated current environment and run sql query to validate table disk space utilization and found that one of the table (TableChangeNotifications) was using huge size of disk space in available disk space. In client environment sql has 200 gb disk space but particulate table was using 160GB. Find the below snap shoot for reference.
After the finding of the table details, we have tried to find which key is using more space in this table.
Found that one of the able was holding more than 6.5 Milion of the table rows. By seeing details package id we have deleted from the console but due to replication issue table space not clear from the database. Find the screen shoot for reference.
Based on finding we have decided to delete data if you are aware SQL then execute query as below or Consult SQL DB team and do clean up .
Ran below statement to delete the entries for package: CS10000E
delete from TableChangeNotifications where Key1 = ‘CS10000E’
-
But this action was taking long time to complete. Also post re-initialization none of the incoming messages were processed by the PS1 server, hence I decided to truncate the table.
-
Ran truncate to delete all the entries after stopping SMS services. truncate able TableChangeNotifications.
