Backing up multiple instance System Databases with distributed Maintenance Plan I am running maintenance plans on a server, which is running several instances of SQL Server 2104.

I have set up a nightly maintenance plan to run backups for all user databases, with the backups set to create their own sub-directory.

This works absolutely fine with the current setup- with all backups residing in their own specific locations.

However, I was curious as to what would happen if databases on multiple instances shared the same database name - is there any way of adding an extra level in the maintenance plan to include the instance name?

I am not that huge fan of Maintenance plans to use such strategy for Backups.

What suits the best to our environment is using ola.hallengren Backup scripts.

It backups by taking the initial folder as the location define by the user and later it itself identifies the instance that it is backing up and thereafter creates the folder respectively:

Give it a try , as its simple to execute and easy to maintain.


Slony - Wait for Event What is the behavior of Slony waiting for an event after lock set command?

Basically I am trying to do a switchover and it takes a lot of time. We are following these steps:

Lock Set
Wait for Event (timeout = 300)
Move Set (timeout = 300)
Wait for Event (timeout = 300)

The alt perl script we have with Slony source has the following sequence:

Lock Set
Sync
Wait for Event
Move Set

What should be the correct sequence of events and how can I make the switchover faster?

In the slony docs they have this third sequence as an example of a structured switchover [http://slony.info/documentation/failover.html#AEN839]:

lock set (id = 1, origin = 1);
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2, wait on=1);

Anyway, I've always used the alt-perl tools with the following, probably well-known, syntax. This moves set2 origin from node 1 to node 2, ie node 2 will become provider.

/usr/local/slony/bin/slonik_move_set set2 1 2 | /usr/local/pgsql/bin/slonik

As you state the script use this sequence of commands: lock set, sync, wait for event, move set. This has worked flawlessly.

What might take some time is the locking and sync. The locking might have to wait for any long running transactions to finish. If the sync is lagging all nodes has to be synchronized when doing a slonik_movet_set.

It seems reasonable to do a sync after the lock as way to confirm the sync between nodes. You could add the sync to your slonik script, ie:

lock set (id = 1, origin = 1);
sync (id = 1);
wait for event (origin = 1, confirmed = 2, wait on=1);
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2, wait on=1);

Also notice id of the confirming node and that you can specify a timeout for the wait for event command, default is 600 sec  [http://slony.info/documentation/stmtwaitevent.html]


How to tell if Azure SQL DW is using a secondary index? Using Azure SQL DW, I have created a secondary index on a single column in a table, yet I'm unsure if the index is ever being used by my query. The performance is still slow, but I'm searching about 7 billion rows of data.

My table is essentially:

CREATE TABLE FactBusinessEvent
(
    [EmailAddress] [nvarchar](200) NOT NULL,
    [EventDate] [datetime] NOT NULL,
    [EventDate_key] [int] NOT NULL,
   -- OTHER COLUMNS HERE
)
WITH
(
    DISTRIBUTION = HASH ( [EmailAddress] ),
    CLUSTERED COLUMNSTORE INDEX
);

CREATE INDEX IX_FactBusinessEvent_EmailAddress ON FactBusinessEvent
(
   EmailAddress ASC
);

And my query is:

SELECT * FROM FactBusinessEvent WHERE EmailAddress = 'test@test.com'

Using SSMS 17.6, I can show the estimated query plan and it completely ignores the secondary index, showing a single Get from the table. I can't seem to use hints in SQL DW, so is there anything else to try?

Thanks for any insight.

As you have chosen to hash distribute your table on EmailAddress, this will mean all email addresses with the same value will end up with the same hash and consequently the same distribution - where SQL DW always has 60 distributions distributed across a number of nodes.  As such you won't be making best use of the compute available to you.

Having said that, can you confirm what DWU you are running at, what resource class is associated with the user you are running as and that you have created the relevant statisics ( ie on Email )?

Looking at your secondary index, it only contains one column, so is best suited to queries only containing that column, or for small point-lookups (assuming SQL DW behaves in a similar manner to SQL Server on this which isn't necessarily true).  Even it did this, it would then have to fetch the other columns from the main columnstore index to service your SELECT *.

Have a look at this article for advice on hash distributing large tables:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-best-practices#hash-distribute-large-tables

If this is one of your business-critical queries, you could consider a different hashing column, or even experiment with round robin distribution.  Is that something you join on for example?  In in this simple example, I create a copy of the table using ROUND_ROBIN distribution and run the query against that table:

CREATE TABLE FactBusinessEvent_rr
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM dbo.FactBusinessEvent;
GO

-- Create the required statistics
CREATE STATISTICS _st_FactBusinessEvent_Email_rr ON dbo.FactBusinessEvent_rr ( EmailAddress );
-- other stats here, ie columns you will join on, use in WHERE clause, or aggregate on
-- ...
GO

SELECT *
FROM dbo.FactBusinessEvent_rr
WHERE EmailAddress = 'test@test.com'
OPTION ( LABEL = 'email round robin query' );


MySQL CPU & Memory Spikes Every couple of days, my server's CPU and memory usage spikes up to 100%, making our vBulletin forum almost unusable for 5-10 minutes. CPU is typically at 1-2, Memory at 3GB. During the spikes, CPU goes up past 40 and Memory at 5GB (max).

There's no correlation with traffic load or cron jobs. The access logs show that hits are consistent during these spikes. No increase in hits. These spikes can happen in the middle of the night when there are less than 20 users online. Pages go from loading in 0.3 seconds to 10+ seconds. When I run top, I see MySQL eating up all of the CPU and memory, with httpd in second place.

I'm on an SSD VPS with 5GB RAM, Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz, usually lightning fast for our mid-sized forum.

MySQL stats from past week:

Uptime: 7 days 3 hours 50 min 12 sec

Max Used Connections:      230/500 (46.00%)
Key Buffer Used:           95.71M/256.00M (37.39%)
Current Key Buffer Usage:  77.97M (30.46%)
InnoDB Buffer Pool:        79.41M/128.00M (62.04%)
Query Cache:               62.59M/90.00M (69.55%)
Table Locks Waited:        25.16k Rate: 0.0/s (0.04%)
--------------------
Query Cache Limit (bytes): 524288
Query Cache Min Unit (bytes): 4096
Query Cache Size (bytes): 94371840
Query Cache Free Mem (bytes): 28736608
Query Cache Used Mem (bytes): 65635232
Queries in Query Cache: 1
Query Cache Avg Size (bytes): 65635232

TOP during normal, fast time (I don't have one from a recent spike)

top - 12:19:21 up 9 days, 22:08,  3 users,  load average: 0.92, 1.50, 2.10
Tasks: 112 total,   2 running, 109 sleeping,   0 stopped,   1 zombie
Cpu(s):  8.6%us,  0.4%sy,  0.0%ni, 90.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.1%st
Mem:   5242880k total,  2269752k used,  2973128k free,        0k buffers
Swap:        0k total,        0k used,        0k free,  1034424k cached

Queries from the past 7 days:

Total          92.95M     150.3/s
  QC Hits      39.96M      64.6/s  %Total:  42.98
  DMS          25.85M      41.8/s           27.81
  Com_         20.12M      32.5/s           21.65
  COM_QUIT      7.03M      11.4/s            7.56
  -Unknown      2.16k       0.0/s            0.00
Slow 10 s          90       0.0/s            0.00  %DMS:   0.00 Log: OFF
DMS            25.85M      41.8/s           27.81
  SELECT       22.59M      36.5/s           24.30         87.39
  INSERT        1.40M       2.3/s            1.50          5.40
  UPDATE        1.30M       2.1/s            1.40          5.04
  REPLACE     509.01k       0.8/s            0.55          1.97
  DELETE       51.48k       0.1/s            0.06          0.20
Com_           20.12M      32.5/s           21.65
  set_option   13.09M      21.2/s           14.09
  change_db     7.02M      11.3/s            7.55
  show_fields   5.16k       0.0/s            0.01

Rows            5.53G      8.9k/s
  Using idx     2.49G      4.0k/s  %Index:  44.95
Rows/question   59.47

Table Locks
  Waited         25.16k       0.0/s  %Total:   0.04
  Immediate      70.33M     113.7/s

Our my.cnf file:

[mysqld]
thread_cache_size=4
table_cache=512
local-infile=0
innodb_file_per_table=1
query_cache_limit=1M
max_connections=500
key_buffer_size=256M
join_buffer_size=1M
query_cache_limit = 512K
query_cache_size = 90M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
max_tmp_tables=1
default-storage-engine=MyISAM
open_files_limit=4974
max_allowed_packet=268435456
innodb_use_native_aio=0
innodb=on
innodb_buffer_pool_size=134217728

Do you see anything that looks like it could be causing these intermittent problems on an otherwise speedy website? We have around 1 million page views per month. 30-60 concurrent visitors at any given moment on a regular day. 60-130 on a busy day. But again, these spikes don't have any correlation with number of concurrent visitors.

Thanks!

I remember to have the same problems and it has something to do with how vBulletin is programmed. Please check regular "cronjobs" (not talking about system cronjobs you have already mentioned not to be the culprit) run by vBulletin which are triggered by visting users.

You can find them in version 4 of vBulletin in your admin panel, just below the settings for RSS-Feeds.

Another point to check is wether your vBulletin tables are in MyISAM format. vBulletin (at least until version 4) is optimized for MyISAM - if you happen to use InnoDB then some queries on vBulletin's tables (especially with the post table) will literally grind your system down to a halt.

Hope this helps.


Mysql high safety implementation (replication or separate writes or something...) I want to implement highly safe system storage with mysql.

My case is:

I need to store records.
For more safety i want to have N separate physical mysql nodes.
Then client save record and get OK response at this moment I want to be sure that data durable written in all N nodes.

Can mysql cover this?
I am not familiar with mysql replication can it cover this?

  Then client save key value and get OK response - at this moment I want to be sure that data durable written in all N nodes.

So I am afraid that then client get "ok" on saving row in mysql the data can be  written in one node (other replicas not sync) and if at this moment crash happens in only one node that holds client row
i can't restore it (replicas not in sync).

So i want be sure that all nodes written then client get OK.

Should i use mysql replication or I must implement this on applicaion level by do N separate INSERTS to  N servers or something other?

The simple answer - You need Galera or NDB cluster

This is very simple form of answer, because new problems could come after this.

Some often "next" questions from projects - all was fine on my old server, but now my UPDATE of 5000000 records - always fail!

Or - I have 3 nodes, but my insert start work much slower, help me please! I just every few minutes run SELECT GROUP BY over 500 000 rows - but it was perfect work before.

If high safety - it mandatory, cluster + replication (or any other solution for reporting part of application - such as AWS Redshift and etc) - http://severalnines.com/blog/how-set-asynchronous-replication-galera-cluster-standalone-mysql-server-gtid

The best way - install and test!


Select from list based on compound key I have following student table

ID     Version      Name       Email
1        1         Johan        Johan@email.com
1        2         Johan 1      Johan@email.com
2        1         Saif         saif@email.com
2        2         Saif  1      saif2@email.com
2        3         Saif  4      saif1@email.com

I want to select student records based top version.

ID     Version      Name       Email
1       2          Johan 1      Johan@email.com
2       3          Saif  4      saif1@email.com

Key points here, are  :

ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY [Version] DESC) AS RN

and

WHERE RN = 1

Here is:

CREATE TABLE Student
    ([ID] int, [Version] int, [Name] varchar(6), [Email] varchar(15))
;

INSERT INTO Student
    ([ID], [Version], [Name], [Email])
VALUES
    (1, 1, 'Johan', 'Johan@email.com'),
    (1, 2, 'Johan1', 'Johan@email.com'),
    (2, 1, 'Saif', 'saif@email.com'),
    (2, 2, 'Saif1', 'saif2@email.com'),
    (2, 3, 'Saif4', 'saif1@email.com')
;

-- select

;WITH StudentCTE AS
(
SELECT [ID], [Version], [Name], [Email]
  ,ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY [Version] DESC) AS RN
FROM Student
)

SELECT  [ID], [Version], [Name], [Email]
FROM StudentCTE
WHERE RN = 1

output:

ID  Version Name    Email
1   2   Johan1  Johan@email.com
2   3   Saif4   saif1@email.com


SQL Agent job for SSIS execution fails with "An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed." A SQL Agent job with a SQL Server Integration Services Package step fails with the following message:

  Executed as user: DOMAIN\SQLAGENTACCOUNT. Microsoft (R) SQL Server Execute Package Utility  Version 13.0.4259.0 for 64-bit  Copyright (C) 2016 Microsoft. All rights reserved.    An invalid reporting option was specified.  Only E, W, I, C, D, P, V, and N are allowed.  The command line parameters are invalid.  The step failed.

This error can occur if the Configuration tab on the job step is used to set package parameters and one of the parameters (such as password) contains a character that needs to be delimited, such as a single quote.  When the parameters are modified on the job step dialog, the parameters are included on the job step's command line, and a single or double quote will not be delimited.  This will result in an invalid (or worse, erroneous) command line.

Possible solutions are:

Delete the job step, create a new job step, and set the package parameters on the SSIS project directly (in SSMS, under Integration Services, SSISDB, SSIS Packages, Projects).  The command line for the job step will not try to pass the parameters, but the package execution engine will read the parameters directly and do a better job of handling special characters.
It may be possible (but I haven't tested it) to include the backslash delimiter before the special character.  So for a password of My'Pa$$word you would instead use My\'Pa$$word.
Remove the offending special character from the parameter (e.g., if it's a password, generate a new password without any quotation marks in it).

I don't know what all characters can cause this issue, but I do know that single quotes will cause it, and I think it's safe to assume double quotes will also.

You can see what command will be executed by SQL Agent by running the following query:

USE msdb
SELECT t1.command FROM sysjobsteps t1
JOIN sysjobs t2 ON t1.job_id = t2.job_id
WHERE t2.name = '<SQL AGENT JOB NAME>'

The command will include a couple of /Par by default for $ServerOption::LOGGING_LEVEL and $ServerOption::SYNCHRONIZE, but if any parameters on the job step were modified you'll also see them in the command line, such as:

/Par "\"SQLServer_Password\"";"\"PLAIN_TEXT_PASSWORD\""

Note that for password fields, the password gets stored in sysjobsteps in plain text, so that's definitely not something you want to change on the job step.

There may be other issues that could cause invalid command errors, and anyone is welcome to edit this answer to add any others.


Exception Details: System.Data.SqlClient.SqlException: Login failed for user One of our user from another domain WATSON received the following error message:

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'WATSON\User1'.

when he is trying to access one of our web application that retrieve information from a database.

The web application and the database that the web application use is located in a Windows Server 2008 R2. The machine name is Jack11 and has join the domain WATSON. The web application is hosted in the IIS wile the database is hosted in the SQL Server 2008 Enterprise.

Do I need to setup my SQL Server 2008 Enterprise in order to allow domain user such as in this case WATSON\User1 (not local user) to access web application that uses any of the database inside the SQL Server 2008?

(Kindly note that there might be more than 50 domain users that may required to access the web application and therefore it would be inefficient to add each domain user to the database's user rights)

One of our user from another domain (...)

To authenticate against a resource in another domain, either there must be a domain trust established (ask your Windows sysadmins), or pass-through authentication must be used (very likely not in this case). This is true for Windows in general, not just SQL Server.

  Do I need to setup my SQL Server 2008 Enterprise in order to allow domain user (...)

If there isn't an existing Windows user or group login for the user, then yes, you'll need to create a new login.

  Kindly note that there might be more than 50 domain users that may required to access the web application and therefore it would be inefficient to add each domain user to the database's user rights

I agree -- with this type of scenario, I would recommend setting up a group in AD, and then creating a Windows group login in SQL Server. This way, access is controlled by AD group membership, and you don't have to mess around with changing logins in SQL Server once it's set up.


Trouble Installing PostgreSQL icacls I'm trying to install latest version of PostgreSQL and the install keeps running into the problem of

  Error running the Post-Install Step. Installation may not complete correctly. The data cluster initialization has failed.

I looked online and found a lot of solutions to the problem and I've tried most of them. However, I keep getting the same error, so I looked at the Install-Log and it say that Icacls doesn't exist, which seems new.

  Called IsVistaOrNewer()...

    'winmgmts' object initialized...

    Version:10.

    MajorVersion:10

  Ensuring we can write to the data directory (using icacls) to  CORPORATE\pjy002:

    Executing batch file 'radC235D.bat'...

    'icacls' is not recognized as an internal or external command,
    operable program or batch file.

Some other important things.

Downloading PostgreSQL on a virtual machine
Am an administrator of said virtual machine or at least can get adminrights
Using Windows 10
Installing PostgresSQL 10.4

Figure out the problem. Had to get elevated rights and then install it. Thanks to everyone that helped.


SQL Server becomes slower over time until we have to restart it We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite acceptable, but the memory consumption gets more and more until all available memory (30GB) is exhausted. After that, the system gets slower and slower.

Commands like Dbcc freeproccache have no effect.

There are not many transactions in select * from sys.dm_tran_session_transactions (not more than when the system is fine), some times this list is empty.

The first result of dbcc memorystatus is

VM Reserved               42136628
VM Committed               1487176
Locked Pages Allocated    24994048
Reserved Memory               1024
Reserved Memory In Use           0

A restart of SQL Server solves the problem for a while.

What causes this behavior? How can it be avoided?
If a real solution for the cause is too difficult, is there a command that forces SQL Server to actually release all memory without a complete DBMS restart?

The server is running on dedicated hardware (not a VM). We had some scheduled jobs, but we disabled them for a while, with no change. There are other mid tier applications running on the same server, but they use no more than 2GB memory, negligible CPU, and almost no I/O. We restarted all such applications with no change.

I would suggest collecting performance metrics on this server, so you can eliminate the guesswork from troubleshooting these types of problems. See this article for a more complete guide if you don't know where to start with this.

In particular, I would check the performance counters Memory\Available MBytes and Paging File(_Total)\% Usage because you said the issues only start occurring when the buffer pool is full. The numbers you get back from these counters may indicate that the max server memory setting needs to be adjusted (either up or down) for the amount of physical memory allocated to the server. As I mentioned here, I don't recommend basing the max memory setting on the amount of physical memory except as an educated guess for a starting point. Always measure the result, and adjust from there.

If the amount of free memory is too low (< 500), or the page file usage is over zero, this may indicate that the SQL Server instance is overcommitted: on SQL Server 2008 R2, the max server memory setting only controls buffer pool size, and not other memory usage such as the plan cache. SQL Server also does not care about other applications you may have running on the system. This extra memory usage can put memory pressure on Windows -- or the other applications -- possibly leading to disk swapping. This is something you want to avoid at all cost, particularly if the page file exists on a volume backed by just a simple RAID 1 mirror. My feeling is that this is the issue, and backing off the max server memory setting should fix the problem.

If the amount of free memory is high (> 1000) and the page file usage is zero, you can probably bump up the max server memory slightly (in 256 MB increments) to maximize the memory usage of the server. This most likely won't solve the problem, however, and you'll need to look elsewhere, probably at the physical disk counters and the buffer pool page life expectancy. If queries are thrashing the buffer pool, there's nothing you can do except improve disk performance, increase the amount of physical memory available to the server so all the data pages can fit in memory at once, or modify the database to not take up as much physical space (maybe by using row or page compression, or by rebuilding indexes with a higher FILLFACTOR).

I've published an article on this topic here that goes into more depth about this issue and how to solve it.


Will I need more system resources to run DB2 instead of MySQL? I need to convert my web application from MySQL to run on DB2. I need to know in advance whether I will need a high spec server to ensure the web application performs at the same speed as it does now on MySQL.

The application is a very database intensive data analysis application with a browser interface. It is only using about 20% of the available CPU power, and 30% of the memory on the server now. Would it take more CPU or memory if converted to DB2? Would I need a high spec server?

I am just looking for any general feel of whether a server upgrade would be required for a MySQL to DB2 conversion if everything else remains equal (database size, traffic, etc).

I have administered both MySQL and DB2 database systems. In general, I'd say that DB2 requires more memory and more administration. But sometimes, you can use DB2's sophisticated features to perform queries which are much more efficient.

About memory and administration: DB2 is not very good at making good use of RAM. Either you spend a lot of time fine-tuning its memory config, or you use its automatic memory configuration ("STMM") and accept that it will waste a depressingly high percentage of your RAM. And DB2 has a bunch of other small annoyances which add up to a need for significant need for administration. Example: If you create a database, it will not be automatically "activated" at boot time, meaning that the first connection to it will take forever (a script can help you, but why-oh-why is that needed).

About sophisticated features: If you take the (potentially long) time to study some DB2 features such as index-only scans, OLAP queries, semantic query optimizations (where the optimizer can sometimes take advantage of constraints defined in the tables), materialized views, etc, then there are situations where you can make things go orders of magnitude faster compared to a simpler database system. And if/when you make something run much faster through design and queries, then you can cut down on CPU, RAM and I/O requirements.

If you have large amounts of data, then you also need to think about backups: Do you need to back up your data? If so, what options do the database systems provide? - There is not much fun about an otherwise fast system which is often held back due to long running and I/O-saturating backups. DB2's backup+restore has many annoying characteristics, but it does also have interesting features such as the ability to split data into hot+cold data (via separation into tablespaces), incremental or delta backups, etc. Last time I looked at MySQL, the backup options were less sophisticated.


Select data from one table with id from another so as text is saying I need to grab user id by name from one table and then select count from another table by that ID. is it possible?

users table

ID | User

online table

ID

How about something more like:

SELECT COUNT(users.id)
FROM users
    INNER JOIN online
        ON online.ID = users.ID
WHERE users.id = 'myuser';


Wrapping up the repetitive sql statements into transaction in MySQL I have a task to cancel more than 90 contracts, and I would like to know if it's possible to do it in one shot, rather than running manually all of them. I have 3 statements which I need to run per each contract. These are the examples below.

When I run the first statement:

UPDATE `dbo2`.`allcontracts` SET `ctr_status`='4' WHERE `ctr_id`='4483';

I'm changing the status of contract with id =  4483. Status will be 4 for all contracts ( more than 90 of them ).

When I run the second statement:

INSERT INTO `dbo2`.`tctrchanges`
      (`chg_ctr_id`, `chg_type`, `chg_date`, `chg_status`,
       `chg_appr_date`, `chg_emp_id`, `chg_req`, `chg_reserved`)
    VALUES
      ('4483', '0', '2017-06-02 11:11:11', '1',
       '2017-06-02 11:11:11', '3', '31', '4483');

I need to insert contract id from the first statement into chg_ctr_id and chg_reserved. I will also need id of column chg_id which is PK AI from this second statement above, to automatically assign it to third statement below in column note_text as URL parameter. ( For example below chg_id=5334 )

 INSERT INTO `dbo2`.`allnotes`
       (`note_dt`, `note_user`, `note_ref_id`, `note_type`,
        `note_text`,
        `note_emp_id`, `note_reserved`)
     VALUES
       ('2017-06-02 11:11:11', 'mikeb', '328515', '2',
        'Automatic Note: Contract was Cancelled ',
        '31', '4483');

As you can notice, the column note_reserved will have the same contract id from the first statement, and column note_emp_id will have the same static value as columnchg_req from the second statement.

All other values can be the same for all contracts.

Any ideas?

I would do something like the following:

CREATE TABLE `DelContracts_20170607` (`contract_id` varchar(32), `chg_id` varchar(32));
INSERT INTO `DelContracts_20160607` VALUES ('1234', NULL);
-- ... insert rest of contract IDs

SET @DelDate = DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d %T');

UPDATE `dbo2`.`allcontracts` ac JOIN `DelContracts_20170607` d ON (ac.`ctr_id` = d.`contract_id`)
   SET ac.`ctr_status`='4'
;

INSERT INTO `dbo2`.`tctrchanges`
  (`chg_ctr_id`, `chg_type`, `chg_date`, `chg_status`, `chg_appr_date`,
   `chg_emp_id`, `chg_req`, `chg_reserved`)
  SELECT `contract_id`, '0', @DelDate, '1', @DelDate, '3', '31', `contract_id`
    FROM `DelContracts_20170607`
;

UPDATE `DelContracts_20170607` d JOIN `dbo2`.`tctrchanges` tcc ON (d.`contract_id` = tcc`chg_reserved`)
   SET d.`chg_id` = CAST(tcc.`chg_id` as char(32))
 WHERE tcc.`chg_date` = @DelDate
;

INSERT INTO `dbo2`.`allnotes`
  (`note_dt`, `note_user`, `note_ref_id`, `note_type`, `note_text`, `note_emp_id`, `note_reserved`)
  SELECT @DelDate, 'mikeb', '328515', '2',
         CONCAT('Automatic Note: <a href=\"ctrchange4.asp?chg_id=',`chg_id`,'">Contract was Cancelled '),
         '31', `contract_id`
    FROM `DelContracts_20170607`
;

DROP TABLE `DelContracts_20170607`;

NOTE: Code is untested.

First, you create a table to hold the contract ID values. Set this up to also hold the chg_id values form tctrchanges, once they're created.

Insert all the contract IDs into the table.

Next set a variable to the current date and time.

Update allcontracts by JOINing it to your newly created table.

Insert your rows into tctrchanges by selecting contract_id, the date variable, and the hardcoded values from your table.

Next, update your table, setting the chg_id value for each contract_id. You find the chg_id based on each row's contract_id and the value from your date variable. This is why we put the date in the variable instead of directly calling CURRENT_TIMESTAMP(); so we'd be sure we had the matching date when we went to search.

Then, insert into allnotes by selecting the hard-coded values (concatenating the chg_id into the URL) and the contract_id from your table yet again.

Finally, delete the table you created.


Merging data from multiple tables I'm trying to merge data from multiple tables in different databases on the same server however the result is incorrect (i.e. the number of rows returned is more than what it should be). Here's the code:

merge into factOrders fo
using (
    select CustomerKey
        ,ProductKey
        ,CategoryKey
        ,dt1.TimeKey as [OrderDateKey]
        ,dt2.TimeKey as [RequiredDateKey]
        ,dt3.TimeKey as [ShippedDateKey]
        ,o.OrderID
        ,Freight
        ,od.UnitPrice as [UnitPrice]
        ,od.Quantity as [Quantity]
        ,od.Discount as [Discount]
        ,od.UnitPrice * od.Quantity * (1 - Discount) as [TotalPrice]
        ,sh.CompanyName as [ShipperName]
        ,sh.Phone as [ShipperPhone]
    from db1.dbo.Orders o
        ,db1.dbo.[Order Details] od
        ,db1.dbo.Shippers sh
        ,db1.dbo.Products p
        ,dimCustomers dc
        ,dimProducts dp
        ,dimCategories dct
        ,dimTime dt1
        ,dimTime dt2
        ,dimTime dt3
    where o.OrderID = od.OrderID
        and dc.CustomerID = o.CustomerID
        and dp.ProductID = od.ProductID
        and dct.CategoryID = p.CategoryID
        and sh.ShipperID = o.ShipVia
        and dt1.date = o.OrderDate
        and dt2.date = o.RequiredDate
        and dt3.date = o.ShippedDate
    ) o
    on (
            fo.ProductKey = o.ProductKey
            and fo.CustomerKey = o.CustomerKey
            and fo.CategoryKey = o.CategoryKey
            and fo.OrderDateKey = o.OrderDateKey
            )
when matched
    then
        update
        set......
when not matched
    then
        insert.....
        values (......)

I can tell the issue is somewhere in the WHERE condition and I've tried multiple options but not getting the correct result.

You don't mention what engine this is going against so I'm assuming it's Microsoft SQL Server.  One word of advice is that you put the join predicate and specify the type of join in the FROM clause, it will make errors like this much more visible as well as making reading the query easier.  You forgot to specify the join type for the db1.dbo.Products table, which I think makes it a cross join, so vastly multiplying the number of rows you would get back.  See below.

merge into factOrders fo
using (
    select CustomerKey
        ,ProductKey
        ,CategoryKey
        ,dt1.TimeKey as [OrderDateKey]
        ,dt2.TimeKey as [RequiredDateKey]
        ,dt3.TimeKey as [ShippedDateKey]
        ,o.OrderID
        ,Freight
        ,od.UnitPrice as [UnitPrice]
        ,od.Quantity as [Quantity]
        ,od.Discount as [Discount]
        ,od.UnitPrice * od.Quantity * (1 - Discount) as [TotalPrice]
        ,sh.CompanyName as [ShipperName]
        ,sh.Phone as [ShipperPhone]
    from db1.dbo.Orders o
        INNER JOIN db1.dbo.[Order Details] od ON o.OrderID = od.OrderID
        INNER JOIN db1.dbo.Shippers sh ON sh.ShipperID = o.ShipVia
        INNER JOIN dimCustomers dc ON dc.CustomerID = o.CustomerID
        INNER JOIN dimProducts dp ON dp.ProductID = od.ProductID
        INNER JOIN dimCategories dct ON dct.CategoryID = p.CategoryID
        INNER JOIN db1.dbo.Products p ON
        INNER JOIN dimTime dt1 ON dt1.date = o.OrderDate
        INNER JOIN dimTime dt2 ON dt2.date = o.RequiredDate
        INNER JOIN dimTime dt3 ON dt3.date = o.ShippedDate
    ) o
    on (
            fo.ProductKey = o.ProductKey
            and fo.CustomerKey = o.CustomerKey
            and fo.CategoryKey = o.CategoryKey
            and fo.OrderDateKey = o.OrderDateKey
            )


Performance issues after moving a database to SQL Server 2014 I've received a database that was previously on SQL Server 2008R2 but was just put on a SQL Server 2014 instance. There were no maintenance tasks run of any kind run on the database since 2014 (e.g. Rebuilding of indexes, updating statistics, etc.).

Once we ran update statistics as part of our regularly scheduled maintenance that we do on a set schedule, the performance of some queries has taken a massive hit to the point where some select statements will seem to never finish.

The queries have some CASE...WHEN statements in them, but I wouldn't expect there to be such a performance hit. Does anybody have any thoughts on what might cause such issues?

I've tried updating the compatibility level to 120 since it was on 100 when the database first came in but, that didn't make any difference on the performance.

Inculding links to executuion plans:

Slow: https://www.brentozar.com/pastetheplan/?id=Byf1Manvg

Fast: https://www.brentozar.com/pastetheplan/?id=SkJB762De

Ok, at this point I would be considering "manually" replacing statistics to tide me over. I haven't done this myself, but it should work in theory...

You can confirm that this would work as follows:
1.  Restore the pre-upgrade database to your LIVE system and confirm that the query is fast in that database, and delete that database once done. (This test is to eliminate any additional environmental differences as the cause. If the query is slow, then the rest of this proposal may be useless.)
1a. Restore the pre-upgrade database to your test system and confirm that the query is fast.
2. Update statistics, and confirm that the query is slow (cancel after a couple minutes of course)
3. Set compat to 120 and confirm that the query is still slow
4. Set compat back to 100 and confirm that the query is still slow
5. Restore another copy of the pre-upgrade database (I'll refer to this as Rest2, and the earlier as Rest1)
6. Extract all statistics from Rest2 using the techniques at https://thomaslarock.com/2015/07/how-to-recreate-sql-server-statistics-in-a-different-environment/ for all tables involved in the problematic queries (or all tables if that's simpler)
7. Apply the statistics to Rest1, and see if query is now fast (you may need to dbcc freeproccache first).

If it works, then it's almost certainly safe to apply the statistics to your live database--just make sure you have ONLY statistics scripted. And also set its compat level to 100. You should then see the queries run fast (though you may need to dbcc freeproccache first--but consider the possible effect on live performance).

Note I'm assuming here (based on your original post) that you do not have statistics autoupdate turned on, and that your data changes slowly enough that your old statistics will do until you figure out how to get your workload working with compat 120 and/or updated statistics (and you may as well sort both at this point).


Elasticsearch and product characteristics I have a small question about elasticsearch. Let's say I have a database with products to sell, like an ecommerce.

I know that elasticsearch can help me to find the best product with, for instance a full text search on the description and sorting by score.

Let's say someone is looking for sport shoes, and I don't sell any, but I do sell shoes.
What would be the best way to show the user I don't have any? The full text search will for sure give me everyday shoes in the results, since the word "shoes" is in the query, but I would prefer the user to know I don't sell any rather than seeing products that don't match his query.

Is it as simple as setting a score threshold and playing with it until having good results, or are there specific settings?

I'm also interested in generic answers having nothing to do with elasticsearch.

Thanks a lot!

When every document is about shoes, then the term "shoe" will likely do little for the relevancy score, which may be enough of a filter in many cases. Still, as you point out, if there are no sport shoes and a document contains multiple occurrences of "shoes", that document may be considered relevant enough to be included in the result and without competition from actual sport shoes it will be at the top of the set.

However, if every search is for a shoe then any generic term for shoes will have little significance and you may want to ignore that term completely from either the index (can be done with a stop list) or from the searches (can be done with a token filter).

In both cases the occurrence of "shoes" will have no effect on search results, thus you end up selling a pair of the all-round sneakers that "feels like a sport shoe" which happened to be the only ones in the result set.

Same concept used in language based optimization when we remove the significance of articles and prepositions such as "the" and "on".

This can be applied to just about any text search, including ElasticSearch and Oracle Text.


How should JOINed columns be included in an index? We are using SQL Server SE. Using a simple query, I would like to determine which index setup is best. The simple query is:

SELECT
    COUNT(*)
FROM
    (PS LEFT OUTER JOIN LIS
     ON (PS_ID = LIS_PS_ID AND LIS_DATE_PUBLISHED IS NOT NULL))
WHERE
    PS_PER_ID_FIRST = 2000
    AND PS_DATE_INSERTED >= '1/1/2019'
    AND PS_DATE_INSERTED < '1/1/2020'

Notes: Columns ending in _ID are unique identifier INT columns. The PS table holds 2,500,000 records while the LIS table holds 200,000 records. If important, both tables grow organically and slowly, perhaps 20% per year.

It is important for me to get the indexes for this query correct. Normally, I would do this:

CREATE INDEX IX_PS_1 ON PS (PS_PER_ID_FIRST, PS_DATE_INSERTED, PS_ID);
CREATE INDEX IX_LIS_1 ON LIS (LIS_PS_ID, LIS_DATE_PUBLISHED);

I have never been too familiar with when and why to specify columns as INCLUDEd. I know to specify them as INCLUDEd when they are SELECTed but I'm never sure about the columns in the JOIN statements. In other words, should the indexes instead be like this:

CREATE INDEX IX_PS_1 ON PS (PS_PER_ID_FIRST, PS_DATE_INSERTED) INCLUDE (PS_ID);
CREATE INDEX IX_LIS_1 ON LIS (LIS_DATE_PUBLISHED) INCLUDE (LIS_PS_ID);

Or, if I'm off my rocker and there is a much smarter way of going about the indexing for this query, please let me know.

Including columns is done when you don't want to be able to search on them through the root and intermediate nodes of the index.

As a result only the leaf level pages have the data that is included.

Wether you put the join columns in the key columns depends on the filters used.

Filtered index

Based on your query, the best index in theory would be a filtered index (assuming no parameterization).

CREATE INDEX IX_PS_1 ON PS (PS_ID)
INCLUDE(PS_PER_ID_FIRST,PS_DATE_INSERTED )
WHERE PS_PER_ID_FIRST  = 2000
      AND PS_DATE_INSERTED >= '1/1/2019'
      AND PS_DATE_INSERTED < '1/1/2020';

CREATE INDEX IX_LIS_1 ON LIS (LIS_PS_ID)
INCLUDE(LIS_DATE_PUBLISHED)
WHERE LIS_DATE_PUBLISHED IS NOT NULL;

Depending on the data, a merge join (join that can use 2 sorted inputs) could be chosen.
This join will be best when comparing large datasets.
The stored index data matches the filters exactly.

If filtered indexes are a no go

DEPENDS ON YOUR DATA

Now, when comparing >= and < in your where clause, it will probably not matter whether PS_ID is included or not in terms of performance as it will not be able to search on it.

As such these indexes should do

CREATE INDEX IX_PS_1 ON PS (PS_PER_ID_FIRST,PS_DATE_INSERTED )
INCLUDE(PS_ID);

CREATE INDEX IX_LIS_1 ON LIS (LIS_DATE_PUBLISHED,LIS_PS_ID);

This gives the seeks on the filters, but a hash join instead of a merge join with my data.

Again, depending on your data & how much is filtered by the applied filters, these indexes could work too:

CREATE INDEX IX_PS_1_3 ON PS (PS_ID)
INCLUDE(PS_PER_ID_FIRST,PS_DATE_INSERTED);
CREATE INDEX IX_LIS_1_3 ON LIS (LIS_PS_ID)
INCLUDE(LIS_DATE_PUBLISHED);

With my data, nothing is filtered out and the optimizer knows to use a merge join with residual predicate on the filters.

This is an extreme example but shows the differences and why it depends™


Union Table with a flag? i have an unique case where a union table with a flag might be the answer, but my db admin experience is limited.

CREATE TABLE patient(
   patient_id serial PRIMARY KEY,
   name VARCHAR (50) UNIQUE NOT NULL,
   //...
);
CREATE TABLE diagnosis(
       diagnosis_id serial PRIMARY KEY,
       name VARCHAR (50) UNIQUE NOT NULL,
       //...
    );

//this is past medical history

CREATE TABLE patient_diagnosis(
      diagnosis_id integer
      patient_id integer
      FOREIGN KEY (diagnosis_id) REFERENCES diagnosis (diagnosis_id)
      FOREIGN KEY (patient_id) REFERENCES patient (patient_id)
)

Now, I wan't to add a patient's family medical history, which is also just referring to the diagnosis table, with similar ability to search later (show me all patients with family diagnosed with xyz). I was thinking of putting a family=boolean flag in the patient_diagnosis table, but not sure if thats bad design. I also don't know what terms to search for here for help. any thoughts appreciated

Well you would need a family table to begin with:

create table family
(
  family_id integer primary key,
  name text not null,
  .... other attributes of a family ...
);

And a table that links a patient to a family

create table family_member
(
  family_id integer not null references family,
  patient_id integer not null references patient
  ... maybe even a date range when this patient was part of the family ...
);

Then you can link the diagnosis to the family the same way you linked it to the patient:

create table patient_diagnosis
(
  diagnosis_id integer not null references diagnosis,
  family_id integer not null references family
);


Why does 10^37 / 1 throw an arithmetic overflow error? Continuing my recent trend of playing with large numbers, I recently boiled an error I was running into down to the following code:

DECLARE @big_number DECIMAL(38,0) = '1' + REPLICATE(0, 37);

PRINT @big_number + 1;
PRINT @big_number - 1;
PRINT @big_number * 1;
PRINT @big_number / 1;

The output I get for this code is:

10000000000000000000000000000000000001
9999999999999999999999999999999999999
10000000000000000000000000000000000000
Msg 8115, Level 16, State 2, Line 6
Arithmetic overflow error converting expression to data type numeric.

What?

Why would the first 3 operations work but not the last? And how can there be an arithmetic overflow error if @big_number can obviously store the output of @big_number / 1?

Understanding Precision and Scale in the context of Arithmetic Operations

Let's break this down and take a close look at the details of the divide arithmetic operator.  This is what MSDN has to say about the result types of the divide operator:

  Result Types

  Returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL).

  If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

We know that @big_number is a DECIMAL. What data type does SQL Server cast 1 as?  It casts it to an INT. We can confirm this with the help of SQL_VARIANT_PROPERTY():

SELECT
      SQL_VARIANT_PROPERTY(1, 'BaseType')   AS [BaseType]  -- int
    , SQL_VARIANT_PROPERTY(1, 'Precision')  AS [Precision] -- 10
    , SQL_VARIANT_PROPERTY(1, 'Scale')      AS [Scale]     -- 0
;

For kicks, we can also replace the 1 in the original code block with an explicitly typed value like DECLARE @one INT = 1; and confirm we get the same results.

So we have a DECIMAL and an INT. Since DECIMAL has a higher data type precedence than INT, we know the output of our division will be cast to DECIMAL.

So where's the problem?

The problem is with the scale of the DECIMAL in the output.  Here is a table of rules about how SQL Server determines the precision and scale of results obtained from arithmetic operations:

Operation                              Result precision                       Result scale *
-------------------------------------------------------------------------------------------------
e1 + e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 - e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 * e2                                p1 + p2 + 1                            s1 + s2
e1 / e2                                p1 - s1 + s2 + max(6, s1 + p2 + 1)     max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2   max(s1, s2) + max(p1-s1, p2-s2)        max(s1, s2)
e1 % e2                                min(p1-s1, p2 -s2) + max( s1,s2 )      max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result
  precision is greater than 38, the corresponding scale is reduced to prevent the
  integral part of a result from being truncated.

And here's what we have for the variables in this table:

e1: @big_number, a DECIMAL(38, 0)
-> p1: 38
-> s1: 0

e2: 1, an INT
-> p2: 10
-> s2: 0

e1 / e2
-> Result precision: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 + max(6, 11) = 49
-> Result scale:                    max(6, s1 + p2 + 1) =      max(6, 11) = 11

Per the asterisk comment on the table above, the maximum precision a DECIMAL can have is 38. So our result precision gets cut down from 49 to 38, and "the corresponding scale is reduced to prevent the integral part of a result from being truncated."  It is not clear from this comment how the scale is reduced, but we do know this:

According to the formula in the table, the minimum possible scale you can have after dividing two DECIMALs is 6.

Thus, we end up with the following results:

e1 / e2
-> Result precision: 49 -> reduced to 38
-> Result scale:     11 -> reduced to 6

Note that 6 is the minimum possible scale it can be reduced to.
It may be between 6 and 11 inclusive.

How this Explains the Arithmetic Overflow

Now the answer is obvious:

The output of our division gets cast to DECIMAL(38, 6), and DECIMAL(38, 6) cannot hold 1037.

With that, we can construct another division that succeeds by making sure the result can fit in DECIMAL(38, 6):

DECLARE @big_number    DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million   INT           = '1' + REPLICATE(0, 6);

PRINT @big_number / @one_million;

The result is:

10000000000000000000000000000000.000000

Note the 6 zeros after the decimal. We can confirm the result's data type is DECIMAL(38, 6) by using SQL_VARIANT_PROPERTY() as above:

DECLARE @big_number   DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million  INT           = '1' + REPLICATE(0, 6);

SELECT
      SQL_VARIANT_PROPERTY(@big_number / @one_million, 'BaseType')  AS [BaseType]  -- decimal
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Precision') AS [Precision] -- 38
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Scale')     AS [Scale]     -- 6
;

A Dangerous Workaround

So how do we get around this limitation?

Well, that certainly depends on what you're making these calculations for. One solution you may immediately jump to is to convert your numbers to FLOAT for the calculations, and then convert them back to DECIMAL when you're done.

That may work under some circumstances, but you should be careful to understand what those circumstances are. As we all know, converting numbers to and from FLOAT is dangerous and may give you unexpected or incorrect results.

In our case, converting 1037 to and from FLOAT gets a result that is just plain wrong:

DECLARE @big_number     DECIMAL(38,0)  = '1' + REPLICATE(0, 37);
DECLARE @big_number_f   FLOAT          = CAST(@big_number AS FLOAT);

SELECT
      @big_number                           AS big_number      -- 10^37
    , @big_number_f                         AS big_number_f    -- 10^37
    , CAST(@big_number_f AS DECIMAL(38, 0)) AS big_number_f_d  -- 9999999999999999.5 * 10^21
;

And there you have it.  Divide carefully, my children.


issues with encoding and pg_dump/restore between windows and linux I have a dmp file I have created using pg_dump encoded in WIN1252.  What I would like to do is import it using pg_restore to a database on a Linux machine.  The problem is that on the windows machine postgreSQL recognizes the encoding WIN1252 but not the CP1252 encoding (they both are identical just different names for the same standard).  On the Linux machine, postgreSQL recognizes CP1252 but not WIN1252.  So when I try and do a pg_restore I get an error because WIN1252 is not recognized as a proper encoding.  Also, if I try to do a pg_dump on windows and specify the encoding as CP1252, I get a similar error.  I would like to stick with this encoding format so doing a pg_dump in utf8 or something is not something I would like to do.  Any help would be appreciated!

EDIT:
Actually, upon further investigation, postgreSQL does not understand cp1252.  The iconv linux command used to convert a text file from one encoding to another understands cp1252 and not win1252 and thats where I got it from.  On the Linux machine, I still cannot set the client encoding to WIN1252.  This is the error message:

FATAL:  conversion between WIN1252 and LATIN1 is not supported

The variable client_encoding was originally set to UTF8 on the Linux machines so I dont know where LATIN1 is coming from.  Unless WIN1252 is not supported so postgres tries to convert it to latin1 because that is the closest thing to it and fails at achieving that due to some character miss-match?

I suppose the dump file you have is encoded in WIN1252 and the target database on Linux has LATIN1 as the server encoding.  That's not going to work, as the error message says.

I suggest you reinitialize the target database using UTF8.

Alternatively, create the dump in UTF8 using the pg_dump -E option.


Select query according to date We have a table to store the Events.

Table has got a schema like

EventID INT Primary Key
EventName NVARCHAR(100)
StartDate Datetime
EndDate Datetime

So in the front end we want to show the events according to start time and end time.
From front end we are passing StartDate and EndDate as parameters to take the value.

So far we have taken like

   Select * from tbl_Events
   where ((startDate BETWEEN @start AND @end) OR (EndDate BETWEEN @start AND @end))

We have got 2 views to show the events -

A month view - which show all events in the month
A day view showing events of that day

Suppose i got an event with StartDate as 23 October 2012 and EndDate as 27 October 2012.

And i am passing @StartDate as 24 Oct 2012 and @EndDate as 25 OCt 2012

According to the above query its working in the Month view. But its not working in the day view.

Can anyone please help me on this to modify the query to work in both cases

You simply need to think about the date range slightly differently.  An event falls in your given range if the start date is prior to the end of your reporting period, and the end date is after the beginning of your reporting period:

Select * from tbl_Events
where StartDate <= @end
    AND EndDate >= @start;

For example:

CREATE TABLE MyTestDates
(
    MyTestDateID INT NOT NULL PRIMARY KEY CLUSTERED CONSTRAINT PK_MyTestDates IDENTITY(1,1)
    , StartDate DATETIME
    , EndDate DATETIME
    , EventName NVARCHAR(255)
);

INSERT INTO MyTestDates (StartDate, EndDate, EventName)
VALUES ('2012-01-27', '2012-01-30', 'Test Event 1');

INSERT INTO MyTestDates (StartDate, EndDate, EventName)
VALUES ('2012-01-27', '2012-02-28', 'Test Event 2');

INSERT INTO MyTestDates (StartDate, EndDate, EventName)
VALUES ('2011-01-01', '2012-03-22', 'Test Event 3');

DECLARE @FromDate DATETIME;
DECLARE @ToDate DATETIME;

SET @FromDate = '2012-01-01';
SET @ToDate = '2012-02-01'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, *
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2012-01-28';
SET @ToDate = '2012-01-29'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, *
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2010-01-01';
SET @ToDate = '2012-05-15'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, *
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

DROP TABLE MyTestDates;

The three SELECT statements return all events in this example.


SQL Server - Stopping a server side trace I want to setup a server side replay trace and scripted out setting up the trace. However I want the trace to end either after 12 hours or once 10 1GB files have been created. Normally with a max file count, the system deletes the oldest file and then creates the new one. In my situation this would end with me having Files 2-11 instead of the desired 1-10.
I have space limitations and need to replay all the steps from start to finish. I could just set the file size limit to 10GB. I think there is a 1GB limit on file size, but this could just be a limit in the GUI.

Any ideas on how I can stop the script once it reaches 10 files?

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
declare @maxfilecount int

set @DateTime = DATEADD(HOUR,12,GETDATE())
set @maxfilesize = 1024
set @maxfilecount = 10

exec @rc = sp_trace_create @TraceID output, 2, N'D:\Output\Trace', @maxfilesize, @Datetime, @maxfilecount

I figured out a way to do it.

declare @files table (ID int IDENTITY, FileName varchar(100))
insert into @files execute xp_cmdshell 'dir d:\Output\*.trc /b'

Checking the file count and if it is at my limit then I lookup the trace id and use sp_trace_setstatus  to stop it. Since my trace files take a while to get up to the filesize limit, I run this job every 5 or 10 minutes on agent.


max_stack_depth error in postgresql I am fairly new to PostgreSQL and would appreciate any help and recommendations.

I'm running the following function to update a table:

$BODY$
  DECLARE
    commercial_period date := (SELECT periods.period FROM common.periods WHERE periods.type = 'commercial');
   --period text := concat('_',right((date_part('year',residential_period))::text,2),'_',right((date_part('year',residential_period))::text,2)::integer+1);
  BEGIN
-------------------------------------------------------------------------------------------------------------
    --copies alternative data from temporal table to common.status
    UPDATE
      common.status
    SET
      implementation_status = A.implementation_status,
      latest_version = A.latest_version
    FROM
      monitoring_commercial_temporal.possible_alternatives AS A
    WHERE
      status.reference = A.reference
      AND (status.update_period = A.update_period
      OR status.update_period IS NULL);
-------------------------------------------------------------------------------------------------------------
    --sets as lastest version any site, not in the temporal list of alternatives
    UPDATE
      common.status
    SET
      latest_version = 'yes'
    WHERE
      (reference, update_period) NOT IN (SELECT reference, update_period FROM monitoring_commercial_temporal.possible_alternatives)
      AND update_period = commercial_period + interval '1 year'
      AND implementation_status <> 'non-preferred alternative'
      AND latest_version IS NULL;
-------------------------------------------------------------------------------------------------------------
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.monitoring_update_alternatives_commercial()
  OWNER TO postgres;

but I get the following error:

  ERROR:  stack depth limit exceeded HINT:  Increase the configuration
  parameter "max_stack_depth" (currently 2048kB), after ensuring the
  platform's stack depth limit is adequate.

> CONTEXT:  SQL function "st_intersects" during inlining SQL statement
> "SELECT (SELECT
>                                     array_agg(status.reference)
>                                   FROM
>                                     common.geometries AS A,
>                                     common.geometries AS B,
>                                     common.status,
>                                     common.permission_details
>                                   WHERE
>                                     NEW.reference = A.reference
>                                     AND status.reference = B.reference
>                                     AND status.reference = permission_details.app
>                                     AND NEW.reference <> status.reference
>                                     AND ST_Intersects(B.geom, A.geom)
>                                     AND (NEW.update_period = residential_period + INTERVAL '1 YEAR' OR NEW.update_period IS NULL)
>                                     AND status.update_period = residential_period + INTERVAL '1 YEAR'
>                                     AND permission_details.monitoring_type <> 'commercial'
>                                     AND (((ST_Area(ST_Intersection(B.geom, A.geom))+0.01) /
> (ST_Area(B.geom)+0.01) >= 0.2)
>                                     OR ((ST_Area(ST_Intersection(B.geom, A.geom))+0.01) /
> (ST_Area(A.geom)+0.01) >= 0.2)))" PL/pgSQL function
> monitoring_update_status_before() line 72 at assignment SQL statement
> "UPDATE
>     common.status AS A   SET
>     trigger = TRUE   WHERE
>     A.latest_version = NEW.reference
>     AND A.update_period = NEW.update_period" PL/pgSQL function monitoring_update_status_after() line 5 at SQL statement

Then it keeps repeating the last 6 lines from the UPDATE

Presumably your trigger firing on UPDATE executes an UPDATE on the same table, which itself fires the triggers, and so on recursively until the stack is full and the stack depth limit exceeded error is raised.

Here's a bare bones example demonstrating the problem:

create table tst(id int primary key, col int, trigger boolean);

create function trg() returns trigger  as $$
 begin update tst set trigger=true where tst.id=NEW.id; return NEW; END
$$ language plpgsql;

create trigger t before update on tst for each row execute procedure trg();

insert into tst(id) values(1),(2);

update tst set col=1 where id=1;
ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "update tst set trigger=false where tst.id=NEW.id"
PL/pgSQL function trg() line 1 at SQL statement
SQL statement "update tst set trigger=false where tst.id=NEW.id"

The mistake in the above case is to issue an UPDATE of the table already being updated, inside the trigger. The correct way is to assign directly NEW.trigger, as in:

create function trg() returns trigger  as
$$
 begin NEW.trigger=true; return NEW; END
$$ language plpgsql;


SQL Server Merge mandate Does the target table need to be identical to source table schematically? Specifically: 1) do they need to have the same number of columns and do the column types must be the same?, 2) do the column NAMES need to be the same also?

General MERGE syntax is as follows:

MERGE INTO <target table> AS TGT
USING <SOURCE TABLE> AS SRC
 ON <merge predicate>
WHEN MATCHED [AND <predicate>]
 THEN <action> -- one with UPDATE one with DELETE
WHEN NOT MATCHED [BY TARGET] [AND <predicate>]
 THEN INSERT... –- if indicated, action must be INSERT
WHEN NOT MATCHED BY SOURCE [AND <predicate>]
 THEN <action>; -- one with UPDATE one with DELETE

No, they don't need to have the same name/number/type of columns (although for the columns you are comparing on in the "join" i.e. the on-predicate, they need to be compatible to compare).

The MERGE statement created according to your example syntax will specify which fields to match on (merge predicate), or insert/update/delete by. If you want to match on CustomerName = Customer, or insert a OrderDate into the Date field, that would be fine.

Think of it as if you are doing an INSERT based on a join, and an UPDATE based on a join, within the single statement. Some people refer to this process as an "upsert"!


Cannot drop materialized view privileges from user Legacy database to me, pg version 9.6.12 running on a linux ami.

I've a user ('example_user') that I would like to drop. I've confirmed that 'example_user' owns no objects. However, when I run DROP USER 'example_user'; I get this (I've truncated for brevity) error:

exampledb=# drop user 'example_user';
ERROR:  role 'example_user' cannot be dropped because some objects depend on it
DETAIL:  privileges for materialized view "example-prod".view_foo
privileges for materialized view "example-prod".view_bar
privileges for materialized view "example-dev".view_foo
privileges for materialized view "example-dev".view_bar

I've tried about 15 different revocation statements to attempt to kill the privileges, and in some cases Postgres doesn't even complain. For example:

exampledb=# revoke all privileges on all tables in schema public from "example_user";
REVOKE

# OR

revoke all privileges on "example-prod".view_foo from "example_user";
REVOKE

I've tried countless different revokes on the separate schemas, views, databases and nothing seems to work. The privileges are not removed and I I get the same errors when I attempt to drop the user. Not sure if it's in any way related, but pg complains if I don't double quote the user.

How can these specific, or indeed all privileges, be removed from this user? Are there any other strategies for dropping this user if I don't need to worry about preserving object or privileges?

Thanks!

revoke all privileges on all tables in schema public from "example_user";

This drops privileges from objects in the schema named "public".  It does not drop them from objects in other schemas, such as "example-prod".

  revoke all privileges on "example-prod".view_foo from "example_user";

Works for me.  It still fails due to the other 3 MV, but no longer for that specific one.


Actual Clustered Index Scan I/O is huge The plan attached should not take more than a minute to run, but sometimes it takes hours. The I/O on the two outer clustered index scans balloons out of control and the query crawls. Can someone explain to me why this is happening and how to fix it?

https://www.brentozar.com/pastetheplan/?id=Hyp7qKFEx

Let's trace through the query plan from right to left. SQL Server estimates that only a single row will be returned from the t1 derived table. For each row returned it will do a clustered index scan of the VoterTelephones table in node ids 20 and 26. This means that if the cardinality estimate from the t1 derived table is wrong then SQL Server could end up doing many more clustered index scans than it expected when costing this plan. For example, if a thousand rows are returned from the t1 derived table then SQL Server will may do 2000 clustered index scans on the VoterTelephones table. If SQL Server had a more accurate cardinality estimate then it probably would have picked a different query plan.

There's another less technical way to get to a similar conclusion. Let's start at the left of the query plan this time. SQL Server thinks that only a single row will be inserted into the #tmpKeep1 table. Is that true? If you expect it to not be true then the estimated cost for the query plan could be quite inaccurate. The plan has an estimated cost of 50 magic optimizer units yet the query sometimes takes hours to finish. That's another reason to think that the cost is inaccurate compared to what actually happens when executing the query. If SQL Server has made bad assumptions or estimates about your data then it may have picked a sub-optimal query plan to return your data.

To improve query performance from I would start by trying to correct the cardinality estimate returned from the t1 derived table:

--get dupe cell phones
select TelAreaCode, TelNumber
from VoterTelephones
where TelCellFlag = 1 group by TelAreaCode, TelNumber having count(*) > 1

The most straightforward way to do this would be to put the results from that query into a temp table. SQL Server will gather stats on the temp table so you will get a more accurate cardinality estimate which is likely to change the query plan.

Depending on your version of SQL Server you may be able to improve the estimate by creating a multi-column statistics object on the VoterTelephones table, or even just by updating the stats with FULLSCAN. A filtered statistics that includes the where TelCellFlag = 1 predicate could help as well. The idea here is that if you give SQL Server more information about your data then it may be able to give you a better cardinality estimate for that derived table.

It may be true that it's too difficult to fix the cardinality estimate from that part of the query. In that case you could try creating indexes on the VoterTelephones table that eliminates the need for SQL Server to do a clustered index scan for each row in the outer table. If you create covering indexes on the table then SQL Server will do an index seek per row in the outer result set instead of a clustered index scan per row. That should be far more efficient.

For the joins to the table aliased as vt you filter on the TelCellFlag column, join on the TelAreaCode and TelNumber columns, and also need the LALVoterID and TelMatchScore columns for other parts of the query. You can get that information by carefully parsing the query text or by looking at the nested loop join operator with a node ID of 7 and the clustered index scan with a node ID of 20. For example, here is the predicate for the scan:

[S_MS].[dbo].[VoterTelephones].[TelCellFlag] as [vt].[TelCellFlag]=(1)

Here is the join predicate for the nested loop operator:

[S_MS].[dbo].[VoterTelephones].[TelAreaCode] as [vt].[TelAreaCode]
=[S_MS].[dbo].[VoterTelephones].[TelAreaCode]
AND [S_MS].[dbo].[VoterTelephones].[TelNumber] as [vt].[TelNumber]
=[S_MS].[dbo].[VoterTelephones].[TelNumber]

Here is the output column list for the clustered index scan:

[S_MS].[dbo].[VoterTelephones].LALVoterID,
[S_MS].[dbo].[VoterTelephones].TelNumber,
[S_MS].[dbo].[VoterTelephones].TelMatchScore,
[S_MS].[dbo].[VoterTelephones].TelAreaCode

You can combine that data to figure out your needs for the index. You want predicate and join columns to be in the index columns and the other necessary columns to be included columns. Something like this could work:

CREATE INDEX NAME_YOUR_INDEX ON VoterTelephones (TelCellFlag, TelAreaCode, TelNumber)
INCLUDE (LALVoterID, TelMatchScore);

You can perform a similar analysis for the other index scan in the plan (node ID 26).

Depending on your application, it's also possible that you're running into plan caching or statistics caching issues with your temp tables. I think that this is unlikely to be the cause but I'm including it in case other ideas don't work out. You can test for this by including a RECOMPILE hint in your query. Depending on how often this query runs that may not be a good option.


MariaDB innodb bufferpool load taking long time When I am restarting my MariaDB database engine it takes a long time to show 'innodb bufferpool load completed' depending on data volume. Is there a way to make it faster?
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 100000000      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 29             |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 100000000006   |
+-------------------------------------+----------------+

Assuming the buffer pool was completely full, this would mean 25G (based on innodb_buffer_pool_dump_pct) would be loading.
The loading of the innodb buffer pool is throttled by the setting of innodb_io_capacity.
The loading is also throttled heavily if there is other server activity in innodb.
Reading this amount from disk, especially in a random read pattern that would happen with innodb loading can take some time.
The loading thread currently is also single threaded. It is also blocking that one (space,page) is loaded at a time.
MDEV-9930 was written a while ago however without enough details to action.
Looking at the code now, there a significant performance enhancements possible. If increasing the innodb_io_capacity significantly doesn't improve this to your desired level, please write a bug report or include details in MDEV-9930 to be improved.
Edit:
MariaDB now documented this (MDEV-25366 document innodb buffer pool loading dependency on innodb_io_capacity).
MariaDB is looking for better solutions on reducing the throttling time based on your question here (MDEV-25417 reduce InnoDB buffer pool load time).


Query to join three tables I have three tables which I have to join. I'm not a good SQL writer. Can please sombody help me to prepare this using joins:

branches
-----------------------------------------
branch_id | branch_name | branch_address
-----------------------------------------

branch_services
-------------------------------
bs_id | branch_id | service_id
-------------------------------

services
--------------------------
service_id | service_name
--------------------------

I need to select * from the branches table and service_names related to each branch.

select
    b.*,
    s.service_name
from
    branches b,
    branch_services bs,
    services s
where
    b.branch_id=bs.branch_id
    and bs.service_id=s.service_id


pg_hba.conf allow mutliple I.P. addresses for single user In my pg_hba.conf, I need a single user to be allowed to connect from two completely different IP addresses (like work and home, for example).

Something like the following would be really nice:

host    all    my_awesome_user    151.123.45.67/0, 72.11.12.13/0    md5

Putting adding two entries (one for each IP doesn't work: postgres just sees the first entry, which ether matches or doesn't and completely ignores the second entry.

Any help would be appreciated.

Putting adding two entries (one for each IP doesn't work: postgres just sees the first entry, which ether matches or doesn't and completely ignores the second entry.

I don't believe that's true. PostgreSQL won't match it if the address is wrong, from the docs on pg_hba.conf

  Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

If the client's address does not match, it continues looking for one that does.


how to see multiple column values in new line Possible Duplicate:
  how to query multiple row data in single column in oracle?

my table structure is given bellow

deptid  ename
------  ------
10  CLARK,KING,MILLER
20  ADAMS,FORD,JONES,SCOTT,SMITH

but i want the output sould be
like

deptid  ename
----------------
10      CLARK,
        KING,
        MILLER
------------------
20      ADAMS,
        FORD,
        JONES,
        SCOTT,
        SMITH

please tell me how to do it in sql

select deptid, replace(ename, ',', ',\n') from table;

Try to use this query and it will gives you your expected result.


ORA-00838 error after increasing MEMORY_TARGET I've increased MEMORY_TARGET in pfile and then create spfile. I'm getting the same problem as before:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3084M

The memory_target in pfile is 3084M.

I've already increased tmpfs on /dev/shm to 5Gb. And no idea what to do next?

Set MEMORY_TARGET to at least the recommended value.

SQL> CREATE PFILE FROM SPFILE;
MANUALLY EDIT PFILE & CORRECT MEMORY_TARGET VALUE
SQL> CREATE SPFILE FROM PFILE;
SQL> STARTUP

What OS are you using?


DBMS_SCHEDULER event schedule jobs not executed when triggered almost at the same time I moved this question from stackoverflow to here as it was marked on hold as off-topic. On Oracle 10g2 DB I have set up multiple event triggered DMBS_SCHEDULER jobs and all of them are subscribed for the same event queue. The problem is when I call the function that enqueues the message in the advanced queue, therefore triggering the scheduler event all of the subscribed jobs are executed only once.

In the loop I am calling procedure that enqueues the messages in the
queue. (I did check the message count in the queue and it matches the
loop count);
There are multiple jobs (or a single job - I did test both cases the
result is the same) that have event schedule and are subscribed to
the particular queue;
After executing this loop, I do check the message count in the queue
and the instance count of the new jobs in sys.dba_scheduler_job_log.
Result - all the messages in the queue have been processed (meaning
it is empty) and only one instance per each job have been registered
in scheduler_job_log table;
The thing is that if I add DBMS_LOCK.sleep(1); after at the end of the each iteration in the loop then all of the job instances are registered in the scheduler_log_table fine.

Why do all of the other queue messages processed from the queue without running the job? Are there any DBMS_SCHEDULER settings to be adjusted?

And this documented behaviour can be bypassed by setting this attribute to the job before its enabled:

DBMS_SCHEDULER.set_attribute ('my_job_name', 'parallel_instances', TRUE);


How to use variable in Exec statement I'm trying to add my target server to the Multi Server SQ LAgent Job (MSX). We have  multiple versions of SQL Agent jobs and I'm trying to automate adding only related targets according to SQL Server version. I'm trying to add target server using the script below but the the EXEC statement does not recognized the @TargetServer variable.

 Declare @SQLVersion varchar(10)
set @SQLVersion = (SELECT
   CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 2)
          WHEN '8.' THEN '2000'
          WHEN '9.' THEN '2005'
          WHEN '10' THEN '2008'
          WHEN '11' THEN '2012'
          WHEN '12' THEN '2014'
          WHEN '13' THEN '2016'
          WHEN '14' THEN '2017'
          WHEN '15' THEN '2019'
    END)
Declare @TargetServer varchar(20)
SET @TargetServer = 'ProdEMM' --<--Insert target server name here.
IF @SQLVersion > '2014'
EXEC msdb.dbo.sp_add_jobserver @job_id=N'jb3510a9-d75f-4906-b9e3-en74b947d7ev', @server_name = N'@TargetServer'
IF @SQLVersion = '2012'
EXEC msdb.dbo.sp_add_jobserver @job_id=N'9E0GAK8P-0H74-41H7-A619-80TCDFD3E5G7', @server_name = N'@TargetServer'
IF @SQLVersion < '2012'
    EXEC msdb.dbo.sp_add_jobserver @job_id=N'9E0GAK8P-0H74-41H7-A619-80TCDFD3E5G7', @server_name = N'@TargetServer'

ERROR:

Msg 14262, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 88 [Batch Start Line 0]
The specified @server_name ('@TARGETSERVER') does not exist.

What would be the best way to handle this situation?

See @DanGuzman comment above. It did the trick. I have to remove the quotes and N from the EXEC statement for my variable. Credit goes to Dan Guzman.

EXEC msdb.dbo.sp_add_jobserver @job_id=N'jb3510a9-d75f-4906-b9e3-en74b947d7ev', @server_name = @TargetServer


trying to log into correct database without password My SQL 5.6.31 is setup on RHEL 7.2 as follows:

I can log in using the root user account as follows:

mysql -u root -p
Enter Password:

I created two normal users (one via my command line and one via my PHP based application)

Normal User Created via Command Line:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

Normal User Created via my PHP based Web App:

My PHP Web app is just inserting the username into the database (database name: practiceDB) using INSERT INTO command in the code. Say for
example normal user that got created and stored into the database has name peter

Scenario #1:

When I log in into the mysql from the command line using username jeffery and password mypass like the following:

mysql -u jeffery -pmypass

And run the command, show databases, I can see my practiceDB and other default MySQL databases like information_schema.

Scenario #2:

Since one of the normal user (peter) got created by the webapp and is residing in the database (practiceDB), I tried to access from the
commandline the user without using any password as follows:

mysql -u peter

I can get into the mysql command prompt. Infact, forget about specifying the existing username peter in the above commandline.
Even if I specify any thing , I can get into the command prompt. Also, when I checked show databases, I only saw only information_schema database
which is not where my user got stored.

Could anyone tell me why this is happening?

When you use CREATE USER, all it does is add a row to mysql.user with host set to localhost, user set to jeffrey or peter, and the password column is filled in. All the DB privileges are defaulted to 'N'.

When you connect, run this:

SHOW GRANTS;

It will simply say GRANT USAGE. That means you can connect, AND NOTHING ELSE !!!

When you login as root, you must now explicitly tell mysqld to give grant privileges at various levels.

EXAMPLES

This will let jeffrey do everything except give away his privileges

GRANT ALL PRIVILEGES ON *.* to 'jeffrey'@'localhost';

This will let jeffrey do everything, including give away his privileges

GRANT ALL PRIVILEGES ON *.* to 'jeffrey'@'localhost' WITH GRANT OPTION;

This will let jeffrey do everything inside a database called jeff

GRANT ALL PRIVILEGES ON jeff.* to 'jeffrey'@'localhost';

Next time you login as root, run the following

DESC mysql.user;
DESC mysql.db;

This will show you what global (mysql.user) and database (mysql.db) privileges look like.

Please read MySQL Documentation on all the grants you can give out to users.


SQL Server tempdb log file isn't autogrowing, instead getting error “The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'” FYI: moved from Stack Overflow https://stackoverflow.com/questions/56350491/sql-server-tempdb-log-file-isnt-autogrowing-instead-getting-error-the-transac

I'm using SQL Server 2016 and am encountering this error intermittently:

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

It appears that this happens when a transaction is opened and then an expensive tempdb operation is done within the transaction. It seems to me that the tempdb log file should be growing, but it isn't, and instead I"m getting the error above.

I'm able to reproduce this by using the code below. The error comes up everytime I run the code below on the server having this issue. I tried reproducing this on a different SQL Server and couldn't.

BEGIN TRAN

    SELECT * INTO #temp
    FROM sys.all_objects

    DECLARE @iLoops INT = 1000
    DECLARE @i INT = 0
    WHILE @i < @iLoops
    BEGIN
        INSERT INTO #temp
        SELECT * FROM #temp

        SET @i = @i + 1
        DECLARE @rowCount INT = (SELECT COUNT(*) FROM #temp)
        PRINT CONVERT(VARCHAR(16), @i) + ' of ' + CONVERT(VARCHAR(16), @iLoops) + ', rows in #temp: ' + CONVERT(VARCHAR(16), @rowCount)
    END

    DROP TABLE #temp
ROLLBACK

As the reproduction code is running, I'm monitoring the size of the tempdb log file by using this query (which I got from https://www.sqlshack.com/monitor-sql-server-tempdb-database/):

SELECT instance_name AS 'Database',
[Data File(s) Size (KB)]/1024 AS [Data file (MB)],
[Log File(s) Size (KB)]/1024 AS [Log file (MB)],
[Log File(s) Used Size (KB)]/1024 AS [Log file space used (MB)]
FROM (SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN
('Data File(s) Size (KB)',
'Log File(s) Size (KB)',
'Log File(s) Used Size (KB)')
AND instance_name = 'tempdb'
) AS A
PIVOT
(MAX(cntr_value) FOR counter_name IN
([Data File(s) Size (KB)],
[LOG File(s) Size (KB)],
[Log File(s) Used Size (KB)])) AS B

The error appears to happen when the tempdb's log file's used size reaches the allocated size. Why isn't the log file autogrowing? Looking in SSMS, the tempdb log file is set to autogrow and has no maximum size. The drive on which the log file lives has over 20GB of free space. The tempdb database is using the simple recovery model.

This is happening because the disk is full.

I was given bad information. The primary DBA was out when I had this problem and I asked one of our secondary DBAs to check the disk space on the drive where the tempdb log file lives because I don't actually have access to this server. The secondary DBA told me that there was 25GB free space on that drive. In actuality, there was less than 10MB free space.


SQLite - get every row that has a common entry in the second column I am working on an Android studios project and I have a table that has two columns item and listname.

I want to get all the items that have a common Listname so in this case if I were looking for items that have Market as a listname I only want to get the last 4 from the table, I only want to return the items. I have this but it keeps crashing.
   public Cursor getItem(String listname) {
    SQLiteDatabase db = this.getWritableDatabase();

    String selectQuery = "SELECT  * FROM " + LIST_ITEM_TABLE + " WHERE "
            + ItemListName + " = " + listname;

    Cursor c = db.rawQuery(selectQuery, null);

    return c;
}

String have to be in single quotes
Also i don't see ItemListName  be filled, i hoe there is Listname in it
See like:
String selectQuery = "SELECT  * FROM " + LIST_ITEM_TABLE + " WHERE "
        + ItemListName + " like  '" + listname +"'";


What is the Server name for SQL Server2008? I have sucessfully installed the Microsoft® SQL Server® 2008 Management Studio Express
from the following link:
http://www.microsoft.com/download/en/details.aspx?id=7593
file name: SQLManagementStudio_x86_ENU.exe

When I opend the MS SQL Server Management Studio, it asks for the Server Name which I am not sure what it is nor the login name and password.

Can someone please explain?

Thanks much!

The server name (and instance name) for SQL Server Express by default would be localhost\sqlexpress (or .\sqlexpress, or yourMachineName\sqlexpress, or x.x.x.x\sqlexpress, where x.x.x.x is your machine's IP address).  That is provided two things:

You on the machine that houses the database engine
You kept the defaults for SQL Server Express installation to make it a named instance, named SQLEXPRESS

As for the login.  Most likely if you just installed it, select Windows Authentication and try to connect.  I'm guessing you have added your account as SA priv.  Did you create an sa account during installation?  If so, and you enabled Mixed Mode authentication then you can log in with the sa credentials you created initially.


Database Structure with Recurring Payment I'm working on a new database structure for a new website.

I need to accept 2 types of payment : OneShot and Recurring (monthly)

In a single order (basket), I can put a product "OneShot" and "Recurring" and paid both.

Here is my actual structure (I do not provide all columns) :

Transaction table can have multiples lines. If I make an order with OneShot Product ID:89 - 70$ and Recurring Product ID:129 - 29$/Month

Table Order
OrderId / OrderNumber / CustomerID / TotalAmount / CompanyId
998 / AA-22-ER / 11 / 99$ / 2

Table OrderItem
OrderId / ProductId / UnitPrice / Quantity / Created
998 / 89 / 70$ / 1 / 2017-09-17
998 / 29 / 29$ / 1 / 2017-09-17

Table Transaction
TransactionID / OrderID / Status / Amount / Created
129 / 998 / P / 99$ / 2017-09-17 ==> OneShot + First Recurring Payment
345 / 998 / P / 29$ / 2017-10-17 ==> recurring payment
523 / 998 / P / 29$ / 2017-11-17 ==> recurring payment

How can I deal with rebill and/or refund?

For example, I want to refund the OneShot Product ID:89, do I need to stock this information in Transaction Table with Status colum? But how can I know quickly which OrderItem is still paid?

In another way, how can I deal with recurring payment? Hope I'm  clear in my question.

I think, I have a beginning of a solution.

Customer cannot buy 2 subscriptions products in one sale.

Here are my use cases :

In one Sale, 1 product is normal (70$), 1 is a subscription for 12 months (29$/Month). Customer ask a refund on december, so subscription stop at this moment.

Table Orders
ID  /   ClientID    /   Amount  /   AmoutVAT    /   Reference   /   Company_ID  /   Created /
998 /   112 /   99$ /   89$ /   2-20170927-SZZ-345  /   2   /   2017-09-17

Table OrderItem
ID / OrderId / ProductId / UnitPrice / Quantity / Created / Expire_Date
1021 / 998 / 89 / 70$ / 2017-09-17 /    2099-12-12
1022 / 998 / 29 / 29$ / 2017-09-17  /   2017-12-17

Table Transaction
TransactionID / OrderID / Status / Amount / Created / Refund
129 / 998 / P / 99$ / 2017-09-17 / 0
345 / 998 / P / 29$ / 2017-10-17 / 0
523 / 998 / P / 29$ / 2017-11-17 / 0
657 / 998 / R / 29$ / 2017-12-17 / 29$

Table TransactionEvent
TransactionID / Event   / Created   /   Amount  /   Reason
657 /   R   /   2017-12-23  /   29$ /

One sale with 1 normal product (19$)

Table Orders
ID  /   ClientID    /   Amount  /   AmoutVAT    /   Reference   /   Company_ID  /   Created /
997 /   189 /   19$ /   16$ /   2-20171027-XXS-990  /   2   /   2017-09-17

Table OrderItem
ID / OrderId / ProductId / UnitPrice / Quantity / Created / Expire_Date
1009 / 997 / 78 / 19$ / 2017-09-17 /    2099-12-12

Table Transaction
TransactionID / OrderID / Status / Amount / Created / Refund
119 / 997 / P / 19$ / 2017-09-17 / 0

One sale with 1 subscription product for 29$/month on 12 months

Table Orders
ID  /   ClientID    /   Amount  /   AmoutVAT    /   Reference   /   Company_ID  /   Created /
1002    /   234 /   29$ /   25$ /   2-20171029-AAS-120  /   2   /   2017-10-29

Table OrderItem
ID / OrderId / ProductId / UnitPrice / Quantity / Created / Expire_Date
1044 / 1002 / 99 / 29$ / 2017-10-29 /   2018-01-29

Table Transaction
TransactionID / OrderID / Status / Amount / Created / Refund
789 / 1002 / P / 19$ / 2017-10-29 / 0
844 / 1002 / P / 19$ / 2017-11-29 / 0
901 / 1002 / P / 19$ / 2017-12-29 / 0

Systems I have worked with in the past stored for each customer a row for each recurring product they have. Each row has a product id, start date and anticipated end date, and a "billed to date". Each month when you bill them you increment the date.

From that you generate an invoice (storing the values to make up the invoice), and they customer makes payments against that. Their balance is calculated from opening balance + invoices +/- payments.

It gets complicated when product prices change over time, or discounts only apply for a specific period. You need to store enough detail that you can recreate the invoice correctly at that point in time.  If you give a refund that you don't want to refund too much, and if required can you pro-rate adjustments?

A good approach is to ask yourself what level of detail do you need to see on an invoice. If I had to recreate the invoice, adjust it or provide a refund, where would I get the data from. You need to either store the detail or be able to reproduce it reliably. Can you?

The next level to consider is, what accounting information to I need to report? How much did we sell last month? what discounts applied? how many customers have paid? who owes us money and how much?(debt mngt) How much tax have i charged? (is anything exempt? different rates?)

Look and the end results you need and work back from there.


Pulling autogrowth events by file name from default trace I use below query to get auto-growth event occurred to databases in a sql server.

DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1
SELECT COUNT(*)as no_of_autogrowths,
sum(duration/(1000*60)) as time_in_min,
Filename
FROM ::fn_trace_gettable(@trcfilename, default)
WHERE (EventClass = 92 OR EventClass = 93)
GROUP BY  Filename

It outputs number of auto-growths, time taken for auto-growth and logical name of the file.
But I want physical name of the file (mdf and ldf file name) instead of logical name.I  don't know whether from sys.traces I will get physical name or please help me with an alternate way to do it.

As Remus mentioned in a comment, you have to join the trace table result to sys.master_files to get the physical file names.

Here is a completed query. Note that the duration in the trace is in microseconds, and I have adjusted the computation accordingly to output a more sensible metric (hopefully you don't have actual minutes of auto-growth happening):

DECLARE @trcfilename nvarchar(2048);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1;

SELECT
    DB_NAME(mf.database_id) AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.physical_name AS PhysicalFileName,
    a.NumberOfGrowths,
    CAST(a.DurationOfGrowthsInSeconds AS decimal(18, 3)) AS DurationOfGrowthsInSeconds
    FROM
    (
        SELECT
            tt.DatabaseID AS database_id,
            tt.FileName AS LogicalFileName,
            COUNT(*) AS NumberOfGrowths,
            SUM(tt.Duration / (1000 * 1000.0)) AS DurationOfGrowthsInSeconds
            FROM sys.fn_trace_gettable(@trcfilename, default) tt
            WHERE (EventClass IN (92, 93))
            GROUP BY
                tt.DatabaseID,
                tt.FileName
    ) a
    INNER JOIN sys.master_files mf ON
        (mf.database_id = a.database_id) AND
        (mf.name = a.LogicalFileName);


SQL Server Restore DB from MDF file, lost LDF and DB was not cleanly shutdown My dev DB server was formatted by some admin without my knowledge. Thankfully I had mdf file kept in some other drive but ldf was in its default location. Since I don’t have DB backup I am now left with mdf file. I googled and found various tricks to restore DB from mdf file. Since database was not cleanly shutdown none of the tricks worked. I have SQL Server 2008 sp2 installed in my machine. As I remember lost DB server was SQL Server 2005 instance. Please let me know how I could recover my database.

Tricks i tried:

I tried to attach the mdf from SSMS by removing the log file item from database to attach list.
CREATE DATABASE Monitoring ON (FILENAME = N'D:\Dump\Data\Monitoring.MDF') FOR ATTACH_REBUILD_LOG
sp_attach_single_file_db N'Monitoring', N'D:\Dump\Data\Monitoring_Data.MDF'

In all above I got

  Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options

Then I tried DBCC REBUILD_LOG('Monitoring',N'C:\Program Files ..MSSQL\Data\Monitoring_log.ldf') which also resulted in

  Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options

There is a hack you can try: where you create a dummy DB with the same name and size as your old data and log files. Then you have to stop SQL server, replace the new empty files with the old files that have data in them.

Check out this blog link by Paul Randal where the hack is explained in detail. It looks like you have tried the first method mentioned, but read along and try the other methods. Please let us know how it goes. I do not know how well it will work without log files, but it is worth a try.


MSSQLSERVER service does *not* start, port 1433 in use Sorry to raise if this has been asked before.
The SQL Server service is defined as manual, whenever I start it I get (event viewer):

Server TCP provider failed to listen on [ 'any'  1433]. Tcp port is already in use.
The SQL Server (MSSQLSERVER) service terminated with service-specific error
Only one usage of each socket address (protocol/network address/port) is normally permitted.

Odd thing both my ethernet and WiFi adapters have ipv6 disabled and it has ran fine for ages.
Environment details:

Windows 2008-R2;
SQL Server 2012 BI SP1;
all MSFT patches applied.

Find out what process is already using TCP 1433.  You can do this through the use of TCPView.  That utility is a sysinternals tool that gives you the information you need.  Just start it, pause the data view, and look at which process is using the local port of 1433 with the TCP protocol.

Note: You'll probably want to uncheck "Resolve Addresses" in the Options menu item so that you will be able to see the default instance port number.

Then once you know the process that is already using that port, you can take the appropriate action or reach out to the appropriate personnel that may know more about the process, why it's there, etc.

Or you could always change the port in which your SQL Server instance is listening on, but I'd personally be extremely curious as to which process is already using that port.


Any DBMS with table structures and data defined separately? I'm working on an application which is  being used in multi-tenant mode, and have created a MySQL database instance, where each tenant has its own   schema. The problem I'm facing is, because the application is constantly changing, due to addition of features, etc., the database tables are also changing very often. This means, I have to keep changing the table structures in all schemas for every single change.

Is there any database management system (DBMS) where table structures are defined once, and each instance only has the table data?

If you have set up separate logins (passwords) for every tenant maybe you could consider using one "main" schema and building a database view(s) for each table filtered by condition "where login = current_user()" ("login" column must be properly handled in your inserts).
Then you can grant access to users only to views.

This way you could filter data by logged user and also you minimize your work dedicated to keeping database schema/structures up-to-date - you have to update only 2 structures: database tables and database views.

But honestly I would rather advise you to automate process of deploying changes to databases - store every change to database schema in script files and then automate executing scripts on databases  (some shell/bash scripts where parameters are database names to handle).


Is Oracle more demanding environment for a DBA than SQL Server? I don't know Oracle at all but I have heard Oracle DBA's saying that working with Oracle (as a DBA) is "harder" and more difficult and more demanding than working with SQL Server. Does this statement have a basis?

Oracle has a significantly higher barrier to entry than SQL Server. Does that make working with Oracle "harder" than SQL Server, well that depends on what is meant by "harder" and where you're setting the bar.

I've very rarely encountered enterprise, Oracle backed systems, that didn't have dedicated Oracle DBAs/developers on the development team. I frequently work with SQL backed systems that have been developed without any DBA expertise whatsoever.

Why?

Microsoft are smart. They've built a database platform that competes with Oracle but made it accessible to anyone that can knock up a database in Access.  The notion of Code First, for anything other than the most noddy of departmental applications, scares me witless. But doesn't it make great commercial sense?

Hide the complexity. Make it easy for anyone to build on your database platform. Later, once they're locked in, they can find all the knobs, levers and switches they'll need to get the best out of the product.

Oracle requires expertise on knobs, levers and switches before you can create your first table. To the novice, that makes Oracle more difficult to get started with. Not technically more challenging, just in comparison to SQL Server, plain difficult.

If you're a developer that just wants to "get things done", which platform are you likely to lean toward?

Is an expert Oracle DBA more expert than his SQL Server equivalent? No.
Is Oracle technology more complex than SQL Server? No.
Is difficult or demanding a good benchmark for anything? Commuting across London on the underground is both but I don't feel superior for doing so :)


mysqldmup with data only Is there a way to backup data using mysqldump --no-create-info ... where the inserts in the backup file will look like this:

insert into t1(col1, col2)values(val1,val2);

instead of

insert into t1 values(val1,val2);

The problem with the second way is that when the table structure changes, the insert will not work. With the first way, the table structure, e.g, one more column is added, will not affect the insert statement.

I have to use the mysqldump. Any suggestions please?

Use --complete-insert

Use complete INSERT statements that include column names.

Source: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_complete-insert