DBCC SHRINKFILE | SQL with Manoj

SQL DBA – Change RECOVERY mode & SHRINK all databases at once in SQL Server

July 17, 2020 2 comments

There are times when you are left with no or very less space in you SQL Server VM disks, and the main cause of this is not only the lot amount of data coming in but the heavy processing your database Server is doing, and thus filling up the entire log space.

SQL Server provides you some effective commands to shrink the database files which got inflated due to heavy processing and to make room for new data.

ALTER DATABASE <db_name> SET RECOVERY SIMPLE;

DBCC SHRINKFILE (N'<log_file_name>' , 100);

But this works with one database at a time, so what if you have lots of databases? It will be tiring and time consuming to visit each DB, get the database file names and apply the shrink command. Its even slow to do the same via SSMS GUI.

Note: Do not change the recovery mode in a PROD environment unless it is really required.

With the below T-SQL statements you can generate scripts for all the databases and can run to change all database settings at once:

-- Generate SQL Script to change Recovery mode to 'SIMPLE' for all DBs:
SELECT 
	'ALTER DATABASE [' + name + '] 
	 SET RECOVERY SIMPLE;' as SimpleRecovery4AllDBs
FROM sys.databases  
WHERE recovery_model_desc <> 'SIMPLE'

-- Generate SQL Script to Shrink log files of all DBs:
SELECT 
	'USE ' + DB_Name(database_id) + '; 
	 DBCC SHRINKFILE (N''' + name + ''' , 100);' as ShrinkAllDBs
FROM sys.master_files 
WHERE database_id > 4 and [type] = 1

Please let me know if you have any other way to do the same (and in more effective way) !!!

Categories: DBA Stuff, SQL DB Engine Tags: DBCC SHRINKDATABASE, DBCC SHRINKFILE, Recovery Mode, SHRINK DATABASE

SQL DBA – SHRINK DATABASE & SHRINK FILE – SQL Server

July 2, 2011 3 comments

While working with huge databases with large tables there are times when you get your database and log files expand ridiculously and eating up the entire disks.
This happens when you are either on an:
– OLTP environment and doing lots of DMLs (INSERTS, UPDATES or DELETES) or
– OLAP environment when you are running huge SQL queries that goes on expanding your tempdb and finally eats up your entire drive space.

There are lot of ways to better configure your disks and databases there to avoid such situations. But these kind of situations come and you have to approach the DBA or become a DBA yourself.

Check the LOG Size and Space used, here:

DBCC SQLPERF (LOGSPACE)

Check if your database has adequate space available to free up:

SELECT 
	name, 
	size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Before shrinking the log file, it better to backup your transaction logs, as shown below:

BACKUP LOG <database_name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(database_name_log, 2)

OR, you can simply change the Database recovery mode to Simple, and run the above mentioned DBCC statement.

tempdb can be freed up by simply restarting SQL Server. When SQL Server shuts down it deletes the tempdb and on restart it automatically creates tempdb database. Thus you get fresh tempdb with minimal space. This is not advisable on production servers and should be handled by experts.