Sql-server – Auto-Shrinking All Databases Files To Their Minimum Size In Sql Server

disk-spacerestoresql serversql-server-2016

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?

Best Answer

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

Related Solutions

Sql-server – Shrinking Reluctant Log Files

This is one of those exceptions where it's probably okay to execute a DBCC SHRINKFILE. The log file is most likely this large not because of what is going on right now but because there was a time when hourly transaction logs were not being run (or you had an exceptionally large transaction that ran between or across log backups). I'm not sure where the misconception comes from, but backing up the log does not shrink the file for you, it only frees up space within the file.

You can perform a one-time operation like this without interfering with the log chain (or having to "hope" that "nothing happens in the meantime"), preferably right after a log backup has completed successfully:

DBCC SHRINKFILE(yourdb_log, 4000);

This should shrink the file to ~4 GB, which hopefully is plenty of room to accommodate your hourly transactions. You may want to pick a different size, I'm not sure...

If this command doesn't shrink the file as much as you expect, you can check why the log modification may be blocked using:

SELECT log_reuse_wait_desc
  FROM sys.databases
  WHERE name = N'yourdb';

Sql-server – How to shrink the physical Transaction Log file when it’s the principal in a mirror

From what I can tell, I can't.

I believe much of the log file is waiting to be mirrored to the mirror server, but the mirror is down and can't be resumed because the mirrored transaction log has also grown to take up all the space on the disk.

This theory is further backed up by the fact once I remove the mirroring, the DBCC SHRINKFILE command correctly shrinks the physical log file, and the log_reuse_wait_desc is back to waiting on LOG_BACKUP

I can't shrink the log file on the mirrored server because it's acting as a mirror and can't be opened, so I think the mirror is unrecoverable.

So I am going to delete the mirror entirely, and set everything up again (very slow process with a 300GB database). And this time, I'll make sure the transaction log size is fairly small before starting up the mirror, and that the transaction log backups are running once the mirror is back up and running.

I'm also going to set a limit on how big the transaction log can grow on the production server, so the mirror doesn't ever try to grow its transaction log beyond the size available on the disk.

Best Answer

Related Solutions

Sql-server – Shrinking Reluctant Log Files

Sql-server – How to shrink the physical Transaction Log file when it’s the principal in a mirror

Related Question