January 17, 2012 at 1:03 pm
I have a database with a file that is around 300GB, data is being added to it's sole table daily
According to DBCC SHOWFILESTATS, this file only is only 50% occupied -- I would think that as data is added to the table the file size would remain the same but the used/unused ratio would tilt toward fully used
But NO, the file keeps growing and the used/unused ratio remains about the same
Why is this?
For your information:
- The file is the only file in its filegroup (not the primary filegroup)
- Filegroup contains one table and its clustered index (that table's non-clustered indexes live in a different filegroup -- this is an issue in that filegroup also)
- All indexes for that table are rebuilt daily (alter index rebuild) -- during rebuild the database is put in simple recovery mode to cut down on log file action and put back to full recovery mode afterwards
- Fill factor is 90
Any help is truly appreciated
January 17, 2012 at 1:39 pm
Have you tried something like this to be sure of the contents:
http://www.sqlservercentral.com/scripts/Miscellaneous/30852/
Are you sure the file is growing? Meaning have you detected auto grows, or monitored the actual file size? If there is some issue with SHOWFILESTATS, you would not see it. It should be very accurate, but I wonder if there is something going on.
Also, can you post the data size of the file, autogrowth settings, and then the size reported from something like this: http://www.sqlservercentral.com/scripts/sp_spaceused/64271/
January 17, 2012 at 1:50 pm
Are you shrinking the files daily along with your daily index rebuilds?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2012 at 1:59 pm
Would the index rebuild be causing the file to want to force an autogrow?
January 17, 2012 at 2:01 pm
(variation on your query)
SELECT un.name, ung.groupname , si.indid
FROM sysobjects un INNER JOIN sysindexes si ON un.id=si.id
INNER JOIN sysfilegroups ung ON si.groupid=ung.groupid
WHERE ung.groupname = ...
name (table)
groupname (fg)
indid 1.00
also the file does indeed grow, I can compare it in production with a copy of the db restored elsewhere a week ago
numbers from production:
sizemb 301,411.00
resmb 148,897.83
unused 152,513.17
unusedpct 0.51
growthmb 4,096.00
January 17, 2012 at 2:01 pm
Yes. If you are shrinking the file after the rebuild, you are fragmenting the index. The defrag job will require the file to grow in order to defrag the index.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2012 at 2:02 pm
no
January 17, 2012 at 2:04 pm
jgenovese (1/17/2012)
no
NO to the shrink?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2012 at 2:22 pm
correction, there is a daily shrink with TRUCNATEONLY, dont know what that does to file size if anything, but the overall trend is for growth
January 17, 2012 at 2:27 pm
am I correct in that TRUNCATEONLY does not move data, only frees unused space from the end of the file?
January 17, 2012 at 2:28 pm
re: index rebuild causing autogrow -- maybe, but I would think it would utilize the unused space first before growing
January 17, 2012 at 2:35 pm
Yes - reindex will use free space before causing an autogrow. Autogrow can occur if the data is large enough.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2012 at 3:12 pm
OK, I'm confused.
The TRUNCATE ONLY is for the backup of the log, correct?
Do you have a 300GB data file, or database? Are you shrinking the data file somehow? Or the log file? Check your maintenance to be sure.
When the database autogrows, it does so because there is no space in the data or log files. If you have a 50% fill on either (or both), and you add, say 10%, the files should not grow. However if you have shrunk the files down, then perhaps an autogrow occurs.
TRUNCATE ONLY should mark the space in the log as cleared, and it can be re-used, but it should not affect the size of the file.
January 17, 2012 at 3:19 pm
DBCC SHRINKFILE ... , TRUNCATEONLY
on the file in question itself, not the log
your'e confisuing it with:
BACKUP LOG ... WITH TRUNCATE_ONLY
January 17, 2012 at 3:41 pm
jgenovese (1/17/2012)
DBCC SHRINKFILE ... , TRUNCATEONLYon the file in question itself, not the log
your'e confisuing it with:
BACKUP LOG ... WITH TRUNCATE_ONLY
When you do this, you are releasing space to the OS.
http://technet.microsoft.com/en-us/library/ms189493.aspx
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply