search

marcingminski / sqlwatch

SQL Server Performance Monitor
https://docs.sqlwatch.io
Other
436 stars 171 forks source link

SQLWATCH 4.3: usp_sqlwatch_logger_disk_utilisation: sp_spaceused returns non-numeric data #451

Open kekcjkee opened 2 years ago

kekcjkee commented 2 years ago

Describe the bug

insert into @spaceused
    exec [dbo].[usp_sqlwatch_internal_foreachdb] @command = 'use [?]; exec sp_spaceused @oneresultset = 1;'
        , @snapshot_type_id = @snapshot_type_id
        , @calling_proc_id = @@PROCID
        , @databases = @databases

The script for my one database returns resultset which contains nvarchar symbols "*". During execution procedure "usp_sqlwatch_internal_foreachdb" returns the error:

Msg 8114, Level 16, State 5, Procedure usp_sqlwatch_logger_disk_utilisation, Line 215 [Batch Start Line 0]
Error converting data type varchar to numeric.

I think it depends on size of a database. In my case it is 30T. (Yes, it is test env :) )

Screenshots image image

Windows Server (please complete the following information):

SQL Server (please complete the following information):

SQL Server Management Studio (SSMS -> about -> copy info): SQL Server Management Studio 15.0.18410.0 SQL Server Management Objects (SMO) 16.100.47008.0+9f71e8549924d85d66afcca2b9f45a33061faa1b Microsoft Analysis Services Client Tools 15.0.19750.0 Microsoft Data Access Components (MDAC) 10.0.20348.1 Microsoft MSXML 3.0 6.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 10.0.20348

SQLWATCH version (from DACPAC or from sysinstances)

marcingminski commented 2 years ago

Hi, Can you help me fix this? I don't have 30TB db in my test setup :). What do you think the problem is?

kekcjkee commented 1 year ago

Hi, Can you help me fix this? I don't have 30TB db in my test setup :). What do you think the problem is?

Hi, I recently left a company where I managed a 30TB database. Initially, I thought that sp_spaceused utilized an int variable, but that turned out to be incorrect. I'm not sure why it doesn't work.