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:
- In SSMS right-click the database you want to shrink the log file for
- Hover over Tasks
- Hover over Shrink
- Click Files
- In the Pop-up change the File Type dropdown from Data to Log
- Leave the default Shrink action as: Release unused space
- 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 INNER JOIN ( 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 INNER JOIN ( 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.