Upgrading MySQL 5.1 to 5.5 - All tables gone I'm trying to upgrade from MySQL 5.1 to MySQL 5.5.

I followed the instructions here:
http://dev.mysql.com/doc/refman/5.5/en/windows-upgrading.html
I also adjusted the Datadir.

But after the upgrade, all tables are gone. I only use Innodb tables.

The Datadir setting seems to be correct, cause the ibdata1 file changes if I change something in the db. But even if I add a new schema with a new table it does not create a directory for it.
Am I missing something completely?

Turns out I had a mess with the my.ini files. Not sure why ibdata1 still changed, but that was the solution. The mysql workbench configuration was still pointing to the old my.ini file.


Postgres 10 not returning disk space after dropping indexes I had several indexes that totaled ~1TB, according to pg_relation_size(). I dropped them.

I expected to get a TB of disk space back at the OS level, but this did not happen. Maybe 15GB total (I didn't note the precise number at the time).

I used the following query to determine the sizes which I pulled from the wiki (https://wiki.postgresql.org/wiki/Index_Maintenance):

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

I used the value in the "index_size" column. The size matched my expectations based on the fields in the index and the size of the table.

For example, the remaining index on one of the tables is for the primary key and is, according to that query, 147GB on a 448GB table containing 7 billion records. Given that there are only a few fields, this seems like a reasonable size to me.

It's not table data so a VACUUM FULL/pg_repack should not be necessary. lsof shows no open deleted files. I can't even address them at this point.

How do I reclaim this space? Why didn't it come back immediately?

Have you previously used pg_upgrade -k to get to this version, and then didn't delete the old version's cluster directory?  If so, that old directory has hard links to the index data files which cause the underlying storage to be retained even when the link in the new version's directory gets unlinked.


Which should I use on my DB? - Clustered Index or Non Clustered Index or Both I just need advice on how I am going to optimize my database.

Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN

I have that format of tables on my database. Each tables has 30-days of records with hundred thousands of records each data. Uploading of data to database is every morning. The data of yesterday will be uploaded.

The most common queries to run is grouped by Date or Date and Area or Date and Area and Block. Now, in order to make queries faster, I found out using indexes. I used indexes before. But that was when I am using a primary key. In this table, I didn't use primary key because it is totally unnecessary.

Now, I am really confused on how I am going to optimize this. Based on this reference on Column Considerations part, clustered index is not a good choice on Columns that undergo frequent changes. I am really confused.

Please help me if I need to use clustered index here or just non clustered or both.

A primary key will enforce uniqueness in your data, which is almost always highly desired.  One of the issues you'll run into if you allow yourself to have multiple copies of the same record is that any results you have will be skewed to the point of being unusable for many purposes.  Also, this will happen, no matter how much you try to say it won't.  You can either use a primary key or a unique index for this purpose, and a primary key in SQL Server, by default on a table without a clustered index, will create a unique clustered index.

The rule of "clustered index is not a good choice on columns that undergo frequent changes" is misquoted here.  You appear to have records that have a static value for date, you're just adding more records every day that have a new date field.

The date field is actually perfect as the first key field of your clustered index for a couple reasons.  It's added in order, so inserts will be quicker and cause less fragmentation.  Also, you always query by it, so queries will be able to take advantage of it.  Just add the key columns to the clustered index until you can make it unique and make it a unique index (primary key is optional, but preferred).

What the clustered index is going to do for you is actually store the data sorted by the key fields.  A phone book is nothing more than a clustered index with the first key field of "Last Name", and you're able to find the person you're looking for very quickly.  In your case, you're adding data according to the date making it easy for SQL Server to keep it sorted this way, then you're querying by the date making it so SQL Server can find the rows in question as quickly and efficiently as you look up names in the phone book.

To learn more about indexes, you can read my blog post on the topic here: http://simplesqlserver.com/2013/11/20/indexing-fundamentals/


Union from Multiple Tables in many databases and group results in batches of n First post in dba, so please be gentle.

I have a project I am working through and have 4+ SQL Server databases to work with.

These DBs contain 2 (or more tables) I wish to UNION the results from.

The data I am interested in contains Windows Server file paths (SrcFilename, DestFilename for reference) and looks something like this:

  srcfilename001.dwg|\\srcServer1\path\to\file\|destfilename001.dwg|\\destserver\path\to\file\
  srcfilename002.dwg|\\srcServer2\path\to\file\|destfilename002.dwg|\\destserver\path\to\file\
  srcfilename003.dwg|\\srcServer3\path\to\file\|destfilename003.dwg|\\destserver\random\path\
  srcfilename004.dwg|\\srcServer4\path\to\file\|destfilename004.dwg|\\destserver\random\path\

I have been able to UNION the data using a query like this:

    SELECT * FROM (
SELECT * FROM [Database_015].[dbo].[CAD_DATA] Where [Database_015].[dbo].[CAD_DATA].[UniqueValue] = 1
UNION ALL
SELECT * FROM [Database_016].[dbo].[CAD_DATA] Where [Database_016].[dbo].[CAD_DATA].[UniqueValue] = 1
UNION ALL
SELECT * FROM [Database_017].[dbo].[CAD_DATA] Where [Database_017].[dbo].[CAD_DATA].[UniqueValue] = 1
UNION ALL
SELECT * FROM [Database_020].[dbo].[CAD_DATA] Where [Database_020].[dbo].[CAD_DATA].[UniqueValue] = 1
) A
ORDER BY NEWID()

Which works great...

I get the data I need in a random order rather than a bunch of files from \\server1, \\server2, \\server3 etc.

Obviously the randomness is because of the ORDER BY NEWID().

The reason for gathering the data is because we're migrating millions of files and ultimately need to hash both the source and destination files to see if they match.

Ultimately I will use Powershell to hash the files from the resultant data and I think that having each Powershell Start-ThreadJob 'Job' point at each server in turn for n files will be faster than having it work on a "stripe" of data across multiple servers.

Thanks for your time. :)

Edit: The first comment asked for more explanation so here goes:

I don't know how to combine both UNION ALL, GROUP BY and ORDER BY to produce a dataset from multiple different database tables that has n batches of files grouped by server in a random order.

Assuming the question is...

  "I've randomised per-server, but now I need to randomize among the whole set"

...then it may be prudent to remove UNION & ORDER BY from your query altogether. You can unify a standard query against all database/server combinations into a single dataset stored as a PSCustomObject and pipe the unified data into Sort-Object using the expression {Get-Random} to randomise among the whole set rather than per-call implicit partitions.

You can try something like the following using dbatools.

$connections = @"
Server,Database
sql-01,Database_015
sql-01,Database_016
sql-02,Database_017
sql-03,Database_020
@" | ConvertFrom-Csv

$query_tmp = @"
select
    '{0}' as serv,
    '{1}' as db,
    SrcFilename,
    DestFilename
from dbo.CAD_DATA
where UniqueValue = 1;
"@

$table = foreach($conn in $connections){
    $query = $query_tmp -f $conn.Server, $conn.Database
    Invoke-DbaQuery -Query $query -Server $conn.Server -Database $conn.Database
}

$table | Sort-Object {Get-Random} | Export-Csv ~/Desktop/myquery.csv

If you don't have access to / can't be bothered with dbatools, you may be able to use Invoke-SqlCmd in lieu of Invoke-DbaQuery


Create trigger base on time and insert to another table How to create a trigger for my case?

A table has columns id, value and datetime.
I want to create a trigger; when data is more than 1 week old move it to another table (historical); data less than 1 week old will be remain in the current table (live table)

It could go something like this. Grab the old data into a temp table. Insert it into the history table then delete from the live table. I'll leave the error handling as an exercise for the reader.

Begin Transaction archive

Select id, value, [datetime]
  Into #historical_to_archive
  From Live_Table
  Where [datetime] < DateAdd(Day, -7, GetDate());

Insert Into History_Table (id, value, [datetime])
Select id, value, [datetime]
  From #historical_to_archive;

Delete
  From Live_Table
  Where id In (Select id From #historical_to_archive);

Drop Table #historical_to_archive;

Commit Transaction archive


Running queries against multiple servers I want to use a Central Management Server, with a server group underneith, to run management queries against multiple server.

I know how to run a query against the whole group through SSMS, but I want to schedule queries to run through server agent.

How do I do this?

What you're looking for it sounds like is the Target Servers of a job. You can add your servers to this portion so that the job is run against your desired servers.

BOL documentation on Target Servers: http://msdn.microsoft.com/en-us/library/ms189553.aspx


Table-valued Data in Common Table Expression Most other databases allow you to generate table values using VALUES. As far as I can tell, SQL Server lets you do this with subqueries:

SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', 'cornelius@example.net'),
        ('Ebenezer','Splodge', 'ebenezer@example.net'),
        ('Sylvester','Underbar', 'sylvester@example.net'),
        ('Cynthia','Hyphen-Smythe', 'cynthia@example.net')
) as sq(givenname,familyname,email);

but I can’t appear to get it working with CTEs. The best I can do is embed a subquery inside a CTE:

WITH data AS (
    SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', 'cornelius@example.net'),
        ('Ebenezer','Splodge', 'ebenezer@example.net'),
        ('Sylvester','Underbar', 'sylvester@example.net'),
        ('Cynthia','Hyphen-Smythe', 'cynthia@example.net')
    ) as sq(givenname,familyname,email)
)
SELECT * FROM data;

I prefer CTEs because of their readability, but is there a more direct way of doing this? For example PostgreSQL, MariaDB (supporting versions) and SQLite all accept this:

WITH data(givenname,familyname,email) AS (
    VALUES
        ('Cornelius','Eversoe', 'cornelius@example.net'),
        ('Ebenezer','Splodge', 'ebenezer@example.net'),
        ('Sylvester','Underbar', 'sylvester@example.net'),
        ('Cynthia','Hyphen-Smythe', 'cynthia@example.net')
)
SELECT * FROM data;

No, SQL Server only currently supports table-valued constructors in a derived table (or the VALUES clause of an INSERT ... VALUES statement). So, you can use VALUES in a join (including apply) or derived table ("subquery") but not a Common Table Expression.

Sadly, T-SQL is lagging in several areas compared with other popular SQL databases, for example Add support for ANSI standard row value constructors.


Ola Hallengren solution, backups not cleaning up I have about 30 SQL Server 2005-2014 instances, all of which use the excellent Ola Hallengran maintenance script to produce my full/diff/log backups. This has worked for me for years with no issues, until recently.

One of the servers has two instances. In the case of the default instance on that server, the cleanup of the .bak files does not occur.

I wouldn't assume this has anything to do with the script, but rather something else like perhaps the .bak files are locked when the cleanup occurs. I have changed the scheduling of the backups in case the backup window is clashing with another process, to no avail. None of the other instances have this issue, including the named instance on the same box.

I know for a fact this is not a permissions issue, as the instance's Agent runs under a domain account with full control of the backup target volume, which is a dedicated share on a physical server, RAID 5 (unfortunately). It has group policy permissions as follows:

bypass traverse checking
log on as batch job

I have checked all the event logs on the backup server and see nothing that would help me troubleshoot it.

Can anyone offer any advice as to how I might be able to troubleshoot this issue?

Thanks

Update:

Date/Time on Server is consistent with the DC and my workstation. The backup server also has perfect time sync so definitely not a time slippage issue.
The @cleanupTime is just as I'd left it (72 hours).
I had initially assumed tape backups of that volume might be the culprit, but have since checked and triple checked that the files are not being locked - our Veeam backup of that volume only happens once a week.

I can delete the files manually with no issues.

On your advice I have added -o to the end of the command to get visibility on how the commands look. The only thing I think might be potentially the cause is:

  Date and time: 2015-10-20 11:29:58
  Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\backupServer\Production\SQLInstance01\Reporting_Database\FULL', 'bak', '2015-10-17T03:29:58' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)
  Outcome: Succeeded
  Duration: 00:00:00

So the command appears to be using a UNC path which is not terminated with a backslash. I have read elsewhere that the xp_delete_file SP requires a backslash:

https://stackoverflow.com/questions/24582996/sql-server-xp-delete-file-parameters

So this is probably a red herring but based on the -o output, the xp_delete_file SP wouldn't work correctly. I'm going to check the output on another instance which doesn't exhibit this problem to compare. Thanks for your suggestions.

Update#2:
I have added the -o to my command on another instance which does correctly cleanup files and found that the output for this also does not have a trailing \ in the output file, so I'm still at a loss.

Update#3:
I tried deleting the filed by dropping the command from the output file into SSMS. What I have found is that the command works correctly on any other instance, just not this one. **It says "Command Completed Successfully" but no files are deleted. Is there an instance-level setting that would enable/disable the ability to use xp_delete_file?

This is a list of random things that come to mind, I can't guarantee any are the problem.

DateTime on Server has been knocked out
DateTime on the Backup store has been knocked out
Someone has changed the @CleanupTime on your agent job (potentially accidentally)
There is a backup of your backup location occurring while the cleanup is trying to run locking the files (have had this one myself)

Since you've said that this is a recent problem not a just happened I'm presuming you can still delete the files manually which would indicate they are not in permanent use.

It may be worth running the -o command at the end to write the output of the command to a file ( where it is '-b' chance to '-b -o C:\Logs\backuplog.txt') for example and consult that for if it is bringing back any problems


Oracle 12c installation I have Oracle 11g installed on my system and want to install 12c now. Read articles (Oracle docs and general) which suggest that I can do so in different homes. But, when I try to install 12c (12.1.0.1 release 1), it does not allow me to do so as it says "oracle _home (in environment variables) already defined and does not match the path specified (during installation)".
A search on this portal gives results that do not answer my query.

What path should I specify for software location (Oracle home) during installation? (It auto reflects the server path of 11g installed)
Do I need to manually add this specified path to Oracle_Home path (in addition to the already existing oracle home for 11g) in environment variables for the installation to auto-accept it and allow further installation?

How do I get around the installation?

Docs don't just "suggest" that you "can" install into a new location.  It is required.  Two different installations must be in their own ORACLE_HOME directories.  And you do not "add" another path/directory to ORACLE_HOME. ORACLE_HOME specifies only one home/directory at a time.

To quote from the Installation Guide (emphasis mine):

  "Do not install Oracle Database 12c software into an existing Oracle
  home."

Also, the installation guide specifically says to unset ORACLE_HOME and several other environment variables before launching the OUI.

  If you have had an existing installation on your system, and you are
  using the same user account to install this installation, then unset
  the ORACLE_HOME, ORACLE_BASE, ORACLE_SID, TNS_ADMIN environment
  variables and any other environment variable set for the Oracle
  installation user that is connected with Oracle software homes.


How to connect two PostgreSQL databases in two different locations There is a project running on PostgreSQL with postgres_fdw. Due to legal reason some user data should be stored only in the remote db on the remote server in the closed country. Now the table that is connected to the remote server becomes unavailable when we lose connection to the remote database and storage.
The main database must be available even if the remote database crashed. It should not crash the main database.
How to connect two different databases from different locations to ensure data integrity (now it implemented on postgres_fdw)?

And the main requirement of our customer, the data should storage at a remote database only.

A materialized view not suitable, because it stores physically data at main server side.

Any ideas?

I would create a materialized view on the foreign tables.

You can refresh the materialized view while the remote database is available and use it in between.


sp_WhoIsActive waiting resource I have a huge query in SQL Server 2016 – when I run the query for a subset of records, it completes in 15 minutes. When I extrapolate it to total number of records, it is supposed to be complete in 1 hour. But it is not complete even after 3 hours.  When I checked  master.sys.sysprocesses there is a CXPACKET wait with 10293331 waittime

SELECT waittime, waittype, lastwaittype,  open_tran, cpu, physical_io, [sid], loginame, [sql_handle]
FROM master.sys.sysprocesses WHERE SPID=58

Note: I did exec sp_Who2 to get the specific SPID=58

From How to Use sp_WhoIsActive to Find Slow SQL Server Queries

  @get_task_info = 2 – if a query has gone parallel and you’re troubleshooting CXPACKET waits, you can figure out what each task in the query is waiting on.

I executed sp_WhoIsActive with @get_task_info = 2.

The wait_info gives the following:

(5x: 68/168850/635541ms)CXPACKET:15, (3x: 3/16/32ms)CXPACKET:7, (1x: 10207374ms)CXPACKET:1

How to determine what each task is waiting on from this information?

UPDATE

This question is more about finding a "way" to figure out the waiting resources (using sp_WhoIsActive or similar) than actually fixing this current performance issue by adding MAXDOP or analyzing execution plan.

This question is more about finding a "way" to figure out the waiting resources (using sp_WhoIsActive or similar) than actually fixing this current performance issue

If you're looking for what these numbers mean then, check out the sp_whoisactive Documentation.

From Adam Machanic's the page on the CXPACKET node identifier, you can see that this line in your output:

  (5x: 68/168850/635541ms)CXPACKET:15
  (3x: 3/16/32ms)CXPACKET:7
  (1x: 10207374ms)CXPACKET:1

Means that there are

5 tasks waiting on CXPACKET in node 15 of the execution plan
3 tasks waiting on CXPACKET in node 7 of the execution plan
5 tasks waiting on CXPACKET in node 1 of the execution plan

The 3 numbers separated by slashes are documented here:

  (Nx: MINms/AVGms/MAXms)[wait_type] where Nx is the number of tasks, MINms is the shortest wait duration of the tasks, AVGms is the average wait duration of the tasks, and MAXms is the longest wait duration of the tasks

The output of @get_task_info = 2 is telling you

where exactly in the execution plan your query is getting stuck
how many tasks are getting stuck there, and
how long they're stuck there (min, max, and on average)

Hopefully this provides enough information for you to be able to find which part of your query is getting stuck on these parallelism waits so you can investigate further.


All instances upgrade from Command Prompt I want to create a cmd script to upgrade sql servers in the organization(mostly 2014) to 2016. I've read Microsoft documentation
Install SQL Server 2016 from the Command Prompt regarding this issue.
The problem is that there is no option written there to upgrade all server instances like there is in installing updates: Installing Updates from the Command Prompt using /allinstances.
Is there any way to auto upgrade all instances on the server using one command line?

You can first download the SQL server 20XX edition of your needs here.
Then you can download and extract the local content media on your machine.
After that you should see in the downloaded folder the SETUP.EXE file
Then you can open the command prompt and type (By considering your sql standard instance name is MSSQLSERVER):
C:\Path_to_file\SETUP.EXE /QS /IACCEPTSQLSERVERLICENSETERMS /ACTION=UPGRADE /INSTANCENAME=MSSQLSERVER

When you connect to your database, you should see the new version installed
. In my case I upgraded from SQL server 2017 to SQL server 2019. Old Maj version was 14.XX, now the new Maj version is 15.XX. See below.

And there are plenty of cool explanations on this page
EDIT: I think its not possible to upgrade all instances at one with the allinstances parameter, because not allowed (Tried it).
Here is what the command line says:
 ALLINSTANCES                 Specifies that all instances are to be included
                              in the Setup operation. This parameter is
                              supported only when applying a patch.

Bu you can loop with powershell on all your instances with the above command. If you dont have 1 million instances, it should be fine.


Why isn't space deallocated after deleting all rows from a clustered table? Today one of our queries has gone crazy. It was hanging for more than an hour before I killed the process.

UPDATE  tc
SET tc.Status   = 0
FROM    #RC_Task_Calc   AS tc
CROSS
APPLY   (   SELECT  TOP 1
            _tt.CalcStamp
        ,   _tt.TrDateInto
        FROM    dbo.TableName   AS _tt
        WHERE   _tt.TableId = tc.TableId
        AND _tt.EventKind   = tc.EventKind
        AND _tt.DayDate <= @DayDate
        AND _tt.Status  = 1
        ORDER
        BY  _tt.DayDate DESC
    )   AS tt
WHERE   tc.CalcStamp    = tt.CalcStamp
AND tc.TrDateInto   = tt.TrDateInto

It was expected to perform about 600k loops to dbo.TableName which had no rows. But something went wrong.

CREATE TABLE [dbo].[TableName](
    [Id] [dbo].[TBigInt] IDENTITY(1,1) NOT NULL,
    [TableId] [dbo].[TRowId] NOT NULL,
    [EventKind] [dbo].[TRowId] NOT NULL,
    [DayDate] [dbo].[TDate] NOT NULL,
    [CalcStamp] [varbinary](8) NULL,
    [TrDateInto] [dbo].[TDate] NULL,
    [Flag] [dbo].[TFlag] NOT NULL,
    [Status] [dbo].[TTinyInt] NOT NULL,
    [Stamp] [timestamp] NOT NULL,
    [CreatorId] [dbo].[TRowId] NOT NULL,
    [Created] [dbo].[TDateTime] NOT NULL,
    [ChangerId] [dbo].[TRowId] NOT NULL,
    [Changed] [dbo].[TDateTime] NOT NULL,
 CONSTRAINT [ITableNameId] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
),
 CONSTRAINT [ITableNameIdentity] UNIQUE CLUSTERED
(
    [TableId] ASC,
    [EventKind] ASC,
    [DayDate] ASC
)
)

I saw in properties of dbo.TableName (using SSMS) such information:

  Data space ~ 129 MB

  Index space ~ 49 MB

  Row count = 0

After rebuilding indexes on the table the query completed in less than a second

ALTER INDEX all ON [dbo].[TableName] REBUILD

I'm aware of removing all data from the table using a DELETE command yesterday. But why did I have to rebuild indexes so that the space was deallocated?

The table is clustered so it shouldn't have heaps' problems on deletion. All pages should have been empty after deletion because all rows was removed. I thought it could be linked to mixed extents, but is_mixed_page_allocation_on=0 for that database.

So could someone explain me what I missed here? Why those pages weren't deallocated? Or maybe I'm a victim of some misconception about the deletion from clustered table process.

  Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64)
    Nov  3 2018 00:01:54    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server
  2016 Standard 10.0  (Build 14393: )

I was able to reproduce described behavior. I created the same table, fill it with data and then delete all rows.

After deleting I had a look at indexes:

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TableName_Test'), -1, NULL, 'DETAILED')

Results (I hid columns which don't matter):

Thank you,  Scott Hodgin and Erik Darling, you both were partly right and direct me to  the right way in my researches. The issue occurs because Ghost Cleanup process can't clean the version ghost records.

The behavior is similar to one described here: Problem With Too Many version_ghost_records.

Unfortunately, I can't call exactly what is root cause of being the versioned rows still held. I have not found any long transaction on any database which could cause it.

Index rebuilding and sometimes (as told in the article linked above) server restart help to solve the issue.


Why B-tree indexing is used instead of hash based indexing? I have an employee table which has index on name. I want to understand how Oracle works at high level

My understanding based on resources mentioned below:-

DB constructs the index table/map where it will keep the name in sorted fashion with memory location as the value.

Question :-

My question is will oracle bring all index table/map entries in memory from disc and then do the binary search  or is it the search at disc level without bringing all index table records in memory?
If I compare this approach with Java based data structures, Oracle is maintaining the TreeMap of column where key is employee name and value is memory location of that entry. So to find any entry, look up time will be log(n). My question why not DB/oracle takes hash based approach where it keeps the hashtable where it calculate the memory location based on name value and put the entry there. So lookup time to find entry will be O(1) .

Resources I referred

"Indexes and Index-Organized Tables" from the Oracle manual
How does database indexing work

Oracle will not read all the index; only the index blocks needed to satisfy the particular query. The number of blocks will depend on the depth of the index; rarely more than three have I seen in practice, for a single lookup.

Here is an example, using a big version of the EMP table. BIGEMP contains just under 2M rows:

select count(*) from bigemp
;

  COUNT(*)
----------
   1835008

I have created a unique index, called IDX1 on empno. Now I can examine the BLEVEL (which is what I was referring to as depth above) and the number of leaf blocks:

 select index_name, blevel, leaf_blocks
 from user_indexes
 where index_name = 'IDX1'
 ;

INDEX_NAME               BLEVEL LEAF_BLOCKS
-------------------- ---------- -----------
IDX1                          2        3947

So this tells me that this index has a root node, and then two level below that. So this mean that to find a unique row from the table, it should only take 4 IO's. That is the index root block, the two leaf blocks, and then another IO to the table to get the actual rows. This can be verified using autotrace

SQL> set autotrace on
SQL> select empno, ename, sal
  2  from bigemp
  3  where empno = 12345
  4  /

     EMPNO ENAME             SAL
---------- ---------- ----------
     12345 Scott            3000

Execution Plan
----------------------------------------------------------
Plan hash value: 3460625844

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    15 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIGEMP |     1 |    15 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX1   |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=12345)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
        682  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Sure enough, 4 consistent gets.

Oracle does also support "hash indexing" - called hash clusters. However, for transactional systems, there is a big maintenance penalty on them, so they are rarely used in these systems.


Cannot set MEMORY_TARGET for Oracle 11.2 I'm trying to configure an Oracle installation on a developer machine (Windows 7 Professional 64bit).

The machine has 16GB of RAM and I'd like to configure MEMORY_MAX_TARGET to 8g but have MEMORY_TARGET set to 4g by default because that is enough for most of the work done on that machine. But in case I need more memory I can dynamically resize it.

However Oracle won't let  me set MEMORY_TARGET to 4G:

Here is my current configuration:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 8G
memory_target                        big integer 0
parallel_servers_target              integer     64
pga_aggregate_target                 big integer 250M
sga_target                           big integer 0

SQL> show parameter policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      MANUAL
workarea_size_policy                 string      AUTO

Now when I try to run

SQL> ALTER SYSTEM SET MEMORY_TARGET=4G scope=both;
ALTER SYSTEM SET MEMORY_TARGET=4G scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least
6432M

I don't understand why MEMORY_TARGET can't be set to 4G? From all I know, it cannot exceed memory_max_target, but 4G is clearly less than 8G (and where do the 6432M come from?)

According to the manual this should be possible. Additionally I wasn't able to set PGA_AGGREGATE_TARGET to zero though - even if the manual claims that this is possible.

What am I missing here?

First look what other parameters are set:

CREATE pfile='pfile.txt' FROM spfile;

Maybe db_cache_size, shared_pool_size, sga_target or other memory related parameters are set to non zero? Remember that when using AMM those parameters specify minimum memory allocated for particular pool. So if sga_target is 6GB you will not be allowed to set memory_target to 4GB.

Also sum of internal variables __sga_target, __db_cache_size, etc. may be more than your specified value of 4GB.

If you see those symptoms you can cleanup pfile bounce Oracle with pfile and recreate spfile. In the same step you can also set PGA_AGGREGATE_TARGET to zero.

STARTUP pfile='pfile.txt'
CREATE spfile from pfile='pfile.txt';


Using a combination of block storage and object storage for serving photos. Good idea? I have a real estate app and I need to create a database for listing photos.  These photos will be actively accessed by users.

The full-size photos are about 50-70KB in size.  I will create thumbnails for the first photo of each listing, which will be about 20KB.

Here's my plan:

Store thumbnail photos directly on server (block storage), for faster access
Store all the full sized photos in object storage

Is this a good optimization idea?  Am I adding unnecessary complexity?  Should I simply store all photos in object storage?

The problem with block storage is that you loose all the benefits of integrity constraints - photos will get forgotten when listings are deleted etc...

Block storage is likely to be faster and is easier to diversify if there are speed problems,  so your trade-off is speed and scalability vs ease of development and management.


pg_basebackup fails with " too many connections for role "replication"" I am trying to set up a standby server and keep getting this error. My primary server has more than enough connections to handle the load:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 10
max_connections=100
checkpoint_segments = 8
wal_keep_segments = 8
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'

This is the command that fails on the standby server:

pg_basebackup -h ${MASTER_PORT_5432_TCP_ADDR} -D ${PGDATA} -U ${REP_USER} -vPw --xlog-method=stream

I don't understand why this happens.

Per comments, pg_roles.rolconnlimit is set to 1 for that role so it needs to be increased a bit to allow for several simultaneous connections.

Choose a more reasonable value, say 5 (or -1 for unlimited) and issue as superuser:

ALTER ROLE replication CONNECTION LIMIT 5;

...or connect with a different database user for pg_basebackup.


TSQL to get the list of associated users from all databases for SQl server Login I am looking for tsql query to get all the associated users in all dbs for a login.
Tried below SQL, but this is not giving right result set.
If I execute this query under the Database context where the user is present, it gives output as if user is present in all DBs.
Same query executed under the Database context where the user is absent, gives 0 rows as if user is not present in any of the DBs.
exec sp_msforeachdb 'select  ''?'' AS DBName,dp.name as username from sys.database_principals dp join sys.server_principals
srp on dp.sid=srp.sid where srp.name=''domain\account'''

Thanks

You can use the following Dynamic SQL to build the query you need to hit all databases and get a list of Users:
DECLARE @LoginName AS NVARCHAR(100) = N'Your Login Name Here'
DECLARE @DynamicSQL AS NVARCHAR(MAX) = N'';

SELECT @DynamicSQL = @DynamicSQL +
    'SELECT ''' + [name] + ''' AS DatabaseName, [name] AS UserName FROM [' + [name] + '].sys.database_principals WHERE [type] IN (''S'', ''U'', ''E'', ''G'') AND [name] = ''' + @LoginName + ''' UNION ALL '
FROM sys.databases
WHERE [state] = 0; -- Online databases only

SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10); -- Cleans up unneeded trailing "UNION ALL"

-- PRINT @DynamicSQL -- Prints the dynamic SQL string (For Testing)
EXEC master.sys.sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL string

I based it off the query Aaron Bertrand is using in the answer that @Learning_DBAdmin linked, but I figured re-writing my own Dynamic SQL could make it a little more readable. (And as he mentions sp_msforeachdb is undocumented and unreliable, so better to query dynamically.)
This can be used to get SQL Users, Windows Users, Azure Active Directory External Users, and Windows Groups based on the Type column in sys.database_principals (you can adjust this as needed).


MongoDB is not using the index I created for this specific query MongoDB 4.0.

I have a simple query:

db.product.find({
        modifiedOn: {
            $gte: ISODate("2019-08-02T03:00:00.000Z")
        }
    })
    .limit(10)

And a simple index on modifiedOn.

For some reason, the query is taking a long time to run against 40 million docs.

Explain says that the query is using a collection scan (COLLSCAN) instead of an index:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "dbname",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "modifiedOn" : {
                "$gte" : ISODate("2019-08-02T00:00:00.000-03:00")
            }
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 10,
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "modifiedOn" : {
                        "$gte" : ISODate("2019-08-02T00:00:00.000-03:00")
                    }
                },
                "direction" : "forward"
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "serverhost",
        "port" : 27017,
        "version" : "4.0.11",
        "gitVersion" : "417d1a712e9f040d54beca8e4943edce218e9a8c"
    },
    "ok" : 1,
    "operationTime" : Timestamp(1564756397, 18),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1564756397, 18),
        "signature" : {
            "hash" : BinData(0,"2D2uJyey5qPMYEk17CekUzpdqpU="),
            "keyId" : 6662358951796932610
        }
    }
}

This index took some hours to be created. Is there something I don't know with MongoDB that I need to do after creating an index to make it active?

Using explain("executionStats") I can see NO INDEX AVAILABLE FOR THIS QUERY.

Try using $hint in your query to enforce your optimizer to use this specific index for the query and post the output.

Thank you!

-Anban


Reorganize Indexes T-SQL I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on View T-SQL I noticed that it generates the T-SQL on the fly for all the indexes in the Database. If I copy the generated T-SQL I will have to maintain it every time I add a new Index.

Is there a way to do the same that the Task is doing by using T-SQL? Iterating over all indexes and creating the T-SQL for Rebuilding the index on the fly?

I recommend either of the following:

Michell Ufford's index defragmentation script
Ola Hallengren's index and statistics maintenance script

Either of these can accomplish the task of maintaining indexes without requiring you to rebuild the scripts when indexes are added or removed.

One of the biggest problems I've seen with using the maintenance plan is the fact that it appears to iterate through every single index and place the index rebuild into a single script. Then the script is compiled and finally run.

Now imagine that you have a 150 databases each with over 1000 indexes. This becomes a maintenance plan that takes 8 hours to run.


SQL Server switched to use pagefile exclusively instead of RAM and effectively froze the world I was hammering my SQL Server instance yesterday for about 3 hours running an intensive calculation which was consuming all allocated physical RAM (I have it limited to 2GB due to my limited workstation hardware), and suddenly it (and many other things?) released all their memory in an apparent switch to exclusively using pagefile.

My computer was lagging so incredibly after that I was forced to restart (which took 10 minutes just to access the power options).

I was able to get a screencap of task manager before restarting (which I leave running in the background with a low update speed). With the window resized to show the maximum history, this is what it looked like:

This surprising behavior caused downtime. Is this a documented feature?

I am running Windows Server 2008 R2 with an Enterprise 2014 database engine.

Thanks.

This surprising behavior caused downtime. Is this a documented feature?

Yes this is, its documented in RAM, virtual memory, pagefile, and memory management in Windows. Quoting from this suport article

  RAM is a limited resource, whereas for most practical purposes, virtual memory is unlimited. There can be many processes, and each process has its own 2 GB of private virtual address space. When the memory being used by all the existing processes exceeds the available RAM, the operating system moves pages (4-KB pieces) of one or more virtual address spaces to the computer’s hard disk. This frees that RAM frame for other uses. In Windows systems, these “paged out” pages are stored in one or more files (Pagefile.sys files) in the root of a partition. There can be one such file in each disk partition. The location and size of the page file is configured in System Properties (click Advanced, click Performance, and then click the Settings button).

The reason why it froze because disk speed is much much slower than speed in memory. After paging portion of disk was behaving like RAM.

To find out how much memory was paged at given time you can use DMV sys.dm_os_process_memory. Look at difference of column physical_memory_in_use_kb and virtual_address_space_committed_kb. The difference of these two columns would give you SQL Server using paged memory.

select (virtual_address_space_committed_kb/1024) as Total_Memory_Used_MB, --Ram+page file
(physical_memory_in_use_kb/1024) as Total_physical_memory_used_MB ,--PhysicalMemory Used
((virtual_address_space_committed_kb-physical_memory_in_use_kb)/1024) as Page_file_Utilization_MB
from sys.dm_os_process_memory.

You can to larger extent avoid this by giving SQL Server service account Locked Pages in Memory Privilege. This will not allow SQL Server memory to be paged BUT still windows can ask SQL Server to trim its memory utilization uptill min server memory after that if there is still a severe memory pressure OS process will be paged and this can even lead to unexpected OS shutdown so be careful. Better solution is to add more RAM, in this case specifically.

EDIT: From your comment

  I already limit the entire SQL Server instance to 2GB of memory

Ohh!! that is really too small amount of memory for SQL Server to work and that too when you are doing some severe testing


Microsoft SQL Server 2016 performance degradation for stored procedure called 1000's of times Context :

Small table, stored procedure to update table, first two thousand hits no problem. After those first hits it starts to crawl to a snails pace. This stored procedure has no problems in our current environment of SQL Server 2008 R2 with the calling C# app. In our new testing environment for SQL Server 2016 we see this problem. The offending section of the query deals with a where clauses that has four comparisons of a column not equaling a param for an update of the small table:

WHERE A.Column1 <> @param1 OR A.Column2 <> @param2     etc..

My assumption was parameter sniffing but have ruled that out with various query hints.

I have also tried:

Changing to select not update
Playing with degrees of parallelism (MAXDOP)
Updating stats and recompiling, even doing it mid process
Various changes to the indexes
Resetting connection pooling in calling C# code after X amount of iterations
Removing any transactions controls

The only change that seems to remedy the issue is to remove the where clause and push the logic to the setter:

SET A.Column1 = CASE
        WHEN A.Column1 <> @param1
            THEN @param1
        ELSE A.Column1
        END

I don't like having to change the code, yet I have found no other resources online that could answer why SQL Server 2016 would break.

Any help would be appreciated.

In SQL Server 2014 & up, new cardinality estimation logic was introduced.

From BOL :

  The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator.

Recently, we upgrade from SQL server 2012 to SQL server 2014 and got hit by the new Cardinality estimator short coming - queries were timing out, cpu pegging close to 100%.

After much troubleshooting, updating stats, rebuilding indexes, doing query plan analysis, we figured out that changing compatibility level to sql 2012 works well.

Paul White explains - Cardinality Estimation for Multiple Predicates

  Selectivity computation in SQL Server 2014 behaves the same as previous versions (and trace flag 4137 works as before) if the database compatibility level is set lower than 120, or if trace flag 9481 is active.

So my advise would be

For small number of queries revealing the problem, use QUERYTRACEON(9481) hint.
If you dont want to gamble, then just have the trace flag TF9481 as a start-up parameter, so it gets persisted during server restarts.

Note: Enabling TF 9481, you dont need to set the compatibility level of the database to a lower level.

From KB2801413 :

  9481   :Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.

As a side note, along with proper testing -  you also want to look into TF4199 (Think of it as a master key to turn on every fix for the query optimizer). TF4199 behavior changes with sql server 2016.. TF4199 helped in my environment at lot and is by default ON for all new installations.

In SQL Server 2016, you dont need to turn on the trace flag 9481 .

SELECT  name, value
    FROM  sys.database_scoped_configurations
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
-- if above is having value = 0, then set to ON
ALTER DATABASE
    SCOPED CONFIGURATION
        SET LEGACY_CARDINALITY_ESTIMATION = ON;
go

For Trace Flag 4199, you can just do

ALTER DATABASE
    SCOPED CONFIGURATION
        SET QUERY_OPTIMIZER_HOTFIXES = ON


Single node parallelism with PL/Proxy? I have seen some suggest that single node parallelism is possible, but all of the material I have found so far deals with parallelizing across nodes. I am interested in getting large static queries to exercise multiple cores when a query maximizes on a single thread.

In a DWH environment, I would like to use PL/Proxy to do the following:

develop functions that query a local database on a single node
individually querying partitions
aggregating the intermediate data sets into a single result

Is this possible?

Sure it's possible: check this blogpost http://www.depesz.com/index.php/2011/12/02/the-secret-ingredient-in-the-webscale-sauce/


Microsoft SQL linked server to Oracle data not updating I need to pull data from an Oracle database running on a remote machine and use it on my sql server instance. I need to check these tables periodically for updated or new records.

I have created a linked server connection to the oracle db. I am able to select data from tables using the following syntax.

SELECT * FROM OPENQUERY(CUSTOMER_HOST, 'SELECT * FROM dc_15.MISSIONPLAN')

This works, I get the data I expect. However I noticed when the data on the remote server changes (adding, removing or modifying records) the changes aren't selected. The same data is pulled as before. If I alter the table on the oracle db, and run the select again, I get the most recent data. However now the data seems to be frozen again in this state, unless I alter the table again.

Is there some sort of refresh I'm supposed to invoke on the sql server side? Or is there something I need to have triggered on the oracle db to say the data has been updated?

To interface with the DBs I'm using Miscrosoft SQL Server Management Studio, and Oracle SQL Developer.

I feel really stupid for not thinking about this earlier. I'm so used to SQL Management Studio not requiring changes to be committed. I also thought that the changes were being committed on oracle, because my select statements reflected the changes made.

I found the solution to my problem was upon changing data in Oracle SQL Developer I needed to explicitly

commit;

Now the updated data is available through my linked server.


Replication with fail over cluster in SQL Server 2014 Presently I am Using Replication Feature in SQL Server 2012 .I want to migrate to SQL server 2014 .I want to use fail over cluster feature .Does it effect the replication feature which we are using for our project

Yes you can upgrade without affecting current features .

To upgrade a SQL Server failover cluster to SQL Server 2014, you must run setup with upgrade action on each failover cluster node, one at a time, starting with the passive nodes. As you upgrade each node, it is left out of the possible owners of the failover cluster. If there is an unexpected failover, the upgraded nodes do not participate in the failover until cluster resource group ownership is moved to an upgraded node by SQL Server Setup.

MSDN


What makes an Oracle Applications DBA different from an Oracle DBA? All Oracle databases support some type of application that the Oracle DBA would need to have some knowledge of.  What makes an Oracle Applications DBA different than this?  Does the term mean that they support the Oracle Applications and not the database, or does it include responsibilities for both the Oracle Applications and the database.  If the latter then this seems to be done differently than every other type of application that can run with an Oracle back end.  For example, you don't see Oracle .NET DBAs.  Can someone clear up my limited understanding on this topic?

Update:
It seems very clear from the two answers so far that I did not word the question well enough.  My context is not Application DBA in the Developer DBA sense vs. Production DBA.  I am asking about the particular job title of "Oracle Applications DBA" or "Oracle Apps DBA".  Do a search for the phrase and you'll get results like the following:

http://becomeappsdba.blogspot.com/
http://appsdba.info/
http://teachmeoracle.com/interview.html
etc.

The best explination I have found so far is from http://www.club-oracle.com/articles/what-is-oracle-applications-259/ which says:

  Oracle Applications is an Enterprise Resource Planning Software, which
  is run by large industries to control their industry-wide operations.
  This software involves separate segments known as modules for separate
  business processes. Each module is basically a collection of forms
  (with some data displaying reports) where at each stage of the
  business process, data needs to be entered. The forms enforce industry
  best-practices controls on data input and process flow.

As your own question answers: "Oracle Applications is an [ERP]." An "Oracle Apps DBA" will be someone who is experienced in working with the particular schema and tooling around this ERP. There are hundreds (if not thousands) of packages, tables, stored procedures, and interfaces that are specifically built for the ERP. It is not a trivial database to admin. You can get a brief glimpse into some of the things in their ERP by looking around - for example, this blog - or by logging into a Metalink account as someone who is a customer of their ERP solution, where you can get ERDs and other documentation.


What causes log shipping copy error : "The process cannot access the file because it is being used by another process."? We log ship several DBs from primary to secondary server.  Looking at mdb.dbo.log_shipping_monitor_error_detail on secondary server.
Seeing this error pattern over and over many times a day..
Record 1 : Could not copy log backup file '\\SQLSERVER\LogShip\FOO\FOO_20201126153001.trn'. The copy operation will be re-attempted on the next run of the Copy job.

Record 2 : The process cannot access the file '\\SQLServer\LogShip\FOO\FOO_20201126153001.trn' because it is being used by another process.

Is this error simply caused because the tran file is still open at the time of the attempted log copy operation?  Is this expected behavior for log shipping secondary copy operation?

Found a post discussing how to address this issue :


importing database backup to phpmyadmin return #1064 When I tried importing mysql database backup to MySQL using phpMyAdmin
I got the following error message:

  #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
  near 'PK00PK' at line 1

In another post, it says open the sql document and delete weird lines and shall work
where is the sql document that i should open?
i m not a specialist in this.

  PK00PK�"�8B_�5=db.optKIMK,�)�M�H,JL.I-�-N-�--I��J�I���$�d�灅��S�R�s��3�PK�"�8:�h�n�"f2blog_attachments.frm��?k�@�'���K��K����Ԃ
  *-u(X� 5��bA���E�C�� �^���[|C��Cxɽ�ݷƙ�h��ׄZd�0���f���w�O��A�4@!�B!���L���_�+P'{h[�i=o����p�ċ[_��\p/|]��[��W���[1lɝM0��i�.�;��e�]j,�M]U��Ю�"�Yg�/�SM禉q)p<�7����瘖)���(�̧!�Q�|�0YL@�/��K�PK%�8RDE�DPf2blog_attachments.MYDc``���͍��-,��
  ҅ܟ��x�{��}˞m�
  ���MLO�/�*HM�Z�°���a%��K�PK$�8��Y��f2blog_attachments.MYI�������q���L@�����P#
  <0F̀�^�$�0�r����0\���>K(��� ���@��l��B -�\
  ��ؘA&%0,bdd�B{���JF�.LY�OX�(F=u��hL%X�Q0
  �P��(������d�h�2
  F�H��PK�"�8�^��y�"f2blog_categories.frm�ؽJ�P���G�{S�����c�P_����Rup,M�`M�����(�ѡ��E�|
  �'M�v'<�������ܡpϧP��p��M9�    ���

The database was created in linux and I'm trying to import it to MySQL using phpMyAdmin running in Win 7

Most likely your backup file is a zip file (judging by magic bytes PK at the beginning) that contains a binary backup of MyISAM tables (a set of files):

f2blog_attachments.frm
f2blog_attachments.MYD
f2blog_attachments.MYI
etc...

What you need to do to restore your backup

Unzip the archive file
Stop MySQL
Copy unpacked table files to a DB directory (or create a DB first) in the MySQL data directory
Start MySQL


Access denied to user ODBC, but trying to login as root I'm trying to do a backup of a MySQL database on Windows, but for some reason it's trying to log me in as ODBC instead of root - does anyone know why?

As you can see below the red line, I can login to MySQL no problem.

The only thing I can suggest is to explicitly choose a host by doing one of the following:

mysqldump -hlocalhost -uroot -ppassword ideal_inv > ideal_inv.sql

mysqldump -h127.0.0.1 -uroot -ppassword ideal_inv > ideal_inv.sql


MySQL - Arrange a table so that it shrinks as much as possible? I'm a newbie and I have +10 million articles(200 to 1000 words) in a InnoDB table. I use this to select them:

SELECT article,title,other_fields from table where id=123;

There is no full-text search or any updates on article field.

How can I arrange the table so that the table size shrinks as much as possible. I'm happy with a little performance tradeoff. I'm sorry for the vague question but I couldn't find any answers online.

PS: There are inserts and updates on table, should I put article and id to a seperate table?

Here's general recommendations for keeping table size small

Try ROW_FORMAT=COMPRESSED when creating InnoDB tables. There will be impact on writes.
Use index with prefix
Use OPTIMIZE TABLE often if it gets many DELETE/UPDATE's

Normalizing tables would actually result in bigger overall size, but less. It MAY help with caching, though.


SQLServer:Buffer Cache Hit ratio is a good indication of POSSIBLE memory bottleneck? I am looking for an indication (if any) that would either disregard the memory bottleneck option, or accept it, or make me investigate further.

for instance:

page life expectancy:

SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

ON this occasion, on my current environment,
I have a server that has 8 GB of RAM,
I would ask management for more memory. I believe this is a memory bottleneck.

what is using the RAM would be another investigation.

is this email below valid to identify possible memory bottlenecks?

  we need to look at SQLServer:Buffer Cache Hit ratio

  If that ratio is less than 95% than the server is under memory
  pressure

  Hope this helps,

We need to look at SQLServer:Buffer Cache Hit ratio.If that ratio is less than 95% than the server is under memory pressure

Stop looking at Buffer Cache hit ratio to determine memory pressure. This is because with read ahead mechanism in SQL Server more than enough pages are alredy in buffer pool to satisfy the query so BCHR does not gives accurate figure about memory pressure. You might even see that BCHR even would not drop when there is memory pressure. All these has been explained in Great SQL Server Debate about BCHR

PLE output you posted seems really low but we cannot just use one counter to gauge memory pressure. PLE is more indication of I/O activity on server. It could be possible that due to heavy I/O activity PLE plummeted. If you note Target and Total server memory still remains same. Which is good sign.

For Edition upto 2008 R2. You can use below counters

SQLServer:Buffer Manager--CheckpointPages/sec:
SQLServer:Buffer Manager--Memory Grants Pending:
SQLServer:Buffer Manager--Target Server Memory:
SQLServer:Buffer Manager--Total Server memory
SQLServer:Buffer Manager--Free Pages
SQLServer:Buffer Manager--Free List Stall/sec
SQLServer:Buffer Manager--Page Life expectancy

For SQL Server 2012 onwards few of Buffer Pool counters are deprecated and so we have to use Memory Manger counters

SQL Server: Memory Manager-- Target Server Memory (KB)
SQL Server: Memory Manager--Total Server Memory (KB)
SQL Server: Memory Manager- Free Memory (KB)
SQL Server: Memory Manager--Database Cache Memory (KB)
SQLServer:Buffer Manager--Free Pages
SQLServer:Buffer Manager--Free List Stall/sec
SQLServer:Buffer Manager--Page Life expectancy

Note if you have heavy disk activity do not forget to refer Disk related counters as well. Create a data collector set and allow it to run for 4-5 hrs when load on system is at peak and then add the snapshot of data collector in your question. Then we can determine whether SQL Server needs more memory or not.

Personally 8G is a bit of less RAM considering workload and OS requirements these days. At the back of your head you should always think about increasing RAM.


Space Calculation for SQL Server Table: What's wrong with my query? I'm using the following query in order to calculate some space related measurements on a particular table in my SQL Server database:
SELECT
    t.name AS TableName,
    p.rows,
    (sum(a.total_pages) * 8) as reserved,
    (sum(a.data_pages) * 8) as data,
    N'Not Needed' as index_size,
    (sum(a.total_pages) * 8) -  (sum(a.used_pages) * 8) as unused
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = N'XYZ'
GROUP BY t.name, p.rows
ORDER BY
   1 Desc;

It gives me correct results for calculating number of rows, reserved space, and unused space. However, when I compare its output with the output from running sp_spaceused stored procedure, I observe a different value for space used by data:

How can I fix it?

The documentation for sys.allocation_units  is incorrect, it seems. It states that data_pages is number of used pages and it should include LOB pages.
LOB pages are pages for data types such as the (MAX) types, XML, geospatial and columnstore data.
But, looking on my machine (SQL server 2019), I see 0 data pages for LOB data. If you remove the GROUP BY and look at the individual rows, you'll see what I mean. So, you would have to CASE whether it is LOB data and use some other column in that case.
I've used the FactResellerSalesXL_CCI in the AdventureworksDW2016 database for my test. Adjust to your liking. I parameterized the schema name and table. See the discrepancy between the resulting column names data (your original calculation) and data_corrected (my adjusted calculation):
DECLARE @s sysname, @t sysname, @f nvarchar(256)
SET @s = 'dbo'
SET @t = 'FactResellerSalesXL_CCI'
SET @f = QUOTENAME(@s) + '.' + QUOTENAME(@t)

SELECT
    t.name AS TableName,
    a.type_desc,
    i.index_id,
    p.rows,
    a.total_pages * 8 as reserved,
    a.data_pages * 8 as data,
    (CASE WHEN a.type_desc = 'LOB_DATA' THEN a.used_pages ELSE a.data_pages END) * 8 as data_corrected,
    N'Not Needed' as index_size,
    a.total_pages * 8 -  a.used_pages * 8 as unused
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = @t
ORDER BY
   1 Desc;

I leave it to you to bring back the aggregation. :-)


Trusted Constraints and NOT FOR REPLICATION Version and Build:  SQL Server 2005 SP4 (9.0.5000)

As a disclaimer of sorts, I am asking this question to generate some discussion and/or debate on the topic.  I don't know if there is really a correct answer, or if the correct answer is a big fat "It Depends".  As such, I will be pretty slow to accept an answer, unless definitive evidence is presented that cannot be countered by the opposing view.

The issue deals with the OBJECTPROPERTY CnstIsNotTrusted and the OBJECTPROPERTY CnstIsNotRepl, and how they relate.  The CnstIsNotTrusted property is controlled with the WITH CHECK|NOCHECK while adding or checking a constraint.  The CnstIsNotTrusted property means that the constraint was enabled without checking existing rows; therefore, the constraint may not be true for all rows.  When true, the constraint cannot be used for query optimization.  For example, I have a Person table with a Check Constraint on LastName ensuring LastName LIKE 'Mc%'.  If CnstIsNotTrusted = 1 then if I search for LastName = 'Smith', the query plan will still need to scan/seek the index.  If CnstIsNotTrusted = 0 then the execution plan won't even touch the table.  This has been proven here:

https://sqlserverfast.com/blog/hugo/2007/03/can-you-trust-your-constraints/

The OBJECTPROPERTY CnstIsNotRepl (NOT FOR REPLICATION in DDL) enforces that the constraint will not be checked during replication synchronizations.  More accurately stated, the publisher side will be checked, but the subscriber side will not be checked.  This ensures that the same constraint isn't checked more than once.  However, using NOT FOR REPLICATION will render your constraint untrusted.

My question is, is it worth the extra CPU cycles to not use NOT FOR REPLICATION, in order to sometimes receive a more efficient execution plan?

NOT FOR REPLICATION was indended for this scenario:

You have a Order table with a FK to Person table.
Order table is in publication no.1 and Person table is in publication no.2.
The Order pub and Person pub will happen at different times. Therefore if some new records were in the Order table were replicated down to the subscriber and the Person table was missing the person that new Order was for, you don't want the replication insert to fail at the subscriber. Instead, it can be trusted that the new Person record will come down at some point because the FK was used at the Publisher.
At the subscriber, if you insert an Order record, the FK will check that the Person record exists.

I would always stick with this policy and use NOT FOR REPLICATION only for that purpose of FK's relating to tables in different publications, and not for the purpose of query plan optimisation.


How to speed up datetime MySQL queries or improve db scheme for time series data? I'm suffering a hard time because of a university project. The goal is to retrieve measurement values from a field test and to store them in a db. Upon those stored values calculations shall be executed...

The current DB scheme has one table for incoming data values, called data:

+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | bigint(20) | NO   | PRI | NULL    | auto_increment |
| data_node_id | bigint(20) | NO   | MUL | NULL    |                |
| timestamp    | datetime   | NO   | MUL | NULL    |                |
| value        | float      | YES  |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+

This table holds about 1.7 billion records so far and is ~165GB in size.

Now I'm trying to do some simple queries to extract data for research like:
SELECT timestamp, value FROM iren2.data WHERE data.timestamp >= now() - INTERVAL 7 DAY AND data_node_id = 114
Those queries take more than 10 minutes to finish :( Is there any way to speed up things? Did I miss something in the scheme design?

By the way, the db is a MySQL 5.7 Community Edition running on a Ubuntu 16.04 HyperV machine with 10 vCores (Intel® Xeon® Processor E5-2698 v3) and 256GB Memory.

Thanks for your help! :)

EDIT
Some more information on config:

innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 216G
innodb_thread_concurrency = 0
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

You may need to add proper indexes to your data... My guess is that you need a multi-column index on (data_node_id, timestamp).

Check the mySQL documentation on Multiple-Column Indexes

--

Side note:

I suspect that a data_node cannot have two readings at the same time. That would make the (data_node_id, timestamp) pair always unique (and not null). If this were the case, I would use this as the primary key, and take out of the table the id column (and the corresponding index). Taking out the surrogate id key will save some data that doesn't need to be read... if you're looking for the last bit of performance, it also helps.

As mySQL + innoDB keeps your data clustered by primary index, having (data_node_id, timestamp) as your PK would clearly be an advantage for retrieving information. Like always, it comes with a price: INSERTS will cause fragmentation (and thus, be more expensive), because your PK will not be an increasing number.


/dev/shm size recommendation for postgres database in docker We have postgresql 11.7 database in docker container.
We had an issue "pq: could not resize shared memory segment "/PostgreSQL.XXX" to XXX bytes: No space left on device", described here.
Solution helped, we do not have an issue anymore. But which size should we set for shm_size for docker container with Postgres?
Is any recommendation? How can we calculate 'optimal size' for shm_size?

There are two things that use shared memory:

The shared memory that is allocated at server start. This consists of several parts, but the lion's share is shared_buffers, the data cache.
So you have to have more shared memory than that.

Shared memory segments allocated by parallel query to communicate between parallel workers.
Their size depends on the data transferred, and several of them can be allocated, so that is a resource that is harder to control.

On a memory-constrained system, I'd recommend that you disable parallel query. It makes queries faster, but uses more resources per query, so overall throughput doesn't improve. It is good if you want to throw a lot of resources on a single query.
To disable parallel query, set max_parallel_workers to 0.


Searching through ~1M slow I've never had a database with this many rows, so I can new to keeping the search time to a minimum.

I have a database table with 1.5M rows.

The primary key is an auto-increment id.

Then there is a composite key comprised of 4 of the columns, each row must be unique in that the 4 columns can not be exactly the same for 2 rows.

When inserting, there is an ON DUPLICATE KEY UPDATE to ensure this.

Here is a picture of the indexes on my temp server: http://i.imgur.com/IG27z5V.png, which is the same as the production server.

The php search function takes the search term and does a: (Using Laravel)

$parts = Parts::where('part_number', 'LIKE', "%$part%")->get();

There is no other index to search by when searching through the entire thing.

The search page is also SSL, which I assume is making it slower.

What recommendations do you have to speed this up?

column name like '%search%' cannot take advantage of standard BTREE/B+Tree indexes on MyISAM/InnoDB, so you are effectively doing a full table scan. The index is useless, so every single row has to be compared individually.

In order to perform string searches in a performant way you have to create a FULLTEXT index and use the operator MATCH ... AGAINST.

Alternatively, you can use an external indexing engine like Sphinx or Apache Solr.

Edit: Good source material on full text search: http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql


Error during pg_restore: text search dictionary "unaccent" does not exist I'm trying to move data between servers. I recreated the whole database structure, checked that same users exist and the unaccent extension that we use is enabled for all schemas of the target database.

When I try to run:

pg_restore -h %server_host% -a -c --disable-triggers -d %db_name% -U %user_name% 2014-12-09.custom

I get the following error:

pg_restore: [archiver (db)] COPY failed for table "contracts":
ERROR:  text search dictionary "unaccent" does not exist

What am I missing here?

It came out that there was a stupid hack, unaccent function was marked immutable and there was an index that used this unaccent function. Dropping the index and recreating it after backup did the trick.

Do not use hacks, folks :)


SQL Server - separate database for reports? On our SQL Server, we have a database for each of our web apps. For reports, we use Reporting Services and all report data (including report parameters) come from stored procedures.

The stored procedures are in the same database as the data in the report. So, for example, the procs that serve the Stock reports are in the Stock database. Some reports show information from more than one database and then the proc will be in one of those source databases. The report parameters get their data from procs in an Enterprise database that has data like stores, employees etc.

This means that all reports have at least a connection to the Enterprise database and another connection to another database -- and sometimes more than that.

My question is: is there a benefit of moving the reporting procs into a separate "Reports" database. I know the benefits of moving reports onto another server and I'm not talking about that -- this would be on the same server.

Things that might affect this are:

Does having more than one database connection for a report, affect the speed of the report?
Would having the reporting proc in a separate database from the data, prevent us from using indexed views?
Have you found it easier / harder is administer you reports in a separate database?

Please let me know what you think.

The answer is: yes, there is a benefit to doing it.  Reports on on operational database will use a lot of resources and will interfere with the performance of the operational system.  Remember that database performance is subject to mechanical constraints (disk heads moving back and forth and rotational latency as we wait for the right sector to make its appearance under the head).  You have two broad options for a reporting strategy:

Replicate your database onto another server and move the reporting sprocs onto it.  Reports are run off the replicated server.  This is the least effort and can re-use your existing reports and stored procedures.
Build a Data Warehouse that consolidates the data from your production systems and transforms it into a form that is much friendlier for reporting.  If you have a lot of ad-hoc statistical reporting that could be done acceptably from a snapshot as of 'close of business yesterday' a data warehouse might be the better approach.


Cannot Run SQL INSERT in MySQL Neither

[vagrant@localhost ~]$ sudo mysql -e "USE books; INSERT INTO authors (id,name,email) VALUES(1,"test","test@gmail.com");"
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com)' at line 1

Nor

[vagrant@localhost ~]$ sudo mysql -e "USE books; INSERT INTO authors (id,name,email) VALUES(1,"test","gmail.com");"
ERROR 1054 (42S22) at line 1: Unknown column 'test' in 'field list'

works for inserting values in MySQL, while the columns exist:

[vagrant@localhost ~]$ sudo mysql -e "USE books; SHOW COLUMNS FROM authors;"
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| email | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Version

[vagrant@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.73 Source distribution

You are using the wrong quotes for the SQL

SUGGESTION #1 : Use Single Quotes for the SQL

sudo mysql -e "USE books; INSERT INTO authors (id,name,email) VALUES(1,'test','gmail.com');"

SUGGESTION #2 : Escape Double Quotes for SQL with Backslash (\)

sudo mysql -e "USE books; INSERT INTO authors (id,name,email) VALUES(1,\"test\",\"gmail.com\");"

The confusion is caused by the double quotes you are using for the sudo and for the SQL

GIVE IT A TRY !!!


MySQL: will a transaction lock the row? I haven't tried using MySQL transaction before, I just want to clarify something.

If two users execute a query at the very exact time, how MySQL would handle this?
e.g. the users is trying to update a record.

user1: update table set column = column - 4 where column_id = 1;

user2: update table set column = column - 7 where column_id = 1;

Now if I use transactions, will MySQL choose which query will be executed first and lock the second user until the first query is committed? Will that be a table lock or a row lock?

What if a third user will issue a select statement? What will be the value MySQL will return?

P.S. this will be on Innodb.

A single statement like that works the same with MyISAM or InnoDB, with a transaction or with autocommit=ON.  It blocks enough to do the query, thereby blocking the other connection.  When finished, the other connection proceeds.  In all cases, the column is soon decremented by 11.

A third user may see the value decremented by 0 or 4 or 7 or 11.  The "very exact time" is not really possible because, at some point in the execution of each statement, a single-threaded lock is checked/set/whatever.  That is, they will be serialized, just so fast that you can't see it.

InnoDB locks only rows, not tables.  (OK, DDL statement do bolder locks.)

What gets more interesting is a transaction that modifies two things, or that takes a noticeable amount of time:

Intention Case: Single item but taking time:

BEGIN;
SELECT something;
think about it for a while
UPDATE that something;
COMMIT;

The select needs to be written thus:

SELECT something  FOR UPDATE;

This tells other connections "I intend to update the row; please don't mess me up".  (I bring up this example, because a lot of newbies miss this subtlety.)

Deadlock case:  Messing with 2 things:

BEGIN;    -- in one connection
UPDATE thing_1;
UPDATE thing_2;
COMMIT;

BEGIN;    -- in another connection, at the "exact same time"
UPDATE thing_2;
UPDATE thing_1;
COMMIT;

This is the classic example of a deadlock -- each grabs one thing and then reaches for the other thing.  Clearly it can't be made to work.  One transaction is killed; the other completes.  Hence, you must check for errors, so you can discover it.

The normal reaction to a deadlock is to replay the entire failed transaction.  By then, the other connection will not be interfering, and it should proceed without trouble.  (OK, yet another connection could create another deadlock.)

Delay Case: If the two connections grab multiple things in the same order, then one can be delayed until the other finishes.  To keep this from "waiting forever", there is a default 50-second innodb_lock_wait_timeout.  Your pair of simple UPDATEs is actually an example of this case.  One will finish promptly; the other is stalled until the first finishes.

Note how a Deadlock can (in some cases) be turned into a Delay by consistently ordering the things you touch.

autocommit=1:  With this setting and without calling BEGIN, each statement is effectively:

BEGIN;
your statement
COMMIT;

autocommit=0:  This is trouble waiting to happen.  When you perform a write query, a BEGIN is implicitly generated.  However, it is your responsibility to eventually issue COMMIT.  If you fail to do so, you will wonder why your system is hung.  (Another common newbie bug.)  My advice: "Never use =0".


How to do an in-place upgrade of SQL Server 2005 Express to SQL Server 2008 R2 Express I would like to upgrade my SQL Server 2005 Express instance which is installed using this command:

SQLEXPR.EXE/qs /action=INSTALL /IACCEPTSQLSERVERLICENSETERMS
   /FEATURES=SQLEngine /INSTANCENAME=ABCD
   /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /SECURITYMODE=SQL
   /SAPWD=abc#Wd1234 /TCPENABLED=1 /NPENABLED=1

but when I am trying to upgrade the server instance ABCD with this command

SQLEXPR_x64_ENU.exe /q /ACTION=upgrade /INSTANCENAME=ABCD /IACCEPTSQLSERVERLICENSETERMS=1

the instance ABCD is not upgraded to SQL Server 2008 R2 Express.

When I check ADD/Remove program list of OS, it shows me both SQL Server 2005 and 2008 R2 are installed (that may be I am doing a side by side upgrade but not a in-place one) and when I click on start button of OS and then goes to SQL Server 2008 R2 folder in AllPrograms list. It has a sub folder configuration tools which contains SQL Server Installation center. When I open the installation center it show various option to install SQL Server 2008 R2 or to upgrade from 2005 etc. I am confused whether SQL Server 2008 R2 is even installed on my machine after running the above 2nd query. Please help me where I am going wrong.

You have a 32-bit instance of SQL Server 2005 and are trying to upgrade to a 64-bit instance of a newer version. This is not supported; you can check any of the upgrade documents about this, e.g. http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx:

  Cross-platform upgrade is not supported. You cannot upgrade a 32-bit instance of SQL Server to native 64-bit.

It says stuff about WOW64 but my recommendation for your scenario is to install a separate 2008 R2 instance, backup/restore your databases from the 2005 instance (and transfer jobs/logins), then remove that instance. You will need to choose a different instance name or use a default instance.


SQL Server Performance This is rather a generic question.

I've always understood that SQL Server likes RAM, the more the better.

If for example I had a SQL Server with just a single 2GB database and 8GB RAM, would there be any performance benefit to upgrading to 16GB RAM?

I guess what I'm getting at is that if SQL Server can fit the entire database into the RAM it currently has, would more RAM make any difference?

Thanks

SQL Server does indeed like RAM and will take everything it is allowed to. For that reason, an upper limit of RAM should be assigned, leaving sufficient for the OS and anything else running on that server.

It would seem your 2GB database would easily fit into your existing RAM, but again, it depends on what else is running on your server. In addition to your buffer pool, you have the plan cache, and other applications - SSIS, 3rd party apps?

The only way to tell if an upgrade would be of benefit would be to monitor your memory usage via perfmon, DMV or any other appropriate means to see if your server is using fully the existing memory.

I found this great Technet article (http://technet.microsoft.com/en-us/library/ms176018.aspx) that explains which counters to monitor. Is also explains the conditions where adding more memory is appropriate: -

  The Buffer Cache Hit Ratio counter is specific to an application.
  However, a rate of 90 percent or higher is desirable. Add more memory
  until the value is consistently greater than 90 percent. A value
  greater than 90 percent indicates that more than 90 percent of all
  requests for data were satisfied from the data cache.

  If the Total Server Memory (KB) counter is consistently high compared to the amount
  of physical memory in the computer, it may indicate that more memory
  is required.


How to clear PostgreSQL temp files after crash I faced cumputer crash while doing clustering. The temp files has not got cleared after computer resume. How to clear them safely now?
Update
Windows OS. pgsql_tmp directory is empty but disk space still occupied by clustering temp files located in the table base folder.

temporary files in the pgsql_tmp directory
You'd have to remove them by manually deleting the files in the pgsql_tmp subdirectory.
But be careful – manually messing with the data directory is always dangerous. I recommend only removing files whose modification timestamp is safely in the past, on Unix systems perhaps
find pgsql_tmp -mtime +1 -mindepth 1 -maxdepth 1 -exec rm {} \;

Other files
If you are not talking about temporary files at all, but about files that got left behind after a crash during an operation that rewrites a table, like VACUUM (FULL), CLUSTER or ALTER TABLE, the case is not so simple.
The proper, safe way to do this
Back up the database with pg_dump, drop it and restore it.
Then all those files should be gone.
The messy, dangerous way if you cannot afford the down time
Again, manually messing with the data directory should be left to an expert, and if you delete something that is vital for your database, you have lost your database. Don't do that unless you have a good physical backup of the database.
Let's assume you are talking about the default tablespace, the base subdirectory of the PostgreSQL data directory.
Change into the directory for your database, whose name you can find with
SELECT oid FROM pg_database
WHERE datname = 'my_database';

The following statement will create a list of all “filenodes” of objects in the default tablespace:
SELECT pg_relation_filenode(oid)
FROM pg_class
WHERE reltablespace = 0;

If the number 12345 occurs in the result, the following files are part of that table, index or other object: 12345, 12345.1, 12345.2, ..., 12345_vm, 12345_fsm (basically everything that starts with 12345).
The files that got orphaned by the crash are those that are left behind if you discount all the above files for all the objects in pg_class. They could in principle be deleted.


Backup set expire option - Backup SQL Server 2012 I had seen an option while taking a backup i.e. set backup expire date, I have a doubt. What does this mean:

Backup will be no longer valid after date DD/MM/YYYY?
Backup declared with option, is it valid file to restore?
Can we store this backups for 1 year after expire date option is given and restore?
can Expired Backups(not deleted/purged) restored, any cons or limitations?

Any links to this concept also appreciated, which helps many junior DBA's.

Thanks to all.

Answered here by @JamesAnderson

The "Backup set will Expire" option is used to indicate how long a backup should be prohibited from being over written. It will not clean up old backup files. You can use the "Maintenance Clean up Task" in your maintenance plan to clean up backups files older than a certain number of days.

What is the use of "Backup set will Expire:" option while creating Backup Database task in Maintenance plan


SQL Server Service's Log On As account I just inherited a set of new servers and whoever set them up left the "Log On As" account as the default "NT Service\MSSQLSERVER". I'd like to change that over to an Active Directory service name. I have never attempted changing it after we have already went live in production. Is there anything I should be aware of? Will it require a restart of the service? This server is only using SQL, not SSIS, SSAS, SSRS. I will make sure the AD service name has access to all the folders necessary to reach files. Anything else I'm missing?

As long as you change the service account within SQL Server Configuration Manager (SSCM) it will apply all the permissions required (registry, files, etc.).

Only thing to be aware of are those rare occasions where SSCM process does not apply all permissions as expected, generally from some WMI issue. I so far have not come across this in new versions of SQL Server (2008 R2+).

By the name of that service account I am going to assume you are on an OS of Window Server 2008 R2 or higher. Just as a side note, if you do not actually need access to Active Directory resources leaving that account as the system account is perfectly alright. Most security standards will allow it as well if you are running on a current Operating System. These are virtual accounts that offer more security than the previous local system accounts did in previous versions of the OS. They are less chances of someone impersonating those accounts. I use them on most servers I setup for clients now unless they have some reason to need full domain access.


SQL which removes entries with duplicate emails DELETE
FROM Person
WHERE Id IN (
 SELECT Id
 FROM Person
 GROUP BY Email
 HAVING COUNT(*) > 1 AND Id <> MIN(Id)
)

The above query produces the error:
You can't specify target table 'Person' for update in FROM clause

Why?
I am just trying to solve this problem to improve my SQL skills.
I checked the above SQL query here and it seems my syntax is fine.

Use multiple-table DELETE syntax:
DELETE t1.*
FROM Person t1
JOIN Person t2 USING (Email)
WHERE t1.id > t2.id


Number of SQL Server languages As per MSDN

  There are currently ten SQL Server languages. The report server
  language for a given report server instance will always be one of the
  ten available languages. Support for all ten of the SQL Server
  languages is provided in a single Reporting Services installation.
  There are no language packs to install.

However, as per the page for sys.syslanguages there are many more listed

How should these 2 pages be interpreted together? Once says there are 10 SQL Server languages, and Report Server supports those 10. The other says there are many more, so are these supported by Report Server or not?

You get 11 languages (well, 10 if you count us and british english as the same) from SELECT DISTINCT language_id FROM sys.messages
and 33 from SELECT DISTINCT lcid FROM sys.syslanguages.

If not listed in sys.messages, messages for the default language are used.

As for SSRS:

  If you install Reporting Services in a default configuration, Setup
  determines the report server language when it creates the report
  server database. The language resource that Setup uses is the one that
  mostly closely matches the language of the operating system: If an
  exact match between the operating system language and a SQL Server
  language is found, Setup will use the exact match. For example, if the
  operating system language is U.S. English, Setup will use the SQL
  Server language resource for U.S. English as the report server
  language. If an exact match cannot be found, a close match will be
  used instead. For example, suppose the operating system is U.K.
  English. Because U.K English and U.S. English are in the same language
  family, Setup chooses the SQL Server language resource that most
  closely matches U.K English (in this case U.S. English). If there is
  no close match available, the report server language will be U.S.
  English.

https://msdn.microsoft.com/en-us/library/ms156493%28v=sql.105%29.aspx


Is it recommend to create a table for addresses? I have a table persons with the columns per_id, per_passport, per_name and per_address.

Two or more persons can live in the same address (like families or room-mates) so if this happened, the data is repeated. I think it should be avoided due to a normal form.

So should I create a table addresses to store all the addresses of the persons?

Also some persons may be in the home address or in the work address. It means persons can have multiple addresses. So should I create a per_add intermediate table?

I have the same problem to store email addresses and phone numbers.

I use PostgreSQL.

If the address is a single attribute there's nothing to be gained from moving out to another table. You're not suffering from a low normal form here, since there's no functional dependency from a non key attribute to the address. Things might be different if, say, you were storing the address as a set of attributes.


How do I return JSON structured as the transpose of json_agg() in PostgreSQL? I'm trying to convert an arbitrary subquery to JSON in PostgreSQL.

json_agg() seems like the obvious tool for the job. For example:

select json_agg(a) from (
    select count(lead_source), lead_source from leads_table group by lead_source order by count desc
) as a;

However, data is structured as the_data[row_number]["column_name"].
Suppose I want data structured as the_data["column_name"][row_number]. That is, I want the transpose structure of json_agg(). How do I do that?

My best effort is:

select row_to_json(row(array_to_json(array_agg(count)), array_to_json(array_agg(lead_source)))) from (
    select count(lead_source), lead_source from leads_table group by lead_source order by count desc
) as a;

But this has (at least) two drawbacks:

It doesn't work for an arbitrary subquery. The column names must be hardcoded each time array_agg() is used.
The column names become f1, f2, ...

This will take care of the f1,f2,f3 name issue, but unfortunately can't think of an easy way for an arbitrary query.

 SELECT row_to_json(r) from
      (SELECT array_to_json(array_agg(count)) As count,
              array_to_json(array_agg(lead_source)) As lead_source
      FROM (
        SELECT count(lead_source), lead_source from leads_table group by lead_source
          ORDER BY count desc
    ) as a) AS r;

This one is not quite as short but will allow you to to put an arbitrary in your a subquery and output the columns. (so solving both issue 1 and issue 2).  It piggy backs on hstore so if you don't have hstore installed, you'll need to do a:
    CREATE EXTENSION hstore;

SELECT  ('{' || string_agg(sv, ',') || '}')::json
 FROM (SELECT ('"' || (kv).key || '"' || ':'
   || array_to_json(array_agg((kv).value) ) )   As sv
FROM (SELECT each(hstore(a)) As kv from
  (
   SELECT count(lead_source), lead_source from leads_table group by lead_source
      ORDER BY count desc
   ) as a) As r
 GROUP By (kv).key) As r2;


How Compare two columns of Same SQL table to find duplicate records I have one table with two columns:

StartDateTime
EndDateTime

What will be best query to find out which rows has same (StartDateTime, EndDateTime)?

You did not specify a particular RDBMS (SQL Server, Oracle, etc.), but here is a quick solution that would probably work on most.

Assuming your table has some kind of unique key (primary key - like ID) to determine the difference between one row and another row, you could use a correlated exists check.

SELECT a.*
FROM table1 a
WHERE EXISTS (
        SELECT *
        FROM table1
        WHERE StartDateTime = a.StartDateTime
            AND EndDateTime = a.EndDateTime
            AND Id <> a.Id
        )


guideline for calling optimize table after alter table? Should I be calling 'optimize table' when changing datatypes of columns, adding indexes or dropping indexes, assuming a table with millions of rows, heavily used, both for reading and writing (InnoDB).

Ignore any possible downtime factor during the optimize-process.

No, you should not do OPTIMIZE TABLE because it is the same as

ALTER TABLE tblname ENGINE=WhateverTheStorageEngineIs;
ANALYZE TABLE tblname;

Thus, doing an ALTER TABLE tblname ... ;of any kind followed byOPTIMIZE TABLE tblname;` would create two temp tables.

BTW when you do OPTIMIZE TABLE tblname; on an InnoDB Table you get this

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

So, never use OPTIMIZE TABLE. You could run ANALYZE TABLE tblname; instead.

Changing datatypes of columns ? If and only if there is the possibility of value truncation, you could run ANALYZE TABLE tblname; during off-hours.

Dropping Indexes ? No need to since dropping an index remove all index stats for that index, leaving other indexes alone.


Custom unit of measurement conversion problem Let's say I have something that represents an item, let's say Beer. I store it in a table "Items". I also have a table with units of measurements. Like box, as beers can be sold in boxes.

Now the problem is, the box can have different amount of beers, there can be a box of lets say 20 beers and there can be a box of 10 beers. So there are a couple of possible conversion factors depending on the item.

Also the item can be sold in different unit, like a box, pack and each box and pack can have different amounts of items inside.

How should I design my database to handle this problem?

Your problem is not a unit of measurement problem.  It's a stock keeping unit (SKU) problem.  You can't convert, nor for any practical purposes would you ever want to convert from 8 packs of cans of beer A to 12 packs of bottles of beer B.

Your product ("Beer A") is a generic product header.  You don't sell "Beer A".  What you actually sell are the boxes of cans (or bottles) - of a particular size - of Beer A.  This box is what gets a record in your product/item table.  You may want to have a product header table that just lists "Beer A" without any actual quantity related to it.  That's up to you and depends on your system requirements.  What you really need to have though is a SKU table where the actual salable things are recorded.

Now if you want to be able to get some kind of apples to apples comparison between SKUs of similar products, you can track an actual, objective unit of measure for each SKU.  For example, if you're dealing with beer, then a liquid measure is appropriate.  Say you have 6 x 355ml cans of Beer A in your six pack SKU.  Then that SKU has 2,130ml of beer in it.  You could store that along with the other useful information that it's a six pack of 355ml cans.

If you want to have this apples to apples comparison, then you want to have a unit of measure conversion table that has columns such as:

...
  measure_type char(1) -- `D`ry or `L`iquid (or others as applicable)
, from_unit_of_measure varchar(10)
, to_unit_of_measure   varchar(10)
, conversion_factor    decimal(16,8)
...

If you have something based on these columns then you have to have all the combinations of from / to units of measure in both directions in order to look up a conversion factor.  You could also, alternatively, only have from non-default units of measure to the standard unit of measure for each measure_type.  This would mean less data in the table, but you'd have to have conditional logic to go from and / or to non-standard units of measure.  In the worst case scenario, you might have to convert from units in A (non-standard) to B (standard) and again from B (standard) to C (non-standard).  Which way you go depends on your relative tolerance for more data to maintain or more code complexity.