Aerial Combat- Hornets - ASMR

R3d Ta1l$

How to run sqlcmd as another network user

 Open a command prompt

C:\WINDOWS\system32>runas /user:ecp\admdvelasco cmd.exe

this will open another window

Microsoft Windows [Version 10.0.16299.1087]

(c) 2017 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>sqlcmd -S spectre

1> select name from sys.databases

2> go

name                                                                                                                    

--------------------------------------------------------------------------------------------------------------------------------

master                                                                                                                  

tempdb                                                                                                                  

model                                                                                                                   

msdb                                                                                                                    

eTraceOneToOne                           

There is insufficient system memory in resource pool 'internal' to run this query. SQL SERVER cannot start

 While playing with the DB, I was curious what will happen if I set the max memory of the sql server instance to 10mb.

So it crashed.

Now I cannot restart it.

This is how I fixed it.

Step 1 start the sqlserver instance with minimum settings.

1. open a command prompt as an administrator 

2. Go to the directory where SQL server is installed

    cd C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn

    then run 'sqlservr.exe -f -s <instance name>'

3. open a separate admin window like in step 1

4. type sqlcmd

EXEC sys.sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sys.sp_configure 'min server memory', 1024;

GO

EXEC sys.sp_configure 'max server memory', 4096;

GO

RECONFIGURE;

GO

5. Go back to the first window and do a ctrl c to shutdown the instance

6. Startup normal in the sql server configuration manager.

7. you are back in business

02-Baselining Hardware

 

02-Baselining Hardware

Monday, January 28, 2019

2:10 PM

-- Hardware and Storage Configuration Queries

-- Glenn Berry

-- Make sure you are using the correct version of these diagnostic queries for your version of SQL Server

-- Check the major product version to see if it is SQL Server 2017 CTP 1 or greater

IF NOT EXISTS (SELECT * WHERE CONVERT(varchar(128), SERVERPROPERTY('ProductVersion')) LIKE '14%')

BEGIN

DECLARE @ProductVersion varchar(128) = CONVERT(varchar(128), SERVERPROPERTY('ProductVersion'));

RAISERROR ('Script does not match the ProductVersion [%s] of this instance. Many of these queries may not work on this version.' , 18 , 16 , @ProductVersion);

END

ELSE

PRINT N'You have the correct major version of SQL Server for this diagnostic information script';

-- Hardware information from SQL Server 2017  (Query 1) (Hardware Info)

SELECT cpu_count AS [Logical CPU Count], scheduler_count, 

       (socket_count * cores_per_socket) AS [Physical Core Count], 

       socket_count AS [Socket Count], cores_per_socket, numa_node_count,

       physical_memory_kb/1024 AS [Physical Memory (MB)], 

       max_workers_count AS [Max Workers Count], 

   affinity_type_desc AS [Affinity Type], 

       sqlserver_start_time AS [SQL Server Start Time], 

   virtual_machine_type_desc AS [Virtual Machine Type], 

       softnuma_configuration_desc AS [Soft NUMA Configuration], 

   sql_memory_model_desc, process_physical_affinity -- New in SQL Server 2017

FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

------

-- Gives you some good basic hardware information about your database server

-- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean 

--       you are running SQL Server inside of a VM

-- It merely indicates that you have a hypervisor running on your host

-- sys.dm_os_sys_info (Transact-SQL)

-- https://bit.ly/2pczOYs

-- Soft NUMA configuration was a new column for SQL Server 2016

-- OFF = Soft-NUMA feature is OFF

-- ON = SQL Server automatically determines the NUMA node sizes for Soft-NUMA

-- MANUAL = Manually configured soft-NUMA

-- Configure SQL Server to Use Soft-NUMA (SQL Server)

-- https://bit.ly/2HTpKJt

-- sql_memory_model_desc values (Added in SQL Server 2016 SP1)

-- CONVENTIONAL

-- LOCK_PAGES

-- LARGE_PAGES

-- Get System Manufacturer and model number from SQL Server Error log (Query 2) (System Manufacturer)

EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';

------ 

-- This can help you determine the capabilities and capacities of your database server

-- Can also be used to confirm if you are running in a VM

-- This query might take a few seconds if you have not recycled your error log recently

-- This query will return no results if your error log has been recycled since the instance was started

-- Get BIOS date from Windows Registry (Query 3) (BIOS Date)

EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\BIOS', N'BiosReleaseDate';

------

-- Helps you understand whether the main system BIOS is up to date, and the possible age of the hardware

-- Not useful for virtualization

-- Get processor description from Windows Registry  (Query 4) (Processor Description)

EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';

------

-- Gives you the model number and rated clock speed of your processor(s)

-- Your processors may be running at less than the rated clock speed due

-- to the Windows Power Plan or hardware power management

-- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information

-- https://www.cpuid.com/softwares/cpu-z.html

-- You can learn more about processor selection for SQL Server by following this link

-- https://bit.ly/2F3aVlP

-- SQL Server NUMA Node information  (Query 5) (SQL Server NUMA Info)

SELECT node_id, node_state_desc, memory_node_id, processor_group, cpu_count, online_scheduler_count, 

       idle_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state

FROM sys.dm_os_nodes WITH (NOLOCK) 

WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

------

-- Gives you some useful information about the composition and relative load on your NUMA nodes

-- You want to see an equal number of schedulers on each NUMA node

-- Watch out if SQL Server 2017 Standard Edition has been installed 

-- on a physical or virtual machine with more than four sockets or more than 24 physical cores

-- sys.dm_os_nodes (Transact-SQL)

-- https://bit.ly/2pn5Mw8

-- Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes

-- https://bit.ly/2vfC4Rq

-- Get memory-optimized checkpoint mode from SQL Server Error log (Query 6) (Memory-Optimized Checkpoint Mode)

EXEC sys.xp_readerrorlog 0, 1, N'In-Memory OLTP initialized on';

------ 

-- Possible values:

-- In-Memory OLTP initialized on standard machine

-- In-Memory OLTP initialized on highend machine      (Large Checkpoint mode)

-- Checkpoint process for memory-optimized tables in SQL 2016 and implications on the log

-- https://bit.ly/2c8jKj1

-- A "highend machine" must meet all of these criteria:

-- The server has 16 or more logical processors

-- The server has 128GB or greater memory

-- The server is capable of greater than 200MB/sec I/O measured for the IO subsystem of that database

-- FIX: Slow database recovery in SQL Server 2016 due to large log 

--      when you use In-Memory OLTP on a high-end computer

-- https://bit.ly/2FlOEUL

-- In SQL Server 2016 CU1 and later, global TF 9912 must be enabled to get large checkpoint mode

-- Good basic information about OS memory amounts and state  (Query 7) (System Memory)

SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 

       available_physical_memory_kb/1024 AS [Available Memory (MB)], 

       total_page_file_kb/1024 AS [Total Page File (MB)], 

   available_page_file_kb/1024 AS [Available Page File (MB)], 

   system_cache_kb/1024 AS [System Cache (MB)],

       system_memory_state_desc AS [System Memory State]

FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

------

-- You want to see "Available physical memory is high" for System Memory State

-- This indicates that you are not under external memory pressure

-- Possible System Memory State values:

-- Available physical memory is high

-- Physical memory usage is steady

-- Available physical memory is low

-- Available physical memory is running low

-- Physical memory state is transitioning

-- sys.dm_os_sys_memory (Transact-SQL)

-- https://bit.ly/2pcV0xq

-- See if buffer pool extension (BPE) is enabled (Query 8) (BPE Configuration)

SELECT [path], state_description, current_size_in_kb, 

CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]

FROM sys.dm_os_buffer_pool_extension_configuration WITH (NOLOCK) OPTION (RECOMPILE);

------

-- BPE is available in both Standard Edition and Enterprise Edition

-- It is a more interesting feature for Standard Edition

-- Buffer Pool Extension to SSDs in SQL Server 2014

-- https://bit.ly/1bm08m8

-- Buffer Pool Extension

-- https://bit.ly/2oBuieO

-- sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)

-- https://bit.ly/2qSF4xr

-- Drive information for all fixed drives visible to the operating system (Query 9) (Fixed Drives)

