Loading ...
Sorry, an error occurred while loading the content.
 

Database will not shrink

Expand Messages
  • David Fekke
    I have a transactional database that has balooned up to about 30 gigabytes on one of my test servers. I have been deleting data out of two of the main tables
    Message 1 of 6 , May 3, 2004
      I have a transactional database that has balooned up to about 30 gigabytes
      on one of my test servers. I have been deleting data out of two of the main
      tables to empty it down to a reasonable size. It is freeing space up in the
      database file, but it is not releasing it back to the operating system.

      I have tried the following commands in the database to clear up space with
      little or no avail.

      DBCC SHRINKFILE(databaseName_Data, EMPTYFILE)

      DBCC SHRINKDATABASE (databaseName,TRUNCATEONLY)

      DBCC CLEANTABLE (databaseName, tableName)

      BACKUP LOG databaseName WITH TRUNCATE_ONLY

      If anybody has any ideas, please let me know. I have been fooling around
      with this for about four days.

      Thank you,
      David Fekke.

      Database Administrator/Software Engineer
      Jagged Peak, Inc.
      2701 N. Rocky Point Dr. Suite 1250
      Tampa, FL 33607

      email: dfekke@...
      Phone: (813) 314-2950 ext. 254
      Fax: (813) 288-0685

      ****************************************************************************
      *************
      This e-mail message is intended only for the addressee(s) and contains
      information which may be confidential. If you are not the intended recipient
      please do not read, save, forward, disclose or copy the contents of this
      e-mail. If this e-mail has been sent to you in error, please delete this
      e-mail and any copies or links to this e-mail completely and immediately
      from your system and notify the sender immediately. We also like to inform
      you that communication via e-mail over the Internet is insecure because
      third parties may have the possibility to access and manipulate e-mails.

      Any views expressed in this message are those of the individual sender,
      except where the sender specifically states them to be the views of Jagged
      Peak, Inc.
      ****************************************************************************
      *************
    • Jeff Garbus
      Easiest solution: detach db, delete log file, reattach. ... regards, Jeff Garbus www.soaringeagle.biz 813 949 7016 (o) 813 354 4681 (f)
      Message 2 of 6 , May 3, 2004
        Easiest solution: detach db, delete log file, reattach.

        On Mon, 3 May 2004 6:35pm, David Fekke wrote:
        > I have a transactional database that has balooned up to about 30
        > gigabytes
        > on one of my test servers. I have been deleting data out of two of the
        > main
        > tables to empty it down to a reasonable size. It is freeing space up in
        > the
        > database file, but it is not releasing it back to the operating system.
        >
        > I have tried the following commands in the database to clear up space
        > with
        > little or no avail.
        >
        > DBCC SHRINKFILE(databaseName_Data, EMPTYFILE)
        >
        > DBCC SHRINKDATABASE (databaseName,TRUNCATEONLY)
        >
        > DBCC CLEANTABLE (databaseName, tableName)
        >
        > BACKUP LOG databaseName WITH TRUNCATE_ONLY
        >
        > If anybody has any ideas, please let me know. I have been fooling
        > around
        > with this for about four days.
        >
        > Thank you,
        > David Fekke.
        >
        > Database Administrator/Software Engineer
        > Jagged Peak, Inc.
        > 2701 N. Rocky Point Dr. Suite 1250
        > Tampa, FL 33607
        >
        > email: dfekke@...
        > Phone: (813) 314-2950 ext. 254
        > Fax: (813) 288-0685
        >
        > ****************************************************************************
        > *************
        > This e-mail message is intended only for the addressee(s) and contains
        > information which may be confidential. If you are not the intended
        > recipient
        > please do not read, save, forward, disclose or copy the contents of
        > this
        > e-mail. If this e-mail has been sent to you in error, please delete
        > this
        > e-mail and any copies or links to this e-mail completely and
        > immediately
        > from your system and notify the sender immediately. We also like to
        > inform
        > you that communication via e-mail over the Internet is insecure because
        > third parties may have the possibility to access and manipulate
        > e-mails.
        >
        > Any views expressed in this message are those of the individual sender,
        > except where the sender specifically states them to be the views of
        > Jagged
        > Peak, Inc.
        > ****************************************************************************
        > *************
        >
        >
        >
        > ------------------------ Yahoo! Groups Sponsor
        >
        >
        > Yahoo! Groups Links
        >
        >
        >

        regards,

        Jeff Garbus
        www.soaringeagle.biz
        813 949 7016 (o)
        813 354 4681 (f)
      • Pam Barker
        I ve had the same problem in the past and found that I needed to defrag the tables first. The tables are occupying many more than pages than they should. Once
        Message 3 of 6 , May 3, 2004
          I've had the same problem in the past and found that I needed to defrag the
          tables first. The tables are occupying many more than pages than they should.
          Once they're defragged, you'll be able to shrink the database. I personally like
          to shrink files rather than the entire database, but either one should work.

          -Pam

          On Mon, 3 May 2004 18:33:12 -0400, "David Fekke" wrote:





          I have a transactional database that has balooned up to about 30 gigabytes
          on one of my test servers. I have been deleting data out of two of the main
          tables to empty it down to a reasonable size. It is freeing space up in the
          database file, but it is not releasing it back to the operating system.

          I have tried the following commands in the database to clear up space with
          little or no avail.

          DBCC SHRINKFILE(databaseName_Data, EMPTYFILE)

          DBCC SHRINKDATABASE (databaseName,TRUNCATEONLY)

          DBCC CLEANTABLE (databaseName, tableName)

          BACKUP LOG databaseName WITH TRUNCATE_ONLY

          If anybody has any ideas, please let me know. I have been fooling around
          with this for about four days.

          Thank you,
          David Fekke.

          Database Administrator/Software Engineer
          Jagged Peak, Inc.
          2701 N. Rocky Point Dr. Suite 1250
          Tampa, FL 33607

          email: dfekke@...
          Phone: (813) 314-2950 ext. 254
          Fax: (813) 288-0685

          ****************************************************************************
          *************
          This e-mail message is intended only for the addressee(s) and contains
          information which may be confidential. If you are not the intended recipient
          please do not read, save, forward, disclose or copy the contents of this
          e-mail. If this e-mail has been sent to you in error, please delete this
          e-mail and any copies or links to this e-mail completely and immediately
          from your system and notify the sender immediately. We also like to inform
          you that communication via e-mail over the Internet is insecure because
          third parties may have the possibility to access and manipulate e-mails.

          Any views expressed in this message are those of the individual sender,
          except where the sender specifically states them to be the views of Jagged
          Peak, Inc.
          ****************************************************************************
          *************










          Yahoo! Groups Sponsor


          ADVERTISEMENT












          Yahoo! Groups Links

          To visit your group on the web, go to:http://groups.yahoo.com/group/tampasql/%c2%a0
          To unsubscribe from this group, send an email
          to:tampasql-unsubscribe@yahoogroups.com 
          Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
        • David Fekke
          It really is not the .LDF file that is the problem, it is the .MDF file. That is the file that is the file that is 30 gigs. The BACKUP LOG databaseName WITH
          Message 4 of 6 , May 3, 2004
            It really is not the .LDF file that is the problem, it is the .MDF file.
            That is the file that is the file that is 30 gigs.

            The 'BACKUP LOG databaseName WITH TRUNCATE_ONLY' seems to take care of the
            tranaction log for me. I can get it down to about 2 megabytes.



            -----Original Message-----
            From: Jeff Garbus [mailto:jeff@...]
            Sent: Monday, May 03, 2004 6:58 PM
            To: tampasql@yahoogroups.com; tampasql@yahoogroups.com
            Subject: Re: [tampasql] Database will not shrink


            Easiest solution: detach db, delete log file, reattach.

            On Mon, 3 May 2004 6:35pm, David Fekke wrote:
            > I have a transactional database that has balooned up to about 30
            > gigabytes
            > on one of my test servers. I have been deleting data out of two of the
            > main
            > tables to empty it down to a reasonable size. It is freeing space up in
            > the
            > database file, but it is not releasing it back to the operating system.
            >
            > I have tried the following commands in the database to clear up space
            > with
            > little or no avail.
            >
            > DBCC SHRINKFILE(databaseName_Data, EMPTYFILE)
            >
            > DBCC SHRINKDATABASE (databaseName,TRUNCATEONLY)
            >
            > DBCC CLEANTABLE (databaseName, tableName)
            >
            > BACKUP LOG databaseName WITH TRUNCATE_ONLY
            >
            > If anybody has any ideas, please let me know. I have been fooling
            > around
            > with this for about four days.
            >
            > Thank you,
            > David Fekke.
            >
            > Database Administrator/Software Engineer
            > Jagged Peak, Inc.
            > 2701 N. Rocky Point Dr. Suite 1250
            > Tampa, FL 33607
            >
            > email: dfekke@...
            > Phone: (813) 314-2950 ext. 254
            > Fax: (813) 288-0685
            >
            >
            ****************************************************************************
            > *************
            > This e-mail message is intended only for the addressee(s) and contains
            > information which may be confidential. If you are not the intended
            > recipient
            > please do not read, save, forward, disclose or copy the contents of
            > this
            > e-mail. If this e-mail has been sent to you in error, please delete
            > this
            > e-mail and any copies or links to this e-mail completely and
            > immediately
            > from your system and notify the sender immediately. We also like to
            > inform
            > you that communication via e-mail over the Internet is insecure because
            > third parties may have the possibility to access and manipulate
            > e-mails.
            >
            > Any views expressed in this message are those of the individual sender,
            > except where the sender specifically states them to be the views of
            > Jagged
            > Peak, Inc.
            >
            ****************************************************************************
            > *************
            >
            >
            >
            > ------------------------ Yahoo! Groups Sponsor
            >
            >
            > Yahoo! Groups Links
            >
            >
            >

            regards,

            Jeff Garbus
            www.soaringeagle.biz
            813 949 7016 (o)
            813 354 4681 (f)




            Yahoo! Groups Links
          • Jeffrey R. Garbus
            FYI, if the files are that badly fragmented, reindexing (or dropping & recreating the clustered index) is probably a timely thing to do. ... From: Pam Barker
            Message 5 of 6 , May 3, 2004
              FYI, if the files are that badly fragmented, reindexing (or dropping &
              recreating the clustered index) is probably a timely thing to do.

              ----- Original Message -----
              From: "Pam Barker" <pam@...>
              To: <tampasql@yahoogroups.com>
              Cc: <tampasql@yahoogroups.com>
              Sent: Monday, May 03, 2004 7:03 PM
              Subject: Re: [tampasql] Database will not shrink


              I've had the same problem in the past and found that I needed to defrag the
              tables first. The tables are occupying many more than pages than they
              should.
              Once they're defragged, you'll be able to shrink the database. I personally
              like
              to shrink files rather than the entire database, but either one should work.

              -Pam

              On Mon, 3 May 2004 18:33:12 -0400, "David Fekke" wrote:





              I have a transactional database that has balooned up to about 30 gigabytes
              on one of my test servers. I have been deleting data out of two of the main
              tables to empty it down to a reasonable size. It is freeing space up in the
              database file, but it is not releasing it back to the operating system.

              I have tried the following commands in the database to clear up space with
              little or no avail.

              DBCC SHRINKFILE(databaseName_Data, EMPTYFILE)

              DBCC SHRINKDATABASE (databaseName,TRUNCATEONLY)

              DBCC CLEANTABLE (databaseName, tableName)

              BACKUP LOG databaseName WITH TRUNCATE_ONLY

              If anybody has any ideas, please let me know. I have been fooling around
              with this for about four days.

              Thank you,
              David Fekke.

              Database Administrator/Software Engineer
              Jagged Peak, Inc.
              2701 N. Rocky Point Dr. Suite 1250
              Tampa, FL 33607

              email: dfekke@...
              Phone: (813) 314-2950 ext. 254
              Fax: (813) 288-0685

              ****************************************************************************
              *************
              This e-mail message is intended only for the addressee(s) and contains
              information which may be confidential. If you are not the intended recipient
              please do not read, save, forward, disclose or copy the contents of this
              e-mail. If this e-mail has been sent to you in error, please delete this
              e-mail and any copies or links to this e-mail completely and immediately
              from your system and notify the sender immediately. We also like to inform
              you that communication via e-mail over the Internet is insecure because
              third parties may have the possibility to access and manipulate e-mails.

              Any views expressed in this message are those of the individual sender,
              except where the sender specifically states them to be the views of Jagged
              Peak, Inc.
              ****************************************************************************
              *************










              Yahoo! Groups Sponsor


              ADVERTISEMENT












              Yahoo! Groups Links

              To visit your group on the web, go
              to:http://groups.yahoo.com/group/tampasql/
              To unsubscribe from this group, send an email
              to:tampasql-unsubscribe@yahoogroups.com
              Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.




              Yahoo! Groups Links
            • David Fekke
              I guess this command might do the trick DBCC INDEXDEFRAG (databaseName,tableName,PK_IndexName) I have solved the problem in the mean time. I dropped the entire
              Message 6 of 6 , May 4, 2004
                I guess this command might do the trick

                DBCC INDEXDEFRAG (databaseName,tableName,PK_IndexName)

                I have solved the problem in the mean time. I dropped the entire database
                along with all of the logs and other files. I recreated it with the table
                definition scripts and lookup tables. It freed up about 31 gigabytes of
                space.

                I got impatient with it, but it fixed the problem.

                Thanks for the help.
                David Fekke.

                -----Original Message-----
                From: Jeffrey R. Garbus [mailto:jeff@...]
                Sent: Monday, May 03, 2004 8:19 PM
                To: tampasql@yahoogroups.com
                Cc: tampasql@yahoogroups.com
                Subject: Re: [tampasql] Database will not shrink


                FYI, if the files are that badly fragmented, reindexing (or dropping &
                recreating the clustered index) is probably a timely thing to do.

                ----- Original Message -----
                From: "Pam Barker" <pam@...>
                To: <tampasql@yahoogroups.com>
                Cc: <tampasql@yahoogroups.com>
                Sent: Monday, May 03, 2004 7:03 PM
                Subject: Re: [tampasql] Database will not shrink


                I've had the same problem in the past and found that I needed to defrag the
                tables first. The tables are occupying many more than pages than they
                should.
                Once they're defragged, you'll be able to shrink the database. I personally
                like
                to shrink files rather than the entire database, but either one should work.

                -Pam

                On Mon, 3 May 2004 18:33:12 -0400, "David Fekke" wrote:





                I have a transactional database that has balooned up to about 30 gigabytes
                on one of my test servers. I have been deleting data out of two of the main
                tables to empty it down to a reasonable size. It is freeing space up in the
                database file, but it is not releasing it back to the operating system.

                I have tried the following commands in the database to clear up space with
                little or no avail.

                DBCC SHRINKFILE(databaseName_Data, EMPTYFILE)

                DBCC SHRINKDATABASE (databaseName,TRUNCATEONLY)

                DBCC CLEANTABLE (databaseName, tableName)

                BACKUP LOG databaseName WITH TRUNCATE_ONLY

                If anybody has any ideas, please let me know. I have been fooling around
                with this for about four days.

                Thank you,
                David Fekke.

                Database Administrator/Software Engineer
                Jagged Peak, Inc.
                2701 N. Rocky Point Dr. Suite 1250
                Tampa, FL 33607

                email: dfekke@...
                Phone: (813) 314-2950 ext. 254
                Fax: (813) 288-0685

                ****************************************************************************
                *************
                This e-mail message is intended only for the addressee(s) and contains
                information which may be confidential. If you are not the intended recipient
                please do not read, save, forward, disclose or copy the contents of this
                e-mail. If this e-mail has been sent to you in error, please delete this
                e-mail and any copies or links to this e-mail completely and immediately
                from your system and notify the sender immediately. We also like to inform
                you that communication via e-mail over the Internet is insecure because
                third parties may have the possibility to access and manipulate e-mails.

                Any views expressed in this message are those of the individual sender,
                except where the sender specifically states them to be the views of Jagged
                Peak, Inc.
                ****************************************************************************
                *************










                Yahoo! Groups Sponsor


                ADVERTISEMENT












                Yahoo! Groups Links

                To visit your group on the web, go
                to:http://groups.yahoo.com/group/tampasql/
                To unsubscribe from this group, send an email
                to:tampasql-unsubscribe@yahoogroups.com
                Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.




                Yahoo! Groups Links









                Yahoo! Groups Links
              Your message has been successfully submitted and would be delivered to recipients shortly.