locked
MSDB Database - User Tables RRS feed

  • Question

  • Hi,

    I have a 'smart' rebuilding index mechanism based on fragmentation, and for it to work I need to have a control table, but this table isn't specific to any user DB, it should be considered as a system table.

    So the question is, is it safe to create user tables on msdb/master without the risk of being swiped away at any moment? Is there any impact on backups?

    (should I use msdb/master? or any other?)

    Thanks in Advance,

    José

    Friday, July 17, 2015 10:17 AM

Answers

  • (should I use msdb/master? or any other?)

    I would say: Any other. Why not creating a new database as "your System DB" with that table, it's easier to manage?

    Lets say you are going to move SQL Server to a new Server, you would install first SQL Server with it's all system database; but would you restore all System DB's of the old Server to the new one, just to get back your control table?

    With a separate user database it is an easy one.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, July 17, 2015 10:24 AM
    Answerer
  • I am not aware about any risk as such when you create a table in master DB. I have seen many systems doing it. Its safer to be on master DB

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, July 17, 2015 10:43 AM
    Answerer
  • Master database is totally fine for creating control tables, stored procedures.

    Also take a look at

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Free, good flexible solution for index and stats maintenance, and it actually creates all of the executable in Master db :)

    Let me know if it helps

    Wednesday, July 22, 2015 3:11 AM
  • you can use either master or msdb database for creating a table or stored procedure.

    It is preferable to use a separate user database. 

    Wednesday, July 22, 2015 3:25 AM

All replies

  • (should I use msdb/master? or any other?)

    I would say: Any other. Why not creating a new database as "your System DB" with that table, it's easier to manage?

    Lets say you are going to move SQL Server to a new Server, you would install first SQL Server with it's all system database; but would you restore all System DB's of the old Server to the new one, just to get back your control table?

    With a separate user database it is an easy one.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, July 17, 2015 10:24 AM
    Answerer
  • I am not aware about any risk as such when you create a table in master DB. I have seen many systems doing it. Its safer to be on master DB

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, July 17, 2015 10:43 AM
    Answerer
  • Master database is totally fine for creating control tables, stored procedures.

    Also take a look at

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Free, good flexible solution for index and stats maintenance, and it actually creates all of the executable in Master db :)

    Let me know if it helps

    Wednesday, July 22, 2015 3:11 AM
  • you can use either master or msdb database for creating a table or stored procedure.

    It is preferable to use a separate user database. 

    Wednesday, July 22, 2015 3:25 AM