If you are using snapshots be aware that running sp_helpdb reports wrong files. Let’s take a look by running some code

First create a test database

use master
go

CREATE DATABASE [test] ON  PRIMARY 
( NAME = N'test', FILENAME = N'C:test.mdf'  )
 LOG ON 
( NAME = N'test_log', FILENAME = N'C:test_log.LDF' )

GO

Now let’s create a table and populate it with some data

USE test
go


create table TestTable (id int not null,somecol char(100) default 'a')
go

insert TestTable(id)
select row_number() over (order by s1.id)
from sysobjects s1
cross join sysobjects s2

go

Now create the snapshot database

use master
go

    
    CREATE DATABASE TestSnapshot ON  
( NAME = N'test', FILENAME = N'C:testss.mdf' )
  AS SNAPSHOT of Test;
GO

Now run a count for that table against the test database and against the snapshot

select COUNT(*) from TestSnapshot..TestTable
select COUNT(*) from Test..TestTable

Output
--------------
3025
3025

As you can see the count is the same

Now run sp_helpdb and look at the sizes, we will come back to this output later

sp_helpdb 'Test'

Output
---------------------------------------------------------------
test        1   C:test.mdf      PRIMARY 2304 KB
test_log    2   C:test_log.LDF          NULL    1792 KB

Let’s add some more data to our table

use Test
go

declare @id int =(select MAX(id)
					from  Test..TestTable)

insert Test..TestTable(id)
select row_number() over (order by s1.id) + @id
from sysobjects s1
cross join sysobjects s2
cross join sysobjects s3

Now if we run the count we will see that the count for the table in the Test database has increased (as expected)

select COUNT(*) from TestSnapshot..TestTable
select COUNT(*) from Test..TestTable

Output
-------------
3025
169400

Okay now it is time to run sp_helpdb again

sp_helpdb 'Test'

Output
---------------------------------------------------------------
test        1   C:test.mdf      PRIMARY 20736 KB
test_log    2   C:test_log.LDF          NULL    69760 KB

sp_helpdb 'TestSnapshot'

Output
---------------------------------------------------------------
test        1   C:test.mdf      PRIMARY 2304 KB
test_log    2   C:test_log.LDF          NULL    1792 KB

Do you see that? When you run sp_helpdb against the snapshot now it reports what we had originally for the Test database when we ran sp_helpdb before adding rows to the table

Now run this query

select db_name(dbid) as DB_Name,name,filename,size * 8 from master..sysaltfiles
where dbid in (db_id('Test'),db_id('TestSnapshot'))

Output
---------------------------------------------------------------
DB_Name         name        filename    size
test            test        C:test.mdf  20736
test            test_log    C:test_log.LDF  69760
TestSnapshot            test        C:testss.mdf    2304

As you can see, this reports the correct files and sizes for both Test and the snapshot.

So where is the problem? The problem is in the sysfiles table, it has the wrong filename

Run these two queries to verify that

select size * 8 as Size,filename from Test..sysfiles
select size * 8 as Size,filename from TestSnapshot..sysfiles

Output
----------------------------
Size        filename
20736       C:test.mdf
69760       C:test_log.LDF

Size        filename
2304        C:test.mdf
1792        C:test_log.LDF

sp_helpdb actually calls the sys.sp_helpfile proc to get the size and file name

run this and you will see that it is the second result set of sp_helpdb

exec Test.sys.sp_helpfile
exec TestSnapshot.sys.sp_helpfile

Inside the sys.sp_helpfile proc you will find the following code

select  name,  fileid, filename,  
 filegroup = filegroup_name(groupid),  
 'size' = convert(nvarchar(15), convert (bigint, size) * 8) + N' KB',  
 'maxsize' = (case maxsize when -1 then N'Unlimited'  
   else  
   convert(nvarchar(15), convert (bigint, maxsize) * 8) + N' KB' end),  
 'growth' = (case status & 0x100000 when 0x100000 then  
  convert(nvarchar(15), growth) + N'%'  
  else  
  convert(nvarchar(15), convert (bigint, growth) * 8) + N' KB' end),  
 'usage' = (case status & 0x40 when 0x40 then 'log only' else 'data only' end)  
 from sysfiles  
 

As you can see it uses the sysfiles table

If you want to see what the code for sp_helpdb and sys.sp_helpfile looks like execute the following

exec sp_helptext 'sp_helpdb'
exec sp_helptext 'sys.sp_helpfile'

So what do you think, is this a bug?

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum