How can I write windowing query which sums a column to create discrete buckets? I have a table which includes a column of decimal values, such as this:

id value size
-- ----- ----
 1   100  .02
 2    99  .38
 3    98  .13
 4    97  .35
 5    96  .15
 6    95  .57
 7    94  .25
 8    93  .15

What I need to accomplish is a little difficult to describe, so please bear with me.  What I am trying to do is create an aggregate value of the size column which increments by 1 each time the preceding rows sum up to 1, when in descending order according to value.  The result would look something like this:

id value size bucket
-- ----- ---- ------
 1   100  .02      1
 2    99  .38      1
 3    98  .13      1
 4    97  .35      1
 5    96  .15      2
 6    95  .57      2
 7    94  .25      2
 8    93  .15      3

My naive first attempt was to keep a running SUM and then CEILING that value, however it doesn't handle the case where some records' size end up contributing to the total of two separate buckets.  The below example might clarify this:

id value size crude_sum crude_bucket distinct_sum bucket
-- ----- ---- --------- ------------ ------------ ------
 1   100  .02       .02            1          .02      1
 2    99  .38       .40            1          .40      1
 3    98  .13       .53            1          .53      1
 4    97  .35       .88            1          .88      1
 5    96  .15      1.03            2          .15      2
 6    95  .57      1.60            2          .72      2
 7    94  .25      1.85            2          .97      2
 8    93  .15      2.00            2          .15      3

As you can see, if I were to simply use CEILING on crude_sum record #8 would be assigned to bucket 2.  This is caused by the size of records #5 and #8 being split across two buckets.  Instead, the ideal solution is to reset the sum each time it reaches 1, which then increments the bucket column and begins a new SUM operation starting at the size value of the current record.  Because the order of the records is important to this operation, I've included the value column, which is intended to be sorted in descending order.

My initial attempts have involved making multiple passes over the data, once to perform the SUM operation, once more to CEILING that, etc.  Here is an example of what I did to create the crude_sum column:

SELECT
  id,
  value,
  size,
  (SELECT TOP 1 SUM(size) FROM table t2 WHERE t2.value<=t1.value) as crude_sum
FROM
  table t1

Which was used in an UPDATE operation to insert the value into a table to work with later.

Edit:
I'd like to take another stab at explaining this, so here goes.  Imagine each record is a physical item.  That item has a value associated with it, and a physical size less than one.  I have a series of buckets with a volume capacity of exactly 1, and I need to determine how many of these buckets I will need and which bucket each item goes in according to the value of the item, sorted from highest to lowest.

A physical item cannot exist in two places at once, so it must be in one bucket or the other.  This is why I can't do a running total + CEILING solution, because that would allow records to contribute their size to two buckets.

I am not sure what type of performance you are looking for, but if CLR or external app is not an option, a cursor is all that is left. On my aged laptop I get through 1,000,000 rows in about 100 seconds using the following solution. The nice thing about it is that it scales linearly, so I would be looking at a little about 20 minutes to run through the entire thing. With a decent server you will be faster, but not an order of magnitude, so it would still take several minutes to complete this. If this is a one off process, you probably can afford the slowness. If you need to run this as a report or similar regularly, you might want to store the values in the same table un update them as new rows get added, e.g. in a trigger.

Anyway, here is the code:

IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable;

CREATE TABLE dbo.MyTable(
 Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 v NUMERIC(5,3) DEFAULT ABS(CHECKSUM(NEWID())%100)/100.0
);

MERGE dbo.MyTable T
USING (SELECT TOP(1000000) 1 X FROM sys.system_internals_partition_columns A,sys.system_internals_partition_columns B,sys.system_internals_partition_columns C,sys.system_internals_partition_columns D)X
ON(1=0)
WHEN NOT MATCHED THEN
INSERT DEFAULT VALUES;

--SELECT * FROM dbo.MyTable

DECLARE @st DATETIME2 = SYSUTCDATETIME();
DECLARE cur CURSOR FAST_FORWARD FOR
  SELECT Id,v FROM dbo.MyTable
  ORDER BY Id;

DECLARE @id INT;
DECLARE @v NUMERIC(5,3);
DECLARE @running_total NUMERIC(6,3) = 0;
DECLARE @bucket INT = 1;

CREATE TABLE #t(
 id INT PRIMARY KEY CLUSTERED,
 v NUMERIC(5,3),
 bucket INT,
 running_total NUMERIC(6,3)
);

OPEN cur;
WHILE(1=1)
BEGIN
  FETCH NEXT FROM cur INTO @id,@v;
  IF(@@FETCH_STATUS <> 0) BREAK;
  IF(@running_total + @v > 1)
  BEGIN
    SET @running_total = 0;
    SET @bucket += 1;
  END;
  SET @running_total += @v;
  INSERT INTO #t(id,v,bucket,running_total)
  VALUES(@id,@v,@bucket, @running_total);
END;
CLOSE cur;
DEALLOCATE cur;
SELECT DATEDIFF(SECOND,@st,SYSUTCDATETIME());
SELECT * FROM #t;

GO
DROP TABLE #t;

It drops and recreates the table MyTable, fills it with 1000000 rows and then goes to work.

The cursor copies each row into a temp table while running the calculations. At the end the select returns the calculated results. You might be a little faster if you don't copy the data around but do an in-place update instead.

If you have an option to upgrade to SQL 2012 you can look at the new window-spool supported moving window aggregates, that should give you better performance.

On a side note, if you have an assembly installed with permission_set=safe, you can do more bad stuff to a server with standard T-SQL than with the assembly, so I would keep working on removing that barrier - You have a good use case here where CLR really would help you.


setting statistics time on/off giving different results I am tuning an SP and re-written that to optimize. Now I am comparing old code and new code in terms of timing using "setting statistics time on/off".

While setting  time statistics off, my new code is performing well at least 4 times better than old code (Old code is taking 4 secs and new code is taking 1 secs to execute), but when I am setting time statistics On, my new code is taking approx 12 secs and old code is taking around 7 secs.

Why new code is performing badly after setting time stats on? Looks like there is some cost of time stats also and in my new code, that cost is very higher in comparison to old code. Am I right? If so what is that?

If there are user defined functions in your code, there can be significant overhead to setting STATISTICS TIME ON. See here.

More importantly, be REALLY careful when using scalar UDFs. They will inhibit parallelism for your query, and have a nasty habit of running once per row returned by your query.

The impact they have on STATISTICS TIME should be the least of your concerns.


Move named instance to another server To be honest I'm not a DBA expert. I get asked by one of my clients in terms of moving a SQL Server development instance that is stored in the production server (source) to another server (destination)

The source server has two volumes E and F:

Volum E stores Databases (E:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\DATA)
Volum F stores Logs (F:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\Data)

I have backed one database (Backup_Test.bak) for testing purpose in the source server and restored to the destination by using the following T-SQL via SQL Server Management Studio in the destination:

RESTORE DATABASE Backup_Test
FROM DISK = 'C:\SQLBackup\Backup_Tesk.bak' \\\Stored in the destination
WITH REPLACE,
MOVE 'Backup_Test' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\DATA\Backup_Test.mdf',
MOVE 'Backup_Test_log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\Data\Backup_Test_log.ldf'

After executing, I get the error

  Msg 102, Level 15, State 1, Line 4
  Incorrect syntax near 'MOVE'.

In the destination, someone installed SQL Server and locate all things stored in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER.

I'm suspecting that different location on both the source (E & F) and destination (only C) causes my issue. If so, do I have to re-install SQL Server on the destination?

The version on the source is SQL Server 2008 and on the destination is /R2. The named instance on the source is SQLSP1\SPDEV and on the destination is SQLSP2. Do I have to create an alias SQLSP2\SPDEV?

Many thanks for your help. I really appreciate.

Regards,
-T.s

You are not moving instances, you are copying databases via backup restore. All you need is to get the syntax right, as per RESTORE spec:

<general_WITH_options> [ ,...n ]::=
--Restore Operation Options
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
          [ ,...n ]

Each WITH option is separated from the preceding with a comma. What you're missing is the , and you need to move the files into the destination location:

RESTORE DATABASE Backup_Test
FROM DISK = 'C:\SQLBackup\Backup_Tesk.bak' \\\Stored in the destination
WITH REPLACE
 , MOVE 'Backup_Test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test.mdf',
 , MOVE 'Backup_Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test_log.ldf'

Judging from your folder names (MSSQL10 vs. MSSQL10_50) your source server is SQL Server 2008 (not R2) and only the destination is SQL Server 2008 R2. Which means that the copy will upgrade the database and the database will never be possible to copy/restore on the original non-R2 instance.


Mixed granularity weeks/months time series data in a data warehouse? I'm not sure that "anti-aliasing of time series data" is the correct terminology to use, so let me explain:

I have some sources of data that are aligned quarterly, mostly to do with quarterly running costs. I have some other sources of data, some of which are aligned weekly, and some monthly, and these are mostly related to transactions that took place. By the term 'anti-aliasing', I am referring to the problem of how to represent this data on a common time granularity, so that it can all be integrated together into a data warehouse.

How do I get this data which does not align through time, into a data warehouse? The alignment problem mostly stems from the fact that weeks do not exactly fit into months or quarters. The months against quarters issue is less of a problem, since 3 months make a quarter.

For example, if I know the quarterly running cost, should I divide that up, and attribute an equal share of it to each day in the quarter. Then if I know the number of transactions completed on a weekly basis, should I divide them up and attribute them equally to each day of the week. That would artificially break down the grain of the data into days, and allow me to roll it up by week, month or quarter.

(Is there a more correct terminology in data-warehousing for this problem?)

I don't know if there is a better term for fixing the problem, but the name for the problem in the first place is "mixed granularity" or "overlapping granularity". It is most common in calendar related data but can occur in data split by geography (different sources using different boundaries when aggregating their results) especially when reporting over time (as official boundaries can change in significant ways some times deliberately (https://en.wikipedia.org/wiki/Gerrymandering)). Fixing the issue so you have some useful merging of the data in your warehouse depends on how you need to report upon the data.

If you are reporting on calendar quarters then it is fairly easy: you know the three months that go in each quarter, and 13 weeks go into each too. As weeks and quarters don't align perfectly you are going to have some data at each end of the quarter that has the possibility of not quite being counted right and there are one or two extra days (two in a leap year). One suggestion would be to count January the 1st as day 1 of week 1, and count weeks 1 through 13 quarter 1 and so on. For the one or two days that make up the partial week 53, add them to quarter 4. There is going to be some "error" here but that is unavoidable and should be more than small enough (given large enough samples/figures) to no significantly affect the trends you are trying to detect and report upon.

The other that springs to mind you have already suggested: average out all the data sources, breaking them down to a common such as a day during your ETL process - so record of £123,000 sales in June becomes £4,100 counted against each day, and £35,000 in the week beginning averages to £5,000 on each of the seven days. You can then aggregate this back up to whatever larger granularity you desire. Again you are losing some definition in your data, but the results will be less "lumpy" than, for example, mapping whole weeks to months and having some with four and some with five.

Of course the ideal solution is to try get hold of the original data before it was rolled up at all so you have it all at the finest granularity to start with, but that is often not practical or not even possible because only the aggregated data remains.


PostgreSQL import XML to empty database, automatically create columns The server is Linux but the development machine is Windows so I'm using psql command line. I tried \i C:/Users/John/Desktop/Parts.xml which did not work so either \i doesn't work with XML or I'm missing some syntax.

I have an empty database (and the user has permissions as far as i can tell). How do I import XML and have it's columns automatically created based on the column defined in the XML file? The XML file is an export of a Microsoft Access 2010 database table.

I was able to automate the process enough, keyword being enough. To help anyone else dealing with exporting from Microsoft Access and importing to PostgreSQL I'm providing details that helped us work through the errors we encountered. While I'm fairly competent with MySQL and new (though still reasonably well at PostgreSQL) the people who setup the various Microsoft Access databases didn't setup the databases/tables/columns well (understatement).
First download "MS Access to PostgreSQL", it's freeware and I plan to make a donation to them. Currently on their download page the download link is on the right side of the table, threw me off for a little bit.
http://www.bullzip.com/products/a2p/info.php
Secondly I needed to download the "2007 Office System Driver: Data Connectivity Components" which works fine with Office 2010...
http://www.microsoft.com/en-us/download/details.aspx?id=23734
Thirdly I opened up MS Access to PostgreSQL and followed the directions having the data imported to an existing empty database. Keep in mind that the program (at least by default) will spawn errors if there are tables already there with the same names (it won't automatically append/overwrite). Thankfully it provides a checkbox list of tables so when you've confirmed via psql or pgAdmin III (you must click on the left column before pressing F5 or it won't update the database/tables information) that at least some of the data has been imported it is time to deal with errors.
There were two error types that I've encountered for the most part thus far with just two separate databases. For accounting the person who setup Access used a proprietary currency type for some of the columns, the program didn't like that so I was able to maintain the row values by simply converting it to the text type. I'm correcting the database structure and all that after I've freed it from Microsoft's shenanigans as I'm trying to reduce the complexity of exporting it. You can change the column type by choosing the table, then clicking on the Fields menu in Access 2010, then under the secondary Formatting hierarchy you'll find "Display Type:".
Apparently the program also does not like it when a column shares the same name as it's table so I simply added a prefix or suffix underscore and make note of it to correct after the dubious process of migrating the data is complete.
In general a necessary practice in Access is to right-click on the table name under the Tables object list, at the bottom of the context menu should be a Check Web Compatibility menu item. If it finds errors it will create a whole new table in the database; in Access 2010 URLs to the Microsoft website were clickable.
Another error I encountered was binary data, an image that exceeded the programs ability to import. I exported the data as XML and browsed to determine which row had the data, found the ID in Access and removed it. You can save it (in the Microsoft way at least) by copying the row itself and paste it in to Word...how to export it as a normal image I'm not sure offhand.
The following are my notes that I've written down to quickly repeat the process to get Access data migrated to PostgreSQL. I originally was going to just export as XML and import to PostgreSQL though I was unable to find a working solution in that manner and I would have still have had to deal with some of the issues mentioned here.

Access to PostgreSQL - Import Notes
1.) Use freeware "MS Access to PostgreSQL".
2.) When encountering errors use MS Access table web compatibility check (right click table).
3.) If further stumped...
A.) Allow import to create columns.
B.) Export Access to Excel.
C.) Export Excel to CSV/DOS.
D.) Ensure that the first id is NOT 0, must be 1 (may be subjective though).
4.) If table data import success then repeat step 1 but unselect trouble table AND already imported tables.


problem transforming table to First normal form I have table named Agent. It has attribute named agent_status. It's value can be on or off. I want to conform this table to first normal form. Do i have to create different table for this attribute?

It is better to create a separate table for agent_status. Now it seems easy to add agent_status column in agent table itself. But with my little experience I can tell you, business requirements may change in future, sometime too often. Our data model must be developed in a way it should accommodate all the future requirements and changes without much alteration on the underlying model.

In some cases, business will required to track history of agent_status over a period of time (@Ziggy) and, there are possibilities, where you are required to add new status values, also you may required to change status name in future in order to accommodate your business requirement.

If you are sure that, your business requirement will not change in future, you can add agent_status column in agent table itself, or else create a separate entity named agent_status.

I hope this answer will give you some idea, Thank you.


Mysql: 1000 select queries vs 1 query ( select .. in ) I have just read about N+1 queries problem. So I have the doubt about the performance of 2 ways.
The way 1: I have 1000 queries like that:

select * from topic where user_id = 1
select * from topic where user_id = 2
...
select * from topic where user_id = 1000

I supposed the topic table has 1.000.000 rows and each query uses binary search, so I think the Big O's way is:

1000 * log2(1.000.000)

The way 2: I have 1 query:
select * from topic where user_id in (1,2,..., 1000)
I don't know the algorithm of 2nd way. So can you explain me the algorithm of (select .. in) query. Is there any case the 1st way is better than 2nd way?

to get the same result, you need to UNION the result and you can't union 10000 selects. the limit is 63 table references per query
Further IN clause has also a limit of elements you can use.

The number of values in the IN() list is only limited by the max_allowed_packet value.

see manual
As you have many ids, you should try
select t1.*
from topic t1
      INNER JOIN
     (SELECT  user_id FROM topic WHERE user_id <= 1000) t2 ON t1.user_id = t2.user_id

On big numbers for the IN you will get faster results.


which database engine is best for full text search on a 10M rows table? I'm creating a search engine and needs to search keywords in a text field (about 300 words in each text field, unstructured).

I'm thinking about noSQL\MySQL+Sphinx, the performance impact here is crucial.

which DB engine should I use?

First, relational systems are not really all that great for full text search.  PostgreSQL's full text search isn't bad as things go but it has important limitations.  For example you cannot search for neighboring words.  So you are going to have to break this part out to another system.  You are far better off just getting a dedicated system for that part.  Most of the RDBMS-based solutions tend to be good enough to use when using other filters as well for general db queries, but that's not the same thing as being good enough on their own.

The second thing to keep in mind is that this is a specific type of search query.  Get a dedicated engine to handle it.

That doesn't mean ditch the RDBMS though.  You very likely are going to need to track things which an RDBMS does a good job at and if you go with a fully non-relational approach you will paint yourself into corners.  NoSQL means saying "No, you can't run an ad hoc report aggregating the data in that way because we aren't storing it in that way."  In general you want to be storing everything except the actual documents and full text indexes in an RDBMS.


When should you shrink your transaction log? We do log backups every 3 minutes but despite that the transaction log has still managed to grow to 200+ gb on disk.  Its almost completely empty after log backup.  How do you know if your transaction log has become too big and needs shrinking?  How do you determine the optimal size?

Transaction log sizing is important also because of VLF fragmentation which could wreck havoc on your restores and transaction log performance.  Ensure your tlog VLF isnt heavily fragmented first I'd say.
Secondly a good way Paul Randal stated was to let your app run natively for a week, and see how big it gets. Make sure you do your regular maintenance on it and such.
Now maybe it gets so big because of ad hoc maintenance, queries, badly written queries, but to find the current good size that would work.
https://www.sqlshack.com/what-is-sql-server-virtual-log-file-and-how-to-monitor-it/


Counting postgres open connections without SQL query or connecting to postgres itself For a monitoring script I need to count the number of open connections to a postgres database. This is done easily enough by opening up a client connection and running a count on the pg_stat_activity table.

However, if the maximum number of connections has already been reached, the connection, and check script, will hang waiting for the connection. I'd prefer to not have this as it complicates the check script and could hang for an indeterminate amount of time. Also, although it is only one connection, opening up a client connection adds to the connection pool which is not ideal.

Looking at the pg_stat_activity table there is a column listing the process id for the connection. This gave me the idea of using the linux command ps to list the current running process and grep out those that are related to protgres. Specifically, the command I am using is:

  ps -ef | grep 'postgres' | grep 'root' | grep -v 'grep' | grep -v 'su - postgres'| wc -l

This seems to match the number of entries in pg_stat_activity, but is sometimes, but not always, off by 1 (can be above and below).

Is there anything that might be in the process list that I should include or grep out?
Or is there another way to do this without using a db connection?

A safe and reliable way is to use netstat.

I would start with netstat --numeric-ports | grep 5432 as this will show you active connections.  The problem of course is that there may be some false positives.  The following should work for most cases though:

netstat --numeric-ports | awk '/(localhost:5432|PGSQL.5432)/ {print $0}' | wc -l

Inspecting the process table (with ps) won't necessarily give you the number of open connections: there is no reason you might not start more worker processes than you need at startup.


Best way to designate unit of weight in PostgreSQL I am working with a simple database with tables of users, products and orders, but previously the products table was not very well designed in my opinion in that for the weights column, it just had a number, but its unclear whether its in pounds or ounces.
I have tried to find documentation on how to designate pounds and ounces as a unit of weight for a table, but I have not been successful. Can anyone suggest a way to do this? Again the column is simply called weight, how do I make it clear that it starts at 2 ounces up to 1 pound as a unit of weight inside of Postgres?

My first instinct is to use the metric system, but if you have to use imperial measurements, I suggest that you store the weights as ounces but have [Computed | Calculated | Virtual | Derived] columns - actually called Generated in PostgreSQL - see this wiki article about Virtual columns - which says that the "The industry standard" is:
column_name [type] GENERATED ALWAYS AS (expression)

PostgreSQL is normally very good about sticking to the SQL standard! The PostgreSQL documentation on generated columns is available here.
Imperial units - i.e. weight in ounces (see below for metric):
I would have something like this (see fiddle here):
CREATE TABLE foo
(
  field_1 SERIAL PRIMARY KEY,
  field_2 VARCHAR (25) NOT NULL,
  --
  --  Other fields
  --
  weight_oz INTEGER NOT NULL,
  pound_oz INTEGER GENERATED ALWAYS AS (weight_oz/16) STORED,   -- INTEGER division
  ounce_oz INTEGER GENERATED ALWAYS AS (MOD(weight_oz, 16)) STORED  -- Remainder
);

And then populate it with some sample data:
INSERT INTO foo (field_2, weight_oz) VALUES ('asdfa', 25), ('adfads', 78);

And then:
SELECT * FROM foo;

Result:
field_1 field_2  weight_oz  pound_oz    ounce_oz
      1   asdfa         25         1           9
      2  adfads         78         4          14

Now, unfortunately, PostgreSQL doesn't yet have the VIRTUAL keyword for the storage type of the generated field (unlike, say, MySQL) which would be better here, but AIUI, it's on the way.
Also, you didn't mention your version of PostgreSQL (you should always do this), but methods to do this in versions prior to 12 are explored here.
S.I. (Système International) units - i.e. weight in kg.
I've added a method for dealing where the weight is stored in kg (S.I. units are the ideal storage medium for any quantity IMHO). From here, "1 kg = 35.27396195 oz". Conversely, the mass m in ounces (oz) is equal to the mass m in kilograms (kg) divided by 0.02834952 (the reciprocal of 35.xxx).
From the PostgreSQL documentation, a REAL variable:

On most platforms, the real type has a range of at least 1E-37 to
1E+37 with a precision of at least 6 decimal digits.

That should be sufficient for most needs - if not, you could use NUMERIC or DECIMAL types, but (from the same page):

However, calculations on numeric values are very slow compared to the
integer types, or to the floating-point types

This method works to the nearest ounce - I've tested here and it appears to work well. Of course, if you want fractional ounces to eliminate potential rounding errors, just keep it REAL (pardon the pun!)! A fiddle is available here:
CREATE TABLE foo
(
  field_1 SERIAL PRIMARY KEY,
  field_2 VARCHAR (25) NOT NULL,
  --
  --  Other fields
  --
  weight_kg REAL NOT NULL,
  pound_kg INTEGER GENERATED ALWAYS AS (((weight_kg * 35.273961)::INT)/16) STORED,
  ounce_kg INTEGER GENERATED ALWAYS AS (MOD((weight_kg * 35.273961)::INT, 16)) STORED
);

Populate:
INSERT INTO foo (field_2, weight_kg)
VALUES
('xxxxx', 0.85), ('asdfa', 25), ('adfads', 78);

Then:
SELECT * FROM foo;

Result:
field_1 field_2 weight_kg   pound_kg    ounce_kg
      1   xxxxx      0.85          1          14
      2   asdfa        25         55           2
      3   adfads       78        171          15


create trigger mysql why this is not work?? i want if i'm insert to this table the table in before i'm insert get decrement....

CREATE TRIGGER prosesqc2
AFTER INSERT ON quality2
FOR EACH ROW
BEGIN
INSERT INTO stock_barang
SET  idbarang = NEW.idbarang,
     lolos_qc_sfl=New.terima,
     dalam_proses=new.terima
ON DUPLICATE KEY
UPDATE lolos_qc_sfl=lolos_qc_sfl+New.terima
   and dalam_proses=dalam_proses-new.terima;
END

why this is not work??

Wrong and in UPDATE section, comma needed;
The trigger contains single statement, so BEGIN-END block is excess.

CREATE TRIGGER prosesqc2
AFTER INSERT ON quality2
FOR EACH ROW
INSERT INTO stock_barang
    SET  idbarang     = NEW.idbarang,
         lolos_qc_sfl = NEW.terima,
         dalam_proses = NEW.terima
ON DUPLICATE KEY
UPDATE lolos_qc_sfl = lolos_qc_sfl + NEW.terima,
       dalam_proses = dalam_proses - NEW.terima;


Syncing two (or more) PostgreSQL 9.1 clusters This question assumes the use of PostgreSQL 9.1.

Say I have two PostgreSQL database clusters. Each cluster contains three PostgreSQL servers. A master, a slave synced via synchronous replication and a standby synchronous replication server. One cluster is hosted in London and the other is hosted in Tokyo.

I have a web app that directs users to the nearest server to them. So I have two instances of the web app running. Each directly connected to one of the PostgreSQL clusters. What would be the best way to keep the two PostgreSQL clusters in sync with each other? I know I could do synchronous replication between the two masters in London and Tokyo but that would add a long time on top of any write queries because the PostgreSQL server would have to wait for a response from the remote system which would (at best) probably add another 200ms of latency to the whole operation.

Does anyone have any suggestions for solving this issue?

Thank you for any help :).

Edit: Also it would be good if the answer took into out account the fact that I may want to expand the system to include a third or a fourth PostgreSQL cluster. I'm lost on the best way to handle syncing between so many different clusters.

There isn't currently anything in the PostgreSQL core which would help with that, but there is work in progress to do what you want that is likely to make it into version 9.3, which will probably be released in the summer of 2013.  If you can't wait for that, there are a number of solutions available from third parties. From what you describe, Bucardo sounds like it might be the best fit.

A new option, which recently hit production release 1.0, is Postgres-XC.  It is exciting technology, but if you have a stable production environment you might want to just keep an eye on it for now; it's pretty "bleeding edge" at the moment, and they haven't yet figured out how to incorporate all of the features of normal PostgreSQL in that environment.


PostgreSQL will stop logging when logrotate rotate postgresql log file I have installed

PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit

And configured:

log_filename = 'postgresql.log'

But I would like to manage log rotations by logrotate instead of internal logrotation by PostgreSQL:

#vim /etc/logrotate.d/postgresql

/var/lib/pgsql/data/log/postgresql.log {
    daily
    rotate 7
    compress
    create 0664 postgres postgres
    missingok
    notifempty
    sharedscripts
}

Where logrotate works as expected but PostgreSQL will stop logging into the rotated /var/lib/pgsql/data/log/postgresql.log file. The only resolution is to systemctl restart postgresql which could be placed inside logrotate postrotate/endscript block but I can't do it this way if this is done on critical production system.

Do you have any advice how to properly logrotate PostgreSQL log file without need of restarting PostgreSQL?

Thanks!

UPDATE:
During reloading of PostgreSQL it looks for changes. I found a workaround how to achieve that PostgreSQL will start to log into rotated postgresql.log file - I need to modify config file, for example comment out/modify log_filename then reload PostgreSQL and revert changes back and reload PostgreSQL again. Because PostgreSQL looks for changes in configuration file. But I would be glad if there is another better solution than touching configuration file.

You can try using the copytruncate directive of logrotate if you can accept the small risk of losing log data.


Backup chain for DBs restored from a different server When a DB is restored from a different server (say, Test is refreshed with Prod), the info on the differential base is brought with the backup. So, the SQL instance will keep relying on the backup chain that exists in the original environment (Prod) until a new full backup is taken (in Test). Ola’s backup solution is not able to detect and resolve this condition, so it would keep taking diff backups (even with @ChangeBackupType = 'Y') until the next scheduled full backup. Is there any workaround on that?

Before running Ola's script, you can check the msdb.dbo.backupset.type = 'D' using top(1) .. ORDER BY backup_finish_date desc.

If it returns value then you have a full backup done and you can proceed with differential.
If it returns null or no value then you dont have a full backup and you can take a full backup and then proceed with your regular differential backups.

I would agree with JohnM that do you really need differential backups on a test server ? Its easy for you to get a full backup and refresh the environment.


SQL Job step history to display DML changes A vendor provided a script to run with many DML statements and i dump this in a stored procedure to run it from SQL job. When running the script via SSMS, it returns affected row counts after going through each DML statements such as "update". How can i quickly get the row counts to appear in SQL job step or dump to a text file via job step without setting using @@rowcounts after each of the DML statements?

Use SQLCMD through a cmdexec jobstep, instead of TSQL step.


What exactly is a database engine? I have gone through the definition on http://en.wikipedia.org/wiki/Database_engine several times:

  A database engine (or "storage engine") is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.

What I do not understand is what is left to do, isn't CRUD all that the databases do?

If the database engine performs these functions, what does the rest of the database do?

CRUD is meant to define the characteristics necessary for a database as it relates to persistent storage.  It is not meant to describe everything that could be done by a database engine.

To make a comparison, fundamentally a vehicle is a device used for transport.  While true, this definition certainly doesn't include all the detail entailed in a modern automobile.

A database engine might handle multiple users, transactions, MVCC (Multiversion Concurrency Control), buffers and caches, ACID (atomicity, consistency, isolation, durability), as well as different isolation levels.  A read may pull data from memory, remote databases, and multiple tables on disk processing it using SQL through multiple explicit and/or implicit code paths in order to present it to the requesting application.  A create may allocate storage, provision structures, assign values, and do it's own processing before storing data. Etc.


How to combine date and time with a string value in column created by Case in SQL Query I am in the process of creating a query for an SSRS report that will show a column that has DATETIME values.  The column can have NULL values.  In my query I would like to use a CASE expression.

I want to create this column to just display the date and time value if its not null but if its NULL I want it show something like 'No Data'.

The Expression I have is:

Case
  When (Column with Date/Time) is not null then (Column with Date/Time)
  When (Column With Date/Time) is NULL then 'No Data'
End AS Column A

However, this query returns an error:

  Conversion failed when converting date and/or time from character
  string.

I guess because it can't have a string value with a column formatted for Date and time?

Is there any way I can combine these two?  If not I know I can leave it as is and just show the NULL value, but I wanted it to look cleaner.

I guess because it cant have a string value with a column formatted
  for Date and time?

You are correct. CASE Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. Data type precedence comes into play here.

You'd have to convert your column to a varchar.

DB FIDDLE

case
   when DateTimeColumn  is null then 'No Data'
   else convert(varchar (20),DateTimeColumn,120)
end

You can reference the CAST and CONVERT documentation for other date/time formats.


AlwaysOn Replicas set to manual but failing automatically My two synchronous-commit SQL AO replicas are set for manual failover mode but are failing over automatically. When I look through the logs and extended events, it looks just like what would happen if it were set automatically. This was set before a time when I was out of this office and the servers have been rebooted multiple times for updates, so I know the service account isn't waiting for a restart. Manual failover works but unsure why automatic is happening. There were 4 nodes, but the 2 remote DR nodes are currently removed both from SQL AOAG and WSFC. Any advice on where to look to figure this out?

A reboot of a cluster node that has a resource group that's configured to automatically start forces the WSFC to start that resource on a surviving node, implementing HA feature of SQL Server AAG. This action also triggers SQL Server instance to promote an existing synchronous secondary replica to primary. I think you can avoid automatic failover by setting only one replica to be synchronous, however this will effectively make you lose data should you irreversibly lose the cluster node with the primary replica.


mongoimport error - Failed: error connecting to db server: no reachable servers I am currently trying to learn MongoDB and I am having trouble finding a solution for this problem. When I run a mongoimport command it I get the following error:

~ mongoimport --host localhost --port 27017 --db test --collection people --file ~/Downloads/mongodb-consultas.json --jsonArray
2015-09-27T20:46:03.228-0600    [........................] test.people  0.0 B/684.2 KB (0.0%)
2015-09-27T20:46:03.745-0600    Failed: error connecting to db server: no reachable servers
2015-09-27T20:46:03.745-0600    imported 0 documents

When I substituted localhost for 127.0.0.1 I get the following error:

~  mongoimport --host 127.0.0.1 --port 27017 --db test --collection people --file ~/Downloads/mongodb-consultas.json --jsonArray
2015-09-28T15:15:42.047-0600    connected to: 127.0.0.1:27017
2015-09-28T15:15:42.049-0600    Failed: error reading separator after document #1: bad JSON array format - found no opening bracket '[' in input source
2015-09-28T15:15:42.049-0600    imported 0 documents

The document that I am trying to import is not corrupted as I got it from a MongoDB tutorial, and it is working for other users.

My MongoDB shell version is 3.0.6.

I have a MongoDB server by running mongod in the command line. The command mongo runs fine as well.

My firewall allows incoming connections for MongoDB.

I have gone through the error and checked out, the problem is resist with mongoimport syntax. The mongoimport shell is unable to find the location of (.json) file.

And also there is mistake like --jsonArray.

As per MongoDB BOL --jsonArray Modifies the output of mongoexport to write the entire contents of the export as a single JSON array. By default mongoexport writes data using one JSON document for every MongoDB document.

  Note:- Accepts the import of data expressed with multiple MongoDB documents within a single JSON array. Limited to imports
  of 16 MB or smaller.

For Simple mongoimport syntax will be like that

mongoimport -d databasename -c collectionname (File Location of json file.json)

For Example

mongoimport -d test -c zips C:\MongoDBDBA\zips.json

2018-02-07T08:32:31.084+0300    connected to: localhost
2018-02-07T08:32:31.532+0300    imported 29353 documents

IN the above mongoimport syntax the database name is test and collection name is zips and C:\MongoDBDBA\zips.json is the (.json) file location.

For Example with localhost and port number 27017

If you want to use localhost and by default port number 27017 in mongoimport then simply the syntax will be like that

mongoimport --host localhost --port 27017 -d blog -c posts C:\MongoDBDBA\posts.json

2018-02-07T10:48:54.340+0300    connected to: localhost:27017
2018-02-07T10:48:56.333+0300    [########################] blog.posts   33.9MB/33.9MB (100.0%)
2018-02-07T10:48:56.345+0300    [########################] blog.posts   33.9MB/33.9MB (100.0%)
2018-02-07T10:48:56.346+0300    imported 1000 documents

Here in the above syntax blog is the database name and collection name is posts.

  Most Important Note:- Run mongoimport from the system command line, not the mongo shell.

For your further ref Here


Does the || operator in Postgres result in a blank result if one of the columns is blank? I am running a query in Postgres using the || operator to concatenate some columns and it seems where one of the columns is blank the whole result is blank. Is that normal || behaviour?

With "blank" I mean NULL.

Depends what "blank" means, NULL or the empty string.

If it means NULL, then yes, that's normal. SELECT 'ab' || NULL ; will return null.
If it means an empty string, then no. SELECT 'ab' || '' ; will return 'ab'.

To avoid the issue, you can convert the nulls to empty string with COALESCE():

SELECT
    COALESCE(str1, '') || COALESCE(str2, '') || ... || COALESCE(strN, '')

or use CONCAT() or CONCAT_WS() functions - which ignore nulls:

SELECT
    CONCAT(str1, str2, ..., strN)

SELECT
    CONCAT_WS('', str1, str2, ..., strN)


Query takes forever to run I have the following query that is used to define a view in MySQL.  But the query takes forever to complete even if I perform the query outside the view.  I have a feeling that the inner select is what's slowing things down, but i have no idea how to optimize it further.  HELP PLEASE!

SELECT DISTINCT
    `table1`.`date` AS `date`,
    `table1`.`col1` AS `col1`,
    (SELECT
            COUNT(`tbl1`.`col1`)
        FROM
            `tbl1`
        WHERE
            ((`tbl1`.`col1` = `table1`.`col1`)
                AND (`tbl1`.`date` = `table1`.`date`))) AS `usage_count`
FROM
    `table1` `tbl1`

Yes, the subselect is slowing you down.  This is because it has to execute that query for every single row of the outer query.  The bigger the outer table, the more times you have to run that same query again.

By moving it out to a join you will only have to execute it once.

SELECT DISTINCT
    `table1`.`date` AS `date`,
    `table1`.`col1` AS `col1`,
    `usage_count`
FROM `table1` LEFT JOIN
(
    SELECT COUNT(`col2`) AS `usage_count`, `col1`, `date`
    FROM `tbl1`
    GROUP BY `col1`, `date`
) AS tbl1 ON `tbl1`.`col1` = `table1`.`col1`
    AND `tbl1`.`date` = `table1`.`date`


Why is the ROW keyword needed when constructing a row with one element, but not two? In PostgreSQL 11, the following expression
UPDATE test_table SET ("column_a","column_b") = ('value-a','value-b') WHERE "column_a" = 'value-c'

is accepted and performs the update.
But for a single column / value, like
UPDATE test_table SET ("column_a") = ('value-a') WHERE "column_a" = 'value-c'

it gives an error. ROW must be supplied, like
UPDATE test_table SET ("column_a") = ROW ('value-a') WHERE "column_a" = 'value-c'

Is there an explanation why this behavior was chosen? Is there an ambiguity if ROW is missing?

As is often the case, the answer is to be found in documentation.
The UPDATE reference says, in particular:

According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. PostgreSQL only allows the source value to be a row constructor or a sub-SELECT.

whereas "row constructor" is defined like so:

A row constructor is an expression that builds a row value (also called a composite value) using values for its member fields. A row constructor consists of the key word ROW, a left parenthesis, zero or more expressions (separated by commas) for the row field values, and finally a right parenthesis. [...] The key word ROW is optional when there is more than one expression in the list.

The last sentence kind of makes sense, because a single expression in parentheses is ambiguous by itself and can be confused with a simple parenthesised expression.
This syntax was introduced in version 10 -- HT to ypercube™.


Security and Performance implications of "View Server State" This question points out that "View Server State" permission is required for various DMV's (dynamic management views), but I can't find anything about who you do and do not want to grant the permission to.

Now of course I understand "least permissions", and why you wouldn't want to just grant it to anybody, but I can't find any guideance on how to evaluate whether it SHOULD be granted or not.

So, my question: What are the security and performance implications of granting a user "View Server State" permission.  What can they do that they maybe shouldn't be allowed to do...

Update: one implication is that the user will be able to use DMV's to look at queries.  If the queries or query parameters can contain confidential information that the user wouldn't otherwise be able to see, allowing VIEW SERVER STATE would allow them to do so (i.e. dob = or ssn =).

There are no significant performance issues that I can think of from granting this permission. From a security perspective, you run the risk of letting a user see what you most details about your weak spots, so for example, a malicious user could view your most common wait stats are, which could help them target a DoS attack against your server.

Is this possible? Definitely.
Is this likely? I'm compelled to say No, but remember that it is estimated that 90 percent of attacks against companies are from internal attackers.


Products with different prices in an order I have a "classic" database schema for order and products:

Orders
------
id
etc.

Products
--------
id
name
etc.

Order Products  --> Pivot table
--------------
id --> some people don't like this, nevermind.
order_id (FK)
product_id (FK)
etc.

Now I have to deal with prices inside orders. Two problems:

There are two currencies -dollar and euros- and price are not calculated. Let's say, sometimes a product costs $79 vs 89€, some other times $89 vs 99€
Some products can be bought in a bundle and their prices can vary. Buying only product "A" costs $4, and buying product "A" with product "B" costs $3 + $3 (product "B" costs $3 in the "A"+"B" bundle)

Is there any simple, elegant and readable way to deal with such a problem? Maybe some part of the solution could be done outside of database, not sure.

Below is my take on it:

Have two separate price columns, once for $ and the other for €. If in some stage in the future you will start taking payments in more currencies, you will likely use one of the prices as the base price and do a currency conversion calculation.
Depending on the complexity of the rules you have two choices:

a. Add "bundles" and "bundled products" tables which will store information about discount prices and what product combinations are eligible.
Pros:
You can easily implement functionality to manage bundles in the front end.
Cons:
Complex ad-hoc rules cannot be implemented this way.
b. Implement order price calculation rules in code (either database side or front end side, which will calculate final order price based on the products added.
Pros:
This approach allows for any number of complex rules specified.
Cons:
Requires program changes to change the rules.
The rules are not transparent to administrators / customers.


Sqlyog: copy table from localhost to remote server without duplicate records Hi guys i want to copy record from table which is stored in localhost into remote server but when i tried to copy there is pop up like below:

If i checked Drop if exists in target it would delete all of data in table, but if unchecked it would duplicate several record,that's why i want to copy record from table in localhost into remote server without duplicate data or delete existing data, so just adding new data from localhost table and ignoring existing data. Is there any way to use such a feature like that in sqlyog ? if not how could i to do this task ?

You can copy your table first into a temporary table in your source database, copy that table to the target database, and merge the data there.

That is, in your source database:

CREATE TABLE emails_temp AS
SELECT
    *
FROM
    emails ;

... copy this table to the target database using the Copy Database functionality; and drop it from the original:

 DROP TABLE emails_temp ;

Once you have your temporal table at the destination, add the new data to the existing table, doing something like:

INSERT INTO emails
SELECT
    *
FROM
    emails_temp
WHERE
    primary_key NOT IN (SELECT primary_key FROM emails) ;

(Obviously, you have to substitute primary_key by your actual PRIMARY KEY).

Or you can use also INSERT IGNORE.

After you have transferred all the new rows, you can

DROP TABLE emails_temp ; -- At the destination database


Set MySQL option to restore MySQL server after deleting ibdata1 ON an Ubuntu Server 12.10 machine, I had attempted to delete the ibdata1 file before reading Rolando's explanation about how exactly to do so. Specifically, I had not set innodb_fast_shutdown = 0 before shutting MySQL down.

How might I start MySQL, now that ibdata1 has been deleted but innodb_fast_shutdown had not been set?

I have backups of all the databases, but I cannot reinstall MySQL since the Ubuntu version is no longer supported. I cannot update Ubuntu to a supported version due to an accessibility issue in the current versions, nor can I downgrade to 12.04 LTS (still supported) since Ubuntu was never designed to be downgradable and some applications that I need won't run well on that older version if I do a complete reinstall as they depend on a string of dependencies not in 12.04 but in 12.10.

If you got a mysqldump export (.sql) of your databases, you do not need to have stopped mysql in any special way.

Make sure mysql is down

pgrep mysqld
(no output)

Delete everything on your datadir (by default, /var/lib/mysql)

mkdir /tmp/mysql.bak && sudo mv /var/lib/mysql/* /tmp/mysql.bak

Run:

sudo mysql_install_db

Change the permissions od your datadir to the user mysql:

sudo chown -R mysql:mysql /var/lib/mysql

Start mysql:

sudo service mysql start

Import your databases back:

mysql -u root < your_dump.sql

You will have your databases back on a fresh installation of mysql, with a just created ibdata1.


Taking Back up on Non readable Secondary Replica Availability group Is there any way to back up a non readable secondary replica database to restore in a dev environment and then do dbcc checkdb? Will appreciate any help!

Backups are only allowed in readable secondaries, so not really.

Also you need to take in mind that running checkdb might be considered a production workload if you don't run it in production too.

You should be running checkdb on all your databases regardless primary or secondary and also your backups, that way you wouldn't have to license the instance where you restore your backups and run checkdb on them (you are validating backups, not production databases), otherwise MS can make you pay for that, but I'm not a licensing expert, so better ask to confirm this point.


Is there a shorthand way to 'Auto_Fix' all Orphaned Users in an SQL Server 2008 R2 database? It's fairly straight forward to fix up a single orphaned SQL user to a login using:

  EXEC sp_change_users_login 'Auto_Fix', 'user'

I could script this, but is there an existing stored procedure which automatically tries to fix up every orphaned user in a given database?

Ted Krueger (@onpnt on twitter) wrote a great script that does this. It adds logins for any user without a login and runs the auto_fix. He even wrote one that includes fixing Windows logins:

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users

Of course if you want to test it first (or merely perform an audit) you can comment out the action lines (EXEC) and just print out the results.


DISABLE / DROP INDEX causing deadlock Rather urgent one.

Have created an index on one of our tables and for some reason all data going into that table has stopped.

Can't see any blocking, but when I'm trying to drop or disable the index with either

ALTER INDEX [IX_DataCapture_Received] ON [queue].[queued_datacapture_data] DISABLE
DROP INDEX [IX_DataCapture_Received] ON [queue].[queued_datacapture_data] WITH ( ONLINE = OFF, MAXDOP = 1)

I get a report of deadlock preventing it, but nothing is being reported to sp_readerrorlog

Any clues as to what's going on?

Problem solved

Very hacky but got the system back

Set the deadlock priority to high to stop it being auto killed

there are a lot of external apps that access the DB which ended up blocking the drop index (information from the master..sysprocesses table) (but not reporting as blocking through all the checks we have), so I just killed until there was nothing more in its way, dropped straight away

cheers for the quick eyes over


Why innodb doesn't store geospatial data? I am very curious.

I have business tables. Now I think I will have to create a separate table, location table. That separate table should be myisam.

But why would I do so?

Why can't innodb store points?

Because R-Trees are not B-Trees:

  For MyISAM tables, SPATIAL INDEX creates an R-tree index. For storage
  engines that support nonspatial indexing of spatial columns, the
  engine creates a B-tree index. A B-tree index on spatial values will
  be useful for exact-value lookups, but not for range scans.

Adding a completely different storage structure for InnoDb is significant effort (much more than for MyISAM due to locking and recovery).


Postgresql: What the default_statistics_target value really means? Increasing the default_statistics_target value can make your database faster, specially after analyze....

Reading this article I see that https://discuss.pivotal.io/hc/en-us/articles/201581033-default-statistics-target-Explained

  (...)in short and in basic term, this
  parameter control the way the stats are collected , with value 1 being
  the least estimated/accurate statistics and the value 1000 being the
  most accurate statistics , obviously with the expense of time /
  resources ( CPU , memory etc ) / space . Normally the default value is
  sufficient to get a accurate plan , but if you have a complex data
  distribution / or a column is referenced in the query quite often ,
  then setting a higher value might help in getting a better statistics
  on the table and hence a better plan for the optimizer to execute.

It is a good explanation, but for example if I set default_statistics_target= 1000 what 1000 really means? It is 1000 kilobytes of statistics being generated? or maybe it is 1000 rows of the tables analyzed? Maybe it is 1000 columns? or perhaps 1000 seconds for each analyze...

So my question is how this number is really affecting the analyze or the query planner? Obvious I understand that default_statistics_target = 1000 will get more time than 100, for running analyze, and that 1000 will generate better statistics...

It will sample 300 * default_statistics_target rows from each table.  It will use that sample to determine upto default_statistics_target most common values to store in that array, and upto default_statistics_target histogram bounds to store in that array. Plus a few other scalar statistics, like the number of distinct values.

The multiplier 300 was chosen because some statistical theory says that is how many you need to sample per each histogram bound you wish to compute, in order for your sampled histogram bounds to have an acceptable level of uncertainty.

The most common value list is used to help the planner predict the selectivity of equality expressions, like where state='CA'.  The histogram bounds are used to help the planner predict the selectivity of inequality or range expressions, like where income between 55000 and 64000


SQL Server comparing dates algorithm How is the comparison between dates (with times) actually implemented?

Consider the following query for example:

SELECT *
FROM dbo.Table1
WHERE DateTimeValue >= '2016-01-01 00:00:000'
  AND DateTimeValue < '2016-01-03 00:00:000'

The above internal contains exact two days, no millisecond more or less.

So, how does SQL Server compare the values in the column with the values in the interval?

String comparison?

This seems not so bad, but if you see the execution plan, you'll notice that the SQL Server is using CONVERT_IMPLICIT to cast the string value to datetime.

Integer comparison?

This sounds ridiculous if you think how many milliseconds there are in one day.

Does anyone know how the comparison is implemented internally in SQL server ?

Assuming DateTimeValue is a DATETIME column or similar then both '2016-01-01 00:00:000' and '2016-01-03 00:00:000' are going to get cast to that type and the comparison is done using the natural date comparison routine. Of course if you have something odd like dates stored as strings then yes it may perform a string comparison.

Internally DATETIME is essentially a large integer type (it is actually implemented as two integers but for the purpose of this discussion that difference is purely academic) so the natural comparison is integer comparison.

The conversion of your input strings to DATETIME is not going to take significant resources: it will be done once for the whole query and not for every row that gets considered.

If you have a relevant index for DateTimeValue then a ranged query will be used on that index (a seek to 2016-01-01 or the first value above that then a scan down the index until the values seen stop being less than 2016-01-03. This should be fairly efficient though you will of course have heap/cluster lookups going on if that index isn't your clustered one or covers all the columns. If you look at the query plan for such a query in SSMS you should see that index seek and related row lookups clearly.

So it is an integer comparison, but the other way around to the one you call ridiculous (which you are right, would be silly). There will be a few integer comparisons as it works down the B-Tree of the index to find the first relevant row followed by one comparison per subsequent row as it asks "have we hit 2016-01-03 or higher yet?", not one comparison for every possible value in the range. It might help to think of how you would go about following the instruction "list all the words from your dictionary that are between index and iteration" - that is essentially what SQL Server is doing with the datetime range search.


SQL Server 2016: Table can't grow while 313 MB is still available in the database Yesterday, the below error was reported:
Could not allocated space for object 'dbo.X'.'Y' in database 'Z' because the 'PRIMARY' filegroup is full
After deletion of some records from the table by an engineer, the error was cleared. I couldn't check the details yesterday as I actually didn't have admin access to it. My access was sorted later on. As I checked the DB size today, I observed the below:

There is 312.63 MB available free space, which means that there is 312.63 MB of space allocated to the database but not yet allocated to any page or objects (Please correct me if I'm wrong). I don't expect the delete operation of yesterday to have released any page/space. So why wasn't the database able to use this space which was readily available and allocated to the database?
I leave out the possibility that the file has grown any further since yesterday because there was ample disk space already available when the the incident occured. This is a SQL Server 2016 SP1 Express Edition which has the Autogrowth setting having been enabled with file growth size of 64 MB and the maximum size being set to Unlimited. Considering:
  10184 MB + 64 MB = 10248 MB > 10240 MB (= 10 GB = maximum allowed DB size in Express Edition)

It's obvious the file couldn't (and can't) grow any further.
While the database couldn't resize the file, it still could have used whatever space that was available to it. So why it didn't happen?
Could it be that, some objects have been dropped after delete was performed yesterday?

You mentioned you don't expect the delete operation to have released space but that is not the case. Pages that are emptied as a result of the delete become unused space (which may be reused for the same object) and emptied extents become unallocated space (which may be used for any object).
Consequently, the DELETE looks to have freed enough space within the data file to avoid the space problem. Consider reorganizing the indexes to further reclaim space from partially filled pages since your database is still over 95% full.


Row count from a certain table in multiple databases I need to know the row count of a certain table that exists (or don't) in multiple databases.

Here's what I came with:

DECLARE @NomeBase as char(60)
declare @base as char(100)
declare @select as char(1000)

DECLARE CBases CURSOR FOR  select databasename from sig_orgao_web
 --gets all my databases
OPEN CBases
FETCH NEXT FROM CBases INTO  @NomeBase

WHILE (@@FETCH_STATUS = 0)
  BEGIN
    set @base =rtrim(@NomeBase) + '.dbo.sia_pais' -- database.dbo.table format
    select @base
    set @select = 'SELECT cast(  count(*) as char(70)) from ' + @base
    exec (@select)

    FETCH NEXT FROM CBases INTO  @NomeBase
  END
CLOSE CBases
DEALLOCATE CBases

I know that tablenames have to be static, so I'm creating the select statements dynamically. The results are like this:

SIIG_DAT_DESENV.dbo.sia_pais
13
SIIG_DAT_HOMOLOGACAO.dbo.sia_pais
13

And it goes on.

The thing is, if the table doesn't exist in my database or if the user which is running this script on management studio doesn't have access to a certain database, an error will be thrown.

Is there a better way to do this?

I would use the system metadata to make all of this an easier experience. The query from this article will show you how to use the metadata to determine the rowcount for all your tables in a database.

It'd look like

SELECT
    s.[Name] as [Schema]
,   t.[name] as [Table]
,   SUM(p.rows) as [RowCount]
,   db_name() AS dbname
FROM
    sys.schemas s
    LEFT OUTER JOIN
        sys.tables t
        ON s.schema_id = t.schema_id
    LEFT OUTER JOIN
        sys.partitions p
        ON t.object_id = p.object_id
    LEFT OUTER JOIN
        sys.allocation_units a
        ON p.partition_id = a.container_id
WHERE
    p.index_id  in(0,1) -- 0 heap table , 1 table with clustered index
    AND p.rows is not null
    AND a.type = 1  -- row-data only , not LOB
    and t.[name] = 'sia_pais'
GROUP BY
    s.[Name]
,   t.[name]
ORDER BY
    1
,   2;

Great, now you know the row count for a single table. You can then patch that into your existing cursor. The other nice thing about this route is it doesn't matter whether the table exists or not. It will simply not return a row if the table is gone.

And for the sake of providing a mostly full solution

DECLARE
    @NomeBase as char(60)
,   @base as nvarchar(4000)
,   @select as nvarchar(4000);

-- Create a template query to capture table counts
-- Since it's lightning fast, I delay filtering until the end
SELECT
    @base = N'USE <dbname/>;
    INSERT INTO
        ##rowCounts
    SELECT
        s.[Name] as [Schema]
    ,   t.[name] as [Table]
    ,   SUM(p.rows) as [RowCount]
    ,   db_name() AS dbname
    FROM
        sys.schemas s
        LEFT OUTER JOIN
            sys.tables t
            ON s.schema_id = t.schema_id
        LEFT OUTER JOIN
            sys.partitions p
            ON t.object_id = p.object_id
        LEFT OUTER JOIN
            sys.allocation_units a
            ON p.partition_id = a.container_id
    WHERE
        p.index_id  in(0,1)
        AND p.rows is not null
        AND a.type = 1
    GROUP BY
        s.[Name]
    ,   t.[name]
    ';

--
if object_id('tempdb..##rowCounts') IS NOT NULL
BEGIN
    DROP TABLE ##rowCounts;
END;

-- Create a global temporary table to keep track of table counts
CREATE TABLE
    ##RowCounts
(
    [SchemaName] sysname
,   [TableName] sysname
,   [RowCounts] bigint
,   [DatabaseName] sysname
);

-- Enumerate through all of your databases
DECLARE CBases CURSOR FOR
-- select name from sys.databases
select databasename from sig_orgao_web
 --gets all my databases
OPEN CBases
FETCH NEXT FROM CBases INTO  @NomeBase

WHILE (@@FETCH_STATUS = 0)
  BEGIN
    -- Replace our token with the actual database name
    SET @select = replace(@base, '<dbname/>', @NomeBase);

    EXEC(@select);
    FETCH NEXT FROM CBases INTO  @NomeBase;
  END;
CLOSE CBases;
DEALLOCATE CBases;

-- Retrieve actual counts
SELECT
*
FROM
    ##RowCounts RC
WHERE
    RC.TableName = 'sia_pais'
    AND RC.SchemaName = 'dbo';

What remains? Your account might not have access to a given database. The template could be further armored by wrapping a try/catch around the whole thing. But for this case, I assume you have access and if you don't, it comes roaring back at you instead of silently swallowing errors.


Can I perform a MAX over a pair of columns? I would like to execute the following:

SELECT person, MAX( (date, priority) ) FROM table GROUP BY person;

It would return a person, date and priority row for each distinct value in the person column.  The date and priority are chosen such that the date value is maximised, and the maximum priority that occurs on that date is chosen.

As an example, running the query on this table

person |     date     | priority
---------------------------------
   1   | '2014-01-01' |    10
   1   | '2014-01-02' |     2
   1   | '2014-01-02' |     3

should result in

person |     date     | priority
---------------------------------
   1   | '2014-01-02' |     3

Postgres complains about this particular attempt:

ERROR:  function max(record) does not exist
HINT:  No function matches the given name and argument types.
You might need to add explicit type casts.

Is there a way that I can achieve this?  Anything that works with Postgres will be acceptable but if a solution is more standard then so much the better.

Does this fits your needs?

SELECT DISTINCT ON (person)
       person, date, priority
  FROM table
 ORDER BY person, date DESC, priority DESC;


Unable to access SSRS web URL outside Azure VM I've configured SSRS in SQL Server 2016 on Azure VM. However I am unable to access the web URL outside of the VM. Since it is my test box I've tried disabling the firewall as well. Still didn't help.

Any suggestions where to look? I've attached screenshot of my browser in my laptop.

The URL is accessible inside the VM where Report Server and Database Engine is Installed.

Your URL is wrong and also you can configure the firewall. Try below URL.  Configure port as describe below if you have not already.

http://jarvissqlvm.cloudapp.net/ReportServer/PageTo Connect to Remote Report Manager

Here are more detail steps.

Reference:

https://web.archive.org/web/20140523110804/http://msdn.microsoft.com/en-us/library/azure/jj992719.aspx

  If you want to connect to Report Manager on the virtual machine from a
  remote computer, create a new virtual machine TCP Endpoint. By
  default, the report server listens for HTTP requests on port 80. If
  you configure the report server URLs to use a different port, you must
  specify that port number in the following instructions. Create an
  Endpoint for the Virtual Machine of TCP Port 80. For more information
  see, the Virtual Machine Endpoints and Firewall Ports section in this
  document.

  Open port 80 in the virtual machine’s firewall.

  Browse to report manager using Azure Virtual Machine DNS Name as the
  server name in the URL. For example:

  Report manager:http://uebi.cloudapp.net/reportserver

  Report server:http://uebi.cloudapp.net/reports

  Configure a Firewall for Report Server Access
  (http://technet.microsoft.com/en-us/library/bb934283.aspx).


Drop 15 days records in monthly partition I have created a monthly partition on my table like below:

PARTITION BY RANGE (PARTITION_KEY)
     INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
   (
     PARTITION MayPartition VALUES LESS THAN (TO_DATE('01/06/2016', 'DD/MM/YYYY'))
   );

Since my table has data in TB, dropping this monthly partition is taking more than 10 hours for 1 month.

Is it possible to drop partition for only 15 days? Or is there a way to reduce the time taken to drop partition?

I made sure there was no other load on database while dropping the partition.

I am using this command to drop the partition:

alter table table_name drop partition partition_name update indexes

I have global indexes on the table. I don't have foreign keys referencing the PK of that table.

If it was me, I would partition by hour, then you can just drop 24 partitions every day. That is only 744 partitions per month. Then if you query within a relatively short window, you will look at much less data. You may also want to look at your index strategy. My guess is that the cost of maintaining the indexes is too high. IMHO, smaller partitions should help speed up the queries and make it easier to maintain the partitions.


Choosing the Most Recently Entered Non-Blank Value When Many Values Exist I have run into a problem where some IDs have multiple names associated with them.  That is, if I have a location ID from one table and need to pull the location's name from another table based on the location ID, the query is returning multiple names.  I want to choose the newest name based on a timestamp, if that one is not blank (that is, it's not null, but many of the entries are just a blank space).

TABLE LOCATIONS:

LOCATION_ID   LOCATION_NAME    LOCATION_TS
  12345         'BEST AUTO'        4
  12345         'BESTAUTO'         3
  12345         ' '                5
  12347         'FETTS'            3
  12347         'FESTS'            2

TABLE LOCATION_DETAIL:

LOCATION_ID    LOCATION_TYPE
  12345         'AA'
  12347         'ZQ'

I am trying to get a result that looks like this:

LOCATION_ID   LOCATION_NAME    LOCATION_TYPE
  12345         'BEST AUTO'        'AA'
  12347         'FETTS'            'ZQ'

Here is an SQLfiddle.  I am using Oracle 11g.  I have been spinning my wheels on this for a while and I feel like I'm missing something obvious.  I should mention that the way my permissions are set up, pretty much the only thing I can do is query the database, not create views or tables, update data, etc.

What you're looking for is generally known as a "greatest of group" solution.  There are several patterns for this.  A search of dba.SE or SO will show you some.

The posted question and the SQL Fiddle have the table names swapped.  This code is from the Fiddle:

with NEWEST as
(
    select
        LOCATION_ID,
        MAX(LOCATION_TS) as MAX_TS
    from Location_detail
    where Location_name is not null
    and LOCATION_NAME <> ' '         -- or whatever your exclusion conditions are
    group by LOCATION_ID
)
select
    l.LOCATION_ID,
    ld.LOCATION_NAME,
    l.LOCATION_TYPE
from LOCATIONS l
LEFT OUTER JOIN NEWEST n
ON l.LOCATION_ID = n.LOCATION_ID
LEFT OUTER JOIN LOCATION_DETAIL ld
on ld.LOCATION_ID = n.LOCATION_ID
and ld.LOCATION_TS = n.MAX_TS;


MySQL asynchronous multi master replication conflict resolution I have setup Multi Master replication, following this blog thread:

http://0wned.it/geek-bits/guides/mysql-multi-master-replication/

And now it synchronizes perfectly, up until the moment the connection between the servers is lost. When I have no connection both servers still update fields. This results in the following problem:

connection is lost.
server1 updates a field to the value "bike".
server2 updates the same field a few seconds later to the value "car"
connection restored
server1 gets the value "car" from server2 and updates the field
server2 gets the value "bike" from server 1 and updates the field.

Is there a way to check if the value has already has been changed and neglect the update based on time of update? In the field on both servers the value "car" should be the current value.

Conflict-free replication isn't supported in multi-source replication...

You should look at cluster multi-master setup, but, I don't know a multi-master cluster with lazy updates (async-replication).


Mysql DB server hits 400% CPU I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top.

 - load average 40 to 50
 - CPU % - 400%
 - idle % - 45%
 - wait % - 11%
 - vmstat procs r-> 14 and b-> 5

And then drains down within 5 minutes. And when I check the show processlist I see queries for DML and SQL are halted for some minutes. And it processes very slowly. Whereas each query are indexed appropriately and there will be no delay most of the time it returns less than 1 second for any query that are being executed to server the application.

Mysql Version : 5.0.77
OS : CentOS 5.4
Mem: 16GB RAM (80% allocated to INNODB_BUFFER_POOL_SIZE)
Database Size: 450 GB
16 Processor & 4 cores
Not in per-table model.
TPS ranges 50 to 200.
Master to a Slave of the same configuration and seconds behind is 0.

Below url shows show innodb status \G and show open tables; at the time spike. And this reduced within 5 minutes. Sometimes rare scenarios like once in two months I see the processes takes more than 5 to 8 hours to drain normal. All time I notice the load processor utilization and how it gradually splits its task and keep monitoring the process and innodb status and IO status. I need not do anything to bring it down. It servers the applications promptly and after some time it drains down to normal. Can you find anything suspicious in the url if any locks or OS waits any suggestion to initially triage with or what could have caused such spikes ?

http://tinyurl.com/bm5v4pl  -> "show innodb status \G and show open tables at DB spikes."

Also there are some concerns that I would like to share with you.

Recently I have seen a table that gets inserts only about 60 per second. It predominantly locks for a while waiting for auto-inc to get released. And thus subsequent inserts stays in the processlist tray. After a while the table gets IN_USE of about 30 threads and later I don't know what it makes to free them and clears the tray. (At this duration the load goes more than 15 for 5 minutes)
Suppose if you say application functionality should be shapped to best suite the DB server to react. There are 3 to 5 functionalities each are independent entities in schema wise. Whenever I see the locks it gets affected to all other schemas too.
Now what makes best fuzzy is the last one. I see slave keeps in synch with master with a delay of 0 second all time whereas slave has a single thread SQL operation that is passed from relay IO that which acts in FIFO model from the binary logs where Master had generated. When this single headed slave can keep the load less and have the operations upto-date, should the concurrent hits are really made to be concurrent for the functionalities which I assume making the possible IO locks in OS level. Can this be organized in application itself and keep the concurrent tenure density thinner?

InnoDB for MySQL 5.1 exhibits single-threaded behavior because the number of read and write I/O threads are fixed at 4 each. I was just reminded that versions of MySQL before 5.1.38 has only one thread for read and one for write. Therefore, scaling up hardware will not improve performance at all.

You need to upgrade to MySQL 5.5/5.6 because there are settings that will enable InnoDB to engage multiple CPUs and multiple cores (such as innodb_read_io_threads and innodb_write_io_threads).

Sep 20, 2012 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
May 26, 2011 : About single threaded versus multithreaded databases performance

CAVEAT : You must tune MySQL 5.5/5.6 properly because left unconfigured MySQL 5.1 is faster than 5.5/5.6 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

Give it a Try !!!


Constraint column value based on another column? SQL Server i am trying to set my defaults column Y or N based on membershipType column.
There can be only one Y in defaults column based on the 3 different value in membershipType column. Other than that, others will be N. How do i do it?

ID  titlePromo   membershipType         defaults
--  ----------   --------------         ------
1    Promo 1     Membership Promotion      Y
2    Promo 2     Membership Renewal        Y
3    Promo 3     Membership Grad           Y
4    Promo 4     Membership Promotion      N
5    Promo 5     Membership Promotion      N
6    Promo 6     Membership Grad           N

Use a unique filtered index.

CREATE UNIQUE INDEX uixDefault
ON dbo.Promotions(membershipType)
WHERE defaults = 'Y';

This stops there being a second row with defaults='Y' of the same membershipType.


can rman backup a subset of a table I have been asked to make incremental backups of a table based on the value of one of the fields in the table (called groupid). They want a separate incremental backup for groupid=1, groupid=2, groupid=3, etc... I use rman for entire databases or at least whole tables. Can rman backup a subset of a table?

RMAN only works at the block level & has no idea about the contents of a given block, and therefore cannot do this.

You need to use expdp with the query parameter:

expdp phil/phil directory=myexportdir dumpfile=yourtable.dmp query=yourtable:\"where groupid in (1,2,3)\" tables=yourtable

Obviously, this isn't incremental. There's no really easy way of doing it incrementally based on a query/the data. You'd have to roll your own tool to do it - flashback query could help.

What data volume are you dealing with? If < a few million rows, just export the full data each time.


How to generate TPC-DS query for sql-server from templates I know that dsqgen is used to transform the query templates into executable SQL. But when I type in the command line: "dsqgen /directory query_templates /template query1.tpl /dialect sqlserver /scale 5", there's an error that "Substitution 'STATE' is used before being initialized". I also copied the ansi.tpl and tpcds.idx files in the same directory as dsqgen.exe because dsqgen always read these two files. Anyone has any idea how can I solve this? Thanks in advance.

You can download fix error "STATE" and endcoding dump data to database at https://github.com/grahn/tpcds-kit


"ERROR: there is no parameter $1" in "EXECUTE .. USING ..;" statement in plpgsql I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this:

CREATE TABLE parent_table (
  value integer,
  end_time timestamp without time zone
);

CREATE OR REPLACE FUNCTION mk_child(_year INTEGER, _month INTEGER)
RETURNS text AS $$
DECLARE
  tname varchar;
  start_date date;
  end_date date;
  next_month varchar := (_month + 1)::text;
  next_year varchar := (_year + 1)::text;
BEGIN
  tname := 'child_y' || substring(_year::text from 3 for 2)
           || 'm' || lpad(_month::text, 2, '0');
  start_date := DATE (_year::text || '-' || _month::text || '-01');
  IF ( _month = 12 ) THEN
       end_date := DATE (next_year || '-01-01');
  ELSE
       end_date := DATE (_year::text || '-' || next_month || '-01');
  END IF;

  RAISE NOTICE 'Creating child table %', tname;
  EXECUTE format('CREATE TABLE %I ( CHECK ( end_time >= %L AND end_time < %L ) )
                  INHERITS (parent_table)',
                 tname, start_date, end_date);
  -- EXECUTE format('CREATE TABLE %I ( CHECK ( end_time >= $1 AND end_time < $2 ) )
  --                 INHERITS (parent_table)',
  --                tname)
  --     USING start_date, end_date;
  RETURN tname;
END
$$ LANGUAGE plpgsql;

When I call it, it is successful:

# select mk_child(2015,1);
NOTICE:  Creating child table child_y15m01
   mk_child
--------------
 child_y15m01
(1 row)

However, if I use the EXECUTE ... USING ...; form (commented out in the snippet above), I get an error:

# select mk_child(2015,2);
NOTICE:  Creating child table child_y15m02
ERROR:  there is no parameter $1
CONTEXT:  SQL statement "CREATE TABLE child_y15m02 ( CHECK ( end_time >= $1 AND end_time < $2 ) ) INHERITS (parent_table)"
PL/pgSQL function mk_child(integer,integer) line 21 at EXECUTE statement

The PostgreSQL docs say explicitly that this format works and is more efficient.

So why does it not work as expected?

You can only pass values to DML statements. The manual:

Another restriction on parameter symbols is that they only work in
SELECT, INSERT, UPDATE, and DELETE commands. In other statement types
(generically called utility statements), you must insert values
textually even if they are just data values.

So CREATE TABLE statement does not accept parameters via USING clause, even if expressions in a CHECK constraint look like values that might be parameterized.
That aside, you can largely simplify your function:
CREATE OR REPLACE FUNCTION mk_child(_year int, _month int)
  RETURNS text AS
$func$
DECLARE
   start_date date := to_date(_year::text || _month::text, 'YYYYMM');
   tname text := to_char(start_date , '"child_y"YY"m"MM');
BEGIN
   RAISE NOTICE 'Creating child table %', tname;
   EXECUTE format('
      CREATE TABLE %I (CHECK (end_time >= %L AND end_time < %L))
      INHERITS (parent_table)'
    , tname, start_date, (start_date + interval '1 month')::date);
   RETURN tname;
END
$func$ LANGUAGE plpgsql;

Related:

Self-managing PostgreSQL partition tables

If this is about table partitioning, look to the new declarative table partitioning in Postgres 10 or later. Related answer with sample code and links:

Better database for "keep always the 5 latest entries per ID and delete older"?


MariaDB - how to drop partition on master but not on slave I've read about filtering replication and I only run into information I know already, like you can select only a database or a table to do replication on.

My use case is that I want to have a slim and fast master with main data organized into partitions - one partition per day (each day a new partition is created). The master only keeps most recent 10 days of data and I need the slaves to keep all.

How do I ALTER TABLE ... DROP PARTITION on master without being replicated on slaves?

I seems to me that somehow I have to instruct the master not to write this operation into bin-log, but how do I do it?

You could run SET SESSION sql_log_bin=0 in the session before running the ALTER TABLE...


saving all action in database I have oracle database 11gR2 .
I want to save all changes of all tables in database such as insert,update and delete values . I want to save them in a table .
When i search for its solution ,find out that ORACLE AUDIT can not save data of changes .
Does ORACLE have any solution for this problem ?
Thanks

Flashback Data Archive (formerly known as Oracle Total Recall) let you store history for all the changes to one or more tables automatically.  If you have the Advanced Compression option, Oracle will apply a number of optimizations to the history table that will significantly decrease the amount of space that is required and that will generally make queries against history more efficient.


Logshipping alert emails are being sent out while there is no error I have logshipping setup with a Primary and a Secondary, and this has been working fine for a long time. Yesterday I started reveiving these emails:

  The log shipping primary database SERVER.DATABSE has backup threshold
  of 300 minutes and has not performed a backup log operation for 2670
  minutes. Check agent log and logshipping monitor information.

Checking the server I see backups being made, copied and restored. All in good order.

Executing execute sp_help_log_shipping_primary_database @database = 'DATABASE'
shows me the last backup date is '2012-10-16 07:00:00.673' as expected.

Things I have tried:

execute sp_refresh_log_shipping_monitor
    @agent_id = '43C13441-6892-4C1E-8435-60F4900E5994',
        @agent_type = 0,
    @database = 'DATABASE',
    @mode = 1

EXEC msdb.dbo.sp_processlogshippingmonitorprimary @mode = 1
,@primary_id = '43C13441-6892-4C1E-8435-60F4900E5994'
, @primary_server = 'SERVER'
, @monitor_server = NULL
, @monitor_server_security_mode = 1
, @primary_database = 'DATABASE'
, @backup_threshold = 30000
, @threshold_alert = 14420
, @threshold_alert_enabled = 0
, @history_retention_period = 5760

I have tried to disable the alert:

exec sp_change_log_shipping_primary_database
    @database = 'DATABASE'
    , @threshold_alert_enabled = '0'
    ,@backup_threshold = 30000

And I have disabled the SQL server agent job 'LSAlert_SERVER' which sends out these emails. Even so, emails get sent put every 10 minutes.

Is there an option I missed?

I have had similar issues to this and every time it was caused by a cloned machine where the SQL components had not been renamed (@@SERVERNAME, Database Mail SMTP, and so on).  I would check that possibility first.  If the LSAlert job is stopped you should not be receiving mails any more.


How to resolve "Error log file size is increasing in mysql 5.5" issue w/o restarting mysql I have an Ubuntu server with MySQL 5.5 installed. In /var/lib/mysql a .err file is kept on updating with these warnings only:

[Warning] IP address '234.22.11.221' could not be resolved: Temporary failure in name resolution
[Warning] IP address '234.22.11.221' could not be resolved: Temporary failure in name resolution

Its size is currently around 2 GB.

I googled this error and in most of the places it has been written that I need to write skip-host-cache in my.cnf file and then need to give a restart mysql.

Currently this server is carrying lot of running web services, applications and giving a restart may effect all these applications.

As far as I understand, these warnings are not native mysql errors.

Also, can someone tell me whether these warnings effect my database in future if I will not take any action and is it possible that due to other web applications, these errors are generating? What is the other way around for this without restarting mysql?

IS there any probability , that mysql will not come up after restart ???

Put the following parameters in your my.cnf and then restart the service:-

skip-name-resolve
log-warnings=1

The above activity will solve your  issue.

Try to check this link also:-

https://serverfault.com/questions/341290/mysql-warning-ip-address-could-not-be-resolved


How to launch SQL Server Agent job on completion of another one? An SQL Server Agent job (SSA) have to be executed after a few other SSA jobs.

Is there any way to start SSA jobs on completion (or success, or failure) of another one?

Currently, I do this on schedule adding the delay for the duration of previous jobs. This is uncomfortable for many reasons:

the real execution time of preceding job can exceed the usual time and and the follow-up job will start without required completion;
if to include the follow-up processing as job steps then I need to duplicate them over a few jobs

SQL Server 2012 R2 on Windows Server 2008 R2

Use sp_start_job.

Put that command into a job step as needed. No need to worry about the duration of previous jobs.


bug in database_scoped_configurations I'm trying to insert the result set from:

SELECT * FROM sys.database_scoped_configurations

into a temp table, because I want to check the settings for all databases on my server.
So I wrote this code:

DROP TABLE IF EXISTS #h
CREATE TABLE #h(dbname sysname, configuration_id INT, name sysname,     value SQL_VARIANT,  value_for_secondary SQL_VARIANT)
EXEC sys.sp_MSforeachdb 'USE ?; insert into #h(dbname, configuration_id, name, value,value_for_secondary)  SELECT ''?'' as dbname, * FROM sys.database_scoped_configurations  D'
SELECT * FROM #h H

But then there will only be one row per database, not the four rows that I expect from running a plain select in each database.

I know there are better ways to code this than using sp_MSForEachDB, and I tried several. But I still only get one row per database.
I've tried this on both SQL Server 2016 RTM and SP1

Is this a bug with SQL Server 2016, or am I doing something wrong?

Is this a bug with SQL Server 2016?

Yes. Definitely this is not correct behaviour. I have reported it here and is fixed in SQL Server 2016 SP2 CU9.

As Mikael Eriksson says in the comments sys.database_scoped_configurations and sys.dm_exec_sessions are implemented as views in the format

SELECT ...
FROM OpenRowset(TABLE xxxx)

However comparing the two plans below there is an obvious difference.

DBCC TRACEON(3604);

DECLARE @database_scoped_configurations TABLE(x INT);

INSERT INTO @database_scoped_configurations
SELECT configuration_id
FROM   sys.database_scoped_configurations
OPTION (QUERYTRACEON 8608, QUERYTRACEON 8615, QUERYTRACEON 8619, QUERYTRACEON 8620 );

DECLARE @dm_exec_sessions TABLE(x INT);

INSERT INTO @dm_exec_sessions
SELECT session_id
FROM   sys.dm_exec_sessions
OPTION (QUERYTRACEON 8608, QUERYTRACEON 8615, QUERYTRACEON 8619, QUERYTRACEON 8620 );

Trace flag 8619 output for both of these queries shows

  Apply Rule: EnforceHPandAccCard - x0-> Spool or Top (x0)

SQL Server is apparently not able to ascertain that the source for the TVF isn't also the insert target so it requires Halloween protection.

In the sessions case this was implemented as a spool that captures all rows first. In the database_scoped_configurations by adding a TOP 1 to the plan.
The use of TOP for Halloween protection is discussed in this article. The article also mentions an undocumented trace flag to force a spool rather than TOP that works as expected.

DECLARE @database_scoped_configurations TABLE(x INT);

INSERT INTO @database_scoped_configurations
SELECT configuration_id
FROM   sys.database_scoped_configurations
OPTION (QUERYTRACEON 8692)

An obvious problem with using TOP 1 rather than a spool is that it will arbitrarily limit the number of rows inserted. So this would only be valid if the number of rows returned by the function was <=1.

The initial memo looks like this

Compare this with the initial memo for query 2

If I understand the above correctly it thinks that the first TVF can return a maximum of one row and so applies an incorrect optimisation. The Max for the second query is set to 1.34078E+154 (2^512).

I've no idea where this maximum rowcount is derived from. Perhaps metadata supplied by the author of the DMV? It's also odd that the TOP(50) workaround doesn't get rewritten to TOP(1) because TOP(50) wouldn't prevent the Halloween issue from occurring (though would stop it continuing indefinitely)


Weekly full backups and daily differential backups, saving the full backups from each week? I am wanting to create a maintenance plan that performs a full backup that is done every Sunday morning. After work hours on Monday through Friday, I want to perform a differential backup based on this full backup. However, instead of overwriting the previous week's full backup, I want to retain that full backup for historic records/off-siting while getting rid of the differentials associated with that full backup. The type of plan I am planning on creating is as such:

If needed, a rebuild of the database indexes is done beginning early AM Sunday morning.

A further caveat to this would be to reorganize + update statistics instead based on the amount of fragmentation present.

Following the index maintenance, a full backup is created that same morning.
Monday through Friday at night, a differential backup is taken based on the full backup.
Following the differential, a DB integrity check is ran.
The next Sunday arrives, a full backup is established, the previous week’s differentials removed, and the previous full backup is brought here locally to a Drobo after-hours.

The reasoning for this type of plan is due to HDD size restrictions on the Azure environment I am working with. Given that, is this a sound strategy, or where should I seek improvement? Second, how do I go about retaining the previous week's full database backups but NOT the differentials? The articles I've read thus far seem to indicate that a revolving backup of this nature will have both the full backups and differentials removed.

This is a SQL Server 2012 instance on a Windows Server 2012 VM on the Azure environment. There are 5 databases, ranging from 6GB to 10GB each, that will be affected by this type of plan.

Thanks for your assistance.

As you have stated, your backup strategy can be viewed as below :

  The reasoning for this type of plan is due to HDD size restrictions on the Azure environment I am working with.

Two imporatant things that you can do

Enable Instant file initialization (do follow other best practices when runing SQL Server on Azure VM)
Enable backup compression and take backups with WITH COMPRESSION

  Given that, is this a sound strategy, or where should I seek improvement?

From the above figure, there is a clear room for improvement. You should take a differential backup on Sat (just incase if a disaster happens). Also, its important to read Backups Gone Bad: The Danger of Differentials. Make sure if you have to take a full backup in the middle of your backup strategy, you use COPY_ONLY backup.

  Second, how do I go about retaining the previous week's full database backups but NOT the differentials?

Dont use maintenance plans as they have noticeable drawbacks. I highly recommend to use Ola's backup solution (and Index maintenance  solution as well).

Edit : As per your comment:

If a full backup occurs in the middle, your differentials would be useless, since the base has changed and you dont know who took the last full backup. The differential relies on the last full backup. If you know people will take adhoc full backups that will mess up your differentials then deny them backup rights and create a stored procedure that will take full backup with COPY_ONLY option and grant those people execute rights to that SP. Additionally, do concentrate on recovery model of the databases - Full recovery model has to have a log backup.

You could even query msdb.dbo.backupset where is_copy_only = 0 to find out user_name of users who are taking COPY_ONLY backups

Just adding this for completeness - Restricting users to COPY ONLY LOG backups


How can I restore a master database from its bak file? During a penetration test I found multiple .bak-files for an MSSQL database. I want to restore them all. In a Windows VM I installed SQL Server 2017. For restoring the master database, I put the server in single-user mode and tried restoring the database as follows (also tried the same via SSMS):

RESTORE DATABASE master
FROM DISK = 'C:\Foo\<path>\master.bak'
WITH MOVE 'master' TO 'C:\Foo\master.mdf',
MOVE 'master_log' TO 'C:\Foo\master_log.ldf',
REPLACE
GO

This gave me an error like:

  .. can't be restored because it was created by a different version of the server (13.00.4435) than this server (14...).

I then downloaded MSSQL 2016 with service pack 1 by subscribing to "Visual Studio Dev Essentials" on my.visualstudio.com. Now I got the error:

  .. can't be restored because it was created by a different version of
  the server (13.00.4435) than this server (13.00.4001).

So now I needed to update. I downloaded MSSQL 2016 service pack 2, which gave me the error:

  .. can't be restored because it was created by a different version of
  the server (13.00.4435) than this server (13.00.5026).

So I really seem to require the exact patch. However, I can't find a list of patches with corresponding server versions. Does anyone have an idea on how to find the right patch?

If I could just extract the .bak file to its .mdf- and .ldf files that would be enough. I found a bunch of tools for restoring MSSQL .bak files, but want they all want to connect to a database (to do a simple restore). I'd be very happy if I could either get the right SQL Server version or extract the .bak file.

The master database is special, different than other databases. It's a system database where SQL Server stores internal objects. The only time you'd normally restore it is if you're bringing back a server from the dead - you wouldn't usually want to restore master from one database to another.

In your scenario, when you're just curious about the contents of the database, restore master with a different database name, like this:

RESTORE DATABASE master_copy
FROM DISK = 'C:\Foo\<path>\master.bak'
WITH MOVE 'master' TO 'C:\Foo\master_copy.mdf',
MOVE 'master_log' TO 'C:\Foo\master_copy_log.ldf',
GO

Then, after the restore finishes, you can query objects in the master_copy database.