DBCC checkdb on tempdbLong running DBCC CHECKDBAlternative of DBCC CHECKDBDBCC CheckDB run into early hardware failureDBCC CHECKDB ran out of memoryDBCC CHECKDB LastKnownGoodDBCC CHECKDB errorsDate of Last DBCC checkdb runDBCC CHECKDB errorDBCC CHECKDB grows tempdb to a huge size - Legacy MS SQL Server 2008 R2 InstanceCan a full DBCC CHECKDB operation be safely offloaded to a restored backup on a different server?
Can Ogre clerics use Purify Food and Drink on humanoid characters?
Identifying unknown map projection for image
Shooting someone's past self using special relativity
How would modern naval warfare have to have developed differently for battleships to still be relevant in the 21st century?
"Correct me if I'm wrong"
Why does Linux list NVMe drives as /dev/nvme0 instead of /dev/sda?
Why do all the teams that I have worked with always finish a sprint without completion of all the stories?
Why does using different ArrayList constructors cause a different growth rate of the internal array?
Encounter design and XP thresholds
How many people are necessary to maintain modern civilisation?
What is the highest voltage from the power supply a Raspberry Pi 3 B can handle without getting damaged?
How can lift be less than thrust that is less than weight?
Understanding the reasoning of the woman who agreed with Shlomo to "cut the baby in half"
Does a vocal melody have any rhythmic responsibility to the underlying arrangement in pop music?
Greeting with "Ho"
Are all Ringwraiths called Nazgûl in LotR?
I don't like coffee, neither beer. How to politely work my way around that in a business situation?
How to maintain a closed environment for one person for a long period of time
LWC - Local Dev - How can I run the local server on HTTPS?
How large would a mega structure have to be to host 1 billion people indefinitely?
How does DC work with natural 20?
Why is it easier to balance a non-moving bike standing up than sitting down?
Has there been any indication at all that further negotiation between the UK and EU is possible?
Trainee keeps passing deadlines for independent learning
DBCC checkdb on tempdb
Long running DBCC CHECKDBAlternative of DBCC CHECKDBDBCC CheckDB run into early hardware failureDBCC CHECKDB ran out of memoryDBCC CHECKDB LastKnownGoodDBCC CHECKDB errorsDate of Last DBCC checkdb runDBCC CHECKDB errorDBCC CHECKDB grows tempdb to a huge size - Legacy MS SQL Server 2008 R2 InstanceCan a full DBCC CHECKDB operation be safely offloaded to a restored backup on a different server?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
Is it recommended to run DBCC CheckDB on tempdb? If yes, can someone please list out the reasons to do so? This could be on any SQL Server version. The one I have is SQL Server 2017.
sql-server dbcc-checkdb
add a comment |
Is it recommended to run DBCC CheckDB on tempdb? If yes, can someone please list out the reasons to do so? This could be on any SQL Server version. The one I have is SQL Server 2017.
sql-server dbcc-checkdb
add a comment |
Is it recommended to run DBCC CheckDB on tempdb? If yes, can someone please list out the reasons to do so? This could be on any SQL Server version. The one I have is SQL Server 2017.
sql-server dbcc-checkdb
Is it recommended to run DBCC CheckDB on tempdb? If yes, can someone please list out the reasons to do so? This could be on any SQL Server version. The one I have is SQL Server 2017.
sql-server dbcc-checkdb
sql-server dbcc-checkdb
asked 8 hours ago
SQLPRODDBASQLPRODDBA
1,13511533
1,13511533
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
No, it's not recommended because it's largely a waste of time. When SQL Server restarts, tempdb is created from scratch. Also, the data in there isn't around long enough to be a good indicator of if there's an issue with it, or the underlying storage.
Both Ola's scripts and Maintenance Plans will skip tempdb when you set up CHECKDB routines.
Another reason why it's largely useless is because if tempdb does get corrupted, you'll know in a hurry. Check out my blog post here where I corrupt tempdb on purpose: DBA Days: Killing TempDB softly
Coming up in SQL Server 2019, we'll be able to use "in memory" technology for system tables in tempdb. While this solves pretty big contention issues, you can't run CHECKDB against in-memory tables. The only way you could check those is if you backed up tempdb, which isn't something you can do.
BACKUP DATABASE tempdb TO DISK = 'D:Backupst.bak'
Msg 3147, Level 16, State 3, Line 27 Backup and restore operations are
not allowed on database tempdb. Msg 3013, Level 16, State 1, Line 27
BACKUP DATABASE is terminating abnormally.
If you really think it's worth running checkdb against a database that SQL Server won't allow you to back up, well, it's your maintenance window, I suppose.
I disagree you "should" run checkdb against tempdb, although it does not checks it completely but I would never miss it. Why, simply because it is a database no matter whether created from scratch.
– Shanky
8 hours ago
@Shanky Wouldn't you rather spend that time checking actual databases? Do your thing, though.
– Erik Darling
8 hours ago
let me rephrase you should run checkdb on all user and system databases, is that fine ?. I believe one has to play second fiddle to time when it comes to consistency.
– Shanky
8 hours ago
Even maintenance plan does not have option to select tempdb :-)
– Kin Shah
8 hours ago
@Shanky You can't even back tempdb up. SQL Server won't let you. But it's cool if you wanna spend your time on it.
– Erik Darling
8 hours ago
add a comment |
Complementing to Erik's answer
Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.
The only reason that I can think of running a checkdb against tempdb is when tempdb gets badly corrupted that the sessions using it starts to get errors.
Also, when tempdb gets corrupted, it is possible that your user databases might have corruption as well.
I personally do not have checkdb done on tempdb.
add a comment |
I have a different perspective here, you "should" run checkdb against all the databases no matter system or user period.Why, simply because it is database which stores information what if there is corruption are you going to restart the SQl Server so that tempdb is cleared and "may be" corruption is gone. I would also like you to read Brent Ozar blog
Q: Should I run CheckDB against TempDB?
Yes– you should. CheckDB can’t do every single check against TempDB that it can against other databases (the output will let you know that it can’t create a snapshot), but it can still run a variety of checks against TempDB and it’s supported to run it. Don’t skip it! You should be checking master, model, msdb and tempdb, plus all your user databases.
Should I run checkdb on Tempdb as often i do for user database ? No you can avoid that if required.
Should I not run checkdb at all on tempdb database ? No, you should run. Do not omit it.
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%2f240831%2fdbcc-checkdb-on-tempdb%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
No, it's not recommended because it's largely a waste of time. When SQL Server restarts, tempdb is created from scratch. Also, the data in there isn't around long enough to be a good indicator of if there's an issue with it, or the underlying storage.
Both Ola's scripts and Maintenance Plans will skip tempdb when you set up CHECKDB routines.
Another reason why it's largely useless is because if tempdb does get corrupted, you'll know in a hurry. Check out my blog post here where I corrupt tempdb on purpose: DBA Days: Killing TempDB softly
Coming up in SQL Server 2019, we'll be able to use "in memory" technology for system tables in tempdb. While this solves pretty big contention issues, you can't run CHECKDB against in-memory tables. The only way you could check those is if you backed up tempdb, which isn't something you can do.
BACKUP DATABASE tempdb TO DISK = 'D:Backupst.bak'
Msg 3147, Level 16, State 3, Line 27 Backup and restore operations are
not allowed on database tempdb. Msg 3013, Level 16, State 1, Line 27
BACKUP DATABASE is terminating abnormally.
If you really think it's worth running checkdb against a database that SQL Server won't allow you to back up, well, it's your maintenance window, I suppose.
I disagree you "should" run checkdb against tempdb, although it does not checks it completely but I would never miss it. Why, simply because it is a database no matter whether created from scratch.
– Shanky
8 hours ago
@Shanky Wouldn't you rather spend that time checking actual databases? Do your thing, though.
– Erik Darling
8 hours ago
let me rephrase you should run checkdb on all user and system databases, is that fine ?. I believe one has to play second fiddle to time when it comes to consistency.
– Shanky
8 hours ago
Even maintenance plan does not have option to select tempdb :-)
– Kin Shah
8 hours ago
@Shanky You can't even back tempdb up. SQL Server won't let you. But it's cool if you wanna spend your time on it.
– Erik Darling
8 hours ago
add a comment |
No, it's not recommended because it's largely a waste of time. When SQL Server restarts, tempdb is created from scratch. Also, the data in there isn't around long enough to be a good indicator of if there's an issue with it, or the underlying storage.
Both Ola's scripts and Maintenance Plans will skip tempdb when you set up CHECKDB routines.
Another reason why it's largely useless is because if tempdb does get corrupted, you'll know in a hurry. Check out my blog post here where I corrupt tempdb on purpose: DBA Days: Killing TempDB softly
Coming up in SQL Server 2019, we'll be able to use "in memory" technology for system tables in tempdb. While this solves pretty big contention issues, you can't run CHECKDB against in-memory tables. The only way you could check those is if you backed up tempdb, which isn't something you can do.
BACKUP DATABASE tempdb TO DISK = 'D:Backupst.bak'
Msg 3147, Level 16, State 3, Line 27 Backup and restore operations are
not allowed on database tempdb. Msg 3013, Level 16, State 1, Line 27
BACKUP DATABASE is terminating abnormally.
If you really think it's worth running checkdb against a database that SQL Server won't allow you to back up, well, it's your maintenance window, I suppose.
I disagree you "should" run checkdb against tempdb, although it does not checks it completely but I would never miss it. Why, simply because it is a database no matter whether created from scratch.
– Shanky
8 hours ago
@Shanky Wouldn't you rather spend that time checking actual databases? Do your thing, though.
– Erik Darling
8 hours ago
let me rephrase you should run checkdb on all user and system databases, is that fine ?. I believe one has to play second fiddle to time when it comes to consistency.
– Shanky
8 hours ago
Even maintenance plan does not have option to select tempdb :-)
– Kin Shah
8 hours ago
@Shanky You can't even back tempdb up. SQL Server won't let you. But it's cool if you wanna spend your time on it.
– Erik Darling
8 hours ago
add a comment |
No, it's not recommended because it's largely a waste of time. When SQL Server restarts, tempdb is created from scratch. Also, the data in there isn't around long enough to be a good indicator of if there's an issue with it, or the underlying storage.
Both Ola's scripts and Maintenance Plans will skip tempdb when you set up CHECKDB routines.
Another reason why it's largely useless is because if tempdb does get corrupted, you'll know in a hurry. Check out my blog post here where I corrupt tempdb on purpose: DBA Days: Killing TempDB softly
Coming up in SQL Server 2019, we'll be able to use "in memory" technology for system tables in tempdb. While this solves pretty big contention issues, you can't run CHECKDB against in-memory tables. The only way you could check those is if you backed up tempdb, which isn't something you can do.
BACKUP DATABASE tempdb TO DISK = 'D:Backupst.bak'
Msg 3147, Level 16, State 3, Line 27 Backup and restore operations are
not allowed on database tempdb. Msg 3013, Level 16, State 1, Line 27
BACKUP DATABASE is terminating abnormally.
If you really think it's worth running checkdb against a database that SQL Server won't allow you to back up, well, it's your maintenance window, I suppose.
No, it's not recommended because it's largely a waste of time. When SQL Server restarts, tempdb is created from scratch. Also, the data in there isn't around long enough to be a good indicator of if there's an issue with it, or the underlying storage.
Both Ola's scripts and Maintenance Plans will skip tempdb when you set up CHECKDB routines.
Another reason why it's largely useless is because if tempdb does get corrupted, you'll know in a hurry. Check out my blog post here where I corrupt tempdb on purpose: DBA Days: Killing TempDB softly
Coming up in SQL Server 2019, we'll be able to use "in memory" technology for system tables in tempdb. While this solves pretty big contention issues, you can't run CHECKDB against in-memory tables. The only way you could check those is if you backed up tempdb, which isn't something you can do.
BACKUP DATABASE tempdb TO DISK = 'D:Backupst.bak'
Msg 3147, Level 16, State 3, Line 27 Backup and restore operations are
not allowed on database tempdb. Msg 3013, Level 16, State 1, Line 27
BACKUP DATABASE is terminating abnormally.
If you really think it's worth running checkdb against a database that SQL Server won't allow you to back up, well, it's your maintenance window, I suppose.
edited 8 hours ago
answered 8 hours ago
Erik DarlingErik Darling
24.4k1376125
24.4k1376125
I disagree you "should" run checkdb against tempdb, although it does not checks it completely but I would never miss it. Why, simply because it is a database no matter whether created from scratch.
– Shanky
8 hours ago
@Shanky Wouldn't you rather spend that time checking actual databases? Do your thing, though.
– Erik Darling
8 hours ago
let me rephrase you should run checkdb on all user and system databases, is that fine ?. I believe one has to play second fiddle to time when it comes to consistency.
– Shanky
8 hours ago
Even maintenance plan does not have option to select tempdb :-)
– Kin Shah
8 hours ago
@Shanky You can't even back tempdb up. SQL Server won't let you. But it's cool if you wanna spend your time on it.
– Erik Darling
8 hours ago
add a comment |
I disagree you "should" run checkdb against tempdb, although it does not checks it completely but I would never miss it. Why, simply because it is a database no matter whether created from scratch.
– Shanky
8 hours ago
@Shanky Wouldn't you rather spend that time checking actual databases? Do your thing, though.
– Erik Darling
8 hours ago
let me rephrase you should run checkdb on all user and system databases, is that fine ?. I believe one has to play second fiddle to time when it comes to consistency.
– Shanky
8 hours ago
Even maintenance plan does not have option to select tempdb :-)
– Kin Shah
8 hours ago
@Shanky You can't even back tempdb up. SQL Server won't let you. But it's cool if you wanna spend your time on it.
– Erik Darling
8 hours ago
I disagree you "should" run checkdb against tempdb, although it does not checks it completely but I would never miss it. Why, simply because it is a database no matter whether created from scratch.
– Shanky
8 hours ago
I disagree you "should" run checkdb against tempdb, although it does not checks it completely but I would never miss it. Why, simply because it is a database no matter whether created from scratch.
– Shanky
8 hours ago
@Shanky Wouldn't you rather spend that time checking actual databases? Do your thing, though.
– Erik Darling
8 hours ago
@Shanky Wouldn't you rather spend that time checking actual databases? Do your thing, though.
– Erik Darling
8 hours ago
let me rephrase you should run checkdb on all user and system databases, is that fine ?. I believe one has to play second fiddle to time when it comes to consistency.
– Shanky
8 hours ago
let me rephrase you should run checkdb on all user and system databases, is that fine ?. I believe one has to play second fiddle to time when it comes to consistency.
– Shanky
8 hours ago
Even maintenance plan does not have option to select tempdb :-)
– Kin Shah
8 hours ago
Even maintenance plan does not have option to select tempdb :-)
– Kin Shah
8 hours ago
@Shanky You can't even back tempdb up. SQL Server won't let you. But it's cool if you wanna spend your time on it.
– Erik Darling
8 hours ago
@Shanky You can't even back tempdb up. SQL Server won't let you. But it's cool if you wanna spend your time on it.
– Erik Darling
8 hours ago
add a comment |
Complementing to Erik's answer
Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.
The only reason that I can think of running a checkdb against tempdb is when tempdb gets badly corrupted that the sessions using it starts to get errors.
Also, when tempdb gets corrupted, it is possible that your user databases might have corruption as well.
I personally do not have checkdb done on tempdb.
add a comment |
Complementing to Erik's answer
Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.
The only reason that I can think of running a checkdb against tempdb is when tempdb gets badly corrupted that the sessions using it starts to get errors.
Also, when tempdb gets corrupted, it is possible that your user databases might have corruption as well.
I personally do not have checkdb done on tempdb.
add a comment |
Complementing to Erik's answer
Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.
The only reason that I can think of running a checkdb against tempdb is when tempdb gets badly corrupted that the sessions using it starts to get errors.
Also, when tempdb gets corrupted, it is possible that your user databases might have corruption as well.
I personally do not have checkdb done on tempdb.
Complementing to Erik's answer
Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.
The only reason that I can think of running a checkdb against tempdb is when tempdb gets badly corrupted that the sessions using it starts to get errors.
Also, when tempdb gets corrupted, it is possible that your user databases might have corruption as well.
I personally do not have checkdb done on tempdb.
edited 8 hours ago
Erik Darling
24.4k1376125
24.4k1376125
answered 8 hours ago
Kin ShahKin Shah
54.5k484197
54.5k484197
add a comment |
add a comment |
I have a different perspective here, you "should" run checkdb against all the databases no matter system or user period.Why, simply because it is database which stores information what if there is corruption are you going to restart the SQl Server so that tempdb is cleared and "may be" corruption is gone. I would also like you to read Brent Ozar blog
Q: Should I run CheckDB against TempDB?
Yes– you should. CheckDB can’t do every single check against TempDB that it can against other databases (the output will let you know that it can’t create a snapshot), but it can still run a variety of checks against TempDB and it’s supported to run it. Don’t skip it! You should be checking master, model, msdb and tempdb, plus all your user databases.
Should I run checkdb on Tempdb as often i do for user database ? No you can avoid that if required.
Should I not run checkdb at all on tempdb database ? No, you should run. Do not omit it.
add a comment |
I have a different perspective here, you "should" run checkdb against all the databases no matter system or user period.Why, simply because it is database which stores information what if there is corruption are you going to restart the SQl Server so that tempdb is cleared and "may be" corruption is gone. I would also like you to read Brent Ozar blog
Q: Should I run CheckDB against TempDB?
Yes– you should. CheckDB can’t do every single check against TempDB that it can against other databases (the output will let you know that it can’t create a snapshot), but it can still run a variety of checks against TempDB and it’s supported to run it. Don’t skip it! You should be checking master, model, msdb and tempdb, plus all your user databases.
Should I run checkdb on Tempdb as often i do for user database ? No you can avoid that if required.
Should I not run checkdb at all on tempdb database ? No, you should run. Do not omit it.
add a comment |
I have a different perspective here, you "should" run checkdb against all the databases no matter system or user period.Why, simply because it is database which stores information what if there is corruption are you going to restart the SQl Server so that tempdb is cleared and "may be" corruption is gone. I would also like you to read Brent Ozar blog
Q: Should I run CheckDB against TempDB?
Yes– you should. CheckDB can’t do every single check against TempDB that it can against other databases (the output will let you know that it can’t create a snapshot), but it can still run a variety of checks against TempDB and it’s supported to run it. Don’t skip it! You should be checking master, model, msdb and tempdb, plus all your user databases.
Should I run checkdb on Tempdb as often i do for user database ? No you can avoid that if required.
Should I not run checkdb at all on tempdb database ? No, you should run. Do not omit it.
I have a different perspective here, you "should" run checkdb against all the databases no matter system or user period.Why, simply because it is database which stores information what if there is corruption are you going to restart the SQl Server so that tempdb is cleared and "may be" corruption is gone. I would also like you to read Brent Ozar blog
Q: Should I run CheckDB against TempDB?
Yes– you should. CheckDB can’t do every single check against TempDB that it can against other databases (the output will let you know that it can’t create a snapshot), but it can still run a variety of checks against TempDB and it’s supported to run it. Don’t skip it! You should be checking master, model, msdb and tempdb, plus all your user databases.
Should I run checkdb on Tempdb as often i do for user database ? No you can avoid that if required.
Should I not run checkdb at all on tempdb database ? No, you should run. Do not omit it.
edited 8 hours ago
answered 8 hours ago
ShankyShanky
15.2k42346
15.2k42346
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%2f240831%2fdbcc-checkdb-on-tempdb%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