September 7, 2011 at 2:55 am
from Google i come to know that whenever we get the negative unallocated size if we run the DBCC updateusage we will get the accurate values. however it is taking time. is there any alternative for that?
Thanks in advance.
Kumar Reddy
September 7, 2011 at 6:12 am
What version are you using? Shouldn't happen after 2k!
September 7, 2011 at 6:24 am
There are still odd cases of it up til 2005 SP3. Past that though, no.
UpdateUsage shouldn't take long. I tested on a 5GB database - 12 seconds. Don't run it during peak usage, schedule it for a quiet time.
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
September 7, 2011 at 6:32 am
It is MsSql 2005 and up, that too i am dealing with database of size 20 to 50 GB. and i need to get the accurate size of the database at peak time.
Thanks in advance
Kumar
September 7, 2011 at 6:36 am
Why do you need accurate sizes up to the second and why can't you use the file sizes (which are always correct)
p.s. Patch to at least SP3, run UpdateUsage once and you shouldn't have more problems.
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
September 7, 2011 at 6:41 am
I personnally use backup history for this. Of course I do fulls only once per day so it might not be what you need.
September 7, 2011 at 6:42 am
Actually i am working with alarms. for that i am using the formula
unallocated_size=Size-allocated_size (getting from sysindexes);
as transaction log full, finally i am getting the negative unallocated size, which invoking the alarm.
what i did is ,
if @size<@allocated_size i am running this command.
DBCC UpdateUsage
after that i performing the unallocated_size=Size-allocated_size (getting from sysindexes);
it takes time when the condition satisfy, that Updateusage takes sharedlock, may block the process. so i need to know whether there are any alternatives can i use..?
Thanks for ur replies ...:)
September 7, 2011 at 6:52 am
There are no alternatives for UpdateUsage. It's the only thing that updates the PFS page usage counters.
Stop using sysindexes. It's deprecated and the row counts are known to be inaccurate. Use sys.partitions, the row counts are supposed to be accurate in there.
UpdateUsage has no effect on the size reported from the log file, so I don't know what the full tran log has to do with anything.
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
September 7, 2011 at 7:05 am
I am using sysindexes for Server 2000 and
sys.allocation_units for 2005 and up, to get the reservedpages ( Allocated Size).
is it enough to get accurate values.. ?
Thanks In Advance
Kumar
September 12, 2011 at 3:06 am
What do you want to do when you get all this information?
It is very unusual to need to know the database size on a frequent basis. It is possible you are doing something that does not need to be done.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply