“The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.” after adding indexes to temp tables
Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed due to this error and the server needed to be restarted. Can this be related to adding the indexes or it could be something totally else?
Client's server version:
- Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
Testing server version:
- Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index sql-server-2014 tempdb temporary-tables
edited Nov 13 '18 at 15:30
LowlyDBA
7,04252542
asked Nov 13 '18 at 13:15
jindrichjindrich
61
add a comment |
Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed due to this error and the server needed to be restarted. Can this be related to adding the indexes or it could be something totally else?
Client's server version:
- Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
Testing server version:
- Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index sql-server-2014 tempdb temporary-tables
edited Nov 13 '18 at 15:30
LowlyDBA
7,04252542
asked Nov 13 '18 at 13:15
jindrichjindrich
61
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
Nov 13 '18 at 13:24
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
Nov 13 '18 at 14:28
You can runDBCC OPENTRANand see what's going on.
– Terry Carmen
Nov 13 '18 at 18:18
add a comment |
Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed due to this error and the server needed to be restarted. Can this be related to adding the indexes or it could be something totally else?
Client's server version:
- Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
Testing server version:
- Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index sql-server-2014 tempdb temporary-tables
edited Nov 13 '18 at 15:30
LowlyDBA
7,04252542
asked Nov 13 '18 at 13:15
jindrichjindrich
61
Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed due to this error and the server needed to be restarted. Can this be related to adding the indexes or it could be something totally else?
Client's server version:
- Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
Testing server version:
- Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index sql-server-2014 tempdb temporary-tables
sql-server index sql-server-2014 tempdb temporary-tables
edited Nov 13 '18 at 15:30
LowlyDBA
7,04252542
asked Nov 13 '18 at 13:15
jindrichjindrich
61
edited Nov 13 '18 at 15:30
LowlyDBA
7,04252542
asked Nov 13 '18 at 13:15
jindrichjindrich
61
edited Nov 13 '18 at 15:30
LowlyDBA
7,04252542
edited Nov 13 '18 at 15:30
LowlyDBA
7,04252542
edited Nov 13 '18 at 15:30
LowlyDBA
7,04252542
7,04252542
asked Nov 13 '18 at 13:15
jindrichjindrich
61
asked Nov 13 '18 at 13:15
jindrichjindrich
61
asked Nov 13 '18 at 13:15
jindrichjindrich
61
61
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
Nov 13 '18 at 13:24
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
Nov 13 '18 at 14:28
You can runDBCC OPENTRANand see what's going on.
– Terry Carmen
Nov 13 '18 at 18:18
add a comment |
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
Nov 13 '18 at 13:24
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
Nov 13 '18 at 14:28
You can runDBCC OPENTRANand see what's going on.
– Terry Carmen
Nov 13 '18 at 18:18
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
Nov 13 '18 at 13:24
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
Nov 13 '18 at 13:24
1
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
Nov 13 '18 at 14:28
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
Nov 13 '18 at 14:28
You can run
DBCC OPENTRAN and see what's going on.– Terry Carmen
Nov 13 '18 at 18:18
You can run
DBCC OPENTRAN and see what's going on.– Terry Carmen
Nov 13 '18 at 18:18
add a comment |
1 Answer
1
active
oldest
votes
An index create on a temp table will use space in the tempdb transaction log while the index is being built. So yes, the index could have caused the issue in the general sense that it added enough transactions to the log file that it ran out of space.
Transaction Log Disk Space for Index Operations
answered Nov 13 '18 at 13:32
Tony HinkleTony Hinkle
2,3631422
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%2f222429%2fthe-transaction-log-for-database-tempdb-is-full-due-to-active-transaction%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
An index create on a temp table will use space in the tempdb transaction log while the index is being built. So yes, the index could have caused the issue in the general sense that it added enough transactions to the log file that it ran out of space.
Transaction Log Disk Space for Index Operations
answered Nov 13 '18 at 13:32
Tony HinkleTony Hinkle
2,3631422
add a comment |
An index create on a temp table will use space in the tempdb transaction log while the index is being built. So yes, the index could have caused the issue in the general sense that it added enough transactions to the log file that it ran out of space.
Transaction Log Disk Space for Index Operations
answered Nov 13 '18 at 13:32
Tony HinkleTony Hinkle
2,3631422
add a comment |
An index create on a temp table will use space in the tempdb transaction log while the index is being built. So yes, the index could have caused the issue in the general sense that it added enough transactions to the log file that it ran out of space.
Transaction Log Disk Space for Index Operations
answered Nov 13 '18 at 13:32
Tony HinkleTony Hinkle
2,3631422
An index create on a temp table will use space in the tempdb transaction log while the index is being built. So yes, the index could have caused the issue in the general sense that it added enough transactions to the log file that it ran out of space.
Transaction Log Disk Space for Index Operations
answered Nov 13 '18 at 13:32
Tony HinkleTony Hinkle
2,3631422
answered Nov 13 '18 at 13:32
Tony HinkleTony Hinkle
2,3631422
answered Nov 13 '18 at 13:32
Tony HinkleTony Hinkle
2,3631422
answered Nov 13 '18 at 13:32
Tony HinkleTony Hinkle
2,3631422
2,3631422
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%2f222429%2fthe-transaction-log-for-database-tempdb-is-full-due-to-active-transaction%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
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
Nov 13 '18 at 13:24
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
Nov 13 '18 at 14:28
You can run
DBCC OPENTRANand see what's going on.– Terry Carmen
Nov 13 '18 at 18:18