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

Install SQL Server 2008R2 With ConfigurationFile.(Configuration.ini)

Install SQL Server 2008R2 With ConfigurationFile.(Configuration.ini)

 Step 1:-

Run Sql Server 2008 R2 Set Up .

Step 2:-

Run the Sql Server Setup untill Ready To Install option not shown and copy the path of Configuration File(Configuration.INI) and click on the cancel installation.

Step :3

 Edit the configuration file as follows:

1.       Set QUIET to “True”. This specifies that Setup will run in a quiet mode without any user interface

i. QUIET="True"

2.       Set SQLSYSADMINACCOUNTS to “BUILTINADMINISTRATORS”. This will ensure that administrators on the machine are added as members of the sysadmin role. You can set its value based on your needs (Ex: SQLSYSADMINACCOUNTS=”domainYourUser”), but this is the more generic approach. I have added My 

user instead of BUILTINADMINISTRATORS, to secure SQL server from unwanted logins.

i. SQLSYSADMINACCOUNTS="BUILTINADMINISTRATORS" or local system name

3.       Add PID and set its value to your product license key. If your setup.exe already comes preloaded with the key, there is no need to add this option to the configuration file.

4.       Add IACCEPTSQLSERVERLICENSETERMS and set its value to “True”. This is to require to acknowledge acceptance of the license terms at time of unattended installations.

i. IACCEPTSQLSERVERLICENSETERMS="True

5.       Remove the ADDCURRENTUSERASSQLADMIN parameter. The reason is that this parameter can’t be used when SQLSYSADMINACCOUNTS is specified, and it only applies to Express installations.

6.       Remove the UIMODE parameter as it can’t be used with the QUITE parameter.

7.       Remove INSTALLSHAREDDIR, INSTALLSHAREDWOWDIR, INSTANCEDIR parameters if you want to install on the default installation directories or mention appropriate directories for installation.

8.       You can add or remove the feature you want to install, Select FEATURES=SQLENGINE,SSMS,ADV_SSMS in the configuration file. You can change that based on your needs.

9.       The full list of available feature parameters and their descriptions : http://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature

10.   Now, Your configuration file is ready, you need to create a batch file that will run the silent unattended setup. Create a new file ”SQLServer2012_SilentInstall” with extension = “.bat”.

We have added, Date time to get the time taken by complete installation. Edit below script with your setup & configuration file location.

@ECHO offecho Installing SQL Server 2008 R2date/ttime /t “D:SQLFULL_x86_ENUsetup.exe” /ConfigurationFile=”D:ConfigurationFile.ini” date/t time /t save this into .BAT file and run

MY EDIT Configration.ini file For Sql Server 2008R2

==================================================================

;SQLSERVER2008 Configuration File

[SQLSERVER2008]

; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will reflect the instance ID of the SQL Server instance. 

INSTANCEID="SQL2008Rcmd"

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 

ACTION="Install"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, and Tools. The SQL feature will install the database engine, replication, and full-text. The Tools feature will install Management Tools, Books online, Business Intelligence Development Studio, and other shared components. 

FEATURES=SQLENGINE,REPLICATION,FULLTEXT

; Displays the command line parameters usage 

HELP="False"

; Specifies that the detailed Setup log should be piped to the console. 

INDICATEPROGRESS="False"

; Setup will not display any user interface. 

QUIET="True"

SAPWD=”access”

; Setup will display progress only without any user interaction. 

QUIETSIMPLE="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. 

X86="False"

; Detailed help for command line argument ENU has not been defined yet. 

ENU="True"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature. 

ERRORREPORTING="False"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature. 

SQMREPORTING="False"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS). 

INSTANCENAME="SQL2008Rcmd"

; Agent account name 

AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"

; Auto-start service after installation.  

AGTSVCSTARTUPTYPE="Automatic"

; Startup type for Integration Services. 

ISSVCSTARTUPTYPE="Automatic"

; Account for Integration Services: Domain\User or system account. 

ISSVCACCOUNT="NT AUTHORITY\NetworkService"

; Controls the service startup type setting after the service has been created. 

ASSVCSTARTUPTYPE="Automatic"

; The collation to be used by Analysis Services. 

ASCOLLATION="Latin1_General_CI_AS"

; The location for the Analysis Services data files. 

ASDATADIR="Data"

; The location for the Analysis Services log files. 

ASLOGDIR="Log"

; The location for the Analysis Services backup files. 

ASBACKUPDIR="Backup"

; The location for the Analysis Services temporary files. 

ASTEMPDIR="Temp"

; The location for the Analysis Services configuration files. 

ASCONFIGDIR="Config"

; Specifies whether or not the MSOLAP provider is allowed to run in process. 

ASPROVIDERMSOLAP="1"

; A port number used to connect to the SharePoint Central Administration web application. 

FARMADMINPORT="0"

; Startup type for the SQL Server service. 

SQLSVCSTARTUPTYPE="Automatic"

; Level to enable FILESTREAM feature at (0, 1, 2 or 3). 

FILESTREAMLEVEL="0"

; Set to "1" to enable RANU for SQL Server Express. 

ENABLERANU="False"

; Specifies a Windows collation or an SQL collation to use for the Database Engine. 

SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: Domain\User or system account. 

SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"

IACCEPTSQLSERVERLICENSETERMS="True"

; Windows account(s) to provision as SQL Server system administrators. 

SQLSYSADMINACCOUNTS="GHOST-PC\GHOST"

; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication. 

SECURITYMODE="SQL"

; Specify 0 to disable or 1 to enable the TCP/IP protocol. 

TCPENABLED="1"

; Specify 0 to disable or 1 to enable the Named Pipes protocol. 

NPENABLED="0"

; Startup type for Browser Service. 

BROWSERSVCSTARTUPTYPE="Automatic"

; Specifies how the startup mode of the report server NT service.  When 

; Manual - Service startup is manual mode (default).

; Automatic - Service startup is automatic mode.

; Disabled - Service is disabled 

RSSVCSTARTUPTYPE="Automatic"

; Specifies which mode report server is installed in.  

; Default value: “FilesOnly”  

RSINSTALLMODE="FilesOnlyMode"

; Add description of input argument FTSVCACCOUNT 

FTSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE"

========================================================================

When was my database last used ??

Use <DatabaseName>
GO
SELECT DB_NAME() as DatabaseName,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset

When was my table last scanned or updated?

When was my table last scanned or updated?
Hi i am posting a blog related to how to check when your table last updated or scanned. This issue occurred when more than one person work on same project and some some mistakenly changed by anyone.

USE DatabaseName  ----your database name--
GO
SELECT
t.name,
i.last_user_lookup,
i.last_user_scan,
i.last_user_seek,
i.last_user_update
FROM sys.dm_db_index_usage_stats i
INNER JOIN sys.tables t
ON i.object_id = t.object_id
WHERE
database_id = db_id( 'DatabaseName' )  ----your database name--

How could i insert the data into my secondary files(.ndf)?

Step 1:- Create a file group with secondary file (.ndf file)

( NAME = N'FileBackupsTest',
FILENAME = N'E:\FileBackupsTest.mdf' ,
SIZE = 5120KB , FILEGROWTH = 5124KB ),
FILEGROUP [SecondaryFile]
( NAME = N'FileBackupsTestUserData1',
FILENAME = N'e:\FileBackupsTestUserData1.ndf' ,
SIZE = 512KB , FILEGROWTH = 512KB )
LOG ON
( NAME = N'FileBackupsTest_log',
FILENAME = N'e:\SQLData\FileBackupsTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 512KB )
GO
USE [FileBackupsTest]
GO

Step 2: create a table with secondary file group name.By default a table created on Primary file (.mdf) file but if you want to enter data into secondory file(.ndf) than mention seondory File group name.
CREATE TABLE Demo
(
name char(12),
city char(12),
roll int
)
ON [secondaryFile]



Step : 3 insert some data into this table and check the size of your secondary file will grow in size. check the size with sp_helpdb 'FileBackupsTest1'
GO and To Find which object resides in which FileGroup in a database, run the below queries.


T-SQL - Find which Object resides in which FileGroup
To Find which object resides in which FileGroup in a database, run the below queries.

/**************** For SQL 2000 ****************/

Select Distinct OBJECT_NAME(SI.id) AS ObjectName
        ,SO.type AS ObjectType
        ,FG.GroupName AS FileGroupName
from sysindexes SI , sysfilegroups FG ,sysobjects SO
where SI.groupid = FG.groupid and
      SI.id = SO.id
Order by ObjectName

/**************** For SQL 2005 and SQL 2008 ****************/

Select Distinct OBJECT_NAME(SI.object_id) AS ObjectName
        ,OBJECTPROPERTYEX(SI.object_id,'BaseType') AS ObjectType
        ,FG.Name AS FileGroupName
from sys.indexes SI , sys.filegroups FG
where SI.data_space_id = FG.data_space_id
Order by ObjectName