March 26, 2014 at 7:55 am
Greetings all;
I've been trying to find some information about what might be going wrong here. I'll be the first to admit pilot error.
I have a production database that has 15.357 gb initial data size and is showing 27% available free space. The log has a 27.670 gb initial data size and since the recovery model is FULL is showing 99% available free space.
I run nightly backups along with transaction log backups every 15 minutes. Both the backup and transaction logs are being save by the native compression.
I'm running Enterprise Edition (64-bit) in a VM environment.
Here is my problem. I restore the backup to my UAT server and the initial data size explodes to 53gb and the log is set to 1gb. I am creating the restored database at the time of restore and not loading the restore on top of an existing database.
If I take a backup of the production database without compression the restore (same process) creates the initial sizes of data and log.
Has anyone else noticed this issue? Is this a known bug? You input is greatly appreciated.
Kurt Zimmerman
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
March 26, 2014 at 8:06 am
You said the production DB has initial size of 15GB, what's the actual size of the data file on disk?
Edit: Native backups? What's the backup command? What's the restore command?
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
March 26, 2014 at 8:08 am
In order to find the reason, run RESTORE FILELISTONLY from the same source which you use for actual restore. It will show all file sizes in bytes
March 26, 2014 at 8:25 am
GilaMonster (3/26/2014)
You said the production DB has initial size of 15GB, what's the actual size of the data file on disk?Edit: Native backups? What's the backup command? What's the restore command?
15GB
This is the backup command:
BACKUP DATABASE [xxxx]
TO DISK = N'D:\SQLDumps\xxxx_backup_<datetime stamp>.bak'
WITH NOFORMAT
, NOINIT
, NAME = N'xxxx_backup_<datetime stamp>'
, SKIP
, REWIND
, NOUNLOAD
, COMPRESSION
, STATS = 10
This is the restore command:
RESTORE DATABASE [xxxx]
FROM DISK = N'E:\Transfer\xxxx_backup_<datetime stamp>.bak' WITH FILE = 1
, MOVE N'xxxx_dat' TO N'D:\SQLData\xxxx.mdf'
, MOVE N'xxxx_log' TO N'E:\SQLLogs\xxxx_1.ldf'
, NOUNLOAD
, STATS = 10
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
March 26, 2014 at 8:32 am
Hmmm... that's odd.
Should created the DB exactly as it was at the time of the backup. Nothing else going on? No reverts from snapshot? No data obfuscations being run?
Can you repo this on a different database?
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
March 26, 2014 at 8:35 am
GilaMonster (3/26/2014)
Hmmm... that's odd.Should created the DB exactly as it was at the time of the backup. Nothing else going on? No reverts from snapshot? No data obfuscations being run?
Can you repo this on a different database?
Nothing else going on, no snapshots, and no data obfuscations being run. Let me try on another database.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
March 26, 2014 at 8:49 am
What are the exact versions of the two servers?
SELECT @@Version
Any traceflags enabled on either server?
There's only one backup in that file? Not multiple appended somehow?
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
March 26, 2014 at 8:56 am
GilaMonster (3/26/2014)
What are the exact versions of the two servers?SELECT @@Version
Any traceflags enabled on either server?
no traceflags.
Production server:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
UAT server:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
I performed this test on another database in the same environment and I am having the same issue.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
March 26, 2014 at 8:59 am
You're absolutely sure there aren't somehow multiple backups appended in the same file? (since you're using noinit on the backup)
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
March 26, 2014 at 9:05 am
GilaMonster (3/26/2014)
You're absolutely sure there aren't somehow multiple backups appended in the same file? (since you're using noinit on the backup)
Absolutely!
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
March 26, 2014 at 9:08 am
Just to be sure to be sure to be sure 🙂 (because it would be a cause of this), what does RESTORE HEADERONLY return on one of the backups showing this behaviour?
Edit: Also, any jobs running on the UAT server which could change file sizes? Any DDL triggers?
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
March 26, 2014 at 9:44 am
See attached:
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
March 26, 2014 at 11:04 am
Kurt W. Zimmerman (3/26/2014)
GilaMonster (3/26/2014)
You said the production DB has initial size of 15GB, what's the actual size of the data file on disk?Edit: Native backups? What's the backup command? What's the restore command?
15GB
This is the backup command:
BACKUP DATABASE [xxxx]
TO DISK = N'D:\SQLDumps\xxxx_backup_<datetime stamp>.bak'
WITH NOFORMAT
, NOINIT
, NAME = N'xxxx_backup_<datetime stamp>'
, SKIP
, REWIND
, NOUNLOAD
, COMPRESSION
, STATS = 10
This is the restore command:
RESTORE DATABASE [xxxx]
FROM DISK = N'E:\Transfer\xxxx_backup_<datetime stamp>.bak' WITH FILE = 1
, MOVE N'xxxx_dat' TO N'D:\SQLData\xxxx.mdf'
, MOVE N'xxxx_log' TO N'E:\SQLLogs\xxxx_1.ldf'
, NOUNLOAD
, STATS = 10
Kurt
Did you try WITH REPLACE ?
March 26, 2014 at 11:59 am
Officially weird
For completeness (and because I'm stuck for ideas), can you post the results of a RESTORE FILELISTONLY on both backups, as well as the output of a SELECT * FROM sys.database_files on the source database?
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
March 27, 2014 at 7:50 am
I have production issues I need to deal with and will post your requested information as soon as I can free up.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply