Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express
I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to be under the 10 gig limit.
There's 39% space to erase but shrink does not work. I've done this many times including this database after removing 100000's of rows in different tables but it does not want to shrink this time.
No errors just runs and does nothing.
sql-server sql-server-2008 sql-server-2014 shrink sql-server-express
edited Dec 18 '18 at 18:48
MDCCL
6,84331745
asked Dec 18 '18 at 18:04
Daniel SaintongeDaniel Saintonge
61
add a comment |
I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to be under the 10 gig limit.
There's 39% space to erase but shrink does not work. I've done this many times including this database after removing 100000's of rows in different tables but it does not want to shrink this time.
No errors just runs and does nothing.
sql-server sql-server-2008 sql-server-2014 shrink sql-server-express
edited Dec 18 '18 at 18:48
MDCCL
6,84331745
asked Dec 18 '18 at 18:04
Daniel SaintongeDaniel Saintonge
61
Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
– scsimon
Dec 18 '18 at 20:13
@scimon I suppose you meant Tara, not Kendra!
– ypercubeᵀᴹ
Dec 18 '18 at 21:01
@ypercubeᵀᴹ ah you're right!
– scsimon
Dec 18 '18 at 21:51
You don’t need to concern yourself with shrinking if you export/import the data. This is also a good way to rebuild/pack all tables and indexes. (However if the data fits In the 10gb limit it might quickly outgrow it if you start mutating sich densely packed tables)
– eckes
Dec 20 '18 at 21:45
add a comment |
I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to be under the 10 gig limit.
There's 39% space to erase but shrink does not work. I've done this many times including this database after removing 100000's of rows in different tables but it does not want to shrink this time.
No errors just runs and does nothing.
sql-server sql-server-2008 sql-server-2014 shrink sql-server-express
edited Dec 18 '18 at 18:48
MDCCL
6,84331745
asked Dec 18 '18 at 18:04
Daniel SaintongeDaniel Saintonge
61
I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to be under the 10 gig limit.
There's 39% space to erase but shrink does not work. I've done this many times including this database after removing 100000's of rows in different tables but it does not want to shrink this time.
No errors just runs and does nothing.
sql-server sql-server-2008 sql-server-2014 shrink sql-server-express
sql-server sql-server-2008 sql-server-2014 shrink sql-server-express
edited Dec 18 '18 at 18:48
MDCCL
6,84331745
asked Dec 18 '18 at 18:04
Daniel SaintongeDaniel Saintonge
61
edited Dec 18 '18 at 18:48
MDCCL
6,84331745
asked Dec 18 '18 at 18:04
Daniel SaintongeDaniel Saintonge
61
edited Dec 18 '18 at 18:48
MDCCL
6,84331745
edited Dec 18 '18 at 18:48
MDCCL
6,84331745
edited Dec 18 '18 at 18:48
MDCCL
6,84331745
6,84331745
asked Dec 18 '18 at 18:04
Daniel SaintongeDaniel Saintonge
61
asked Dec 18 '18 at 18:04
Daniel SaintongeDaniel Saintonge
61
asked Dec 18 '18 at 18:04
Daniel SaintongeDaniel Saintonge
61
61
Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
– scsimon
Dec 18 '18 at 20:13
@scimon I suppose you meant Tara, not Kendra!
– ypercubeᵀᴹ
Dec 18 '18 at 21:01
@ypercubeᵀᴹ ah you're right!
– scsimon
Dec 18 '18 at 21:51
You don’t need to concern yourself with shrinking if you export/import the data. This is also a good way to rebuild/pack all tables and indexes. (However if the data fits In the 10gb limit it might quickly outgrow it if you start mutating sich densely packed tables)
– eckes
Dec 20 '18 at 21:45
add a comment |
Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
– scsimon
Dec 18 '18 at 20:13
@scimon I suppose you meant Tara, not Kendra!
– ypercubeᵀᴹ
Dec 18 '18 at 21:01
@ypercubeᵀᴹ ah you're right!
– scsimon
Dec 18 '18 at 21:51
You don’t need to concern yourself with shrinking if you export/import the data. This is also a good way to rebuild/pack all tables and indexes. (However if the data fits In the 10gb limit it might quickly outgrow it if you start mutating sich densely packed tables)
– eckes
Dec 20 '18 at 21:45
Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
– scsimon
Dec 18 '18 at 20:13
Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
– scsimon
Dec 18 '18 at 20:13
@scimon I suppose you meant Tara, not Kendra!
– ypercubeᵀᴹ
Dec 18 '18 at 21:01
@scimon I suppose you meant Tara, not Kendra!
– ypercubeᵀᴹ
Dec 18 '18 at 21:01
@ypercubeᵀᴹ ah you're right!
– scsimon
Dec 18 '18 at 21:51
@ypercubeᵀᴹ ah you're right!
– scsimon
Dec 18 '18 at 21:51
You don’t need to concern yourself with shrinking if you export/import the data. This is also a good way to rebuild/pack all tables and indexes. (However if the data fits In the 10gb limit it might quickly outgrow it if you start mutating sich densely packed tables)
– eckes
Dec 20 '18 at 21:45
You don’t need to concern yourself with shrinking if you export/import the data. This is also a good way to rebuild/pack all tables and indexes. (However if the data fits In the 10gb limit it might quickly outgrow it if you start mutating sich densely packed tables)
– eckes
Dec 20 '18 at 21:45
add a comment |
2 Answers
2
active
oldest
votes
Normally the thing holding my data files back from being shrunk is that a CHECKPOINT is needed. I have had to do several iterations of:
CHECKPOINT;
GO
DBCC SHRINKFILE(...)
GO
Before the file would finally shrink.
If the data files still doesn't shrink, try the troublshooting steps here to confirm that you really have the free space that you think you have. Here's the query from the docs link:
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
answered Dec 18 '18 at 18:14
jadarnel27jadarnel27
5,67811937
add a comment |
Try executing the shrink in 100 MBs ,
I don't have my code at hand but create a cursor which will go over, the file many times reducing the total size everytime by 100 MBs. That should work and allow you to stop it and don't loose any job.
answered Dec 18 '18 at 22:45
dbamexdbamex
816
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225302%2fshrinking-a-sql-server-2008-standard-database-to-move-it-into-sql-server-2014-ex%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Normally the thing holding my data files back from being shrunk is that a CHECKPOINT is needed. I have had to do several iterations of:
CHECKPOINT;
GO
DBCC SHRINKFILE(...)
GO
Before the file would finally shrink.
If the data files still doesn't shrink, try the troublshooting steps here to confirm that you really have the free space that you think you have. Here's the query from the docs link:
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
answered Dec 18 '18 at 18:14
jadarnel27jadarnel27
5,67811937
add a comment |
Normally the thing holding my data files back from being shrunk is that a CHECKPOINT is needed. I have had to do several iterations of:
CHECKPOINT;
GO
DBCC SHRINKFILE(...)
GO
Before the file would finally shrink.
If the data files still doesn't shrink, try the troublshooting steps here to confirm that you really have the free space that you think you have. Here's the query from the docs link:
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
answered Dec 18 '18 at 18:14
jadarnel27jadarnel27
5,67811937
add a comment |
Normally the thing holding my data files back from being shrunk is that a CHECKPOINT is needed. I have had to do several iterations of:
CHECKPOINT;
GO
DBCC SHRINKFILE(...)
GO
Before the file would finally shrink.
If the data files still doesn't shrink, try the troublshooting steps here to confirm that you really have the free space that you think you have. Here's the query from the docs link:
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
answered Dec 18 '18 at 18:14
jadarnel27jadarnel27
5,67811937
Normally the thing holding my data files back from being shrunk is that a CHECKPOINT is needed. I have had to do several iterations of:
CHECKPOINT;
GO
DBCC SHRINKFILE(...)
GO
Before the file would finally shrink.
If the data files still doesn't shrink, try the troublshooting steps here to confirm that you really have the free space that you think you have. Here's the query from the docs link:
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
answered Dec 18 '18 at 18:14
jadarnel27jadarnel27
5,67811937
edited Dec 18 '18 at 18:21
answered Dec 18 '18 at 18:14
jadarnel27jadarnel27
5,67811937
answered Dec 18 '18 at 18:14
jadarnel27jadarnel27
5,67811937
answered Dec 18 '18 at 18:14
jadarnel27jadarnel27
5,67811937
5,67811937
add a comment |
add a comment |
Try executing the shrink in 100 MBs ,
I don't have my code at hand but create a cursor which will go over, the file many times reducing the total size everytime by 100 MBs. That should work and allow you to stop it and don't loose any job.
answered Dec 18 '18 at 22:45
dbamexdbamex
816
add a comment |
Try executing the shrink in 100 MBs ,
I don't have my code at hand but create a cursor which will go over, the file many times reducing the total size everytime by 100 MBs. That should work and allow you to stop it and don't loose any job.
answered Dec 18 '18 at 22:45
dbamexdbamex
816
add a comment |
Try executing the shrink in 100 MBs ,
I don't have my code at hand but create a cursor which will go over, the file many times reducing the total size everytime by 100 MBs. That should work and allow you to stop it and don't loose any job.
answered Dec 18 '18 at 22:45
dbamexdbamex
816
Try executing the shrink in 100 MBs ,
I don't have my code at hand but create a cursor which will go over, the file many times reducing the total size everytime by 100 MBs. That should work and allow you to stop it and don't loose any job.
answered Dec 18 '18 at 22:45
dbamexdbamex
816
answered Dec 18 '18 at 22:45
dbamexdbamex
816
answered Dec 18 '18 at 22:45
dbamexdbamex
816
answered Dec 18 '18 at 22:45
dbamexdbamex
816
816
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225302%2fshrinking-a-sql-server-2008-standard-database-to-move-it-into-sql-server-2014-ex%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
– scsimon
Dec 18 '18 at 20:13
@scimon I suppose you meant Tara, not Kendra!
– ypercubeᵀᴹ
Dec 18 '18 at 21:01
@ypercubeᵀᴹ ah you're right!
– scsimon
Dec 18 '18 at 21:51
You don’t need to concern yourself with shrinking if you export/import the data. This is also a good way to rebuild/pack all tables and indexes. (However if the data fits In the 10gb limit it might quickly outgrow it if you start mutating sich densely packed tables)
– eckes
Dec 20 '18 at 21:45