November 18, 2012 at 9:50 am
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
November 18, 2012 at 11:15 am
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" 😉
November 18, 2012 at 11:22 am
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
November 18, 2012 at 12:20 pm
Any index rebuilds or reorganise? Is autoshrink enabled?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2012 at 9:51 pm
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
November 19, 2012 at 1:57 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2012 at 2:37 am
DBCC UPDATEUSAGE, that'll be the culprit 😉
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" 😉
November 19, 2012 at 6:12 am
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
November 19, 2012 at 6:24 am
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
November 19, 2012 at 7:16 am
A bit more info --
I just did a fresh restore from the original .bak file (204GB)
sp_spaceused reports:
namerowsreserveddataindex_sizeunused
PayrollDataStore49824 123979096 KB123897912 KB2528 KB78656 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:
namerowsreserveddataindex_sizeunused
PayrollDataStore49824 96525096 KB96274008 KB2528 KB248560 KB
Doesn't seem to make any sense at all to me now --
Thanks for your help.
sqlnyc
November 19, 2012 at 8:49 am
can you post the column definitions for the table and the backup script you are using to take the backup on the new server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 19, 2012 at 10:08 am
Hi Perry,
Thanks for your help --
BACKUP statement:
BACKUP DATABASE <dbname> TO DISK = 'D:\safetybackups\dbname.bak' with init, stats = 10
Table DDL:
CREATE TABLE [dbo].[Table1](
[PersistingId] [int] IDENTITY(1,1) NOT NULL,
[PayrollId] [int] NOT NULL,
[PayrollData] [image] NULL,
[CreationDate] [datetime] NOT NULL,
[Active] [bit] NOT NULL,
[PersistingGUID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[PersistingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [IX_GUID_151_PersistingId] UNIQUE NONCLUSTERED
(
[PersistingGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [DF_Table1_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [DF_GUID_151_PersistingId] DEFAULT (newsequentialid()) FOR [PersistingGUID]
GO
Thanks,
sqlnyc
November 19, 2012 at 12:32 pm
Have large amounts of data been dropped on the source database from this table at all.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 19, 2012 at 1:05 pm
Hi Perry,
Yes, we have recently deleted a large amount of rows. In fact I was just in touch with Paul Randal about this, and he suggested that it might be a ghost cleanup issue.
Checking the source db in production now shows 5,246,587 for one of the CLUSTERED INDEX ghost_record_count entries. On the new server, there were about ~4m ghost_record_count rows before I did the SQL Backup, and about ~2m ghost_record_count rows after the backup.
In the db I was checking last night, all of the ghost record have been cleaned up.
Guess I'll have to do some research as to why they're not getting cleaned up in production.
Thanks for your help --
sqlnyc
November 19, 2012 at 1:15 pm
sqlnyc (11/19/2012)
Hi Perry,Yes, we have recently deleted a large amount of rows. In fact I was just in touch with Paul Randal about this, and he suggested that it might be a ghost cleanup issue.
That's exactly where I was going 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply