db_recovery_file_dest_size of N bytes is X% used and has R remaining bytes available

The boat launch can be a tough place, especially if you are a beginner on a busy lake.  People are anxious to get onto and off of the lake.  You don’t want people to be sitting there waiting on you, so the temptation is to go as fast as you can.  This can be a huge mistake for many though.  Even experienced boaters can make rookie mistakes that are downright embarrassing in front of all the other boaters.  Personally, I’ve made two mistakes that were somewhat embarrassing.  My first wasn’t a big deal – I forgot to put in the boat plug.  I’d guess most boaters have made this mistake at one time or another.  Water started gushing in the boat.  Once I saw it I quickly jammed in the plug and started the bilge pump, which drained out the water in about five minutes.  No real harm done.  My other big mistake was while I was pulling off a river.  The launch was gravel and had no dock.  I walked out on the tongue of the trailer, but while I was adjusting the boat I lost my balance.  Unfortunately my shoe got stuck on a bracket so instead of stepping down and getting my leg wet up to my knee, I fell back first and ended up totally submerged in 1 1/2 feet of water.  I’m the kind of guy who is able to laugh at my mistakes, so even though I ended up soaking wet and embarrassed, I still had a good laugh at my own expense.

A DBA can certainly see the lesson in this.  If you go too fast you make mistakes.  You need to be very careful and pay close attention to details.  One way to cover yourself is to maintain backups.  I recently ran into a warning in Oracle Enterprise Manager that said I was using 97% of the recovery area free space.  A quick search found that if the used space got up to 100%, the database would cease to process transactions.


I wanted to see within the database what my db_recovery_file_dest_size parameter was set to.  I ran a quick query and found that it was set to 350 GB.


Next I queried the v$recovery_file_dest to see the total space, used space, and directory where I was storing the backupsets.


I checked the directory and found it quite full of back files.


My next step was to attempt to delete any expired archivelogs.  Unfortunately, this didn’t do anything:


I needed to first backup the archivelogs before I could delete any of them.  So I ran the following command in RMAN:


This ran a VERY long time, but eventually succeeded.  This fixed my problem.  After running this command I checked my free space and found I’d gained plenty:


Taking backups is not enough – you need to be aware of what is happening to the backups.  Testing recover-ability is also a great idea!

Challenges with image-based backups

I haven’t done a lot of muskie fishing in the past month.  It’s been a busy summer (as always), and I’ve been sticking to short fishing trips for “lesser” species.  I made a couple trips to little cedar this last weekend and both were fun and fairly successful.  The first was with a friend from church and his young son.  They caught several bluegills and the boy had a blast, although he probably enjoyed the snacks more than the fishing.  I caught a couple northern pike, the biggest of which was 19″.  It was almost big enough to keep, but I ended up letting it go.

The next night I took my five year old out for an evening of fishing.  We got some leeches and were targeting walleye, but we only ended up with some largemouth bass.  I think we were getting bites from walleye, but we didn’t manage to hook any.  Below is a picture of a nice largemouth bass my son took with a skitter pop.  The fish had a couple spectacular jumps before making it to the boat.

Nate LM Bass 20160806

Yesterday was one of the most challenging days I’ve had with my current employer.  We’re in the process of changing managed service providers for IT infrastructure, and changes like these are never easy.  The problems yesterday stemmed from the backup solution changes.  Both our old and new MSPs use a disk image-based backup solution, but they are different brands.  In a previous post, I’ve made it clear that I am not a fan of image-based backups for databases.  Most relational database management systems have their own backup mechanisms, and I understand and trust these backups far more than the image backups.  However, different backups can serve different purposes and in this case, I have no choice in the matter.

The problem occurred over the course of the morning.  I got a report that our Oracle database was not available.  Upon checking, I found I could not access the database.  I remote desktop-ed into the server to check the service and found connectivity spotty.  I recycled the Oracle database service, but that did not fix the issue.  Since the connectivity was slow and spotty, I suspected either a network or disk issue.  The new MSP was able to add some diagnostics to the server and we found no hardware issues with the disks.  There also didn’t appear to be any network connectivity issues.

At this point we attempted a server reboot.  This didn’t help.  Since Oracle database is pretty much the only thing running on the server, we figured the problem either had to be Oracle or the antivirus software.  We disabled both and rebooted the server again.  This time it appeared to help.  We brought Oracle back up but left the antivirus down.  Everything was working fine for about 45 minutes, so we figured there must’ve been some sort of conflict between Oracle and the antivirus software.  To win the worst timing of the day award, I sent out an all-clear email to the users about one minute before the server started breaking again.  We saw the same symptoms as before where we had spotty connectivity and extreme slowness.

After yet another reboot we left both Oracle and the antivirus off.  When slowness occurred yet again, we knew it had to be something else that was running on the server.  A quick check of the Programs list showed that the image-based backups for the previous MSP were still running (as they should have been).  However, that was enough of a clue for a lightbulb moment.  In preparation for the new backup solution, a disk defragmentation had been run the previous night.  The current backup solution did a single full, then continuous incrementals afterward.  The defragmentation had scrambled up all the bytes on the disk, resulting in an incremental with lots of changes to process.  In looking at the backup manager, we found the next incremental would take a full two days to process.  We also discovered that each time we’d had problems, there was an incremental backup scheduled to run.

Fairly confident we’d found our culprit, we disabled the backup and turned Oracle and the antivirus software back on.  As expected, there were no issues for the remainder of the day.

In the end, the pressure of the database trying to use the disks in addition to the backup solution proved to be too much for the server to handle.  Through the use of methodical testing, we found the problem and fix it.

SQL Server Upgrade/Migration

Last week I was feeling burnt out. We’d been having numerous issues pop up at work and it was taking its toll on me. I felt constantly mentally fatigued and knew I needed some time to myself. My wife could sense I needed some relaxation, so she took the kids to her aunt’s house and let me have some solo time. I considered going down to Pewaukee to try and catch some muskies, but the water temp is peaking over 80 degrees, which is the max you should fish for muskies. Any warmer than that and they are unable to properly recover from the fight and could end up dying, even if it isn’t for a couple hours after they are released. Besides, I needed to just relax, and while muskie fishing is an awesome sport it really isn’t very relaxing. So I headed over to Little Cedar Lake to shoot for some walleye, largemouth bass, or northern pike. While I only managed one medium sized bass, it was great to just sit back and sip a few beers while enjoying the lake air and beautiful scenery. Since that night I have felt much more refreshed and ready to get back to work.

Evening Fishing
We have been doing some server upgrade/migrations the last several weeks. We purchased a new server and needed to move our production instance to it. We don’t have any kind of high availability, and were able to tolerate a system outage of four or less hours. The first thing I did was to practice extensively using our Test environment. I did the cutover in Test several times. I was confident going into the upgrade that I would be able to complete it successfully.  This upgrade was slightly more complicated in that in involved moving some databases to different drives during the upgrade process.  Not only was I going to move the system files from the C: drive to the data file and log file drives, but I also needed to move tempdb from the G: to the F: drive and move data and log files into differently named folders on the D: and E: drives.
I used the following procedure list to perform the upgrade:

  1. Stop the application services.  This will close existing connections to the user databases.
  2. Stop the data warehouse transfer process.  We use a third party tool to move data from our Production SQL Server instance to our Oracle data warehouse.
  3. Take full copy-only backups of each user database.  After taking each backup, take the database offline to prevent new connections and changing data.
  4. Take full backups of all system databases.  It is important to do them after the user databases so that msdb has records of the latest user database backups.
  5. Move all system database using SQL Script below.
  6. Create and restore all user databases using SQL Script demonstrated in other blog post.
  7. Shutdown new and old server.  System Admin changes the name of the old server to whatever, then changes the name of the new server to what the old server used to be.  He also updates the IP of the new to be what the old was.  This way we don’t have to change any application settings.
  8. Bring up Reporting Services on new server.  I will explain the intricacies of this step in a later post.
  9. Begin testing everything – application, SSIS, SSRS, and SQL Agent jobs.

Here are the SQL Scripts used to make the changes:

USE [master]
RESTORE DATABASE [model] FROM  DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\model\FULL\OldServer_model_FULL_20150724_201857.bak' 
WITH  FILE = 1,  
--MOVE N'modeldev' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf',  
--MOVE N'modellog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\modellog.ldf',  


ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\DataFiles\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = 'E:\LogFiles\modellog.ldf')


--Stop SQL Server Agent
USE [master]
RESTORE DATABASE [msdb] FROM  DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\msdb\FULL\OldServer_msdb_FULL_20150724_201858.bak' 
WITH  FILE = 1,  
--MOVE N'MSDBData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf',  
--MOVE N'MSDBLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\MSDBLog.ldf',  



--Restart the SQL Service

--Stop the service
--Add -m to SQL Server startup parameters to start in single user mode
--Open command prompt
--Start the service
FROM DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\master\FULL\OldServer_master_FULL_20150724_201856.bak' WITH REPLACE;

--Remove -m from SQL Server startup parameter
--Open command prompt
--Go to the SQL Server directory (on this machine, C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn)
--run SQLServr.exe -T3608 -T3609
--		This will just open Master database.
--Run SQLCMD from a different command prompt and run these commands
ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\DataFiles\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = 'E:\LogFiles\modellog.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev01', FILENAME = 'F:\TempDB\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev02', FILENAME = 'F:\TempDB\tempdb2.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev03', FILENAME = 'F:\TempDB\tempdb3.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev04', FILENAME = 'F:\TempDB\tempdb4.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev05', FILENAME = 'F:\TempDB\tempdb5.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev06', FILENAME = 'F:\TempDB\tempdb6.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev07', FILENAME = 'F:\TempDB\tempdb7.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev08', FILENAME = 'F:\TempDB\tempdb8.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev09', FILENAME = 'F:\TempDB\tempdb9.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev10', FILENAME = 'F:\TempDB\tempdb10.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev11', FILENAME = 'F:\TempDB\tempdb11.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev12', FILENAME = 'F:\TempDB\tempdb12.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev13', FILENAME = 'F:\TempDB\tempdb13.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev14', FILENAME = 'F:\TempDB\tempdb14.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev15', FILENAME = 'F:\TempDB\tempdb15.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev16', FILENAME = 'F:\TempDB\tempdb16.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'F:\TempDB\templog.ldf')

--End SQL Service, then start the service normallyy.
--At this point all the system databases should be transfered to New Server.

As I said, the migration went pretty smoothly.  I did have a few hiccups that should have been tested weeks before the migration, but I missed them.  I was able to get them working within a day of the migration, and next time I’ll get them right away.  The first was the database mail, which took me an extra hour to get set up right after the migration.  The second was a linked server.  This was a bit tougher because I had to install the Oracle ODBC driver, and this required a SQL Server reboot.

Steve Stedman’s Corruption Challenge #3

This Saturday is the fishing opener here in Wisconsin.  The DNR designates some time in spring when the only fishing allowed on most inland waters is panfishing.  This rule was created to protect the fish, since most species spawn in the spring.  I can’t wait to get out there on Saturday and try to catch some opening day fish!  This is my first opener owning my own boat, so I can certainly say I’ve never been looking forward to a fishing opener this much.  Hopefully my next post will have a picture or two of a beautiful 2015 muskie.

SQL Server blogger Steve Stedman recently started a weekly database corruption challenge. The challenges each involve the simulation of some sort of corruption or database failure, and we are tasked with recovering without losing any data. I missed the first two weeks, but decided to spend a bit of time last Saturday morning working on the third challenge. Here is the scenario Steve set up:

Here is the timeline as things occurred

At 2:53pm a full backup was performed. After that full backup, users continued to do work and the database continued to grow.

At 2:54pm a transaction log backup was performed. Followed by more changes to the database.

At 3:01pm another transaction log backup was performed. Followed by more changes to the database.

At 3:12 another transaction log backup was performed. Followed by more changes to the database.

At 3:19 a catastrophic failure occurred causing the operating system running SQL Server to reboot. Let’s blame it on a power surge, or maybe it was just a DBA who accidently bumped the power cord. I like the power surge idea, so that I don’t get blamed for kicking the power cord.

After the server rebooted, several other databases on this SQL Server recovered just fine, however the CorruptionChallenge3 database would not come on line. After examining the SQL Server data directory, it was discovered that the single .mdf file for this database was gone, missing, just not there. The only thing that remained on this SQL Server was the .ldf file for this database.

20150429 corruption challenge db recovering

Steve included five files needed to recover all the data – the last full backup, the three transaction log backups that were taken between the last full backup and the disaster event, and the log file.

20150429 corruption challenge files given

After thinking about the problem briefly, I formulated my recovery plan. I would restore the full backup and each of the transaction log backups. This would bring the database current through the time of the last backup. Then I would detach the database and swap out the log file with the one provided by Steve, and take another log backup. Then I could use this new log backup to bring the database online with all transactions accounted for. The first part of my plan went according to plan:

USE [master]
RESTORE DATABASE [CorruptionChallenge3] FROM  DISK = N'\\server\Gugg\CorruptionChallenge3_Full.bak' WITH  FILE = 1,  MOVE N'CorruptionChallenge3' TO N'X:\SQLData\CorruptionChallenge3.mdf',  MOVE N'CorruptionChallenge3_log' TO N'Y:\SQLLogs\CorruptionChallenge3_log.LDF',  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3] FROM  DISK = N'\\server\Gugg\TransLog_CorruptionChallenge30.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3] FROM  DISK = N'\\server\Gugg\TransLog_CorruptionChallenge31.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3] FROM  DISK = N'\\server\Gugg\TransLog_CorruptionChallenge32.trn' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5
EXEC MASTER.dbo.sp_detach_db @dbname = N'CorruptionChallenge3'

However, when I tried to re-attach the database after replacing the log file, I got the following message:
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file ‘R:\SQLLogs\CorruptionChallenge3_log.LDF’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
At this point I remembered some Oracle training I’d done regarding Log Sequence Numbers (LSNs). When connecting a database, the LSN in the log has to match the LSN in the data file. At this point I couldn’t think of what else to do. I could not think of any way to get the data out of the log file without taking a backup of it, and I couldn’t take a backup without first attaching it. I thought about it for another 15 minutes and then turned my attention to other tasks I had to get done that Saturday.
When Steve revealed the answer on Tuesday, I was disappointed by how close I came, but I missed an important detail. In order to take a tail-log backup without a valid data file, you need to set a “dummy” version of the database offline, delete the data file, replace the log file, and the when you turn it back online you’ll get an error message but you will still be able to take a tail-log backup. (See this post by Paul Randal for details.) So I should have done this:

USE [master]
CREATE DATABASE [CorruptionChallenge3] ON 
( NAME = 'CorruptionChallenge3', FILENAME = N'S:\SQLData\CorruptionChallenge3.mdf' ),
( NAME = 'CorruptionChallenge3_log', FILENAME = N'R:\SQLLogs\CorruptionChallenge3_log.LDF' )


--Delete the mdf file and replace the ldf file


20150429 corruption challenge files inaccesible

BACKUP LOG CorruptionChallenge3 TO DISK = N'\\server\Gugg\Final_Log_Backup.trn' WITH INIT, NO_TRUNCATE;

The key here is to use the NO_TRUNCATE option. With this I am able to take a log backup, and now have one full backup and four log backups, enough to restore the database with no data loss!

I have a busy weekend coming up since it’s the fishing opener on Saturday, but I hope to be able to work on Corruption Challenge #4, coming up on Friday.

Stopping MSDB bloat due to frequent backups

I’ve always wanted to catch a truly fat fish. In fall many species of fish feed heavily to put on fat for the winter, and when catching them you can see the stomach pushing the belly of the fish out and making them look quite bloated. This is the reason that fishing in the cold wind and rain of late fall is worth the pain and sacrifice.
I recently ran into an issue where I noticed the MSDB database on an instance was growing quite bloated. MSDB database contains data related to SQL Agent jobs, database mail, SSIS packages, Backups, and Maintenance plans. In most cases it should be fairly small, but I observed it at close to 4 GB.
20150325 Bloated MSDB Initial Size
To find what was taking all the space I ran this query:

USE msdb

        i.[name] AS indexName ,
        SUM(a.total_pages) AS totalPages ,
        SUM(a.used_pages) AS usedPages ,
        SUM(a.data_pages) AS dataPages ,
        ( SUM(a.total_pages) * 8 ) / 1024 AS totalSpaceMB ,
        ( SUM(a.used_pages) * 8 ) / 1024 AS usedSpaceMB ,
        ( SUM(a.data_pages) * 8 ) / 1024 AS dataSpaceMB
FROM    sys.indexes i
        INNER JOIN sys.partitions p ON i.OBJECT_ID = p.OBJECT_ID
                                       AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
        i.index_id ,
ORDER BY SUM(a.total_pages) DESC ,

20150325 Bloated MSDB Biggest Space Users
This showed the backupfile table to be taking over half the database space, and some other associated backup tables taking up quite a bit of the rest. Looking in MSDN, I was able to find that the backupset table contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. At he bottom of the msdn page I found this: “To reduce the number of rows in this table and in other backup and history tables, execute the sp_delete_backuphistory stored procedure.” I followed the hyperlink to the MSDN page describing this stored procedure. The page describes the stored procedure as follows: “Reduces the size of the backup and restore history tables by deleting the entries for backup sets older than the specified date. Additional rows are added to the backup and restore history tables after each backup or restore operation is performed; therefore, we recommend that you periodically execute sp_delete_backuphistory.”
I decided to get a baseline of how many rows are currently in the backupset table, then start deleting rows using this stored procedure. For this instance we never have to look at backups older than a month at the very most, but to be safe I would prefer not to delete backups that are too recent. I decided I wanted to reduce the record count by about 75%, and then monitor growth to see how much data I should actually keep.
Here is the initial rowcount:
20150325 Bloated MSDB Initial Rowcount Next I ran the stored procedure to delete everything older than January 1st 2011:

 EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2011'

Then I checked the rowcount again:
20150325 Bloated MSDB 2011 rowcount I ran this several more times, keeping less history each time:

 EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2012'

20150325 Bloated MSDB 2012 rowcount

 EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2013'

20150325 Bloated MSDB 2013 rowcount I quickly realized much of this bloat is due to a change in the backup configuration we made late last year. In order to facilitate disaster recovery we had to abandon our native SQL backups (NO!) and go with a third party image backup system. I didn’t like this because I prefer to be in charge of the backups instead of leaving them to an outside vendor, but our cloud backup/disaster recovery area was only able to handle image backups, so I had no real option. Since we are busiest late in the year, we ended up having to change the transaction log backups so they are run every minute. This is the only way the image backup would work without slowing the system, since it requires an I/O freeze when it takes the backup, and the backup capture all changes since the last backup. Smaller intervals mean less changes and a shorter I/O freeze time.
After trimming the backup history to about three months:
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2015'
20150325 Bloated MSDB 2015 rowcount I was able to trim the rowcount enough such that there is significant empty space in the database. Next I set up a SQL job to delete all records older than 3 months and run it once a month. Now I don’t have to worry about MSDB continuing to grow.

Striped Backups in SQL Server

Trolling is a common technique for trying to catch fish. Trolling involves motoring slowly through the water while pulling one or more fishing lines. Usually rod holders secure the rod in the boat and allow you to space the lures apart. You’ll set a line deep, a line shallow, and a line in a medium depth, and you can also use different lure types and different colors. With this great variety of bait in the water, you have a much better chance of giving the fish something that they are interested in biting. I’ve never been a fan of trolling because you generally aren’t holding the rods, so you don’t feel the fish strike. I kind of view it as motoring around in circles, then reeling in a fish when it strikes. That said, I certainly understand how it can be more efficient than casting.
Similarly, SQL Server gives us the ability to be more efficient with our backups by writing them out with multiple lines, or striping them. Using this technique, the backups are written to multiple files. The total space used is equal to a single file, but since each file can be written to and read from simultaneously, the backups and restores may end up being faster if I/O throughput is an issue. Before I show an example, there is one important caveats to mention:

  • All of the backup files are necessary to do a restore. These aren’t like a mirrored RAID where you get redundancy. Therefore, if you lose one file, the entire backup is broken.

Ok, now on to the demonstration. To make a good test you’re going to want a fairly large database that has a high percentage of space used. If you need to artificially fill a database for testing, try using my script below to create a table and fill it with junk data:

CREATE TABLE StripedBackupTest
                          PRIMARY KEY ,
      FixedNumber DECIMAL(38, 10) ,
      MuskieFishingRocks CHAR(100) ,
      ReallyLongBlob VARCHAR(MAX) ,
     ReallyLongBlob2 VARCHAR(MAX) ,
     ReallyLongBlob3 VARCHAR(MAX)
    CREATE INDEX IX_AllCols ON dbo.StripedBackupTest 
    ) INCLUDE (ReallyLongBlob)
WHILE @i <= 100000
       INSERT INTO dbo.StripedBackupTest
               ( FixedNumber ,
                 MuskieFishingRocks ,
                 ReallyLongBlob ,
                 ReallyLongBlob2 ,
       SELECT  ABS(CAST(NEWID() AS BINARY(6))%1000000000)*1000000000.0 
                   + ABS(CAST(NEWID() AS BINARY(6))%1000000000)
                   + ABS(CAST(NEWID() AS BINARY(6))%1000000000)*.0000000001, --A random number with 18 digits left of the decimal and 10 right of the decimal
               REPLICATE(CONVERT(VARCHAR(255),NEWID()) + CHAR(10) + CHAR(13),500),
               REPLICATE(CONVERT(VARCHAR(255),NEWID()) + CHAR(10) + CHAR(13),500),
               REPLICATE(CONVERT(VARCHAR(255),NEWID()) + CHAR(10) + CHAR(13),500)

I’m going to use an existing test database that is almost 28 GB in size, with almost 4 GB of free space. This gives me about 24 GB in used space.
20150219 Striped Backups Final DB Size First I’ll take a normal, one file backup to get a baseline.

BACKUP DATABASE cwi_warehouse TO  DISK = N'T:\TempBackups\SingleBackupTest.bak' 

20150219 Striped Backups Single File Backup Time You can see this backup took 334 seconds to run. It’s also a great idea to perform a restore-verify only consistency check on a backup when you take it. I also ran that and got an additional 3:51 amount of time:

SELECT  @backupSetId = position
FROM    msdb..backupset
WHERE   database_name = N'cwi_warehouse'
        AND backup_set_id = ( SELECT    MAX(backup_set_id)
                              FROM      msdb..backupset
                              WHERE     database_name = N'cwi_warehouse'
IF @backupSetId IS NULL
        RAISERROR(N'Verify failed. Backup information for database ''cwi_warehouse'' not found.', 16, 1)
RESTORE VERIFYONLY FROM  DISK = N'T:\TempBackups\SingleBackupTest.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

20150219 Striped Backups Single File Verify Time

The last step is to run a full restore:

USE [master]
RESTORE DATABASE [cwi_warehouse] FROM  DISK = N'T:\TempBackups\SingleBackupTest.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5


20150219 Striped Backups Single File Restore Time

This ran in 306 seconds. Now we have our baseline. It can be a good idea to run each statment three times and take the average when calculating a baseline, just because other things can affect the results, such as processes, network traffic, etc. Next I’ll run a striped backup using four separate files:

BACKUP DATABASE cwi_warehouse TO  DISK = N'T:\TempBackups\MultiBackup_1.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_2.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_3.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_4.bak' 

This time the backup ran in 340 seconds.
20150219 Striped Backups Multi File Backup Time Next I run the backup verification.

SELECT  @backupSetId = position
FROM    msdb..backupset
WHERE   database_name = N'cwi_warehouse'
        AND backup_set_id = ( SELECT    MAX(backup_set_id)
                              FROM      msdb..backupset
                              WHERE     database_name = N'cwi_warehouse'
IF @backupSetId IS NULL
        RAISERROR(N'Verify failed. Backup information for database ''cwi_warehouse'' not found.', 16, 1)
RESTORE VERIFYONLY FROM  DISK = N'T:\TempBackups\MultiBackup_1.bak',
   DISK = N'T:\TempBackups\MultiBackup_2.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_3.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_4.bak'  WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

20150219 Striped Backups Multi File Verify Time This ran in 3:37.
Lastly I run the full Restore.

USE [master]
RESTORE DATABASE [cwi_warehouse] FROM  DISK = N'T:\TempBackups\SingleBackupTest.bak',
   DISK = N'T:\TempBackups\MultiBackup_2.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_3.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_4.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5


20150219 Striped Backups Multi File Restore Time This ran in 352 seconds.
You can see that the multi-file backup configuration didn’t really give us any significant time savings. The last point to make is that multi-file backups can be a huge advantage when backup disk space is an issue. If you have a particularily large backup and need to spread it across multiple physical disks to get it to fit. You can see here the sum of the four backups were about equal in size to the single backup file.

20150219 Striped Backups Single File Size

20150219 Striped Backups Multi File Size

If I had four disks with five GB available on each, I would not be able to do a backup of this database with a single file, but could with multiple files. Just remember, all the files are needed to be able to restore the database.

Backup size and time – database total space versus used space

There is a rather rough rule that fisherman follow when looking for trophy fish. The rule goes like this: the bigger the lake, the bigger the fish. As I stated, this is a rough rule, and there are lots of exceptions, but in many cases it proves to be true. There is a very small lake here in southeastern Wisconsin called Random lake. This lake holds muskie, but very few, if any, above the 40″ mark. Contrast that with Green Bay, a huge amount of water where many 50″+ muskie are caught every year. Much of this is the results of the forage available and the density of the target fish, but either way, if I’m looking to catch a true trophy, I’m heading to large lakes.
At work I came across a situation that made me wonder about backup sizes. I had inherited a ~100 GB database, and after some initial benchmarking of the system, I decided to compress some of the largest tables. I ended up reducing the amount of used space down by almost 53 gigabytes, which reduced the space needed and increased performance (less I/O). However, I now have these large data files (this database has 10 files) that are about 2/3rds empty. I wondered to myself whether having large files, even if they are mostly empty, increases the backup or the restore time of the database. I decided to run a quick test on my Sandbox machine to see if there was any real difference.
The database started out with an initial size of 0.4 GB.

20150109 Backup Restore Size Small DB

I used this script to do the backup. The script backups up the database and also does a test restore to ensure the backup is usable. This is an important step in any backup strategy.

BACKUP DATABASE [Sandbox] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSANDBOX\MSSQL\Backup\Sandbox.bak' WITH NOFORMAT, NOINIT,  NAME = N'Sandbox-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
SELECT  @backupSetId = position
FROM    msdb..backupset
WHERE   database_name = N'Sandbox'
        AND backup_set_id = ( SELECT    MAX(backup_set_id)
                              FROM      msdb..backupset
                              WHERE     database_name = N'Sandbox'
IF @backupSetId IS NULL
        RAISERROR(N'Verify failed. Backup information for database ''Sandbox'' not found.', 16, 1)
RESTORE VERIFYONLY FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSANDBOX\MSSQL\Backup\Sandbox.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

20150109 Backup Restore Size Small Backup

The backup took 16 seconds. Here is the backup in the directory. You can see the backup size is 373 MB.

20150109 Backup Restore Size Small Backup Size

Lastly, I’ll do a restore to check how long that takes. You can see in the screenshot below it took 23 seconds.

USE [master]
RESTORE DATABASE [Sandbox]   FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSANDBOX\MSSQL\Backup\Sandbox.bak' WITH  FILE = 1,  
                           NOUNLOAD,  REPLACE,  STATS = 5


20150109 Backup Restore Size Small Restore

Next I grow the database file. Since I’m using SQL Server Express, I’ll grow it to its max size, 10 GB.

20150109 Backup Restore Size Large DB

I use the same backup script and it now takes 17 seconds. This is a very slight difference considering the database file grew by 2400%. We’ll call it a negligible difference

20150109 Backup Restore Size Large Backup

The size of the backup has also grown by a very small amount, up to 383 MB.

20150109 Backup Restore Size Large Backup Size

The last thing to check is the restore time. This time the restore took 23 seconds. This is in line with what we saw from the restore with the small size database file.

20150109 Backup Restore Size Large Restore

So this experiment has shown that data file size makes a tiny, if any at all, difference in the amount of time to backup and restore and the size of the backups. Shrinking database data files almost always causes high fragmentation in the database, so unless you absolutely need more disk space on your drive, there is really no reason to shrink the database files, even if they have a lot of free space.