Jon Gurgul's Blog

Tag: Script

  • Backup History

    —

    by

    Jon Gurgul
    in

    If you want to see more than just when the last backup occurred. Here is a script for backup history with two bonus features. Backup compression ratio – CompressionRatio Backup speed – Backup_MiB_S

  • SQL Server and PowerShell (SQLPS) Starter

    —

    by

    Jon Gurgul
    in , ,

    The following two snippets of code are two ways to achieve the same outcome, which is the $Server object containing the default instance. Or when opening a SQL PS (Powershell) prompt at the default location. e.g. PS SQLSERVER:\SQL\SB01\DEFAULT> I mention this because I was asked what the simplist entry point to PowerShell for SQL person…

  • Using the default system_health Extended Event

    —

    by

    Jon Gurgul
    in

    The default extended event system_health appeared in SQL 2008 and provides several key monitors, which can prove useful when investigating issues. In the past I have mentioned this in the capturing deadlocks post as it is one of the monitors automatically in place and it is an easy way to grab the deadlock xml. However…

  • Creating a Deadlock

    —

    by

    Jon Gurgul
    in

    Previously we have looked at how to capture a deadlock, but how do we go about creating a deadlock? I think the following is the simplest method: 1. Create two tables with a single row in each and update a row in the first table (note transaction is started with BEGIN TRAN, but not committed.)…

  • Most Recent SQL Query

    —

    by

    Jon Gurgul
    in

    Here is another query that is useful to have when you need to find out what everyone is up to. Or in some cases when we need to recover what was on someones ssms before they accidentally closed without saving work. 🙂

  • SQL Index dm_db_index_xxx_stats

    —

    by

    Jon Gurgul
    in

    There are 3 dm_db_index_xxx_stats objects that can be used to check and investigate index information. sys.dm_db_index_usage_stats can be used to see how beneficial the index is, and its related maintenance cost. Note that this information is reset upon instance restart. The final two index stats objects provide information on the physical and operational statistics of…

  • Simple Table and Index Breakdown With Buffered

    —

    by

    Jon Gurgul
    in

    This script builds on the Simple Table and Index script to further show the buffered metrics. This script will give you a simple breakdown of your tables and indexes, which is shown with schema(s),table(s),index type(s),row count(s) as well as the partition(s) information. This is a reduced version of the “Detailed Table and Index Breakdown With…

  • Detailed Table Index Breakdown With Buffered

    —

    by

    Jon Gurgul
    in

    This script builds on the Detailed Table and Index script to further show the buffered metrics. This script will give you a comprehensive breakdown of your tables and indexes, which is shown with schema(s),table(s),index type(s),index size(s),row count(s) as well as the filegroup(s) and partition(s) information. Also in this script I have included two columns called…

  • Quick data wipe via truncate

    —

    by

    Jon Gurgul
    in ,

    “How to remove all data from a database?” is something that I have often seen asked on the forums. How do you remove all the data quickly? First you try with delete and find that this is too slow and the transaction log has to record all the changes, so you use truncate which is…

  • Leap Year

    —

    by

    Jon Gurgul
    in

    A leap year can be calculated using a formula (Please see Microsoft article), however there is no real need to calculate it this way. All that is needed in sql is to take one day away from March 1st; with the resultant being either the 28th, or in the case of a leap year the…