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?

  • 2
    Insufficient disk should not be a significant factor for performance. You either have room to restore or you dont. Fragmented indexes, poor statistics and/or insufficient RAM are more likely the culprits. – Sir Swears-a-lot Dec 25 '19 at 8:16
  • @SirSwears-a-lot But, i restore databases from my production and i rebuild my all indexes before taken every backup. Maybe RAM can cause that issue but system team strongly offers me to increase free disk space. I don't know :/ – Furkan Yılmaz Dec 25 '19 at 8:54
  • 1
    Rather than assuming thats the problem, you need to start a methodical process of elimination and performance tuning. If your developers complain of bad performance then start there. Find what performs badly then find out why. If you really want to shrink dbs then you could script it. But i think uts a red-herring and may do more harm than good. – Sir Swears-a-lot Dec 25 '19 at 9:00
  • Is there any script which does this work? @SirSwears-a-lot – Furkan Yılmaz Dec 25 '19 at 10:18
  • 1

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.