How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types We are trying to figure out root cause of slow running sql server queries hitting/fetching data from one of the database , size 300 GB, hosted on server with below configuration:

  Windows server 2003 R2, SP2, Enterprise Edition, 16 GB RAM , 12  CPU'S
  32 Bit

  SQL server 2005, SP4, Enterprise Edition, 32 Bit.

We have already informed business on the upgrade to 64 bit which would take over a month.

But for the current issue, we are trying to gather the data if we can resolve the memory pressure or finally come to a conclusion to increase RAM.

Action Completed: Re-indexing and update stats are proper for this DB.

As shown below, we have been noticing the semaphore waittype for past 5 days, ran during the load hours:

Few info after below  queries: size of buffer= 137272

SELECT SUM(virtual_memory_committed_kb)
FROM sys.dm_os_memory_clerks
WHERE type='MEMORYCLERK_SQLBUFFERPOOL'

and semaphore memory= 644024 per below query

 SELECT SUM(total_memory_kb)
FROM sys.dm_exec_query_resource_semaphores

Below is some more info gathered from dm_exec_query_resource_semaphores and sys.dm_exec_query_memory_grants dmv's

So from above info gathered and per SP_Blitz data Resource semaphore seems to be the problem.

Is memory 'target_memory_kb' assigned for resource semaphore id's too low, as compared to 16 GB RAM available.

Note* per analysis on 8 hours run 'target_memory_kb' is always under 1 GB, compared to 16 GB available?

what could be the issue here and how to resolve, please suggest

Thanks

Oh, goodness, I have some bad news here.

On a 32-bit OS, SQL Server only uses the first 4GB of memory for things like query workspace. (And it's fighting the OS for that 4GB, too - any other running apps will also compete for that memory.)

4GB might sound like a lot, but it's relatively easy to write a query that needs several GB of memory in order to run. When enough queries demand enough memory, SQL Server throws RESOURCE_SEMAPHORE waits because queries can't get enough memory in order to start. RESOURCE_SEMAPHORE_QUERY_COMPILE means they can't even get enough memory to compile an execution plan - and yeah, that's pretty bad.

So how do you fix it?

Switch to a 64-bit OS (the OS you're running is long out of support anyway)
Switch to a 64-bit build of SQL Server
Reduce memory demands on the server (don't run any other apps on this box, and that's especially critical on 32-bit boxes since we're capped at just 4GB)
Use more memory with the AWE/PAE switches - except that doesn't work for RESOURCE_SEMAPHORE waits because SQL Server can only use that first 4GB for query workspace
Tune queries & indexes so they need less memory

I hesitate to even say that last one, because the 32-bit issue is so bad, and it's really hard on the older versions of SQL Server. If you were on a current one, you could go through the plan cache and sort queries by memory grant, find the biggest grant recipients, and tune those. Not an option on this old antique, though.


Reduce Size of SYSAUX I am trying to reduce the size of our SYSAUX Table.  Currently the largest four occupants are below.  As you can see it is mostly the Server Manageability items.

select occupant_desc, space_usage_kbytes
  from v$sysaux_occupants
  where space_usage_kbytes > 0 order by space_usage_kbytes desc
/

OCCUPANT_DESC                                       SPACE_USAGE_KBYTES
Server Manageability - Advisor Framework                4613440
Server Manageability - Optimizer Statistics History     2522944
Server Manageability - Automatic Workload Repository    1457280
Enterprise Manager Repository                       172480

I have reduced retention to 7 days

select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                          7

I have purged statistics older than 7 days:

exec DBMS_STATS.PURGE_STATS(SYSDATE-7);
PL/SQL procedure successfully completed.

All this but no luck reducing space in the tablespace.  I also tried following the instructions in Purging statistics from the SYSAUX tablespace   but the space is still full.

Any suggestion?

Try moving some objects into another tablespace.
Documentation on this is quite confusing, referring to an uncertain pl/sql procedure, that should be used. Though, there is no mention what procedure it is. I would use alter table ... move ...

Edit: (thanks to @Jack Douglas):
Instead of alter, there is a move_procedure defined in V$SYSAUX_OCCUPANTS that should be used to move those occupants.


Access 2000 database rapid growth We have an Access 2000 format database running in Office Access 2003.

We have a problem with the size when I merge the database; the size is about 250 MB.  Two days later, the size has ballooned to about 750 MB.

How can I see where this increase in size comes from?  Can I look behind the scenes in Access?

The database is running for a tennis club and our employees are working all day long with the database.  Another programmer made the database and says it is common to see Access databases increase in size.

What can I do to alleviate this issue?

All you have to do is the following:

Across the Top of the Access Window, Click the File Tab
Click the Compact and Repair Button

I would not worry about growth because data insertion into MDB usually appends changes to back of the file. Garbage collection and space reusage is not a strong suit for MSAccess. To make matters worse, there is filesize limit (I think it is 2GB) on MDB files. This is why some migrate to SQL Server Express where SQL Server can be configured to perform automatic Compacting and Repairing.


Oracle 12c create manual script for automatic startup on RHEL7 I need to create a script to start and shut down a 12c database in rhel7 automatically when the operating system starts and shuts down, it turns out that I have tried the common options with dbstart and dbshut, but it only works for the listener, it does nothing with the database instance ... recently I did an migration of asm to the operating system's filesystem for that reason I disabled the start of asm since I didn't need it, I don't know if this would be influencing the dbstart and dbshut scripts Don't start, I have the oratab file like this:
myoradb:/u01/app/oracle/db/12.1:Y
The truth is that the content of these dbstart and dbshut seems a bit dense and I do not see where the problem could be so that they do not run correctly with the database instance.
Is there an example of how to create these scripts manually without using dbstart and dbshut ???
Thanks

You need to create a system service pointing to start and shutdown shell scripts.

See Automating Database Startup and Shutdown on Linux on Oracle Base.

Another option is to download an Oracle Developer VM and explore how auto start was setup along with a system service (one of the options from the link) – Kumar


How can I dynamically alias columns? I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change.

The possible column names are stored in another table, that I can query very easily.

Therefore, the query I'm really looking for goes something like this:

SELECT Col1 AS (SELECT ColName FROM Names WHERE ColNum = 1 and Type = @Type),
       Col2 AS (SELECT ColName FROM Names WHERE ColNum = 2 and Type = @Type)
FROM   Tbl1
WHERE  Type = @Type

Obviously that doesn't work, so how can I get a similar result?

'
I've tried building a query string and EXECUTEing it, but that just returns "Command(s) Completed Successfully" and doesn't seem to return a rowset.

It turns out I was using an incorrect query to build the dynamic SQL and as such built an empty string. SQL Server definitely executed the empty string correctly.

Note that the reason I need this to occur, rather than simply hard coding the column names, is that the column names are user configurable.

Try the following code:

CREATE TABLE #Names
(
    [Type] VARCHAR(50),
    ColNum SMALLINT,
    ColName VARCHAR(50),
    ColDataType VARCHAR(20)
)

INSERT  INTO #Names VALUES
('Customer', 1, 'CustomerID', 'INT'),
('Customer', 2, 'CustomerName', 'VARCHAR(50)'),
('Customer', 3, 'CustomerJoinDate', 'DATE'),
('Customer', 4, 'CustomerBirthDate', 'DATE'),
('Account', 1, 'AccountID', 'INT'),
('Account', 2, 'AccountName', 'VARCHAR(50)'),
('Account', 3, 'AccountOpenDate', 'DATE'),
('CustomerAccount', 1, 'CustomerID', 'INT'),
('CustomerAccount', 2, 'AccountID', 'INT'),
('CustomerAccount', 3, 'RelationshipSequence', 'TINYINT')

CREATE TABLE #Data
(
    [Type] VARCHAR(50),
    Col1 VARCHAR(50),
    Col2 VARCHAR(50),
    Col3 VARCHAR(50),
    Col4 VARCHAR(50),
    Col5 VARCHAR(50),
    Col6 VARCHAR(50),
    Col7 VARCHAR(50)
)

INSERT  INTO #Data VALUES
('Customer', '1', 'Mr John Smith', '2005-05-20', '1980-11-15', NULL, NULL, NULL),
('Customer', '2', 'Mrs Hayley Jones', '2009-10-10', '1973-04-03', NULL, NULL, NULL),
('Customer', '3', 'ACME Manufacturing Ltd', '2012-12-01', NULL, NULL, NULL, NULL),
('Customer', '4', 'Mr Michael Crocker', '2014-01-13', '1957-01-23', NULL, NULL, NULL),
('Account', '1', 'Smith-Jones Cheque Acct', '2005-05-25', NULL, NULL, NULL, NULL),
('Account', '2', 'ACME Business Acct', '2012-12-01', NULL, NULL, NULL, NULL),
('Account', '3', 'ACME Social Club', '2013-02-10', NULL, NULL, NULL, NULL),
('Account', '4', 'Crocker Tipping Fund', '2014-01-14', NULL, NULL, NULL, NULL),
('CustomerAccount', '1', '1', '1', NULL, NULL, NULL, NULL),
('CustomerAccount', '2', '1', '2', NULL, NULL, NULL, NULL),
('CustomerAccount', '2', '3', '2', NULL, NULL, NULL, NULL),
('CustomerAccount', '3', '2', '1', NULL, NULL, NULL, NULL),
('CustomerAccount', '3', '3', '1', NULL, NULL, NULL, NULL),
('CustomerAccount', '4', '2', '2', NULL, NULL, NULL, NULL),
('CustomerAccount', '4', '4', '1', NULL, NULL, NULL, NULL)

DECLARE @Type VARCHAR(50) = 'Account' -- Or Customer, or CustomerAccount

DECLARE @SQLText NVARCHAR(MAX) = ''

SELECT  @SQLText += 'SELECT '

SELECT  @SQLText += ( -- Add in column list, with dynamic column names.
                SELECT  'CONVERT(' + ColDataType + ', Col' + CONVERT(VARCHAR, ColNum) + ') AS [' + ColName + '],'
                FROM    #Names
                WHERE   [Type] = @Type FOR XML PATH('')
            )

SELECT  @SQLText = LEFT(@SQLText, LEN(@SQLText) - 1) + ' ' -- Remove trailing comma

SELECT  @SQLText += 'FROM #Data WHERE [Type] = ''' + @Type + ''''

PRINT   @SQLText
EXEC    sp_executesql @SQLText

This returns the SELECT statement: SELECT CONVERT(INT, Col1) AS [AccountID],CONVERT(VARCHAR(50), Col2) AS [AccountName],CONVERT(DATE, Col3) AS [AccountOpenDate] FROM #Data WHERE [Type] = 'Account'


Improve querying of Extended Events target file As part of our server estate monitoring, I am adding extended events to pick up warnings, blocking etc and I'd like to periodically (Every couple of minutes) query the event file to collect the data. I have been using the below to query data using xQuery, but it seems to be quite slow. I am aware of using a fileoffset as an option to optimise, but beyond that, are there any ways in which I can better improve predicates as been below?

    SELECT
         event_data
        ,n.value('@timestamp', 'DATETIME2') DtTimeStamp
        ,n.value('(action[@name="collect_cpu_cycle_time"]/value)[1]', 'bigINT') CollectCpuCycleTime
        ,n.value('(action[@name="collect_system_time"]/value)[1]', 'DATETIME2') CollectSystemTime
        ,n.value('(action[@name="last_error"]/value)[1]', 'varchar(255)') LastError
        ,n.value('(action[@name="collect_system_time"]/value)[1]', 'datetime2')             CollectSystemTime
        ,n.value('(action[@name="task_time"]/value)[1]', 'bigint')                          TaskTime
        ,n.value('(action[@name="client_app_name"]/value)[1]', 'varchar(255)')              ClientAppName
        ,n.value('(action[@name="client_hostname"]/value)[1]', 'varchar(255)')              ClientHostName
        ,n.value('(action[@name="database_name"]/value)[1]', 'varchar(255)')                DatabaseName
        ,n.value('(action[@name="nt_username"]/value)[1]', 'varchar(255)')                  NtUserName
        ,n.value('(action[@name="server_instance_name"]/value)[1]', 'varchar(255)')         InstanceName
        ,n.value('(action[@name="session_id"]/value)[1]', 'INT')                            SessionID
        ,n.value('(action[@name="client_pid"]/value)[1]', 'INT')                            ClientPID
        ,n.value('(action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)')                         SQLText
    FROM
        (
            SELECT
                CAST(event_data as XML) event_data
            FROM
                sys.fn_xe_file_target_read_file('C:\Temp\EE_QueryWarnings*.xel', null, null, null)
        ) ed
    OUTER APPLY
        ed.event_data.nodes('event') (n)
    WHERE
        n.value('@name', 'varchar(MAX)')    = 'missing_column_statistics'
    AND
        n.value('@timestamp', 'DATETIME2')   >= DATEADD(MINUTE,-10,GETUTCDATE());

Another option is Microsoft.SqlServer.XEvent.Linq instead of T-SQL to process XE data in Powershell or .NET application. The QueryableXEventData class can process XE data directly from target files or from the live event stream.

The live steam source is generally appropriate for events that occur relatively infrequently (as in your monitoring use case) whereas the file source most useful for high-frequency event use cases (e.g. capturing rpc and batch completed events for tracing).

Assuming your goal is inserting events into a table for forensics, below is a PowerShell example and target table DDL. One can use SqlBulkCopy instead of trickle inserts for high-frequency events.

Table:

CREATE TABLE dbo.missing_column_statistics (
      Timestamp datetimeoffset
    , Name varchar(100)
    , collect_cpu_cycle_time decimal(20, 0)
    , collect_system_time datetimeoffset
    , last_error int
    , task_time decimal(20, 0)
    , client_app_name varchar(100)
    , client_hostname varchar(100)
    , database_name sysname
    , nt_username varchar(100)
    , server_instance_name varchar(100)
    , session_id int
    , client_pid int
    , sql_text nvarchar(MAX)
);
CREATE CLUSTERED INDEX cdx ON dbo.missing_column_statistics(Timestamp);

PowerShell script:

# class with actions/fields of interest
class MissingColumnStatisticsEvent {

    [DateTimeOffset]$Timestamp
    [String]$Name
    [UInt64]$collect_cpu_cycle_time
    [DateTimeOffset]$collect_system_time
    [UInt32]$last_error
    [UInt64]$task_time
    [String]$client_app_name
    [String]$client_hostname
    [String]$database_name
    [String]$nt_username
    [String]$server_instance_name
    [UInt16]$session_id
    [UInt16]$client_pid
    [String]$sql_text

    MissingColumnStatisticsEvent($event) {
        $this.Name = $event.Name
        $this.Timestamp = $event.Timestamp
        $this.collect_cpu_cycle_time = $event.Actions["collect_cpu_cycle_time"].Value
        $this.collect_system_time = $event.Actions["collect_system_time"].Value
        $this.last_error = $event.Actions["last_error"].Value
        $this.task_time = $event.Actions["task_time"].Value
        $this.client_app_name = $event.Actions["client_app_name"].Value
        $this.client_hostname = $event.Actions["client_hostname"].Value
        $this.database_name = $event.Actions["database_name"].Value
        $this.nt_username = $event.Actions["nt_username"].Value
        $this.server_instance_name = $event.Actions["server_instance_name"].Value
        $this.session_id = $event.Actions["session_id"].Value
        $this.client_pid = $event.Actions["client_pid"].Value
        $this.sql_text = $event.Actions["sql_text"].Value
    }

}

# insert event data into target table
Function Insert-MissingColumnStatisticsEvent($event) {
    $insertQuery = @"
INSERT INTO dbo.missing_column_statistics (
      Timestamp
    , Name
    , collect_cpu_cycle_time
    , collect_system_time
    , last_error
    , task_time
    , client_app_name
    , client_hostname
    , database_name
    , nt_username
    , server_instance_name
    , session_id
    , client_pid
    , sql_text
)
VALUES (
      @Timestamp
    , @Name
    , @collect_cpu_cycle_time
    , @collect_system_time
    , @last_error
    , @task_time
    , @client_app_name
    , @client_hostname
    , @database_name
    , @nt_username
    , @server_instance_name
    , @session_id
    , @client_pid
    , @sql_text
);
"@

    $connection = New-Object System.Data.SqlClient.SqlConnection($targetDatabaseConnectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($insertQuery, $connection)
    ($command.Parameters.Add("@Timestamp", [System.Data.SqlDbType]::DateTimeOffset)).Value = $event.Timestamp
    ($command.Parameters.Add("@Name", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.Name
    ($command.Parameters.Add("@collect_cpu_cycle_time", [System.Data.SqlDbType]::Decimal, 20, 0)).Value = $event.collect_cpu_cycle_time
    ($command.Parameters.Add("@collect_system_time", [System.Data.SqlDbType]::DateTimeOffset)).Value = $event.collect_system_time
    ($command.Parameters.Add("@last_error", [System.Data.SqlDbType]::Int)).Value = $event.last_error
    ($command.Parameters.Add("@task_time", [System.Data.SqlDbType]::Decimal, 20, 0)).Value = $event.task_time
    ($command.Parameters.Add("@client_app_name", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.client_app_name
    ($command.Parameters.Add("@client_hostname", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.client_hostname
    ($command.Parameters.Add("@database_name", [System.Data.SqlDbType]::NVarChar, 128)).Value = $event.database_name
    ($command.Parameters.Add("@nt_username", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.nt_username
    ($command.Parameters.Add("@server_instance_name", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.server_instance_name
    ($command.Parameters.Add("@session_id", [System.Data.SqlDbType]::Int)).Value = $event.session_id
    ($command.Parameters.Add("@client_pid", [System.Data.SqlDbType]::Int)).Value = $event.client_pid
    ($command.Parameters.Add("@sql_text", [System.Data.SqlDbType]::NVarChar, -1)).Value = $event.sql_text
    $connection.Open()
    [void]$command.ExecuteNonQuery()
    $connection.Close()
}

# ############
# ### MAIN ###
# ############
try {

    # load assemblies needed for QueryableXEventData
    $sharedPath = "C:\Program Files\Microsoft SQL Server\150\Shared" # path varies by SQL tool version
    $xeCore = [System.IO.Path]::Combine($sharedPath, "Microsoft.SqlServer.XE.Core.dll")
    $xeLinq = [System.IO.Path]::Combine($sharedPath, "Microsoft.SqlServer.XEvent.Linq.dll")
    Add-Type -Path $xeLinq
    Add-Type -Path $xeCore

    $xeSessionConnectionString = "Data Source=.;Initial Catalog=master;Integrated Security=SSPI;Application Name=XE ETL" # run in master database context for live stream
    $targetDatabaseConnectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI;Application Name=XE ETL"
    $sessionName = "EE_QueryWarnings"

    # get events from file target
    # $events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("DC:\Temp\$sessionName*.xel")

    # get events from live session
    $events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData(
       $xeSessionConnectionString,
       $sessionName,
       [Microsoft.SqlServer.XEvent.Linq.EventStreamSourceOptions]::EventStream,
       [Microsoft.SqlServer.XEvent.Linq.EventStreamCacheOptions]::DoNotCache)

    # Stream source will consume continuously until this script or XE session is stopped.
    # File source will read all XE files and exit.
    foreach($event in $events) {
        if($event.Name -eq "missing_column_statistics") {
            $missingColumnStatisticsEvent = New-Object MissingColumnStatisticsEvent($event)
            Insert-MissingColumnStatisticsEvent -event $missingColumnStatisticsEvent
        }
    }
}
catch {

    throw

}


Database state set to an earlier state before creating new indexes This is a remote server (SQL Server 2008).

We are trying to get benchmarking of some stored procedures. One stored procedure was taking avrg 44 seconds, we have checked this approx 20 times for a span of two weeks avrg was 44 seconds.

SQL Profiler suggested a missing index, we created the suggested index for checking the performance. The execution time reduced significantly after indexing.

We dropped the newly created index. Rebuild index was not performed, as we assumed drop index should rebuild the index for the table.

Client is also testing on same db. They also felt a performance gain in this particular operation. They are asking the cause for this gain, we have conveyed the above process, but index benefit can also be verified  with older DB state, therefor we need to reset the data base to earlier state where it was taking 44 seconds.

Database Engine can not be restarted and Offline/Online options can also not be performed as "Alter Any Database" permission can not be granted.

We are looking on below command.

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

Permissions
Requires ALTER SERVER STATE permission on the server.

USE <<db Name>>
GO
GRANT ALTER TO your_user
GO

Need help to understand the possible cause behind this behaviour?

Does DROPCLEANBUFFERS also clear the index cache?

When you drop the index, the database is in the same state as before in terms of indexing. If you want to go back to a previous state then you'll need to get a backup for that time range. Depending on the requirements it's either just the FULL Backup or additional DIFFERENTIAL Backups or even a point-in-time recovery using the transaction logs.

But the question is: Why do you want to do it in the first place?

The benefit of any index should not be explained by runnig it against an older data set just to see that it would have been better in the past, too, but by explaining the effect itself. Either a Scan has changed into a Seek operation and/or the new index is more narrow than the one previously used and/or a bookmark lookup has been eliminated and/or the index allows for a better performing query plan.

Also, verify that the statistics are up to date before comparing old and new version to eliminate the side effect of a better performance due to better statistics.

The index comparison should be done using the same procedure against the same set of data (e.g. current data) and compare the actual execution plans.


MySQL master-slave replication over an unstable internet link Connection from our main office to couple remote sites are pretty unstable, if I want to do a slave replication to those remote sites, I might need to increase multiple timeout values, anything else I need to aware? Also, does replication are atomic? what if connection breaks in the middle of getting data? does everything will retransfer from master to slave or slave is unaware of broken pieces?

Increase some of these:  net_retry_count, slave_transaction_retries, slave_net_timeout.
Monitor the slaves to catch them in the case of a really long network break.  Then do SLAVE STOP; SLAVE START;
Make sure expire_logs_days is long enough so that you won't lose binlogs.  There is no "too big", except that you could eventually run out of disk space.

Everything should work find after the Slave gets going again.  (There will be a delay as it "catches up".)


Promoting MySQL slave with unknown replication delay Consider I have a master on DC1 and slave on DC2. Now, DC1 network is completely down for indefinite time and I want to resume the service as soon as possible on DC2 by promoting the slave to master.

There are two problems:

I don't know how much the slave is lag behind the master
If original master went back online, how to solve the conflict?

Are there any recommended steps and procedures?

Master + Slave?  Not Dual Master?

With Dual Master, each server is the Master and Slave to the other.  In this case either can receive writes, but you should write to only one.  More pertinent, failover and failback is readily available.

With Master + Slave, you have not prepared for the failback because things like log_bin and log_slave_updates are not turned, nor is the Master listening to the Slave.

I'm afraid your only option (now) is to stop the Slave (which you promoted to Master), copy the data, build a Slave to it (presumably on the original Master's server).  That is, you lost the Master, and whatever data is on it is useless.  It's like initially adding a Slave to a running system.


dynamically creating query in a loop I"m looping through a table trying to create a dynamic sql query. It appears to work fine except when I'm concatenating results to make the actual statement. Psudo code follows

WHILE (Data is in the table)
BEGIN
SELECT TOP 1 @result = result from table

@sql = @sql + ', ' + @result

END

If I look at @sql in each loop I see the expected results, but when the loop is finished @sql is empty. Can someone explain why?

Assuming your pseudo-code is a good approximation of what your actual code is doing, you'd need something like:

DECLARE @sql NVARCHAR(MAX);
SET @sql = '';
WHILE (Data is in the table)
BEGIN
    SELECT TOP 1 @result = result from table;

    @sql = @sql + ', ' + @result;

END

If you don't initialize @sql to a zero-length string prior to the loop, SQL Server will attempt to append @result to a NULL string, which results in a NULL string, hence the @sql at the end of the loop will appear empty.


RDS or Object DB for 99.99% static data of 25 million rows in one table I have important data that consists of approx 25 million rows and will grow year on year. This dates back to 1995 and is split into yearly CSV files.

Example:  1995.csv, 1996.csv and so on up to 2016.csv.

The data is of UK addresses with the Postcode being the primary identifier.

Let's say I perform a search on SW19 2EA.

I need the database to query the data and return the result set that is available for that given postcode. This will return data that contains information such as the address, postcode, property type and so on.

The problem I have at the moment is querying 25 million rows in a single table is fairly heavy and has a hefty delay. That being said, I'm only just learning advanced SQL and my configuration is probably not up to scratch.

We have 2x 128 GB RAM servers both running dual Xeon x5's on 10 Gbit dedicated connections. We assume the severs are of no issue whatsoever, and therefore are unsure what the best solution would be.

Do we store all the databases into a single table and do some research on the best configurations or do we store them in an object database?

The data will not change apart from new data being added in 2017 that is for the 2017 year. All other years are static and are never changed.

My initial thought was to store each data set in its own table according to each CSV file, so, for example, 1 table for 1995 data, 1 table for 1996 data and so on. The question is, whenever a search query is performed, is it possible to have multiple workers query each table at the same time, so that, let's say, 10 tables are being queried concurrently as opposed to one giant 25-million-row table?

Could you use the same use case on an object database? I'm also curious if it's possible to cache the entire database, it being static data. On the other hand, would this significantly reduce the query delay of around 20 seconds that I currently experience on the single 25-million-row table?

Example of CSV data structure:

"3E0330EF-67CA-8D89-E050-A8C062052140","112000","2006-05-22 00:00","MK13 7QS","F","N","L","HOME RIDINGS HOUSE","13","FLINTERGILL COURT","HEELANDS","MILTON KEYNES","MILTON KEYNES","MILTON KEYNES","A","A"

I have been using Laravel with MariaDB backend and been importing the CSV files into MariaDB. Then I have been simply using a query like this:

Select * from history where `postcode` = 'MK13 7QS';

and in Laravel terms:

$postcode = 'MK13 7QS';
History::where('postcode', '=', $postcode)->get();

Database Layout is not perfectly set up and the data types can certainly be tweaked to make them more ideal for the dataset.

Currently I have just used varchar on every column apart from the date column, as I have only been playing about with things to try and get a more suitable setup and configuration working for production.

I'm hoping to get some much appreciated advice from a newcomer point of view.

You will keep the data 'forever', correct?  That is, there is no 'purging'.

Is there potentially a different entry for each year?  Or multiple entries for each year?

Why not have a single, non-partitioned, InnoDB, table with the PRIMARY KEY starting with the postcode.  Perhaps you want the address and DATETIME as other columns in the PRIMARY KEY.

PRIMARY KEY(postcode, address, as_of)

That would let you get an ordered history of who lived at $pc, $addr:

SELECT * FROM tbl
    WHERE postcode = $pc
      AND address = $addr
    ORDER BY as_of;

It would be very fast (much less than 1 second), regardless of hardware.  (With ENGINE=CSV, you are looking at minutes or hours for any query.)

If you envision other queries, let's see them.


Something is totally wrong with my query. .. Slow. .. Why? i have query with multiple JOINS and SUBQUERYS and it seems i have messed something up here. ..
It did worked when there was low data (data is not even that much now too) but now it is not even finishing query (even in MySQL workbench)
I know how to write simple join query but when it has to load data i mess up :(
SELECT l.id               AS lid,
       l.docNr            AS receive_docNr,
       l.thisTransport    AS receive_transport,
       l.productNr        AS receive_productNr,
       l.activityDate     AS receive_date,
       h.ladingNr         AS receive_ladingNr,
       l.place_count      AS receive_place_count,
       h.cargo_status     AS receive_cargo_status,
       h.clientCode       AS clientCode,
       e.id               AS eid,
       e.docNr            AS docNr,
       e.deliverydate     AS deliveryDate,
       e.activitydate     AS activityDate,
       e.assistant_amount AS assistant_amount,
       e.amount           AS amount,
       e.productnr        AS productNr,
       e.productumo       AS productUmo,
       e.status           AS status,
       e.orgline,
       ni.name1,
       ni.name2,
       h.agreements       AS agreements,
       nc.Name,
       a.uom              AS uom,
       au.uom             AS auuom,
       (select z.status from        item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as istatus,
       (select z.place_count from   item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as iplace_count,
       (select z.activityDate from  item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as iactivityDate,
       (select z.place_count from   item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as iassistant_amount

FROM cargo_line_receive AS l

    LEFT JOIN item_ledger_entry AS e
    ON e.cargoline = l.id
    OR e.orgline = l.id

    LEFT JOIN cargo_header_receive AS h
    ON l.docNr = h.docNr

    LEFT JOIN agreements_lines AS a
    ON h.agreements = a.contractnr
    AND e.productnr = a.item
    AND e.resource = a.service

    LEFT JOIN additional_uom AS au
    ON e.productnr = au.productnr
    AND au.status = 1
    AND au.convert_from = 1

    LEFT JOIN n_customers AS nc
    ON h.clientCode = nc.Code

    LEFT JOIN n_items AS ni
    ON l.productNr = ni.code

WHERE  h.clientCode = '999999999999'
AND h.agreements = 'LIG00067'

GROUP  BY l.id
ORDER  BY l.activityDate ASC, l.docNr

EXPLAIN QUERY:

I did not know what i can remove from query so i left all.
If i removed subquerys from select nothing changed (so for me it seems to be some join)
Can anyone tell me what indexses should have here for each table?
EDIT: I have added indexes but nothing changed :(
ALTER TABLE cargo_header_receive ADD INDEX aaa (docNr, agreements, clientCode);
ALTER TABLE item_ledger_entry ADD INDEX bbb (cargoline, orgline, productNr, resource);
ALTER TABLE cargo_line_receive ADD INDEX ccc (docNr, productNr, activityDate);
ALTER TABLE agreements_lines ADD INDEX ddd (contractnr, item, service);
ALTER TABLE additional_uom ADD INDEX eee (productnr, status, convert_from);
ALTER TABLE n_customers ADD INDEX fff (Code);
ALTER TABLE n_items ADD INDEX ggg (code);

That query seems to fetch 4 columns from one particular row of one table:
select ((four columns))
    from        item_ledger_entry as z
        where  ( z.orgLine = l.id OR z.cargoLine = l.id )
          AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1

Correct?  If so, I suggest:
SELECT ....
    FROM ( SELECT a,b,c,d
               FROM item_ledger_entry z
               WHERE (   z.orgLine = l.id
                      OR z.cargoLine = l.id )
                 AND e.docNr = l.docNr
               ORDER  BY z.id DESC LIMIT  1
         ) AS z2
    JOIN FROM cargo_line_receive AS l
                     ON ( z2.orgLine = l.id
                      OR  z2.cargoLine = l.id )

    LEFT JOIN ...

That will help performance some.
The OR hurt performance; you use it often.  Is there some way to avoid it?  I hesitate to mention using UNION because I am not sure the transformation will work easily.
WHERE  h.clientCode = '999999999999'
AND h.agreements = 'LIG00067'

begs for h to have INDEX(clientCode, agreements).  (The order of those columns does not matter.)
GROUP  BY l.id

worries me.  It sounds like the "explode-implode" scenario.  Note that (at least logically), first all the JOINs are done.  This produces (potentially) a much larger table that any of the original ones.  Then the GROUP BY implodes down to the original size.  The typical remedy is to get the l.id values you ultimately need, then do the JOINs.
The high "Rows" for e confirms that OR is a big part of the performance issue.  It is doing a full table scan -- probably repeatedly -- because of
ON e.cargoline = l.id
OR e.orgline = l.id

But, before I address that, Please justify or get rid of LEFT.  Keep in mind that JOIN matches up rows from the two tables, whereas LEFT JOIN also takes any missing rows from the 'right' table, supplying NULLs to indicate that the row is missing.
If JOIN is warranted, then this might 'save the day':
JOIN  ( ( SELECT  FROM item_ledger_entry
            WHERE  e.cargoline = l.id )
        UNION DISTINCT
        ( SELECT  FROM item_ledger_entry
            WHERE  e.orgline = l.id )
      ) AS e

instead of
LEFT JOIN item_ledger_entry AS e
ON e.cargoline = l.id
OR e.orgline = l.id

Note, e will need two separate indexes:  INDEX(cargoline), INDEX(orgline)
(I'm sorry, but your query is so convoluted that each of my suggestions may fail for some reason.  You can add the indexes safely; at worst, they might not 'help'.)


Changing Fast Recovery Area destination My database is running in archive log mode, but flashback is disabled because I do not need it.

The FRA is set to /flasharch mount point.

Now, I am going to change the FRA location to /flasharch01 mount point due to space limitation.

What to do with archivelog in the old location. Do I need to move the old files to new location?

Could you guys please guide me step by step?

RMAN uses control file instead of recovery catalog.

Should I create a recovery catalog? if yes.. please how? I need steps please.

As, I can't increase the FRA location. The only way to change the location is the solution. Could you please share the steps how to move the old files and how to catalog them? RMAN using control file instead of recovery catalog.

My problem is solved. I have changed the FRA to new location.

alter system set db_recovery_file_dest='/flasharch01';
alter system set db_recovery_file_dest_size=750G;

Copy all archive log file which is generated in old and copy to new location.
using OS level copy commond.

RMAN> catalog db_recovery_file_dest;

Or

RMAN> catalog recovery area;

Or

RMAN> catalog start with 'New location Path';
RMAN> catalog start with 'New location Path';

create PFILE='/flasharch/file_raiddb.ora' from spfile;

RMAN> crosscheck copy;
RMAN> crosscheck backup;
RMAN> delete expired copy;
RMAN> delete expired backup;

alter system set log_archive_dest_1='location=/flasharch01';
create spfile from pfile=''/flasharch/file_raiddb.ora''

CREATE SPFILE='/flasharch/file_raiddb.ora'
FROM PFILE='/flasharch/file_raiddb.ora';


Copy DBF files from production to development environment I'm not an expert. I'm a Software Developer but got trapped in doing this DBA work.

We have a development environment and we would like to copy the DBF files from the production environment to the development environment to get the latest version of the production database.

I'm wondering if this will work?

Do you have an existing database on the development env. and you want to refresh it or is it going to be a new database?  And if this is a new database, do you have the Oracle software installed on the server?

If the database already exists, then the easiest way to refresh is to export the required schema(s) from the production database and import into the dev. database.

I'm providing a very crude example using datapump, but you should follow and read the links @a_horse_with_no_name provided in the comments above:

Export:

expdp system DIRECTORY=your_db_dir DUMPFILE=your_out_file.dmp LOGFILE=your_log_file.log SCHEMAS=schema_a,schema_b CONSISTENT=Y COMPRESSION=ALL

Import (you might need to drop the schemas first or you might need to add the TABLE_EXISTS_ACTION parameter):

impdp system DIRECTORY=your_db_dir DUMPFILE=your_out_file.dmp LOGFILE=your_log_file.log FULL=y

If however you want to refresh the database by copying the DBF files, you have a lot more things to take into consideration.  The very high level steps would be:

Create pfile from spifile from Prod database
Shutdown Production and Development database
Copy DBFs, control files and pfile from primary to development
Do any necessary changes in the pfile to reflect dev. env.
Mount the dev. database using pfile
Change DBF directory names if different from Prod using 'alter database rename file'
Recover database if needed
Open the database and create spfile

As you can see, this also involves downtime on your production env. and is much more complex.  I would recommend using this approach only if you know what you are doing or with an experienced DBA.  But it would be a good learning experience if you could try this yourself on a separate dev. env. (and if you have enough time and patience to experiment).


How will the order of operations in this query affect my results? I've written a query, and I'm kinda sure that it does what it want.  I'm thinking, "huh, I wonder if I know how Postgres works?".  I'm going to list a specific assumption about this query below, and if that is wrong, please correct my understanding.

Assumption: The OVER (PARTITION BY) will execute against the data after the final GROUP BY and ORDER BY are executed, thereby limiting the records that are summed to those that are indeed DISTINCT ON (a.id, scl.new_status_id).

Note: This is a Python string that will be formatted into a proper SQL query using a Python library, hence the %s format characters.  If that causes confusion for you, I will update it.  Please assume that I am passing in arguments that make a valid SQL query.

SELECT DISTINCT ON (a.id, scl.new_status_id)
    scl.new_status_id
    ,(scl.change_date AT TIME ZONE %s)::date as d
    ,SUM(COUNT(*)) OVER (PARTITION BY scl.new_status_id
                         order by (scl.change_date AT TIME ZONE %s)::date)

FROM automated_responses_statuschangelogs scl
JOIN application_app a ON scl.applicant_id = a.id
WHERE a.applied_class in %s
AND a.applied_track in %s
AND a.applied_date <= %s
AND a.applied_date > %s
AND scl.new_status_id IN %s
AND scl.new_status_id NOT IN %s

GROUP BY a.id, scl.new_status_id, d
ORDER BY a.id, scl.new_status_id, d desc

Your assumptions are almost correct.
The order of events:

Build table from FROM list

Apply join conditions and filters in WHERE clause.

GROUP BY / aggregate functions
Window functions
ORDER BY
DISTINCT ON (has to match leading columns of ORDER BY)
LIMIT (not in your case).

Window functions operate logically independent of the ORDER BY clause. But there are synergies for performance if both use the same sort order.

There is not enough information to tell whether this query does what it is supposed to do.

Related answers to shed some more light on the topic:

How do I get the aggregate of a window function in Postgres?
PostgreSQL: running count of rows for a query 'by minute'


Mirroring SQL Server 2008R2 My question might be dumb...

But anyone please tell me

Does automatic fail over happens in mirroring if the connection between witness server and principle server is lost for some reason.?

Thanks in advance..

Does automatic fail over happens in mirroring if the connection between witness server and principle server is lost for some reason.?

No automatic failover does not happen in this scenario as the current principal will still have a quorum forming a partner-to-partner quorum. Without a witness, automatic failover is not possible - ONLY Manual failover is possible.

This is explained in more detailed at How a Witness Affects Database Availability ?


How to INSERT INTO ... RETURNING for matrices in PostgreSQL? Approximate size and type of c_matrix_data is 2k x 3k double which is equivalent max to rand(5000,5000) in Matlab 2016b.
The random set selection of such data corresponds max 1 GB size (so BYTEA ok) for many measurements, presenting about 5% of all measurements.
Queries to CREATE and INSERT bulk with RETURNING

DROP TABLE IF EXISTS measurements, c_matrices;
CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        db_id INTEGER NOT NULL,
        patient_id INTEGER NOT NULL,
        channel_id INTEGER NOT NULL,
        c_id INTEGER NOT NULL,
        time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT no_duplicate_measurements UNIQUE (time),
        CONSTRAINT no_same_measurements UNIQUE (db_id, patient_id, channel_id, c_id)
);

CREATE TABLE c_matrices (
        c_id SERIAL PRIMARY KEY NOT NULL,
        measurement_id INTEGER NOT NULL,
        event_index_start INTEGER NOT NULL,
        event_index_end INTEGER NOT NULL,
        c_matrix_data BYTEA NOT NULL,
        c_size_in_bytes INTEGER NOT NULL,
        UNIQUE (measurement_id, event_index_start, event_index_end)
);

-- http://dba.stackexchange.com/a/107062/69807
WITH ins1 AS (
   INSERT INTO measurements (measurement_id, db_id, patient_id, channel_id) -- TODO not sure since this autogenerated
   VALUES (1, 2, 3, 4) -- if data comes from client app
   -- SELECT ... if data from other db TODO  how to do this here SELECT c_id FROM c_matrices WHERE measurement_id==777
   RETURNING measurement_id  -- generating a serial ID
   )
INSERT INTO c_matrices (measurement_id, event_index_start, event_index_end, c_matrix_data, c_size_in_bytes)
    VALUES (1, 2, 3, 'testBinaryData', 1234)
    RETURNING c_id -- generating a serial id -- TODO right?
    FROM   ins1;

I run psql data -f creates.sql and get where the error comes from INSERT INTO ... RETURNING

DROP TABLE
CREATE TABLE
CREATE TABLE
psql:creates.sql:38: ERROR:  syntax error at or near "FROM"
LINE 10: FROM   ins1;

PostgreSQL: 9.4
OS: Debian 8.5 64 bit
Client application: Matlab 2016b
Linux kernel: 4.6 of backports
Hardware: Asus Zenbook UX303UA

Looks good, though I have to wonder if this isn't a better match for PostGIS's raster, or another tool entirely.

INSERT INTO c_matrices (measurement_id, event_index_start, event_index_end, c_matrix_data, c_size_in_bytes)
    VALUES (1, 2, 3, 'testBinaryData', 1234)
    RETURNING c_id -- generating a serial id -- TODO right?
    FROM   ins1;

Not sure what this is supposed to do. To have a FROM you must have a SELECT, RETURNING comes after FROM. Close though.

-- Sample data.
# CREATE TABLE foo (id serial, b int);

-- Demo
# INSERT INTO foo (b) SELECT 42 RETURNING id;
 id
----
  1
(1 row)


How do I set up high availability at the instance level? I have two database instances (Instance A, Instance B) of SQL Server, each one hosted on a separate VM using windows server 2012. Instance A has SQL Server 2008 R2, instance B has Sql server 2012. I'm not a DBA, and I'm completely lost as to how to provide high availability for both of these instances.

Ideally, I'd like someone to point me in the right direction to a solution that allows these instances to automatically fail over gracefully if we have problems, preferably at the instance level, since I have a bunch of databases in each instance. I have enough hardware to host these instances somewhere else on the network, and several gigabits of network bandwidth to get this done.

Please go ahead and assume that I am an idiot.

Mew your cat into the sun, brother!

It sounds like Failover Clustering is what you may be looking for.  You can have the instance failover (though there will be a period of disconnect for end-users, but that is typically minimal while the passive node becomes active and SQL Server starts up).  SQL Server Failover Cluster Instances (FCI) are the way to achieve high availability at the instance level.

It is noteworthy, that this technology sits on top of WSFC respectively, and you will require shared storage (this will be different if you are on SQL Server 2012 implementing AlwaysOn Availability Groups, but that is not instance-wide failover).

Failover Cluster Instances are a robust technology within themselves, much more that can be directed through a Stack Exchange answer, so my recommendation is to use the above link and if you have any more specific questions feel free to ask them on a separate question.


Generate sample data for an Oracle database Good morning,

What free [or trial] software can I use to generate sample data for an Oracle database?

I've been trying with various tools, but none work.

Thanks for all suggestions,

Alec Taylor

I like Benerator. It's not super easy to setup but quite flexible.


Not enough space for MySQL optimize; do new rows overwrite existing data? My MyISAM table consumed most of my disk space. I deleted a significant portion of the table, but the optimize command (to clean out the deleted data) does not have sufficient space.

Is there any path around this? One idea is that as I add new data, it might consume from this extra space, rather than add to the disk usage, but I don't know how MySQL implements this.

The DB use is around 70GB and the available disk space is about 72GB. This is an isolated machine to which I have no physical access, hence I cannot add space myself.

There is a user comment on the OPTIMIZE TABLE documentation page that had the same situation as you. His solution was to copy the table somewhere else, optimize, then copy back. This required offline time.
Unfortunately, my solution would also require offline time as well, for 70GB worth of data. Something like this:

mysqldump table from another machine with enough space
DROP TABLE foo
Load table from the mysqldump file: mysql -hXXX.XX.XX.XXX -u... -p... < foo.sql

The downtime will be from the DROP TABLE step until the load table from a remote host of 70GB of data can be completed.
One potential bright spot is that new INSERTS will re-use the space created from DELETES:

Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

But this is what causes fragmentation to begin with. So you will need to make sure your tablespace is optimized frequently to prevent this in the future.
I just want to throw out there that to run an optimize query, you're always going to need at least the database size + the size of the table you're optimizing available on disk.
As an example, this means for a database size of 30GB and the table your optimizing is 28GB, you will need at minimum a harddrive that can fit 58GB of data space. This is because MySQL rewrites a new temporary file and then replaces the old one.
I say at least, because there's always other things going on. You'll need space for other programs, MySQL might be creating on-disk temporary tables during the operation, etc.


Replicating into a Percona XtraDB Cluster We have a 2 node (plus an arbiter) Percona XtraDB cluster. It is used for data indexing/archiving. It has it's own database called archive but it is also the replication slave for a database called primary. This comes from a standard master->slave Percona Server pair.

One of the clustered servers segfaulted (reason still being investigated...). This node was, specifically, the one that was doing replication. On restarting it, it deleted the contents of the MySQL data directory (/var/lib/mysql/) and did an SST from the other node. This went fine, but it now has a partially incomplete dataset on the primary database and no master binlog position information.

How should replication be restored?

It is not feasible to export the primary database as a mysql dump since it is circa 1TB in size when looking at the raw innodb files
If we export the primary DB with xtrabackup and prepare it as a whole on the clustered node, it is assumed it will get deleted as soon as the node starts SST
It is difficult but not infeasible to allow the node to do SST and then restore an xtrabackup of primary DB table by table... but it will take a while given 500+ tables

So, the question is... What is the correct way to set up replication into a multi-master cluster?

I suspect you are 'out of luck'.

Was there some reason not to have 'primary' part of the cluster?

Look into pt-table-checksum and pt-table-sync.  These may be a way to incrementally 'fix' the Slave to get it synced with Primary.


how do I insert timzone-pois.sql into MYSQL? I have read about how to insert the time zone into MYSQL as below:

I can't find time zone tables in sys database of MySQL

However, after I type show databases, there was no response except showing mysql>

Thus I'd like to clarify how do I go about inserting the timzone-pois.sql into MYSQL.

I am using mysql workbench and mysql 8.0.13 server. Windows 10.
Tks.

Looking at the manual, for Windows, you need to download the timezones which is a zip file that contains a SQL file. This can be run in workbench.


Why did my views get renamed? I'm experiencing a very strange behavior on SQL Server 2012. I have a database with several views, two of them are CustomerView and ShipToAddressView.

Problem is: Suddenly these views got renamed to CustomerViewView and ShipToAddressViewView! If I drop and create the views with the correct names, it succeeds, but after a few minutes the rename happens again.

I have no idea why this is happening; this is what I've checked:

no other users are renaming those views;
there are no jobs in SQL Server Agent renaming those views;
there are no triggers renaming those views.

What else could it be?

Seems like it was some long-running transactions involving those views, which when rolling back somehow reverted back their names to the name used when creating them, i.e. the ones ending in ViewView (which was a typo in the create statement) then renamed to the correct name while some transactions were already started, and possibly renamed back on rollback.


PostgreSQL wal archive name collision I recently experienced a filename collision with PostgreSQL's wal archives.

This is with Postgresql 11.2, containerized, on Ubuntu 18.04.

I also noticed the container itself was restarted some 4 weeks ago.

The archive command starts with test, as is recommended on the documentation page: https://www.postgresql.org/docs/11/continuous-archiving.html

And it turned out that file that postgresql was trying to create, already existed.

So, a name collision.

What I'd like to know is how this is possible. I was expecting postgresql to name its wal_archives continously, not restart its naming scheme upon a restart of the process.

Or am I wrong in my analysis and is something else going on here?

The archive command starts with test, as is recommended on the documentation page: https://www.postgresql.org/docs/11/continuous-archiving.html

To quote the following sentence from that page "This is an example, not a recommendation"

Most likely the file had been copied, and it had been (unknown to PostgreSQL) persisted to disk, but for some reason the copy command returned an error, or didn't return anything at all, because of a crash or an unclean shutdown, or storage was detached unexpectedly from underneath it.  So as far as PostgreSQL is concerned, the archival was not successful and so needed to be repeated.  But when it tried to repeat it, it finds a file with same name already exists.  You could move the existing file to new name, let the archival proceed, then compare the two files to see if they are identical and if so delete the first one.  There is no way to atomically coordinate the filesystem with PostgreSQL, so these things do happen.  If you used a dedicated product to do the archiving rather than rolling your own script, it might take these steps for you.

If you have two clusters archiving to the same place, you will find the files are not identical in their headers.  You will have to figure out whether the other cluster is still valid, and if so where it is supposed to be archiving, fix it going forward, and move the old version of the file to the correct location.

If you are absolutely certain you can't have two instances trying to archive to the same place, then you can remove the 'test' from the archive_command.  Its only useful function is guard against that eventuality.  And as you see, it complicates the case when a archive operation was unsuccessful (either actually unsuccessful, or was not reported up to PostgreSQL as being successful) but did create the named file before failing.


The table is full - MariaDB I am getting the following error whist trying to execute a long running query.

  The table 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp#sql1664_349_19be' is full

The C drive (NTFS) on the server has 135GB free space.
The D drive (NTFS) which holds the data has 365GB free out of 800GB.

The server has 32GB RAM.

The query I am running is reasonably simple but it is run against 61 million rows.

I have 18 indexes on tblinvoice and the table is INNODB.

SELECT
    InvoiceNo,
    NetAmountAbs,
    InvoiceDate
  FROM tblinvoice
  WHERE GrossAmountAbs >= 200
  GROUP BY InvoiceNo,
           NetAmountAbs,
           InvoiceDate
  HAVING COUNT(*) > 1
  AND SUM(CASE WHEN NetAmount_Doc >= 0 THEN 1 ELSE 0 END) > 1
  AND MIN(AccountNumber) != MAX(AccountNumber)
  AND MAX(GrossAmountAbs) != MIN(GrossAmountAbs)
  AND MAX(ImportID) = 2

This is my.ini file

[mysqld]
log_bin_trust_function_creators=1
datadir=E:/MariaDB 10.2.8
port=3306
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_buffer_pool_size=25000M
character-set-server=utf8
innodb_io_capacity=200
innodb_read_io_threads=64
innodb_thread_concurrency=0
innodb_write_io_threads=64
max_connections=1000
innodb_lock_wait_timeout=50
lower_case_table_names=2
event_scheduler=on
character-set-server=utf8
max_allowed_packet=100M
connect_timeout=10
innodb-log-file-size=1G
innodb_log_buffer_size=64M
tmp_table_size=2G
max_heap_table_size=2G
[client]
port=3306
plugin-dir=C:/ProgramFiles/MariaDB 10.2/lib/plugin

Thanks

EDIT: Updated my.ini with suggested changes to innodb-log-file-size,
    innodb_log_buffer_size,
    tmp_table_size,
    max_heap_table_size,

Please do not get fooled by the error message.

Note that the error is table is full. It does not say disk is full.

What would create a table is full condition ?

It has to do with the changes that are pouring into the rollback segments for a transaction.

I have addressed this many times

May 31, 2013 : How to solve "The table ... is full" with "innodb_file_per_table"?
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
Jun 16, 2014 : MySQL Index creation failing on table is full
Apr 07, 2015 : MySQL query INSERT INTO ... failed - The Table "x" is full

It appears that one of the following occurred

The SELECT was in the middle of a transaction
The SELECT attempted to hold too much rollback info in the face of  many transactions

In either case, the tblinvoice table probably had so many changes pending that the SELECT just had to give up. This could have caused all write transactions against tblinvoice to rollback.

If you were running this SELECT on a busy Master with lots of writes against tblinvoice, then I could this SELECT having this problem.

What you should do is set up a reporting slave (running MySQL Replication) and run this type of query on it instead on the main server.

UPDATE 2018-02-27 10:10 EDT

Your problem may be the amount of RAM free.

You currently have this

tmp_table_size=2G
max_heap_table_size=2G

Try running the query again so that the temp table goes immediately to disk

set tmp_table_size = 1024 * 1024 * 16;
set max_heap_table_size = 1024 * 1024 * 16;
SELECT
    InvoiceNo,
    NetAmountAbs,
    InvoiceDate
  FROM tblinvoice
  WHERE GrossAmountAbs >= 200
  GROUP BY InvoiceNo,
           NetAmountAbs,
           InvoiceDate
  HAVING COUNT(*) > 1
  AND SUM(CASE WHEN NetAmount_Doc >= 0 THEN 1 ELSE 0 END) > 1
  AND MIN(AccountNumber) != MAX(AccountNumber)
  AND MAX(GrossAmountAbs) != MIN(GrossAmountAbs)
  AND MAX(ImportID) = 2;

Keep watch on the C:\Windows\SERVIC~2\NETWOR~1\AppData\Local folder. Watch the temp file that shows up in that folder. What I am hoping is that the temp table will reach the needed size on disk. Why ?

With tmp_table_size=2G and max_heap_table_size=2G, the temp table has to reach 2G in RAM before transferring to disk. My working theory is that you do not have 2GB of RAM free (We are talking Windows, right ???). This is why I am suggesting lowering these to 16M (which is the default value anyway). Give it a try !!!


How to restore .wt backup file to local MongoDB? This is a question that was asked before, but I have tried all solutions, and simply cannot get it right. I have spent quite some time researching before posting this question. I have looked at the official MongoDB documents and many other blogs.

How to restore a .wt MongoDB backup file to a local MongoDB database?

Community wiki answer:

*.wt are binary data files used by the WiredTiger storage engine. Individual files are not usable as a standalone backup. If you want to take a file copy backup of a MongoDB dbPath you need to include all of the files using a Supported File Copy Backup Method.

If you have a valid file backup you can use it as the dbPath for another mongod instance. Aside from copying files, there is no restore special restore process for a file copy backup.

Can you clarify what files you have in your backup?


MCSE: Data Management and Analytics requirements overlap with MCSA: SQL Server 2016 Database Development I've been reviewing the requirements to get the MCSE: Data Management and Analytics certification, and was thinking of going the path of the MCSA: SQL 2016 Database Development pre-req. The thing is there's an overlap in the requirements. The MCSA requires passing exam 70-761 and 70-762. To get the MCSE I need to take 1 exam from a list of about 12, 70-762 being one of the options. I'm confused about the requirement overlap of exam 70-762. Once I have the MCSA do I meet the stated requirements for the MCSE. Or do I need to take a 3rd exam from the list to qualify?

Because there are many ways to obtain the MCSA the 762 is included as a choice for those who have not previously taken it. If you have already taken it then you would need to choose a different exam to obtain your MCSE.


Database Normalization Causing Inefficiency Note: I am relatively new to managing an SQL database. I have done my fair share of reading, research, and experimentation, but it is possible that I am missing some fairly basic knowledge. I'm also not sure how to tag this question so if someone could add relevant tags that would be appreciated.

I am currently building an SQL database for a forum like website. Among other things, the website will contain pages with threads, basically a series of posts by different users.

Users on the website are identified by a unique ID (char(36)) that is set once at registration and cannot be changed afterward. The user also has a username (varchar(16)) that is just a screen name. This name is almost always unique between users, but not necessarily. The user can also change the name, but there is a hard limit of two weeks between any two username changes. All of this information, and a few other things like login details, are stored in one Users table with the ID as a primary key.

There are multiple tables for the forum itself, but the one that is important for this question is the Posts table (primary key is 'post_id' int(10) NOT NUll AUTO_INCREMENT PRIMARY KEY. It contains one row for every time somebody makes a post on a thread, including responses to existing posts. As a result, whenever a user requests a thread, I query the posts table for all posts matching that thread (I store a thread ID foreign key). My question now concerns storing a user who is the author of the post. When a thread is requested, I need to send the username (not ID) of the users that made different posts. However, I also need to make sure that changes in usernames do not cause the apparent author of a post to change. As far as I can tell I have two possibilities:

For storing the author of a post, I can store only the ID. However, this means that I will have to make quite a few requests to the Users table for the usernames of the various IDs of the posters. This seems highly inefficient, seeing that the usernames will change very rarely.
I can store both the author ID and the author username in the Posts table. I can then go through the table and change all usernames among the posts (I can even set this up with a trigger, I believe). However, this opens the window for update anomalies, if the username stored for an ID in the Users table is different than in one of the posts. I can still use a trigger to prevent these anomalies at the database level, but from what I understand this should not be necessary in a well-designed database.

Which one of these two solutions (or something entirely different) should I use to both maintain efficiency and database normalization?

If it matters, I'm using the most recent version of MariaDB and accessing using PHP.

With regard to the threading issue that people have picked up on as much as your question about user id and name normalisation, without using CTEs there are a number of ways to arrange hierarchies to allow efficient retrieval of threads or portions thereof. You could store a materialised path to each post for instance. Efficiently dealing with trees in SQL is a subject whole books could be (and have been) written about, so I'll not go into more detail here. For a quick primer the relevant chapter in Bill Karwin's "SQL Antipatterns" (a book I recommend generally) is a good place to start, or for a fuller analysis Joe Celko's "Trees and hierarchies in SQL for Smarties", or search online for the tree management methods (with search terms like adjacency list, materialised path / cached path / path enumeration, nested sets, and of course recursive CTE / recursive common table expression).

On to the actual question:

  For storing the author of a post, I can store only the ID. However, this means that I will have to make quite a few requests to the Users table for the usernames of the various IDs of the posters. This seems highly inefficient, seeing that the usernames will change very rarely.

This is normal and should not be significantly inefficient. You will not be performing a separate lookup for every user ID spotted, instead you will use a JOIN and let the SQL engine worry about that for you which it will do well if you give it good index choices to work with. Something like:

SELECT posts.postTitle
     , posts.postText
     , posts.postTimeStamp
     , users.fullName
FROM   posts
JOIN   users
       ON users.id = posts.userID

Having said that, it is important to note that data normalisation is not about performance efficiency it is about protecting data integrity/correctness and (less importantly) storage efficiency: in this instance if you avoid having several copies of the same data (the username) you avoid the possibility of inconsistency because one or more copies disagree with the others.

  I can store both the author ID and the author username in the Posts table.

Unless you want to allow usernames to change and keep the original name displayed, this is unnecessary and you should use normal form and a join, as above.

Before trying to support name changes in a particular way, first decide if you actually need to allow them at all. If the name in question is an account name then perhaps you don't need or want to support it changing at all. If it is a personal name or email address, both of which can change over time, then you perhaps want to change all instances when this happens so you don't have to worry about it: just update the name in the users table and future queries like the above will get the new data instead of you needing to

  go through the [posts] table and change all usernames among the posts

at all.

Things get a bit more complicated if you want to maintain the display of the user's name at the time they made a post instead of old posts being displayed with the new name. You could store "name at time of post" in the posts table, and with this requirement you don't need to update it when the user's name changes (you are wanting to keep the old name). The other option is to keep a history of the user's record in normal form and using that to ready the correct name. This will be more efficient for storage (and less likely to become inconsistent due to bugs) but reading the username-at-time-of-post can be more complicated and will vary depending on what your chosen database supports (does it have efficient window functions?) and how you chose to store the history (using "system versioned history table" support in SQL Server or Oracle for instance). I'll not go in depth on this, as I suspect simply letting the displayed name change when the user's details change is perfectly fine and easy to implement.

Side note: key type choice

With CHAR(36) I assume you are using UUIDs for internal user ID? Unless you have reason to use UUIDs (the requirement comes from elsewhere and is not in your control, you are writing a distributed system and are concerned about key collisions for users created in different locations, ...) you might want to consider using an automatically incrementing integer instead (INT NOT NULL AUTO_INCREMENT in mysql/mariadb, INT NOT NULL IDENTITY(1,1) in MSSQL, ...) which will only use 4 bytes per key.

The same for post IDs if you are using UUIDs for them also.

For space efficiency some DBS support packed UUID types (UNIQUEIDENTIFIER in MS SQL Server, UUID in postgres, ...) which will store the value in 16 bytes instead of 36, but I don't think mySQL does. You could use BINARY(16) but you may need to faf around converting back and forth when reading/writing the values so that probably isn't recommended.

Depending on the number of rows you expect the difference in key size may not be significant to worry about, but if you plan for your application to scale into something huge it should be considered in the early design stages (making key type changes is a nightmare once an application has already rolled out!).


Distinct rows from a join I have two MySQL tables:

╔═══════════════════╗
║ wpss_question_set ║
╠═══════════════════╣
║ id                ║
║ quesset           ║
╚═══════════════════╝

╔════════════════╗
║ wpss_questions ║
╠════════════════╣
║ id             ║
║ question       ║
║ ques_set_id    ║
╚════════════════╝

ques_set_id is the foreign key. The query is:

SELECT
    wpss_quesset.quesset,
    wpss_questions.*
FROM wpss_quesset
JOIN wpss_questions
    ON wpss_quesset.id = wpss_questions.ques_set_id;

I am getting duplicate results on ques_set_id and quesset. How do I select unique records from 'ques_set_id and quesset?

if you want to select unique record from other join table which has same FK in multiple row you can use your query as

 SELECT
        wpss_quesset.quesset,
        wpss_questions.*
    FROM wpss_quesset
    JOIN wpss_questions
        ON wpss_quesset.id = wpss_questions.ques_set_id
   GROUP BY wpss_quesset.id

OR If you want to get all record that of related FK you should use group concat with group by

Example

 SELECT
        wpss_quesset.quesset,
        GROUP_CONCAT( wpss_questions.question)
        wpss_questions.*
    FROM wpss_quesset
    JOIN wpss_questions
        ON wpss_quesset.id = wpss_questions.ques_set_id
   GROUP BY wpss_quesset.id


SQL Query too slow on SUM function I've a table with around 32 million rows having clustered unique index on CountryID,RetailerID,ProductID,DateID,EventID,TypeID and query is

SELECT  f.RetailerID
,       TypeID                                AS TypeID
,       c.CalendarMonth                         AS CalendarValue
,       SUM(ISNULL([Volume],0)                  ) AS Volume
,       SUM([VolumeBox]                         ) AS VolumeBox
,       SUM([VolumeKG]                          ) AS VolumeKG
,       SUM([VolumeUnit]                        ) AS VolumeUnit
,       SUM(ISNULL([R_Turnover] , 0.0)          ) AS R_Turnover
,       SUM(ISNULL([R_VAT],0.0)                 ) AS R_VAT
,       SUM(ISNULL([R_TurnoverExVAT],0.0)       ) AS R_TurnoverExVAT
,       SUM([SupplierRealisation_Amt]           ) AS [SupplierRealisation_Amt]
,       SUM([SupplierDiscount1_Amt]             ) AS [SupplierDiscount1_Amt]
,       SUM([Supplier_1NetSales_Amt]            ) AS [Supplier_1NetSales_Amt]
,       SUM([SupplierDiscount2_Amt]             ) AS [SupplierDiscount2_Amt]
,       SUM([Supplier_2NetSales_Amt]            ) AS [Supplier_2NetSales_Amt]
,       SUM([SupplierDiscount3_Amt]             ) AS [SupplierDiscount3_Amt]
,       SUM([Supplier_3NetSales_Amt]            ) AS [Supplier_3NetSales_Amt]
,       SUM(ISNULL(S_CostofGoodsSold, 0)        ) AS [S_CostofGoodsSold]
,       SUM(ISNULL(S_Profit, 0)                 ) AS S_Profit
,       SUM(0.0                                 ) AS AdditionalCostofGoodsSold
,       SUM(ISNULL([R_DistributionCost],0.0)    ) AS R_DistributionCost
,       SUM(ISNULL([R_Profit],0.0)              ) AS R_Profit
FROM [dbo].[EventScenarios] es
JOIN dbo.[Event] e ON es.[EventID] = e.ID
JOIN dbo.EventProduct ep on es.EventID = ep.EventID
JOIN [dbo].Product p ON p.ID=ep.ProductID
JOIN dbo.EventPL f  ON  e.CountryID = f.CountryID AND f.RetailerID = e.RetailerID AND f.EventID = e.ID
AND ep.ProductID = f.ProductID
INNER JOIN Calendar c   ON  c.DateID = f.DateID
WHERE  f.CountryID= 14  AND c.CalendarMonth BETWEEN 201301  AND 201312
GROUP BY f.RetailerID , c.CalendarMonth ,TypeID

The query plan is showing 88% time on Clustered Index seek on EventPL table but still its taking around 15 seconds to complete. Is there any way I can optimise it to around 1/2 seconds?

Query Plan XML Link

(Moved my comment to an answer, and expanded a bit.)

  I cannot create indexed views as I've some calculations as well like SUM(Volume * CaseCount) AS SomeColumn which is not allowed in indexed views and also it's a dynamic query and grouping and joins are based on user input.

From the plan you provided, the clustered index scan is 3.5x overestimated, and the Sort is an order of magnitude underestimated which caused a spill to tempdb. Performance may improve after updating statistics.

However, based on your reply, if this schema is servicing "random" queries against that amount of data, it's going to be extremely challenging and/or expensive to get good performance for all the possible combinations.

For this type of workload, I think the only realistic way to meet the performance goal would be to abandon trying to find a database engine solution, and use Analysis Services instead.


Update is not happening in Golden Gate I have configured GoldneGate and Extract, Data pump and Replicat everything is working fine. However Update is not happening. Below are my Extract, Data Pump and Replicat file.

Extract File:

extract ext3
userid ogguser, password ogguser
exttrail /u01/app/oracle/product/11.2.0.4/ggsrc/dirdat/lt
DDL INCLUDE MAPPED
table gg01.t2;

Data Pump File:

extract dpump1
userid ogguser, password ogguser
rmthost targetGG, mgrport 7810
rmttrail /u01/app/oracle/product/11.2.0.4/ggtrg/dirdat/rt
passthru
table gg01.t2;

Replicat File:

replicat rep3
assumetargetdefs
userid ogguser, password ogguser
map gg01.t2, target gg01.t2;

Let me know, what parameters need to be configured to get update working.

Error which i get:

2016-11-28 21:45:32  WARNING OGG-01004  Aborted grouped transaction on 'GG01.T2', Database error 1403 (OCI Error ORA-01403: no da
ta found, SQL <UPDATE "GG01"."T2" x SET x."NAME" = :a4,x."ADDRESS" = :a5,x."AGE" = :a6,x."GENDER" = :a7 WHERE x."NAME" is NULL AN
D x."ADDRESS" is NULL AND x."AGE" is NULL AND x."GENDER" = :b3 AND ROWNUM = 1>).

2016-11-28 21:45:32  WARNING OGG-01003  Repositioning to rba 7748 in seqno 5.

2016-11-28 21:45:32  WARNING OGG-01154  SQL error 1403 mapping GG01.T2 to GG01.T2 OCI Error ORA-01403: no data found, SQL <UPDATE
 "GG01"."T2" x SET x."NAME" = :a4,x."ADDRESS" = :a5,x."AGE" = :a6,x."GENDER" = :a7 WHERE x."NAME" is NULL AND x."ADDRESS" is NULL
 AND x."AGE" is NULL AND x."GENDER" = :b3 AND ROWNUM = 1>.

Thanks!

Got the answer. Below command had to be executed before executing update.

add trandata gg01.*

Thanks!


mysql import failed caused by overgrown general log file I have a table about 70G in size. I took a backup using mysqldump.

In the new server, I performed a restoration.

time mysql -uroot -p TDB < TDB.sql
Enter password:
ERROR 3 (HY000) at line 58155: Error writing file '/app/mysql/admin/tmp/MLLVx92e'
(Errcode: 28 - No space left on device)

Further checked, showed that the general log file in /app has increased tremendously.

show variables 'general_log_file'
general_log_file                        | /app/mysql/admin/mysql.log

Does importing using mysql causes general log file to grow exponentially?

All statements that hit the server are stored inside the general log. So general log is duplicating your dump. The general log should be used only for debugging only, and it should be activated for the time required to spot a problem. Try to disactivate general log.


Running the .sql script in Linux to get the postgresql database table and functions I am using Red hat Centos 7. When I use this command to run the .sql script to get postgres database table and fucntion:

$sudo psql -U postgres -d tsdb -W -f /.sqlscript/location
It asks for postgres password and when i put password and hit enters it says
psql: FATAL: Peer authentication failed for user "postgres"

What could be my error? How do I fix the error so that I can successfully run the script to restore table and function and all the data in postgresql.

This is my pg_hba.conf. Is there anything that need to be changed in this file?

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                               peer
#host    replication     postgres        127.0.0.1/32           ident
#host    replication     postgres        ::1/128                ident

You are trying to run the psql as the OS user 'root'.  Your pg_hba.conf file doesn't let OS user 'root' log in as database user 'postgres'.

You could instead use sudo to run as OS user 'postgres', not 'root':

sudo -u postgres psql -U postgres -d tsdb -f /.sqlscript/location

(Although perhaps OS user 'postgres' won't have access to the sqlscript)

You could also make use of a mapping file pg_ident.conf to say that root is allowed to log in as postgres, although I don't see much point in that.

Or you could change the "local" line of your pg_hba.conf so that it uses "md5" rather than "peer".  At that point, there is no reason to use "sudo" at all as it doesn't care who you are, just that you know the password.

Or you could make use of the existing 'host ... md5' line of your pg_hba.conf, by specifying a host rather than defaulting to using a unix socket:

psql -h 127.0.0.1 -U postgres -d tsdb -W -f /.sqlscript/location


Are Cataloge and relation cache in PostgreSQL per connection or global to the server? I am trying to figure out if Postgres' relation and catalog cache are per connection or per server? I did find that plan caches are per connection. It would make sense for the cache to exist for as long as the server is running, but I can't find any documentation on it.

Those caches are per connection.  They are not held in shared memory.


MongoDB script to backup replication set locally to a ubuntu 14.04 server I would like to make a Daily Backup of my MongoDB from a replication set running from ubuntu 14.04 server.

End goal would be to get a daily backup and write the backup to a remote or local share - ubuntu.

Can I batch the mongodump command?

Please help me on this

You can execute mongodump from the remote server as mentioned below:

mongodump --host <ip-of-mongodb-server> --port <port-number-of-mongod> --out <some-directory>

This is required mongo server to be accessible from the remote server.

This dump file can be then compressed and archived on remote host or s3.

If the mongo server is not accessible from remote host, in that case you can execute mongodump on local mongodb server and copy it to remote or local share.


How to find out the maximum number of datafiles per tablespace in Oracle? According to this Oracle document, the maximum number of database files per tablespace is "Operating system dependent; usually 1022".

I know there are queries for some of the other limits mentioned in that document (e.g. to determine the database block size: select value from v$parameter where name='db_block_size').

Is there a query to determine the maximum number of datafiles per tablespace?  (I tried select name from v$parameter where value='1022' but that returned no rows.)

db block size is a database parameter but maximum number of tablespace or datafile is a physical limit and you can not find them in v$parameter view. You can use documentation for that: https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm#i287915


How can I keep “approximate” date data points? I'm working on a web app that displays some products with their release date. Some dates are complete (for example, '2017-09-05') and some are not (for instance, 'first 2018 quarter', '2018 september', '2019').

The existing database is built on MySQL and I want to keep requests “simple”.
Sometimes I need to request (a) released products, sometimes (b) future products, and sometimes (c) all products.

The questions

How can I (1) retain and (2) manipulate these incomplete date data points? In the same date column? In which format?

There is no "standard" way.  You need to invent something, then write app code to handle it.  Some thoughts:

Strings like what you suggest.  Pro: displays nicely; Con: hard to parse.
Strings, but more like 2018Q1, 201809, or whatever.  Pros/cons:  Somewhat readable; somewhat parsable.
DATE, with the day of the beginning (or end?) of the period, plus another column that says day or month or quarter or annual.  That could be an ENUM or some other technique.  Pro: somewhat sortable.


how to improve performance by using or not using table variables I have a table variable:

DECLARE @to_process TABLE
(
    [Id] [bigint] NOT NULL,
    [SequenceId] [bigint] NOT NULL,
...
)

INSERT INTO @to_process
   (  Id
    , SequenceId
...
   )
  SELECT
    TOP (@recordsToProcess)
      Id
    , SequenceId
...

in my stored procedure. I have investigated that insert into it spends about 66% of total execution time.

How can I improve or optimize my code to speed up my sp execution?

ADDED:

First thought...

If you have sufficient data to take this much % of the batch, use a #temptable.

When the table variable is used later, it is always assumed to have one row: there are no statistics on this table variable. So if you have several 1000, subsequent plans won't be optimal.

Temporary tables have statistics (and indexes if required etc) and can perform better for larger datasets


Relationship between number of transactions and redo generation If I execute some SQL statements over many transactions instead of just committing once after all statements, will this have an effect on the amount of redo log generated? And hence on the size of the archiving log directory?

Yes, it will.

1000 statements, each in its own transaction, will generate more redo than 1 transaction of 1000 statements.  Transaction begin/end markers do require space in the redo and archive log.

Further, Oracle writes the redo log buffer to disk as it needs to;  generally speaking, committing more than 3 times per seconds adds latency (due to serialized physical I/O required to flush the log buffer to disk) to the overall operation.


How to troubleshoot SQL "The timeout period elapsed prior to completion of the operation or the server is not responding." I'm trying to troubleshoot a timeout issue that occurs on one of our SQL 2005 servers.

The database responds normally for about a day until we  start to see sql timeout errors.  After that all queries take 30+ seconds to return and it does not return to normal operation until we reset the SQL services. The sql queries are very simple, updating a single row in a database.

It seems like the problem is specific to one database because we can run the same queries on another database on the same server and they return normally. I've looked into possible locks using the technique outlined here and I don't see anything that sticks out.  This is on one of our beta servers so there are very few requests to it.  Any ideas?  Thanks

Have you tried polling for dbcc opentran? It could be you have an uncommitted transaction problem occurring on that database. Also, worth checking the collation of the databases is the same, always the first thing to check in a "this one works this one dosent" scenario

http://msdn.microsoft.com/en-us/library/ms182792.aspx


Update field with the longest varchar field I need to update on of the varchar fields in a table, but only the record that is the longest varchar (has the most data in it.). I could easily find it with this query:
SELECT TOP(1) options
FROM ForrasReportsRicsiTeszt
ORDER BY LEN(options) DESC

Now I need to update it, but I am quite unexperienced with sql. This is a 2005 ms sql server.
my query for updating:
  UPDATE ForrasReportsRicsiTeszt
  SET options = REPLICATE(options, 4)
  WHERE TOP(1) ORDER BY LEN(options) DESC

This gives me an error to TOP(1) which was quite expected. I just don't know the right way to write this query, how could I get to update only the longest varchar field? The one I've managed to get with my select query. Sadly there is no primary key that I could use in my WHERE.
Any help would be much appreciated!

Using the MAX function you could write the following to obtain the entry with the longest varchar
SELECT
  MAX(LEN(options))
FROM
  ForrasReportsRicsiTeszt;

This can then be combined with an UPDATE statement to give
UPDATE ForrasReportsRicsiTeszt
SET
   options = REPLICATE(options, 4)
WHERE
   LEN(options) = (  SELECT
                     MAX(LEN(options))
                   FROM
                    ForrasReportsRicsiTeszt
               );

Alternatively you could use a Common Table Expression (CTE).
;WITH MyTestCTE ( LengthLogngestOption)
AS (
    SELECT
      MAX(LEN(options))
    FROM
      ForrasReportsRicsiTeszt
   )
UPDATE FRRT
   SET FRRT.Options = REPLICATE(options,4)
FROM
   ForrasReportsRicsiTeszt FRRT
JOIN
   MyTestCTE MTCTE
ON
LEN(FRRT.OPTIONS) = MTCTE.LengthLogngestOption;


How to prevent locking and possible performance issue I'm working on a website that lets users and admins to generate reports, and those reports require many database queries - they can take over one minute to complete. I noticed when these reports are being generated, the website might freeze for all its users.

These reports consist basically of SELECT queries, i.e., they don't update or insert anything into the MySQL database.

How do you guys implement intensive database operations such as report generations in production environments?

Don't use MyISAM; use InnoDB.  (to avoid "table locks")
Build and maintain Summary tables.  (often 10x faster)
Let's see the slow queries; maybe they can be improved.


Why the length of JOBHISTORY field is 172 here? It should be 150. Is Varray allocating some extra space?

Varray stores the total length of the varray and length of each element, so yes, it needs some extra bytes.

I am not aware of any public documentation on this topic, and I do not know the complete internal representation, so below is a simple example.

create or replace type jobs_done as varray(5) of varchar2(30);
/
create table emp_job_his (empno number(6), jobhistory jobs_done);
insert into emp_job_his values (1, jobs_done(
  'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA',
  'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB',
  'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC',
  'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD',
  'EEEEEEEEEEEEEEEEEEEEEEEEEEEEEE')
);
commit;
alter system checkpoint;

select
  dbms_rowid.rowid_relative_fno(rowid) as fno,
  dbms_rowid.rowid_block_number(rowid) as block_number
from emp_job_his;

       FNO BLOCK_NUMBER
---------- ------------
         4          134

alter system dump datafile 4 block 134;

The relevant part from the dump:

col  1: [162]
 88 01 a2 01 01 00 05 1e 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 1e 42 42 42 42 42 42 42 42 42 42 42
 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 1e 43 43 43 43 43
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
 1e 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44
 44 44 44 44 44 44 1e 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45
 45 45 45 45 45 45 45 45 45 45 45 45

The 3rd byte, a2, is the length of the column in bytes = 162 (col 1: [162]).

The 7th byte, 05, is the number of elements in the array  = 5.

Starting after that, each element is stored as length + data. Length is 1e = 30, and data is 30 times the letter A = 0x41, and so on.


Connect Two MySQL Databases I have two servers.

Eg. server A and server B.

I want to select data from server A and insert into server B using MySQL.

How to make it?  I can access server A's database remotely from server B but I can't do select and insert query. Because they are in different usernames.

Any ideas?

mysqldump from the database on the source server.

Then still on the source server mysql -h[dest server hostname/IP] -u[user] -p[pass] < [location of the file you just made].


SQL Server Upgrade - A new Approach for Upgrading Microsoft's technical guide for SQL Server upgrade references two upgrade paths: in-place and side-by-side.

I don't see a third, visibly more viable/convenient option, which is to do an in-place upgrade of a cloned version of the server/SQL Server. Here is what I mean: why not clone the server in question, then perform an in-place upgrade, and if all goes well, then one could simply create an ALIAS and then have all applications and users automatically re-routed to the new server. Are there any downsides to this? Is there something I am not seeing? Is using an alias to redirect users and apps not wise?

ASSUMPTIONS:

I am assuming that this is a simple case of upgrade: no mirroring, no replication, etc. This is just one server with a number of databases that need to be upgraded.
I am assuming that direct upgrade is supported. Specifically, in this case, it's from SQL Server 2008R2 to 2016.

Since one of the primary tenets of SQL Server, transactional consistency, is difficult to get right when performing a clone operation, I'd recommend against using a clone to perform an upgrade in the way you suggest.  A clone of the underlying machine while the SQL Server service is running and accepting client connections might result in the clone containing invalid data.  If you can create a clone of the machine while the SQL Server service is turned off, your proposed solution might be a viable method of performing an upgrade.  Having said that, I'd tend towards not using a VM/SAN clone since the potential for it to go badly wrong is pretty high.  As any DBA worth their money will tell you, unnecessary risk is unacceptable.

Having said that, using a DNS alias is an excellent way of abstracting away the machine name from a server, and is what Windows Failover Clustering uses to direct clients to the active node in a clustered SQL Server installation.

The high-level steps to effect an upgrade where you have a DNS alias in place look like:

Ensure the DNS alias has a short TTL value that will allow clients to "see" changes to the IP address pointed at by the alias quickly enough to connect to the new machine in the desired period of time.  I'd consider 1 minute.
Create a new machine, or VM, with the latest version of Windows and SQL Server as desired.
Backup the databases on the old server, then restore them to the new server.
Test all software/website/applications against the new server to ensure compatibility.
Once testing is complete, and you're happy with the new server, disable user-connections to the old server to ensure no one makes updates that won't be seen on the new server.
Take a final backup of the databases on the old server, then restore those databases to the new server.
Modify the DNS alias to point to the new server's IP address.

At this point client applications should be able to use the new server without any changes required at the client end.

The steps above assume you have an upgrade window long enough for you to perform a backup-and-restore of the databases involved in the upgrade process.  If you have no upgrade window, i.e. you require 99.999% uptime, you'll need to consider using mirroring, log shipping, or failover clustering to minimize downtime during the cutover to the new machine.


Differentiating physical standby to cascaded standby Why is it that for non-real time apply from cascading to cascaded standby that we have to mention as SYNC as the redo transport mode? Ideally we mention SYNC for real time apply right?

Also, should we mention as SYNC NOAFFIRM for non-real time apply?

Thanks!

Why is it that for non-real time apply from cascading to cascaded standby that we have to mention as SYNC as the redo transport mode?

Whether the Real-Time cascade feature is used or not, actually it depends on the attributes in the parameter LOG_ARCHIVE_DEST_n of the cascading standby that control how the redo stream is forwarded to the cascaded database. Beginning with Oracle 12c, the attributes LGWR and ARCH of the LOG_ARCHIVE_DEST_n parameters are deprecated. The only valid values are SYNC, ASYNC or none of them.
In particular, for a cascading standby (thus, for a DB in Standby Role), the only valid attribute for the transport is ASYNC, meaning that synchronous transport from a standby database to another is not possible. If ASYNC is specified then Real-Time cascade is used, if no transport attribute is specified, Real-Time cascade is disabled and the redo stream from the cascading to the cascaded database is transported using the archived logs.

  Ideally we mention SYNC for real time apply right?

Yes, when we are not using cascading and cascaded standby

  should we mention as SYNC NOAFFIRM for non-real time apply?

Maximum Availability mode now allows the LOG_ARCHIVE_DEST_n attributes SYNC and NOAFFIRM to be used together. This enables a synchronous standby database to be deployed at a further distance from the primary site without increasing the impact on primary database performance. (In an Oracle Data Guard broker configuration, this is referred to as FASTSYNC mode.)

So, its not necessary.


SSIS how to parse column with erratic data I have a question on how to parse out a column and hoping a SSIS/Database guru can help out :)

Here is the problem.  I have a column saved as a string/varchar format in SQL Server, and need to parse out data from it.  I have Business Intelligence Development Studio(BIDS) and SSIS support.  I am familiar with using the tool to use derived columns and splitting the data like that using BIDS.

The problem is the data, and here's an example of it.

Easy to parse with derived columns
24 Year
2 Month
3 Month
2 Month
8 Year
7 Year

No clue how to approach to parse
1x Month
3x per Month
1-2 x per month
6 days per month

So what i want to essentially do is, if data is standard I want to split the column into two columns, Number(int) and DayType(string), and if it falls into the 2nd category (erratic data), put it in a 3rd column, Other.  Any suggestions on how to approach this problem?

Added Clarification:  I want to put the non standard data into another 3rd column, so I can look at the data, and run commands on that particular column, to edit out some of the fluff, then make the data workable in the application.

For example, 3x per month, take out the "x per" and then parse that into the columns Number, and Daytype.

Also where there are cases of 1-2, I would like to take the higher number, and drop the lower number, so for example 4-6 days a week, keep 6, drop "a week" and keep days

For something like this the optimal solution of course is to control your input. That said, the reality is you have to parse the supplied input.

For something as complex as your parsing, I'd skip the Derived Column transformation and go straight for a Script transformation. I select my source column, Input and create three output columns: number, trash and Interval. number and Interval will hold the parsed values while trash will only be populated when the script can't make heads or tails from the input.

I use two member variables, numbersRegex and periodDomain. periodDomain is just a list with the acceptable values. For string comparisons, I force everything to lowercase and hope for English. numbersRegex is a regular expression that is used to identify digits in a string.

For every row that comes in, the script will split the Input value based on whitespace. For each of those tokens, I test whether the token has a digit in it. If it does, we'll call the GetBiggestNumber method. Otherwise, we'll call the ValidatePeriodDomain Once all the tokens have been processed, then it's important to make certain both values have been set.

GetBiggestNumber attempts to look at all the groupings of number and find the largest set.

ValidatePeriodDomain attempts to compare the current value to a known list of acceptable values.

using System.Text.RegularExpressions;
using System.Collections.Generic;

/// <summary>
/// The amazing script transformation
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    Regex numbersRegex;
    List<string> periodDomain;

    /// <summary>
    /// Initialize member variables
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        // match consecutive digits
        this.numbersRegex = new Regex(@"\d+", RegexOptions.Compiled );
        this.periodDomain = new List<string>(){ "year", "month" };
    }

    /// <summary>
    /// Parse the incoming data
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        string[] parts = Row.Input.Split();
        string period = string.Empty;
        int? foo = null;

        foreach (string token in parts)
        {
            // try to do something with it
            // If the token has a digit in it, then we'll extract the largest value
            // if no digits, then the first token matching our domain is preserved
            if (this.numbersRegex.IsMatch(token))
            {
                foo = GetBiggestNumber(token);
            }
            else
            {
                if (ValidatePeriodDomain(token))
                {
                    period = token;
                }
            }
        }

        // at this point, we've processed the input data
        // If the local variables are in their initial states, then we didn't find
        // anything of note and need to populate the Row.Junk column
        // Why local variables, because can't read from Row.column
        if (period == string.Empty || (foo == null))
        {
            Row.trash = Row.Input;
        }
                    else
                    {
            Row.number = foo.Value;
            Row.Interval = period;
                    }
    }

    private bool ValidatePeriodDomain(string token)
    {
        return (this.periodDomain.Contains(token.ToLower()));
    }

    private int? GetBiggestNumber(string token)
    {
        int? bigOne = null;
        int? current = null;
        // Get all the groups of numbers and compare them
        foreach (Match item in this.numbersRegex.Matches(token))
        {
            current = int.Parse(item.Value);
            if (!bigOne.HasValue)
            {
                bigOne = current;
            }

            if (current.Value > bigOne.Value)
            {
                bigOne = current;
            }
        }

        return bigOne;
    }
}

Using the above script, you can see how it slices and dices the Input data. I made a minor change between the code that generated the below screenshot and what's posted. I observed that the input value 9000 was assigned to Row.number but as that Row never had an Interval assigned, I deferred the actual Row population to the end of the script (it was in the