DBA Role's and Responsibilities..

Before we begin let's first understand what are the roles and responsibility of a DBA are :

1 . Installation, Administration and Maintenance of SQL Server 
Instances.

2. Creating logins, users, roles and assign permissions as needed.

3. Create Linked Servers to SQL Servers and other databases.

4. Design database Backup and Restoration Strategy.

5. Check the SQL Error logs for any errors.

6. Check available physical Disk space and capacity planning.

7. Setup High-Availability as part Disaster Recovery Strategy
for the databases.

8. Troubleshoot various problems that arise in a day-to-day
work and fix the issues.

9. Performance Tuning and query tuning.

10. Applying Service packs regularly.

11. Check the Event Viewer regularly.

12. Check job history daily.

13. Perform integrity and optimization check weekly.

14. Perform database health check and security audit every month

15. Set up alerts for your BCA applications.

Some SQL Server Interview Question .

SQL Server Interview Qustions [1-5]:

Interview question 1:

List down 3 SQL Server DMVs related to Troubleshooting Memory Issues

1. sys.dm_os_process_memory
2. sys.dm_os_memory_clerks
3. sys.dm_os_ring_buffers

Interview question 2:
Important DMVs for troubleshooting CPU pressure
1. sys.dm_os_schedulers
2. sys.dm_os_performance_counters
3. sys.dm_os_wait_stats
4. sys.dm_os_ring_buffers

Interview question 3:
How to improve efficiency of plan cache?

Enable "optimize for ad hoc workloads", a server level setting.
This will prevent huge number of one time ad-hoc query plans occupy plan cache.
Only plan stub will be created for first time ad-hoc queries. When the ad-hoc query is executed for the second time, actual execution plan will be created and stored in plan cache.

Interview question 4:
What is the use of Tempdb?

1. Temp Tables and Table Variables
2. Online index operations
3. Triggers
4. Version Store (ex: snapshot isolation)
5. SORT/HASH JOIN/UNION/DISTINCT/ORDER BY
6. Work tables for some DBCC commands
etc.

Interview question 5:
What is the difference between RTO and RPO?

Recovery Time Objective (RTO) is the duration of time and a service level within which a business process must be restored after a disaster/failure

Recovery Point Objective (RPO) is the maximum acceptable amount of data loss (measured in time) after disaster/failure

SQL SERVER Job with Email notification Failure and success status

 Create a job step which you declare T-SQL

Create Step 2

Create Step 3

Note:- Change owner name  in code like sa is mentioned


Configured SQL Server Job with multi action with wizard
in this script if your user not sa than give the user name instead of sa.


Run this command in sql server and you need to create a demo database on your system or you can change the database name .


USE [msdb]
GO

/****** Object:  Job [bckup]    Script Date: 06/13/2013 13:46:32 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 06/13/2013 13:46:32 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Multiaction',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', --------------------------change owner name -----
@notify_email_operator_name=N'Admin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [bk]    Script Date: 06/13/2013 13:46:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'bk',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=3,
@on_fail_action=4,
@on_fail_step_id=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec  dbback
go',
@database_name=N'demo',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [failure]    Script Date: 06/13/2013 13:46:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'failure',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=2,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'----email failoure  script',
@database_name=N'demo',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [sucess]    Script Date: 06/13/2013 13:46:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'sucess',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'---sucess mail',
@database_name=N'demo',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'jobnew',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130613,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'130464a9-87ae-4717-91ba-876b7d222b19'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO