2

While doing a restore of a big database 1.8 TB into a different server I came across this error message when the restore was about 90% completed:

90 percent processed.
Msg 3013, Level 16, State 1, Line 52
RESTORE DATABASE is terminating abnormally.

Msg 1204, Level 19, State 4, Line 52
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

enter image description here

SELECT [instance]=@@servername,[setting name]=name, value, value_in_use, [description]
FROM sys.configurations
where name in ('min server memory (MB)',
               'min memory per query (KB)',
               'max server memory (MB)',
               'optimize for ad hoc workloads')

enter image description here

                SELECT [instance]=@@servername,
                       cpu_count AS [Logical CPU Count], 
                       hyperthread_ratio AS [Hyperthread Ratio],
                cpu_count/hyperthread_ratio AS [Physical CPU Count], 
                CONVERT(decimal(12,2),physical_memory_kb/1024.00/1024.00) AS [Physical Memory (GB)], 
                affinity_type_desc, 
                virtual_machine_type_desc, 
                sqlserver_start_time
                FROM sys.dm_os_sys_info WITH (NOLOCK) 
                

enter image description here

using this to check the sql error log, I could not find any clear indication of the cause of the above.

EXEC xp_readerrorlog 1

this is what I found:

Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

what to do to allow my restores to work thoroughly and not stop at the middle?

1 Answer 1

3

I remember I had similar problem during the database restore, in my case it were reporting queries that run every 15-20 min and took tens of gigabytes of RAM for memory grants, as well as some extra memory for locks. That seemed to create memory pressure and disrupt the database restore.

The solution in my case was to temporarily disable SQL account that reporting queries used, until database restore was completed.

For your case I would suggest either increasing max server memory to a bigger number, or prevent users or apps or reports from running heavy queries for the duration of restore.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.