Archive

Posts Tagged ‘Recovery Mode’

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) !!!
 


SQL DBA – Database restored successfully, but stuck in (Restoring…) state

November 27, 2015 Leave a comment

 
Today after restoring a database on my DEV machine from a PROD backup, I observed that the database was still showing as Restoring… in Object Explorer, and cannot be used. I again checked in SSMS results pane and it was showing as a successful restore with below message:

100 percent processed.
Processed 21713736 pages for database ‘dbName’, file ‘dbName_Data’ on file 1.
Processed 398 pages for database ‘dbName’, file ‘dbName_log’ on file 1.
RESTORE DATABASE successfully processed 21714134 pages in 506.682 seconds (334.808 MB/sec).

 
I checked online and found that I was using WITH NO RECOVERY option with my RESTORE DATABASE statement, like:

RESTORE DATABASE db_name WITH NO RECOVERY

… and by using above option, you make the Database to be unused, and allow more Roll Forward actions to continue with the next RESTORE DATABASE statements in sequence.

 
As this was not my intention, so I can simply mark by Database to stop accepting more Transaction Logs, by forcing the database out of Restoring state, by issuing following statement:

RESTORE DATABASE dbName WITH RECOVERY

Converting database ‘dbName’ from version 706 to the current version 852.
Database ‘dbName’ running the upgrade step from version 706 to version 770.

Database ‘dbName’ running the upgrade step from version 851 to version 852.
RESTORE DATABASE successfully processed 0 pages in 1.716 seconds (0.000 MB/sec).

… and my database was out from Restoring… state, and I could use it now !!!


Categories: DBA Stuff, SQL Errors Tags: