Copy of database is much larger than the original

  • paul.goldstraw

    SSCrazy

    Points: 2626

    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

    Fileid FileGroup TotalExtents UsedExtents

    1 1 16000 1940

    New DB

    Fileid FileGroup TotalExtents UsedExtents

    1 1 16000 9854

    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

  • SQLZ

    SSChampion

    Points: 12872

    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.

  • paul.goldstraw

    SSCrazy

    Points: 2626

    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.

  • SQLZ

    SSChampion

    Points: 12872

    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).

  • paul.goldstraw

    SSCrazy

    Points: 2626

    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

  • SQLZ

    SSChampion

    Points: 12872

    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

  • paul.goldstraw

    SSCrazy

    Points: 2626

    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

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    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

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

  • paul.goldstraw

    SSCrazy

    Points: 2626

    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

    FileSizeMB UsedSpaceMB UnusedSpaceMB Type DBFileName

    214.13 12.32 201.8 Log TMS_log

    1000 121.25 878.75 Data TMS

    1214.13 133.57 1080.55 *** Total for all files ***

    Sum of Reserved Column 114104

    Sum of Data Column 100160

    Sum of Index Column 12032

    Sum of Used MB 111.4298

    New DB

    FileSizeMB UsedSpaceMB UnusedSpaceMB Type DBFileName

    4.13 2.27 1.86 Log PaulTMS_log

    1000 615.88 384.13 Data PaulTMS

    1004.13 618.15 385.99 *** Total for all files ***

    Sum of Reserved Column 89920

    Sum of Data Column 77328

    Sum of Index Column 11488

    Sum of Used MB 87.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

  • Jeffrey Williams

    SSC Guru

    Points: 88652

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • paul.goldstraw

    SSCrazy

    Points: 2626

    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

  • paul.goldstraw

    SSCrazy

    Points: 2626

    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?

  • paul.goldstraw

    SSCrazy

    Points: 2626

    Sorry to bump this up but does no one else have any ideas with regard to this?

  • rothj

    SSCrazy

    Points: 2796

    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. ???

  • paul.goldstraw

    SSCrazy

    Points: 2626

    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 17 total)

You must be logged in to reply to this topic. Login to reply