Here is a script to check if an Oracle instance is running.
SELECT instance_name, status FROM v$instance;
No comments
Author: Enrique
ORACLE – Investigate the Memory Structures of the Instance
Below is a script to show the current, maximum and minimum sizes of the SGA components that can be dynamically resized.
SELECT component, current_size, min_size, max_size FROM v$sga_dynamic_components;
Execute the script below to Determine how much memory has been, and is currently, allocated to program global areas
SELECT
name,
value
FROM
v$pgastat
WHERE
name IN ('maximum PGA allocated','total PGA allocated');
SQL Server SSRS – Query the Report Server Execution Log
Below is the T-SQL script to query the Report Server Execution Log. You may query either the dbo.ExecutionLog2 or dbo.ExecutionLog3 table (same as dbo.ExecutionLog2 with 2 fields renamed: ReportPath has been renamed to ItemPath and ReportAction has been renamed to ItemAction)
Below is the output of the query.
USE ReportServer GO SELECT * FROM dbo.ExecutionLog3 ORDER BY TimeStart DESC
SQL Server – Get Index Creation Date
There are many T-SQL scripts to get the index creation date but I always use this one.
DECLARE @filename VARCHAR(500) SELECT @filename = CAST(value AS VARCHAR(500)) FROM fn_trace_getinfo(DEFAULT) WHERE property = 2 AND value IS NOT NULL -- Go back 4 files since default trace only keeps the last 5 and start from there. SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc' SELECT gt.EventClass, gt.EventSubClass, te.Name AS EventName, gt.HostName, gt.StartTime, gt.DatabaseName, gt.ObjectName FROM fn_trace_gettable(@fileName, DEFAULT) gt JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id WHERE EventClass = 46 and ObjectType = 22601 and gt.DatabaseName <> 'tempdb' ORDER BY StartTime desc;
SQL Server – Export SSRS Report to PDF in Landscape Format
By default, your SSRS reports are generated in portrait format. The body of your report will be automatically resized when you add report items. When you deploy your reports, it will portray correctly in your browser. But when you export your report to PDF, you will notice that it is in portrait format. It is not presentable and it will have extra pages. You can prevent this from happening by exporting the PDF in landscape format. To do that, just follow the steps below.
- Go to the properties of your report. (Right-click mouse just outside the body of your report). Set the width of the report to the landscape size of your A4 paper: 29.7 cm. Set the height of the report to 21 cm.
2. When you export the report, you will see extra blank pages. To avoid that, the size of the body should be less or equal to the size of the report margins
Set the width of the body to 26.7 cm Set the height of the body to 18 cm
So now, when you export your report to PDF, it will be in landscape format.
SQL Server – Check Database Properties
The T-SQL query below can be used to check the properties of your databases.
USE master GO SELECT name AS 'Database_Name' , snapshot_isolation_state AS 'Allow Snapshot Isolation' , is_ansi_null_default_on AS 'ANSI NULL Default' , is_ansi_nulls_on AS 'ANSI NULLS Enabled' , is_ansi_padding_on AS 'ANSI Paddings Enabled' , is_ansi_warnings_on AS 'ANSI Warnings Enabled' , is_arithabort_on AS 'Arithmetic Abort Enabled' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , is_cursor_close_on_commit_on AS 'Close Cursor on Commit Enabled' , is_concat_null_yields_null_on AS 'Concatenate Null Yields Null' , is_db_chaining_on AS 'Cross-Database Ownership Chaining Enabled' , is_date_correlation_on AS 'Data Correlation Optimization Enabled' , is_read_only AS 'Database Read-Only' , is_local_cursor_default AS 'Default Cursor' , is_encrypted AS 'Encryption Enabled' , is_arithabort_on AS 'Numeric Round-Abort' , page_verify_option_desc AS 'Page Verify' , is_parameterization_forced AS 'Parameterization' , is_quoted_identifier_on AS 'Quoted Identifiers Enabled' , is_read_committed_snapshot_on AS 'Read Committed Snapshot' , is_recursive_triggers_on AS 'Recursive Triggers Enabled' , user_access_desc AS 'Restrict Access' , is_broker_enabled AS 'Service Broker Enabled' , is_trustworthy_on AS 'Trustworthy' FROM sys.databases ; GO
SQL Server – Top Queries Consuming CPU
Below is a script that captures that top SQL queries consuming CPU.
SELECT TOP 50 [Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count, [Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000, [Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count, [Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000, qs.execution_count, [Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count, [Total I/O] = total_logical_reads + total_logical_writes, Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.[text]) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1 ), Batch = qt.[text], [DB] = DB_NAME(qt.[dbid]), qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp where qs.execution_count > 5 --more than 5 occurences ORDER BY [Total MultiCore/CPU time(sec)] DESC
SQL Server – Check Database Mirroring Status
Below is a script to check the database mirroring synchronization state.
SELECT @@SERVERNAME as Server_Name, DB_NAME(database_id) as Database_Name, mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc FROM sys.database_mirroring WHERE mirroring_role IS NOT NULL
SQL Server – Get Size of All Databases in MB and GB
The script below will retrieve the size of all your databases in MB and GB.
SELECT d.NAME ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4 -- Skip system databases GROUP BY d.NAME ORDER BY d.NAME