September 14, 2012 at 2:30 pm
Just curious... For what reasons would a restore of a read-only database show a difference in free space vs the original?
Jared
CE - Microsoft
September 14, 2012 at 2:35 pm
Of course there are no data changes... Its read-only 🙂 I had worded the subject differently and didn't remove that part. Ugh...
Jared
CE - Microsoft
September 14, 2012 at 4:09 pm
Hmm, no good reason is materializing in my mind right now.
What method did you use to determine the freespace (change)?
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".
September 14, 2012 at 5:47 pm
ScottPletcher (9/14/2012)
Hmm, no good reason is materializing in my mind right now.What method did you use to determine the freespace (change)?
Good question! I will find out. Just started at a new place with a lot of emphasis on pro-active monitoring. I don't know where everything is yet, so I'll find out.
Jared
CE - Microsoft
September 14, 2012 at 9:11 pm
I'm going to make a couple of educated guesses and if anyone call tell me if they are flat out wrong or can prove them I would appreciate it.
I can think of 3 possibilities.
1) Your initial freespace calculation is wrong. The stats that your method uses haven't been updated. Then after the restore, the stats get updated, and all of a sudden your free spaces don't match anymore.
2) You have an open transaction taking up x amount of space when you run the backup. This transaction then gets rolled back during the recovery process freeing up some of your space.
3) This one is a bit farther out there but there is a "space" bug on tables with no clustered index but one or more non-clustered indexes that causes the table to show that it has "reserved" more space than it is actually using. I don't know if a backup/restore will cause the "reserved" space to go back to a correct value or not though.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 14, 2012 at 9:16 pm
Here is the piece that gets us free space:
SELECT CAST(size*8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)*8/1024 AS int) AS FreeSpaceMB
FROM sys.database_files
Thoughts?
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply