701: Failure to allocate sufficient memory to run a query.
802: Failure to get memory to allocate pages in the buffer pool (data or index pages).
1204: Failure to allocate memory for locks.
6322: Failure to allocate memory for XML parser.
6513:Failure to initialize CLR due to memory pressure.
6533: AppDomain unloaded due to out of memory.
8318: Failure to load SQL performance counters due to insufficient memory.
8356 or 8359: ETW or SQL trace fails to run due to low memory.
8556: Failure to load MSDTC due to insufficient memory.
8645: Failure to execute a query due to no memory for memory grants (sorting and hashing) For more information, see How to troubleshoot SQL Server error 8645.
8902: Failure to allocate memory during DBCC execution.
9695 or 9696: Failure to allocate memory for Service Broker operations.
17131 or 17132: Server startup failure due to insufficient memory.
17890: Failure to allocate memory due to SQL memory being paged out by the OS.
22986 or 22987: Change data capture failures due to insufficient memory.
25601: Xevent engine is out of memory.
26053: SQL network interfaces fail to initialize due to insufficient memory.
30085, 30086, 30094: SQL full-text operations fail due to insufficient memory.
Error: 5180, Severity: 22, State: 1
Could not open XYZ for invalid file ID ## in database. Table or database may be corrupted.
A query or operation may fail with an error 5180 when an invalid file ID is referenced by the SQL Server Database Engine. Since the error is raised with severity 22, the user's session will be disconnected. This error message is written into the SQL Server Error Log and the Windows Application Event Log with EventID = 5180.
The SQL Server Database Engine references a file ID in many different situations mostly when referencing a page ID (since the file ID is the first portion of the page ID). If for any reason, the file ID being referenced is < 0 or is not a valid file ID in a database (per the valid file IDs listed in system catalog views such as sys.database_files), then a 5180 error can be encountered.
If you encounter this error, you should run DBCC CHECKDB against the database as listed in the error message. If you find errors, you should restore from a known good Backup. If you cannot restore from a Backup, then your other option is to use the repair option of DBCC CHECKDB as recommended by its output. In most cases, a repair of this type of error will result in a data loss.
Use SQL Server Profiler and capture the SQL:BatchStarting, RPC:Starting, and Exception Events. Find the query that precedes the Exception Event for 5180 for the session associated with the Exception Event.
Error: 9004, Severity: 23 State: 6
An error occurred while processing the log for database 'db_name'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Error 9004 is a general error indicating the contents of the transaction log are damaged. The reason for the log to become inconsistent are similar to any database corruption problem detected by the SQL Server Engine. To find the cause for the log damage, you should follow similar techniques used for database corruption including an analysis of possible hardware, filesystem, and I/O problems. Note that DBCC CHECKDB does not check the transaction log as part of its operations and cannot detect log corruption errors. Error 9004 is raised by SQL Server Engine itself.
One of the following actions will correct this error:
Restore from a backup: Restore from a known good backup to recover from this problem. It is possible that, if the log portion of a database or log backup contains damaged contents, you encounter an Error 9004 on RESTORE. In this situation, the transaction log in the backup is damaged.
Rebuild the log: If you cannot restore from a backup, you may be able to bring the database online by rebuilding the transaction log. You should carefully understand the ramifications of rebuilding the transaction log. This includes possible loss of transactional consistency in your database.
Error: 832, Severity: 24, State: 1
A page that should have been constant has changed
An external factor has caused a database page to be modified outside the normal SQL Server engine code used to change database pages. The conditions could be:
A thread running in the SQL Server process that incorrect writes on a database page. This is often referred to as a "scribbler"
A hardware or operating system problem were the memory backing the database page is incorrect modified or damaged
When SQL Server detects this behavior error 832 is raised.
To find the cause of the error, consider these options:
You should run any normal hardware or system checks to determine if a memory, CPU, or other hardware-related problem exists. Ensure all system drivers, Operating system updates, and hardware updates have been applied to our system. Consider running any hardware manufacture diagnostics including memory-related tests.
Evaluate what "external" DLLs may be loaded in SQL Server that could cause this problem including extended stored procedures, COM objects, or other DLLs that may be incorrectly modifying SQL Server memory reserved for database pages.
Anytime you see this error, you should immediately consider running DBCC CHECKDB against the database referenced by the <dbid> in the error message.
Msg 823, Level 24, State 2, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000000000 in file 'C:\SQLData\mydatabase.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error
Error 824 signifies a logical consistency error during a read or write operation. This could be due to corrupt pages within the database file, often caused by disk issues or a sudden power loss. Similar to Error 823, your immediate response should be to run DBCC CHECKDB to check the integrity of your database and attempt repairs.
Msg 9001, Level 21, State 1, Line 1
The log for database 'mydatabase' is not available.
Error 9001 indicates that the transaction log for a database is not accessible, which can halt database operations. This could be due to a full disk, file corruption, or other I/O issues. When this error appears, start by checking the SQL Server error log and the Windows event log for additional details.
Commonly error 9001 occurs when a transaction log file is damaged or due to storage device issues that make the log file unavailable. Here are two examples of errors that you may observe:
An example where the storage volume became unavailable and OS returned error "The device is not ready". You can see other errors that resulted from the disks being damaged on unavailable. These examples provide context so you can understand that error 9001 is just one of the many symptoms of a larger issue.
To address such issues:
Ensure that the storage volumes where the database and log files reside are online, that the entire I/O path from machine to storage is stable and reliable, and that it doesn't lead to physical file damage.
Work with your hardware and device manufacturer to ensure that hardware and its configuration is suitable to the I/O requirements of a database system. Ensure that device drivers, firmware, BIOS and other supporting software components in the I/O path are up to date.
Run DBCC CHECKDB to check the consistency of the database, if it can be brought online after a restart
If the database and log files aren't intact and as a result the database can't come online, restore the last known good backup of the database
Msg 3132
The media set has 6 media families
This error message means that the original backup was done as a striped backup where the backup stream was split into two destination files. When attempting to restore, you need to specify all of the same files which were used to take the backup. So, the simple version is: If you back up to 2 files, you must specify 2 files to restore from.
ISSUES
Some CPU cores are not accessible to SQL Server due to affinity masking or licensing problems
Question They have a really a very big server – by configuration – close to 80 cores. And they said, for some strange reasons, the SQL Server was not using all of them. They felt SQL Server was not optimized for such high number of server cores.
Answer
You have got CPUs, but SQL Server isn’t using all of them. This can be caused by affinity masking or by SQL Server not being licensed to use all of its CPU cores.
This can be checked using a simple DMV script on sys.dm_os_schedulers looking for is_online = 0.
SELECT * FROM sys.dm_os_schedulers
GO
Disabled Schedulers Can Mean Major Performance Trouble. How would you fix Disabled Schedulers?
Here are few scenarios’ that are common and lesser known. Check for each of the following scenarios:
Did someone mess up affinity masking on the server? If you’re using affinity masking, make sure you know what you’re doing. You are basically restricting SQL Server’s CPU use, tying some of its hands behind its back. And this can be one of the possibilities because a junior DBA was playing around with these settings.
Is this a virtualized environment? Are virtual cores misconfigured by mistake? If you are using virtualization, consider increasing the number of cores per virtual socket. This is a purely software change that doesn’t usually impact performance, although there’s some gotchas around NUMA configuration. I can discuss about them some other time. But please double check.
Did you upgrade your SQL Server instance recently? Are you running the wrong license, or unaware of your licensing limitations? If you’re using SQL Server 2012 Enterprise, and it was upgraded from CAL-based licensing, you may be limited to 20 cores.
From SQL Server 2012 onwards, there are two enterprise licenses – Core Based and
SERVER/CAL Based. As per my research, CAL based license limits SQL Enterprise to use only 20 physical processors. If hyper-threading is enabled then it would be 40 logical processors.
No comments:
Post a Comment