November 6, 2003 at 10:51 am
I have my primary file on a disk separate from several other file groups. In Enterprise manager, it is reporting the space used as 5Gb or so. When I do a backup and then restore this db to another server, the primary file group shows about 2Gb space used in EM.
I run update stats on a weekly basis and it doesn't seem to change the reported value. I also dbcc reindex on a bi-weekly basis. Any thoughts on how I can identify why this is happening and how I can resolve it?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
November 6, 2003 at 11:46 am
You should run sp_spaceused @updateusage = true or DBCC UPDATEUSAGE and go back EM to check.
November 6, 2003 at 11:52 am
When I wrote that I run update stats, dbcc updateusage is what I meant to say. Upon further investigation, it appears that some of my tables aren't getting re indexed as I thought they were.
However, this doesn't answer the question in my mind of the space being different when I restore to a different server.
In a probably unrelated issue, in sysindexes I see an index named the same as my table and then an index with a lower case t prefix and then the name of the table. What do these represent?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
November 6, 2003 at 12:25 pm
Run dbcc dbreindex and sp_spaceused @updateusage = true to both databases and compare the result back to see there is still a difference.
November 7, 2003 at 9:20 am
check the server's default index fill factor, and the actual index fill factor you used to create indexes. lower number consumes more space.
November 10, 2003 at 10:21 am
Summary of what I have done so far:
DBCC CHECKDB --Found no errors
DBCC DBREINDEX --I think checkdb does this too but just wanted to cover everything.
DBCC UPDATEUSAGE --Ran after hours and I didn't capture any results
The file group is still showing much higher than when I restore it to another server.
As for the fill factor, I have created all of the indexes with the sql server default of 0. Could this be an issue?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
November 11, 2003 at 1:08 am
Can you post the results of
DBCC SHOWFILESTATS
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 11, 2003 at 9:28 am
The Production primary File Group:
FileIDFGrpTotalExtentsUsed Extents
11109408 91567
The Restored primary file group on dif server:
FileIDFGrpTotalExtentsUsed Extents
11109408 46945
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
November 12, 2003 at 1:09 am
Well, at least your results are consistent to what you have noticed. Same space allocated, but roughly half the space used.
All I would do has been mentioned above, and I don't want to give a WAG.
So maybe someone else has an idea?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 13, 2003 at 9:26 am
Thanks for the candid response. One thought I have is to do a comparison on the system tables, sysindexes I presume to see where the extra used space is coming from. Any thoughts on what would be the best methodology to do this?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply