Sfrgttk

How do we create new idioms and use them in a novel?

ESPP--any reason not to go all in?

Finding the minimum value of a function without using Calculus

Writing text next to a table

Was it really inappropriate to write a pull request for the company I interviewed with?

What can I do if someone tampers with my SSH public key?

What do you call someone who likes to pick fights?

Sampling from Gaussian mixture models, when are the sampled data independent?

Computation logic of Partway in TikZ

Is there a way to make cleveref distinguish two environments with the same counter?

How to install round brake pads

What does the Digital Threat scope actually do?

What would be the most expensive material to an intergalactic society?

Is "cogitate" used appropriately in "I cogitate that success relies on hard work"?

Can the Witch Sight warlock invocation see through the Mirror Image spell?

Should we avoid writing fiction about historical events without extensive research?

Are these two graphs isomorphic? Why/Why not?

Giving a career talk in my old university, how prominently should I tell students my salary?

When an outsider describes family relationships, which point of view are they using?

Why do phishing e-mails use faked e-mail addresses instead of the real one?

How to copy the rest of lines of a file to another file

Which country has more?

How can I portion out frozen cookie dough?

Smooth vector fields on a surface modulo diffeomorphisms

What's the most efficient way to reclaim unused DB hard drive space?

What's the most efficient UUID column typeHow to reclaim the unused Space in a databaseReclaim unused spaceShrinking database which is not having any insert/updateNeed assistance shrinking MDF FileBest Way to Reduce the Size of 8 TB DB?Is it a good idea to shrink prior to a SQL Server upgrade to a new instanceReclaim disk space from large database's primary filegroup after truncating tables (other than shrink)SQL Replication - Distribution database backlog of commands for one tableHow to reclaim unused space from clustered index table

0

Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having.

We have a database that unintentionally ballooned to 300+ GB. We have turned off the option that caused this in our application, but we want to purge the offending data (in only 1 table) and reclaim the space it was taking up on the hard drive. We have tried truncating the table and then shrinking it on a copy of the production database, but this process takes about a week.

Is there a quicker/better/more efficient way to do this? I have read that shrinking a database is a taboo among DBAs because it introduces index fragmentation.

We are using Microsoft SQL Server.

"Best way" translates to quickest and least likely to cause us issues in the future (like index fragmentation might). We are trying to make this as quick as possible so that our database won't be tied up for several days deleting data and shrinking files.

sql-server shrink truncate

share|improve this question

edited Oct 11 '16 at 22:39

ypercubeᵀᴹ

77.4k11134216

asked Oct 11 '16 at 21:19

martin122089martin122089

42

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

migrated from stackoverflow.com Oct 11 '16 at 21:28

This question came from our site for professional and enthusiast programmers.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It start with this msdn.microsoft.com/en-us/library/ms189493.aspx I don't think you can shrink a single table file only. When done then rebuild the fractured indexes.
  
  – paparazzo
  Oct 11 '16 at 21:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please add version (2008, 2008R2, 2012, etc) and edition (Standard, Enterprise, Azure, etc)
  
  – ypercubeᵀᴹ
  Oct 11 '16 at 22:42
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I think you'll most likely have to go with Randolph's solution, but you 'might' get some relief if you try SHRINKFILE TRUNCATEONLY - (msdn.microsoft.com/en-us/library/ms189493.aspx) - Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
  
  – Scott Hodgin
  Oct 12 '16 at 9:44
  

  
  
  
  

add a comment | 

0

Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having.

We have a database that unintentionally ballooned to 300+ GB. We have turned off the option that caused this in our application, but we want to purge the offending data (in only 1 table) and reclaim the space it was taking up on the hard drive. We have tried truncating the table and then shrinking it on a copy of the production database, but this process takes about a week.

Is there a quicker/better/more efficient way to do this? I have read that shrinking a database is a taboo among DBAs because it introduces index fragmentation.

We are using Microsoft SQL Server.

"Best way" translates to quickest and least likely to cause us issues in the future (like index fragmentation might). We are trying to make this as quick as possible so that our database won't be tied up for several days deleting data and shrinking files.

sql-server shrink truncate

share|improve this question

edited Oct 11 '16 at 22:39

ypercubeᵀᴹ

77.4k11134216

asked Oct 11 '16 at 21:19

martin122089martin122089

42

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

migrated from stackoverflow.com Oct 11 '16 at 21:28

This question came from our site for professional and enthusiast programmers.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It start with this msdn.microsoft.com/en-us/library/ms189493.aspx I don't think you can shrink a single table file only. When done then rebuild the fractured indexes.
  
  – paparazzo
  Oct 11 '16 at 21:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please add version (2008, 2008R2, 2012, etc) and edition (Standard, Enterprise, Azure, etc)
  
  – ypercubeᵀᴹ
  Oct 11 '16 at 22:42
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I think you'll most likely have to go with Randolph's solution, but you 'might' get some relief if you try SHRINKFILE TRUNCATEONLY - (msdn.microsoft.com/en-us/library/ms189493.aspx) - Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
  
  – Scott Hodgin
  Oct 12 '16 at 9:44
  

  
  
  
  

add a comment | 

Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having.

We have a database that unintentionally ballooned to 300+ GB. We have turned off the option that caused this in our application, but we want to purge the offending data (in only 1 table) and reclaim the space it was taking up on the hard drive. We have tried truncating the table and then shrinking it on a copy of the production database, but this process takes about a week.

Is there a quicker/better/more efficient way to do this? I have read that shrinking a database is a taboo among DBAs because it introduces index fragmentation.

We are using Microsoft SQL Server.

"Best way" translates to quickest and least likely to cause us issues in the future (like index fragmentation might). We are trying to make this as quick as possible so that our database won't be tied up for several days deleting data and shrinking files.

sql-server shrink truncate

share|improve this question

edited Oct 11 '16 at 22:39

ypercubeᵀᴹ

77.4k11134216

asked Oct 11 '16 at 21:19

martin122089martin122089

42

share|improve this question

edited Oct 11 '16 at 22:39

ypercubeᵀᴹ

77.4k11134216

asked Oct 11 '16 at 21:19

martin122089martin122089

42

share|improve this question

edited Oct 11 '16 at 22:39

ypercubeᵀᴹ

77.4k11134216

asked Oct 11 '16 at 21:19

martin122089martin122089

42

edited Oct 11 '16 at 22:39

ypercubeᵀᴹ

77.4k11134216

edited Oct 11 '16 at 22:39

ypercubeᵀᴹ

77.4k11134216

asked Oct 11 '16 at 21:19

martin122089martin122089

42

asked Oct 11 '16 at 21:19

martin122089martin122089

42

2 Answers
2

active

oldest

votes

0

There are two options for this kind of scenario:

1) Shrink the data file, and then rebuild the indexes. As you say, this is time consuming and you're largely at the whim of the storage subsystem.

2) Create a new filegroup, and migrate all the data into the new filegroup, one table at a time. Shrink the old filegroup when you're done, and either move everything back again, or leave it as is.

This second option adds some complexity, and additional maintenance for the extra FG, but is probably what you're after, since you can move one table at a time. You would do this by altering each table's clustered index to rebuild in the new filegroup.

Adding a Filegroup:

(See https://msdn.microsoft.com/en-us/library/bb522469.aspx)

USE master  

GO  

ALTER DATABASE AdventureWorks2012  

ADD FILEGROUP NewFG;  

GO  

Adding a File to a Filegroup:

ALTER DATABASE AdventureWorks2012   

ADD FILE   

(  

    NAME = FG2,  

    FILENAME = 'G:DATAFG2.ndf',  

    SIZE = 500MB,  

    MAXSIZE = UNLIMITED,  

    FILEGROWTH = 250MB  

)  

TO FILEGROUP NewFG;  

GO  

Rebuilding the clustered index in the new filegroup:

CREATE UNIQUE CLUSTERED INDEX [PK_Example] ON Test.dbo.Example(ExampleID) 

WITH (DROP_EXISTING = ON) ON [NewFG]

GO

share|improve this answer

answered Oct 12 '16 at 4:09

Randolph WestRandolph West

2,649215

add a comment | 

-2

I am pretty sure that truncating is the most efficient way. However, you may want to re-index the table. Hope this helps!

share|improve this answer

answered Oct 11 '16 at 21:24

Mark Ellis

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f152026%2fwhats-the-most-efficient-way-to-reclaim-unused-db-hard-drive-space%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

There are two options for this kind of scenario:

1) Shrink the data file, and then rebuild the indexes. As you say, this is time consuming and you're largely at the whim of the storage subsystem.

2) Create a new filegroup, and migrate all the data into the new filegroup, one table at a time. Shrink the old filegroup when you're done, and either move everything back again, or leave it as is.

This second option adds some complexity, and additional maintenance for the extra FG, but is probably what you're after, since you can move one table at a time. You would do this by altering each table's clustered index to rebuild in the new filegroup.

Adding a Filegroup:

(See https://msdn.microsoft.com/en-us/library/bb522469.aspx)

USE master  

GO  

ALTER DATABASE AdventureWorks2012  

ADD FILEGROUP NewFG;  

GO  

Adding a File to a Filegroup:

ALTER DATABASE AdventureWorks2012   

ADD FILE   

(  

    NAME = FG2,  

    FILENAME = 'G:DATAFG2.ndf',  

    SIZE = 500MB,  

    MAXSIZE = UNLIMITED,  

    FILEGROWTH = 250MB  

)  

TO FILEGROUP NewFG;  

GO  

Rebuilding the clustered index in the new filegroup:

CREATE UNIQUE CLUSTERED INDEX [PK_Example] ON Test.dbo.Example(ExampleID) 

WITH (DROP_EXISTING = ON) ON [NewFG]

GO

share|improve this answer

answered Oct 12 '16 at 4:09

Randolph WestRandolph West

2,649215

add a comment | 

0

There are two options for this kind of scenario:

1) Shrink the data file, and then rebuild the indexes. As you say, this is time consuming and you're largely at the whim of the storage subsystem.

2) Create a new filegroup, and migrate all the data into the new filegroup, one table at a time. Shrink the old filegroup when you're done, and either move everything back again, or leave it as is.

This second option adds some complexity, and additional maintenance for the extra FG, but is probably what you're after, since you can move one table at a time. You would do this by altering each table's clustered index to rebuild in the new filegroup.

Adding a Filegroup:

(See https://msdn.microsoft.com/en-us/library/bb522469.aspx)

USE master  

GO  

ALTER DATABASE AdventureWorks2012  

ADD FILEGROUP NewFG;  

GO  

Adding a File to a Filegroup:

ALTER DATABASE AdventureWorks2012   

ADD FILE   

(  

    NAME = FG2,  

    FILENAME = 'G:DATAFG2.ndf',  

    SIZE = 500MB,  

    MAXSIZE = UNLIMITED,  

    FILEGROWTH = 250MB  

)  

TO FILEGROUP NewFG;  

GO  

Rebuilding the clustered index in the new filegroup:

CREATE UNIQUE CLUSTERED INDEX [PK_Example] ON Test.dbo.Example(ExampleID) 

WITH (DROP_EXISTING = ON) ON [NewFG]

GO

share|improve this answer

answered Oct 12 '16 at 4:09

Randolph WestRandolph West

2,649215

add a comment | 

There are two options for this kind of scenario:

1) Shrink the data file, and then rebuild the indexes. As you say, this is time consuming and you're largely at the whim of the storage subsystem.

2) Create a new filegroup, and migrate all the data into the new filegroup, one table at a time. Shrink the old filegroup when you're done, and either move everything back again, or leave it as is.

This second option adds some complexity, and additional maintenance for the extra FG, but is probably what you're after, since you can move one table at a time. You would do this by altering each table's clustered index to rebuild in the new filegroup.

Adding a Filegroup:

(See https://msdn.microsoft.com/en-us/library/bb522469.aspx)

USE master  

GO  

ALTER DATABASE AdventureWorks2012  

ADD FILEGROUP NewFG;  

GO  

Adding a File to a Filegroup:

ALTER DATABASE AdventureWorks2012   

ADD FILE   

(  

    NAME = FG2,  

    FILENAME = 'G:DATAFG2.ndf',  

    SIZE = 500MB,  

    MAXSIZE = UNLIMITED,  

    FILEGROWTH = 250MB  

)  

TO FILEGROUP NewFG;  

GO  

Rebuilding the clustered index in the new filegroup:

CREATE UNIQUE CLUSTERED INDEX [PK_Example] ON Test.dbo.Example(ExampleID) 

WITH (DROP_EXISTING = ON) ON [NewFG]

GO

share|improve this answer

answered Oct 12 '16 at 4:09

Randolph WestRandolph West

2,649215

USE master  

GO  

ALTER DATABASE AdventureWorks2012  

ADD FILEGROUP NewFG;  

GO  

ALTER DATABASE AdventureWorks2012   

ADD FILE   

(  

    NAME = FG2,  

    FILENAME = 'G:DATAFG2.ndf',  

    SIZE = 500MB,  

    MAXSIZE = UNLIMITED,  

    FILEGROWTH = 250MB  

)  

TO FILEGROUP NewFG;  

GO  

CREATE UNIQUE CLUSTERED INDEX [PK_Example] ON Test.dbo.Example(ExampleID) 

WITH (DROP_EXISTING = ON) ON [NewFG]

GO

share|improve this answer

answered Oct 12 '16 at 4:09

Randolph WestRandolph West

2,649215

answered Oct 12 '16 at 4:09

Randolph WestRandolph West

2,649215

answered Oct 12 '16 at 4:09

Randolph WestRandolph West

2,649215