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

T-SQL
1
2
3
4
5
6
7
8
9
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
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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
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

T-SQL
1
2
3
4
5
6
7
8
use master
go
 
    
    CREATE DATABASE TestSnapshot ON  
( NAME = N'test', FILENAME = N'C:testss.mdf' )
  AS SNAPSHOT of Test;
GO
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

T-SQL
1
2
select COUNT(*) from TestSnapshot..TestTable
select COUNT(*) from Test..TestTable
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

T-SQL
1
sp_helpdb 'Test'
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
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
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)

T-SQL
1
2
select COUNT(*) from TestSnapshot..TestTable
select COUNT(*) from Test..TestTable
select COUNT(*) from TestSnapshot..TestTable
select COUNT(*) from Test..TestTable
Output
-------------
3025
169400

Okay now it is time to run sp_helpdb again

T-SQL
1
sp_helpdb 'Test'
sp_helpdb 'Test'
Output
---------------------------------------------------------------
test		1	C:test.mdf		PRIMARY	20736 KB
test_log	2	C:test_log.LDF	        NULL	69760 KB
T-SQL
1
sp_helpdb 'TestSnapshot'
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

T-SQL
1
2
select db_name(dbid) as DB_Name,name,filename,size * 8 from master..sysaltfiles
where dbid in (db_id('Test'),db_id('TestSnapshot'))
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

T-SQL
1
2
select size * 8 as Size,filename from Test..sysfiles
select size * 8 as Size,filename from TestSnapshot..sysfiles
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

T-SQL
1
2
exec Test.sys.sp_helpfile
exec TestSnapshot.sys.sp_helpfile
exec Test.sys.sp_helpfile
exec TestSnapshot.sys.sp_helpfile

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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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  
 
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

T-SQL
1
2
exec sp_helptext 'sp_helpdb'
exec sp_helptext 'sys.sp_helpfile'
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