Autogrowth problem of SQL Server 2005

Question

Hi Experts,

 We are using SQL Server2005 Sp3.

Database Auto-growth is ON by "10% unrestricted" growth rate for all data files.

 

But i observed that during last 8 months, auto-growth is triggered for 2 times, but growing only MDF file.

other 2 NDF files are not growing even both are full.

 

All datafiles resides on same Logical Drive.

 

Please suggest why only 1 file out of all 3 files is growing by automatic growth..??

 

 

 

Thanks in Advance

 

Regards

Surjit

Answer 1

Hello Surjit,

The data files will grow, if its required.

Is there any possibilities that the secondary data files are read-only?

---

SKG: Please Marked as Answered, if it resolves your issue. (b: http://sudeeptaganguly.wordpress.com)

Answer 2

Hello Surjit,

Mybe just because only data are inserted into table located in the MDF file, and there are no data added to tables located in the NDFs?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 3

What objects have you put on secondary files?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Sir,

Let me clarify a bit more...... We are using SQL Server with SAP ERP.

Previously, there was SQLServer 2000, 3 Datafiles on 3 different logical drives, with a fixed growth rate (say 40GB for each) & growth was homogeneous for all datafiles.

Recently(actually 8 months back), we upgraded to SQL server2005, 3 datafiles on same logical drives, with growth rate in percentage(%) of original size & observed the growth phenomena changed.

It left me confused over changed behavior of SQL server as m not able to trace any inconsistency at any level even examining all logs carefully.

Is there any logical concept behind this behavior that i need to taken into care??

Please guide.......

Answer 5

Surjit,

It leads to the same question, what is stored on the secondary data files (.ndf)? If it store old archive data, then there is a possiblity that no data is archived during the above periods. As Olaf mentioned earlier, its possible that the current insert/updates are occuring on the tables located in the Primary File group. As its internal to your organization, it might be a good idea, to check with a DBA, what is there on the secondary files.

If the secondary files are set with a fixed maximum size, then its possible that the file will not grow beyond that fixed maximum file size, although you should get an error in SQL error log/application events/Monitoring software, if it is configured.

Hope, this may help :)

---

SKG: Please Marked as Answered, if it resolves your issue. (b: http://sudeeptaganguly.wordpress.com)

Answer 6

Sudeepta ji,

Thanks for ur support...... All data files contain live transactional data.... And no size restriction is there on any file......

In fact application counters are showing that

kB written / request    DATA1.mdf    10.100
kB written / request    DATA2.ndf    8.589
kB written / request    DATA3.ndf    10.508

kB written / sec    DATA1.mdf    89.225
kB written / sec    DATA2.ndf    16.883
kB written / sec    DATA3.ndf    37.330

It reflects data is being written into all files, but space left in DATA2 & DATA3 is not more than 10 MB since last 8 months.

**I also observed SQL Server "Empties" some of used space from NDF files while restart i.e. on many days I observed:

DATA2 size at day end (372,992)MB, At Next day startup after cold backup (372,984)MB

(note:- Filled space decreases, empties 8MB of DATA2 and this is regular behavior)

Regards

Surjit

Answer 7

USE <Database_Name>;
GO
SELECT type_desc, name, physical_name, size, growth, max_size, is_read_only, is_percent_growth
FROM sys.database_files

Can you please share the resultset for above t-sql?

Answer 8

To further elaborate Olaf's guidance, the link from Pinal would assist is locating the filegroups for the tables. You could monitor the table counts and infer as so to which tables/files are growing.

-Jeelani

Answer 9

Hi Experts,

 We are using SQL Server2005 Sp3.

Database Auto-growth is ON by "10% unrestricted" growth rate for all data files.

hello Surjit

please change the growth factor to an absolute value for both data and transaction log as the default setting 10% are absolutely nonsense.

with the default settings (10%) you'll get probably a heavily fragmented data files and definitively an extremely internal fragmented log file which leads to performance issues.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 10

Thanks to All for your valuable suggestions.....

Moreover We are going to do following counter measures...

a) Restricting size (max. size validation) for Rapidly growing datafile.(This would probably leads to use of other datafiles by SQL server for writing)

b) Changing Growth Rate of all datafiles from 10percent(%) to a fixed extent (say 40GB for each)

c) If required, growing the NDF files manually, so as to synchronize size of all datafiles to ensure data to be homogeneously distributed across all datafiles. 

Please let me know if you see any area of concern in above approach over a Live system.

Regards

Surjit

Answer 11

You can keep a watch on the disk space usage report for the database. You should have some free space available in the secondary files.

Restricting max size of the data file will not help much, you may monitor the filegrowth manually; however the autogrowth option should be ON as a failsafe mechanisim.

Hope, this may help :)

---

SKG: Please Marked as Answered, if it resolves your issue. (b: http://sudeeptaganguly.wordpress.com)

Answer 12

thanks for all valuable guidance........!!!!

Regards

Surjit