size of LOB data 41GB smaller after BACKUP/RESTORE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » size of LOB data 41GB smaller after...

18 posts, Page 1 of 2

1 2 »»

size of LOB data 41GB smaller after BACKUP/RESTORE

Rate Topic

Display Mode

Topic Options

Author

Message

sqlnyc

Posted Sunday, November 18, 2012 9:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:16 PM
Points: 49, Visits: 438

Hi everyone,

Maybe someone more experienced than I can explain this to me.

Made a backup of our largest production database, and the backup file size was 204GB. Restored it to another server, and made a backup there. The new backup size was 164GB.

At least half of the database is LOB data, and here are the before/after results for that table:

table on original server = 127GB
table on new server = 86GB

That's a whole lotta space to reclaim, simply via BACKUP/RESTORE. Can anyone explain why there would be such a drastic difference?

The file was zipped with 7zip on the original server, decompressed on the new server and restored. Ran a CHECKDB on the new server, and there were no problems.

Thanks for the clarity --

Best wishes,

sqlnyc

Post #1386058

Perry Whittle

Posted Sunday, November 18, 2012 11:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165

what was done to the database in between the restore to the secondary server and then the new backup, got any maintenance tasks\scripts running?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1386074

sqlnyc

Posted Sunday, November 18, 2012 11:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:16 PM
Points: 49, Visits: 438

Hi Perry,

Thanks for your reply.

I ran scripts to create users, roles and other permission/security related stuff. My scripts do not touch any user tables.

Will run the process again to verify, and make sure that no other hands have touched it.

Thanks,

sqlnyc

Post #1386077

GilaMonster

Posted Sunday, November 18, 2012 12:20 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021

Any index rebuilds or reorganise? Is autoshrink enabled?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1386086

sqlnyc

Posted Sunday, November 18, 2012 9:51 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:16 PM
Points: 49, Visits: 438

Hi Gail,

Thanks for your reply. I have some more info about the situation, and it seems quite strange to me.

Just to make sure I wasn't going crazy, I transferred the database again via backup/restore from the original server to the new server.

sp_configure backup compression default is 0 for the config_value and run_value on the new server

(I apologize for the formatting issues with some of the following output--)

I'm running SQL 2008R2 standard, sp2 on Win Server 2008R2 Enterprise, sp1

Here is what I did, after the initial RESTORE:

sp_spaceused Table1
name rows reserved
Table1 49824 121954664 KB

DBCC UPDATEUSAGE (<dbname>)
WITH NO_INFOMSGS, COUNT_ROWS

sp_spaceused Table1
name rows reserved
Table1 49824 115661672 KB

~6GB reduction in reserved space usage, not so shocking

DBCC CHECKDB (<dbname>) with data_purity, ALL_ERRORMSGS, NO_INFOMSGS
comes up clean

size of original .bak file
214,559,784 KB (204GB)

ran a new backup, and the size is:
173,457,410 KB (165 GB)

A staggering 39GB difference.

Then I ran:
sp_spaceused Table1
name rows reserved
Table1 49824 85894824 KB

Really stumped --

Thanks for any suggestions --

sqlnyc

Post #1386141

GilaMonster

Posted Monday, November 19, 2012 1:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021

sqlnyc (11/18/2012)

DBCC UPDATEUSAGE (<dbname>)
WITH NO_INFOMSGS, COUNT_ROWS

That.

You probably had some page usage errors within the DB (incorrect metadata as to how full pages were). Very common on SQL 2000, less common but still possible on SQL 2005. DBCC UpdateUage goes through and corrects all that.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1386215

Perry Whittle

Posted Monday, November 19, 2012 2:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165

DBCC UPDATEUSAGE, that'll be the culprit Wink

Just out of interest, does the backup\restore cross database version?
I.e. are you restoring from SQL2000 to SQL2008 for instance?

Do you have the output from the DBCC command, can you post it?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1386245

sqlnyc

Posted Monday, November 19, 2012 6:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:16 PM
Points: 49, Visits: 438

Gail and Perry - thanks so much for your help.

Both servers run SQL 2008R2. The source server is running RTM, and the new server is running SP2.

The database was originally created in SQL 2005, and is running at compatibility level 90.

DBCC on the source server did not show any errors.

I will sleep a lot easier now....

Best wishes,

sqlnyc

Post #1386339

sqlnyc

Posted Monday, November 19, 2012 6:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:16 PM
Points: 49, Visits: 438

OK - many not sleeping better just yet....

Originally I ran the DBCC UPDATEUSAGE WITH NO_INFOMSGS, COUNT_ROWS, so I thought that perhaps I missed something.

I just went to the source server and ran DBCC UPDATEUSAGE WITH COUNT_ROWS

Here is the output:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Then I ran sp_spaceused for the table in question:

sp_spaceused table1
127260440 KB

So the size of the table has not been reduced, as far as SQL is concerned.

Perhaps this is a difference between SQL 2008 RTM and SP2. But it sure makes me uneasy --

sqlnyc

Post #1386347

sqlnyc

Posted Monday, November 19, 2012 7:16 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:16 PM
Points: 49, Visits: 438

A bit more info --

I just did a fresh restore from the original .bak file (204GB)

sp_spaceused reports:
name rows reserved data index_size unused
PayrollDataStore 49824 123979096 KB 123897912 KB 2528 KB 78656 KB

Performed a backup to disk, but did NOT execute DBCC UPDATEUSAGE.

New .bak file size is 165GB.

After the backup completes, I ran sp_spaceused again:

name rows reserved data index_size unused
PayrollDataStore 49824 96525096 KB 96274008 KB 2528 KB 248560 KB

Doesn't seem to make any sense at all to me now --

Thanks for your help.

sqlnyc

Post #1386387

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions