Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

1 Answer

up vote 7 down vote accepted

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.)

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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