“The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.” after adding indexes to temp tables
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
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 yesterday
LowlyDBA
6,66352241
asked yesterday
jindrich
61
New contributor
jindrich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
1
down vote
favorite
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 yesterday
LowlyDBA
6,66352241
asked yesterday
jindrich
61
New contributor
jindrich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
yesterday
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
yesterday
You can runDBCC OPENTRANand see what's going on.
– Terry Carmen
yesterday
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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 yesterday
LowlyDBA
6,66352241
asked yesterday
jindrich
61
New contributor
jindrich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 yesterday
LowlyDBA
6,66352241
asked yesterday
jindrich
61
New contributor
jindrich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited yesterday
LowlyDBA
6,66352241
asked yesterday
jindrich
61
New contributor
jindrich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited yesterday
LowlyDBA
6,66352241
edited yesterday
LowlyDBA
6,66352241
edited yesterday
LowlyDBA
6,66352241
6,66352241
asked yesterday
jindrich
61
New contributor
jindrich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked yesterday
jindrich
61
asked yesterday
jindrich
61
61
New contributor
jindrich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
jindrich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
jindrich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
yesterday
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
yesterday
You can runDBCC OPENTRANand see what's going on.
– Terry Carmen
yesterday
add a comment |
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
yesterday
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
yesterday
You can runDBCC OPENTRANand see what's going on.
– Terry Carmen
yesterday
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
yesterday
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
yesterday
1
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
yesterday
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
yesterday
You can run
DBCC OPENTRAN and see what's going on.– Terry Carmen
yesterday
You can run
DBCC OPENTRAN and see what's going on.– Terry Carmen
yesterday
add a comment |
1 Answer
1
active
oldest
votes
up vote
5
down vote
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 yesterday
Tony Hinkle
1,5241417
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
5
down vote
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 yesterday
Tony Hinkle
1,5241417
add a comment |
up vote
5
down vote
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 yesterday
Tony Hinkle
1,5241417
add a comment |
up vote
5
down vote
up vote
5
down vote
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 yesterday
Tony Hinkle
1,5241417
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 yesterday
Tony Hinkle
1,5241417
answered yesterday
Tony Hinkle
1,5241417
answered yesterday
Tony Hinkle
1,5241417
answered yesterday
Tony Hinkle
1,5241417
1,5241417
add a comment |
add a comment |
jindrich is a new contributor. Be nice, and check out our Code of Conduct.
jindrich is a new contributor. Be nice, and check out our Code of Conduct.
jindrich is a new contributor. Be nice, and check out our Code of Conduct.
jindrich is a new contributor. Be nice, and check out our Code of Conduct.
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
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
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
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
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
Yes, it can be. Add details about servers versions into your question.
– Denis Rubashkin
yesterday
1
jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
– LowlyDBA
yesterday
You can run
DBCC OPENTRANand see what's going on.– Terry Carmen
yesterday