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.

SQL SERVER - Puzzle - Why does sp_spaceused Show No Values? spaceused-shows-NULL-problem-01-800x577

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:\DATA\InMem_OLTP_data.mdf',size = 30MB)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_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:\Data\InMem_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)

, ,
Previous Post
SQL SERVER Puzzle – Conversion with Date Data Types
Next Post
SQL SERVER – Why We have Audit Trace in DATA Folder? What are These Files?

Related Posts

10 Comments. Leave new

  • i checked the same thing on my server it works i don’t know who to upload its image to your site but honestly peaking its like bellow

    The whole Database (Unalocated Spcae (1.03 MB)) (Unused 3488 KB),
    while the egproducts Table Unused Space is 64 KB

    is it a bug as well ??

    Reply
  • Well you answered it Pinal. This is a memory optimized table so it resides in memory and not in storage.

    Reply
  • tx2cagirl (Michellea David)
    February 14, 2017 7:02 am

    Yes, I agree with Anil. There is no disk space used, yet.

    Reply
  • Hello Pinal,
    Supose if we restart the server then the memory in sql server will get cleared i.e zero.so if we run the select * from products then otput must be empty.so this will be the wrong scenario .I think the In-memory tables will have a copy on disk so that when we run the select statements it will fetch the data from disk. Am is right or wrong?

    Reply
  • Elaena Bakman
    March 16, 2017 5:22 am

    With having MEMORY_OPTIMIZED = ON, even thought you have DURABILITY = SCHEMA_AND_DATA nothing has been written to disk yet. …just a theory, I have not really spent much time with In Memory tables.

    Reply

Leave a Reply

Menu