THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

SYS2 Scripts Updated – Scripts to monitor database backup, database space usage and memory grants now available

I’ve just released three new scripts of my “sys2” script collection that can be found on CodePlex:

Project Page:

Source Code Download:

The three new scripts are the following

  • sys2.database_backup_info.sql
  • sys2.query_memory_grants.sql
  • sys2.stp_get_databases_space_used_info.sql

Here’s some more details:


This script has been made to quickly check if and when backup was done. It will report the last full, differential and log backup date and time for each database. Along with these information you’ll also get some additional metadata that shows if a database is a read-only database and its recovery model:


By default it will check only the last seven days, but you can change this value just specifying how many days back you want to check.

To analyze the last seven days, and list only the database with FULL recovery model without a log backup

select * from sys2.databases_backup_info(default)
where recovery_model = 3 and log_backup = 0

To analyze the last fifteen days, and list only the database with FULL recovery model with a differential backup

select * from sys2.databases_backup_info(15)
where recovery_model = 3 and diff_backup = 1

I just love this script, I use it every time I need to check that backups are not too old and that t-log backup are correctly scheduled.


This is just a wrapper around sys.dm_exec_query_memory_grants that enriches the default result set with the text of the query for which memory has been granted or is waiting for a memory grant and, optionally, its execution plan


This is a stored procedure that list all the available databases and for each one the overall size, the used space within that size, the maximum size it may reach and the auto grow options. This is another script I use every day in order to be able to monitor, track and forecast database space usage.

As usual feedbacks and suggestions are more than welcome!

Published Sunday, February 13, 2011 7:30 PM by Davide Mauri
Filed under: , , , , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Jim said:

I'm getting an error on the db_id() parens when I try to compile the operational stats function in SS2008 R2.  It complains about the left parens.  any idea?

February 15, 2011 1:54 PM

Davide Mauri said:

Hi Jim

I've just tested it on SQL Server 2008 R2 and it work perfectly. I just did a copy and paste of sys2.indexes_operational_stats.sql from CodePlex to SSMS and I had no problem...

If you execute the following query, do you still get an error?

select * from sys.dm_db_index_operational_stats(db_id(), null, NULL, NULL)

In my case it also works perfectly.

February 15, 2011 3:52 PM

abx said:


May 31, 2018 11:06 PM

chenlixiang said:



September 18, 2018 10:11 PM

chenjinyan said:


October 9, 2018 6:40 PM

chenqiuying said:


October 10, 2018 6:40 PM

xiaozhengzheng said:

2018-11-20 xiaozhengzheng

November 19, 2018 8:19 PM

chenlixiang said:


November 20, 2018 10:49 PM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement