Path: csiph.com!news.mixmin.net!eternal-september.org!reader01.eternal-september.org!.POSTED!not-for-mail
From: Anton Shepelev <anton.txt@g{oogle}mail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: `sqlcmd' withholding error messages?
Date: Fri, 19 Aug 2022 17:52:45 +0300
Organization: A noiseless patient Spider
Lines: 146
Message-ID: <20220819175245.7e5cceb77519de242cc7b54c@g{oogle}mail.com>
References: <20220811131035.f10b114df6b905668746c2ab@g{oogle}mail.com> <XnsAEF1E77EE8A2CYazorman@127.0.0.1>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: reader01.eternal-september.org; posting-host="5512ec1875fad79f40e4fc89ad6af1b4"; logging-data="1614021"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19unKpSw6Vfl9P8VDQGkGH8ZnU729YCmzo="
Cancel-Lock: sha1:+9JIpYE3WDh4scppAFSgk3erUiQ=
X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
Xref: csiph.com comp.databases.ms-sqlserver:2157

Erland Sommarskog:

> Unfortunately  without  a reproducible case, this is
> difficult to comment on.

I should tell you how to reproduce it but  if  I  knew
myself...

> What  I  can say is that sometimes I get the feeling
> that SQLCMD does not relay all errors when there are
> many, but I have never investigate it.

It has several mutually dependent parameters governing
reaction to errors.

> But have I run into no errors at all, but still it's
> apparent  that the operation falied? When I think of
> it, I think I have, but I don't recall the  details.
> What's in your scripts? Loading of stored procedures
> and other objects? Or something else? (In  my  case,
> it was most likely a stored procedure.)

It  is  my scandalous memory balancer that failed, but
it is much more stable now than it used  to  be.   The
code is not of publishing quality, so you might prefer
to skip it:

-- WARN: May cause the instance to freeze and become unresponsive:
--       Invoke within a DAC to decrease the chance of this failure.
-- TODO: Analyse sys.dm_os_wait_stats from DAC when a server hangs next time.
sp_configure 'show advanced options', 1 RECONFIGURE

/* ----------------------- User-adjustable paramters ------------------------ */
DECLARE @srv_n INT =    6 -- number of MSSQL instances
DECLARE @h_sta INT =   10 -- workday starting hour
DECLARE @h_end INT =   19 -- workday ending hour
DECLARE @m_res INT = 2048 -- memory reserved for the OS
DECLARE @m_db  INT =   14 -- additional memory per DB

/* -------------------------------- Constants ------------------------------- */
-- TODO: instead of c_mis, try: round down to next 32, subract 32.
DECLARE @c_mmi INT         = 128
DECLARE @c_mma INT         = 2147483647
DECLARE @c_ste INT         = 32 -- minumum memory decrement (exp.)
DECLARE @c_two FLOAT       = 2
DECLARE @c_min VARCHAR(17) = 'min server memory'
DECLARE @c_max VARCHAR(17) = 'max server memory'
DECLARE @c_ofs INT         = 32

/* -------------------------------- Variables ------------------------------- */
DECLARE @v_low BIT,
        @p_low BIT
-- RAM values, in MB:
DECLARE @m_min INT        -- min RAM parameter
DECLARE @m_use INT        -- RAM used by this MSSQL instance
DECLARE @m_lim INT        -- target RAM limitation
DECLARE @m_lco INT        -- corrected limit
DECLARE @N     INT
DECLARE @DB_N  INT        -- number of databases
DECLARE @r_msg NVARCHAR(max) -- result message
DECLARE @r_pre NVARCHAR(max) -- message prefix
DECLARE @m_gra INT           -- memory grants pending
DECLARE @m_low BIT
DECLARE @m_mi  INT

DECLARE @dec_2 INT =  @h_end - @h_sta + 1 -- number of iterations for half-decay

/* ------------------------------ Main script ------------------------------- */
-- Active only in work hours:
IF NOT DATEPART( hour, GETDATE() ) BETWEEN @h_sta AND @h_end GOTO Finish
SET @r_pre = ''

SELECT @DB_N = SUM(1) FROM sys.databases

SET     @m_mi  = 1 GOTO MemInfo
MI1: IF @m_low = 1 GOTO Finish

SET @m_min  = @c_mmi + @m_db * @DB_N
IF @m_use <= @m_min BEGIN
     SET  @r_msg = FORMATMESSAGE('Current memory usage below lower limit %i Mb.', @m_min)
     GOTO Finish END

SET @m_lim = @m_min + (@m_use - @m_min) / POWER(@c_two, 1.0/@dec_2)
IF  @m_use - @m_lim < @c_ofs / 4 BEGIN
     SET @m_lim = @m_use - @c_ofs / 4
END

SET @m_lco = @m_lim - @c_ofs
IF  @m_lco < @m_min BEGIN
     --SET  @r_msg = FORMATMESSAGE('Current memory usage within correction offset %i Mb of the lower limit %i Mb', @c_ofs, @m_min)
     --GOTO Finish END
     SET @m_lco = @m_min END

SET @r_pre = FORMATMESSAGE('%i -> %i: ', @m_use, @m_lim)

EXEC sp_configure @c_min, 0
EXEC sp_configure @c_max, @m_lco RECONFIGURE

SET @r_msg = 'Failed to decrease memory usage in time.'
SET @N = 60
WHILE @N > 0
BEGIN
     SET @m_mi = 2      GOTO MemInfo
     MI2: IF @m_low = 1 BREAK

     WAITFOR DELAY '00:00:01'
     IF @m_use < @m_lim + @c_ofs/8 BEGIN
          -- TODO: Will misfire in case of decrements < 2*@c_ste: fix it somehow!
          SET @r_msg = 'Memory usage reached target level.'
          BREAK                     END
     SET @N = @N - 1
END
EXEC sp_configure @c_max, @c_mma RECONFIGURE
Finish:
SET @r_msg = @r_pre + FORMATMESSAGE('%i: %s', @m_use, @r_msg)
PRINT @@servername + ': ' + @r_msg
GOTO EndOfScript

MemInfo:
/* --------------------------- Subroutine MemInfo  -------------------------- */
-- IN : @m_mi: return point: 1 for label MI1 and 2 for label MI2
-- OUT: @m_use: RAM used by isntance,
--      @m_low: low memory condition
--      @r_msg: low memory message
SET @m_low = 1

SELECT @m_use = physical_memory_in_use_kb/1024,
       @p_low = process_physical_memory_low   ,
       @v_low = process_virtual_memory_low
FROM sys.dm_os_process_memory
IF @p_low = 1 OR @v_low = 1 BEGIN
     SET @r_msg = 'Low memory.' GOTO LOWMEM END

SELECT @m_gra = cntr_value
FROM   sys.dm_os_performance_counters
WHERE  counter_name = N'Memory Grants Pending'
IF @m_gra > 0 BEGIN
     SET @r_msg = 'Memory grants pending' GOTO LOWMEM END

SET @m_low = 0
LOWMEM:

IF @m_mi = 1 GOTO MI1
IF @m_mi = 2 GOTO MI2

EndOfScript: