Archive for the ‘Database Specifications’ Category

What are the possible recovery models for the SQL Server databases?

Have you ever had a conversation with your colleagues where they keep on saying “recovery mode” instead of “recovery model” of a database? You might have even second guessed yourself if you were the one who is mistaken on the correct terminology. Make no mistake about it. You are certainly correct. But, there’s no need to argue with them or correct them. You can just keep on consistently refer to it correctly. Others will follow when they know you are correct. Next time, you should be more confident in yourself. It is “recovery model” not “recovery mode.” If you feel otherwise, please do share with us in the comment section. So, what exactly does recovery model mean and what are the possible recovery models that are available in SQL Server and when do you select one over the others? Here’s a brief explanation just to refresh your memory.

Recovery models control the transaction log maintenance. You choose one over the others based on your business rules and the recoverability that the databases must have in case of disaster. There are 3 different types of recovery models:

  • Simple
  • Bulk-logged
  • Full

Simple recovery model is usually selected for the databases that are in development or testing servers where the recoverability of the data is not critical to the business. In case of disaster, you can only recover up to the most recent full or differential backup as you can’t take transaction log backups in the simple recovery model.

Bulk-logged recovery model should be used only when you need to perform large bulk operations such as bulk importing over large amounts of data. It should only be used on a temporary basis and switch back to full when the bulk operations are completed. You can select it to reduce the transaction log space during a bulk transaction as they won’t be logged. In case of disaster, you cannot recover the database to the point of failure. On a side note: “Large amounts of data” is a fully loaded statement and can cause endless debates on what is considered as large. Many of the documentations have stay away from being specific just to avoid debates. But, I will say >= 5 GB should be considered as large. All of us are certainly entitled to our own opinions. Either way you spin it, others can pick it apart and get into debate with you if they wish to. Let them talk and hear them out. As per Stephen Covey, “SEEK FIRST TO UNDERSTAND, THEN TO BE UNDERSTOOD.” Let’s get into habit number 5.

Full recovery model should be the recovery model for all production databases until otherwise specified with good reasons. If full, differential, and transaction log backups are accounted for, it is the only recovery model that can recover to any point in time.

How can you find the recovery model for each of the databases in the SQL Server instance? Here’s the script:

-- Querying the recovery model for each of the databases
SELECT name as DatabaseName
, recovery_model_desc
FROM SYS.DATABASES

REFERENCES:

Recovery Model Overview
http://msdn.microsoft.com/en-us/library/ms189275(v=sql.105).aspx

Backup Under the Bulk-Logged Recovery Model
http://msdn.microsoft.com/en-us/library/ms190692(v=sql.105).aspx

Operations That Can Be Minimally Logged
http://msdn.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

How to query the databases logical data and log file names, physical path, and space utilization information?

You wish to query all the databases in the current SQL Server instance to find their database names, logical files names, physical path, total space reserved in MB, space used in MB, space free in MB, and % of space free. All these valuable information are stored in the sys.database_files system table. The space allocation and utilization information are stored as data pages. Note that 1 page = 8KB. 128 pages = 1 MB. To convert the data pages over to MB for the space used in MB, space free in MB, you must first use the FILEPROPERTY() function and pass in the logical file name and SpaceUsed as parameters, then divided by 128. Instead of a while loop, we can use the undocumented stored proc sp_MSForeachDB to loop through all databases in the instance. The following script is an example of how it can be done:

-- Querying the data and log file names, physical path, and space info

-- create a temp table to store the databases' files specs info for all databases
IF OBJECT_ID('TEMPDB..#DatabaseFilesSpecs') IS NOT NULL
DROP TABLE #DatabaseFilesSpecs

CREATE TABLE #DatabaseFilesSpecs
(
	ID INT identity (1,1)
	,Database_Name VARCHAR(123)
	,Logical_File_Name VARCHAR(123)
	,Physical_Name NVARCHAR(MAX)
	,TotalSpaceReservedMB DECIMAL(10,2)
	,SpaceUsedMB DECIMAL(10,2)
	,SpaceFreeMB DECIMAL(10,2)
	,PercentSpaceFree DECIMAL (10,2)
)

-- load the databases' files specs into the temp table
INSERT INTO #DatabaseFilesSpecs
EXECUTE sp_MSforeachdb' USE ?
SELECT db_name() as Database_Name
,df.name as Logical_File_Name
,df.physical_name
,ROUND(CAST((df.size) AS FLOAT)/128,2) AS TotalSpaceReservedMB
,ROUND(CAST((FILEPROPERTY(df.name,''SpaceUsed'')) AS FLOAT)/128,2) AS SpaceUsedMB
,ROUND((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,''SpaceUsed''))AS FLOAT)/128),2) AS SpaceFreeMB
,ROUND((((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,''SpaceUsed''))AS FLOAT)/128)) / (CAST((df.size) AS FLOAT)/128) * 100),2) AS PercentSpaceFree
FROM sys.database_files df
'
-- Insert the following between line #21 and #22 to omit the list of system databases
-- IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')

-- query the #DatabaseFilesSpecs temp table
SELECT
	ID
	,Database_Name
	,Logical_File_Name
	,Physical_Name
	,TotalSpaceReservedMB
	,SpaceUsedMB
	,SpaceFreeMB
	,PercentSpaceFree
FROM #DatabaseFilesSpecs

REFERENCES:

FILEPROPERTY()
http://msdn.microsoft.com/en-us/library/ms188401(v=sql.105).aspx

sys.database_files
http://msdn.microsoft.com/en-us/library/ms174397(v=sql.105).aspx