March 27, 2014 at 10:14 am
I restore the backup to my UAT server and the initial data size explodes to 53gb and the log is set to 1gb.
Hmm, I think the log file should be the same size as it initially was when it's restored. Are there some other commands running on the db after it is restored? Maybe to reorg/rebuild the data and/or shrink the log?!
[Btw, 53GB is only ~3.5x 15GB, not 5x :-)).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 4, 2014 at 9:54 pm
It is wierd. This is just a guess - Could it be that the recovery model may be changing which is causing the log to flush over to the data files and drop down to 1GB itself?
Another thing is that although the database is restored on a SQL 2008 R2 environment, it appears that the database is running under the SQL 2005 compatibility mode (90). Not sure if backup compression on a database with compatibility level 90 has something to do with it.
I don't have a SQL instance right now to test it on, but will give it a shot as soon as I have one.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 17, 2014 at 8:32 am
OK... well I have had some more time to look at this issue. What I did was to run a Disk Usage by Table report on 2 databases, my production database and a copy of that same database created from a SQL Compressed backup.
From my discovery the RESERVED (KB) along with the DATA(KB) increased especially with some of the larger tables.
However when I restore the database from a backup that doesn't utilize SQL Compression I have a database close to the original size of the production (original) database.
Still scratching my head! :crazy:
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
April 17, 2014 at 10:17 pm
Just thinking out loud (not prototyped/tested) - is it possible that both servers have different hard drive partition configuration when they were formatted as part of setting up of the servers (sector sizes, etc)?
I'm guessing the when restoring from a compressed backup, a different drive configuration has some role to play in the final size.
By the way - closing one of my action items from a previous comment - I was able to confirm that the compatibility level has nothing to do with this issue.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 29, 2014 at 11:30 am
Greetings!
Did you inherit this UAT server or build it yourself?
Before going mad just restore it elsewhere, if successful and the result is a perfect copy of production, you can rule out any troubles in production and focus on the one UAT server. <-- how much time do you wish to waste if its the UAT server or are you determined to find out what's going on?, how long to rebuild?
Have you run dbcc checkdb on a restored copy on the UAT server?
Offlined sql and run check disk at the os level?
Maybe run a profiler trace on the restore?
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply