January 29, 2010 at 5:50 am
Database size is 289385.75 MB.
One of the production server have database free space issues, user they can not store any data, bcz there is no free space in this database, so i did run command dbcc updatausage, after that i got some space in data file (data_File1 ) around 5.31 MB.
File_size_MB, Space_used_MB, Free_space_MB
-----------------------------------------------------
124096.88 124091.56 5.31
(In datafile Data_File2, i could not get any free space after run dbcc updateusage, plz tell me, Is there any worng)
File_size_MB, Space_used_MB, Free_space_MB
---------------------------------------- -------------
165276.88 165276.88 00
Datafile setting is currently on this server as follows
Automatically grow file
Filegrowth in MB - 50
maximum file size option is Unrestricted file growth, for both data files.
Recovery model - full.
Autoshirnk - off.
as per above setting, daily database grow arround 1 GB. how to avoid keep on increasing datafile.
I want to get suggestion from your side, regarding to change database setting as follows
Automatically grow file
Filegrowth in MB - 10
maximum file size option is restricted file growth, for both datafiles. ( in restricted file growth how much size i have to mention)
Recovery model - simple.
Autoshirnk - on. ( this is for to shirnk log and data at every checkpoint)
Pease tell me, If i changed datafile setting above setting how much size to be mention in max file growth restricted option?
Thanks for advance.
January 29, 2010 at 6:21 am
Your Autogrowth value for a large database of 280 + Gigs is very very low (10 MB).
That needs to immediately addresses, I would look at having that value in multiple ,000 MB's such as 5000 MB or 3000 MB (It depends on how much you would like to and also depends on how much disk space those drives contain)
If you would like to restrict the file size, you need to know that if the database needs some space, it cannot grow and would end up going offline (users cannot use it immediately).
Changing the Recovery Model will break all Log Shipping and Transaction Log backups is not possible and it would not be possible to do a point in time recovery.. In your case it depends on how critical the database and its data for the organization.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply