March 8, 2017 at 2:09 am
Hi
I am hoping for some advice, I have taken over the support of a SQL 2008 instance with a database that is currently growing at around 2.5GB a month (physical disk space used). Checking the free space in the database it always reports that there is 42-50% free space. If this is the case why is the mdf file growing? The size of the database is 37GB with around 17Gb free space available. Why could it be extending the database size instead of using the free space in the database. Is there any settings I can check?
thanks
Paul
March 8, 2017 at 2:24 am
Paul
Please will you show us the results of this query?
SELECT
name
, size/128 AS SizeMb
, growth/128 AS GrowthMB
, is_percent_growth
FROM MyDB.sys.database_files
John
March 8, 2017 at 2:31 am
name SizeMb GrowthMB is_percent_growth
_Data 36931 1024 0
_Log 200 50 0
John, as requested (edited db names)
thanks
Paul
March 8, 2017 at 2:42 am
Paul
Your log file is tiny, and your data file is growing by only a GB at a time. Is auto-shrink enabled?SELECT DATABASEPROPERTYEX('MyDB','isAutoShrink')
What are the usage patterns of your database? If it's a warehouse or reporting database that you are constantly clearing out and refreshing, that may explain why the amount of data in it fluctuates so much.
John
March 8, 2017 at 2:51 am
John
I am still getting to grips with each database on this instance and usage trends, auto-shrink is set to off. Database is in simple recovery mode.
It is a production database with no clear out and refresh. There is no batch process job to manipulate data.
I have recently changed the growth to 1Gb as it was set to 100 and was auto growing every day. I appreciate this might need to be revisited however I was more puzzled by the fact it is growing but maintaining a consistent free space of around 42-50%
thanks
Paul
March 8, 2017 at 3:02 am
Paul
Where do you get that figure of 42-50%? Please will you post the results of sp_spaceused? How big is the largest table in the database, and how often are its indexes rebuilt? When do the growth events occur? You can find that out from the default trace.
John
March 8, 2017 at 3:19 am
John
Database size is 37131MB, unallocated is 16842MB,
Largest table is 16GB with 46 million records, the indexes are completely full rebuild overnight, every night, I have been moving the databases on this instance way from generic maintenance plans as appropriate however given the number of dbs it is time consuming work.
Checking the growth events it is every night during the 'catch all' maintenance plan this database is part of.
thanks
Paul
March 8, 2017 at 3:30 am
Paul
That's your answer, then. Index rebuilds need space in the database, which is freed up at the end of the operation. Consider using Ola Hallengren's database maintenance solution to target your rebuilds so you're not using resources unnecessarily. You can use ALTER INDEX REORGANIZE instead of REBUILD to avoid the extra space requirement. Make sure you understand the pros and cons of that if you decide to go ahead. Also consider partitioning your large table so that you can rebuild only the sections that see heavy use and hence get fragmented.
I have to ask - this sounds like an OLTP database. Why do you not have it in Full recovery mode?
John
March 8, 2017 at 3:51 am
John
Thank you for your help, given the size of the database I was surprised to find the size of that one table,
I am moving databases slowly over to the Ola Hallengrens maintenance however this instance is in addition to my usual workload and not high priority wise, although it is still production so is still up there. I am lucky to have inherited its partner test server so I do have a luxury there.
Limited notes on why each db is in which recovery model, at this point in time I have sent out a series of questions highlighting concerns to the systems teams for each database/application including enquiring on restore requirements. Some do not need point in time however I am yet to hear back on this one. I need to justify each change in a Change Management request so I need system team buy in for each one.
thanks again
Paul
March 8, 2017 at 8:34 am
Although Ola's good code is a work of art, consider the following before you implement any index maintenance.
SQL Server doesn't actually care if the indexes are fragmented or not.
It's not a measure that the optimizer takes into consideration at all when it creates an execution plan. For the normal "single row" lookups, defragmenting won't actually help at all because each and every lookup has to go through the B-Tree anyway. For "range scans", it may not help either if the index is on a SAN because busy SANs have really active read/write heads that are taking care of everyone's requests on a time-share basis and there's no guarantees that any data is stored in contiguous sorted pages on the disk even if it's not a SAN.
I'm certainly open for debate but, IMHO, the ONLY thing that matters is the fullness of each page for the sake of memory because the whole page has to be present in memory even if you're reading from a single row. For the likes of "range scans", it can waste a lot of memory if the pages are, for example, only half full. Very few disk maintenance plans even consider page fullness.
I've not rebuilt the indexes on my main production box since 17 Jan 2016 (more than a year now). Yep... there's a little wasted space but not as much as I expected because of the "natural fill factor" that occurs after a page split happens. Performance noticeably improved on the floor especially for the first two weeks, I didn't have the flurry of blocking like we normally got (bad fill factors on a lot of indexes), and, on some tables, the size on disk (and therefor, in memory as well) dropped. Performance continued to gradually improve for the first 3 months for CPU and especially Reads.
Yeah... I know... it sounds like I'm drinking bong water with 2 straws but Brent Ozar (in particular and has several YouTubes on the subject) and Kendra Little have written bout it several times. I thought it would fail miserably but it has not. And, I have more time for my other nightly tasks and I don't have log file or data file explosions because of index maintenance any more.
The real KEY to it all is making sure that you keep statistics up to date, which you have to do whether you elect to do index maintenance or not. And, you have to do both types. Index statistics and any column statistics that may have been created auto-magically.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply