Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
TSQL Bulk-Copy Check-Up Sample
/* NJohnson9402 / natethedba.wordpress.com
Designed for use with RedGate SQLPrompt Snippet Manager.
The $PLACEHOLDERS$ tokens get replaced with values of your choice when you use the snippet in your SSMS editor.
For plain/raw TSQL use, replace the tokens with actual values as desired.
See corresponding blog-post for background. Basically, this is a quick way to "check up on" the status of a
bulk table-copy (data copy from one table to another, potentially across different databases and/or schemas),
by knowing the original start-time, and getting row-counts using `sp_spaceused` for each table (source, destination).
This way, you can get a fairly accurate ETA on when the job will finish, based on how it's done so far.
*/
DECLARE @Sql nvarchar(MAX), @Msg nvarchar(MAX);
DECLARE @StartTime datetime = '$START_TIME$';
DECLARE @DbName_Src sysname, @DbName_Dst sysname
, @SchemaName_Src sysname, @SchemaName_Dst sysname
, @TableName_Src sysname, @TableName_Dst sysname
;
SELECT @DbName_Src = '$SOURCE_DB$', @DbName_Dst = '$DEST_DB$'
, @SchemaName_Src = '$SOURCE_SCHEMA$', @SchemaName_Dst = '$DEST_SCHEMA$'
, @TableName_Src = '$SOURCE_TABLE$', @TableName_Dst = '$DEST_TABLE$'
IF (OBJECT_ID('tempdb.dbo.#SpaceUsed') IS NOT NULL)
DROP TABLE #SpaceUsed;
CREATE TABLE #SpaceUsed (id int not null identity(1,1)
, [name] nvarchar(128), [rows] char(11), [reserved] varchar(18), [data] varchar(18)
, [index_size] varchar(18), [unused] varchar(18), [measured_at] datetime NOT NULL DEFAULT(GETDATE())
);
--Destination
SET @Sql = QUOTENAME(@DbName_Dst) + '.sys.sp_spaceused ''' + QUOTENAME(@SchemaName_Dst) + '.' + QUOTENAME(@TableName_Dst) + '''';
INSERT #SpaceUsed (name, rows, reserved, data, index_size, unused)
EXEC sp_executesql @Sql
--Source
SET @Sql = QUOTENAME(@DbName_Src) + '.sys.sp_spaceused ''' + QUOTENAME(@SchemaName_Src) + '.' + QUOTENAME(@TableName_Src) + '''';
INSERT #SpaceUsed (name, rows, reserved, data, index_size, unused)
EXEC sp_executesql @Sql
DECLARE @PctComplete real, @ELT_seconds int, @ETT_seconds int, @ETA datetime;
SELECT @PctComplete = CONVERT(real, Curr.[rows]) / CONVERT(real, Total.[rows]) * 100.0
, @ELT_seconds = DATEDIFF(SECOND, @StartTime, Curr.measured_at)
, @ETT_seconds = DATEDIFF(SECOND, @StartTime, Curr.measured_at) * CONVERT(real, Total.[rows]) / CONVERT(real, Curr.[rows])
FROM #SpaceUsed Curr, #SpaceUsed Total
WHERE Curr.id=1 AND Total.id=2
SET @ETA = DATEADD(SECOND, @ETT_seconds, @StartTime)
SELECT [Elapsed time (day.hh:mm:ss)] = CONVERT(varchar(10), @ELT_seconds / 86400) + '.' + CONVERT(varchar(30), DATEADD(SECOND, (@ELT_seconds % 86400), 0), 108)
, [% Complete] = CONVERT(varchar(10), @PctComplete)
, [# Rows] = CONVERT(varchar(10), Curr.[rows]), [of Total] = CONVERT(varchar(10), Total.[rows])
, [Est. total time (day.hh:mm:ss)] = CONVERT(varchar(10), @ETT_seconds / 86400) + '.' + CONVERT(varchar(30), DATEADD(SECOND, (@ETT_seconds % 86400), 0), 108)
, ETA = @ETA, ETA_Friendly = CONVERT(varchar(30), @ETA)
FROM #SpaceUsed Curr, #SpaceUsed Total
WHERE Curr.id=1 AND Total.id=2
@NJohnson9402

This comment has been minimized.

Copy link
Owner Author

commented Jul 7, 2017

TBD: finish corresponding blog post, add more comments, test with snippet-manager.

@NJohnson9402

This comment has been minimized.

Copy link
Owner Author

commented Aug 30, 2017

Blog post finished at http://wp.me/p7D6Ye-13p ; works with RegGate snippets.

@NJohnson9402

This comment has been minimized.

Copy link
Owner Author

commented Aug 10, 2018

Updated to show row-counts in final output (might as well!)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.