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

Project Page: http://sys2dmvs.codeplex.com/

Source Code Download: http://sys2dmvs.codeplex.com/SourceControl/changeset/view/57732

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:

database_backup_info

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.

query_memory_grants

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

stp_get_databases_space_used_info

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!