Posts

Get SQL Server Index Size

Modified from this SQLShack article . DECLARE @SchemaName SYSNAME = N 'dbo' ; DECLARE @TableName SYSNAME = N 'MyTableName' ; DECLARE @object_id INT ; SELECT @object_id = [ object_id ] FROM sys . tables WHERE [ schema_id ] = SCHEMA_ID ( @SchemaName ) AND [ name ] = @TableName ; SELECT tn . [ name ] [ TableName ] , ix . [ name ] [ IndexName ] , FORMAT ( SUM ( sz . [ used_page_count ] ) * 8 / 1024 , '#,##0' ) AS [ Index size ( MB ) ] FROM sys . dm_db_partition_stats AS sz INNER JOIN sys . indexes ix ON sz . [ object_id ] = ix . [ object_id ] AND sz . [ index_id ] = ix . [ index_id ] INNER JOIN sys . tables tn ON tn . OBJECT_ID = ix . object_id WHERE tn . [ object_id ] = @object_id GROUP BY tn . [ name ] , ix . [ name ] ORDER BY SUM ( sz . [ used_page_count ] ) * 8 DESC ;

Get SQL Server Index Fragmentation

Modified from RedGate SQL Scripts Manager DECLARE @SchemaName SYSNAME = N 'dbo' ; DECLARE @TableName SYSNAME = N 'MyTableName' ; DECLARE @object_id INT ; SELECT @object_id = [ object_id ] FROM sys . tables WHERE [ schema_id ] = SCHEMA_ID ( @SchemaName ) AND [ name ] = @TableName ; --fragmentation SELECT i . [ name ] [ index ] , ddips . [ index_type_desc ] , ddips . [ avg_fragmentation_in_percent ] [ FragmentationPercent ] , ddips . [ fragment_count ] , ddips . [ page_count ] FROM sys . dm_db_index_physical_stats ( DB_ID ( ) , NULL , NULL , NULL , 'limited' ) ddips JOIN sys . [ indexes ] i ON ddips . [ object_id ] = i . [ object_id ] AND ddips . [ index_id ] = i . [ index_id ] WHERE ddips . [ object_id ] = @object_id AND ddips . alloc_unit_type_desc = 'IN_ROW_DATA' ORDER BY ddips . [ avg_fragmentation_in_percent ] DESC ;

Retrieve SSIS package contents

Here's a quick .NET console application to retrieve the DTSX XML from SQL Server for an SSIS package: using System . Collections . Generic ; using System . Data ; using System . IO ; using Microsoft . Data . SqlClient ; async IAsyncEnumerable < byte [ ] > ReadFileAsync ( ) { int startingByte = 1 ; while ( true ) { byte [ ] bytes ; using var conn = new SqlConnection ( " server=XXXXX;database=msdb;integrated security=true; " ) ; await conn . OpenAsync ( ) . ConfigureAwait ( false ) ; using var comm = conn . CreateCommand ( ) ; comm . CommandText = @"             SELECT substring(packagedata, @StartingByte, 8000) [FileContents]             FROM msdb.dbo.sysssispackages             WHERE name = 'MyPackageName' -- assuming your package has a unique name on the server          " ; comm . Parameters . Add ( new SqlParameter ( " @StartingByte "

Restore all Glacier objects in S3 bucket

If you've got an entire bucket in S3 with items in Glacier or Glacier Deep Archive storage class, and you want to restore them to download them, this C# code will restore all of the files. Just set the constant values at the top and it'll go through the items one-by-one and submit a restore request. No notifications or anything when the restore is done, so maybe just give it a day or so and come back. using static System . Console ; using Amazon ; using Amazon . Runtime ; using Amazon . S3 ; using Amazon . S3 . Model ; const string ACCESS_KEY = " XXXXXXXXXXXXXXXX " ; const string SECRET_KEY = " XXXXXXXXXXXXXXXX " ; var regionEndpoint = RegionEndpoint . XXXXXXXXXXXXXXXX ; const string BUCKET_NAME = " XXXXXXXXXXXXXXXX " ; const int NUM_DAYS = 15 ; var client = new AmazonS3Client ( new BasicAWSCredentials ( ACCESS_KEY , SECRET_KEY ) , regionEndpoint ) ; var request = new ListObjectsRequest { BucketName =

Exception logging in ASP.NET Core

public void HandleException ( IApplicationBuilder app ) { app . Run ( async requestContext = > { try { string path = requestContext . Request . Path ; var exceptionHandlerPathFeature = requestContext . Features . Get < IExceptionHandlerPathFeature > ( ) ; var exception = exceptionHandlerPathFeature ? . Error ; User ? user = null ; try { user = /* implementation */ Cast requestContext . User into your custom user type } catch ( Exception ex ) { Debug . WriteLine ( ex ) ; } if ( exception != null ) { new Thread ( ( ) = > { // Using a new thread to escape any open transactions that are getting rolled back try { using var conn = new SqlConnection ( Configuration . GetConnectionString ( " connstr " ) ) ; conn . Open ( ) ; using var comm = conn . CreateCommand (

Brotli in .NET

Brotli  is a compression algorithm like GZip - it tends to have smaller compressed files at the expense of a small amount of extra time. Implementing in .NET is just as easy as with GZip: public class Brotli { public static void Compress ( Stream inputStream , Stream outputStream ) { using var gzip = new BrotliStream ( outputStream , CompressionMode . Compress ) ; byte [ ] buffer = new byte [ 8192 ] ; int count ; while ( ( count = inputStream . Read ( buffer , 0 , buffer . Length ) ) > 0 ) { gzip . Write ( buffer , 0 , count ) ; } } public static void Decompress ( Stream inputStream , Stream outputStream ) { using var gzip = new BrotliStream ( inputStream , CompressionMode . Decompress ) ; byte [ ] buffer = new byte [ 8192 ] ; int count ; while ( ( count = gzip . Read ( buffer , 0 , buffer . Length ) ) > 0 )

Check progress of SQL Server restore

From MSSQLTips : SELECT session_id as SPID , command , a . text AS Query , start_time , percent_complete , dateadd ( second , estimated_completion_time / 1000 , getdate ( ) ) as estimated_completion_time FROM sys . dm_exec_requests r CROSS APPLY sys . dm_exec_sql_text ( r . sql_handle ) a WHERE r . command in ( 'BACKUP DATABASE' , 'RESTORE DATABASE' )