Transact SQL :- sys.dm_os_enumerate_filesystem – Error – 0x8007007a

Background

Still stuck on sys.dm_os_enumerate_filesystem.

Want to touch on a couple of diagnostic steps.

Lineage

Posts along same subject.

  1. Transact SQL :- sys.dm_os_enumerate_filesystem
    Date Published :- 2020-04-23
    Link

Usage

Syntax

SQL


SELECT * 

FROM   sys.dm_os_enumerate_filesystem
(
        [basefolder]
      , [filePattern]

) tblOSEFS

Sample

Sample -01

SQL


declare @folder         nvarchar(max)
declare @filePattern    nvarchar(max)
declare @iMaxNumberofRecords smallint

set @folder='c:\downloads\hibernate.org';

/*
    set @filePattern = '*.zip';
    set @filePattern = '*';
    set @filePattern = '*lib*';
*/

set @filePattern = '*.java';

set @iMaxNumberofRecords = 40

set @iMaxNumberofRecords = 41

--set @iMaxNumberofRecords = 42

SELECT
        top (@iMaxNumberofRecords) *

FROM   sys.dm_os_enumerate_filesystem
    (
          @folder
        , @filePattern
    ) tblOSEFS

Output

output.fileSystem.hirbernate.org.01.20200423.1221PM

Issues

Issue – “Internal error. The string routine in file sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp, line 799 failed with HRESULT 0x8007007a.”

Error Message

Textual


Msg 407, Level 16, State 1, Line 2
Internal error. The string routine in file sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp, line 799 failed with HRESULT 0x8007007a.

Image

error.407.stringRoutineIsInvalid.01.20200423.1226PM

Explanation

The error read:-

Msg 407, Level 16, State 1, Line 2
Internal error. The string routine in file sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp, line 799 failed with HRESULT 0x8007007a.

Interpretation

  1. File Module
    • sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp
      • Line #:- 799
      • Error Number:- 0x8007007a

TroubleShooting

Outline

  1. Upgrade SQL Server
    • Upgrade from v2017/CU15 to v2017/CU20
  2. Dig into the error message

Upgrade

Upgraded Microsoft SQL Server from v2017/CU15 to v2017/CU20.

Dig Into The Error Message

Tools

Outline

  1. Net Help
  2. Microsoft Error Lookup Tool ( Err  )

Net Help

From console/command line, issue Net Help against our decimal number.

Outline
  1. Error Number
    • The error number is 0x8007007a
    • Let us convert the last four digits to decimal
      • Convert 007a in Hex to Decimal get us 122
  2. Call Net helpmsg
    • Pass the decimal equivalence of the last 4 digits

 

Command
Syntax

net helpmsg [errorNumber]

Sample

net helpmsg 122

Output
Image

netHelpMsg.122.01.20200423.0313PM

Textual

C:\>net helpmsg 122

The data area passed to a system call is too small.

Explanation

We see and confirm that our issue is that our allotted buffer is too small.

Tool – Microsoft Error Lookup Tool – Err

Download

A working version of the Microsoft Error Lookup Tool is available at :-

  1. v6.4.5
    • Info
      • Version:- 6.4.5
      • File Name:- Err_6.4.5.exe
      • Date Published:- 10/24/201
    • Link
      Link

 

Command

From console/command line, issue err against the HRESULT error.

Syntax

err [errorNumber]

Sample

Err_6.4.5.exe 0x8007007a

Output
Image

HRESULT.0x8007007a.01.20200423.0407PM

Textual

>Err_6.4.5.exe 0x8007007a
# for hex 0x8007007a / decimal -2147024774
  WER_E_INSUFFICIENT_BUFFER                                      werapi.h
  DRM_E_BUFFERTOOSMALL                                           windowsplayready.h
# as an HRESULT: Severity: FAILURE (1), FACILITY_WIN32 (0x7), Code 0x7a
# for hex 0x7a / decimal 122
  ERROR_INSUFFICIENT_BUFFER                                      winerror.h
# The data area passed to a system call is too small.
# 3 matches found for "0x8007007a"

Explanation

The error message is consistent:-

  1. hex 0x8007007a / decimal -2147024774
  2. WER_E_INSUFFICIENT_BUFFER werapi.h
  3. DRM_E_BUFFERTOOSMALL windowsplayready.h
  4. FACILITY_WIN32 (0x7), Code 0x7a
  5. # for hex 0x7a / decimal 122
  6. ERROR_INSUFFICIENT_BUFFER winerror.h
  7. # The data area passed to a system call is too small.
  8. # 3 matches found for “0x8007007a”

Workaround

Ask for less data.

Don’t shoot or try to accept too much data.

Sample

Outline

  1. Changed number of records from 42 to 41
    • We are using top to limit the number of records returned

SQL

Sample

declare @folder         nvarchar(max)
declare @filePattern    nvarchar(max)
declare @iMaxNumberofRecords smallint

set @folder='c:\downloads\hibernate.org';

/*
    set @filePattern = '*.zip';
    set @filePattern = '*';
    set @filePattern = '*lib*';
*/

set @filePattern = '*.java';

/*
    set @iMaxNumberofRecords = 40
*/

set @iMaxNumberofRecords = 41

/*
    set @iMaxNumberofRecords = 42
*/

SELECT
        top (@iMaxNumberofRecords) *

FROM   sys.dm_os_enumerate_filesystem
    (
          @folder
        , @filePattern
    ) tblOSEFS

Version

Here are the SQL Server Version Numbers that we tested against:-

Failed

v2017

v2017 – CU20

Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) – 14.0.3294.2 (X64)
Mar 13 2020 14:53:45
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 2016 LTSB 10.0 (Build 14393: )

v2017 – CU15

Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) – 14.0.3192.2 (X64)
Jun 15 2019 00:45:05
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 2016 LTSB 10.0 (Build 14393: )

Succeded

v2019

v2019 – CU4

Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) – 15.0.4033.1 (X64)
Mar 14 2020 16:10:35
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Summary

Upgrade your machines.

And, go back and try again.

Keep in mind that upgrading does not fix all problems.

Having said that, the problem is fixed in Microsoft SQL Server v2019, at least in CU4.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s