Shulou(Shulou.com)06/01 Report--

Yesterday, a netizen on the Internet said that because his colleague mistakenly set "max server memory" to 10m, the SQL Server database could not be logged in. At that time, I briefly tested it. Today, I am free to sort out the whole process and record it here.

Set "max server memory" in SSMS's UI interface, even if you set it to 10m, but it will be "quietly" changed to 128m by default. You will find that it secretly "modified" your setting value (changed to 128m) after tracking or setting it with Profile.

EXEC sys.sp_configure N'max server memory (MB)', N'128'GORECONFIGURE WITH OVERRIDEGO Configuration option 'max server memory (MB)' changed from 4096 to 128. Run the RECONFIGURE statement to install.

If you don't pay attention to these details or don't believe it, you can also test it with a script, as shown below, which prompts you that the value (10m) is not a valid value.

When you set "max server memory" incorrectly, basically, any query or connection will have an error similar to the following:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

There is insufficient system memory in resource pool 'internal' to run this query. (Microsoft SQL Server, Error: 701)

-

ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0-the remote host forcibly closes an existing connection.) (Microsoft SQL Server, Error: 10054)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

-

The remote host forced an existing connection to be closed.

If you check the error log of the database, you will find a lot of additional information. The excerpt is as follows:

...

...

2019-12-24 10 spid53 There is insufficient system memory in resource pool 'internal' to run this query.

2019-12-24 10-15 spid53 Error: 18056, Severity: 20, State: 29. (Params:) The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2019-12-24 10 State 15 Server Error: 17300, Severity: 16, State: 1. (Params:) The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2019-12-24 10 16 12. 70 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1

2019-12-24 10 1612.70 Server

Process/System Counts Value

Available Physical Memory 6614454272

Available Virtual Memory 140726213148672

Available Paging File 7776440320

Working Set 95432704

Percent of Committed Memory in WS 100

Page Faults 57030

System physical memory high 1

System physical memory low 0

Process physical memory low 1

Process virtual memory low 0

2019-12-24 10 1612.70 Server

Memory Manager KB

VM Reserved 10652776

VM Committed 57972

Locked Pages Allocated 86472

Large Pages Allocated 0

Emergency Memory 1024

Emergency Memory In Use 16

Target Committed 131072

Current Committed 144448

Pages Allocated 84176

Pages Reserved 0

Pages Free 0

Pages In Use 144432

Page Alloc Potential-19912

NUMA Growth Phase 2

Last OOM Factor 1

Last OS Error 0

2019-12-24 10 1612.70 Server

Memory node Id = 0 KB

VM Reserved 10652712

VM Committed 57952

Locked Pages Allocated 86472

Pages Allocated 84176

Pages Free 0

Target Committed 131048

Current Committed 144424

Foreign Committed 0

Away Committed 0

Taken Away Committed 0

2019-12-24 10 1612.70 Server

Memory node Id = 64 KB

VM Reserved 0

VM Committed 20

Locked Pages Allocated 0

2019-12-24 10 1612.70 Server

MEMORYCLERK_SQLGENERAL (node 0) KB

...

...

To solve this problem, you need to turn off the database service, start the database instance in single-user mode + minimum configuration, and then modify the max server memory parameters. If you encounter some problems in the process of shutting down the database, you can solve the problem by restarting the server (this should be decided according to the actual situation. Sometimes there will be no problems, sometimes there will be some problems, such as the net stop mssqlserver command is stuck and service_ state [MSSQLSERVER]): Stop pending)

Note: if you start in single-user mode and then connect to the database with sqlcmd, the following error will occur, so you must start the database instance in single-user mode + minimum configuration

EXEC sys.sp_configure 'max server memory (MB)', 4096; # sets the memory size according to the actual situation. RECONFIGURE GO

Then restart the SQL Server instance and the problem is solved. Of course, you can also restore the backup of the master library to other test databases, and then replace the relevant files of the current database master with the relevant files of the restored master database to solve the problem. But that will be relatively troublesome, there is no such method is simple and effective!

C:\ Windows\ system32 > net stop mssqlserver

The SQL Server (MSSQLSERVER) service is stopping.

The SQL Server (MSSQLSERVER) service was stopped successfully.

C:\ Windows\ system32 > net start mssqlserver

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.