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: Bero on 23 Jul 2010 21:59 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?
From: John Bell on 24 Jul 2010 02:13 On Fri, 23 Jul 2010 18:59:55 -0700 (PDT), Bero <robertatnova(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
From: Erland Sommarskog on 24 Jul 2010 06:55 Bero (robertatnova(a)gmail.com) writes: > 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? First, what is growing: the data file or the log file? Second, SQL Server never releases free space to the OS. (Unless you have the autoshrink option, which is an extremely bad idea.) Next, for what you should do, depends on the answer to the first question. But while we're at it: do you use any form of replication? Have you done so in the past? -- 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: Bero on 24 Jul 2010 07:13 On Jul 24, 6:55 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Bero (robertatn...(a)gmail.com) writes: > > 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? > > First, what is growing: the data file or the log file? > > Second, SQL Server never releases free space to the OS. (Unless you have the > autoshrink option, which is an extremely bad idea.) > > Next, for what you should do, depends on the answer to the first question.. > > But while we're at it: do you use any form of replication? Have you done > so in the past? > > -- > Erland Sommarskog, SQL Server MVP, esq...(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 ******************************************************************************************************************* 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?
From: Bero on 24 Jul 2010 07:16
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. |