Create a gist now

Instantly share code, notes, and snippets.

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)
, [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
Owner

NJohnson9402 commented Jul 7, 2017

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

Owner

NJohnson9402 commented Aug 30, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment