log send queue size and redo queue size in AG I am trying to figure out the way to monitor these 2 events

log send queue size - I can see this in perfmon

Redo queue size - I can see in dmv but not in perfmon counter

Is there any way of using perfmon so that I can calculate the redo queue size even thought the counter is not available in perfmon?
Also, I see these events are logged in the Windows event viewer when they are part of database mirroring. But now using AG, how is it possible to log these 2 values in Windows event viewer for a certain range exceeding?
Edit
By alert I mean do we have something in AG as shown here as a message seen from db mirroring?

Both the log send queue and redo queue can be found in sys.sys.dm_hadr_database_replica_states (docs), and also available using the SQLServer:Database Replica Perfmon counters (docs).
Perfmon
With Perfmon counters, you'll need to collect counters individually on each replica. Each replica knows about the Send/Redo(recovery) queue affecting that replica itself.
It sounds like you already found the Log Send Queue perfmon counter, which is defined as:

Amount of log records in the log files of the primary database, in kilobytes, that haven't been sent to the secondary replica. This value is sent to the secondary replica from the primary replica. Queue size doesn't include FILESTREAM files that are sent to a secondary.

The perfmon counter for the Redo Queue is actually the Recovery Queue counter, which is defined as:

Amount of log records in the log files of the secondary replica that have not been redone.

You can also access the Perfmon counters using T-SQL by querying the sys.dm_os_performance_counters DMV.
SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Database Replica'
AND counter_name IN ('Log Send Queue','Recovery Queue')
ORDER BY cntr_value DESC;

T-SQL / DMVs
From T-SQL, you can query the DMVs to get this info. The below query is from my standard toolbox, and includes both the Send & Redo queue sizes & rates (in the first few columns), plus some other information on the replica states & send/redo status. Note that running this on the Primary Replica will report status information for all replicas, running it on the secondary replica will only report local information for that secondary replica.
The advantage of monitoring via T-SQL is that you can have a single/central collection point to check on the status of all replicas at once.
-- ▶ Run this on **PRIMARY**
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
    --AG info
    AGName                          = ag.name,
    ReplicaServer                   = ar.replica_server_name,
    DbName                          = adc.database_name,
    -- Queue Size & rates
    SendQueueSize                   = drs.log_send_queue_size,
    RedoQueueSize                   = drs.redo_queue_size,
    SendRateKb                      = drs.log_send_rate,
    RedoRateKb                      = drs.redo_rate,
    -- State Info
    IsLocal                         = drs.is_local,
    IsPrimary                       = drs.is_primary_replica,
    SynchronizationState            = drs.synchronization_state_desc,
    SynchronizationHealthState      = drs.synchronization_health_desc,
    -- Last ____ Time
    LastSentTime                    = drs.last_sent_time,
    LastRedoTime                    = drs.last_redone_time,
    LastReceivedTime                = drs.last_received_time,
    LastHardenedTime                = drs.last_hardened_time,
    LastCommitTime                  = drs.last_commit_time,
    -- LSN info
    RecoveryLSN                     = drs.recovery_lsn,
    TruncationLSN                   = drs.truncation_lsn,
    LastSentLSN                     = drs.last_sent_lsn,
    LastReceivedLSN                 = drs.last_received_lsn,
    LastHardenedLSN                 = drs.last_hardened_lsn,
    LastRedoneLSN                   = drs.last_redone_lsn,
    EndOfLogLSN                     = drs.end_of_log_lsn,
    LastCommitLSN                   = drs.last_commit_lsn,
    --Oh yeah, filestream, too
    FileStreamSendRate              = drs.filestream_send_rate
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_databases_cluster AS adc
    ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
JOIN sys.availability_replicas AS ar
    ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
ORDER BY
    -- ag.name, ar.replica_server_name, adc.database_name;
    drs.log_send_queue_size + drs.redo_queue_size DESC;

Alerting
If you don't have monitoring product capable of watching the Perfmon Counters or DMVs directly, you can hack your way into alerting using the SQL Server Agent Alerting system.
In Object Explorer --> SQL Server Agent --> Alerts, select to create a new alert

On the General tab of the New Alert window, select the Perfmon counter you want to monitor, as well as the alert threshold.

On the Response tab, you can select to either send an email to notify SQL Agent Operators or execute a SQL Agent Job. You can have the SQL Agent job response run T-SQL or PowerShell that executes just about anything (like logging to the Windows Event Log).


CopyFromRecordset Not Working Hi I Inserted A Select MaxID+1 Data and want to retrieve what was that ID I inserted the Following is the codes

Im trying to Insert data from Excel to Ms Access and Automatically Retrieve that Data.
For MultiUser Purpose I need to retrieve specifically the data I inserted.

Sub PostData()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset classe here
Dim dbPath
Dim x As Long, i As Long

'add error handling
On Error GoTo errHandler:

dbPath = Sheets("Sheet3").Range("h1").Value

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

Set rst = New ADODB.Recordset 'assign memory to the recordset
Sql = "INSERT INTO DvID(DVnumber)SELECT Max(DVNumber)+1 FROM DvID "
rst.Open Sql, cnn
Sheet3.Range("A2").CopyFromRecordset rst
rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

On Error GoTo 0
Exit Sub
errHandler:

Set rst = Nothing
Set cnn = Nothing

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"
End Sub

I finally Figure it out.
Do
On Error Resume Next 'reset Err.obj.

 'Get the Max ID +1
Set rst = Nothing
Set rst = New ADODB.Recordset 'assign memory to the recordset
SQL = "SELECT Max(ApNumber)+1 FROM PayVoucherID "
rst.Open SQL, cnn

'Check if the recordset is empty.
'Copy Recordset to the Temporary Cell
Sheets("MAX").Range("A2").CopyFromRecordset rst

'Insert the Data to Database And Check If no Errors
Sql2 = "INSERT INTO PayVoucherID(ApNumber)Values('" & Sheets("MAX").Range("A2") & "') "
cnn.Execute Sql2
Loop Until (Err.Number = 0)}


Database maintenance rebuilding indexes daily My database is around 2.5TB. Is it a good practice to run rebuild indexes daily?

We delete data daily around 20 GB & at the same time we delete partitions & we will add new partition. To run rebuild index is taking 8 hours, sometimes 24 hours. During this, the log file is increasing to 300 GB.

We need to send daily reports for database fragmentation to customer. Seeing 99 or 98% they are scared. Please suggest.

Tables size is 70GB  & for this table fragmentation level daily it will be 99%. No idea what made customer request daily reports of fragmentation level, but he just need it daily.

Firstly you should take into account the page_count of the index. If the page_count is less than 1000 (or whatever you decide) then you should ignore the index.

We have a maintenance script which analyses the page_count and fragmentation of the indexes and follows the following guideline:-

less than 10% logical fragmentation, don’t do anything
between 10% and 30% logical fragmentation, reorganize it (using ALTER INDEX … REORGANIZE)
more than 30% logical fragmentation, rebuild it (using ALTER INDEX … REBUILD)(ONLINE= ON for Enterprise Editions)

This is a starter to find out where you are at present:-

SELECT dbtables.[name] AS 'Table'
    ,dbindexes.[name] AS 'Index'
    ,indexstats.avg_fragmentation_in_percent
    ,CASE
        WHEN indexstats.avg_fragmentation_in_percent < 10
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END
    ,indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
    AND page_count > 1000
    AND dbindexes.NAME IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC

It all depends on your requirements. How is performance effected when you only rebuild once a week? Could you send the report once a week straight after a rebuild? I would base line the performance in a test environment (if possible), and if it doesn't degrade too much, only perform the index maintenance once a week.

An agent job could be something like:-

DECLARE @Indexes AS TABLE (
    ID INT IDENTITY(1, 1) NOT NULL
    ,TableNm VARCHAR(500) NOT NULL
    ,IndexNm VARCHAR(500) NOT NULL
    ,FragPerc DECIMAL(16, 3) NOT NULL
    ,RecAction VARCHAR(50) NOT NULL
    ,PageCount INT NOT NULL
    )
INSERT INTO @Indexes
SELECT dbtables.[name] AS 'Table'
    ,dbindexes.[name] AS 'Index'
    ,indexstats.avg_fragmentation_in_percent
    ,CASE
        WHEN indexstats.avg_fragmentation_in_percent < 10 --change all levels to your requirement
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END
    ,indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
    AND page_count > 1000
    AND dbindexes.NAME IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC

DELETE FROM @Indexes WHERE RecAction = 'NOTHING'

DECLARE @sql AS VARCHAR(MAX)
DECLARE @whilecount AS INT = 1
DECLARE @rowcount AS INT = (
        SELECT MAX(ID)
        FROM @Indexes
        )
DECLARE @IndexNm AS VARCHAR(500)
DECLARE @TableNm AS VARCHAR(500)
DECLARE @RecAction AS VARCHAR(50)

WHILE @whilecount <= @rowcount
BEGIN
    SET @IndexNm = (
            SELECT IndexNm
            FROM @Indexes
            WHERE ID = @whilecount
            )
    SET @TableNm = (
            SELECT TableNm
            FROM @Indexes
            WHERE ID = @whilecount
            )
    SET @RecAction = (
            SELECT RecAction
            FROM @Indexes
            WHERE ID = @whilecount
            )

    IF @RecAction = 'REBUILD'
    BEGIN
        SET @sql = 'ALTER INDEX [' + @IndexNm + '] ON [' + @TableNm + '] REBUILD WITH (FILLFACTOR = 98, ONLINE = ON);' --change depending on SQL version
    END
    ELSE IF @RecAction = 'REORGANIZE'
    BEGIN
        SET @sql = 'ALTER INDEX [' + @IndexNm + '] ON [' + @TableNm + '] REORGANIZE;'
    END

    EXECUTE (@sql);

    SET @whilecount += 1
END


Tuning query with multiple joins i have this query ..
214 Execution/min , 44.42 Avg CPU(ms)
is there a way to make it much faster

SELECT        P.Id id0,
              P.ProgramId ProgramId1,
              P.ProgramName ProgramName2,
              P.ProgramLevel ProgramLevel3,
              P.Department Department4,
              P.Track Track5,
              P.AcademicYear AcademicYear6,
              P.StartTerm StartTerm7,
              P.Delivery Delivery8,
              P.Fee Fee9,
              P.City City10,
              P.STATE State11,
              P.StartDate StartDate12,
              P.Deadline Deadline13,
              P.DeadlineDisplay DeadlineDisplay14,
              P.ProgramType ProgramType15,
              O.Id as OrganizationId16,
              O.NAME OrganizationName17,
              P.ApplicationType ApplicationType18,
              P.Concentration Concentration19,
              P.ZipCode ZipCode20,
              P.Campus Campus21,
              P.WADisplayName WADisplayName22,
              P.UpdatedDate updateDate23,
              AF.Id InstanceId24,
              RD.Id stateId33
INTO #TempGetFullProgramSelectionInfo
FROM    unicas_config..applicationForm AF
  INNER JOIN UNICAS_CONFIG.. AcademicInstitution AI
           ON AF.casid=AI.casid
  INNER JOIN UNICAS_CONFIG..Organization O
           ON O.academicInstitutionid=AI.id
  INNER JOIN UNICAS_CONFIG..AssociationOrg asOrg
           ON asOrg.FormId=AF.id
  INNER JOIN  UNICAS_CONFIG..Program P
           ON P.AssociationOrgId=asOrg.Id and asOrg.OrganizationId=O.id AND AF.Id = 6286
  INNER JOIN  unicas_config..ReferenceData RD
                 ON P.STATE = RD.ValueId AND RD.ValueAbbr ='US'
  INNER JOIN  unicas_config..ReferenceDataSet RS
           ON RD.ReferenceSetId = RS.SetId AND RS.NAME = 'LK_States'

Now this  if i split  to 2 queries

select RD.ValueId, RD.id into #temp1
from   unicas_config..ReferenceData RD
INNER JOIN  unicas_config..ReferenceDataSet RS   ON RD.ReferenceSetId= RS.SetId AND RS.NAME= 'LK_States'
where RD.ValueAbbr='US';

SELECT        P.Id id0,
              P.ProgramId ProgramId1,
              P.ProgramName ProgramName2,
              P.ProgramLevel ProgramLevel3,
              P.Department Department4,
              P.Track Track5,
              P.AcademicYear AcademicYear6,
              P.StartTerm StartTerm7,
              P.Delivery Delivery8,
              P.Fee Fee9,
              P.City City10,
              P.STATE State11,
              P.StartDate StartDate12,
              P.Deadline Deadline13,
              P.DeadlineDisplay DeadlineDisplay14,
              P.ProgramType ProgramType15,
              O.Id as OrganizationId16,
              O.NAME OrganizationName17,
              P.ApplicationType ApplicationType18,
              P.Concentration Concentration19,
              P.ZipCode ZipCode20,
              P.Campus Campus21,
              P.WADisplayName WADisplayName22,
              P.UpdatedDate updateDate23,
              AF.Id InstanceId24,
              RD.Id stateId33
INTO #TempGetFullProgramSelectionInfo
FROM    unicas_config..applicationForm AF
  INNER JOIN UNICAS_CONFIG.. AcademicInstitution AI
           ON AF.casid=AI.casid
  INNER JOIN UNICAS_CONFIG..Organization O
           ON O.academicInstitutionid=AI.id
  INNER JOIN UNICAS_CONFIG..AssociationOrg asOrg
           ON asOrg.FormId=AF.id
  INNER JOIN  UNICAS_CONFIG..Program P
           ON P.AssociationOrgId=asOrg.Id and asOrg.OrganizationId=O.id AND AF.Id = 6286
  INNER JOIN    #temp1 RD ON P.STATE= RD.ValueId;

using the query recommended by Frisbee

Using Hash join

Give this a try
You have some (potentially) misplaced join conditions

SELECT        P.Id id0,
              P.ProgramId ProgramId1,
              P.ProgramName ProgramName2,
              P.ProgramLevel ProgramLevel3,
              P.Department Department4,
              P.Track Track5,
              P.AcademicYear AcademicYear6,
              P.StartTerm StartTerm7,
              P.Delivery Delivery8,
              P.Fee Fee9,
              P.City City10,
              P.STATE State11,
              P.StartDate StartDate12,
              P.Deadline Deadline13,
              P.DeadlineDisplay DeadlineDisplay14,
              P.ProgramType ProgramType15,
              O.Id as OrganizationId16,
              O.NAME OrganizationName17,
              P.ApplicationType ApplicationType18,
              P.Concentration Concentration19,
              P.ZipCode ZipCode20,
              P.Campus Campus21,
              P.WADisplayName WADisplayName22,
              P.UpdatedDate updateDate23,
              AF.Id InstanceId24,
              RD.Id stateId33
INTO #TempGetFullProgramSelectionInfo
FROM       UNICAS_CONFIG..Organization O
INNER JOIN UNICAS_CONFIG..AcademicInstitution AI
            ON AI.id = O.academicInstitutionid
INNER JOIN unicas_config..applicationForm AF
            ON AF.casid = AI.casid
           AND AF.Id = 6286
INNER JOIN UNICAS_CONFIG..AssociationOrg asOrg
            ON asOrg.FormId         = AF.id
           and asOrg.OrganizationId = O.id
INNER JOIN UNICAS_CONFIG..Program P
            ON P.AssociationOrgId = asOrg.Id
INNER JOIN unicas_config..ReferenceData RD
            ON RD.ValueId = P.STATE
           AND RD.ValueAbbr ='US'
INNER JOIN unicas_config..ReferenceDataSet RS
            ON RS.SetId = RD.ReferenceSetId
           AND RS.NAME = 'LK_States'

If #temp has indexes then sort on the one that makes the most sense

If that does not improve response then just force a hash join on all
Still use the select in my answer - add the HASH to the query above

INNER HASH JOIN


MySQL - Many to Many Join. Left Join already Present I am building a Attendance System for a Client and have become a tad stuck with JOIN's. I already have a left JOIN running, and want to add a JOIN on a many-to-many relationship but can't get my head around it.

The Problem:

I have a query that checks if a student is currently in a class, if they are it brings back the Student_ID, Class_ID and Attendance Status E.G Late, Present or Absent.

This query runs fine *IF the student is currently in a class, if they are not it returns an empty set. *

What I would like it to return is the student ID even if the student is in no class.

This is my query:

SELECT  student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_Status end as 'attendence_status'
          FROM Student
          INNER JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id)
          INNER JOIN class ON(class.class_id = student_has_class.class_id)
          INNER JOIN Subject ON(class.subject_id = subject.subject_id)
          LEFT JOIN Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE NOW() between class.class_start_timestamp and class.class_end_timestamp
          AND Student.student_id like '1'

I think it needs a Left Outer Join but I can't seem to get my head around how to format the query correctly.

I have tried the below query but doesn't work!!!

SELECT  student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_Status end as 'attendence_status'
          FROM Student
          LEFT OUTER JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id
                                                AND Student.student_id like '1')
          LEFT OUTER JOIN class ON(class.class_id = student_has_class.class_id)
          INNER JOIN Subject ON(class.subject_id = subject.subject_id)
          LEFT JOIN Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE NOW() between class.class_start_timestamp and class.class_end_timestamp;

Any help would be very much appreciated.

There is a many to many relationship between: Student and Class called Student_Has_Class

As a rule of thumb conditions on the Outer table are usually placed in WHERE, but on the Inner table in ON:

SELECT  DISTINCT student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_Status end as 'attendence_status'
          FROM Student
          LEFT JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id)
          LEFT JOIN class ON class.class_id = student_has_class.class_id
            AND NOW() between class.class_start_timestamp and class.class_end_timestamp
          LEFT JOIN Subject ON(class.subject_id = subject.subject_id)
          LEFT JOIN Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE Student.student_id like '1'

And please no like '1', it's = '1', but probably student_id is numeric so = 1


Enforcing option hint for a statement in a while loop is not possible SQL Server 2014 Compatability level 110 uses old Cardinality Estimator.
I am trying to enforce new CE for a statement.
I am getting error "Incorrect syntax near the keyword 'option'."
I need to do deletions in batches.

while
(
    Select top (1) 1
    from [AdventureWorks].dbo.DatabaseLog
    option (querytraceon 2312)  --error
) > 0
begin
    delete top (10000 )
    from [AdventureWorks].dbo.DatabaseLog
end

-- it runs ok by itself:

Select top (1) 1
from [AdventureWorks].[Person].[Person]
option (querytraceon 2312)

The option clause can only be used on standalone SELECT statements.

Not where the SELECT is being used as an expression.

The following examples don't allow you to specify a query hint either.

DECLARE @I INT = (SELECT 1 OPTION (RECOMPILE))

IF (1 = (SELECT 1 OPTION (RECOMPILE))
    PRINT '1';

So you will either need to

restructure your code so that the statement you are trying to apply a query hint to is not directly embedded inline in the WHILE (would require either repeating the statement or wrapping it in a non inline function)
set the trace flag on at the session level
Use a plan guide (potentially - I haven't tested this)

For the specific example in your question you don't need the SELECT at all.

DECLARE @Dummy VARCHAR(100) ='setting initial rowcount'

WHILE @@rowcount > 0
  BEGIN
      DELETE TOP (10000 ) FROM [AdventureWorks].dbo.DatabaseLog;
  END

or (as this will end up deleting all rows anyway).

TRUNCATE TABLE [AdventureWorks].dbo.DatabaseLog;


Tracking down a transaction rollback I have a very busy database and I've been asked to look for transactions that rollback.

I used SQL Server Profiler to monitor the TM: Rollback Tran completed event, which shows me that dozens of these events are happening every minute, but it doesn't give me any indication as to what transaction is being rolled back, what objects are affected by the rollback, or why the rollback occurred.  In short, it seems of very limited use.

If I try to also monitor, for example, SQL:StmtStarting to give some context to the rollback then there is simply too much noise on this busy environment to be able to identify which statement is responsible for the rollback.

I also tried running the statement

SELECT *
FROM fn_dblog (NULL, NULL)
WHERE Operation = 'LOP_ABORT_XACT';

but this did not return any results (which seems odd given the number of rollbacks being reported by SQL Profiler).

Is there any way that I can produce a report showing transactions that have rolled back?

TIA!

Here's the pre-Extended Events way to do this.

Instead of using Profiler interactively, use it to generate a Server Side SQL Trace that writes to a file.  After running the trace for a bit, you can query the trace files and/or load the trace files into a table and query those.  Find a session that has the rollback, and then query for all the events for that session, in order.


What does the base64 BINLOG statements in mysqlbinlog output mean? I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0.

When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed those base64 statements:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111219 19:25:06 server id 395220  end_log_pos 107      Start: binlog v 4, server v 5.5.18-log created 111219 19:25:06
BINLOG '
ko/vTg/UBwYAZwAAAGsAAAAAAAQANS41LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#111219 19:25:06 server id 370534  end_log_pos 171      Query   thread_id=387276        exec_time=73680 error_code=0

When I've checked the MySQL 5.0 bin-log files, I've noticed those statments were missing. My first guess was that this is a new feature in MySQL Replication. I know I should not use binlogs from MySQL 5.5 on MySQL 5.0. Therefore, the only way I can promise the rollback of the data is by running the queries as queries.

When I ran mysqlbinlog --base64-output=NEVER, I noticed that the base64 statements had disappeared. I would have expected them to be translated to plain text queries, but this was not the case.

Can someone put more light on what those base64 statements means?

Thanks,

Shahar.

From the MySQL Documentation

  •--base64-output[=value]   This option determines when events should
  be displayed encoded as base-64 strings using BINLOG statements. The
  option has these permissible values (not case sensitive):

  ■ AUTO ("automatic") or UNSPEC ("unspecified") displays BINLOG
  statements automatically when necessary (that is, for format
  description events and row events). If no --base64-output option is
  given, the effect is the same as --base64-output=AUTO.

  Note

  Automatic BINLOG display is the only safe behavior if you intend to
  use the output of mysqlbinlog to re-execute binary log file contents.
  The other option values are intended only for debugging or testing
  purposes because they may produce output that does not include all
  events in executable form.

  ■ ALWAYS displays BINLOG statements whenever possible. If the
  --base64-output option is given without a value, the effect is the same as --base64-output=ALWAYS.

  Note

  Changes to replication in MySQL 5.6 make output generated by
  this option unusable, so ALWAYS is deprecated as of MySQL 5.5.8 and
  will be an invalid value in MySQL 5.6

  ■ NEVER causes BINLOG statements not to be displayed. mysqlbinlog
  exits with an error if a row event is found that must be displayed
  using BINLOG.

  ■ DECODE-ROWS specifies to mysqlbinlog that you intend for row events
  to be decoded and displayed as commented SQL statements by also
  specifying the --verbose option. Like NEVER, DECODE-ROWS suppresses
  display of BINLOG statements, but unlike NEVER, it does not exit with
  an error if a row event is found.

  For examples that show the effect of --base64-output and --verbose on
  row event output, see Section 4.6.7.2, “mysqlbinlog Row Event
  Display”.

CAVEAT

Please keep in mind that binary logs are not backward compatible.

In ServerFault. someone thought that a master's binary log was corrupt, not realizing that binary logs have different start points. I addressed by explaining the following:

An empty binary log's filesize is

for MySQL 5.5, 107
for MySQL 5.1, 106
pre-MySQL 5.1, 98

As you already pointed out, Replication works from a 5.0 Master to a 5.5 Slave, not the other way around.


MySQL Workbench - how to search all columns in every table In MySQL Workbench how do I search for a column which can be located in any schema. Basically I'm looking for a column called "notification" and I want to search all schemas for that column.

Look at this screenshot where I know for a fact there is a column named "activated_at" yet when I search for it after highlighting all my tables (using select all command also) it finds nothing. Why?

but if I run the following query its able to find the column, so why not with GUI?

    SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    ordinal_position

FROM  INFORMATION_SCHEMA.COLUMNS

WHERE column_name = 'activated_at' ;

Open a new Query tab - from the File menu option choose the New Query item (second one down).

Then, it's exactly the same as in the MySQL tool.

use information_schema;
show tables;
SELECT * FROM columns;

will give the result:

Tables_in_information_schema
-----------------------------
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS                                <<--
COLUMN_PRIVILEGES
..
<more columns snipped>
..

For some reason, the information_schema database doesn't appear in the
databases list in the GUI, but if you explicitly 'use' it, it will work.

Et voilà


AWS Webapp tutorial: how to choose source in database security group? I am following the AWS tutorial to create a Webapp here: http://docs.aws.amazon.com/gettingstarted/latest/wah-linux/getting-started-create-rds.html

I am stuck at the part 'Create a Security Group for Your DB Instance'.

The tutorial instructs you to start writing 'WebserverSG' into the IP source, and to select an option. However, even after completely typing 'WebserverSG', no such option is presented and I am unable to create the security group.

Is there a way to know the source without selecting it from the options? Alternatively, have I done something wrong for the option to not be presented?

This is my first time dealing with AWS; I don't have a concrete understanding of the purpose of security groups - so please bear this in mind!

Thanks in advance for any answers.

A couple of things:

The tutorial assumes that you have already created an EC2 Security Group named WebServerSG, as shown in the previous step of the tutorial.
As of this writing, the auto-complete logic for the field in question keys off of the security group's Group ID rather than the Group Name. The tutorial incorrectly suggests the opposite. You can verify this by typing sg- (all Group IDs begin with sg-) and seeing the drop-down show all of your existing security groups.

Here's how it looks when I try it:

Security Groups are an abstraction that define inbound and outbound traffic rules that you can apply to instances.

The purpose of this exercise is to allow inbound traffic associated with MySQL (we can tell this because the inbound port in the listed rule is 3306 -- commonly associated with MySQL) from your web server(s) to your database server(s).

As demonstrated in the tutorial, you can reference other security groups in the rule definitions to build complex (or in this case, common -- web application servers regularly communicate with database servers) access patterns.


How do I GRANT for all tables across all schemas I want to

GRANT SELECT
    ON ALL TABLES
    TO foo;

But postgres tells me I have a syntax error at "TO". It expects a GRANT like:

GRANT SELECT
    ON ALL TABLES
    IN SCHEMA public
    TO foo;

We have a handful of tables that have been sharded such that public, shard123, and shard124 all have a table my_table, and public.my_table is the parent of both shard123.my_table and shard124.my_table. I've GRANTed SELECT to foo for public schema, but foo doesn't have permission for any of the shard schemas.

I've also done:

ALTER DEFAULT PRIVILEGES
    FOR ROLE dba
    GRANT SELECT
    ON TABLES
    TO foo;

...which should automatically grant for any new tables and schemas made from now on.

There are 1000 < n < 10000 shards/schemas, so GRANTing separately for each shard/schema is impractical.

How can I GRANT to a role for all existing tables across all existing schemas (all shards)?

Or even better, can I GRANT for all schemas LIKE 'shard%'?

If you need to do this only once, the quickest way is probably the following.

Find all the schemas you want to touch by querying, for example, the pg_namespace system catalog:

SELECT nspname
FROM pg_namespace
WHERE nspname LIKE 'shard%'; -- tweak this to your needs

Then we can expand the query output into GRANT statements:

SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || nspname || ' TO foo;'
FROM pg_namespace
WHERE nspname LIKE 'shard%';

This will give you an enormous amount of statements, which you can just copy over and run.  As you have a big number of schemas, the easiest is possibly the following (running it in psql):

\copy (SELECT 'GRANT ...' ...) TO some_local_file.sql

\i some_local_file.sql

Of course, if you prefer, you could do this using dynamic SQL, too - I find the above solution slightly better, as it leaves a file behind with the actions taken, that can then be checked in under version control.

If you happen to use a psql version 9.6 (or later), there is just another possibility using \gexec:

SELECT 'GRANT SELECT ...'
... -- you can even omit the semicolon here
\gexec

Notes:

I've written an answer about a similar issue a while ago - you may find useful details there.
there is no possibility of doing this from GRANT, unfortunately.  As it is not uncommon what you need here, I'd expect this functionality appearing sooner or later.
just to emphasize, the above commands will do the trick for the already existing tables.  Future tables need the default privileges (which you've set, for a given role).


MySql column value to indicate 2 possible options I don't know if the title represent my doubt very well, but basically I have some columns that can represent 2 possible values.

For example, with the following table that retains data about orders:

id | payment_type | shipping_type
14 | 1            | 1
15 | 1            | 2
16 | 2            | 1

So, for this those 1 and 2* would be:

payment_type: 1= 'Money', 2= 'Online'
shipping_type: 1= 'Delivery', 2= 'Take on store'

* The number values are used to store in the database only, on the screen it's displayed the text for the admins.

This data need to be displayed on the screen for the admins to analyse the order report. Some times the "labels" (money/online) may change, not so frequently but that's a possibility, but it will always be one or the other option.

This data should be displayed on the screen as simple as the following:

Order: #01234
Payment type: Money
Delivery type: Take on store

This data also needs to be able to be edit. For example, let's say I sold an Item for my client to delivery on his address, but for some reason he was passing by and decided to stop on the store to take it. So I need to change the shipping_type from 1 (delivery) to 2 (take on store). Same applies for the payment_type.

So, my doubt is about the way it's organized and how I can manage this data in order to be able display and edit as needed. Currently I'm naming the fields manually with a CASE statement, ex.: CASE WHEN payment_type = 1 THEN 'Money' ELSE 'Online' END AS 'payment_type'. But I'm feeling this is not the best way to go, since it's very manually and if I update the field, I need to also update it manually in multiple queries. If the terminology changes, ex.: Money becomes Check, than I would need to go trought all the options and change it. The name or "labels", don't change often, but it's something that may occur, as it already did in the past.

As an alternative, I was thinking about a table like the one shown below:

payment_table
id | value | name
-  | 1     | Money
-  | 2     | Online

But would have a lot of these tables. Actually, it's not a big deal, but I'd like to know if this is the correct approach to this situation. I'm new to databases and backend in general.

You can use the MySQL ENUM type which is stored as a one-byte int internally. From the docs which only hint on implementation,

  Inserting 1 million rows into this table with a value of 'medium' would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string 'medium' in a VARCHAR column.

Though it's documented elsewhere explicitly,

  1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)

MySQL can use two byte enums if you have 255<x<65,535 potential values to enumerate over.

It looks like this,

CREATE TABLE payment (
    id      int  AUTO_INCREMENT,
    payment ENUM('money', 'online'),
    PRIMARY KEY (id)
);


postgresql query hangs indefinitely over VPN - new firewall We have a product where a local linux machine "SCANNER" does some polling on a local network and then stuffs information into a database in our cloud server over an OpenVPN connection, using pg_query (server is running PostgreSQL 9.5.5).

There is a PHP (5.5.9) daemon on SCANNER that checks the database in a 'while' loop for work to be done. This has always worked great, and continues to work great on all of our client networks, except for one, which has recently developed a bizarre problem.

After they upgraded their firewall (a Checkpoint 5200, and as far as we can tell, all the rules are correct to allow traffic from SCANNER to our cloud server over the VPN), one query in one script hangs indefinitely. Here are the symptoms we have noted:

Most of the time, the query works fine and the script continues. Every once in a while tough, the pg_query() call blocks and never returns. It's not that there's an error; the call literally blocks forever (or many hours until we manually restart).

This query has been the same for a long time, and we have never had this problem at any of our other clients, nor at this client until they changed their firewall.

We can tell from watching the pg_stat_activity table on the cloud server that the query does make it to the cloud, and then sits in that table forever. In every case, pg_stat_activity.state='idle' and pg_stat_activity.waiting=false

During this time, we can still ping the cloud server from SCANNER over the VPN, and we can continue to successfully query its remote database from other scripts on SCANNER, and from SCANNER's command line.

This client happens to have two different SCANNER machines, on different subnets but behind the same firewall. This problem can occur at any time on either one, but doesn't necessarily occur at the same time on both (at least not with any statistical significance).

If we restart the daemon, the problem is temporarily resolved. But it usually recurs sometime between 2 seconds and a few hours later.

We are looking for any input that might solve the problem, whether it is related to our application or the firewall itself (which we have been given permission to modify as needed). Feel free to ask any clarifying questions.

Thanks in advance!

We solved the problem. Technically, we never figured out exactly why it was happening, but UDP packets were getting received by the VPN server in an incorrect order, or sometimes not at all. The firewall didn't give us any indication that it was doing this, so as best we can figure it was an unlogged side-effect of CheckPoint's multiple layers of threat protection.

We switched over to using OpenVPN over TCP instead of UDP, and that seems to have solved the problem. Hopefully we don't suffer any adverse effects from that in the long-term!


Why does mysql adds a default timestamp on insert/update even if I did not specify anything? I have a table that is as follows:

CREATE TABLE SomeTable (
    id int unsigned NOT NULL AUTO_INCREMENT,
    status enum('broken','repaired') NOT NULL,
    from datetime NOT NULL,
    until datetime NOT NULL,
    brought_in timestamp,
    brought_by varchar(255) NOT NULL,
    repair_status enum('unpayed', 'payed') DEFAULT NULL,
    payed_when timestamp,
    delivered_when timestamp,
    primary key(id)
);

When I create the table if I do a SHOW CREATE TABLE then I see that the column brought_in is defined to have the current timestamp ON INSERT and ON UPDATE.
But I did not add such an instruction in my create table.
Why doesn't it let it be empty like the other columns?
Now on every insert or update of another column it adds the current timestamp. I don't want that.
It does not do that with the other columns. What am I doing wrong?

This is the default behaviour of the (first) timestamp column. Many more details can be read in the documentation: Automatic Initialization and Updating for TIMESTAMP.

If you don't want this, you have to explicitly tell MySQL when you create the table. You can declare a default value (or default null) and the automatic properties will be supressed. Any of the following will do (with differences in behaviour explained):

Option 1: not null and default 0

brought_in timestamp NOT NULL DEFAULT 0,             -- option 1a

brought_in timestamp DEFAULT 0,                      -- option 1b

These two options are identical. The column is defined as NOT NULL and has a default value of 0. Note that TIMESTAMP is the only datatype that has a default property of NOT NULL, in contrast with all other datatypes and with the ISO/ANSI standard.

If you try to insert NULL in the column, the CURRENT_TIMESTAMP is inserted (surprise, another MySQL feature.)

Option 2: null and default null

brought_in timestamp NULL DEFAULT NULL,              -- option 2a

brought_in timestamp NULL,                           -- option 2b

These are identical, they allow NULL (and both result in default being NULL as well.)

If you try to insert NULL in the column, it is allowed and inserted fine (no surprises here.)

Option 3: null and default 0 or some other constant

brought_in timestamp NULL DEFAULT 0,                 -- option 3a

brought_in timestamp NULL DEFAULT '2013-01-01',      -- option 3b

This is similar to option 2 but has a different default value.

If you try to insert NULL in the column, it is allowed and inserted fine, too (no surprises here either.)


Searching for a keyword across multiple columns I have a set of names of schools stored in each row in my schools table. the same table has four different category columns which store the names of the subjects they offer. I want to enable functionality where the user conducts a search on the table to find out all the names of the schools which teach a particular subject (for eg: physics). The name of the particular subject could be stored in any of the four subject columns. Please suggest a mysqli_/mysql_ query to conduct such a search or if you may so please, suggest a PHP script that would achieve the above functionality.

You could write a simple query using OR in WHERE clause and passing your search string.

SELECT school_name
FROM schools
WHERE Subject1 = 'Physics'
OR Subject2 = 'Physics'
OR Subject3 = 'Physics'
OR Subject4 = 'Physics'

You can parameterize the search string and pass on to this query.


Is there any way to tell the number of matches found in a database value matched with LIKE? Is there any way I can query a SQL-Server-CE database that will return the number of matches found in a cell value using LIKE (or MATCH AGAINST or any other method I'm not aware of, really)?

EXAMPLE table, "exampleTable":

**ObjectID**        **value**
     1           I Love Lemonade
     2             I Love Love

Using a query like this one:  SELECT * FROM exampleTable WHERE value LIKE '%love%', I can easily find any entries where the value contains the word (or has a word where part of it contains the word) "love." I would like to be able to know just "how many" matches were found (for sake of search priority) in the matched fields, so that they can be displayed as more relevant. I realize that I'm probably asking a lot out of a simple query, however, I also know how powerful SQL can be, too, so thought I would ask if this was possible and if not, if there is any other approach I might take (that won't force me to rewrite the database).

I thought of using sub-queries somehow, but there is no way (that I have been able to find) to combine "LIKE" and "IN".  Also, I am not even sure using a sub-query would get the job done since I'm working with a relational database where one main table relates to 8 others and the "8 other" tables have the values I want to search on, but the main table has the location I need (although, I suppose I don't have a problem adding a "location" column to these "8 other" tables, as it would be helpful in other areas, too).

It's entirely possible that I didn't design the "relational" part of the relational database to perfection, but this is the first time I have designed (although, certainly not worked with) a relational database by myself, and, at least, it works well with the back-end of my website (yes this site is a private partial CMS).

Any help is appreciated, and thank you for your time!

You compare the length of the string with and without the text you're searching for:

  select
        ObjectID
        ,value
        ,(len(value) - len(replace(value,'love',''))) / 4 as occurs
        from exampleTable
        where value like '%love%'


What is the reason of Host 'host_name' is blocked in MySQL? When I use incorrect password to connect to mysql server, and the test count is over the max_connect_errors, and I find the Aborted_connects is also arise as the count I test, but the host is still not blocked.

So does the Aborted_connects over the max_connect_errors then host_name is blocked?

At this point, you may need to run FLUSH HOSTS and see if this unblocks.

The MySQL Documentation says this about FLUSH HOSTS:

  Empties the host cache tables. You should flush the host tables if
  some of your hosts change IP address or if you get the error message
  Host 'host_name' is blocked. When more than max_connect_errors errors
  occur successively for a given host while connecting to the MySQL
  server, MySQL assumes that something is wrong and blocks the host from
  further connection requests. Flushing the host tables enables further
  connection attempts from the host. See Section C.5.2.6, “Host
  'host_name' is blocked”. You can start mysqld with
  --max_connect_errors=999999999 to avoid this error message.

Why should a host get blocked to begin with ??

According to the MySQL Documentation:

  If you get the following error, it means that mysqld has received many
  connect requests from the host 'host_name' that have been interrupted
  in the middle:  Host 'host_name' is blocked because of many connection
  errors. Unblock with 'mysqladmin flush-hosts'   The number of
  interrupted connect requests permitted is determined by the value of
  the max_connect_errors system variable. After max_connect_errors
  failed requests, mysqld assumes that something is wrong (for example,
  that someone is trying to break in), and blocks the host from further
  connections until you execute a mysqladmin flush-hosts command or
  issue a FLUSH HOSTS statement. See Section 5.1.3, “Server System
  Variables”.

  By default, mysqld blocks a host after 10 connection errors. You can
  adjust the value by starting the server like this:

shell> mysqld_safe --max_connect_errors=10000 &

  If you get this error message for a given host, you should first
  verify that there isn't anything wrong with TCP/IP connections from
  that host. If you are having network problems, it does you no good to
  increase the value of the max_connect_errors variable.


BCP To Show Full Text Values I have a long varchar field that I am exporting to a csv with BCP.  The export goes as it should, however in the actual cell itself the csv file shows X.XXXXXX+13 instead of XXXXXXXXXXXXXX

Am I missing a Cast() or BCP switch that could force the csv file to show XXXXXXXXXXXXXX?  Or since it is BCP this is the output you get?

Declare @sql nvarchar(max), @filename varchar(max)
Set @filename = 'C:\Test.csv'
Create Table #test (field1 varchar(100))
Insert Into #test (field1) Values
('XXXXXXXXXXXXXX')
Select @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM #Test''" queryout "'
SET @sql = @sql + @filename + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql

This works fine for me:

CREATE TABLE test ( field1 VARCHAR(100) );
INSERT  INTO test
        ( field1 )
VALUES  ( 'XXXXXXXXXXXXXX' );

DECLARE @sql VARCHAR(4000) ,
    @filename VARCHAR(4000);
SET @filename = 'C:\temp\Test.csv';
SELECT  @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM Test" queryout "';
SET @sql = @sql + @filename + '" -c -t, -T -S ' + @@SERVERNAME;
PRINT @sql;
EXEC master..xp_cmdshell @sql;

Providing results:

field1
"XXXXXXXXXXXXXX"

Force text formatting on the column by adding a preceeding = thus:

CREATE TABLE test ( field1 VARCHAR(100) );
INSERT  INTO test
        ( field1 )
VALUES  ( 'XXXXXXXXXXXXXX' );

DECLARE @sql VARCHAR(4000) ,
    @filename VARCHAR(4000);
SET @filename = 'C:\temp\Test.csv';
SELECT  @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT ''=''+CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM Test" queryout "';
SET @sql = @sql + @filename + '" -c -t, -T -S ' + @@SERVERNAME;
PRINT @sql;
EXEC master..xp_cmdshell @sql;


Where does SQL Server physically store the IDENTITY VALUE for a table? I'm hoping someone can point me in the right direction on this one.  Here's my workings out so far.

SELECT * FROM sys.identity_columns is a system view which gives "last_value" but the definition for that view uses an internal function IdentityProperty(colName, 'LastValue') - so that's a dead end (not pulling it from a system table there).

Everywhere (I've looked) on the internet suggests using DBCC IDENT_... commands to uncover the value but that still leaves me in the dark as to where it's actually stored.

So, I arrived at searching the individual pages with DBCC PAGE(TestDB,1,1325,3) against my test harness db and using the RESEED command to reseed between values 10 and 12.

In doing this, I noticed the hex values on the IAM: Header, IAM: Single Page Allocations and IAM: Extent Alloc Status Slot 1 all changed.  (And realised they change periodically anyway along with the bUse1 value which changes incrementally by itself too).

So another dead-end and I'm all out of ideas.  Where else can I search?

I'm running SQL Server 2014. I have an insatiable thirst for internals knowledge and have yet to come across anything as elusive as this.  It's caught my attention because in theory, it (an absolute value) is stored somewhere and should (arguably) be locatable. In my quest to unearth locations of internally stored data / meta data, this particular value strikes me as particularly elusive.  I'm guessing/hoping that someone will come along and tell me, you can get it with DBCC PAGE but I was looking in the wrong place.

If you can access the DAC (Dedicated Administrator Console), you can inspect the value of the identity column, for INT columns, by looking at the idtval column in sys.syscolpars.

Thanks to Martin Smith for directing me to that table via this very useful answer by Roi Gavish on a related question here.

Take, for instance, the following temporary table:

USE tempdb;

CREATE TABLE #d
(
    ID INT NOT NULL IDENTITY(1,1)
);

TRUNCATE TABLE #d;

DBCC CHECKIDENT ('#d',RESEED, 2147483635);

INSERT INTO #d DEFAULT VALUES;

Let's see what the table contains:

SELECT *
FROM #d;

+------------+
| ID         |
+------------+
| 2147483635 |
+------------+

The identity value can be inspected by this code:

DECLARE @idtval VARBINARY(64);

SELECT @idtval = scp.idtval
FROM sys.syscolpars scp
    INNER JOIN sys.objects o ON scp.id = o.object_id
WHERE o.name LIKE '#d____%'

DECLARE @LittleEndian NVARCHAR(10);
SET @LittleEndian = LEFT(sys.fn_varbintohexstr(@idtval), 10);
SELECT @LittleEndian;
DECLARE @BigEndian NVARCHAR(10) = '0x';
DECLARE @Loop INT = 0;
WHILE @Loop < 4
BEGIN
  SET @BigEndian = @BigEndian + SUBSTRING(@LittleEndian, ((4 - @Loop) * 2) + 1, 2);
  SET @Loop += 1;
END
SELECT CurrentIdentityValue = CONVERT(INT,
    CONVERT(VARBINARY(32), @BigEndian, 1), 2);

+----------------------+
| CurrentIdentityValue |
+----------------------+
|                      |
| 2147483635           |
+----------------------+

For BIGINT identity columns, we need to expand the size of some variables used in the code, such as:

CREATE TABLE #dBig
(
    ID BIGINT NOT NULL IDENTITY(1,1)
);

TRUNCATE TABLE #dBig;

DBCC CHECKIDENT ('#dBig',RESEED, 9223372036854775704);

INSERT INTO #dBig DEFAULT VALUES;

SELECT *
FROM #dBig;

DECLARE @idtval VARBINARY(64);

SELECT @idtval = scp.idtval
FROM sys.syscolpars scp
    INNER JOIN sys.objects o ON scp.id = o.object_id
WHERE o.name LIKE '#dBig____%'

DECLARE @LittleEndian NVARCHAR(18);
SET @LittleEndian = LEFT(sys.fn_varbintohexstr(@idtval), 18);
DECLARE @BigEndian NVARCHAR(18) = '0x';
DECLARE @Loop INT = 0;
WHILE @Loop < 8
BEGIN
  SET @BigEndian = @BigEndian + SUBSTRING(@LittleEndian, ((8 - @Loop) * 2) + 1, 2);
  SET @Loop += 1;
END
SELECT CurrentIdentityValue = CONVERT(BIGINT,
    CONVERT(VARBINARY(32), @BigEndian, 1), 2);

Results for the BIGINT:

+----------------------+
| CurrentIdentityValue |
+----------------------+
|                      |
| 9223372036854775704  |
+----------------------+


Should I disable the guest user on system databases The guest user is disabled on our user databases but data security guys want us to disable it on system databases as well. Should I do it?

No you shouldn't disable the guest account on the system databases.  You also shouldn't disable it on the user databases.  This will stop anyone from using SQL Server Management Studio to connect to the instance.  As long as you haven't granted any permissions in the user databases to public, and you haven't changed any of the default permissions in the databases, then the guest role poses no security risk.

Also your security team should stop making recommendations about how to secure SQL Server  until they've learned how SQL Server and it's tools work.


Having trouble with a T-SQL scalar variable during a create table I am still a beginner, but I am having trouble with a T-SQL scalar variable during a create table.   Can anyone tell me what is wrong with this script?

-- T-SQL MSSQL
SET NOCOUNT ON
DECLARE @tt = 'TempNWA'
--Create Temporary Table
CREATE TABLE [@tt]
 (  [Table_Name] varchar(50),
    Row_Count int,
    Table_Size varchar(50),
    Data_Space_Used varchar(50),
    Index_Space_Used varchar(50),
    Unused_Space varchar(50)
 )
DROP TABLE [@tt]

The error I get, which I can't decipher is:

Incorrect syntax near '='

DECLARE @tt = 'TempNWA'

is missing any dataype declaration. You haven't explained what you are trying to do exactly but I assume you need something like.

SET NOCOUNT ON
DECLARE @TempTable SYSNAME = 'TempNWA'

DECLARE @SQL NVARCHAR(MAX) = N'
CREATE TABLE ' + QUOTENAME(@TempTable) + N'
 (  [Table_Name] varchar(50),
    Row_Count int,
    Table_Size varchar(50),
    Data_Space_Used varchar(50),
    Index_Space_Used varchar(50),
    Unused_Space varchar(50)
 );

DROP TABLE ' + QUOTENAME(@TempTable) + N';'

EXEC(@SQL)

You cannot parameterise object names so would need to use dynamic SQL for this See Dealing with Dynamic Table and Column Names for more.


Why is pg_restore unable to create database with --create option before restoring from dump? Why pg_restore is unable to create database with --create option before restoring from dump?

pg_restore --username=myuser --create --dbname=mydb --verbose  mybackup

Error,

pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL:  database "mydb" does not exist

In order to create a database, it needs to be able to connect to your database server in order to issue the "CREATE DATABASE" command.  Connecting to the server requires you to specify an existing database to connect under, and obviously that cannot be the database which fails to exist and which is to be created.  There are special databases usually reserved for maintenance operations, usually postgres, which you can connect to for this purpose.

When using --create mode, --dbname specifies not the name of the database to be restored into, but rather the name of the database to be used to create the other database.


SQL Server Management Studio closes automatically suddenly My MSSQL Server Management Studio throw's error

  (Exception from HRESULT: 0x800736B3)

Database successfully connecting but

  While opening New Query Window following error message coming

when i press OK then MSSQL Server Management Studio closes automatically.

Try uninstall/reinstall. You have a corrupt installation here.


How to UPDATE a column by COUNT of FK from another table? How to extend this query for the entire table, to update all ids?

UPDATE table1 SET number=(
SELECT COUNT(*) FROM table2 where id=1
) WHERE id=1

id is the PRIMARY KEY in table1 and FK in table2.

How to remove the WHERE clause to update each row of table1 by counting the number of rows in table2 with the same FK id?

You can do an UPDATE JOIN of table1 against a subquery that aggregates id counts in table2

UPDATE
    table1 A INNER JOIN
    (SELECT id,COUNT(1) idcount
    FROM table2 GROUP BY id) B
    USING (id)
SET A.number = B.idcount;

This query will not get every id. Why? If there is an id in table1 that is missing in table2, that does not write a zero in the numbers column.

To cover for id values missing in table2, run this one:

UPDATE
    table1 A LEFT JOIN
    (SELECT id,COUNT(1) idcount
    FROM table2 GROUP BY id) B
    USING (id)
SET A.number = IFNULL(B.idcount,0);

Give it a Try !!!


Issues with 'plus symbol' output in PSQL on update query Simple but frustrating issue... I'm trying to do an update command to correct a misspelling with this field:

grouper=> select * from master_list WHERE group ~ 'JustAdopted';
       group       | activity | usefulness
-------------------+----------+------------
 JustAdoptedAdvice+|          |
                   |          |
(1 row)

But postgres won't give me a straight answer on the field name...

grouper=> UPDATE master_list SET group = 'JustAdoptingAdvice' WHERE group = 'JustAdoptedAdvice';
UPDATE 0

grouper=> UPDATE master_list SET group = 'JustAdoptingAdvice' WHERE group = 'JustAdoptedAdvice+';
UPDATE 0

grouper=> select * from master_list WHERE group ~ '+';
ERROR:  invalid regular expression: quantifier operand invalid

What is going on with that field? Why is that plus symbol there and what does it represent?

And how do I reference it to change it, if the field won't update with or without the symbol being there?

This:

UPDATE master_list
SET group = 'JustAdoptingAdvice'
WHERE group = 'JustAdoptedAdvice+';

should work perfectly fine if the field was really JustAdoptedAdvice+, but I don't think it is. psql is showing that + to mean continuation ... I suspect there's actually a newline there.

UPDATE master_list
SET group = 'JustAdoptingAdvice'
WHERE group = E'JustAdoptedAdvice\n';

There could be whitespace after the newline too, so maybe even:

UPDATE master_list
SET group = 'JustAdoptingAdvice'
WHERE group ~ 'JustAdoptedAdvice[[:blank:]\n]+';

which says "JustAdoptedAdvice followed by one or more of tab/space/newline".

Demo showing it's likely to be a newline:

regress=> CREATE TABLE t4(x text);
CREATE TABLE
regress=> INSERT INTO t4(x) VALUES (E'JustAdoptedAdvice\n');
INSERT 0 1
regress=> SELECT * FROM t4;
         x
-------------------
 JustAdoptedAdvice+

(1 row)

To be sure, look at the hex representation of the utf-8 encoding of the string:

regress=> SELECT encode( convert_to(x, 'utf-8'), 'hex') FROM t4;
                encode
--------------------------------------
 4a75737441646f707465644164766963650a
(1 row)

You'll see that it ends with 0x0a, which is a newline.


Get list of functions used in a view Say I have a Function like this:

create function house_analysis(ingeo geometry)
  returns table(count_all numeric, count_important numeric) as
$$
  select count(*), count(*) filter (where h.import_flag)
  from house_table h where st_intersects(ingeo, h.geom)
$$ language sql stable;

And I define a View like this:

create or replace view postzone_analysis as (
  select p.zipcode, ha.count_all, ha.count_important
  from postzone_table p, house_analysis(p.geom) ha
);

The question is:

How can I query the system catalogs (pg_catalog.*) with my view (postzone_analysis or its oid) get a list of the functions used within it? Their pg_proc.oid values are fine.

I know the database keeps track, since I cannot drop the function, but I can't find the entries in pg_depend.

DB is PostgreSQL 9.5.

(The real-life case is a lot more complex - it's scoped down to be lowest viable example. The view calls like 6 analysis functions, which combines the data from difference sources, and there are multiple views that are based on different area classes.)

Views are implemented as table plus rewrite rule in Postgres. Details in the manual, chapter Views and the Rule System.

The tricky part: not the view itself depends on involved functions, only the rewrite rule does. So this query should be your solution:

SELECT r.ev_class::regclass AS view, d.refobjid::regprocedure AS function
FROM   pg_rewrite r
JOIN   pg_depend  d ON d.objid = r.oid
                   AND d.refclassid = 'pg_proc'::regclass  -- only functions
WHERE  r.ev_class = 'v123'::regclass;  -- name of view here (optionally schema-qualified)

Returns all user-defined functions (excluding built-in functions).

The query assumes you did not define any additional rules for the view. Else filter the one with rulename = '_RETURN', which is the default name for the rewrite rules of a view.

The cast to regprocedure (not just regproc) returns the function with argument types, uniquely identifying it. The name is schema-qualified automatically if the current search_path would not resolve differently otherwise.


psql asks for password, CREATE USER didn't specify one I used to have a user, say 'jack' in postgresql, and then I forgot what the password. I reassigned all its ownerships to user postgres, removed the user, and then created it again with CREATE USER jack;.

Now if I try to connect with it, psql asks for a password.

$ psql db_name jack
Password for user jack:
psql: fe_sendauth: no password supplied

What is going on and how do I check if a user has a password or not?

A user having a password or not won't affect if you're asked for a password when trying to connect.  That is controlled by the pg_hba.conf file.

If you're being asked for a password that indicates that a password is required.  If you try to connect to an account which doesn't have a password set that will simply fail.  You need to either set a password for the account and use that, use a different type of connection or modify the pg_hba.conf file to use a different authentication method.


SQL order of precedence Given a query ending in where a and b and c or d, will this behave as where (a and b and c) or d, or where a and b and (c or d)? (Or something else?)

I've read the docs and they seem to suggest it would be the former as and has a higher precedence than or, but my results seem to performing the latter so I'm wondering if I'm either misunderstanding the docs or else I'm not generating the query correctly (via an ORM).

If the query purpose is unclear, then you should make it clear.

You don't want to be relying on some relatively obscure ordering rules to get it right. You can't rely on them always staying the same throughout different versions of the same product, or if the underlying technology changes (e.g. Oracle -> MySQL).

If you want it to behave in one way, make it explicit how you want it to behave by using parentheses.


Update and select concurrently running on same set of rows but different indexes are referred? In multithreaded application where update and select can go simultaneously assuming update is used within transaction. If table has non clustered index and clustered index, what are the chances of having deadlock in such situation?
clustered index is used by update statement for example Cluster Index Update <- Clustered index seek
whereas non clustered index used by select statement for example select <- nonclustered index seek
What happens when one thread updates and other is using select assuming same rowids referred(where clause returns same rows)?
Are there any chances of having deadlock? how such situations can be handled if application allows update and select to run concurrently?

What are the chances of occurring index row level locking deadlock?

What you are looking at is more of a blocking scenario. Transactions usually happen quickly and you would not notice a difference when an update and select are issued at the same time.

As per your scenario, the update will happen first. Then depending on your concurrency level, the select will be blocked till the end of the update transaction. In a good case scenario, this happens at the micro second level, and the end user does not notice it.

One way a deadlock can occur: the update transaction, while blocking the select transaction, should request for a resource that is held by the select transaction.


PL/SQL - Drop User inside BeginEnd block I have some experience with SQL Server but now I'm starting to work with PL SQL and I'm facing some problems because the difference between the two technologies.

I have some scripts to drop my database and when I need to delete my user I do something like this:

DEFINE OWNER = MYUSER

ALTER SESSION SET DDL_LOCK_TIMEOUT = 3600;

BEGIN
  FOR r IN (select sid,serial# from v$session where username=&OWNER)
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' || r.serial# || '''';
  END LOOP;

  DROP USER &OWNER CASCADE;
END;
/

However I'm getting this error:

  Details:
           ORA-06550: line 6, column 3:   PLS-00103: Encountered the symbol \"DROP\" when expecting one of the following:

  (    begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with        << continue close current delete fetch lock   insert open rollback savepoint set sql execute commit forall   merge pipe purge json_exists json_value json_query   json_object json_array (DBD ERROR:

  error possibly near <*> indicator at char 189 in 'BEGIN   FOR r IN (select
  sid,serial# from v$session where username=MYUSER)   LOOP   EXECUTE
  IMMEDIATE 'alter system kill session ''' || r.sid  || ',' || r.serial#
  || '''';  END LOOP;   <*>DROP USER MYUSER CASCADE;   END; ')

I already tried to add a slash in line 8:

  END LOOP;
  /
  DROP USER &OWNER CASCADE;
END;
/

but the error still appearing, and I already tried to move DROP USER &OWNER CASCADE; to the end of the script but I'm still getting errors.

I can't understand what is wrong and I already read many links this about this, including:

https://www.tutorialspoint.com/plsql/plsql_basic_syntax.htm
https://stackoverflow.com/questions/15664924/dropping-connected-users-in-oracle-database
https://stackoverflow.com/questions/20334067/pls-00103-encountered-the-symbol-create

What is the problem? Begin End don't allow empty lines? DROP USER &OWNER CASCADE; must be inside another Begin End?

Data Definition Language (DDL) Statements

  The DDL statements are:

  ... DROP ... (All statements beginning with DROP) ...

PL/SQL does not support DDL as static SQL:

PL/SQL Static SQL

To run a DDL statement in PL/SQL, use dynamic sql:

PL/SQL Dynamic SQL

Example:

begin
  ...
  execute immediate 'DROP USER &OWNER CASCADE';
  ...
end;
/

Or just use:

BEGIN
  FOR r IN (select sid,serial# from v$session where username=&OWNER)
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' || r.serial# || '''';
  END LOOP;
END;
/

DROP USER &OWNER CASCADE;


Data sharing between client and server two ways One of my Silverlight applications is running. This Application is using one central database (SQL Server 2008) and three other client databases (SQL Server 2008) in server other locations.

Our requirement is that with minor changing or without changing the Silverlight application,
when we make changes in the central database (insert, update and delete in multiple tables) these changes automatically occur in all client databases. And when any change is made in any client database all client as well as central database should be updated automatically.

For this I read some about Sync Framework and SQL Server 2008 Change Tracking. But I have no idea about how will I do this. Please help me. How can we achieve this?

The out-of-the-box solution for this in SQL Server is Peer-to-Peer Transactional Replication. This requires Enterprise edition at all nodes.

If your application truly needs this architecture, I would strongly recommend using the built-in tools rather than trying to patch something together using another technology. Peer-to-Peer replication was designed for this exact scenario.

Having said that, I would recommend that you make sure this architecture is actually necessary, as opposed to having one centralized database. I'm not saying this is the case in your scenario, but sometimes the people in suits can try to dictate a solution like you're asking for, without fully understanding the technical complexity. Setting up and managing this type of replication can be challenging.


Creating a structure to allow for optional teams of people rather than just individual people I currently have a database set up that has tables as follows:

Developer
    id (PK)

Project
    id (PK)
    developer_id (FK)

/* and a number of other first or second level tables related to the Project table. */

Currently each developer can have one or more projects in the project table.

I am now wanting to provide an option for having team management and a team of developers for companies that want to manage entire teams. However, still want the original method of doing things if teams are not needed.

So far I have come up with the idea to implement things like this:

Developer
    id (PK)

Company
    id (PK)
    name

CompanyDeveloper
    company_id (FK)
    developer_id (FK)
    manage_teams (bool) - allows user to create, delete, and manage teams and team members

Team
    id (PK)
    company_id (FK)
    name

TeamDeveloper
    team_id (FK)
    developer_id (FK)

ProjectDeveloper
    project_id (FK)
    developer_id (FK)
    view - allows the developer to view a particular project
    edit - allows the developer to edit a particular project

/* Modified project table to add team_id */
Project
    id (PK)
    developer_id (FK)
    team_id (FK) - can be null for those developers working by themselves and not in a team for a particular company.

Am I on the correct track of thinking here? Is there anything that I am doing wrong or that I could implement in a better way?

Requirements

As requested, my requirements are as follows:

Allowing individual developers to develop one or more projects (this is what I currently have in place)
Companies may purchase a team account in order to manage teams of developers.
One or more people in the company are able to manage teams which includes creation of teams, adding developers to teams, and assigning teams to projects.
Some team developers are able to view the project only, whereas others are able to edit them.

More detailed specs

As requested by SQLRaptor, here are some more specs:

Developers

A Developer is a Person which is identified by developer_id.
A Developer contributes to zero or more projects
A Developer may sign up as an individual developer or may be employed by a company. (I see this as a problem source)
A developer may be assigned to zero or more teams

Companies

A Company is a legal identity which is identified by company_id.
A Company hires teams of one or more Developers.
A Company owns Projects
Developers may be employed by Companies or may be signed up as an individual developer (again this is a problem source)

Teams

A Team is a logical container for Developers which is identified by team_id
A Team can be assigned to zero or more Developers.
A Team can be assigned to zero or more Projects.
A Team belongs to a Company.

Projects

A Project is a collection of tasks identified by id.
A Project may be assigned to zero or more Teams
A Project belongs to either a Company or individual Developer. (This is a problem source)

Questions in regards to the problem  sources:

Should individual developers be required to sign up as a Company?
Should individual developers be assigned a default Team? (In the real world, Teams only exist when there is a Company)?

If you have any other questions please ask.

Am I on the correct track of thinking here?

IMHO you are not. You are putting the carriage in front of the horse.
To get back on the right track, start at the beginning, with your data universe.
Let's list what entities you have in your game, and separate that from the business rules.
First question is: "what is it in the real world, which we are trying to model?". From what you said, I see the following - please fill in the missing pieces. the essence and relationships between developers - companies - teams - projects is not well defined IMHO.

Developers

A Developer is a Person which is identified by {Fill in}
A Developer contributes work to Projects
A Developer works for a company {True?}
A Developer IS a part of a team
A Developer may be assigned to zero or more teams {True?}

** You said that the developer 'may be part of a team', but it doesn't work that way. you can't have the cake and leave it whole. If your universe has a hierarchy of teams now, it is true for all. The way you keep 'the old way', is by creating a team of one person.
This will resolve the evil cyclic reference that you can see in the solution that Alfonso suggested, who was just following your specs.

Companies

A Company is a legal entity which is identified by {Fill in}.
A Company hires teams of one or more Developers.
A Company owns Projects {True?}.
Developers are employed by Companies {True?}.
Teams

A Team is a logical container for developers which is identified by name.
A Team can be assigned zero or more developers.
A Team can be assigned to zero or more projects {True?}.
A Team belongs to a company {True?}.
Projects

A Project is a collection of tasks which is identified by name.
A Project may be assigned to zero or more teams.
A Project belongs to a company {True?}.

Once you fill in the gaps and clarify what you have, we can continue on the right track.

-- Follow up to OP Updating Requirements --

Developers are not identified by ID. That is a surrogate key which does not exist in the real world. We will sort out the table keys later. The question is "How do you tell developers apart in the real world". I would assume they have a unique user name, or use their Email address to log on, so that your application can identify them, right? perhaps you use SSN?
A company, team, and projects are probably identified by their name, which is what tells them apart in the real world, unique in their respective scope of course - It seems that team per company, and project per company make sense.

As for the entities, based on your reply I now believe that we have missed a few. Let me suggest something, and see if that works for you.
I think the term 'Companies' is misleading, as you don't care if it's a company or an individual. What this represents is your customers, or the project owners, which are the legal entity which you issue invoices for. Since you already mentioned the separation of roles (developer / administrator / Read only etc.), I've taken the liberty of adding a couple of more entities that exist out there, and which you probably want to consider in your model.
Let me know if this makes any sense.

Customer:       A Customer is a legal entity identified by it's tax identification number (SSN/ITIN).
                Customers employ zero or more Employees.
                Customers own zero or more Projects.

Employee:       An Employee is a Person, identified by his (SSN / User Name / Email Address).
                Employees are employed by a Customer.
                Employees can have zero or more roles in association with zero or more Teams.

Role:           A role is a description of responsibility area for an Employee.
                Roles are identified by name.
                Roles can be one of : Developer, Project Viewer, Project Manager, Adsministrator, Team Lead)
                An Employee may have zero or more roles.

Team:           A Team is a collection of zero or more Team Members.
                A Team is identified by name.
                A Team may be associated with zero or more projects.

Team Member:    A Team Member is an Employee that is associated with a Team.
                A Team may consist of Employees from one customer only.
                Team Members have a single role in a Team.

Developer:      A Developer is an Employee.
                A Developer contributes work for projects.
                A Developer can be associated with zero or more teams.

Project Viewer: A Project Viewer is an Employee.
                A Project Viewer may access Projects, but is not allowed to make changes.

Project Manager:    A Project Manager is an Employee.
                    A Project Manager manages zero or more Projects, and the Teams associated with them.

Administrator:  An administrator is an Employee.
                An administrator represents the Customer and has permissions to manage Employees, Roles, Teams, and Projects.

Team Lead:      A Team Lead is a developer.
                A Team Lead manages zero or more teams.
                A Team may have only one Team Lead.

Project:        A Project is a collection of tasks, which are to be executed by Teams.
                A Project belongs to one Customer.
                A Project is identified by name.
                A Project may be assigned to one or more Teams.


Overhead of DBMS_Profiler If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling?  I realize this will be dependent on what the script is doing, but in general am I looking at seconds, minutes, or perhaps more?

The best way to find out will be to run some known code samples with and without using the profiler. The differences normally should not be much but indeed can vary. Even dbms_application_info can hurt when put inside a high frequent loop.


Specify build and probe tables to hash join? I am observing a situation in AdventureWorks database where the hash join operator is building hash table using a larger input (296 rows) and then probing with a much smaller input (3 rows). I'm not able to understand why this reverse mapping is favoured by SQL server.

Following is the query and plan:

SELECT *
FROM HumanResources.Employee e
INNER MERGE JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
INNER HASH JOIN HumanResources.Shift s ON edh.ShiftID = s.ShiftID

The plan is here: https://www.brentozar.com/pastetheplan/?id=Bk_YvD1iX

I wonder if I can specifically instruct SQL to use HumanResources.Shift for build and the other input to probe?

When you hint a query like that, you force the join order -- you've actually answered your own question.

If you want to test join types and still leave the optimizer with room to explore things, use query options, like this:

SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
ON e.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Shift s
ON edh.ShiftID = s.ShiftID
OPTION(MERGE JOIN, HASH JOIN);


Long running VACUUM VERBSE in Postgres 9.5 - how much time remaining? I am vacuuming a very large (718GB) table containing a lot of dead tuples and 4 indexes.

VACUUM ANALYSE VERBOSE bigtable;

This vacuum has been running for over 11 hours (!) now and I am trying to estimate the remaining time for it to complete, or, at least understand in which "phase" the vacuum process is: scanning heap? cleaning indexes? vacuuming heap? I am referring to the phases described in Vacuum Progress Reporting, introduced in 9.6.

Since I am running Postgres 9.5, I do not have access to this nifty feature. I am trying to make sense of the "verbose" vacuum messages, which look like this:

INFO:  scanned index "bigtable_timestamp" to remove 11184548 row versions
DETAIL:  CPU 77.05s/336.97u sec elapsed 745.75 sec
INFO:  scanned index "bigtable_trip_id" to remove 11184548 row versions
DETAIL:  CPU 87.86s/371.96u sec elapsed 846.21 sec
INFO:  "bigtable": removed 11184548 row versions in 157984 pages
DETAIL:  CPU 3.50s/1.93u sec elapsed 22.42 sec
INFO:  scanned index "bigtable_pkey" to remove 11184526 row versions
DETAIL:  CPU 72.27s/330.03u sec elapsed 704.25 sec
INFO:  scanned index "bigtable_step_id" to remove 11184526 row versions
DETAIL:  CPU 90.21s/370.86u sec elapsed 850.30 sec

The above sequence of messages has repeated itself for the past 11 hours, with no end in sight.

Is there any way to tell, based on those messages, in which phase the vaccum is now?

I've also tried to look at the bytes-read of the vacuum process on Linux, as suggested in this answer, and compare it to the total_bytes of the table and indexes, but this didn't work for me. The process has already read 5.1TB (5101054656512) from the disk although the table including all indexes is estimated at "only" 718GB (pg_class.total_bytes).

You can find the pid of the vacuum process, then do a strace -y -p <pid> and see what part of the table it is currently reading, then compare that to the overall size of the table.  You don't want to leave strace running all the time, as it slows the process down, so see what you need then ctrl-c out of the strace.  You will have to catch it when it is reading the table, not an index, as seeing where it is at in an index is not informative.

Alternatively, if you have the entire history of that verbose output, you can see where it is by summing all of the rows and pages from INFO:  "bigtable": removed 11184548 row versions in 157984 pages and comparing that to what the size of the table and number of dead rows you believe it to have.

What you've shown here starts in the middle of one cycle and ends in the middle of the next one.  It needs to scan all the indexes each time maintenance_work_mem fills up, and since each dead tuple take about 6 bytes and the default value of maintenance_work_mem is 64MB, that closely matches what you are seeing (64MB/6 = 11184810).

I think there is one step that VACUUM VERBOSE doesn't log which it probably should.  First it scans part of the table, accumulating dead tuples until it hits the end of the table or until maintenance_work_mem is full.  It I don't think it logs the end of this step.  Then it scans each index, logging the info for each one.  Then it rescans that same part of the table, marking the dead tuples as being vacuumed away, and logs that info.

I'd interrupt the vacuum and increase maintenance_work_mem and restart. Some amount of work may be lost, but it should be small compared to the speed up you will see going forward.  Unless you conclude it is already like 99% done or something.

I'd also change maintenance_work_mem to 1GB in postgresql.conf, as well as lowering autovacuum_vacuum_cost_delay to 2ms, to try to prevent future occurrences of similar problems.

But more importantly, I'd upgrade off of 9.5 ASAP.  It is still "supported" as far as security fixes are concerned, but people with stringent performance requirements shouldn't be using a version that old.  Yes, it is common management conundrum: "Doing X, Y, and Z are all a higher priority than upgrading".  Well, upgrading will probably make X, Y, and Z easier to do.


What is the F301: the CORRESPONDING clause in query expression? The SQL 2011 Spec supports a CORRESPONDING clause,

  Feature F301, “CORRESPONDING in query expressions”:

What is this feature? How is it used? And, is it supported by any industry RDBMs?

In a nutshell, CORRESPONDING was introduced into the SQL standard to make the syntax more in the spirit of the relational model (RM).

A full answer would inevitable involve a discussion about how the SQL language has strayed from the relational model specifically (e.g. nulls) and good language design generally (e.g. data types).

In essence, before 1992 some SQL syntax such as UNION relied on left-to-right ordering of columns. For example, without nitpicking about data types and their compatibility etc, the following would not work:

SELECT col1, col2 FROM Table1 WHERE col3 = 0
UNION
SELECT col2, col1 FROM Table1 WHERE col3 = 1

because the columns do not follow the same left-to-right ordering.

Of course, in the RM a relation has a set of attributes and a set by implication has no ordering. However, the original SQL UNION syntax implies that SELECT col1, col2 FROM Table1 is somehow not the same result as SELECT col2, col1 FROM Table1.

Therefore, UNION  CORRESPONDING corrects the earlier design flaw i.e.

SELECT col1, col2 FROM Table1 WHERE col3 = 0
UNION CORRESPONDING
SELECT col2, col1 FROM Table1 WHERE col3 = 1

will give the correct result.


Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds We have a number of tables (~1M records) that have a column on them defined as: [GlobalID] [uniqueidentifier] NOT NULL that gets auto-populated with newid(). We use this ID for synchronizing data across multiple systems, databases, file imports/exports, etc.

For this column, we have the following index created:

CREATE UNIQUE NONCLUSTERED INDEX [IxUx_XXXXXX_GlobalID] ON [dbo].[XXXXXX]
(
    [GlobalID] ASC
)

As expected, this index gets fragmented pretty quickly so we have to be on the ball with keeping it rebuilt. However, this is where we have a problem. My maintenance script that goes through and rebuilds all indexes over x% fragmented with ONLINE=ON always hangs forever on these indexes. I've tried to let one run for over 3 hours without success when the database was mostly to completely idle. Just for kicks, I even put the DB into single user mode and nothing changed. I also tried with ONLINE=OFF, again with no change. However, I am able to simply drop/create the index and it completes within ~15 seconds!

Just to make sure there's nothing funny with my maintenance script, I've manually tried rebuilding these indexes with no success. The script I use is: ALTER INDEX IxUx_XXXXXX_GlobalID ON [dbo].[XXXXXX] REBUILD WITH (ONLINE=ON) and it seems like it will never succeed. Given that I can drop and recreate it in only 15 seconds, 3 hours is WAY more than plenty to see this succeed on an idle database.

For now, I've altered my maintenance script to filter out indexes with "GlobalID" in the name and then have a follow-up script that drops/creates these indexes. This gets us by until we start having naming variations on these types of columns (i.e. we need a uniqueidentifier for some other purpose).

Any idea why rebuilding such indexes would never finish within a reasonable amount of time? I see this happen across ~12 tables, all with essentially the same column/index on them.

My suggestion would be:

(a) talk to Azure support. This is not how it should be working AFAIK.

(b) when building your list of indexes to rebuild/reorganize, add a NOT EXISTS clause to the criteria to eliminate any indexes with GUIDs as the leading key column:

SELECT name, etc.
 FROM sys.indexes AS i
 INNER JOIN sys.dm_db_index_physical_stats AS s
 ON ...
 WHERE ... criteria ...
 AND NOT EXISTS
 (
   SELECT 1 FROM sys.index_columns AS ic
     INNER JOIN sys.columns AS c
     ON ic.object_id = c.object_id
     AND ic.column_id = c.column_id
     AND ic.key_ordinal = 1 -- only leading column
     AND c.system_type_id = 36 -- uniqueidentifer
     WHERE ic.index_id = i.index_id
 );

(c) before deciding to manually deal with these columns, wait until they actually cause you a performance problem that you can demonstrate and undeniably identify the fragmentation of this index as the problem.

(d) or, just use the inverse query in (b) to perform manual drop/create all the time instead of dealing with the problem over and over.


MySQL CREATE table with > 4000 fields (with large sized colums) I wish to CREATE a table with 4000+ columns but an error happens.

Traceback (most recent call last):
  File "e:/OECD-Bot/src/csvTomysql.py", line 55, in <module>
    cursor.execute(creatQueryString)
  File "C:\Program Files\Python37\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Program Files\Python37\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Program Files\Python37\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1005 (HY000): Can't create table `csv_db`.`ear_mei` (errno: 185 "Too many columns")

4096 is the limit.

Innodb has a 1017 limit.

Reconsider your design.


Is it a good approach to duplicate data in a database? I have a program that needs to store traceroutes (among other data).

Here is a diagram that depicts the business scenario:

My use case right now is to store the traceroutes using the following tables (with their corresponding columns):

packet

packet_id
source_ip
destination_ip
packet_length

traceroute

traceroute_id
packet_id
timestamp
path_id

path

path_id
traceroute_id
timestamp

ip

ip_id
ip_address

path_ip

path_id
ip_id
order_index

This creates the need to make several JOINs to get back a traceroute, but it makes the pieces of the traceroutes available for query for other needs.

I am considering having a second traceroute table that stores the path as a json string of ip_addresses. This allows me to recover full traceroutes with minimal JOINs, but still retain the individual hops (ip_addresses) used for other queries.

My questions are:

Does having this duplicated data make sense?
Is there a better approach to this?

Responses to comments:

I would add the traceroute data from one method, and would populate the tables at the same time.

The duplicated table (perhaps called traceroute2?) would just be used for retrievals, and none of the data would ever be edited or updated.

I am considering this because I am most interested in speed of data retrieval. I did some benchmarking (sort of crude) and I can get a 2-6X speed improvement on retrieval.

I don't want to prevent JOINs for a particular reason, just want to avoid this many. For this operation, I never need the individual hops of a traceroute, so I don't see what I would gain by splitting them out when I add it, and then piecing them back together again.

De-normalisation and certain kinds of data duplication can be a convenient way to speed-up processes. Examples are caching, datawarehousing, and materialised views.

Meanwhile the duplicated data is relied upon as a read-only snapshot (i.e. point-in-time copy of the actual data), or the system can guarantee consistency (like in the case of caches), it is safe to rely on this practice.


Connecting to a remote Postgres 9.1 DB I am running Postgres 9.1 on Ubuntu.
I have host all all my.ip.address/32 md5
set in pg_hba.conf and listen_addresses= '*' set in postgresql.conf.
Running show listen_addresses shows '*'
Running sudo netstat -ltnp | grep postgres shows

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      28899/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      28899/postgres

However, I sill cannot connect to the DB using pgAdmin III on my machine that is located at "my.ip.address".
What could I have missed in the set up?

Thank you.

You have to configure the following two files

pg_hba.conf

host all all 0.0.0.0/0 md5

postgresql.conf

listen_addresses='*'

Make sure to restart postgres service after changing the files.

You have to check if the port 5432 is open: http://www.yougetsignal.com/tools/open-ports/

If it's not then add a rule to your iptables:

iptables -A INPUT -s 0/0 -p tcp --dport 5432 -j ACCEPT

0/0: If you want anybody to access it.
You can change it to a specific IP address or range of IP addresses.


Are there any reasons not enable SQLCMD-mode as default in SSMS? I don't consider "just to ensure that sqlcmd-mode is not required by a script" a valid reason.

Any things to think of before giving the order to enable this setting to my coworkers?

Yes, I don't want to work in SQLCMD mode all the time
It suits you but not me

Irritating:

  Enabling SQLCMD mode turns off IntelliSense and the Transact-SQL debugger in the Database Engine Query Editor.

Dangerous?

  When executed from the command line, the sqlcmd utility permits full interaction with the operating system. When you use the Query Editor in SQLCMD Mode, you must be careful not to execute interactive statements. The Query Editor cannot respond to operating system prompts.

And some limitations of course...

  So no flow control or TSQL variable assignments with SQLCMD mode? This is a big disappointment. Not very many SQLCMD keywords are supported and no real flow control commands


Static DAC ports for named instances Using SQL 2016 Enterprise Edition

The default instance will listen on 1433 and the DAC port will listen on TCP 1434. SQL Browser will listen on UDP 1434. Any subsequent named instances will get a dynamically allocated port from windows in the range 49152 to 65535 as will their DAC ports. Connection requests (including DAC) to those named instances will make a call to SQL Browser and told the port number.

You can use the SQL Server Configuration Tool to change the port for the named instance to a static port number, but there is no option to set the DAC port to a static port number.

When I suggested to the networks team that I'd like the entire port range opened up for remote DAC connectivity, they were less than enthusiastic.

Is there any way to set the DAC port to a known, static port number for named instances?

Taken from the Microsoft Support site:

  To configure a static port for the specialized Dedicated Administrator Connection (DAC), you must update the registry key that corresponds to your instance. For example, the registry key may be the following:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

  Note The "X" in "MSSQL.X" is a number that indicates the directory where the instance is installed for SQL Server 2005 or the instance name for SQL Server 2008.

Although the article talks about SQL Server 2008, this works for all Windows-based versions from 2005 and above.  SQL Server on Linux uses a different mechanism, which I am not yet aware of.


Select All Instances of Substring in a String I have a varchar(max) column that holds a JSON request string. I need a SQL statement for a report that will select all of the instances of that substring. More specifically I need to grab the LoanId Value from that substring entry pulling a result set that contains a row for each LoanId. Any help would be greatly appreciated.

A considerably abbreviated JSON string containing the stuff I'm looking for.

[
  {"loanId":"1111111111","someotherValue":7},
  {"loanId":"2222222222","someotherValue":4},
  {"loanId":"3333333333","someotherValue":5},
]

First, create a string splitting function. For simplicity I'm going to use an XML splitter, but you can see lots of discussion about it here and in all the links that points you to.

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       nvarchar(max),
   @Delimiter  nvarchar(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
   RETURN ( SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM ( SELECT x = CONVERT(xml, ''
          + REPLACE(@List, @Delimiter, '')
          + '').query('.')) AS a
          CROSS APPLY x.nodes('i') AS y(i));
GO

With the function in place, you can now use it to chop apart this JSON string:

DECLARE @s varchar(max) = '[
  {"loanId":"1111111111","someotherValue":7},
  {"loanId":"2222222222","someotherValue":4},
  {"loanId":"3333333333","someotherValue":5},
]';

SELECT LoanID = PARSENAME(s2.Item,1)
FROM dbo.SplitStrings_XML(@s, ',') AS s
CROSS APPLY dbo.SplitStrings_XML(s.Item, ':') AS s2
WHERE s.Item LIKE '%"loanId":"%'
AND s2.Item NOT LIKE '%"loanId"%';

Results:

LoanID
----------
1111111111
2222222222
3333333333