Fixing SQL Error 17053 In Tempdb.mdf

Table of Contents

• Introduction
• Error 17053 Description
• Alternate Solution
• Conclusion

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. We are quite sure that by the end of this article, users can easily counter the error: 17053, severity: 16, state: 1 issue with ease.

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. Users can manage this with the help of the below steps.

1. From Computer Management select Disk Management option.
2. Select Properties on all the drives that contains the database
3. Select the pane Shadow Copies then Select Settings
4. 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.

1. Trying to create a new DB.
2. While starting the SQL server to perform any activities.
3. 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 operating system error 112 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:

1. Temporary objects that are explicitly created.
2. Tables holding intermediate results from queries.
3. Stores transactions related to snapshot isolation.
4. Other transactions related to online index operations, triggers, etc.

Some tips to improve the performance of the tempdb files are:

By default, the tempdb file is configuration grow automatically as per the need. 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.

• • • Use DBCC SHRINKDATABASE command
    • Use DBCC SHRINKFILE command
    • Use appropriate transact SQL commands

Using any of the above-mentioned queries, one can reduce the size of the database compared to the original size. Moreover, they can reduce the chance of database corruption in SQL server as well.

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:

Users need to replace OS-error-number with the corresponding error number in the message.

Note: This is a general solution to fix all kinds of the operating system errors.

Also Read: Why Backup Database is Terminating Abnormally than Before?

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. Solving the error: 17053, severity: 16, state: 1 is quite easy with the help of the right solutions.