1

Topic: Difference in size

There is a DB version sql 2005, received in a type bak a file. And the version lifted from txt. After a table raising are indexed.
The surface comparing (count of lines and select * from db1. table except select * from db2. table shows that bases identical.
Type fields txt are not compared in any way.
After not which handling the received results in outswapping also are identical.
After  both bases bak files have the size 8 and 5 .
At the expense of what there can be a difference in sizes?

2

Re: Difference in size

AVSONE wrote:

And the version lifted from txt.
...
Type fields txt are not compared in any way.

What else txt?

3

Re: Difference in size

awesome smile

4

Re: Difference in size

Yasha123;
Type fields text. The second basis rose from the text file

5

Re: Difference in size

AVSONE wrote:

the Second basis rose from the text file

What means "rose from the text file"?
From  to me it is clear, it restore database... from disk =....
From the text file as looks ?

6

Re: Difference in size

AVSONE wrote:

After  both bases bak files have the size 8 and 5 .

from what you there that 2 bases which you then  lifted not important if as a result at you.
1. After all yours  with these bases and to them  their sizes what (the sizes of bases and their logs)
2.  then as, ?

7

Re: Difference in size

wrote:

from what you there that 2 bases which you then  lifted not important if as a result at you.

Why it is unimportant?
Can "from the text file" it is a script and with  the data.
Also that in these , we do not know.
At it there .
Here can they and were not transferred by scripts.
Number of lines , the same, well and the data?
Tables should be compared through sp_spaceused at least.
Whether and that is not enough, half of fields is empty, and number of lines same

8

Re: Difference in size

AVSONE wrote:

Yasha123;
Type fields text. The second basis rose from the text file

Type TEXT (NTEXT, IMAGE) - quite specific . Which that separately is stored concerning other data types.
With a source "Text file" the data type "TEXT" is connected approximately as the square root to a felt hat.
And in bak what data types?

9

Re: Difference in size

AVSONE;
For db1, db2

SELECT OBJECT_NAME (sp. [object_id]), [total_pages] = SUM (sau. [total_pages])
FROM sys.allocation_units sau with (nolock)
INNER JOIN sys.partitions sp with (nolock)
ON (sau. [type] in (1,3) AND sau. [container_id] = sp. [hobt_id]) OR (sau. [type] in (2) AND sau. [container_id] = sp. [partition_id])
INNER JOIN sys.objects so with (nolock)
ON so. [object_id] = sp. [object_id] AND so. [is_ms_shipped] =0
GROUP BY sp. [object_id]
ORDER BY SUM (sau. [total_pages]) desc

10

Re: Difference in size

Yasha123 wrote:

it is passed...
What means "rose from the text file"?
From  to me it is clear, it restore database... from disk =....
From the text file as looks ?

create db, create table, bulk insert, index.... Approximately so. The vendor of bases declares that their script and text versions yield legitimate result. That data should be identical lifted of binary . Therefore sql 2005 strike off the list supported versions. There is only a text version.

11

Re: Difference in size

wrote:

it is passed...
From what you there that 2 bases which you then  lifted not important if as a result at you.
1. After all yours  with these bases and to them  their sizes what (the sizes of bases and their logs)
2.  then as, ?

1 - did not look.
2. - yes.

12

Re: Difference in size

AVSONE wrote:

create db, create table, bulk insert, index....

It is called "transferred basis (structure) scripts".
And the data as transferred?
bulk insert - the data flooded from files?
Studio not  beams, it normal  .
And files as flooded, with formats-files?
  on transfer of texts

13

Re: Difference in size

These surrendered to you . Compare to start basis. Files, tables, records, the sizes, structure at last... The Difference will be visible at once.

14

Re: Difference in size

Amount of pages a dataful miscellaneous in these a DB.
fhil already gave a script for check.

15

Re: Difference in size

fhil wrote:

AVSONE;
For db1, db2

SELECT OBJECT_NAME (sp. [object_id]), [total_pages] = SUM (sau. [total_pages])
FROM sys.allocation_units sau with (nolock)
INNER JOIN sys.partitions sp with (nolock)
ON (sau. [type] in (1,3) AND sau. [container_id] = sp. [hobt_id]) OR (sau. [type] in (2) AND sau. [container_id] = sp. [partition_id])
INNER JOIN sys.objects so with (nolock)
ON so. [object_id] = sp. [object_id] AND so. [is_ms_shipped] =0
GROUP BY sp. [object_id]
ORDER BY SUM (sau. [total_pages]) desc
 table1 222505 380002
table2 136367 187967
table3 68087 86602
table4 19815 23510
table5 14818 15282
table6 5861 5833
table7 1109 3268
table8 787 513
table9 17 17 

Thanks for a script. I will understand further