Restore SQL is very slowly
We are executing a SQL restore query but have some problems with the time.
We have three physical server hosting SQL server. There is a storage device that is connected to them.
When we start the restore query, the query get finished in 5 minutes on a server. But on the other two server it takes about an hour to finish the query.
According our monitor, the data part is doing well in the restore process but when it comes to the logs it a kind of stuck in this level and takes about 40 minutes to finish the job. One out of three servers is doing fine and finishes the query in 10 minutes but the others last an hour.
The storage is newly bought and well configured. the OS of the servers are newly installed and configured. Different HBA cards have been tested. we are sure that there is no problem from physical equipment.
We are really stuck in here whether it is a windows problem or server or SQL.
sql
sql-server windows edited Nov 18 '18 at 12:19
Gordon Linoff
772k35304406
asked Nov 18 '18 at 9:25
AshkanAshkan
64
add a comment |
We are executing a SQL restore query but have some problems with the time.
We have three physical server hosting SQL server. There is a storage device that is connected to them.
When we start the restore query, the query get finished in 5 minutes on a server. But on the other two server it takes about an hour to finish the query.
According our monitor, the data part is doing well in the restore process but when it comes to the logs it a kind of stuck in this level and takes about 40 minutes to finish the job. One out of three servers is doing fine and finishes the query in 10 minutes but the others last an hour.
The storage is newly bought and well configured. the OS of the servers are newly installed and configured. Different HBA cards have been tested. we are sure that there is no problem from physical equipment.
We are really stuck in here whether it is a windows problem or server or SQL.
sql
sql-server windows edited Nov 18 '18 at 12:19
Gordon Linoff
772k35304406
asked Nov 18 '18 at 9:25
AshkanAshkan
64
Make sure the SQL Server service account is granted the Windowsperform volume maintenance tasksprivilege so that it can use instant file initialization.
– Dan Guzman
Nov 18 '18 at 11:32
Thanks Dan. It was enabled. Without it it would take three times longer. It's a weird problem.
– Ashkan
Nov 19 '18 at 13:09
I have to ad that restore on local disks are ok and fast enough but on SAN it takes lots of time.
– Ashkan
Nov 19 '18 at 13:30
add a comment |
We are executing a SQL restore query but have some problems with the time.
We have three physical server hosting SQL server. There is a storage device that is connected to them.
When we start the restore query, the query get finished in 5 minutes on a server. But on the other two server it takes about an hour to finish the query.
According our monitor, the data part is doing well in the restore process but when it comes to the logs it a kind of stuck in this level and takes about 40 minutes to finish the job. One out of three servers is doing fine and finishes the query in 10 minutes but the others last an hour.
The storage is newly bought and well configured. the OS of the servers are newly installed and configured. Different HBA cards have been tested. we are sure that there is no problem from physical equipment.
We are really stuck in here whether it is a windows problem or server or SQL.
sql
sql-server windows edited Nov 18 '18 at 12:19
Gordon Linoff
772k35304406
asked Nov 18 '18 at 9:25
AshkanAshkan
64
We are executing a SQL restore query but have some problems with the time.
We have three physical server hosting SQL server. There is a storage device that is connected to them.
When we start the restore query, the query get finished in 5 minutes on a server. But on the other two server it takes about an hour to finish the query.
According our monitor, the data part is doing well in the restore process but when it comes to the logs it a kind of stuck in this level and takes about 40 minutes to finish the job. One out of three servers is doing fine and finishes the query in 10 minutes but the others last an hour.
The storage is newly bought and well configured. the OS of the servers are newly installed and configured. Different HBA cards have been tested. we are sure that there is no problem from physical equipment.
We are really stuck in here whether it is a windows problem or server or SQL.
sql
sql-server windows sql
sql-server windows edited Nov 18 '18 at 12:19
Gordon Linoff
772k35304406
asked Nov 18 '18 at 9:25
AshkanAshkan
64
edited Nov 18 '18 at 12:19
Gordon Linoff
772k35304406
asked Nov 18 '18 at 9:25
AshkanAshkan
64
edited Nov 18 '18 at 12:19
Gordon Linoff
772k35304406
edited Nov 18 '18 at 12:19
Gordon Linoff
772k35304406
edited Nov 18 '18 at 12:19
Gordon Linoff
772k35304406
772k35304406
asked Nov 18 '18 at 9:25
AshkanAshkan
64
asked Nov 18 '18 at 9:25
AshkanAshkan
64
asked Nov 18 '18 at 9:25
AshkanAshkan
64
64
Make sure the SQL Server service account is granted the Windowsperform volume maintenance tasksprivilege so that it can use instant file initialization.
– Dan Guzman
Nov 18 '18 at 11:32
Thanks Dan. It was enabled. Without it it would take three times longer. It's a weird problem.
– Ashkan
Nov 19 '18 at 13:09
I have to ad that restore on local disks are ok and fast enough but on SAN it takes lots of time.
– Ashkan
Nov 19 '18 at 13:30
add a comment |
Make sure the SQL Server service account is granted the Windowsperform volume maintenance tasksprivilege so that it can use instant file initialization.
– Dan Guzman
Nov 18 '18 at 11:32
Thanks Dan. It was enabled. Without it it would take three times longer. It's a weird problem.
– Ashkan
Nov 19 '18 at 13:09
I have to ad that restore on local disks are ok and fast enough but on SAN it takes lots of time.
– Ashkan
Nov 19 '18 at 13:30
Make sure the SQL Server service account is granted the Windows
perform volume maintenance tasks privilege so that it can use instant file initialization.– Dan Guzman
Nov 18 '18 at 11:32
Make sure the SQL Server service account is granted the Windows
perform volume maintenance tasks privilege so that it can use instant file initialization.– Dan Guzman
Nov 18 '18 at 11:32
Thanks Dan. It was enabled. Without it it would take three times longer. It's a weird problem.
– Ashkan
Nov 19 '18 at 13:09
Thanks Dan. It was enabled. Without it it would take three times longer. It's a weird problem.
– Ashkan
Nov 19 '18 at 13:09
I have to ad that restore on local disks are ok and fast enough but on SAN it takes lots of time.
– Ashkan
Nov 19 '18 at 13:30
I have to ad that restore on local disks are ok and fast enough but on SAN it takes lots of time.
– Ashkan
Nov 19 '18 at 13:30
add a comment |
0
active
oldest
votes
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
});
}
});
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%2f53359423%2frestore-sql-is-very-slowly%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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%2f53359423%2frestore-sql-is-very-slowly%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
Make sure the SQL Server service account is granted the Windows
perform volume maintenance tasksprivilege so that it can use instant file initialization.– Dan Guzman
Nov 18 '18 at 11:32
Thanks Dan. It was enabled. Without it it would take three times longer. It's a weird problem.
– Ashkan
Nov 19 '18 at 13:09
I have to ad that restore on local disks are ok and fast enough but on SAN it takes lots of time.
– Ashkan
Nov 19 '18 at 13:30