Programming, SQL, SQL Server 2008, Technology, SQL Server 2005, Transact-SQL, tSQL, SQL Tips, Database, SQL Server 2012

TSQL How to Query the Recovery Model and Log Location of all Databases

Seeing where each database and log lives on your server and how large the file is

If you are maintaining the databases on a server, you’ll likely want a comprehensive view of what each database’s recovery model is as well as where the database and log files are stored. An added bonus is seeing how large each file has grown so you can shrink them if necessary. You can see this all in one place using a handy query…

Why do I need this Information?

SQL Server databases are relatively stable and self-maintaining, but every once in a while, you should have a quick look to check how each database is doing and that the log files haven’t grown too large.

In this case, we’ll use a simple T-SQL query in SSMS that will give us an overview of all databases on a server, which obviously saves time over clicking to see the properties in the SSMS UI.

Also, just a note: you’ll need to run this query with an account that has access to the system tables.

Checking the Recovery Model

For starters, you’ll often want to know the database recovery model. For most simple databases that don’t get a lot of activity, having a simple recovery model will be enough. The default ‘Full’ recovery model generates a lot of transaction log files and is overkill if you are ok with knowing that in the event of a disaster, you can recover your database from your last nightly backup. The full recovery model is good if you can’t afford any data loss and need to be able to restore your database down to the point where it stopped working.

Checking File Location and Size

File location and size are important as well. No server has unlimited drive space, so you need to maintain the log file size. If the size gets out of hand, you’ll need to shrink the log in SSMS.

Here is how to shrink a log file that has grown out of hand:

  1. In SSMS right-click the database you want to shrink the log file for
  2. Hover over Tasks
  3. Hover over Shrink
  4. Click Files
  5. In the Pop-up change the File Type dropdown from Data to Log
  6. Leave the default Shrink action as: Release unused space
  7. Click the Ok button

The SQL to Check your Databases

Here is the query you can use to get a quick overview of the tables in your database.

SELECT TOP 100 name, recovery_model_desc
,tbl_db_data.DB_File_Location [DB File Loc], CAST((CAST(tbl_db_data.size AS DECIMAL(18,2)) * 8)/1024.00 AS DECIMAL(18,2)) [DB File Size (MB)] 
,tbl_log_data.DB_File_Location [Log File Loc], CAST((CAST(tbl_log_data.size AS DECIMAL(18,2)) * 8)/1024.00 AS DECIMAL(18,2)) [Log File Size (MB)] 
FROM sys.databases a
SELECT database_id, physical_name AS DB_File_Location,size
FROM sys.master_files
WHERE type_desc='ROWS'
) tbl_db_data ON a.database_id=tbl_db_data.database_id
SELECT database_id, physical_name AS DB_File_Location,size
FROM sys.master_files
WHERE type_desc='LOG'
) tbl_log_data ON a.database_id=tbl_log_data.database_id

From this query you can see that it is calculating size in Megabytes. By default the size is kept in Kilobytes in SQL server and since there are 1024 Kilobytes in a Megabyte, we divide the Size by 1024. Also important to note is that the size column is stored in the sys.master_files table as an Int in 8 Kilobyte pages, so to compensate for this, we need to multiply the size column by 8 before we divide by 1024.

Also from the query above, note that the database and logs are stored in separate rows in the sys.master_files table. To get the info in one row, the query joins on sub-tables and filters by type_desc=’Rows’ to get the database file information and type_desc=’LOG’ to get the log file information.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s