Fixing SQL Error 17053 In Tempdb.mdf
Introduction
SQL server is a Relational Database Management System from the side of Microsoft. The primary function provided by the tool is data storage and retrieval. SQL server maintains three types of files for managing data i.e. primary, secondary and a transaction log file. These files have extension as .mdf, .ndf and .ldf respectively.
While managing the large amount of data in the file,chances for occurrence of error are high. SQL Error 17053 is one such error that occurs in SQL server. Microsoft provides a free utility to handle the error situation to some extent. The DBCC CHECDB command checks for the integrity of the database and fixes it to certain level. However, we will discuss the error in detail and the precautionary methods to handle the SQL Error 17053.
Description Of SQL Error 17053:
SQL Server Error 17053 indicates the NTFS file system and the alternate streams for the internal snapshot. The error message mostly indicates the real reason behind the cause.
Case 1:
The error code implies that we have violated the file size constraint in the NTFS file system. The size limitation of NTFS file System is few Exabyte.So, this error while using DBCC CHECKBD can be due to the VSS (Volume Shadow Copy Services) size limitation. By default the VSS snapshot can be less than 10% of the drive size available. This can be managed within few steps as show below.
- From Computer Management select Disk Management option.
- Select Properties on all the drives that contains the database
- Select the pane Shadow Copies then Select Settings
- Select the radio button “No limit”
This option eliminates the VSS limitations on your system so that you can now smoothly go forward with the work.
Case 2:
This error is encountered when we are using an SMB file share or network storage device. You can be met with this error while performing any of the below mentioned activities related to the database.
- Trying to create a new DB.
- While starting the SQL server to perform any activities.
- Marking the database online.
This error can be due to the incompatibility with device IO controllers. SQL server uses the device control code FSCTL_FILESYSTEM_GET_STATISTICS to create and open the log or data files.So the solution is to use a compatible third party network drive or SMB share with Windows API device IO control.
NOTE: If you are using NTFS file system with SQL data file the error can be safely ignored, but if you are having ReFS file system it will lead to the degradation of the performance SQL server.
Case 3:
This error indicates that there is not further space in the disk. This can be due to the unexpected growth of the tempdb.mdf file. You should effectively manage the tempdb file as a precaution to maintain the file size.
What are Tempdb Files?
First let’s see the contents in the tempdb file and then how to manage it. The main contents of a tempdb file are:
- Temporary objects that are explicitly created.
- Tables holding intermediate results from queries.
- Stores transactions related to snapshot isolation.
- Other transactions related to online index operations, triggers, etc.
Some tips to improve the performance of the tempdb files are:
- Use DBCC SHRINKDATABASE command
- Use DBCC SHRINKFILE command
- Use appropriate transact SQL commands
By default, the tempdb file is configured to grow automatically as needed. Sometimes this database may grow unexpectedly in time to a very large size than the desired. This large tempdb files can be shrank to some extent in order to manage the situation. The possible ways to shrink the tempdb database are briefed below.
Using any of the above mentioned queries, one can reduce the size of the database compared to the original size.
Alternative solution:
The above section indicates that the error is caused due to some sort of issues related to Operating System in processing SQL Server.The operating system error ID and its description are indicated with the error message. This error comes in a combination of OS errors. Because the error is reported from OS you can be troubleshoot it using command prompt.
syntax:
OS-error-number must be replaced with the corresponding error number displayed in the message.
Note: This is a general solution to fix any type of operating system errors.
The Verdict
I hope the above mentioned precautionary methods may help you to handle the SQL Error 17053. This may help you to manage the tempdb database efficiently and thereby avoid the exponential growth of the file contents.
| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords |
|---|---|---|---|---|---|---|---|---|---|
|
MSSQLSERVER_17053 |
MSSQLSERVER_17053 |
MashaMSFT |
mathoma |
pijocoder |
01/17/2023 |
sql |
supportability |
reference |
17053 (Database Engine error) |
MSSQLSERVER_17053
[!INCLUDE SQL Server]
Details
| Attribute | Value |
|---|---|
| Product Name | SQL Server |
| Event ID | 17053 |
| Event Source | MSSQLSERVER |
| Component | SQLEngine |
| Symbolic Name | OS_ERROR |
| Message Text | %ls: Operating system error %ls encountered. |
Explanation
Generic operating system error occurred. The error message wraps a more specific operating system (OS) error, which can be used to help diagnose that failure. Examples would include reads or writes to data or log files that fail, registry read/write operations, or other unexpected Win32 API call failures.
You’ll frequently see this error together with other error messages in the SQL Server error log. The operating system errors are shown with their numeric value, followed by the text message of the error.
User Action
Here are examples of how you may see 17053 error together with other errors. Each example provides ideas on how to approach the specific scenario.
Example with OS error 665
In this case, the underlying OS error 665 indicates a file system limitation has been encountered during file write or read.
Error: 17053, Severity: 16, State: 1.
K:DATAMyDB.MDF_MSSQL_DBCC11: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00031397ce2000 in file 'K:DATAMyDB.MDF_MSSQL_DBCC11'.
Resolution:
If you run into this scenario, follow the steps in this article to resolve:
OS errors 665 and 1450 are reported for SQL Server files
Example with SQL Server error 9001 and underlying OS errors 1117 and 21
In this case, the underlying OS error 1117 indicates the disk device has an error or is physically damaged.
Error: 17053, Severity: 16, State: 1.
SQLServerLogMgr::LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.
Write error during log flush.
Error: 9001, Severity: 21, State: 5.
The log for database 'SQLContoso' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
In this case the underlying OS error is 21, which indicates the disk device is offline and not available for the OS and SQL Server to use.
Error: 17053, Severity: 16, State: 1.
SQLServerLogMgr::LogWriter: Operating system error 21(The device is not ready.) encountered.
Write error during log flush.
Error: 9001, Severity: 21, State: 4.
The log for database 'ContosoDB' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
Resolution:
If you encounter a similar scenario, address the underlying OS error. In this case work with your system administrator and hardware vendor to ensure that the disk device is online, functioning properly and there are no errors and damage reported. In cases like this one, you may have to check the physical integrity of the databases once the disk device is restored by running DBCC CHECKDB. If database damage is reported, restore a last known good database backup.
Example with SQL Server error 9001 and underlying OS errors 170
In this case, the underlying OS error 170 indicates the files on disk are being used or locked by some other program, most commonly a file system filter driver.
Error: 17053, Severity: 16, State: 1.
SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.
Write error during log flush.
Error: 9001, Severity: 21, State: 5.
The log for database 'SQLContoso' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
Resolution:
If you encounter a similar scenario, address the underlying OS error. In this case work with your system administrator to ensure that the database and log files aren’t locked by other programs. Most commonly anti-virus or host protection software, defragmentation software, or backup software that holds locks on the files for a long time may cause this OS error. Make sure to exclude database files from being scanned or used; see How to choose antivirus software to run on computers that are running SQL Server.
In WSFC (clustered) environments, if drives aren’t properly configured on the back end they may lock database files when they aren’t supposed to.
For more information on storage for WSFC, see Failover Clustering hardware solution
and Failover cluster instances with SQL Server on Azure Virtual Machines — Storage
Example with SQL Server error 9002 and underlying OS error 112
In this case, the underlying OS error 112 indicates the disk volume is out of space.
Error: 17053, Severity: 16, State: 1.
L:SQLLOGContoso.LDF: Operating system error 112(There is not enough space on the disk.) encountered.
Error: 9002, Severity: 17, State: 5.
The transaction log for database 'ContosoDb' is full due to 'DATABASE_MIRRORING'.
Error: 5149, Severity: 16, State: 3.
MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file 'L:SQLLOGContoso.LDF'.
Resolution:
If you encounter a similar scenario, address the underlying OS 112 error. In this case work with your system administrator to free up disk space on the device and then attempt to address the full transaction log. For detailed steps on troubleshooting error 9002, see Troubleshoot a Full Transaction Log (SQL Server Error 9002).
This article is contributed. See the original author and article here.
Recently we have fixed an very interesting issue for a customer. ; sharing the details below
ISSUE:
SQL Server generates below error for every one minute.
2021-05-20 08:15:09.96 spid32s Error: 17053, Severity: 16, State: 1.
2021-05-20 08:15:09.96 spid32s UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.
2021-05-20 08:16:10.08 spid24s Error: 17053, Severity: 16, State: 1.
2021-05-20 08:16:10.08 spid24s UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.
Even though there is no impact these messages fill up the error log and are noisy.
Background:
Every 1 minute, SQL wakes up and updates the Process ID and the Uptime in the registry keys
Key Name: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLServer
Name: uptime_pid
Type: REG_DWORD
Data: 0x1a5c
Name: uptime_time_utc
Type: REG_BINARY
Data: 00000000 98 61 19 0e 42 4e d7 01 –
Troubleshooting and solution :
- SQL Server writes the PID and Uptime of SQL process to Registry key for every one minute and this operation is failing with “Access denied” hence the error.
- We provided permission to the SQL Service Account on the required registry key (HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLServer) but it didn’t fix the issue.
- From the process monitor, it has been identified that Office components are interacting with SQL Server while writing the data to registry key.
- We have identified that SQL Server is using MS Office components to import and export data into Excel through BULK Insert queries. So we couldn’t uninstall Office.
- The Office has been upgraded to latest version but still the issue persists
- Upon further investigation, we have identified that Office is using Virtual registry and we need to bypass it.
- For the same we have added (Don’t overwrite it) the required SQL reg key(HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLServer) to the list of exceptions at HKEY_LOCAL_MACHINESOFTWAREMicrosoftOfficeClickToRunREGISTRYMACHINESoftwareMicrosoftAppVSubsystemVirtualRegistry under “passthroughPaths” and this fixed the issue.
Thanks,
Rambabu Yalla
Support Escalation Engineer
SQL Server Team
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
During routine integrity checks one of our Virtual-DBA customers encountered an error 17053 was encountered causing the job performing these tasks to fail. Investigating the problem further this information was also included with the error in the SQL Error Log:
Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
The error is very misleading almost implying that we have violated a file size constraint of the file system. The file system on this server was NTFS and well apparently it has a huge “theoretical” file size limit of 16 Exabyte’s (1 Exabyte = 1,000,000 Terabyte). Given that the server had only 2 TB on the drive that the database files lived at it was not the issue.
The database files accumulated a total of 1.8TB nearly consuming the entire available disk space all on a single volume.
Looking into how DBCC CHECKDB works is where we can find the answer to the problem. In order for DBCC CHECKDB to keep the databases online while it performs its operations is to create a snapshot file that records all of the transactions applied against the database during this time. Furthermore this file is created in the same location as the database data files. And to make matters worse the location of where these snapshot files are created cannot be modified or configured.
Due to the size of the database the check integrity task was taking long periods of time to complete. This server also had a good amount of transactions running on it at any given time thus causing the snapshot file to grow beyond the 2TB limit of the volume.
To fix this a couple ideas come to mind. One way might be to extend the current size of the volume increasing the space available. Another option might be to create new volumes, create a new file group, span data files across the disks, move largest tables to the new file groups which will distribute across all the data files in the file group, and finally shrink the original database files.
It is important to keep those data files in check and not let them get to large. If you notice one getting to big it may be time to add on another data file to your file group. I would recommend keeping at some free and available space on your volumes for these types of overhead processes. How much space will vary depending on several factors to include database size, database use, and how long the integrity check will take to complete.