SQL SERVER – Puzzle – Why does sp_spaceused Show No Values?

When you see something not behaving the way normal way, either it must be magic or in software terms it is an error or bug. This blog was inspired by someone who caught me unaware with this challenge that it took me by surprise. I didn’t know how to solve it and it took me close to 10 mins before realizing what was happening behind the scenes. Let us see the puzzle about Why does sp_spaceused Show No Values?

I have not done any sort of trick in the below screen shot. It is real table and the output is real too.

Did you see something strange here? Even though the table has values, the sp_spaceused is not recording any values. When I first saw this, it surely looked like some bug.

Now if I had to ask for your help to help me, what would be your response? Why is the output showing like this?

Have you ever encountered such strange behavior with SQL Server? What would you do when such things happen? If this is a bug, I highly recommend you to please go to the Connect site and do a search. On a side note, do let me know if you ever visited this site. Do read about enhancement requests on this site, this is the best way to know what doesn’t work and what are people working on.

Hint: Coming back to the puzzle above. Do you know the various types of tables you can create? I think I am giving away the answer already.

Here is the code to build this example.

USE MASTER
GO
DROP DATABASE IF EXISTS [InMem_OLTP]
GO
-- Create the database
CREATE DATABASE [InMem_OLTP] ON PRIMARY
( NAME = N'InMem_OLTP_data', FILENAME = N'C:DATAInMem_OLTP_data.mdf',size = 30MB)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:DATAInMem_OLTP_log.ldf',size = 10MB)
GO
-- Add Inmemory Filegroup
USE InMem_OLTP
GO
ALTER DATABASE InMem_OLTP
ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE InMem_OLTP
ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:DataInMem_OLTP_mopt')
TO FILEGROUP [InMem_OLTP_InMemory]
-- Let us create the table srtuctures
USE InMem_OLTP
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY NONCLUSTERED HASH (ProductID) WITH (BUCKET_COUNT = 10000),
ProductName VARCHAR(25)
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

INSERT INTO Products VALUES (2, 'SQLAuthority');
 GO
 SELECT * FROM Products
 GO
 sp_spaceused Products
 GO
 USE master
 GO
 DROP DATABASE [InMem_OLTP]
 GO

Do drop your guesses and understanding via comments below.

Reference: Pinal Dave (https://blog.sqlauthority.com)