Search results matching tags 'T-SQL', '2008', and 'Scripts'

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

manowar — Sun, 13 Feb 2011 17:30:22 GMT

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!

SYS2 DMVs on CodePlex

manowar — Mon, 18 Jan 2010 18:09:03 GMT

I’ve just created a new project on CodePlex anmed SYS2DMVS, where I’ll put all my sys2 scripts so that they can be found and accessed more easily. Also it will more easy for me to maintain them, just a Check-In with SVN and that’s it :-).

http://sys2dmvs.codeplex.com/

The scripts are growing in number, they are now 9, so I though that they deserve a better place to stay than my personal website.

This also has another important meaning: if you want to contribuite, I’ll be *very* *very* happy to have you on board for this project. I think it would be very nice and useful if we can make this project grow all togheter.

So, if you have a script to share, and you have the will to make it adhere to a certain style (I followed the DMVs style: lower case name, using “_” to separate names, and so on) and to share your own scripts with the community, you’re more than welcome!

sys2 scripts updated

manowar — Thu, 07 Jan 2010 20:54:10 GMT

I’ve updated my “sys2” scripts with three 3 new scripts:

sys2.objects_dependencies
A wrapper around sys.sql_expression_dependencies that shows also related informations taken from sys.object table, like object name, object type and schema name of the referencing entity.

sys2.objects_partition_ranges
Shows information on partitioned table like rows per partition, partition ranges and partition filegroup destination.

sys2.objects_data_spaces
List tables and indexes and show in which filegroup they reside.

They are freely available – along with all the others – here:

http://www.davidemauri.it/resources/sql-scripts.aspx

Enjoy!