DBCC checkdb on tempdbLong running DBCC CHECKDBAlternative of DBCC CHECKDBDBCC CheckDB run into early...

What can I do with a research project that is my university’s intellectual property?

Dates on degrees don’t make sense – will people care?

Why don't countries like Japan just print more money?

Why does using different ArrayList constructors cause a different growth rate of the internal array?

How to make clear to people I don't want to answer their "Where are you from?" question?

Is there any difference between Т34ВМ1 and КМ1858ВМ1/3?

How large would a mega structure have to be to host 1 billion people indefinitely?

Explain why a line can never intersect a plane in exactly two points.

Story about hunting giant lizards for hides on privately owned planet

Is there a term for the belief that "if it's legal, it's moral"?

career in signal processing

Can Ogre clerics use Purify Food and Drink on humanoid characters?

How does a blind passenger not die, if driver becomes unconscious

Can humans ever directly see a few photons at a time? Can a human see a single photon?

Loss of power when I remove item from the outlet

RandomInteger with equal number of 1 and -1

How did Gollum enter Moria?

What is "industrial ethernet"?

Do I need a shock-proof watch for cycling?

What determines the direction in which motor proteins go?

Android Material and appcompat Manifest merger failed in react-native or ExpoKit

What is the origin of Scooby-Doo's name?

Identifying unknown map projection for image

How do I professionally let my manager know I'll quit over an issue?



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;
}







3















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.










share|improve this question





























    3















    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.










    share|improve this question

























      3












      3








      3








      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.










      share|improve this question














      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






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked 8 hours ago









      SQLPRODDBASQLPRODDBA

      1,13511533




      1,13511533






















          3 Answers
          3






          active

          oldest

          votes


















          4














          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.






          share|improve this answer


























          • 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



















          2














          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.






          share|improve this answer

































            0














            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.






            share|improve this answer


























              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
              });


              }
              });














              draft saved

              draft discarded


















              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









              4














              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.






              share|improve this answer


























              • 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
















              4














              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.






              share|improve this answer


























              • 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














              4












              4








              4







              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.






              share|improve this answer















              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.







              share|improve this answer














              share|improve this answer



              share|improve this answer








              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



















              • 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













              2














              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.






              share|improve this answer






























                2














                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.






                share|improve this answer




























                  2












                  2








                  2







                  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.






                  share|improve this answer















                  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.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited 8 hours ago









                  Erik Darling

                  24.4k1376125




                  24.4k1376125










                  answered 8 hours ago









                  Kin ShahKin Shah

                  54.5k484197




                  54.5k484197























                      0














                      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.






                      share|improve this answer






























                        0














                        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.






                        share|improve this answer




























                          0












                          0








                          0







                          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.






                          share|improve this answer















                          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.







                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited 8 hours ago

























                          answered 8 hours ago









                          ShankyShanky

                          15.2k42346




                          15.2k42346






























                              draft saved

                              draft discarded




















































                              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.




                              draft saved


                              draft discarded














                              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





















































                              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







                              Popular posts from this blog

                              Taj Mahal Inhaltsverzeichnis Aufbau | Geschichte | 350-Jahr-Feier | Heutige Bedeutung | Siehe auch |...

                              Baia Sprie Cuprins Etimologie | Istorie | Demografie | Politică și administrație | Arii naturale...

                              Nicolae Petrescu-Găină Cuprins Biografie | Opera | In memoriam | Varia | Controverse, incertitudini...