SQL Server: How to PRINT statement during loop execution

Have you tried to print some information message during some loop execution on SQL Server? So statements like

DECLARE @i INT
DECLARE @msg AS VARCHAR(20)
SET @i = 0
WHILE <condition>
BEGIN
SET @msg = 'Updating ' + CAST(@i AS VARCHAR(30))
PRINT @msg
UPDATE TOP(50) myTable SET col1 = 0 WHERE <update condition>
WAITFOR DELAY '000:00:10'
SET @i = @i + 1
END

print out your messages just when your loop completes (or print in a blocks of messages from once).

But this is not what you wanted. You do not see progress of UPDATE statements. The reason for this is because SQL Server does not allow any interactivity during batch execution. Therefore, PRINT statement buffers it’s output.

To get message whithin the loop execution you should use

REISEERROR(…) WITH NOWAIT 

statement with the severity 10.

So loop will look like:

DECLARE @i INT
DECLARE @msg AS VARCHAR(20)
SET @i = 0
WHILE <condition>
BEGIN
SET @msg = 'Updating ' + CAST(@i AS VARCHAR(30))
RAISERROR(@msg, 10, 0 ) WITH NOWAIT
UPDATE TOP(50) myTable SET col1 = 0 WHERE <update condition>
WAITFOR DELAY '000:00:10'
SET @i = @i + 1
END

On this way you will see messages “Updating 1”, “Updating 2” … during loop execution.

 

Get SQL Server Database Files Sizes

If you want to check how big are your databases on some SQL Server you need to query  [sys].[master_files]. Better results you will get with the following query:

SELECT CASE GROUPING(d.[name])
WHEN 1 THEN 'All'
ELSE d.[name]
END AS [DatabaseName],
CASE GROUPING(f.[name])
WHEN 1 THEN 'Total'
else f.[name]
END AS [FileName],
ROUND(SUM(f.[size]) * 8 / 1024, 0) SizeInMBs
FROM [sys].[master_files] f
INNER JOIN [sys].[databases] d ON d.[database_id] = f.[database_id]
GROUP BY [d].[name], f.[name] WITH ROLLUP
ORDER BY d.[name]