Simple recovery database bloating?
in [Server Configuration]
|
Prev: SQL 2005 DB Space Allocation
Next: Automated way to discover the structure of the result set of a stored procedure?
From: John Bell on 24 Jul 2010 08:47 On Sat, 24 Jul 2010 04:16:16 -0700 (PDT), Bero <robertatnova(a)gmail.com> wrote: >On Jul 24, 2:13�am, John Bell <jbellnewspo...(a)hotmail.com> wrote: >> On Fri, 23 Jul 2010 18:59:55 -0700 (PDT), Bero >> >> <robertatn...(a)gmail.com> wrote: >> >I have a several hundred GB simple recovery dev database bloats by >> >200-300 GB and does not release the free space to the OS. Sometimes >> >drive runs out of space because of this. I had to shrink database >> >every few months to bring back database to its size and release the >> >space back to OS. Database has autogrow turned on. �Question is what >> >can I do on a dailhy/weekly basis to prevent the database to grow that >> >big so that I do not run out of space on the drive? What makes this >> >grow and not release? >> >> Hi >> >> You should monitor the filegrowth and try �to work out what is causing >> this. Look at the transactions you have an make sure that they are >> short and have error handliing. >> >> John >********************************************************************************************************* >Database is set to simple recovery. Developers use this database quit >a bit and do add, drop, combine, drop objects etc. Question still is >what makes database to grow 200-300 GB? I am still trying to solve the >mistery. If developers have free reign on a production system then you are asking for problems and you should instigate a proper change control process. You should not need to change the size of the data file as this should grow at a rate proportional to the data being added. My guess is that you are doing excessive re-indexeing, possibly as a result of shrinking the data file. You may want to make sure that you don't have excessive page splitting because of poor index choices. John
From: Bero on 24 Jul 2010 09:47 On Jul 24, 8:47 am, John Bell <jbellnewspo...(a)hotmail.com> wrote: > On Sat, 24 Jul 2010 04:16:16 -0700 (PDT), Bero > > > > > > <robertatn...(a)gmail.com> wrote: > >On Jul 24, 2:13 am, John Bell <jbellnewspo...(a)hotmail.com> wrote: > >> On Fri, 23 Jul 2010 18:59:55 -0700 (PDT), Bero > > >> <robertatn...(a)gmail.com> wrote: > >> >I have a several hundred GB simple recovery dev database bloats by > >> >200-300 GB and does not release the free space to the OS. Sometimes > >> >drive runs out of space because of this. I had to shrink database > >> >every few months to bring back database to its size and release the > >> >space back to OS. Database has autogrow turned on. Question is what > >> >can I do on a dailhy/weekly basis to prevent the database to grow that > >> >big so that I do not run out of space on the drive? What makes this > >> >grow and not release? > > >> Hi > > >> You should monitor the filegrowth and try to work out what is causing > >> this. Look at the transactions you have an make sure that they are > >> short and have error handliing. > > >> John > >********************************************************************************************************* > >Database is set to simple recovery. Developers use this database quit > >a bit and do add, drop, combine, drop objects etc. Question still is > >what makes database to grow 200-300 GB? I am still trying to solve the > >mistery. > > If developers have free reign on a production system then you are > asking for problems and you should instigate a proper change control > process. > > You should not need to change the size of the data file as this should > grow at a rate proportional to the data being added. My guess is that > you are doing excessive re-indexeing, possibly as a result of > shrinking the data file. You may want to make sure that you don't have > excessive page splitting because of poor index choices. > > John- Hide quoted text - > > - Show quoted text - John, this is not a production system. It is a development database. Hardly any reindexing is done on this database. New data tables added, old ones dropped and data combines are constantly happening on this database.
From: Erland Sommarskog on 24 Jul 2010 12:56 Bero (robertatnova(a)gmail.com) writes: > This is a simple recovery database. It is the data file growing. In > the past this database was on another server and move to to this new > server by detach/attach process. I can check but I do not think any > replication was done in the past on this database. So do I do > shrinkfile on a weekly basis or continue doing shrinkdb every few > months? Replication could have been an issue if it had been the log file. You can find out which are the largest object in the database with this query: SELECT dbname = db_name(), [Table] = o.name, i.indid, [index] = i.name, i.rows, "size in MB" = convert(bigint, i.reserved*8192)/1000000 FROM sysindexes i JOIN sys.objects o ON i.id = o.object_id WHERE indexproperty(i.id, i.name, 'IsAutoStatistics') = 0 AND indexproperty(i.id, i.name, 'IsStatistics') = 0 AND indexproperty(i.id, i.name, 'IsHypothetical') = 0 ORDER BY i.reserved DESC If there is no big table when you try it, maybe the developers are creating junk tables which they fill with data and then drop. Shrinking the database is meaningless. You could just as well fight windmills. If the developers need 300 GB of headroom, they need 300 GB of headroom. If you disagree, set a max size on the database, and see if someone screams. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: John Bell on 24 Jul 2010 13:11
On Sat, 24 Jul 2010 06:47:53 -0700 (PDT), Bero <robertatnova(a)gmail.com> wrote: >On Jul 24, 8:47�am, John Bell <jbellnewspo...(a)hotmail.com> wrote: >> On Sat, 24 Jul 2010 04:16:16 -0700 (PDT), Bero >> >> >> >> >> >> <robertatn...(a)gmail.com> wrote: >> >On Jul 24, 2:13�am, John Bell <jbellnewspo...(a)hotmail.com> wrote: >> >> On Fri, 23 Jul 2010 18:59:55 -0700 (PDT), Bero >> >> >> <robertatn...(a)gmail.com> wrote: >> >> >I have a several hundred GB simple recovery dev database bloats by >> >> >200-300 GB and does not release the free space to the OS. Sometimes >> >> >drive runs out of space because of this. I had to shrink database >> >> >every few months to bring back database to its size and release the >> >> >space back to OS. Database has autogrow turned on. �Question is what >> >> >can I do on a dailhy/weekly basis to prevent the database to grow that >> >> >big so that I do not run out of space on the drive? What makes this >> >> >grow and not release? >> >> >> Hi >> >> >> You should monitor the filegrowth and try �to work out what is causing >> >> this. Look at the transactions you have an make sure that they are >> >> short and have error handliing. >> >> >> John >> >**************************************************************************�******************************* >> >Database is set to simple recovery. Developers use this database quit >> >a bit and do add, drop, combine, drop objects etc. Question still is >> >what makes database to grow 200-300 GB? I am still trying to solve the >> >mistery. >> >> If developers have free reign on a production system then you are >> asking for problems and you should instigate a proper change control >> process. >> >> You should not need to change the size of the data file as this should >> grow at a rate proportional to the data being added. My guess is that >> you are doing excessive re-indexeing, possibly as a result of >> shrinking the data file. You may want to make sure that you don't have >> excessive page splitting because of poor index choices. >> >> John- Hide quoted text - >> >> - Show quoted text - > >John, this is not a production system. It is a development database. >Hardly any reindexing is done on this database. New data tables added, >old ones dropped and data combines are constantly happening on this >database. Unless you have a large growth amount, then at some point you should have used just about all the space that was allocated at some point. John |