Where to find tempdb logs in SQL Server?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
-1
I'm currently using SQL Server 2014. In a stored procedure, I'm inserting data into a local temporary table (#TestTemporaryTable). I want to see the logs of data insertion into temp table, how can I fetch it from tempdb? And also, I want to know for how long these logs will exist?
sql
sql-server logging edited Nov 26 '18 at 16:56
marc_s
584k13011241271
asked Nov 26 '18 at 16:40
Anoop DeshpandeAnoop Deshpande
535
add a comment |
-1
I'm currently using SQL Server 2014. In a stored procedure, I'm inserting data into a local temporary table (#TestTemporaryTable). I want to see the logs of data insertion into temp table, how can I fetch it from tempdb? And also, I want to know for how long these logs will exist?
sql
sql-server logging edited Nov 26 '18 at 16:56
marc_s
584k13011241271
asked Nov 26 '18 at 16:40
Anoop DeshpandeAnoop Deshpande
535
1
What kind of log are you looking for? In any case,tempdbis temporary. That means it's cleared every time the server restarts. Local temporary tables are discarded as soon as the connection that created them closes
– Panagiotis Kanavos
Nov 26 '18 at 16:44
2
What do you mean by "logs"?tempdbis (by default) set to the Simple Recovery Model (and for good reason), which truncates the transaction log as soon as the transaction is commited/rolled back. You can't inspect the log files for data in the first place anyway (can you imagine the security concerns that would have?), but certainly the logs fortempdbwill be lost as soon as the transaction completes.
– Larnu
Nov 26 '18 at 16:46
I want to view the ones which gets logged when rows are inserted into temporary table
– Anoop Deshpande
Nov 26 '18 at 16:47
2
Logged where? If you have a custom logging tool, then you know what that is; we don't know.
– Larnu
Nov 26 '18 at 16:48
add a comment |
-1
-1
-1
I'm currently using SQL Server 2014. In a stored procedure, I'm inserting data into a local temporary table (#TestTemporaryTable). I want to see the logs of data insertion into temp table, how can I fetch it from tempdb? And also, I want to know for how long these logs will exist?
sql
sql-server logging edited Nov 26 '18 at 16:56
marc_s
584k13011241271
asked Nov 26 '18 at 16:40
Anoop DeshpandeAnoop Deshpande
535
I'm currently using SQL Server 2014. In a stored procedure, I'm inserting data into a local temporary table (#TestTemporaryTable). I want to see the logs of data insertion into temp table, how can I fetch it from tempdb? And also, I want to know for how long these logs will exist?
sql
sql-server logging sql
sql-server logging edited Nov 26 '18 at 16:56
marc_s
584k13011241271
asked Nov 26 '18 at 16:40
Anoop DeshpandeAnoop Deshpande
535
edited Nov 26 '18 at 16:56
marc_s
584k13011241271
asked Nov 26 '18 at 16:40
Anoop DeshpandeAnoop Deshpande
535
edited Nov 26 '18 at 16:56
marc_s
584k13011241271
edited Nov 26 '18 at 16:56
marc_s
584k13011241271
edited Nov 26 '18 at 16:56
marc_s
584k13011241271
584k13011241271
asked Nov 26 '18 at 16:40
Anoop DeshpandeAnoop Deshpande
535
asked Nov 26 '18 at 16:40
Anoop DeshpandeAnoop Deshpande
535
asked Nov 26 '18 at 16:40
Anoop DeshpandeAnoop Deshpande
535
535
1
What kind of log are you looking for? In any case,tempdbis temporary. That means it's cleared every time the server restarts. Local temporary tables are discarded as soon as the connection that created them closes
– Panagiotis Kanavos
Nov 26 '18 at 16:44
2
What do you mean by "logs"?tempdbis (by default) set to the Simple Recovery Model (and for good reason), which truncates the transaction log as soon as the transaction is commited/rolled back. You can't inspect the log files for data in the first place anyway (can you imagine the security concerns that would have?), but certainly the logs fortempdbwill be lost as soon as the transaction completes.
– Larnu
Nov 26 '18 at 16:46
I want to view the ones which gets logged when rows are inserted into temporary table
– Anoop Deshpande
Nov 26 '18 at 16:47
2
Logged where? If you have a custom logging tool, then you know what that is; we don't know.
– Larnu
Nov 26 '18 at 16:48
add a comment |
1
What kind of log are you looking for? In any case,tempdbis temporary. That means it's cleared every time the server restarts. Local temporary tables are discarded as soon as the connection that created them closes
– Panagiotis Kanavos
Nov 26 '18 at 16:44
2
What do you mean by "logs"?tempdbis (by default) set to the Simple Recovery Model (and for good reason), which truncates the transaction log as soon as the transaction is commited/rolled back. You can't inspect the log files for data in the first place anyway (can you imagine the security concerns that would have?), but certainly the logs fortempdbwill be lost as soon as the transaction completes.
– Larnu
Nov 26 '18 at 16:46
I want to view the ones which gets logged when rows are inserted into temporary table
– Anoop Deshpande
Nov 26 '18 at 16:47
2
Logged where? If you have a custom logging tool, then you know what that is; we don't know.
– Larnu
Nov 26 '18 at 16:48
1
1
What kind of log are you looking for? In any case,
tempdb is temporary. That means it's cleared every time the server restarts. Local temporary tables are discarded as soon as the connection that created them closes– Panagiotis Kanavos
Nov 26 '18 at 16:44
What kind of log are you looking for? In any case,
tempdb is temporary. That means it's cleared every time the server restarts. Local temporary tables are discarded as soon as the connection that created them closes– Panagiotis Kanavos
Nov 26 '18 at 16:44
2
2
What do you mean by "logs"?
tempdb is (by default) set to the Simple Recovery Model (and for good reason), which truncates the transaction log as soon as the transaction is commited/rolled back. You can't inspect the log files for data in the first place anyway (can you imagine the security concerns that would have?), but certainly the logs for tempdb will be lost as soon as the transaction completes.– Larnu
Nov 26 '18 at 16:46
What do you mean by "logs"?
tempdb is (by default) set to the Simple Recovery Model (and for good reason), which truncates the transaction log as soon as the transaction is commited/rolled back. You can't inspect the log files for data in the first place anyway (can you imagine the security concerns that would have?), but certainly the logs for tempdb will be lost as soon as the transaction completes.– Larnu
Nov 26 '18 at 16:46
I want to view the ones which gets logged when rows are inserted into temporary table
– Anoop Deshpande
Nov 26 '18 at 16:47
I want to view the ones which gets logged when rows are inserted into temporary table
– Anoop Deshpande
Nov 26 '18 at 16:47
2
2
Logged where? If you have a custom logging tool, then you know what that is; we don't know.
– Larnu
Nov 26 '18 at 16:48
Logged where? If you have a custom logging tool, then you know what that is; we don't know.
– Larnu
Nov 26 '18 at 16:48
add a comment |
1 Answer
1
active
oldest
votes
0
You can examine the log records with the undocumented fn_dblog
eg
SELECT * FROM tempdb.sys.fn_dblog(NULL, NULL)
answered Nov 26 '18 at 17:31
David Browne - MicrosoftDavid Browne - Microsoft
17.2k2827
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%2f53485494%2fwhere-to-find-tempdb-logs-in-sql-server%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
You can examine the log records with the undocumented fn_dblog
eg
SELECT * FROM tempdb.sys.fn_dblog(NULL, NULL)
answered Nov 26 '18 at 17:31
David Browne - MicrosoftDavid Browne - Microsoft
17.2k2827
add a comment |
0
You can examine the log records with the undocumented fn_dblog
eg
SELECT * FROM tempdb.sys.fn_dblog(NULL, NULL)
answered Nov 26 '18 at 17:31
David Browne - MicrosoftDavid Browne - Microsoft
17.2k2827
add a comment |
0
0
0
You can examine the log records with the undocumented fn_dblog
eg
SELECT * FROM tempdb.sys.fn_dblog(NULL, NULL)
answered Nov 26 '18 at 17:31
David Browne - MicrosoftDavid Browne - Microsoft
17.2k2827
You can examine the log records with the undocumented fn_dblog
eg
SELECT * FROM tempdb.sys.fn_dblog(NULL, NULL)
answered Nov 26 '18 at 17:31
David Browne - MicrosoftDavid Browne - Microsoft
17.2k2827
answered Nov 26 '18 at 17:31
David Browne - MicrosoftDavid Browne - Microsoft
17.2k2827
answered Nov 26 '18 at 17:31
David Browne - MicrosoftDavid Browne - Microsoft
17.2k2827
answered Nov 26 '18 at 17:31
David Browne - MicrosoftDavid Browne - Microsoft
17.2k2827
17.2k2827
add a comment |
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%2f53485494%2fwhere-to-find-tempdb-logs-in-sql-server%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
What kind of log are you looking for? In any case,
tempdbis temporary. That means it's cleared every time the server restarts. Local temporary tables are discarded as soon as the connection that created them closes– Panagiotis Kanavos
Nov 26 '18 at 16:44
2
What do you mean by "logs"?
tempdbis (by default) set to the Simple Recovery Model (and for good reason), which truncates the transaction log as soon as the transaction is commited/rolled back. You can't inspect the log files for data in the first place anyway (can you imagine the security concerns that would have?), but certainly the logs fortempdbwill be lost as soon as the transaction completes.– Larnu
Nov 26 '18 at 16:46
I want to view the ones which gets logged when rows are inserted into temporary table
– Anoop Deshpande
Nov 26 '18 at 16:47
2
Logged where? If you have a custom logging tool, then you know what that is; we don't know.
– Larnu
Nov 26 '18 at 16:48