2

Suppose I have created a new database in SQL Server with a file group FG1 and marked this as the default. All my user tables etc are now created in FG1. However I also created a file that is part of the PRIMARY file group. Apparently the system objects are stored in this primary file. I was wondering

What kind of system objects are these? Can I inspect them? Do they appear in the 'System' Database?

Apparently there is some reliability gain to doing things this way. Suppose the file in my primary file group got corrupted, can I still recover my database?

8

The system objects are the objects which store the schema for all your objects. The stored procedures, table schemas, etc. are all stored in these system objects.

If the system objects become corrupt you have to restore the primary filegroup.

You can view the data within the system objects by querying the system catalog views such as sys.tables, sys.procedures, etc.

Do they appear in the 'System' Database?

Not sure what you are talking about, there's no database called "System". The system databases are master, msdb, model (to some extent) and tempdb. There is no user database data stored within the system databases. (Things like logins, jobs, SSIS packages not withstanding.)

  • Thanks that was helpful. Yes my comment about the System database wasn't clear, I meant the databases that appear under the folder 'System Databases' as you surmised. By the way if I create multiple files in the PRIMARY filegroup, only the first file stores the system data, but does it also contain user data? – Antonio2011a Sep 15 '11 at 5:21
  • 2
    If you add multiple files to the PRIMARY filegroup, the system objects could be in both files. User data would also be in both files. – mrdenny Sep 15 '11 at 5:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.