ASUS N56VM Enter the BIOS

 While the computer isn’t powered on yet, press and hold the [F2] button of the keyboard, and then press the [Power button] (Do not release F2 button until the BIOS configuration display.).

Linux: Change swap file

 swapoff /dev/vg1/swap

lvextend -L +30GB /dev/vg1/swap

mkswap /dev/vg1/swap

swapon /dev/vg1/swap

MySQL: Increase InnoDB buffer cache memory allocation

 On each host (as root):

1. Backup the existing MySQL configuration:

cp /etc/my.cnf /etc/my.cnf.bak

2. Using vi, add the following parameters to /etc/my.cnf

[mysqld]

innodb_buffer_pool_size=100G

innodb_flush_method=O_DIRECT

3. Restart the MySQL database

service mysql55-mysqld restart

mysqladmin flush-hosts -p

Sophos UTM Web Proxy!!!

 Sophos UTM Web Proxy is a pain in the backside.

It allows access to web-based admin pages  EVEN THOUGH the FIREWALL doesn't allow such access! The firewall doesn't allow access, but the web proxy does!! FFS!!!

So, for example, your ISP router configuration interface could be accessible by a guest user, albeit protected by a password. Then your ISP router is susceptible to password guessing or script-based attacks.

And it's not just your ISP router is at risk, it's everything with an admin page.

On top of that, it re-badges your (valid) connection as though it's coming from the DESTINATION network. FFS! I guess it's a proxy, so that's sort of expected.

The web proxy is sort of necessary, since it has the dual-AV scanning engine. Therefore, disabling the web proxy and using only the firewall will result in web traffic not being scanned for malware.

SQL Server: How to DBCC REPAIR_ALLOW_DATA_LOSS

 USE master;

ALTER DATABASE Prod_Clone SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

USE Prod_Clone

go

--DBCC CHECKTABLE ('AIFDOCUMENTLOG', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

--DBCC CHECKTABLE ('AIFDOCUMENTLOG') WITH NO_INFOMSGS,ESTIMATEONLY ;

DBCC CHECKDB ('Prod_Clone') WITH NO_INFOMSGS;

GO

--ALTER DATABASE Prod_Clone SET MULTI_USER;

--GO

SQL Server: small TEMPDB causes DBCC Errors

If you get the following DBCC CHECKDB errors, then the problem could simply be a small TEMPDB. I've BOLDEN the really important clue at the end of the error stack.

This was really annoying!!!!!

 Msg 8964, Level 16, State 1, Line 1

Table error: Object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data). The off-row data node at page (1:35728379), slot 0, text ID 1055064064 is not referenced.

Msg 2534, Level 16, State 2, Line 1

Table error: page (1:35797497), whose header indicates that it is allocated to object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data), is allocated by another object.

Msg 2533, Level 16, State 1, Line 1

Table error: page (1:34608553) allocated to object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

Msg 8965, Level 16, State 1, Line 1

Table error: Object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data). The off-row data node at page (1:33939922), slot 0, text ID 944701440 is referenced by page (1:36219352), slot 4, but was not seen in the scan.

Msg 2533, Level 16, State 1, Line 1

Table error: page (1:34616630) allocated to object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

CHECKDB found 0 allocation errors and 3366 consistency errors in table 'XX' (object ID 630097831).

CHECKDB found 0 allocation errors and 3366 consistency errors in database 'DB'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DB).

Msg 9002, Level 17, State 4, Line 1

The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

SQL Server: ETA of a DBCC Command

 SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)

AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))

FROM sys.dm_exec_requests r WHERE command = ('DBCC TABLE CHECK')

SQL Server TEMPDB: /f mode to change TEMPDB

To remove or re-size any files belonging to TEMPDB:

net stop MSSQLSERVER

NET START MSSQLSERVER /f /mSQLCMD

PS C:\tmp> sqlcmd -S SERVER -E

use TEMPDB

go

DBCC SHRINKFILE (temp5, EMPTYFILE);

USE master

GO

ALTER DATABASE tempdb REMOVE FILE temp5;

go

DBCC REPAIR_REBUILD

USE Prod_Clone

GO

ALTER DATABASE Prod_Clone SET SINGLE_USER

GO

DBCC CHECKDB('Prod_Clone', REPAIR_REBUILD)

GO

ALTER DATABASE Prod_Clone SET MULTI_USER

GO

ORA-01652: Unable to Extend Temp Segment in RAC (Doc ID 1534590.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Information in this document applies to any platform.

SYMPTOMS

Temporary tablespace space allocation fails in RAC even when there is still free temp space.

ORA-12801: error signaled in parallel query server P017

ORA-01652: unable to extend temp segment by 640 in tablespace XY_TEMP

 

CAUSE

Unbalanced temp space distribution in RAC.  One instance seems to consume and cache most of the temp space, causing another instance to hit the ora-1652.

SQL> select inst_id, tablespace_name, round((total_blocks*8192)/(1024*1024*1024),2) "Space(GB)"

2 from gv$sort_segment

3 where tablespace_name='XY_TEMP'

4 order by 1;

INST_ID  TABLESPACE_NAME        Space(GB)

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

1           XY_TEMP                           33.39

2           XY_TEMP                           33.77

3           XY_TEMP                           34.16

4           XY_TEMP                           33.39

5           XY_TEMP                           33.46

6           XY_TEMP                       1118.79   <<<<very unbalanced

7           XY_TEMP                           33.28

8           XY_TEMP                           34.26

This is reported in Bug 14383007 - sort runs out of temp space on 2 nodes even when temp space is available

This bug will be fixed in 11.2.0.4 (future release). Refer < Document 14383007.8> for more details.

Useful queries for debugging:

Collect the information every few seconds:

1. select  * from gv$sort_segment

2. select sum(bytes), owner from gv$temp_extent_map group by owner;

3. select inst_id, blocks_cached, blocks_used, extents_cached, extents_used from GV$TEMP_EXTENT_POOL;

SOLUTION

Workaround is:

Retry the operation.

One-off patch 14383007 has been provided for certain platform, please check My Oracle Support for patch detail.

REFERENCES

NOTE:14383007.8 - Bug 14383007 - Sort runs out of temp space in RAC even when temp space is available

"Windows cannot access the specified device..." when installing from a CD/DVD

 In Group Policy:

Computer Configuration -->Administrative Templates --> System --> Removable Storage Access:

CD and DVD: Deny execute access

Find a string by searching all tables in SQL Server Management Studio

 USE DATABASE_NAME

DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'

DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET  @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

    SET @ColumnName = ''

    SET @TableName = 

    (

        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

        FROM     INFORMATION_SCHEMA.TABLES

        WHERE         TABLE_TYPE = 'BASE TABLE'

            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

            AND    OBJECTPROPERTY(

                    OBJECT_ID(

                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

                         ), 'IsMSShipped'

                           ) = 0

    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN

        SET @ColumnName =

        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))

            FROM     INFORMATION_SCHEMA.COLUMNS

            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)

                AND    TABLE_NAME    = PARSENAME(@TableName, 1)

                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')

                AND    QUOTENAME(COLUMN_NAME) > @ColumnName

        )

        IF @ColumnName IS NOT NULL

        BEGIN

            INSERT INTO @Results

            EXEC

            (

                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 

                FROM ' + @TableName + ' (NOLOCK) ' +

                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

            )

        END

    END    

END

SELECT ColumnName, ColumnValue FROM @Results

SQL Server: Determine the transaction log usage

 DBCC SQLPERF (logspace)

Returns the current size of the transaction log and the percentage of log space used for each database. Use this information to monitor the amount of space used in a transaction log.

Oracle hint: ignore_row_on_dupkey_index

 The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in "Hints" does not apply for these three hints.

The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.

The semantic effect of this hint results in error messages if specific rules are violated:

If you specify index, then the index must exist and be unique. Otherwise, the statement causes ORA-38913.

You must specify exactly one index. If you specify no index, then the statement causes ORA-38912. If you specify more than one index, then the statement causes ORA-38915.

You can specify either a CHANGE_DUPKEY_ERROR_INDEX or IGNORE_ROW_ON_DUPKEY_INDEX hint in an INSERT statement, but not both. If you specify both, then the statement causes ORA-38915.

As with all hints, a syntax error in the hint causes it to be silently ignored. The result will be that ORA-00001 will be caused, just as if no hint were used.

SQL Server Missing Index Script

 -- Missing Index Script

-- Original Author: Pinal Dave 

SELECT TOP 25

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 

+ CASE

WHEN dm_mid.equality_columns IS NOT NULL

AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 

IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

mysql 5.5 on centos 6

 # For x86_64

yum install http://repo.mysql.com/yum/mysql-5.5-community/el/6/x86_64/mysql-community-release-el6-5.noarch.rpm

yum install -y --enablerepo=mysql55-community --disablerepo=mysql56-community mysql-community-server

SQL Server: Determine Max DoP

https://dba.stackexchange.com/questions/36522/maxdop-setting-algorithm-for-sql-server?noredirect=1&lq=1

Older versions of SQL Server:

DECLARE @CoreCount int;

DECLARE @NumaNodes int;

SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i);

SET @NumaNodes = (

    SELECT MAX(c.memory_node_id) + 1 

    FROM sys.dm_os_memory_clerks c 

    WHERE memory_node_id < 64

    );

IF @CoreCount > 4 /* If less than 5 cores, don't bother. */

