Sql Server Interview Questions and Answers (1758) - Page 21

BEGIN TRY 

    RAISERROR ('A problem is raised', 16,1)

END TRY 

BEGIN CATCH 

SELECT ERROR_NUMBER() as ERROR_NUMBER,   

ERROR_SEVERITY() as ERROR_SEVERITY,   

ERROR_STATE() as ERROR_STATE,   

ERROR_MESSAGE() as ERROR_MESSAGE

END CATCH

SELECT CASE SysAdmin WHEN 1 THEN 'You are a member of ''Sysadmin'' role' ELSE 'You are not member of ''Sysadmin'' role' END 'Sysadmin Role Permission' FROM 

(

SELECT IS_SRVROLEMEMBER(N'sysadmin') [SysAdmin]

)AS S

DECLARE @PlanID  VARCHAR(36)



SELECT @PlanID = id FROM msdb.dbo.sysmaintplan_plans 

WHERE [NAME] ='Maintenance Plan Name'



EXECUTE msdb..sp_maintplan_start @PlanID,NULL

GO

DECLARE @JobID	VARCHAR(36), 

@retval INT



SELECT @JobID = Job_ID FROM msdb.dbo.sysjobs_view

WHERE [Name] = 'SQL Job Name'



EXEC @retval = msdb.dbo.sp_start_job @job_id = @JobID

GO

SELECT sqlserver_start_time 'SQL Server Started at' FROM sys.dm_os_sys_info

GO

SELECT create_date 'SQL Server Started at' FROM sys.databases WHERE [name] ='tempdb'

GO

SELECT (total_physical_memory_kb/1024.)/1024. 'Total Physical Memory(GB)', 

	   (available_physical_memory_kb/1024.)/1024. 'Available Physical Memory(GB)',

	    (100 / ((total_physical_memory_kb/1024.)/1024.)) * (((total_physical_memory_kb/1024.)/1024.) - ((available_physical_memory_kb/1024.)/1024.)) 'Used Physical Memory(%)' ,

	   (100 / ((total_physical_memory_kb/1024.)/1024.)) * ((available_physical_memory_kb/1024.)/1024.)  'Available Physical Memory(%)',

	   system_memory_state_desc 'Memory Status'

FROM sys.dm_os_sys_memory

EXEC sys.sp_configure N'backup compression default', N'1'

GO

RECONFIGURE WITH OVERRIDE

GO