Jon Gurgul's Blog

Category: SQL Server

  • Running as local system to allow administrators in to SQL Server

    —

    by

    Jon Gurgul
    in

    In cases when there are no provisioned accounts in a SQL Server instance which are known or working, then there is an option instead to run as nt authority \ system (local system). Note that in SQL 2012 local system no longer has sa and you will have to instead start SQL Server in single user…

  • 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…

  • Last Backup Occurred

    —

    by

    Jon Gurgul
    in

    This script will show when the last backup occurred as well as the backup type for each database. This covers all the backup types available and pivots the output into a simple and easily readable format. It will state the reason; if any, for log reuse wait and the database recovery model in use. Depending…

  • Get-VolumeFreeSpace

    —

    by

    Jon Gurgul
    in ,

    Here is an example function to return free space remaining on a volume with associated details. Updated 27/04/2012 to fix mount points. Usage Examples: Get-VolumeFreeSpace|ft Get-VolumeFreeSpace|Select ComputerName,Name,MountPoint,FreeSpaceGiB|ft Get-VolumeFreeSpace|Where-Object {!$_.SystemVolume}|Select ComputerName,Name,MountPoint,FreeSpaceGiB|ft Get-VolumeFreeSpace “ComputerA”,”ComputerB” |ft

  • First day of the week

    —

    by

    Jon Gurgul
    in

    Given a particular date how do you determine the start date of that week? But more importantly what is the start of the week? The first column returned in the script below will return the start of the week as determined by the @@DATEFIRST setting. Using a default setup with the connections made using us_english…

  • Get-DatabaseSizes

    —

    by

    Jon Gurgul
    in ,

    Simple example using SMO to get database storage information. You will need SMO installed for this script to work. Note Size relates to the file size on disk, where as space refers to the storage used or available. Usage Examples: Get-DatabaseSizes Get-DatabaseSizes . | Select * Get-DatabaseSizes . | Select DatabaseName,LogicalName,FileName,Size_MiB Get-DatabaseSizes . | Select…

  • Transaction Log Usage By Session ID

    —

    by

    Jon Gurgul
    in

    A script to show the log usage on a per session basis, which can be useful when determining the potential impact of a query. It can be good to know for example if a session has generated a lot of log entries, and therefore killing that sessions would take a substantial amount of time. I…