BEGIN

    DECLARE @MaxDOP int;

    /* 3/4 of Total Cores in Machine */

    SET @MaxDOP = @CoreCount * 0.75; 

    /* if @MaxDOP is greater than the per NUMA node

       Core Count, set @MaxDOP = per NUMA node core count

    */

    IF @MaxDOP > (@CoreCount / @NumaNodes) 

        SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75;

    /*

        Reduce @MaxDOP to an even number 

    */

    SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);

    /* Cap MAXDOP at 8, according to Microsoft */

    IF @MaxDOP > 8 SET @MaxDOP = 8;

    PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max));

END

ELSE

BEGIN

    PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.';

    PRINT 'This is the default setting, you likely do not need to do';

    PRINT 'anything.';

END

Newer versions of SQL Server: 

DECLARE @socket_count int;

DECLARE @cores_per_socket int;

DECLARE @numa_node_count int;

DECLARE @memory_model nvarchar(120);

DECLARE @hyperthread_ratio int;

SELECT @socket_count = dosi.socket_count

       , @cores_per_socket = dosi.cores_per_socket

       , @numa_node_count = dosi.numa_node_count

       , @memory_model = dosi.sql_memory_model_desc

       , @hyperthread_ratio = dosi.hyperthread_ratio

FROM sys.dm_os_sys_info dosi;

SELECT [Socket Count] = @socket_count

       , [Cores Per Socket] = @cores_per_socket

       , [Number of NUMA nodes] = @numa_node_count

       , [Hyperthreading Enabled] = CASE WHEN @hyperthread_ratio > @cores_per_socket THEN 1 ELSE 0 END

       , [Lock Pages in Memory granted?] = CASE WHEN @memory_model = N'CONVENTIONAL' THEN 0 ELSE 1 END;

DECLARE @MAXDOP int = @cores_per_socket;

SET @MAXDOP = @MAXDOP * 0.75;

IF @MAXDOP >= 8 SET @MAXDOP = 8;

SELECT [Recommended MAXDOP setting] = @MAXDOP

       , [Command] = 'EXEC sys.sp_configure N''max degree of parallelism'', ' + CONVERT(nvarchar(10), @MAXDOP) + ';RECONFIGURE;';

Useful query:

select * FROM sys.dm_os_sys_info dosi;

SQL Server / Ola Hallengren: Re-create statistics

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES'

,@FragmentationLow = NULL

,@FragmentationMedium = NULL

,@FragmentationHigh = NULL

,@UpdateStatistics = 'ALL'

,@StatisticsSample=100

FirstResponderKit

 https://downloads.brentozar.com/FirstResponderKit.zip

Dynamics - where to find its cfg

 

SELECT *

  FROM [dbo].[SYSSERVERCONFIG]

SELECT *

  FROM [dbo].[SRSSERVERS]

SELECT *

  FROM [dbo].[BATCHSERVERCONFIG]

SQL Server: how to start the mail client and check its status

USE msdb ; 
EXEC msdb.dbo.sysmail_start_sp;
EXEC msdb.dbo.sysmail_help_status_sp;

Scripting Out the Logins, Server Role Assignments, and Server Permissions

 -- Scripting Out the Logins, Server Role Assignments, and Server Permissions

-- ************************************************************************************************************************

-- CRITICAL NOTE: You’ll need to change your results to display more characters in the query result.

-- Under Tools –> Options –> Query Results –> SQL Server –> Results to Text to increase the maximum number of characters 

-- returned to 8192 the maximum or to a number high enough to prevent the results being truncated.

-- ************************************************************************************************************************

SET NOCOUNT ON

-- Scripting Out the Logins To Be Created

SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+

   CASE 

WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 

+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END

ELSE ' FROM WINDOWS WITH'

END 

   +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]

FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL

ON SP.principal_id = SL.principal_id

WHERE SP.type IN ('S','G','U')

AND SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added

SELECT 

'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''

' AS [-- Server Roles the Logins Need to be Added --]

FROM master.sys.server_role_members SRM

JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id

JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id

WHERE SL.type IN ('S','G','U')

AND SL.name NOT LIKE '##%##'

AND SL.name NOT LIKE 'NT AUTHORITY%'

AND SL.name NOT LIKE 'NT SERVICE%'

AND SL.name <> ('sa');

-- Scripting out the Permissions to Be Granted

SELECT 

CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 

THEN SrvPerm.state_desc 

ELSE 'GRANT' 

END

    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 

CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 

THEN '' 

ELSE ' WITH GRANT OPTION' 

END collate database_default AS [-- Server Level Permissions to Be Granted --] 

FROM sys.server_permissions AS SrvPerm 

JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 

WHERE   SP.type IN ( 'S', 'U', 'G' ) 

AND SP.name NOT LIKE '##%##'

AND SP.name NOT LIKE 'NT AUTHORITY%'

AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name <> ('sa');

SET NOCOUNT OFF