I restore my all databases in production to my development environment monthly. Development server has 4 TB disk space and there are 4 different instances over it which containt databases on the production server. In Development, i can truncate and shrink some big-sized tables to expand free disk space. The purpose of doing this process is the complaint of software developers about the development server slowness. I talked to system administrator team and they informed me that the slowness was because of the insufficient disk space area. So, i make this work. My question is about how i can shrink all database files to their minimum size after truncating related tables. So, should i make it with powershell, and how?
My question is about how i can shrink all database files to their minimum size after truncating related tables
To answer your question directly, this is done using
DBCC SHRINKFILE command -
DBCC SHRINKFILE (YourDatabaseLogicalFileName,<desired size in MB>)
Here you can read more information on how to use this commnand https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15
But, as other comments already mentioned, the reason of poor performance lies somewhere else and you need to start a methodical process of collecting and analyzing performance information (Performance Counters, Waits, Top heaviest queries, etc.)
Among other reasons, possible that poor performance in your Dev environment could be simply because system specification of Dev environment (CPU, RAM, Storage, Network etc.) is much weaker/worse than your Production; or Developers could be running some heavy and not tuned queries in Dev environment, that they do not run on Production