how to create new users/ login, how to create new job, what are the job involved in log shipping and replication, how to optimize query, how to check slowness, how to add login in standby database, how to add article in replication, difference between all replication and how to configure it, what is isolation level, what is file factor, how to find who deleted the database,how to troubleshoot if the database in suspect mode, how to shrink files, types of backup, how to restore database

Question

Accepted Answer

The question is about SQL Database Administration and covers various topics such as user management, job creation, query optimization, replication, troubleshooting, and backup/restore processes.

To create new users/login, use the CREATE LOGIN and CREATE USER statements.
To create a new job, use the SQL Server Agent or SQL Server Management Studio (SSMS).
Log shipping involves jobs like backup, copy, and restore. Replication involves jobs like snapshot, log reader, and distribution agent.
Query optimization can be done using techniques like index optimization, query rewriting, and statistics update.
To check slowness, analyze query execution plans, monitor server resources, and use performance monitoring tools.
To add a login in standby database, use the CREATE LOGIN statement.
To add an article in replication, use the sp_addarticle stored procedure.
Different types of replication include snapshot, transactional, and merge replication.
To configure replication, use the Replication Configuration Wizard or T-SQL scripts.
Isolation level determines the level of locking and concurrency in database transactions.
File factor is a parameter used in SQL Server's cost-based query optimizer.
To find who deleted the database, check SQL Server's default trace or audit logs.
To troubleshoot a database in suspect mode, use the emergency mode repair or restore from backups.
To shrink files, use the DBCC SHRINKFILE command or the Shrink Database task in SSMS.
Types of backup include full, differential, and transaction log backups.
To restore a database, use the RESTORE DATABASE command or the Restore Database task in SSMS.

Popular interview questions of SQL Database Administrator