February 1, 2013 at 9:21 am
Hi all ,
I am getting daily disk space issue on my PRD server.
below is the situation
DB XYZ total size = 508 GB
unallocated space is 21 GB.
unused space is 5 GB.
reserved space is 30 GB
can you please suggest why database uses huge unallocated, unused and reserved space.
what i need to do , to prevent this.
Regards,
Shivrudra W
February 1, 2013 at 9:38 am
total size included the log file. What size is your transaction log?
---------------------------------------------------------------------
February 1, 2013 at 11:34 am
database is in simple recovery mode, log file is below 1GB
Regards,
Shivrudra W
February 1, 2013 at 12:37 pm
You could shrink database to release the unused space anyways..and set to auto growth on data file.
Even though your database is in simple mode, the log file will grow bigger in size if there is open transactions and that data will not be committed to disk untill it completed the transaction.
February 1, 2013 at 12:44 pm
For a 500GB db, those are modest amounts of space to be available/reserved.
I think trying to shrink that db would do far more harm than good.
If you have multiple data files, you might theoretically be able to gain from shrinking a specific file(s).
But the space is so little it doesn't seem worth it to me.
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".
February 1, 2013 at 12:51 pm
Your database is 1/2 TB in size and you have less than 1% unused. I don't see a problem.
February 2, 2013 at 3:41 am
the space figures you are reporting don't add up, reserved + unallocated should = database size - size of log file.
post the results of this query, obfuscate db info if you want
select
name, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply