December 20, 2013 at 6:10 am
OK, I'm still collecting info, but I've gotten a request to create copies of a couple databases for a project. These copies will need to be kept for about a month. At the moment, I'm not sure if the owner of the DBs intends to use the copies for read-only, or if they also will need to write.
So, I'm starting from the presumption they will be read-only. My problem is, one of these databases is quite big, and there's insufficient room on the server to have a direct copy. If it's going to be read-only, I could (bad words to come) shrink the source DB to remove any free space, then back up the DB and restore it under a new name and set it read-only. Once that's done, I'd re-grow the source back to its' original size to fight off auto-growths. Doing this will all 4 source DBs, I would have enough room for this.
My other thought (and we are running SQL 2008R2 Enterprise) is to create snapshots of all 4 DBs and let the owner point to those. I've read the Technet info, so I know of the performance hit on the source DB. My concern is, there are monthly "purge" jobs and daily data load jobs against one of the DBs (the big one.) Will the snapshot therefore have to "grow" to keep track of the changed records so it (the snapshot) stays consistent to the moment it was taken?
I know I can also look into getting some extra temporary storage on the server, although that could take a couple weeks and they're looking to have this done soon. With lots of staff taking off for the holidays, well...
Thanks all.
Jason
December 20, 2013 at 6:19 am
jasona.work (12/20/2013)
Will the snapshot therefore have to "grow" to keep track of the changed records so it (the snapshot) stays consistent to the moment it was taken?
Yes.
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
December 20, 2013 at 6:25 am
GilaMonster (12/20/2013)
jasona.work (12/20/2013)
Will the snapshot therefore have to "grow" to keep track of the changed records so it (the snapshot) stays consistent to the moment it was taken?Yes.
Crud. Plus, I just did a quick snapshot test on a QA database, the snapshot file (started, I didn't insert / delete anything) was the same size as the source.
I think I'm going to need to request some disk be added to the server...
December 20, 2013 at 6:30 am
OK, never mind on the file size, read a little better the Technet article on snapshots and sparse file sizes.
I was a dunce with my first test, just looked at the column in file manager, not the "size on disk" in the properties.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply