June 20, 2008 at 9:09 am
When viewing the properties of our two main databases through enterprise manager the space available is always 0.00. I removed the auto shrink setting (not sure why it was there to begin with) but the space available has not changed. Both db's are set to auto grow. I am not certain on the fill factor of the pages, but I can see that the average page densities are less than 75% for both db's.
Long story short, should I be alarmed by seeing 0.00 space available consistently? Are there any possible performance issues to look out for with this?
June 20, 2008 at 11:05 am
You could improve the accurary of the report with
DBCC UPDATEUSAGE
( { 'database_name' | 0 }
[ , { 'table_name' | 'view_name' }
[ , { index_id | 'index_name' } ] ]
)
[ WITH [ COUNT_ROWS ] [ , NO_INFOMSGS ]
]
June 20, 2008 at 11:08 am
I'd pick a good time, and grow the files myself. In other words - don't wait for it to auto-grow. auto-grow usually happens at the worse possible time (i.e. when server is busy). Give you database room to grow into, don't make them go out and get their own space.
Also - check your maintenance plans to ensure that they're not shrinking your databases.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 20, 2008 at 11:22 am
I would recommend scheduling a time when you can grow your database files. You want to have at least 20% space available in the data file (not the combined available in both data and logs).
If your database is always showing 0.00% space available, that means you are experience auto growth on a regular basis. Auto growth is expensive and will cause performance issues. You should monitor the space available and schedule expanding the data file as needed.
And finally, what is the recovery model? If it is full recovery model - how often do you backup the transaction logs? If you are not backing up the transaction logs - your log file will always be full and continue to grow also.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2008 at 11:55 am
Both are in Full recovery mode and the transaction logs are backed up every hour. I plan to keep an eye on the db sizes, and will also grow both files on off time soon.
June 20, 2008 at 2:02 pm
Update-
Just for the two databases in question, Enterprise Manager shows 0.00 space available, but when you go to shrink the db's in EM and click to show files, there is free space shown in the db's. Huh?
June 20, 2008 at 4:56 pm
Run a DBCC UPDATEUSAGE for those databases. Then look again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 23, 2008 at 10:40 am
Thanks Jeffrey, EM now displays the appropriate space information. From what I have read, admins may have to run this when updating to SQL 2k5, but should not after that, or on fresh installs of SQL 2k5? I could not find any indications as to why someone would have to run this in SQL 2k though. Any insight?
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply