| Author |
Topic |
|
jazzcatone
Starting Member
12 Posts |
 Posted - 2010-11-30 : 16:51:10
|
| Question: Hi and thanks for taking a moment. I have a SQL Server 2005 database that I am trying to proactively maintain. I have some questions that I have been reading up on and I guess I am still trying to peice together the big picture as it were. Trying to understand what my best courses of action will be in the future. Will give you some background info. I have 1.15 MB of space to work with between both the .mdf and .ndf for this database. Transaction log backups are run every hour. I am using a Full recovery model and Full backups are taken once a night. Auto Shrink is set to True. The autogrowth is set to "By 10% Unrestricted Growth" for the .mdf,ldf,and .ndf. The database in generally holds about 4 years worth of sales data. We are a small company, and although this database is critical to everyday activities, I would not say we are really running very many heavy duty transactions against it. (if that makes any sense at all...) no huge data imports or anything like that So the questions I have are as follows 1)What exactly is sql "autogrowth" ? I see alot of articles explaining the pros and cons without actually going into what it is. More specifically, does it allocate more disk space to the database file?2) When I look at the 1.15 MB of empty space, my first reaction is to think at some point in the near future, I will have to do a shrink of the db. However, I have read a lot of stuff that says regular shrinking is not neccessarily a good thing. (http://www.karaszi.com/SQLServer/info_dont_shrink.asp) Will I have to do a shrink in the future? Or,3) Is there simply a way to allocate more diskspace to the datafiles, hence purhaps eliminating the need for a shrink.Any general advice given would be greatly appreciated and thanks in advance. JasonJason |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-11-30 : 16:58:32
|
quote:
I have 1.15 MB of space to work with between both the .mdf and .ndf for this database.
On disk? Or inside the database files? quote:
Auto Shrink is set to True.
Turn it off. It is not a good idea at all.quote:
What exactly is sql "autogrowth"
It means what will it do once there is no space in the file. With your settings, it's going to extend the files out per your settings. As more data comes in, it'll keep doing that until disk space runs out. I don't see any reason why you would need to shrink your database files with the information you provided.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-30 : 17:15:35
|
| If you decided to delete some old stale data (the oldest year once you ar storing 5 years of data) then you might shrink the database.However, if you are not desperate to reclaim the disk space then within a year the database will have filled up that space with new transactions, (and then you will delete the oldest year again), so you might as well leave SQL with that free space.If you repeatedly Shrink then Grow a database (which is what your AutoShrink setting will do) then the database will become fragmented and performance will suffer."I have a SQL Server 2005 database that I am trying to proactively maintain."Make sure you:BackupRestore to another machine periodically (to prove that you can) and do a DBCC CHECKDB on the second machine's databaseRebuild indexes regularlyUpdate statistics regularlyKeep a record of the file sizes and whether they grow over timeReview how long queries are taking. Have a Baseline so that you will know, in the future, if queries are slower or faster than before."Transaction log backups are run every hour."Consider changing this to 15 minutes. At the moment you may lose a whole hours work. Just by changing the schedule you can reduce that to 15 minutes (more frequently is probably not advisable because you will have a lot of small files if you need to Restore) |
|
|
|
jazzcatone
Starting Member
12 Posts |
Posted - 2010-12-01 : 12:26:05
|
quote:
Originally posted by tkizer
quote:
I have 1.15 MB of space to work with between both the .mdf and .ndf for this database.
On disk? Or inside the database files? I was speaking about inside the data files. Sorry for the confusionquote:
Auto Shrink is set to True.
Turn it off. It is not a good idea at all.quote:
What exactly is sql "autogrowth"
It means what will it do once there is no space in the file. With your settings, it's going to extend the files out per your settings. As more data comes in, it'll keep doing that until disk space runs out. I have read autogrowth is not really a good strategy for long term database maintenance. Although disk space availability is not an immediate concern, it seems I would want to limit the amount of space the datafiles could grow to. I read I could set a MAXSIZE property to do this. Was wondering what your opinion was on this. Any feedback would be greatly appreciated. I don't see any reason why you would need to shrink your database files with the information you provided.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Jason |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-01 : 12:33:07
|
| I disagree completely that autogrowth is not a good strategy. Although you shouldn't allow your database files to actually autogrow, you should having the setting there just in case. Ideally, you would manually extend out the file during a maintenance window and autogrow never kicks in. But autogrow is enabled just in case something weird happens or if you didn't act quick enough to a manual extend. We like autogrow there because it means no downtime. If autogrow kicks in ever, and if we get close to running out of disk space due to the autogrow, then our monitoring software will catch the condition and email or page us (depending on the severity). Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jazzcatone
Starting Member
12 Posts |
Posted - 2010-12-01 : 18:27:26
|
| Thanks for all yopur feedback with this. On more quick follow up question then. If I don't want my database to constantly auto grow, then how do I allocate more disk space to it. Also, what do you mean when you say "manually extend out the file" . Is this the same thing ?JasonJason |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-01 : 18:30:42
|
| And make sure your growth setting isn't the default! If ever it does autogrow, you don't want it to grow in 1MB or 10% chunks (depending upon what version you are using). Use a reasonable number, such as 512MB. This is especially important for the LDF to prevent excessive VLFs (read Paul Randal's and Kimberly Tripp's blogs for more info). On our VLDB, we have the LDF autogrow setting at 5GB.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-01 : 18:54:35
|
| "If I don't want my database to constantly auto grow, "How will you handle users adding more data? It will make the database larger. If the data fills up then users will get errors and if there are any places in the application where you are not using transactions properly (can happen, even in a well written application) then you will get data incompletely written to disk - nightmare to sort out!Our system generated a 26GB Log backup this morning at 06:00. The log file is hand-optimised to 16GB, and designed to not exceed this. But it did ... I have no idea why, as yes, but I not have the hassle of re-optimising the Log file back down to 16GB (or deciding it needs to be 32GB). Either way, I am very glad that it auto-extended first because I would have been paged in the middle of the night and secondly because we are extremely stretched at the moment and apart from detecting that it happened I certainly don't have time to deal with it!You can delete / purge old/stale data and MAYBE! your database will always remain the same size. BUT ... turning on AutoGrow does not mean that your database WILL grow, just that if it needs to it will do so automatically without giving all the users fatal errors.You can shrink it back down again after you delete some data (but repeated shrinking is bad - it will fragment the data, and the physical file, so avoid that except as an emergency remedy.I'm not sure why you are so hung up about NOT having Auto Grow. If you explain that maybe we can answer the question more helpfully. I doubt there are many/any DBas here who have Auto Grow turned off ... |
|
|
|
jazzcatone
Starting Member
12 Posts |
Posted - 2010-12-03 : 00:04:09
|
| Hi Kristen, Thanks again for your input. I should be clear I'm not dead set against Auto Grow. Infact I will probably will use it to some extent. Its just that from what I have read, auto grow is suppose to be more for the scenario which you spoke about where the dba doesn't need to be woke up in the middle of the night due to datafile space issues. However, its my understanding that it should not be a crutch per say, and I guess thats what I meant when I referred to it as a "long term strategy". Also, I do only have a finite amount of disk space and generally the the more bloated the database file the less well it performs. So there does seem to be an arguement for using a maxsize value to limit the amount of disk space I can allocate to the data.Jason |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-03 : 03:02:05
|
"I will probably will use it to some extent."I don't see how that works - either it is on, or not! Its not as though you can turn it on just before the database gets full ...Turn it on.Set up an Alert using Performance Monitor so that every time it happens you get notified (either urgently, or in "Yesterdays Alerts List")Decide what to do when you see how much it is happeningHave another alert when you are running out of disk space. That should be more urgent - or maybe you have a low priority alert at 70% full, and a high priority alert at 80%.I just can't see any justification for having a fixed size with no auto grow. It will just lead to something breaking when the database becomes full.If you have runaway growth, ever, the cause needs finding and fixing.Having AutoGrow on doesn't lead to bloat (unless it is unmanaged). If the database needs 10GB then that's what it needs. If you need the database to be only 5GB then you can work to achieve that - shorten the retention period for stale data; increase the frequency of Log Backups; use more sophisticated maintenance that only rebuilds indexes that needs it rather than "rebuild everything in one go" [which is what most maintenance plans do, and is what governs the size of the database/logs for probably the majority of Small/Medium systems]Here's a question:When the database fills up no one will be able to add any data. They may not even be able to report (if reports store preferences, or recent queries, or use working-space).How quickly will you be able to fix it? How will you fix it in fact? You can't just increase the size, right? that's what Auto Grow does for you ....I don't think your users, or boss, will be happy with this approach! but YMMV  |
|
|
|
|
Trying to Maintain a SQL Database