October 14, 2016 at 8:53 am
Hi SQL Server Gods
I have a mirrored database configured as below:
One FileGroup - Primary
One MDF file 612,065MB init size, autogrowth 100MB, unlimited growth
One LDF file 20,200 init size, autogrowth 700mb, 90,0000
Both files on two separate drives with about 60% free space on each drive on principle and mirror server
Status:
Mirrors are Synchronized and set in High Performance mode
DBCC SQLPERF (LOGSPACE) shows log file is only 0.56% used
MDF file in Primary File Group currently allocated space is 612064.44 MB
available space is 1063.5MB (0%)
My question is why hasn't the MDF grew its set to unlimited growth, is it because there aren't enough transactions currently? Funny thing is when I try to increase the initial file size by a gig manually through SSMS GUI it reverts back to original init file size.
After a lot of googling I resorted to this forum. Please be easy on me I'm just a clueless manager trying to fill the role of a dba that left.
THANKS!
October 14, 2016 at 9:18 am
I'm no sure what you're asking regarding the unlimited size. A data file won't automatically grow to unlimited (and you wouldn't want it to, that's 16TB). It'll grow by its autoincrement sizes as necessary.
100MB is pretty small growth increment for a 600GB database.
As for the reverting back to size, check that you don't have autoshrink enabled and don't have any shrink jobs.
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
October 14, 2016 at 9:19 am
What's the free space in the data file? You can check in properties for the database, or use this:
exec sp_spaceused
Adding data won't grow this until the free space runs out.
If you want to add space, how are you doing it? If you want to use SSMS, please change the size, then click "Script" not OK. Paste the script here.
You want to know and understand the ALTER DATABASE command.
October 14, 2016 at 9:46 am
Thanks Steve the below are results of
database_namedatabase_sizeunallocated space
Skynet 632264.44 MB1069.05 MB
reserveddataindex_sizeunused
625659272 KB614870648 KB7833128 KB2955496 KB
I will try the script out of increasing database size and look into alter database docs on the net.
basically i keep getting alerts through monitoring tool that primary file group for db running low on space and want to resolve the issue.
October 15, 2016 at 10:23 am
You'll have to alter the database file and add space.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply