Top Questions

Here I would like to post some interesting SQL Server questions shared by one of my colleague and friend Sudarsan and I would like to thank him for sharing the same.

1. When a database is restored, What will be the create date for that restored database? Will it be the actual create date of that database or the restored time?
Ans: It will be actual create date of the database.
2. How do we read data from resource database?
Ans: Bring down the instance. Copy the mdf and ldf files of the resource database to a different location and attach them as a new database with different name.
3. How do we make a table read-only without causing any downtime to the application?
Ans: Here we have found two answers so far and the forum is still open for more anwers. If you find one, please submit them as comments to this page.
Ans1: Create a trigger to catch any insert or update statements on that table and make it perform a ROLLBACK.
Ans2: (Application must support multiple filegroups) Create a new filegroup, move the table to that filegroup and then make that filegroup as read-only. You are done!
4. There is a scenario where in there was no freespace in D drive and unfortunately all your database files where in that drive. What could be done to free up some space in D drive.
Ans1: Create a new file in a different filegroup on to a different drive where in there is enough freespace. Then execute index rebuild command on to the new filegroup. Then the indexes will be dropped from the D drive datafiles and then recreated into the newly created filegroup.
5. Suppose we had 10 windows authentication logins added to SQL Server. And later a few of those employees left the company and their windows NT accounts were disabled by the wintel team. How do you find the list of windows authentication logins in SQL server which are deactivated at windows or the active directory by checking from within the SQL Server.
Ans: Those logins are termed as orphaned logins and we can find them out using the stored procedure sp_validatelogins. It will list all logins that do not exist in Windows users or Active Directory.
6. What is the best recommended way of fixing orphaned users instead of using ‘sp_change_users_login’?
Ans: The best recommended way of fixing orphaned users while migrating databases from one SQL instance to another is by creating logins at the destination SQL instance providing the same SID as that of the login at source SQL instance using CREATE LOGIN TSQL command. SID of a user can be obtained from master..syslogins.