XML FeedsIf 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.