THE SQL Server Blog Spot on the Web

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

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

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: 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:

image

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!

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

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement