Ошибка 17053 серьезность 16 состояние 1

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.

A user was instructed by their software support to upgrade a program and install SQLExpress as part of the installation process.

Since that time, the service has been able to start, citing error 17053, which appears to be an authentication issue.

Here is the error log:

2011-01-11 13:17:45.50 Server      Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) 
    Feb  9 2007 22:47:07 
    Copyright (c) 1988-2005 Microsoft Corporation
    Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2011-01-11 13:17:45.50 Server      (c) 2005 Microsoft Corporation.
2011-01-11 13:17:45.50 Server      All rights reserved.
2011-01-11 13:17:45.50 Server      Server process ID is 3332.
2011-01-11 13:17:45.50 Server      Authentication mode is WINDOWS-ONLY.
2011-01-11 13:17:45.50 Server      Logging SQL Server messages in file 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG'.
2011-01-11 13:17:45.52 Server      This instance of SQL Server last reported using a process ID of 2332 at 11/10/2010 2:15:24 PM (local) 11/10/2010 7:15:24 PM (UTC). This is an informational message only; no user action is required.
2011-01-11 13:17:45.52 Server      Error: 17053, Severity: 16, State: 1.
2011-01-11 13:17:45.52 Server      UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.
2011-01-11 13:17:45.52 Server      Registry startup parameters:
2011-01-11 13:17:45.52 Server        -d c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf
2011-01-11 13:17:45.52 Server        -e c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
2011-01-11 13:17:45.52 Server        -l c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
2011-01-11 13:17:45.52 Server      Error: 17113, Severity: 16, State: 1.
2011-01-11 13:17:45.52 Server      Error 3(The system cannot find the path specified.) occurred while opening file 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
2011-01-11 13:17:45.52 Server      Error: 17053, Severity: 16, State: 1.
2011-01-11 13:17:45.52 Server      UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered. 4 Server      Error: 17053, Severity: 16, State: 1.
2011-01-11 13:08:21.34 Server      UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered. 12:47:20.85 spid5s      SQL Trace ID 1 was started by login "sa".
2011-01-11 12:47:20.90 spid5s      Starting up database 'mssqlsystemresource'.
2011-01-11 12:47:20.93 spid5s      The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2011-01-11 12:47:21.21 spid5s      Error: 15466, Severity: 16, State: 1.
2011-01-11 12:47:21.21 spid5s      An error occurred during decryption.
2011-01-11 12:47:21.38 spid8s      Starting up database 'model'.
2011-01-11 12:47:21.38 Server      Error: 17182, Severity: 16, State: 1.
2011-01-11 12:47:21.38 Server      TDSSNIClient initialization failed with error 0x5, status code 0x90.
2011-01-11 12:47:21.38 Server      Error: 17182, Severity: 16, State: 1.
2011-01-11 12:47:21.38 Server      TDSSNIClient initialization failed with error 0x5, status code 0x1.
2011-01-11 12:47:21.38 Server      Error: 17826, Severity: 18, State: 3.
2011-01-11 12:47:21.38 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2011-01-11 12:47:21.38 Server      Error: 17120, Severity: 16, State: 1.
2011-01-11 12:47:21.38 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

One lead I had was to change the SQL logon account from «Network Service» to «Local System».

Unfortunately, that is resulting in the error message The Security ID Structure is Invalid [0x80070539]

Any help either uninstalling or getting SQLExpress running would be fantastic.