SELECT fixed_drive_path, drive_type_desc, 

CONVERT(DECIMAL(18,2), free_space_in_bytes/1073741824.0) AS [Available Space (GB)]

FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK) OPTION (RECOMPILE);

------

-- This shows all of your fixed drives, not just LUNs with SQL Server database files

-- Volume info for all LUNS that have database files on the current instance (Query 10) (Volume Info)

SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name, 

CONVERT(DECIMAL(18,2), vs.total_bytes/1073741824.0) AS [Total Size (GB)],

CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],  

CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %],

vs.supports_compression, vs.is_compressed, 

vs.supports_sparse_files, vs.supports_alternate_streams

FROM sys.master_files AS f WITH (NOLOCK)

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs 

ORDER BY vs.volume_mount_point OPTION (RECOMPILE);

------

-- Shows you the total and free space on the LUNs where you have database files

-- Being low on free space can negatively affect performance with some types of storage

-- sys.dm_os_volume_stats (Transact-SQL)

-- https://bit.ly/2oBPNNr

SQL Server Basic Monitoring

In monitoring servers you should first know what to look for. Based on my experience database problems can be categorized into five categories.

1. CPU
2. Memory
3. Network
4. I/O Bottlenecks
5. Slow Queries

These are the queries we can  use to troubleshoot where the problem is.

CPU

DECLARE @BatchRequests BIGINT;

 

SELECT @BatchRequests = cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec';

 

WAITFOR DELAY '00:00:10';

 

SELECT (cntr_value - @BatchRequests) / 10 AS 'Batch Requests/sec'

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec';

The Batch Requests/sec value depends on hardware used, but it should be under 1000. The recommended value for SQL Compilations/sec is less than 10% of Batch Requests/sec and for SQL Re-Compilations/sec is less than 10% of SQL Compilations/sec

Oracle How to drop all schema objects

 select 'drop '||object_type||' &owner..'|| object_name || ';' from dba_objects WHERE OWNER=UPPER('&owner') and object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 150G SCOPE=BOTH

CREATE READ_ONLY USER IN ORACLE DATABASE

CREATE USER READ_ONLY IDENTIFIED BY "password";

GRANT CREATE SESSION TO READ_ONLY;

SET HEADING OFF

SET PAGES 9999 LINES 400

SPOOL TABLE_GRANTS.sql

SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'

FROM DBA_TABLES

WHERE OWNER IN ('ZEDDBA')

ORDER BY 1;

SPOOL OFF

@TABLE_GRANTS.sql

!rm TABLE_GRANTS.sql 

9 common mistakes you are doing right now

  10 Mistakes We Are Doing

1. Poor Handshake
2. No Eye Contact
3. Being Dismissive
4. Failing to Ask Good Questions
5. Neglecting your Reputation
6. Showing signs of disinterest
7. Talking first, giving Later
8. Failing to showcase your strongest assets
9. Speaking before you listen.
10. Trying too hard

Who is doing the backup on your sql server

; WITH bus AS (

 SELECT database_name, machine_name, backup_finish_date, media_set_id, type,msdb.dbo.backupset.software_vendor_id,

 msdb.dbo.backupset.user_name,

        row_number () OVER(PARTITION BY database_name ORDER BY backup_finish_date DESC) AS rowno

 FROM   msdb.dbo.backupset

)

SELECT bus.machine_name AS ServerName, sdb.Name AS DatabaseName,

       COALESCE(convert(varchar(23), bus.backup_finish_date), 'No backup') AS BackupTime ,

      CASE bus.type 

      WHEN 'D' THEN 'Database' 

      WHEN 'L' THEN 'Log File'

      WHEN 'I' THEN 'Differential'

      END AS BackupType,software_vendor_id,user_name,

busf.physical_device_name AS Location

FROM sys.databases sdb

LEFT OUTER JOIN bus ON bus.database_name = sdb.name AND rowno <= 2

LEFT OUTER JOIN msdb.dbo.backupmediafamily busf ON busf.media_set_id = bus.media_set_id

WHERE database_id > 4

ORDER BY bus.machine_name