July 23, 2009 at 5:09 am
Hi,
Does anyone have any idea what might be causing this?
I needed to recreate a database in a different collation to the original. The original was in SQL_Latin1_General_CP1_CI_AS and the replacement is Latin1_General_CI_AS
The way I went about this was to first make a copy of the schema through SQL Compare, taking care to ensure everything from message queues to users went across in addition to the usual tables and stored procedures. The schema looks fine now, bar a few objects marked for deletion in the source database that there was no need to copy across.
I then used SQL Data Compare to generate scripts to sync the information across until all destination tables had the same information in as their source counterparts. So far so good.
There were some issues in that the source data had some information in it that, when inserted, caused triggers to fire and for messages to be placed in the newly created message queues on the destination database. This caused a log table fill up in a big way and balooned the size of the destination database to almost 4GB from its standard size of about 120MB. I cleared this table and the database is now 'only' about 600MB but that is still about 5 times too big compared to the original.
The problem is, when i look at the total amount of space all the tables are reported to be using, the numbers tally fairly consistently with the source database. However, when looking at the space taken up by the database as a whole and when using the system procedure DBCC showfilestats (the number of extents shown below), the database is actually about 600MB
Old DB
FileidFileGroupTotalExtentsUsedExtents
11160001940
New DB
FileidFileGroupTotalExtentsUsedExtents
11160009854
I can't find anything that stands out as consuming the additional space. The volume of data is the same bar any new records added to the source DB as and when it is used, and the corresponding space taken up on the disk is similar on each table, so where is all the extra space being used?
Is there any way to break down the amount of space used in a DB besides what tables and indexes use up? Or is everything else negligible anyway?
This isn't too urgent because the current DB is still working, and even if i never resolve this, the job is done, however it bothers me as to why this should be so.
Does anyone have any ideas?
Thanks
Paul
July 23, 2009 at 7:40 am
It's quite probably that whilst you were doing all of those inserts, indexes became heavily fragmented and, quite possibly, caused lots of page splits.
I'd go through the exercise of re-indexing all of the tables, which would be good practice anyway, and then redo the size comparison.
July 23, 2009 at 7:52 am
Would that be by doing either this
ALTER INDEX ON REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
or this
ALTER INDEX ON REORGANIZE WITH ( LOB_COMPACTION = ON )
GO
If so, i've already tried all that. It did help some, it brought it down a few MB but according to the SQL Report that displays the size of indexes on all the tables, my indexes are comparablely sized or smaller than that of the source DB.
July 23, 2009 at 8:08 am
The other thing you can do is run dbcc updateusage(0), which corrects rowcount inaccuracies, which can cause wrong data to be presented when you run the space usage procedures.
I'm not entirely certain but I don't think that differences between collations make a difference to raw data storage (but I might be wrong).
July 23, 2009 at 8:19 am
OK, I gave that a try and I got one change come back but that was it. I did wonder about the different collation being the cause but even if it was a factor surely it wouldn't increase storage requirements by a factor of 5?
I have also tried running DBCC SHOWCONTIG and that did not throw up anything alarming. I had wondered if lots of extents were only partially filled, taking up additional space, but I don't see any evidence of that. If anything the structure is better than the source DB.
Thanks
Paul
July 23, 2009 at 8:28 am
Gotta admit, I can't think of anything obvious. It doesn't sound like it's an issue but, like you say, it'd be nice to know where the space has come from.
You might want to consider running dbcc updateusage(0) on the source database - I suppose that database might be incorrectly reporting its space allocation.
The other thing you can try is to run the following query on both databases. This will tell you the page count for each table in the database and might flag up any glaring differences between the two databases.
select distinct object_name(object_id) table_name, page_count
from sys.dm_db_index_physical_stats(db_id(), null, null, null, null)
order by table_name
July 23, 2009 at 9:12 am
I tried DBCC UPDATEUSAGE(0) and that did nothing to the source table, so I guess that was correct. I also tried your query and total pages used comes in as follows
Source DB: 13797
New DB: 10825
Which frankly just confuses things even more since my replacement should be smaller rather than bigger. Are there any other objects in the DB that consume space that aren't accounted for when looking at tables and indexes? For instance if, hypothetically, a stored procedure was enormous, would its size be counted in a query such as the one you provided? I'm just trying to think outside the box a little about what could go uncounted. Perhaps if the message queue stopped for whatever reason, though i'm sure it hasn't, could that also be missed from a count?
Thanks
Paul
July 23, 2009 at 9:57 am
Run the script on the link below to see how much used and free space you have in each database file, and to see how much space is used by each database object.
Run it in each database and then compare the differences.
Script to analyze table space usage
July 23, 2009 at 10:30 am
Michael,
Thanks for your reply. I had already seen your code though I hadn't run it until now. Here are the results. I won't post my entire table listing but will post the sum of the relevant columns
Old DB
FileSizeMBUsedSpaceMBUnusedSpaceMBTypeDBFileName
214.1312.32201.8LogTMS_log
1000121.25878.75DataTMS
1214.13133.571080.55*** Total for all files ***
Sum of Reserved Column114104
Sum of Data Column100160
Sum of Index Column12032
Sum of Used MB111.4298
New DB
FileSizeMBUsedSpaceMBUnusedSpaceMBTypeDBFileName
4.132.271.86LogPaulTMS_log
1000615.88384.13DataPaulTMS
1004.13618.15385.99*** Total for all files ***
Sum of Reserved Column89920
Sum of Data Column77328
Sum of Index Column11488
Sum of Used MB87.8121
It pretty much confirms what I had seen in other places. The database believes PaulTMS to be 600+ MB big, but the sum of the data within it is less than 100MB
Thanks
Paul
July 23, 2009 at 12:41 pm
Do you have any tables that do not have a clustered index? On tables where there is not a clustered index, the heap can use a lot more space. If you find a table like that - add a clustered index and then remove it. That would reorganize the storage used by that table.
Just a thought...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 24, 2009 at 3:08 am
Jeffrey,
I did as you suggested where tables had any contents and it made no difference. I'm going to try copying again from the second DB and check the space taken up after each table migration. I might be able to pin it down that way
Thanks
Paul
July 24, 2009 at 4:45 am
I have tried going step by step through the database creation process, recording the number of extends used at each step. Everything seemed ok until i got to some of the larger tables having their data migrated, however it was not an issue with all tables that sort of size.
Two tables in particular caused significant jumps far in excess of their relative size. One is listed within Michael's script as being 6.0859MB, but the cost in extents is 10 times that, at 516 extents or 64.5 MB. The second is even more excessive, being listed within the script as being 4.3906MB big, but using 1239 extents, or 154.875MB of space after being inserted.
I have one final table which is too large to move in anything other than an overnight job. if this were to exhibit similar problems i could see it matching or exceeding the size of my first attempt.
On that basis, any ideas as to what is causing this to happen? It would seem that the database is inflating beyong the requirements of the table. Could this be in use somewhere uncounted? Is it dead space?
July 27, 2009 at 7:37 am
Sorry to bump this up but does no one else have any ideas with regard to this?
July 29, 2009 at 11:43 am
What is the file allocation size of the underlying storage blocks?
Are they different from where they were backed up from to where you are restoring?
I once moved a volume that was stored in 4K chunks to a SAN that was formatted for graphic files at 16K chunks....took up way more room.
??? Just throwing that out there. ???
July 30, 2009 at 8:29 am
Hi Rothj,
The database is stored in the same location as the old one, they both exist in the same instance, just with different collations. I think on that basis the underlying structure would be the same
Thanks
Paul
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply