Weird AutoGrow Behavior

[SJPS06]

I am having an interesting behavior on one of my MS SQL 2005 (Enteprise Edition) databases.

First, we are using a Partition scheme to partition several tables by month.

Here is the situation. Over the last several days I have had several AutoGrow events (ondata files not log file). Last summer I was having problems because when a previous person created these databases they created them at 1MB with 1MB autogrow, so I manually expanded every single datafile (primary and each of the 36 datafiles) to very large sizes and changed the autogrow to 100MB for primary file and 10MB for montly partition files. I have had long periods (including and entire 2 weeks over the holidays).

The Auto Grow events are happening in bunches (10-100+ in a few secone period). Sometimes the growth is in the primary file, sometime the growth is in one or several of the datafiles. Here is where it gets interesting...

All of these files have plenty of disk space available (see below). And I do not see any SQL Log Events or my application log events that show any activity (ANY) let alone anything being written to the database.

Any suggestions on what might be going on here would be great.

List of files and disk/available space:

snapshot_0010 29548 9719 19828.187500 0.329
PartionFG1 1324 300 1023.875000 0.227
PartionFG10 1374 307 1066.062500 0.224
PartionFG11 1464 325 1138.500000 0.222
PartionFG12 1804 400 1403.125000 0.222
PartionFG13 1554 335 1218.687500 0.216
PartionFG14 1594 352 1241.812500 0.221
PartionFG15 1664 363 1300.812500 0.218
PartionFG16 1554 338 1215.750000 0.218
PartionFG17 1744 418 1325.625000 0.240
PartionFG18 1684 415 1268.437500 0.247
PartionFG19 1644 511 1132.500000 0.311
PartionFG2 1644 627 1017.000000 0.381
PartionFG20 1644 500 1143.875000 0.304
PartionFG21 1644 645 998.937500 0.392
PartionFG22 1644 652 991.937500 0.397
PartionFG23 1644 713 930.062500 0.434
PartionFG24 1644 592 1051.625000 0.360
PartionFG25 1644 1485 159.000000 0.903
PartionFG26 1644 1643 0.062500 1.000
PartionFG27 1644 1643 0.062500 1.000
PartionFG28 1644 1643 0.062500 1.000
PartionFG29 1644 1643 0.062500 1.000
PartionFG3 1644 566 1077.375000 0.345
PartionFG30 1644 1643 0.062500 1.000
PartionFG31 1644 1643 0.062500 1.000
PartionFG32 1644 1643 0.062500 1.000
PartionFG33 1644 1643 0.062500 1.000
PartionFG34 1644 1643 0.062500 1.000
PartionFG35 1644 1643 0.062500 1.000
PartionFG36 1644 1643 0.062500 1.000
PartionFG4 1644 626 1017.062500 0.381
PartionFG5 1644 604 1039.937500 0.367
PartionFG6 1644 651 992.500000 0.396
PartionFG7 1644 648 995.062500 0.395
PartionFG8 1644 592 1051.625000 0.360
PartionFG9 1644 617 1026.750000 0.375
snapshot_0010_log 5128 4860 268.546875 0.948

 

[mrdenny]

The only time that SQL should be trying to grow the files is if data is moving within the data files. How is the partitioning setup? Are you using relitive dates? Is there a job to move data from one partition to another?

I'd want to take a look at your 10 Meg extents. How much data is inserted into each partition at a time. 10 Megs may not be enough.


Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog