I am struggling to find the reason of the arithmetic overflow. why is it happening?
Most likely the metadata is returning some unexpected values that your code cannot handle. For example:
-- Example values returned from sysfiles and FILEPROPERTY
DECLARE
@size integer = 1,
@spaceused integer = 10000;
-- The essence of the code in the question
SELECT
CAST
(
100 *
(
CAST
(
(
(@size/128.0 - @spaceused/128.0)/(@size/128.0)
)
AS decimal(5,2)
)
)
AS varchar(8)
) + '' AS FreeSpacePct;
...returns the error mentioned in the question, because the computed (negative!) value will not fit in decimal(5,2)
.
There are reasons why size might be reported as much lower than space used, including tempdb file growths, filestream files, bugs in older versions of SQL Server...too many to list. You could/should code defensively against this possibility (and also for offline/defunct files...and so on).
The question is tagged SQL Server 2014, so there's no need to use the deprecated sys.sysfiles
view (for backward compatibility with SQL Server 2000):
I might write this query as:
SELECT
DatabaseName = DB_NAME(),
[FileName] = DF.name,
FileType = DF.type_desc,
SizeMB = STR(DF.size * Factor.PagesToMB, 10, 2),
SpaceUsedMB = STR(FP.SpaceUsed * Factor.PagesToMB, 10, 2),
FreeSpaceMB = STR(FS.FreeSpace * Factor.PagesToMB, 10, 2),
FreeSpacePct = STR(Factor.ToPct * FS.FreeSpace / DF.size, 7, 4)
FROM sys.database_files AS DF
CROSS APPLY (SELECT FILEPROPERTY(DF.name, 'SpaceUsed')) AS FP (SpaceUsed)
CROSS APPLY (SELECT DF.size - FP.SpaceUsed) AS FS (FreeSpace)
CROSS JOIN (SELECT 8e0 / 1024e0, 1e2) AS Factor (PagesToMB, ToPct);
Main advantages:
A dynamic SQL version (to collect information for all databases):
DECLARE @SQL nvarchar(2000);
SET @SQL = N'
USE ?;
SELECT
DatabaseName = DB_NAME(),
[FileName] = DF.name,
FileType = DF.type_desc,
SizeMB = STR(DF.size * Factor.PagesToMB, 10, 2),
SpaceUsedMB = STR(FP.SpaceUsed * Factor.PagesToMB, 10, 2),
FreeSpaceMB = STR(FS.FreeSpace * Factor.PagesToMB, 10, 2),
FreeSpacePct = STR(Factor.ToPct * FS.FreeSpace / DF.size, 7, 4)
FROM sys.database_files AS DF
CROSS APPLY (SELECT FILEPROPERTY(DF.name, ''SpaceUsed'')) AS FP (SpaceUsed)
CROSS APPLY (SELECT DF.size - FP.SpaceUsed) AS FS (FreeSpace)
CROSS JOIN (SELECT 8e0 / 1024e0, 1e2) AS Factor (PagesToMB, ToPct);
';
DECLARE @Results AS table
(
DatabaseName sysname NOT NULL,
[FileName] sysname NOT NULL,
FileType nvarchar(60) NOT NULL,
SizeMB char(10) NULL,
SpaceUsedMB char(10) NULL,
FreeSpaceMB char(10) NULL,
FreeSpacePct char(7) NULL
);
INSERT @Results
EXECUTE sys.sp_MSforeachdb
@command1 = @SQL;
SELECT R.*
FROM @Results AS R
ORDER BY R.DatabaseName; -- Or whatever
Usual caveats about using sp_MSforeachdb
.
The TRY...CATCH
block is causing the execution of the stored procedure to terminate before it has a chance to return the values in #TEMP.
Try this stored proc:
IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS
BEGIN
CREATE TABLE #TEMP
(
ID INT NOT NULL
);
INSERT INTO #TEMP
VALUES (1);
INSERT INTO #TEMP
VALUES (1/0);
PRINT (N'Run the SELECT');
SELECT ID
FROM #TEMP;
END
GO
Running it, like this, without a TRY...CATCH
, allows all the statements in the proc to run:
EXEC dbo.SomeProc;
The output:
Msg 8134, Level 16, State 1, Procedure SomeProc, Line 12
Divide by zero error encountered.
The statement has been terminated.
Run the SELECT
However, if you run it inside a TRY...CATCH
:
BEGIN TRY
EXEC dbo.SomeProc;
END TRY
BEGIN CATCH
PRINT (ERROR_MESSAGE());
END CATCH
You see only the error message:
Divide by zero error encountered.
The PRINT (N'Run the SELECT');
never runs, and indeed the SELECT ID FROM #TEMP;
never runs either. Hence no rows are returned, and nothing can be inserted into your #TEMPTABLE
From the MSDN documentation on TRY...CATCH:
If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block.
As always, the devil is in the details. TRY...CATCH
always aborts the code inside the BEGIN TRY...END TRY
code block if any error over severity 10 occurs that does not close the database connection. Execution is immediately passed into the BEGIN CATCH...END CATCH
block, even if this means aborting code in a stored proc.
Be aware that if an error occurs on any row the entire insert will not happen. The only reason you're seeing this error, and a row being inserted, is because you have two insert statements, one that runs to completion, and one that throws an error. Take for instance:
IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS
BEGIN
CREATE TABLE #TEMP
(
ID INT NOT NULL
);
INSERT INTO #TEMP
VALUES (1);
INSERT INTO #TEMP
VALUES (1)
, (2)
, (3)
, (4)
, (1/0);
PRINT (N'Run the SELECT');
SELECT ID
FROM #TEMP;
END
GO
Using this stored proc, either inside a TRY...CATCH
or with no TRY...CATCH
block, will only result in a single row being inserted into the #TEMPTABLE
- none of the rows in the 2nd insert will be present in the output.
Best Answer
That looks more like a
hobt_id
than anobject_id
. Try:Also, I recommend opening the .xdl in SentryOne Plan Explorer, which should present you with friendly object names instead of long, indecipherable ids. I explain the deadlock analysis functionality here.