Solved SQL Server Error : There is insufficient memory available in the buffer pool

While Taking backup of SQL Server table getting error Insufficient memory available in buffer pool.

Note : This is one time activity therefor I didn't try to increase buffer pool memory

select * INTO USER_TABLE_2023_BKP19JULY2023 from USER_TABLE_2023

GO

Error : there is insufficient memory available in the buffer pool

(1 row affected)

Msg 802, Level 17, State 0, Line 1

There is insufficient memory available in the buffer pool.

Completion time: 2023-07-19T11:53:00.2383646+05:30

Possible Solution : Insert data to backup table in chunk.

DECLARE @rerord_count INT = 1;
WHILE @rerord_count > 0
BEGIN
  BEGIN TRANSACTION;
  INSERT USER_TABLE_2023_BKP19JULY2023(USERNAME, PROGRAM,USER_STATUS)
    SELECT TOP (5000)  USERNAME, PROGRAM,USER_STATUS 
    FROM USER_TABLE_2023 AS ST
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM USER_TABLE_2023_BKP19JULY2023 AS TT
      WHERE cast(TT.USERNAME as VARCHAR(100))+'#$----$#'

            +cast(TT.PROGRAM as VARCHAR(100))+'#$----$#'

            +cast(TT.USER_STATUS as VARCHAR(100)) = 

            cast(ST.USERNAME as VARCHAR(100))+'#$----$#'

            +cast(ST.PROGRAM as VARCHAR(100))+'#$----$#'

            +cast(ST.USER_STATUS as VARCHAR(100))

    )
    ORDER BY USERNAME,PROGRAM,USER_STATUS;
  SET @rerord_count = @@ROWCOUNT;
  COMMIT TRANSACTION;
END
GO


(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(3508 rows affected)
(0 rows affected)
Completion time: 2023-07-19T11:30:17.5047267+05:30

---Check Buffer pool size for a database

SELECT

  database_id AS DatabaseID,

  DB_NAME(database_id) AS DatabaseName,

  COUNT(file_id) * 8/1024.0 AS BufferSizeInMB

FROM sys.dm_os_buffer_descriptors

where database_id=24

GROUP BY DB_NAME(database_id),database_id

ORDER BY BufferSizeInMB DESC

GO 

DatabaseID DatabaseName                 BufferSizeInMB

24                 TESTDB_LOCAL                 0.414062

DatabaseID DatabaseName                BufferSizeInKB

24                TESTDB_LOCAL                 424

---Table Size

SELECT

s.Name AS SchemaName,

t.Name AS TableName,

p.rows AS RowCounts,

CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,

CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,

CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB

FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

where t.Name IN('USER_TABLE_2023')

GROUP BY t.Name, s.Name, p.Rows

ORDER BY s.Name, t.Name

GO

SchemaName TableName                RowCounts Used_MB Unused_MB Total_MB

dbo                 USER_INTERFACE 353508         31.09          0.95          32.03