database size increased after restoring on another drive
0
I have restored my database on to another drive and renamed it. I have noticed that my actual database size is 500GB where as the restored (New) went to 600GB. Any theory behind this how it happened.
sql-server asked 2 days ago
user9192401user9192401
967
add a comment |
0
I have restored my database on to another drive and renamed it. I have noticed that my actual database size is 500GB where as the restored (New) went to 600GB. Any theory behind this how it happened.
sql-server asked 2 days ago
user9192401user9192401
967
1
Is 500GB the size of the original database or the backup? The backup doesn't contain empty data pages. As for the size difference, a cluster size difference could account for it. Apart from that one would have to guess about your hardware, server, database configuration
– Panagiotis Kanavos
2 days ago
The size of database was 500GB mate.
– user9192401
2 days ago
Is it the same version of SQL Server? Have you got all the same fill factors on indexes, or have they changed to new defaults somewhere - maybe there is some other type of default setting difference on what I presume is a different SQL Server installation.
– Cato
2 days ago
add a comment |
0
0
0
I have restored my database on to another drive and renamed it. I have noticed that my actual database size is 500GB where as the restored (New) went to 600GB. Any theory behind this how it happened.
sql-server asked 2 days ago
user9192401user9192401
967
I have restored my database on to another drive and renamed it. I have noticed that my actual database size is 500GB where as the restored (New) went to 600GB. Any theory behind this how it happened.
sql-server sql-server asked 2 days ago
user9192401user9192401
967
asked 2 days ago
user9192401user9192401
967
asked 2 days ago
user9192401user9192401
967
asked 2 days ago
user9192401user9192401
967
asked 2 days ago
user9192401user9192401
967
967
1
Is 500GB the size of the original database or the backup? The backup doesn't contain empty data pages. As for the size difference, a cluster size difference could account for it. Apart from that one would have to guess about your hardware, server, database configuration
– Panagiotis Kanavos
2 days ago
The size of database was 500GB mate.
– user9192401
2 days ago
Is it the same version of SQL Server? Have you got all the same fill factors on indexes, or have they changed to new defaults somewhere - maybe there is some other type of default setting difference on what I presume is a different SQL Server installation.
– Cato
2 days ago
add a comment |
1
Is 500GB the size of the original database or the backup? The backup doesn't contain empty data pages. As for the size difference, a cluster size difference could account for it. Apart from that one would have to guess about your hardware, server, database configuration
– Panagiotis Kanavos
2 days ago
The size of database was 500GB mate.
– user9192401
2 days ago
Is it the same version of SQL Server? Have you got all the same fill factors on indexes, or have they changed to new defaults somewhere - maybe there is some other type of default setting difference on what I presume is a different SQL Server installation.
– Cato
2 days ago
1
1
Is 500GB the size of the original database or the backup? The backup doesn't contain empty data pages. As for the size difference, a cluster size difference could account for it. Apart from that one would have to guess about your hardware, server, database configuration
– Panagiotis Kanavos
2 days ago
Is 500GB the size of the original database or the backup? The backup doesn't contain empty data pages. As for the size difference, a cluster size difference could account for it. Apart from that one would have to guess about your hardware, server, database configuration
– Panagiotis Kanavos
2 days ago
The size of database was 500GB mate.
– user9192401
2 days ago
The size of database was 500GB mate.
– user9192401
2 days ago
Is it the same version of SQL Server? Have you got all the same fill factors on indexes, or have they changed to new defaults somewhere - maybe there is some other type of default setting difference on what I presume is a different SQL Server installation.
– Cato
2 days ago
Is it the same version of SQL Server? Have you got all the same fill factors on indexes, or have they changed to new defaults somewhere - maybe there is some other type of default setting difference on what I presume is a different SQL Server installation.
– Cato
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
0
Did you checked log file?
I had same problem few weeks ago but I achieved it using database shrink using two ways.
1. Shrink MSSQL Database Log File / Truncate Transaction Log using Steps
- Log into Microsoft SQL Server Management Studio
- Right click on your database.
- Select Properties.
- Click on Options.
- Change the recovery model to Simple.
- Click OK.
- Right click on the database.
- Select Tasks > Shrink > Database.
- Click OK.
- Right click on the database.
- Select Properties.
- Click on Options.
- Change the recovery model to Full.
- Click OK.
2.Using DBCC SHRINKFILE (Transact-SQL)
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
To implement using T-SQL, use the below queries.
SELECT * FROM sys.database_files;
file_id 5 is Log file in instance. Check the the virtual logs in use.
DBCC LOGINFO;
DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;
Refer link for more information about Database shrink.
answered 2 days ago
Dipak DelvadiyaDipak Delvadiya
2,35721430
Shrinking a heavily used database will only make it slower as it tries to reallocate the space it needed.
– Panagiotis Kanavos
2 days ago
1
Read Stop Shrinking Your Database Files. Seriously. Now..
– Panagiotis Kanavos
2 days ago
@Dipak: I dont see any free space to shrink the database files. It says used 600GB and free space 0%
– user9192401
2 days ago
These people who say 'don't shrink stuff' work for or own dynamic companies who listen to requests for new hardware, but other people work in companies crawling with bean counters who make spreadsheets about hardware costs etc, even though just one of them takes more money than a hardware upgrade.
– Cato
2 days ago
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54252841%2fdatabase-size-increased-after-restoring-on-another-drive%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Did you checked log file?
I had same problem few weeks ago but I achieved it using database shrink using two ways.
1. Shrink MSSQL Database Log File / Truncate Transaction Log using Steps
- Log into Microsoft SQL Server Management Studio
- Right click on your database.
- Select Properties.
- Click on Options.
- Change the recovery model to Simple.
- Click OK.
- Right click on the database.
- Select Tasks > Shrink > Database.
- Click OK.
- Right click on the database.
- Select Properties.
- Click on Options.
- Change the recovery model to Full.
- Click OK.
2.Using DBCC SHRINKFILE (Transact-SQL)
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
To implement using T-SQL, use the below queries.
SELECT * FROM sys.database_files;
file_id 5 is Log file in instance. Check the the virtual logs in use.
DBCC LOGINFO;
DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;
Refer link for more information about Database shrink.
answered 2 days ago
Dipak DelvadiyaDipak Delvadiya
2,35721430
Shrinking a heavily used database will only make it slower as it tries to reallocate the space it needed.
– Panagiotis Kanavos
2 days ago
1
Read Stop Shrinking Your Database Files. Seriously. Now..
– Panagiotis Kanavos
2 days ago
@Dipak: I dont see any free space to shrink the database files. It says used 600GB and free space 0%
– user9192401
2 days ago
These people who say 'don't shrink stuff' work for or own dynamic companies who listen to requests for new hardware, but other people work in companies crawling with bean counters who make spreadsheets about hardware costs etc, even though just one of them takes more money than a hardware upgrade.
– Cato
2 days ago
add a comment |
0
Did you checked log file?
I had same problem few weeks ago but I achieved it using database shrink using two ways.
1. Shrink MSSQL Database Log File / Truncate Transaction Log using Steps
- Log into Microsoft SQL Server Management Studio
- Right click on your database.
- Select Properties.
- Click on Options.
- Change the recovery model to Simple.
- Click OK.
- Right click on the database.
- Select Tasks > Shrink > Database.
- Click OK.
- Right click on the database.
- Select Properties.
- Click on Options.
- Change the recovery model to Full.
- Click OK.
2.Using DBCC SHRINKFILE (Transact-SQL)
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
To implement using T-SQL, use the below queries.
SELECT * FROM sys.database_files;
file_id 5 is Log file in instance. Check the the virtual logs in use.
DBCC LOGINFO;
DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;
Refer link for more information about Database shrink.
answered 2 days ago
Dipak DelvadiyaDipak Delvadiya
2,35721430
Shrinking a heavily used database will only make it slower as it tries to reallocate the space it needed.
– Panagiotis Kanavos
2 days ago
1
Read Stop Shrinking Your Database Files. Seriously. Now..
– Panagiotis Kanavos
2 days ago
@Dipak: I dont see any free space to shrink the database files. It says used 600GB and free space 0%
– user9192401
2 days ago
These people who say 'don't shrink stuff' work for or own dynamic companies who listen to requests for new hardware, but other people work in companies crawling with bean counters who make spreadsheets about hardware costs etc, even though just one of them takes more money than a hardware upgrade.
– Cato
2 days ago
add a comment |
0
0
0
Did you checked log file?
I had same problem few weeks ago but I achieved it using database shrink using two ways.
1. Shrink MSSQL Database Log File / Truncate Transaction Log using Steps
- Log into Microsoft SQL Server Management Studio
- Right click on your database.
- Select Properties.
- Click on Options.
- Change the recovery model to Simple.
- Click OK.
- Right click on the database.
- Select Tasks > Shrink > Database.
- Click OK.
- Right click on the database.
- Select Properties.
- Click on Options.
- Change the recovery model to Full.
- Click OK.
2.Using DBCC SHRINKFILE (Transact-SQL)
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
To implement using T-SQL, use the below queries.
SELECT * FROM sys.database_files;
file_id 5 is Log file in instance. Check the the virtual logs in use.
DBCC LOGINFO;
DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;
Refer link for more information about Database shrink.
answered 2 days ago
Dipak DelvadiyaDipak Delvadiya
2,35721430
Did you checked log file?
I had same problem few weeks ago but I achieved it using database shrink using two ways.
1. Shrink MSSQL Database Log File / Truncate Transaction Log using Steps
- Log into Microsoft SQL Server Management Studio
- Right click on your database.
- Select Properties.
- Click on Options.
- Change the recovery model to Simple.
- Click OK.
- Right click on the database.
- Select Tasks > Shrink > Database.
- Click OK.
- Right click on the database.
- Select Properties.
- Click on Options.
- Change the recovery model to Full.
- Click OK.
2.Using DBCC SHRINKFILE (Transact-SQL)
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
To implement using T-SQL, use the below queries.
SELECT * FROM sys.database_files;
file_id 5 is Log file in instance. Check the the virtual logs in use.
DBCC LOGINFO;
DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;
Refer link for more information about Database shrink.
answered 2 days ago
Dipak DelvadiyaDipak Delvadiya
2,35721430
edited 2 days ago
answered 2 days ago
Dipak DelvadiyaDipak Delvadiya
2,35721430
answered 2 days ago
Dipak DelvadiyaDipak Delvadiya
2,35721430
answered 2 days ago
Dipak DelvadiyaDipak Delvadiya
2,35721430
2,35721430
Shrinking a heavily used database will only make it slower as it tries to reallocate the space it needed.
– Panagiotis Kanavos
2 days ago
1
Read Stop Shrinking Your Database Files. Seriously. Now..
– Panagiotis Kanavos
2 days ago
@Dipak: I dont see any free space to shrink the database files. It says used 600GB and free space 0%
– user9192401
2 days ago
These people who say 'don't shrink stuff' work for or own dynamic companies who listen to requests for new hardware, but other people work in companies crawling with bean counters who make spreadsheets about hardware costs etc, even though just one of them takes more money than a hardware upgrade.
– Cato
2 days ago
add a comment |
Shrinking a heavily used database will only make it slower as it tries to reallocate the space it needed.
– Panagiotis Kanavos
2 days ago
1
Read Stop Shrinking Your Database Files. Seriously. Now..
– Panagiotis Kanavos
2 days ago
@Dipak: I dont see any free space to shrink the database files. It says used 600GB and free space 0%
– user9192401
2 days ago
These people who say 'don't shrink stuff' work for or own dynamic companies who listen to requests for new hardware, but other people work in companies crawling with bean counters who make spreadsheets about hardware costs etc, even though just one of them takes more money than a hardware upgrade.
– Cato
2 days ago
Shrinking a heavily used database will only make it slower as it tries to reallocate the space it needed.
– Panagiotis Kanavos
2 days ago
Shrinking a heavily used database will only make it slower as it tries to reallocate the space it needed.
– Panagiotis Kanavos
2 days ago
1
1
Read Stop Shrinking Your Database Files. Seriously. Now..
– Panagiotis Kanavos
2 days ago
Read Stop Shrinking Your Database Files. Seriously. Now..
– Panagiotis Kanavos
2 days ago
@Dipak: I dont see any free space to shrink the database files. It says used 600GB and free space 0%
– user9192401
2 days ago
@Dipak: I dont see any free space to shrink the database files. It says used 600GB and free space 0%
– user9192401
2 days ago
These people who say 'don't shrink stuff' work for or own dynamic companies who listen to requests for new hardware, but other people work in companies crawling with bean counters who make spreadsheets about hardware costs etc, even though just one of them takes more money than a hardware upgrade.
– Cato
2 days ago
These people who say 'don't shrink stuff' work for or own dynamic companies who listen to requests for new hardware, but other people work in companies crawling with bean counters who make spreadsheets about hardware costs etc, even though just one of them takes more money than a hardware upgrade.
– Cato
2 days ago
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54252841%2fdatabase-size-increased-after-restoring-on-another-drive%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
1
Is 500GB the size of the original database or the backup? The backup doesn't contain empty data pages. As for the size difference, a cluster size difference could account for it. Apart from that one would have to guess about your hardware, server, database configuration
– Panagiotis Kanavos
2 days ago
The size of database was 500GB mate.
– user9192401
2 days ago
Is it the same version of SQL Server? Have you got all the same fill factors on indexes, or have they changed to new defaults somewhere - maybe there is some other type of default setting difference on what I presume is a different SQL Server installation.
– Cato
2 days ago