SQL SERVER Interview Questions

Q)  What are all the system database?

A)  We have 6 system databases:
  • Master
  • Model
  • MSDB
  • TempDB
  • Resource
  • Distribution
Master, Model, MSDB and TempDB are frequently used system databases.
Resource is not visible in server. They will be internally stored in server.
The distribution database is present only if you've configured replication and specified that the server act as a distributor. The

Q)  How to find the size of a database?

A)  We can find the size of the database using many ways. Some of them are:
  1. sp_helpdb is a better command which provides complete information about databases.
  2. sp_database will also provide the database size.
  3. sys.master_files table stores information related database.
            Use the following query to find the size of the database:
                       SELECT DB_NAME(database_id) AS DatabaseName,
                       Name,  (size*8)/1024 SizeMB
                       FROM sys.master_files
                       WHERE DB_NAME(database_id) = ‘master’

Q)  Which command will delete the backup history from msdb?

A)  Executing sp_delete_backuphistory stored procedure will remove backup history from database.



Q)  Can we create user objects in system database?

A)  Yes, We can. But its not recommended to create objects in System databases.


Q)  How can you confirm your server have Resource database in it?

A)  This databse is by all means hidden from the user. You can't view it in Object Explorer nor with the use of sp_helpDb or selecting from a sys.databases view.

So how do we know it's even there? Pretty simple. Go to the Data directory of your SQL installation where the databases are put by default.

In my system data directory is in the following path:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Here is the screenshot for the Resource database:

This entry was posted in . Bookmark the permalink.

4 Responses to SQL SERVER Interview Questions

  1. Hi, This is very useful. So far I never knew there is a system database with name Resource.

    Thanks for the info. But, here questions are very limited. Add few more questions.

    ReplyDelete
  2. Thanks for your suggestion. Will add more questions soon. Daily new articles will be added to this blog. So, Keep watching this space for more updates.

    ReplyDelete
  3. Hi, very useful post. I've just bumped into it and found it concrete and useful, very straight to the point. With your tips and some tricks I've heard during the webinars organized by
    http://www.blog.ivyexec.com I'm sure I'll easily go through career change. Thanks once again!

    ReplyDelete
  4. Hi...Thanks alot for your comment. Its really encourage for new bloggers like me to write new posts.
    Thanks again!!

    ReplyDelete