ALTER TABLE SWITCH fails with indexed view I'm having issues with ALTER TABLE SWITCH between a staging table and a target table which is schema-bound to an indexed view.

When I issue the SWITCH statement, e.g

ALTER TABLE dbo.MASTERPrices_Staging switch TO dbo.MASTERPrices;

I get the following message:-

  Msg 11402, Level 16, State 1, Line 1
  ALTER TABLE SWITCH statement failed. Target table 'MASTERPrices' is referenced by 1 indexed view(s), but source table 'MASTERPrices_Staging' is only referenced by 0 indexed view(s). Every indexed view on the target table must have at least one matching indexed view on the source table.

We use only SQL Server 2008 Standard Edition so partitioning is not a solution. I need to SWITCH about 10 million rows daily, yet not loose the indexed view solution?

If I alter the view to not schema bound, then SWITCH Works, but when I ALTER the view again and set it back to SCHEMABOUND, all the indexes (12 of them including clustered index have dissapeared)

Any ideas anybody?

You need an indexed view on the staging table that matches the definition of the one on the production table, and indexes on the staging view that match every index on the production view.

See SqlFiddle

The idea is that the engine must replace every index, including the ones declared on the views. If it has build an index (ie. if there is an index on production but not on staging) then the switch will fail. Also all constraints, filters etc must match so that the engine knows that the data is valid (staging data will not violate production constraints).

Not sure why you need outer joins or anything similar, this should be straight forward.


Counting orders w/ new items Scenario:
Find out any customers in each factory that have three or more orders which contain new items they have never purchased before. For example, John has:

Order A: Item 1, Item 2
Order B: Item 2, Item 3, Item 4
Order C: Item 3

Order B has items John has never purchased before, while Order C does not.

To put it another way: Starting with each customer's second order, we want to know how many orders they have with at least one item that's never been on one of their orders before.

Table schema: see SQL Fiddle. RelatedRecordID binds multiple records into a single order.

My approach: see also SQL Fiddle above. This approach is flawed and not robust. In the previous John example, this method will give out 2 increments, but because Order C is placed after B, there is actually only one increment. And if Order B does not contain Item 3, its precedence won't affect the result.

Can anybody think of a fix or a new way to do this?

The CTE first finds the first order than every item was purchased (per customer and factory) and then we simply count the distinct order IDs (and subtract 1 as the very first order doesn't count):

with cta as
(
  select
      FactoryID, CustomerID,
      min(RelatedRecordID) as MinRelatedRecordID
  from
      Table1
  group by
      FactoryID, CustomerID, ItemID
)
select
    FactoryID, CustomerID,
    count(distinct MinRelatedRecordID)-1 as OrdersWithNewItems
from
    cta
group by
    FactoryID, CustomerID
having
    count(distinct MinRelatedRecordID) > 3 ;

Tested in SQLfiddle.com


Modelling a Database for Recording Taxes I need to cater to a complicated system of taxes. Following are the possible scenarios:

Simple flat tax rate on net amount e.g. 5%, 12%, 18%, etc.
A compound tax (e.g. 5%) which may be composed as below:

State Tax on net amount  - 2.5%
Central Tax on net amount - 2.5%

There might also be a case of a compound tax, where taxes are applicable not on net amount, but on the previous (net amount + tax). Eg:

Net Amount = 100
Row (1) Plus Tax 1 (5%) = 100 + 5 = 105
Row (2) Plus Tax 2 (5%) on i.e. 5% on 105 = 105 + 5.25 = 110.25

Over and above all this, I need to allow the user to add user defined taxes, to cater to those tax situations which my in-built taxes don't cover.

SOME REQUIREMENTS:

From (4), it is clear there will be some system-defined taxes and some user-defined taxes.

Another requirement is that in the case of a compound tax, I need to keep track of taxes paid for each component individually. That is, in the examples above, I need to keep track of Central Tax, State Tax, Tax 1 & Tax 2 separately.

I was leaning towards a self-referencing many-to-many relationship like so:

taxes (tax_id, tax_name, tax_rate, ...)
tax_components (tax_id, fk_tax_id, order, ...)

The order column would be used to describe the computation order, as in case (3) above.

Example tables for case (2) above:

taxes
----------------------------------------
| tax_id | tax_name         | tax_rate |
----------------------------------------
| 1      | GST 5%           | 5        |
----------------------------------------
| 2      | State GST 2.5%   | 2.5      |
----------------------------------------
| 3      | Central GST 2.5% | 2.5      |
----------------------------------------

tax_components
------------------------------
| tax_id | fk_tax_id | order |
------------------------------
| 1      | 2         | NA    |
------------------------------
| 1      | 3         | NA    |
------------------------------

Problem with this design can be best described in one word - Complicated. It will create a complicated hierarchy of taxes with even more complicated queries.

Can anyone offer a simpler design?

You've specified four types of taxes, but

one of them is user defined unfortunately that probably work. You'll have to define the types of taxes users can apply. How will they design the logic for a tax? Unless your providing a logic system, it seems unlikely.
a compound tax is really just two simple taxes paid to different taxing authorities, so we'll drop the designation of simple and complex and normalize that away.

  Problem with this design can be best described in one word - Complicated. It will create a complicated hierarchy of taxes with even more complicated queries.

The purpose of taxes is quite clear. They're supposed to be complicated. They're puzzles to confuse working class folks so rich capitalists can force their surfs to foot the bill for their own private thugs, armies, and corporate bailouts.

As far as a schema critique. I don't think the self-referencing design makes much sense here, and the constraints are impossible without triggers. I'd ditch it.

CREATE SCHEMA tax;

CREATE TYPE tax.cat AS ENUM ('NET', 'GROSS');
CREATE TABLE tax.authority ( authority_id, authority_name, cat, rate, subject )
AS VALUES
  ( 1, 'CA',      'NET',   CAST(0.025 AS real) ),
  ( 2, 'NATIONAL, 'GROSS', CAST(0.025 AS real) );

Now you link them together in a linking table. You can easily calculate the 0.050 using sum() in a subselect. To change the modes of application just do something like,

SELECT
  p.price,
  p.price * t.gross AS gross_tax,
  (p.price - p.cost) * t.net AS net_tax
FROM product AS p
-- should be an inner join if you have a method of going from
-- products to tax authorities
CROSS JOIN (
  SELECT
    sum(rate) FILTER (WHERE cat='NET') AS net,
    sum(rate) FILTER (WHERE cat='GROSS') AS gross
  FROM tax
) AS t;

Taxes are usually a function of locality, so I would use GIS or allow people to pick their own taxing authorities and what should be collected.


disk I/O error in SQLite What are the possible things that would trigger the "disk I/O error"? I've been having this problem and I couldn't find a solution. I have a SQLite3 database, and I'm trying to insert data from a file that contains SQL inserts.

Sample data in the file:

insert into files (filesize, filedate, md5, fullpath, origin) values (5795096,1370159412, "e846355215bbb9bf5f30102a49304ef1", "SDs/16G-1/DSC00144.JPG", "SDs");
insert into files (filesize, filedate, md5, fullpath, origin) values (5435597,1370159422, "1a7bcf3a4aaee3e8fdb304ab995ff80f", "SDs/16G-1/DSC00145.JPG", "SDs");
insert into files (filesize, filedate, md5, fullpath, origin) values (5121224,1370159432, "16d28e83599c731657a6cd7ff97a4903", "SDs/16G-1/DSC00146.JPG", "SDs");

I tried inserting that in the db file with the following command:

$ sqlite3 allfiles.db < insert.sql

See below the error that I get:

Error: near line 27: disk I/O error
Error: near line 28: disk I/O error
Error: near line 34: disk I/O error
Error: near line 39: disk I/O error
Error: near line 47: disk I/O error
Error: near line 129: disk I/O error

The input lines that don't generate error are successfully included, but I don't understand why some lines have errors, and they are not inserted into the DB. There's nothing special in the lines with error, and if I run the command again I get errors in different lines, which means it's random (not related to the data itself). I tried adding pragma syncrhonous = off; and pragma temp_store = memory;, to no success. I'm running that on a lubuntu, which runs in a VirtualBox virtual machine. The host machine is a windows 7. The pwd of the files is a shared folder, i.e., it's a folder in the host machine. If I run it in a "local folder" in the guest machine, the error doesn't happen, although for some reason it's much slower... In any case, I'd like to know about the I/O error.

That sounds like a problem with the shared drive arrangement, rather then one specific to sqlite. It would be worth stating which virtualisation method you are using as people may know if there are common problems with it.

The slower run from a resourse within the VM may be due to I/O overhead from the virt method, or extra IO contention because the input data was on a different physical drive to the VM. It may also be that the guest OS is memory starved, which will increase IO contention for large inputs as they can't be held in cache as easily on the host with it's larger memory. Again more detail would be needed for a surer answer.

Of course both problems could imply an intermittent IO error, so check you OS logs and the drives' SMART readings.

(this is likely to get more traction on serverfault, as it is likely not database specific)


Can a High number of Worktables/sec and/or workfiles/sec lead to tempdb Contention? We have a large number of very heavily used OLTP systems. One of our biggest issues right now is tempdb contention. I understand how creating many temp tables and table variables (which our developers love to do) will cause contention on GAM/SGAM/PFS pages, I am curious whether workfiles/worktables will also cause contention on those pages? My brain says yes, but I can't find anything to corroborate that.

The reason I'm concerned is that I don't want to be only tackling the temp table creation issue, but if we also need to get worktables and workfiles under control too.

For example one of our DB's currently has these numbers (slightly off peak):

Temp table Creation Rate: 30/sec

Workfiles / sec: 20000

Worktables / sec: 800

Short answer: yes.

Longer answer: Anything which creates and drops objects from tempdb will cause GAM/SGAM/IAM/PFS/etc. contention and needs to be addressed if possible.  Workspaces are creating tables under the covers so they'll cause a lot of the same issues.


Is it possible to develop a SSIS with a SQL Server Evaluation Version We are thinking about buying an SQL Standard Server for implementing an ETL via SSIS. Since it is very expensive for us I would like to test developing SSIS Packages on a free version. Since the express version does not integrate the SSIS I want to try it on the Evaluation 180 Expires Version of the SQL Server 2014 but cannot find anything about

- Is it possible
- Are there limitations.

Could anyone help me here?

For SQL Server 2005 and 2008 R2, the only way to get the Integration Services development environment, Business Intelligence Design Studio a.k.a. BIDS, was through the installation media aka you already bought Developer, Standard or Enterprise Edition. However, if you're looking to purchase SQL Server for the first time, it'd be insane to spend that level of money on an older version that is due to be retired in less than four years

2012 allowed you to install the design studio, rebranded as SQL Server Data Tools-BI edition or simply SSDT-BI, either from the installation media or you could download it directly from MS for free. See also

2014 and presumably 2016, the only way to get the SSIS development tools is to download directly. Again, no payment required, nothing. Just download and develop awesome business intelligence items.

But you're only licensed for development. You can't do things that are supporting production as that's part of the click through license agreement no one reads.

That should get SSIS set up and running via Visual Studio/SSDT/BIDS for nothing in your environment. There's no need to install an actual database, beyond wanting to evaluate it. Honestly, for that purpose, install SQL Express. It's free, handles up to 10GB as of current version and never expires.

My two bits on the Evaluation Edition is to never install that monstrosity unless you are planning on paving over the operating system at a later date. At a client, they have a licensed 2008 install and some one had the bright idea to install 2008 R2 evaluation edition on the same physical box. Due to the shared nature of 2008 and 2008 R2 libraries, the only thing that works on that box is the database engine itself. Attempt to access any of the tooling results in an expired license error message. The uninstaller can't uninstall 2008 R2 components because they're also shared by 2008... While a bit of rant, be cautious with the expiry nature of the evaluation edition. For SSIS, it will be overkill.


Encountering "Snapshot too old" error during execution of expdp command As am facing an issue while performing expdp command in my production db.(Oracle 11g in windows enviornment)

  cmd> expdp 'sys/123@PROD as sysdba' DUMPFILE=BACKUP_02082017_BACKUP.dmp LOGFILE=BakupLog_02082017_BACKUP.log SCHEMAS=A1,B2,C3,D4.. exclude=statistics consistent=y

It was taking more than 1 day to export the database sized 7GB. But my issue is that the exporting have error and shows error message

  ORA-31693: Table data object "owner"."PASSWORD_HISTORY" failed to load/unload and is being skipped due to error:
  ORA-02354: error in exporting/importing data
  ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19_255734752$" too small

As i set my retention policy to 16500 from default 900. Even though, same error was occurring.

As i am planning to increase the retention policy up to 10 hrs ie, 36000. Is it viable? I am confused that do my undo table space is capable for this or not?

Providing some more details:

>  show parameter undo_%;

 NAME                                             TYPE        VALUE
-------------------------------------------------- ----------- --------
undo_management                                   string     AUTO
undo_retention                                    integer    16500
undo_tablespace                                   string     UNDOTBS1

>  select file_name,tablespace_name,trunc(bytes/1024/1024) mb, trunc(maxbytes/1024/1024) mm
        FROM dba_data_files
        where tablespace_name = 'UNDOTBS1';

FILE_NAME                                  TABLESPACE_NAME    MB MM
--------------------------------------------------------------------
C:\APP\ADMIN\ORADATA\PROD\UNDOTBS01.DBF   UNDOTBS1          5630 32767

>Size of undo with current undo_retention :
     Actual Undo size[MBytes]:5630
     UNDO retention[Sec]:16500
     Needed Undo Size[MBytes]:909.433359

I am stuck with this issue. Anyone please advice how i deal with this error?

Thanks in advance.

The cause of the ORA-01555 error is undo_retention parameter has the lower value as compared to the time taken to complete the export with the parameter consistent=y(which is deprecated in 11g and should use flashback_time or flashback_scn).

Your undo retention should able to retain the undo information for the time period of export job.

For example, if you export starts at 00:00 AM and it takes 2 hours to complete then the undo_retention should be set at least 2 hours if you wish to get consistent data.

I have simulated the problem in my test server.

[oracle@orcl expdp_dump]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 8 09:41:40 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     2400
undo_tablespace                      string      UNDOTBS1

My undo retention is set to 40 minutes.

SQL> select bytes/1024/1024 from v$datafile where ts#=2;

BYTES/1024/1024
---------------
          32764

And my undo tablespace is 32GB large.

I tried to export one of my schemas.

expdp system/password directory=DP_DIR schemas=MYSCHEMA dumpfile=expdp_orcl_MYSCHEMA.dmp logfile=expdp_orcl_MYSCHEMA.log FLASHBACK_TIME=SYSTIMESTAMP

  Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" completed with 1 error(s) at Tue
  Feb 7 22:14:46 2017 elapsed 0 01:14:39

It took 1 hour and 14 minutes. And there were moderate DML operations in the database.

ORA-31693: Table data object "MYSCHEMA"."MS_TABLE":"MS_TABLE."MS_TABLE_B_2005" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 84 with name "_SYSSMU84_1745672109$" too small
. . exported "MYSCHEMA"."PRORATE_EVENT"              8.789 KB       4 rows
. . exported "MYSCHEMA"."PRORATE_RULE_GROUP"         5.945 KB       1 rows
. . exported "MYSCHEMA"."ORACLE_JOB_METADATA"            0 KB       0 rows

And I got the ORA-0155 error.

Then I increased the undo retention to 1 hour and 20 minutes.

SQL> alter system set undo_retention=4800;

System altered.

And again I tried to export the schema.

expdp system/password directory=DP_DIR schemas=MYSCHEMA dumpfile=expdp_orcl_MYSCHEMA.dmp logfile=expdp_orcl_MYSCHEMA.log FLASHBACK_TIME=SYSTIMESTAMP

  Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" successfully completed at Wed Feb
  8 22:15:52 2017 elapsed 0 01:15:45.

And there was no ORA-01555 anymore.

Total estimation using BLOCKS method: 182.9 GB

and the actual file size is 141 GB.

Why the difference in estimation and the actual size of the dump file? Look at this post for the answer.

Regarding the time taken to complete the export. Try to use the following parameters.

 Parallel
 DIRECT=y

Also, try to gather data dictionary statistics before starting the export job-

SQL> connect / as sysdba
SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.lock_table_stats (null,'X$KCCLH');
SQL> exec dbms_stats.gather_fixed_objects_stats;

Futher Readings:
Poor Performance With DataPump Export On Large Databases (Doc ID 473423.1)
Data Pump Export


How does SQL Server's optimizer estimate the number of rows in a joined table? I am running this query in the AdventureWorks2012 database:

SELECT
    s.SalesOrderID,
    d.CarrierTrackingNumber,
    d.ProductID,
    d.OrderQty
FROM Sales.SalesOrderHeader s
JOIN Sales.SalesOrderDetail d
    ON s.SalesOrderID = d.SalesOrderID
WHERE s.CustomerID = 11077

If I look at the estimated execution plan, I see the following:

The initial index seek (top right) is using the IX_SalesOrderHeader_CustomerID index and searching on the literal 11077. It has an estimate of 2.6192 rows.

If I use DBCC SHOW_STATISTICS ('Sales.SalesOrderHeader', 'IX_SalesOrderHeader_CustomerID') WITH HISTOGRAM, it shows that the value 11077 is between the two sampled keys 11019 and 11091.

The average number of distinct rows between 11019 and 11091 is 2.619718, or rounded to 2.61972 which is the value of estimated rows shown for the index seek.

The part I don't understand is the estimated number of rows for the clustered index seek against the SalesOrderDetail table.

If I run DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'):

So the density of the SalesOrderID (which I am joining on) is 3.178134E-05. That means that 1/3.178134E-05 (31465) equals the number of unique SalesOrderID values in the SalesOrderDetail table.

If there are 31465 unique SalesOrderID's in the SalesOrderDetail, then with an even distribution, the average number of rows per SalesOrderID is 121317 (total number of rows) divided by 31465. The average is 3.85561

So if the estimated number of rows to be loop through is 2.61972, and the average to be returned in 3.85561, the I would think the estimated number of rows would be 2.61972 * 3.85561 = 10.10062.

But the estimated number of rows is 11.4867.

I think my understanding of the second estimate is incorrect and the differing numbers seems to indicate that. What am I missing?

I think my understanding of the second estimate is incorrect and the differing numbers seems to indicate that. What am I missing?

Using the SQL Server 2012 cardinality estimator, the selectivity of the join drives the estimated number of rows on the inner side of the nested loops join, and not the other way around.

The 11.4867 number is derived (for display in showplan) by dividing the computed estimated cardinality of the join output (30.0919) by the number of iterations (2.61972). The result, using single-precision floating-point arithmetic, is 11.4867.

It really is as simple as that. Note that the (logical) join selectivity is independent of the choice of physical join operator. It remains the same whether the join is ultimately performed using a Nested Loops, Hash, or Merge Join physical operator.

In SQL Server 2012 and earlier, the join selectivity (as a whole) is estimated using the SalesOrderID histograms from each table (computed for each histogram step, after step boundary alignment using linear interpolation as necessary). The SalesOrderID histogram associated with the SalesOrderHeader table is also adjusted for the scaling effect of the independent CustomerID filter.

That is not to say there is anything fundamentally 'wrong' with the alternate calculation proposed in the question; it just makes a different set of assumptions. There will always be different ways to compute or combine estimates for a given sequence of logical operations. There is no general guarantee that different statistical methods applied to the same data will produce the same answers, or that one method would always be superior to the other. Inconsistencies resulting from the application of different statistical methods can even appear within a single final execution plan, though they are rarely noticed.

As a side-note, the SQL Server 2014 cardinality estimator takes a different approach to combining the independent-filter-adjusted histogram information ("coarse alignment"), which results in a different final estimate of 10.1006 rows for this query:

Plan for computation:

  CSelCalcExpressionComparedToExpression
  (QCOL: [s].SalesOrderID x_cmpEq QCOL: [d].SalesOrderID)

Loaded histogram for column QCOL: [s].SalesOrderID from stats with id 1
Loaded histogram for column QCOL: [d].SalesOrderID from stats with id 1

Stats collection generated:

  CStCollJoin(ID=4, **CARD=10.1006** x_jtInner)
      CStCollFilter(ID=3, CARD=2.61972)
          CStCollBaseTable(ID=1, CARD=31465 TBL: Sales.SalesOrderHeader AS TBL: s)
      CStCollBaseTable(ID=2, CARD=121317 TBL: Sales.SalesOrderDetail AS TBL: d)

This happens to be the same result as the calculation in the question, though the detailed reasoning is different (i.e. it is not based on an assumed nested loops implementation).


Database growth causing issues sending backup over network We take a full database backup on our primary SQL Server, and send that file over our network to a remote site where it is recovered on a backup SQL Server.  We use this backup database for slow queries that don't need the latest data, and, as a backup in the event of a complete failure of the primary database.

As the file grows it is causing problems with the available bandwidth.  When the file transfer takes place, it sucks down just about all the bandwidth, which is OK.  However as the file size grows (10+ gig) the amount of time that this transfer takes is becoming a problem because it is interfering with other things.

Is there some other strategy we can use to send our backup to a remote server?

There is no need to ship over the entire database. In fact SQL Server has a built-in solution for exactly this scenario, namely log-shipping. With log-shipping you only need to send the full backup once and subsequently you send over the log backups. Log backups will only contain changes from the last log backup and thus be usually quite small. The stand-by database will be read-only, perfect for reporting and queries. The article linked contains a link at the end to a step-by-step guide how to set it up.


Checking a wide table for nulls Possible Duplicate:
  Test if any fields are NULL

I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column other than writing something like ...WHERE Col1 IS NULL OR Col2 IS NULL OR...? Thanks in advance.

EDIT: The table is on a MS SQL Server 2008 instance. The columns in the table are of different datatypes.

Dynamic SQL:

DECLARE @SQL nvarchar(max);

SELECT
   @SQL = IsNull(@SQL + ' OR ', '') + QuoteName(Column_Name) + ' IS NULL'
FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   Table_Name = 'YourTable'
   AND Table_Schema = 'YourSchema';

EXEC ('SELECT Count(*) FROM YourSchema.YourTable WHERE ' + @SQL);

Another way:

SET ANSI_NULLS OFF; -- deprecated in SQL 2005 - 2012, will eventually be removed

SELECT Count(*) FROM YourSchema.YourTable WHERE NULL IN (Col1, Col2, Col3, Col4 ... Col80);

And yet another way, assuming your column names don't have space character in them:

In SSMS, navigate to the table in the object browser and drop open the column list. Drag the "Columns" folder to a query window. Use search and replace to change all the spaces in the selection to IS NULL OR. Prepend the following code and run it!

SELECT Count(*) FROM YourSchema.YourTable WHERE [put modified column list here]


Bitemporal Table with changing schema I have to design a database covering business assumptions using MS SQL-Server 2008 R2. The current data of the tables will be exported to text files and used as input for a certain application. I would like to implement the tables as bitemporal tables as described by R. Snodgrass in order to track valid and transaction times.

However, it is possible and quite likely that the logical schema of the tables could change, i.e. new columns could be added or old columns could be removed.
How would I reflect this in the design of the tables?

Obviously, I couldn't really delete a column, as all old data would disappear. Should I 'flag' not available columns in rows with a NULL entry? Likewise, how would I keep track of columns which are currently in use? This would be crucial for the export of the data as only 'active' columns should be exported.

And last, but not least, how could I log that a new column was added or an old one removed?

Im not sure if this answers all of your questions but this sounds very similar to what I do with our data warehouse. However the way i interpret your post you would want to have 1 table with your data, and another with your table definition meta data.

For the data table add a column to flag is_current. (Either an int or bit datatype)
Also have 2 date columns valid_dt_from & valid_dt_to.

If a row changes you update  the old row is_current = 0, and valid_dt_to =getdate ().

Insert the new row with is_current =1 and valid_dt_from = getdate ().

You can do the above with a single merge query.

To get the current data select * from table where is_current = 1.

Add new columns as they are needed. But don't delete old columns. In theory if you have to reconstruct the table to previous point in time all rows from that time should have null values at that time.

Create a similar table to define your data table definition. Add a record to show when each field gets added, removed and if it is current.

Your select query could be built dynamically. Where is current =1, or reconstructed as at a point in time.


normal Linux user for oracle database dumps is it possible to allow normal linux user " neither root nor oracle " to be able to execute expdp and impdp and to have access to  dpdump directory with rw access on oracle database 12c ?
i know this doesn't sound logic , however there is business need to avail it if possible
appreciate your support in advance

You can create a custom directory object for this user's use. Don't use the system-level directory created by default. Just make sure that both the user and Oracle will have read/write permissions to it. Also make sure the user has execute permissions on $ORACLE_HOME/bin, and a database account with read/write access to the directory object and whatever you want to import/export.
Be very careful with the individual permissions you grant to the database user. If this isn't just for an automated interface, but for an actual non-privileged user/person, then I would push back hard, as there are serious security implications:

In general, unprivileged users should not have login access to the database server such as is required to use data pump in the first place. Only sys admins and DBAs should be able to log directly in to the database server.
Using datapump to import objects or data for other schemas than the one actually running the import requires elevated database privileges that normal users shouldn't have. The ability to perform full imports, for example, allows the executing user to create other users, modify system settings, and generally make a hash out of everything if they don't know what they're doing.


Is possible to insert an array inside another array in postgresql? Let's say I have a table in postgresql called 'Test' which contains a single column called 'Column1' of the type int[]. So, it can contain an array of integers for each row, for example, {1,2,3}. I want to know if it is possible to insert an array of arrays in a table in postgresql, like {{1,2,3},{1,3,5},{9,10}} in a single cell of a table in postgresql? If so then what should be the datatype of the column, and how can we achieve that?

create table test (id int [])

✓

insert into test values ( ARRAY [1,2,3] )

1 rows affected

insert into test values ( ARRAY [ARRAY [1,2,3],ARRAY [1,2,4],ARRAY [1,2,5]] )

1 rows affected

select * from test

| id                        |
| :------------------------ |
| {1,2,3}                   |
| {{1,2,3},{1,2,4},{1,2,5}} |

db<>fiddle here

Really - multi-dimentional array is formed.

  I want to know if it is possible to insert an array of arrays in a table in postgresql, like {{1,2,3},{1,3,5},{9,10}} in a single cell of a table in postgresql?

No. 3rd element have 2 elements whereas first two have 3 elements. The error message

  ERROR:  multidimensional arrays must have array expressions with matching dimensions

will be produced.

But different dimensions in different records are allowed.


Adding DATETIME() and Status column with xp_FileExist result I'm trying to add a two columns in addition to the results of xp_FileExist. Here's my T-SQL:

DECLARE @path varchar (100)
DECLARE @file varchar (100)
SET @file = '<location>\*.txt'
SET @path = 'dir /B ' + @file
EXEC xp_cmdshell  @path, NO_OUTPUT

CREATE TABLE FILEEXISTS (
        [NAME] VARCHAR (50) NULL)

INSERT into FILEEXISTS exec master..xp_cmdshell @path

SELECT *
FROM FILEEXISTS

I tried to define columns in the "Create Table" section and add them after the @path but it throws an error every time saying something in the line of too many parameters. Any pointers?

You need to add code to your question that doesn't work (that causes the error you are seeing).

Having said that, you probably want to modify the code you are using to something like:

DECLARE @path varchar (100);
DECLARE @file varchar (100);
SET @file = 'C:\temp\*.*';
SET @path = 'dir /B ' + @file;

DECLARE @FILEEXISTS TABLE(
        [NAME] VARCHAR (255) NULL
);

INSERT into @FILEEXISTS
exec master..xp_cmdshell @path ;

SELECT *
FROM @FILEEXISTS;

DECLARE @table TABLE(
        [NAME] VARCHAR (255) NULL
        , somecol VARCHAR(255) NULL
);

INSERT INTO @table(NAME)
SELECT name
FROM @FILEEXISTS;

UPDATE @table SET somecol = 'test' /* add a 'WHERE' clause if necessary */;

SELECT *
FROM @table;

This uses a table-variable to store the results, and doesn't execute the unnecessary xp_cmdshell right after the set @path=... line.

The @table declaration (and other code) I added to the end allows you grab the results into @fileexists, then insert those results into @table and add the other columns as necessary.  xp_cmdshell returns a single column of results, one line for each line output by the command it runs.


Int for identity/primary key and Guid for public identifier, best practices? I almost always use an int for identity/primary (auto incrementing) clustered key for my tables. They're easy to query and debug, bounce around in SSMS, and plenty of other reasons.

I have a case where I'm creating an application that has your typical CRUD interface (create, read, update, delete) and I want to use a Guid in the url instead of the int.

An example table would be:

CustomerId int identity(1,1) PK clustered
CustomerName nvarchar(100)
Guid uniqueidentifier

In the past when someone was viewing a detail record I would just pass the identity:

/app/details/1 <-- id of the record

As I've seen in many applications, I like the idea of this:

/app/details/guidinstead

So in short, an int as the identity, and a guid as the public facing key.

What I'm concerned about is performance if any given table using this method grows substantially. There are business reasons for this, as in a person that has access can share a particular link with another to view details.

Another reason is because it does reduce some overhead on queries in a different manner, as in determining if the person has the "right" to view a particular record.

The initial hit is looking up the real ID of the record. After that all nested resources on the record are easily achieved with joins on the identity field across many tables.

I currently have no indexing set on the [Guid] uniqueidentifier column.

So to sum it up, I want to avoid showing incrementing keys in the URLs and I don't want a nightmare later on down the road if the tables end up with lots of data. Inserts and updates are currently using the int/identity column.

Operations are like this:

When creating a record, I use Guid.NewGuid() in .NET to generate the Guid column.
When updating a record, I find the CustomerId based on the Guid and update accordingly because this usually ends up updating/inserting in other relational tables as well and I need that CustomerId to do so.
When viewing the details of the record, it's WHERE Guid = @Guid
When deleting a record, it's WHERE Guid = @Guid

Questions:

Is it worth having both int and guid in the table or should I just drop the int entirely?
Any other settings I should place on the table?
Is there a better option? Another impossible to guess sequence of some sort that doesn't take as much space?

It's early stages so easy for me to make changes now.

Is it worth having both int and guid in the table or should I just drop the int entirely?

Yes, from the perspective that you can have the identity column be a unique clustered index to take advantage of inserts being append-only to the table, and you can have a nonclustered primary key on your GUID column for both security and uniqueness when locating records.

  Any other settings I should place on the table?

You could consider using NEWSEQUENTIALID to have the GUID inserts be less random, or try to match the behavior natively in .NET. If someone were quite dedicated, they could perhaps guess the next sequential GUID. If you think you might attract that kind of attention, generate them in a normal, random way.

I'd also wanna make the identity column a BIGINT. They're much harder to run out of than INTs. People who get all up in arms about the 4 extra bytes per row have never dealt with the process of altering that attribute after running out of INTs.

  Is there a better option? Another impossible to guess sequence of some sort that doesn't take as much space?

Nope, you're on the right track.


pgbouncer bind(): Permission denied I have pgbouncer running locally with config:

[databases]
* = host=myremoteIP port=5432 user=postgres
listen_addr = *
listen_port = 6432

When I connect using pgsql using:

psql -h 127.0.0.1 -p 6432 -U postgres -W

I receive an error:

  psql: ERROR:  pgbouncer cannot connect to server

/var/log/pgbouncer/pgbouncer.log contains the following:

  WARNING Cannot listen on unix:/var/run/postgresql/.s.PGSQL.6432:
  bind(): Permission denied

Disabling the firewall on the local and remote machines still results in the same error.

What configuration am I missing?

The error message in the log was a red herring, and due to the configuration for Unix sockets (when I only needed TCP). Disabling Unix sockets by setting the unix_socket_dir to an empty string (as per the documentation) made those errors disappear from the logs.

The connection problem was because I needed to specify the password in the pgbouncer.ini file.

The line:

* = host=myremoteIP port=5432 user=postgres

Needed to be:

* = host=myremoteIP port=5432 user=postgres password=mypassword

I can now connect using psql.


layout neo4j graph based on points I have a neo4j database with some data stored in Points (e.g. coordinates: point({srid:4326, x:-4.224099535439225, y:52.10621881941465})).

I want the neo4j browser to honour these points when laying out the graph visually. Is there a way to interact with its layout manager, and ask it to use these points?

You can't do it, without using third-party visualisation software


How get in SQL pairs of connections in a FW Logs I have a database with orign_ip, destination_ip, and other columns.
I am looking for a way to get in SQL just the connections between two IP's, I need to get just the list.
This is my first step :

SELECT DISTINCT orig_h , resp_h from gustavo.conn;
 - 15.214.135.70                15.214.159.100
 - 15.214.136.45                15.214.137.70

But How I can get from this result the just the rows that match with

15.214.135.70               15.214.159.100

SELECT DISTINCT orig_h , resp_h
from gustavo.conn
WHERE orig_h = '15.214.135.70' AND resp_h = '15.214.159.100'


Unable to browse to folder on SSRS 2012 through report url I have installed SSRS 2012 in native mode on windows server 2012 r2 and deployed reports to the server successfully. I am having difficult browsing to any report folder through the report URL. I can browse to the report server home folder using the following URL:

http://192.168.8.1/My_ReportServer

From this URL I can then successfully navigate through the folders and run the reports correctly.

If I try to navigate directly to one of the folders in the home folder through URL.

http://192.168.8.1/My_ReportServer/test

I receive the following message:

Server Error in '/My_ReportServerApplication.

The resource cannot be found.
  Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable.  Please review the following URL and make sure that it is spelled correctly.

Requested URL: /My_ReportServer/test.

Does anyone know the cause of this problem? or point me in the right direction?

Many thanks

You will have to call /pages/folder.aspx with an ItemPath parameter as absolute path /Test which url encoded will look like this.

  http://192.168.8.1/My_ReportSever/Pages/Folder.aspx?ItemPath=%2fTest

You can of course always copy/paste the url when you browse the server.


What kind of table structure to use to display product, testing, and test results? I am creating a database, but I am a beginner. We test several different kinds of paint, with specific test methods, and I would like to document all of the results. So for an example, I would test one type of paint for adhesion, blister resistance, and sag. But, the adhesion testing records both 1 and 7 day results. I have created a table already, and was informed the structure was completely wrong. I only have one table, with about 100 columns that has all of the testing results. An example of my column headings are: ProductCode, BatchNumber, Adhesion(1 day), Adhesion (7 day), Sag. And the product information and test results are listed below. How should this actually be structured?

We would need a larger view of what you're doing to be super accurate, but based on your testing example you'll want a basic setup like this:

CREATE TABLE dbo.Products
(
    productCode NVARCHAR(100)
    --Any other columns relevant to describing a product go here
    ,CONSTRAINT PK_Products PRIMARY KEY (productCode)
)
GO
CREATE TABLE dbo.TestTypes
(
    testType NVARCHAR(100)
    ,CONSTRAINT PK_TestTypes PRIMARY KEY (testType)
)
Go
CREATE TABLE dbo.Tests
(
    testID int IDENTITY(1,1)
    ,productCode nvarchar(100)
    ,batchNumber int
    ,testType nvarchar(100)
    ,passed bit
    ,CONSTRAINT PK_Tests PRIMARY KEY (testID)
    ,CONSTRAINT FK_Tests_Products FOREIGN KEY (productCode) REFERENCES dbo.Products (productCode)
    ,CONSTRAINT FK_Tests_TestTypes FOREIGN KEY (testType) REFERENCES dbo.TestTypes (testType)
)

Products will just contain your basic info about a product and I'm assuming your ProductCode is a unique value that can be used to identify a particular product. If that is not true you can slap an IDENTITY column in there to be your primary key.

TestTypes will hold the name of each type of test you want to have a record for, right now it's just a primary keyed name to enforce uniqueness and for use with a FK to constrain your test column's value. You can add any other useful information that may need to be known for a particular test to this table.

Finally, you can record specific Tests for a particular Product and batch into the Tests table. I am somewhat assuming the tests results are a simple pass/fail, if not you can change up the passed column to reflect the results you need to record. So what will happen with this table is that every test for a product gets it's own row with it's result, this allows you to run a simple query on a productCode and a batch to retrieve all the Tests without having extraneous columns that may or may not be filled in.

This also allows you to add new Tests to the system without having to alter your Table structure and if applicable your products don't have empty columns for tests that don't apply to them.


Ola Hallengren: encrypt, but verify? I've been using Ola Hallengren's scripts for a few years now, and a recent upgrade to MSSQL2017 has allowed me to start testing encrypted back-ups. Before anyone asks: yes, master keys and encryption certificates have been backed up and stored in our password manager.

I'm running into an issue with the following parameter combination:

EXECUTE [OlaHallengren].[DatabaseBackup]
     @Databases = 'SYSTEM_DATABASES'
    ,@Directory = N'\\<SHARE>\SQLBackup\Daily'
    ,@BackupType = 'FULL'
    ,@Verify = 'Y'
    ,@CheckSum = 'Y'
    ,@Encrypt = 'Y'
    ,@EncryptionAlgorithm = 'AES_256'
    ,@ServerCertificate = 'DBBackupEncryptCert<SERVERNAME>'
    ,@LogToTable = 'Y'

The back-up runs fine, but verification does not:

Command: RESTORE VERIFYONLY FROM DISK = N'\\<SHARE>\SQLBackup\Daily\<SERVER>\master\FULL\...
Process Exit Code 1.
The step failed.

It seems to be an issue with Ola Hallengren's script not being able to open the master key, because this doesn't work:

RESTORE VERIFYONLY FROM DISK = N'\\<SHARE>\SQLBackup\Daily\<SERVER>\master\FULL\<SERVER>_master_FULL_<DATE>_<TIME>.bak'

but this does:

use master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PASSWORD>';

RESTORE VERIFYONLY FROM DISK = N'\\<SHARE>\SQLBackup\Daily\<SERVER>\master\FULL\<SERVER>_master_FULL_<DATE>_<TIME>.bak'

CLOSE MASTER KEY

Am I trying to have my cake and eat it, too?
Or is there a way to have both encryption and verification with Ola's scripts?

I've found the root cause and solution.

Ola Hallengren couldn't get to the back-up encryption certificate because doesn't know the Database Master Key (DMK) encryption password. The DMK can also be encrypted using the Service Master Key (SMK), which is automatically opened for a connection with sufficient privileges.

I've added SMK encryption to the DMK, which allows Ola's scripts to access the back-up encryption certificate.

I guess it only works if it's turtles all the way down. :-P


autocommit on long transaction after connection failure Is there any spec on what impact a connection failure has on a mutation statement if autocommit is on?

For example, if a client starts, in autocommit mode, a mutation that can take a long time and then goes away, is it defined whether this will or will not commit?

For various reasons, we don't have the ability to add a start transaction before the statement which I assume would automatically rollback if the connection failed before sending a commit.

I suspect the behavior is not defined but thought I'd ask.

Mostly wondering about mysql and pg.

It will automatically rollback.

But...  If you do not catch the "go away", but automatically reconnect and proceed with the other statements in the transaction, you could have a mess -- whether it is autocommit or not.

So, always check for errors and take evasive action.


Server Logon Trigger fails after deleting many records in "Logon-Table" -SQL Error 2801 We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table.

The table doesn't have clustered index, but it does have a "PK_ID" column, which is bigint and autoincrement.

Last week, we deleted roughly 80 million from a total of 90 million records in that "login table" (with a delete from..., we didn't truncate the table).

But since then we see multiple cases where the logon trigger fails with

Logon failed for login 'SQL\xy' due to trigger execution.
Changed database context to ‘master’.
(Microsoft SQL Server, Error: 17892)

We did restart the whole windows server a couple of times and afterwards the error went away for a few hours, but seems to come back every now and then. Note that this only started to appear after we deleted so many records.

We found out that prior to users not being able to login, we get the following error:

Msg 2801, Level 16, State 1,:
The definition of object 'my_proc' has changed since it was compiled.

Afterwards we get the error 17892 as described above.

Does anybody know what the problem could be?

Trigger definition

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [Tr_My_Logon]
     ON ALL SERVER  WITH EXECUTE AS 'xy'
     FOR LOGON
     AS
     BEGIN
        BEGIN TRY
            INSERT INTO mydatabase.myschema.myLogonHistoryTable
                SELECT GETDATE(),
                      ORIGINAL_LOGIN(),
                      EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(500)'),
                      @@SPID,
                      program_name(),
                      HOST_NAME(),
                      EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(500)')
       END TRY

       BEGIN CATCH
           PRINT ERROR_MESSAGE()
       END CATCH

     END
    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

From a logging perspective, if you intend to delete more than 50% of the rows from a table, you should use the following methodology:

Set the transaction isolation level to serializable
Begin a transaction
Create a new table, named the same as the original table, with a _x
suffix
Copy the results you want to keep from the original table to the _x
one
Drop the original table
Rename the _x table back to the original table
Commit the transaction

Also, the PK_ID column that you described should be the PK and clustred index for the table, otherwise its just a heap.

Also, in terms of logging server logins, perhaps you'd have better luck using an Audit or extended event session?

The "The definition of object ‘my_proc’ has changed since it was compiled" error that you described should be resolvable by calling sp_recompile.


Error creating package, Failed to save package file, error 0x80040154 "Class not registered" A few colleagues & I have installed the latest Visual Studio (SSDT) - BIDS.
We are unable to create packages, in Solution Explorer right clicking on SSIS Packages > New SSIS Package results in this infamous error:

TITLE: Microsoft Visual Studio
Error creating package
Failed to save package file "C:\Users\userid\AppData\Local\Temp\tmp9ABC.tmp" with error 0x80040154 "Class not registered".

Googling this error brings up results from 2008 and it pertains to MSXML. I've tried all the solutions, install MSXML6.0, install OWC, regsvr32 the DLLs, I ran procmon and there is no hint of the problem in the trace.
I am very confident this is a different bug to the 2008 one as none of the solutions work.
One of my colleagues installed BIDS at home and it works perfectly. The work network we are in is locked down, however we all ran the install as Admins and the ISO file was > 2gig so we don't believe a component was missed during the install, but this could be wrong.
Has anyone encountered this error? Anyone got any ideas of how to troubleshoot this?

I've uninstalled the old version of VS2017 SSDT (15.4.xx) and download the latest SSDT 2017 (15.5.x) from Microsoft then, install it.
It seems to be working now. I can create a new SSIS package. Yehey!


Duplicate column for faster queries? The title doesn't make too much sense, but I couldn't think a better title for this problem.

I have the following tables

Projects

id
name

Customers

id
id_project
name

Payments

id
id_customer
date
sum

When a users enters the system, he will have access to a certain project. Now, I want to list all the payments for that project, and it should be pretty easy:

SELECT FROM payments where id_customer in (SELECT id from customers where id_project = 5)

My question is : if it isn't better to add a column id_project to payments table this way the queries will be easier and faster.

It seems you are asking if denormalization makes sense.

  Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data. In some cases, denormalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.

The answer is always "it depends", so here's my rule of thumb:

If ...

the amount of data is not large
you aren't doing a ton of joins already
and/or database performance is not currently a bottleneck

then stay normalized. Yes, denormalization is faster, but it also means you have redundant data in the system -- data that has to be maintained and kept in sync. There is no longer "one source" for that data, but multiple sources that can deviate. This is risky over time, so you shouldn't do it unless you have very good reasons to do it, backed by some benchmarks.

I would only denormalize when ...

the amount of data is very large
joins are expensive and you have to do a lot of them to get even trivial queries returned
database performance is a bottleneck and/or you want to go as fast as possible

Joins are very fast on modern hardware, but they are never free.


Finding Culprit Blocking Sql Whenever in our system there is a block (everything stopped). I see the below query is the culprit,
  (@P1 numeric(10))SELECT "Col1128","Tbl1029"."A_NAME" "Col1124" FROM "DbName"."Schema"."MyTable1"
"Tbl1029" RIGHT OUTER JOIN (SELECT "Tbl1027"."MyColumn1" "Col1126","Tbl1027"."MyColumn2"
"Col1127","Tbl1027"."MyColumn4" "Col1128","Tbl1027"."MyColumn5" "Col1130","Tbl1027"."MyColumn6"
"Col1129","Tbl1027"."MyColumn5" "Col1131" FROM "DbName"."Schame"."MyTable2" "Tbl1027" WHERE
"Tbl1027"."MyColumn1"=@P1) Qry1132 ON CONVERT(numeric(2,0),"Tbl1029"."MyColumn3",0)="Col1126"

We have 2 separate DB servers with completely different databases. The above is running in DB server 2 but request is coming DB server 1 (client host name). We have 100 of applications connecting to DB servers. Also, we have link server among DB servers.
Now, I know that the query came form DB server 1 with ProgramName Microsoft SQL Server. Now I am confused whether the program Microsoft SQL Server is indicating the link server query or application query using ORM.
I am searching in all applications but not finding anything yet. Form the DBA perspective what else I can do to get the real issue

Now, I know that the query came form DB server 1 with ProgramName Microsoft SQL Server. Now I am confused whether the program Microsoft SQL Server is indicating the link server query or application query using ORM.

The host name "DB server 1" along with the "Microsoft SQL Server" program name indicates a linked server query, not a query executed directly from the application.

From the DBA perspective what else I can do to get the real issue?

As @Learning_DBAdmin suggested in comments, examine the active queries running on DB server 1 during the blocking episode. One or more should show the query with the linked server along with the originating application host and program name.
The real issue is likely the need for query/index tuning to optimize the remote query. You may need indexes on the join/where clause columns. The explict conversion on Tbl1029.MyColumn3 will preclude using an index on the column efficiently (if one exists). The resultant full scans can lead to blocking.


Select only the last record for multiple values I have a table called "sessions" with say 4 columns like below
id  name s_time f_time
01  abc  10.15  10.45
02  abc  11.05  11.55
03  abc  12.18  13.46
04  abc  15.12  16.53
05  def  10.01  12.58
06  def  14.06  16.51
07  def  17.43  18.54
08  xyz  09.45  12.36
09  xyz  14.51  15.57
10  xyz  16.23  18.01

How can I get the last f_time for each name?
What I need is:
name f_time
abc  16.53
def  18.54
xyz  18.01

What am trying is this:
select name,f_time
from sessions
where name in ('abc','def','xyz')
order by id DESC LIMIT 1;

but am only getting the finish time for the first name.
MariaDB 10.1.37

give your example data this should do it.

SELECT name, max(f_time) AS f_time
FROM sessions
WHERE name IN ('abc','def','xyz')
GROUP BY name
ORDER BY name;

If you need to pick the f_time according to some other column it gets messy.

In Postgresql you can use the distinct on SQL extension.

SELECT DISTNICT ON (name)
    name, f_time
FROM sessions
WHERE name IN ('abc','def','xyz')
ORDER BY name, id desc;

i think there's also a way using window functions


Can I Rebuild indexes in a DB except for one table? I have a table in my database that is constantly monitored by an application server for changes. Unfortunately rebuilding the indexes connected with this table breaks the app server's connection. This causes print jobs and various other processes to fail silently on the application server.

I have created a small monitoring SQL job that lets me know when this happens, but I would prefer to prevent it from happening in the first place.

I need to rebuild indexes weekly for performance reasons, but I would be fine with skipping the indexes associated with that table.

How can I rebuild indexes for the rest of the database without touching that table?

Edit: I am currently using an SSIS job (the canned maintenance plan) to rebuild all indexes for all tables in the DB. After that it updates stats and checks integrity.

You should implement a better solution, such as Michelle Ufford's - it will perform much more intelligent decisions on which indexes to defrag, rebuild or leave alone, and it is very easy to have exclusions:

http://sqlfool.com/2011/06/index-defrag-script-v4-1/


How to move a 80GB MySQL Database with least effort and least time offline? Im a software developer with 4 years experience but brand new in my company, my first job is to move a 80GB mySQL database from a very very small and old database server to a brand new.
The problem: It is the alltime working database for nearly everything in my company... I can't reduce the data to less gb because it is all needed data.
So: How to do this and how to achieve this with the least time offline so around 500 people can work again fastly...

Information requested for Help:

SELECT COUNT(*) as '# TABLES', CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') as DATA, CONCAT(ROUND(sum(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') as INDEXES, CONCAT(sum(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2)), 'G') as TOTAL, engine as ENGINE FROM information_schema.TABLES GROUP BY engine;

Answer:

3 NULL NULL NULL NULL
13 1.36G 1.70G 3.05G InnoDB
13 0.00G 0.00G 0.00G MEMORY
1159 44.04G 34.89G 78.90G MyISAM

The main problem with MyISAM is that you cannot create a copy of the database without locking the tables (otherwise the copy may be inconsistent). I would recommend you to research a potential migration to InnoDB.

You will have to execute FLUSH TABLES WITH READ LOCK in order to lock all tables in read only mode, then copy all the .MYD, .MYI and .frm files from the filesystem (basically, your entire datadir). Once you copy those files, you will want to get your binary log coordinates so you can setup replication with SHOW MASTER STATUS. If you cannot setup replication, you will have to leave the server in  read_only mode while you setup the other one. Otherwise, UNLOCK TABLES;. In previous versions of MySQL, you had the mysqlhotcopy script that automatized some of those tasks for MyISAM.

EDIT: As you have some InnoDB tables, you must be sure to copy the whole data directory (including ib_logfile* and ib_data*ones), and not only single files- otherwise the copy won't succeed.

Setup the new server copying the files to the right place, start the new server, and you can now setup a replication between the old server and the new one. Once you double check that the setup is correct and the key_buffer/buffer pool is warm, you will put the old server in read_only mode and failover your applications to the new one.

The idea is to avoid mysqldump, which is slower for exporting, but above all, for importing, although it is easier to use. That it is what it is internally executed with phpMyAdmin's "export/import database" feature.

I know this is a high level overview, but I recommend you to start reasearching the links I mentioned and about MySQL replication and asking additional specific questions if needed.


Storing/Retrieving Chinese/Unicode in PL/SQL We are on Oracle9i Enterprise Edition. I tried the following in PL/SQL.
MANUFACTUERER Field is defined as VARCHAR2(75).  I tried similar table with NVARCHAR2(75) instead, and same result.

INSERT INTO TLP.part_extensions_tab(MANUFACTURER, MANUFACTURER_ADDRESS_LINE )
SELECT 'Neal Chinese Test 06', '统一通信系统）【功能性备注：集团电话' FROM DUAL;
commit;
select MANUFACTURER, MANUFACTURER_ADDRESS_LINE from TLP.part_extensions_tab
where MANUFACTURER like 'Neal Chinese Test 06' ;

The SQL runs, but returns the following question marks; so I'm not sure the insert worked, or if I have a problem displaying the text back in Chinese.

My other Googling lead me down this path (not sure if it is a dead end or not):

DECLARE
   MyLanguage nvarchar2(30);

BEGIN
  --EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''SPANISH''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''SIMPLIFIED CHINESE_CHINA.ZHS16GBK''';

  select Value into MyLanguage from nls_database_parameters where Parameter = 'NLS_LANGUAGE';
  DBMS_OUTPUT.put_line('MyLanguage=' || MyLanguage);
  --DBMS_OUTPUT.put_line('NLS_LANG=' || NLS_LANG);
END;

--SELECT USERENV ('language') FROM DUAL;

But this gives me "ORA-12705; invalid or unknown NLS parameter value specified.  I don't know if that language needs to be installed on my client, my server, or my syntax is just wrong.
When I tried Spanish, the SQL ran without error, and it shows Spanish.American in the result.
emphasized text

You can not store Chinese characters in a Western European characterset (WE8ISO8859P1).

Define the MANUFACTURER_ADDRESS_LINE column as NVARCHAR2 to use AL16UTF16 and insert the string as below (notice the N modifier):

INSERT INTO tc(MANUFACTURER, MANUFACTURER_ADDRESS_LINE )
SELECT 'Neal Chinese Test 06', N'统一通信系统）【功能性备注：集团电话' FROM DUAL;
commit;


mysqld upgrade 5.5 -> 5.6 mysql Error: Failed while fetching Server version! Could be due to unauthorized access After updating mysql with yum to 5.6 I am getting such an error message when trying to run mysql

[root@host rpm]# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed

I also tried

mysql_upgrade -u root -p

and the result is the same.

Please check if you are using correct root password. You can also use another login which has all privileges. Check it by logging to mysql:

Successful login example:

After checking return to server try again mysql_upgrade


Nologging materialized view I need to create a materialized view (MV) with auto refresh every hour. After I create it, a lot of redo logs are generated (10GB per hour). I think the solution is to recreate the MV in NOLOGGING mode.

Are there some problems with my DG database and with a second DG database in read only mode?

Can my RMAN backups restore fail, or do I need only to recreate the MV after a data recovery?

Redo log is crucial in data guard environment. Hence we enable force logging before configuring data guard at the database level. So, no matter whether you use NOLOGGING clause or not the operation will be logged and replicated in the standby site.

  When you perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause, the standby database is invalidated and may require substantial DBA administrative activities to repair. You can specify the SQL ALTER DATABASE or SQL ALTER TABLESPACE statement with the FORCE LOGGING clause to override the NOLOGGING setting. However, this statement will not repair an already invalidated database.

NOLOGGING or Unrecoverable Operations

You might be interested in this thread from the asktom site.

Dataguard and Nologging


Mysql Select recent 2 records for each group[user] I have two tables users and login_track. Each user may or may not have login records in login_track table. I want to retrieve recent 2 login dates for each user.

Table structures as below:
users:

user_id    name
     1     John
     2     Mike
     3     Anderson
    .....

login_track:

  track_id   user_id   login_date
   100           1    2017-06-20
   101           1    2017-06-21
   102           2    2017-06-21
   103           1    2017-06-23
   104           2    2017-06-23
   105           1    2017-06-27

So desired result should be.

 user_id  name         login_date
    1      John        2017-06-27, 2017-06-23
    2      Mike        2017-06-23, 2017-06-21
    3      Anderson    NULL

Note : User id 3 (Anderson) has no records in login_track table, even though it should list in the final result with NULL value as login_date column.

In this case I'd suggest to use a subquery to count rows using a variable.

select   user_id, name, group_concat(login_date)
from     (select user_id, login_date, name
          from (select user_id, login_date, name,
                       if(@last_user = user_id, @rn := @rn + 1, @rn := 0) rn,
                       @last_user := user_id
                from (select @last_user := 0, @rn := 0) a,
                     (select users.user_id, login_date, users.name
                      from users
                      left join login_track
                      on users.user_id = login_track.user_id
                      order by users.user_id, login_date desc) b
               ) c
          where rn <= 1
         ) d
group by user_id;

| user_id | name     | group_concat(login_date) |
|---------|----------|--------------------------|
| 1       | John     | 2017-06-27,2017-06-23    |
| 2       | Mike     | 2017-06-23,2017-06-21    |
| 3       | Anderson | NULL                     |

rextester here


Postgres Performance Over Group by with MAX and MIN I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system.

Table details :

CREATE TABLE customer_location ( id UUID, location_id UUID, customer_id UUID );
CREATE TABLE customer_receipt  ( id UUID, receipt_id UUID, customer_id UUID );
CREATE TABLE receipt_product ( id UUID, receipt_id UUID, product_id UUID, txn_dt_tm Timestamp, status TEXT, is_active boolean);

Query :

SELECT cr.customer_id, min(txn_dt_tm), max(txn_dt_tm)
FROM customer_location cl
JOIN customer_receipt cr ON cl.customer_id = cr.customer_id AND cl.location_id = 'UUID here'
JOIN receipt_product rp ON rp.receipt_id = cr.receipt_id AND status = 'PURCHASED'
    AND is_active = true
GROUP BY cr.customer_id

  INDEX :

  receipt_product : Index applied on (status and is_active).

  Other tables are implemented with the primary key index.

Though the query engine performs the SEQ scan rather index. Since I see this could be the huge load for index search and thus the engine picked up the Sequential scan. The time taken to load the data is approximately 36 Seconds which is a huge load time.

Here is the query plan http://explain.depesz.com/s/5hV.

HashAggregate  (cost=1023419.340..1023425.980 rows=664 width=24) (actual time=42811.045..42811.152 rows=142 loops=1)   ->  Hash Right Join  (cost=464863.230..1011575.600 rows=1579165 width=24) (actual time=17306.921..41755.433 rows=1101749 loops=1)
          Hash Cond: (receipt_product.receipt_id = customer_receipt.receipt_id)
        ->  Seq Scan on receipt_product  (cost=0.000..405877.220 rows=6981890 width=24) (actual time=0.051..14179.158 rows=6890896 loops=1)
                Filter: (is_active AND (status <> 'PURCHASED'::uuid))
                Rows Removed by Filter: 2909925
        ->  Hash  (cost=434327.670..434327.670 rows=1579165 width=32) (actual time=17306.658..17306.658 rows=1101749 loops=1)
                Buckets: 65536  Batches: 4  Memory Usage: 17257kB
              ->  Hash Right Join  (cost=44.660..434327.670 rows=1579165 width=32) (actual time=2345.366..16229.130 rows=1101749 loops=1)
                      Hash Cond: (customer_receipt.customer_id = customer_location.customer_id)
                      Join Filter: (customer_location.location_id = 'location_id'::uuid)
                      Rows Removed by Join Filter: 7182859
                    ->  Seq Scan on customer_receipt  (cost=0.000..299670.480 rows=8283848 width=32) (actual time=0.004..7416.387 rows=8283831 loops=1)
                    ->  Hash  (cost=33.180..33.180 rows=918 width=32) (actual time=1.551..1.551 rows=918 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 58kB
                          ->  Seq Scan on customer_location  (cost=0.000..33.180 rows=918 width=32) (actual time=0.006..0.759 rows=918 loops=1)

By re-writing the same query as postgres function which would iterate every customer of location and would fetch the max and min date of that customer. Which performs 6 times faster ( 6 seconds aprx. ) but when data grows huge, that would take time.

I strongly hope to see the false bucket here also like to have the experts' Suggestions.

UPDATE

I tried disabled the seq scan. still that takes same amount of time with index scans. So Query plan picked up the right way to execute. What are all the possibilities to reduce the execution time here. Is there any settings which would deal with the execution speed.

I also adding the query tuning setting parameters here from my DB.

cpu_index_tuple_cost        0.005
cpu_operator_cost           0.0025
cpu_tuple_cost              0.01
effective_cache_size        464300
random_page_cost            1
seq_page_cost               1

Thank you.

The following indexes may help you:

CREATE INDEX stkx_custloc_ind1
  ON customer_location
  USING btree
  (location_id, customer_id);

CREATE INDEX stkx_custrecpt_ind1
  ON customer_receipt
  USING btree
  (customer_id, receipt_id);

CREATE INDEX stkx_recptprod_ind1
  ON receipt_product
  USING btree
  (receipt_id, status COLLATE pg_catalog."default", is_active, txn_dt_tm);

CREATE INDEX stkx_recptprod_ind2
  ON receipt_product
  USING btree
  (receipt_id, txn_dt_tm, status COLLATE pg_catalog."default", is_active);

Please note, in addition, that your query may be better tuned with an inline view:

SELECT
        cr.customer_id,
        min(min_txn_dt_tm),
        max(max_txn_dt_tm)
FROM customer_location cl
JOIN customer_receipt cr ON cl.customer_id = cr.customer_id AND cl.location_id = 'UUID HERE'
JOIN (
        select re_pr.receipt_id, min(txn_dt_tm) as min_txn_dt_tm, max(txn_dt_tm) as max_txn_dt_tm from receipt_product re_pr where status = 'PURCHASED'
    AND is_active = true group by re_pr.receipt_id) rp ON rp.receipt_id = cr.receipt_id
GROUP BY cr.customer_id
;

Your schema suffers from having the 'id' antipattern.  With this you have made a programmer's life easier until the application allows you to enter unexpected data, such as multiple customers having the same receipts.

I hope this helps!


How to resolve: Specified key was too long; max key length is 767 bytes Our server is creating a lot of tables and one of them is the Lock_ table.

We are using MySQL 5.5 on Percona. Also we are using utf8_general_ci and utf8.

It throws the following error when creating the following index on Lock_ table:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Here is the create-table query:

create table Lock_ (
    uuid_ VARCHAR(75) null,
    lockId LONG not null primary key,
    companyId LONG,
    userId LONG,
    userName VARCHAR(75) null,
    createDate DATE null,
    className VARCHAR(75) null,
    key_ VARCHAR(200) null,
    owner VARCHAR(255) null,
    inheritable BOOLEAN,
    expirationDate DATE null
);

Index query:

create unique index IX_DD635956 on Lock_ (className, key_, owner);

This problem is happening on one of our prod servers (previously it was standalone mysql and then later moved to percona, but the problem was present in both the editions), but this is working fine on our dev environment.

So is there a way we can update the prod environment's index length or something so that it does not throw this error?

Thanks

I suspect the difference is a Unicode length issue. As you are not using a single-byte-per-character collation the total length of the fields in your key is 530 character (75+200+255) and not 530 bytes, and 530 characters could easily be more than 767 bytes depending on what language you are storing in those strings (in fact definitely will be if mysql stores unicode data always as two-bytes-per-character as MS SQL Server does in its NVARCHAR column type).

As to why it is working in Dev but not live:

If mysql doesn't use a fixed length encoding then it could be that existing data in some rows in production is long enough to break the 767 byte limit, but in Dev there are no such rows. The length() function (http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length) returns the number of bytes, not characters, so you can scan for such rows by selecting where LENGTH(className)+LENGTH(key)+LENGTH(owner) > 767.
Otherwise it could be that your collation settings are not identical between dev and production, try verifying those.


Availability Group failover stuck in Resolving State I am setting up a lab for testing automatic failover for SQL Server

I have a 2 node WSFC (SQL-CLUSTER-TEST-01) with the nodes

SQL-TEST-01
SQL-TEST-02

A listener SQL-HA-GRP-01

My initial testing is to just power off either of the nodes (virtual machines). It it failing correctly in one direction - SQL-TEST-01 to SQL-TEST-02. But when SQL-TEST-02 is the primary and I pull the power on it, the availability group goes into a permanent "resolving" state which I can only resolve by powering on SQL-TEST-02.

When I check the logs or try to view the properties of the availability group it says the quorum was lost.

What am I missing?

Were you able to get this sorted out?

If not , try the following:

Go to the Failover Cluster Manager ( cluadmin.msc from Run).
Expand the Cluster name ( Expand meaning click on the + sign ).
Expand Services and Applications. You should now see your Availability Group listed there.
R-click on that and go to Properties.
In the General Tab you should see Preferred Owners.

Since you've mentioned this to be a lab environment, play around with the options ( by selecting both or alternating selection of preferred owners ) and see where that gets you. Also the tab next to General would specify if you need it to fail back. Post back with results.


Trigger in trigger or trigger before table creation I need to create trigger inside trigger as follows:

SQL> CREATE OR REPLACE TRIGGER table_account_modify AFTER CREATE ON SCHEMA
  2  DECLARE
  3  NAZWA_TABELI varchar(250);
  4  sq0 VARCHAR2 (30000);
  5
  6
  7  BEGIN
  8  select ora_dict_obj_name into NAZWA_TABELI from DUAL;
  9
 10  sq0:= 'CREATE OR REPLACE TRIGGER new_trigger2; BEFORE INSERT on ACCOUNT FOR EACH ROW; BEGIN INSERT INTO pstepien VALUES (:NEW.DN,:NEW.CONTAINERDN,:NEW.SUPERVISOR,:NEW.OWNERDN,:NEW.SELFDN,:NEW.TARGETCLASS,:NEW.ERACCOUNTCOMPLIANCE,:NEW.ERACCOUNTSTATUS,:NEW.ERLASTACCESSDATE,:NEW.ERPARENT,:NEW.ERSERVICE,:NEW.ERUID); END;';
 11
 12
 13  IF NAZWA_TABELI = 'ACCOUNT' THEN
 14  execute immediate sq0;
 15  END IF;
 16  END table_account_modify;
 17  /

Trigger created.

SQL> create table ACCOUNT (
2    DN VARCHAR2(255),
3    CONTAINERDN VARCHAR2(255),
4    SUPERVISOR VARCHAR2(255),
5    OWNERDN VARCHAR2(255),
6    SELFDN VARCHAR2(255),
7    TARGETCLASS VARCHAR2(255),
8    ERACCOUNTCOMPLIANCE VARCHAR2(255),
9    ERACCOUNTSTATUS VARCHAR2(255),
10    ERLASTACCESSDATE VARCHAR2(255),
11    ERPARENT VARCHAR2(255),
12    ERSERVICE VARCHAR2(255),
13    ERUID VARCHAR2(255)
14  );
create table ACCOUNT (
*
ERROR at line 1:
 ORA-00604: error occurred at recursive SQL level 1
ORA-00911: invalid character
ORA-06512: at line 13

SQL>

After I created the trigger, I created a table to see if it works and I got that error.
Could you please advice me what the problem could be and how to proceed here?

There are several problems here.

The syntax of your dynamic create trigger statement is invalid.
The select from dual and definition of NAZWA_TABELI for ora_dict_obj_name is unnecessary.
The outer trigger will fire for every table, though it only needs to for one.
The dynamic SQL could be a constant but is not.
An early return could be used but is not.
The If statement could encompass the sq0 definition, but does not.
The insert will break if a new column nullable column is added to pstepien.
You are creating a trigger to do what normal code should do.
You are creating a trigger inside a trigger.

I recommend you create your table and in the package where you have the statement:

INSERT INTO Account...

Add the other insert right beside it.

INSERT INTO Account...
INSERT INTO Pstepien...


How to resolve the master server shut down/unavailability in mysql with master - slave replication Let us suppose that we have a master/slave setup and the application is running fine for the time being. All of a sudden the master server suddenly shuts down due to some problem.

Now how can I address this situation? What am I thinking is that:

stop the slave on slave
get the show master status on slave.
get the additional trasactions that were not replicated from the masters binary log
execute them on the slave
point the master to the applicatin and start as master.
recover the original master and make this as slave.

Is there anything else that adds more consistency?

Before you perform any mysqldump to fully restore a Slave, you should consult the output of SHOW SLAVE STATUS\G. Let's start with a sample SHOW SLAVE STATUS\G:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Please notice that there are two sets of replication coordinates from the Master

(Master_Log_File,Read_Master_Log_Pos)
(Relay_Master_Log_File,Exec_Master_Log_Pos)

There is a major difference between them

(Master_Log_File,Read_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs.
(Relay_Master_Log_File,Exec_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs THAT IS NEXT TO BE EXECUTED ON THE SLAVE.

The timestamps from these two coordinates helps you figure out Seconds_Behind_Master.

Knowing these things, here is what you can do:

Step 01) Run SHOW SLAVE STATUS\G
Step 02) Get Relay_Master_Log_File,Exec_Master_Log_Pos from SHOW SLAVE STATUS\G (In the sample, that would be (mysql-bin.000254,858190247)
Step 03) STOP SLAVE;
Step 04) CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;
Step 05) START SLAVE;
Step 06) Wait 10 seconds
Step 07) Run SHOW SLAVE STATUS\G and check Seconds_Behind_Master

If the Seconds_Behind_Master is a number and eventaully drops to zero, replication is fully reesatablished.

After doing all this, if replication breaks because of a corrupt binary log from the master, then you do the last resort:

Steo 01) On the Master, RESET MASTER; to erase all binary logs and start with a new one
Step 02) On the Master, run this

This create proper dump for the slave

echo "STOP SLAVE;" > /root/MySQLData.sql
mysqldump --all-databases --routines --triggers --flush-privileges --master-data=1 >> /root/MySQLData.sql
echo "START SLAVE;" >> /root/MySQLData.sql

Step 03) scp /root/MySQLData.sql over to the Slave and Load it in MySQL on the Slave

Give it a Try !!!


Is it possible to create a function in SQL server to act as clause e.g. Where or Limit I've found a simple way to mimic the limit clause is SQL server by using

SELECT * FROM customers
OFFSET 22
ROWS FETCH NEXT 5 ROWS ONLY

I'd like to replace those two line with

LIMIT(22, 5)
e.g.

SELECT * FROM customers
LIMIT(22, 5)

No, there are no macros in T-SQL to make that syntax look simpler (and non-standard!) or to perform inline substitution, at least in a native non-dynamic query. You do know that you can parameterize it, though, right?

ORDER BY <need an ORDER BY>
  OFFSET ((@PageNumber-1)*@PageSize) ROWS
  FETCH NEXT (@PageSize) ROWS ONLY;

This way you are always adding the OFFSET clause but you can do it in a very standard way and not really have to look at it during everyday coding.

I suppose you could do what you want if you're okay with building your query using dynamic SQL, for example you could create a function like this:

CREATE FUNCTION dbo.Limit(@pn VARCHAR(12), @ps VARCHAR(12))
RETURNS NVARCHAR(255)
WITH SCHEMABINDING
AS
BEGIN
  RETURN (N' OFFSET ' + @pn + ' ROWS FETCH NEXT ' + @ps + ' ROWS ONLY');
END

Then you could call it like this:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... ORDER BY ...' + dbo.Limit(22,5);

PRINT @sql;

This looks kind of like what you typed in your sample, but I don't think it's what you really meant (or should want), unless you already have other reasons (e.g. wildly varying order by parameters or search filters) to use dynamic SQL anyway... still, this obfuscation and simplification doesn't help anybody learn SQL Server-specific syntax, and makes it harder for those people to write this stuff when they can't use the function. I don't think this is the type of "optimization" you should be spending a whole lot of effort on. You type these queries once, and when someone does learn the actual syntax, it is hard to justify using "shortcuts" that might not be any easier to remember in the long run (and can be harder to maintain if, for example, they need to made more flexible later).


Log File Management - Space on Disk At our organisation we have several non-Production environments where Developers run free and wild with TSQL code and databases. The DBA team do not normally monitor or maintain them. Recently several developers using one server have had to write procedures that throw around mountains of code such that their log files are growing to 10-20GB (on DBs approx 15-40GB) until they run out of space on the drive volume we have provided for the log files. The databases are all in Simple recovery mode and backups are (almost) never taken.

As a bandaid I've created a SQL Agent Job that can be run by anyone to shrink all user log files on the server.

What are some valid Log File Management strategies that might be used?

For example it is my understanding that as the users generally do a blitz of intensive work that checkpoints are probably being throttled back and that thus issuing manual checkpoints would see no advantage. Is that the case or should we in fact investigate adding manual checkpoints to their code?

And just to be clear it is log file space on disk we are interested in not space within the log file.

Since your databases are in simple recovery mode checkpoint will be taken care of by database engine either automatically or when log file grow 70 % of its size, you can also give manual checkpoint . But still a long running transaction can hold log hostage and would not allow it to truncate even if you manually give checkpoint.

Log backup would not be possible in your case since recovery model is simple.

You can use Bulk logged recovery model to take benefit of bulk logged features supported.But non minimally logged transactions will be fully logged in bulk logged recovery model so be careful. Since you dont care about point in time recovery you can switch recovery model between full and bulk logged as required.

http://technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

Laslty if your team is doing huge DML which generates lot of logs you would ask them to break there DML in chunks to avoid filling of logs. Index rebuild of huge table also generates massive logs.

Use truncate table to remove records from table( if you want to remove complete record) as it is mildly logged as compared to delete but dont use if table has Foreign key constraints and Indentity.
http://msdn.microsoft.com/en-us/library/ms177570.aspx

Above points will surely give you some relief with huge logging.

Hope this helps


mysql stops working after import I've been running into problems whenever I'm importing a .sql file into my database running locally. After the import, no matter how large the DB was (last one was only 800kb), I'm signed out of phpmyadmin and MySQL stops working. The only way I know of to resolve this at this moment is to run the install process again for MySQL. After that everything is working fine again but it is driving me crazy. Especially if I have to do this multiple times each day. Anyone who know what's going on and how to prevent this?

To be more exact, this is what I get from applications running this database:

  MySQL server has gone away

try this:

[mysqld]  # THIS LINE ALREADY EXISTS – DO NOT ADD  THIS. Just ADD the line below.

max_allowed_packet=500M

and restart the MySQL Server.


MongoDB with Sharding has slow performance I have the following setup for my MongoDB Sharded Cluster (everything is running on the same machine on localhost, just different port numbers):

ConfigServers : 1 ReplicaSet consisting of 3 mongod instances
Router : 1 mongos instance
Shard : 1 ReplicaSet consisting of 3 mongod instances

I have logged into the Router and executed the addShard command:

sh.addShard("shard_rs0/localhost:27022,localhost:27023,localhost:27024");

I have not added any shard keys or enabled shardding via the "sh.enableSharding" command. I also don't have any additional shards as I want to just test using the single shard on it's own and see if the unsharded collections maintain the expected write/read speeds.

I have a .NET Web API that connects to the mongos (i.e. Router) via the CSharpDriver.

The issue I am having is that when connecting to a ReplicatSet directly, I get decent read/write speeds but when I connect via the Router, I see a significant decrease in speed. Is this because I haven't supplied a ShardKey yet or perhaps that I only have 1 shard? I would expect the unsharded collections in the Primary Shard to be unaffected by the Cluster setup but it seems there are indeed overhead costs that are slowing down the queries?

Tests Results when adding 100 000 records (using the InsertOne method on the CSharpDriver for each records). I know I could have done a batch insert but I decided to stick with the InsertOne. I did this 7 times, each time adding 100 000 records to an unsharded collection, firstly directly to the Shard itself and then via the Router. When writing via the Router, I saw the following degradation in write speeds when compared to writing to the Shard directly:

- Writing is 30% slower
- Writing is 39% slower
- Writing is 38% slower
- Writing is 42% slower
- Writing is 36% slower
- Writing is 40% slower
- Writing is 36% slower

First of all I heavily doubt that you config servers build a replica set. By default, config servers are three single instances, which is important to find metadata deviations. If you set up a replica set manually, this would be a single config server. And tbh, I am not even sure wether a mongos would actually switch to a secondary in a failover situation if given a replica set.

Now for the performance gain. All you mongods (at least your shard) run on localhost. And you only have a single shard. Sharding as a load distribution tool only works when the load is distributed across multiple machines. In your setup, your various mongods (6, if I am not mistaken) will battle for resources. Depending on your write concern, for example, the two secondaries may battle for disk IO when syncing data from the primary. If you have WT as your storage engine, the situation gets even more severe, as WT alone will eat up to 50% of your RAM...

I am not sure what you are trying to achieve, but if it is for testing a sharded setup, your test plan is very improvable.


Database - Remove Everything After I am pretty new to modifying databases (running phpmyadmin for WooCommerce) and I am currently stuck at a problem.

Basically I have around 400 items which have random SKUs (example).

JCB-5018057409641_888
OX TOOLS-5060242330476_870
REFINA-281314_856

I need to mass remove for everything going from the underscore and the 3 numbers.

I think I could possibly use Sublime or Notepad++ to achieve this but looking through many queries I can't seem to do it right.

If the column never has an underscore other than the one you want to remove, you can use SUBSTRING_INDEX().

update yourtable set yourcolumn = substring_index(yourcolumn,'_',1);


Export Wizard never starts Pre-executing I'm using the Import/Export Wizard in SSMS 2012 to store some data into a flat file, as I have to move these data from one SQL server to another. I've tried with direct connecting to the server through the export wizard but it fails after som time when the export begins due to some connection-timeout, therefor I stick to the flat-file method. Even if the direct-connection method is really nice.
I've been using the flat-file method with an row-count of ~92+ million rows, but now I'm reaching 1.1 billion (calculated) and the Executing and Pre-Executing phase in the Export wizard doesn't seem to cooperate at all. I've started to export 3 hours ago, and it's still looking as you see on the picture below. Any Idea?

I have seen similar waiting-time for the Pre-execute and executing phase to get done, where it started after 10-20 minutes, and the rows started to count in the pre-execute phase, but 3 hours, I've never tried that before.

From your issue, I can see that your choice of tool is a problem.

Below are 2 options that you can use :

Backup on the source server with COMPRESSION and Restore it on the destination server. Make sure you have Instant file initialization enabled. Once Restore is done, you can delete unwanted objects (if you want).
Use BCP OUT in native mode on source server, copy the file to destination (you may zip the file as well) and BULK INSERT into destination table. Make sure to create the table on destination database. I have written this script that will help you.


How does an alter work? I have a table with a lot of records (more than 15M) and i am executing an alter query to add 2 columns to the table.
the query is taking around 1hr to finish executing.
So my question is: How does an alter work? and why is it affected by the number of records?
is it possible to speed up an alter query using column indexes?

An ALTER TABLE operation that adds a column to a table should be a simple data dictionary only update.  There is no reason for Oracle to visit all the blocks/rows in the table.  So, the operation should be very fast.  (sub-second response time.)

The only exception to this would be, if you're on a version of Oracle prior to 11g, and you're setting a default value for the column being added, Oracle has to visit all the rows.  In 11g, even setting a default value will not precipitate a visit to all the rows in the table.


how important are mysql's innodb logs? I'm basically concerned about the following two settings, these are my default settings :

innodb_log_buffer_size  1048576
innodb_log_file_size    5242880

I haven't changed them for 2 reasons : one, I don't know what they are, and two changing this is risky, according to articales I've read

My application data-structure is write-intensive and I use only innodb tables. Currently I've set innodb_buffer_pool_size to 5GB and my database size is around 7GB and increases at the rate of a 400MB per day. Also note, this 400MB insert is done within a period of only about 30 minutes and some might refer to this as "bulk insert"

As far as i understand, log files are for keeping a record of all the insert/update operations, why do I need this?

What are these log files and why should I increase their size?

And lastly, if I don not want to keep a log, how should I fine tune my database then?

innodb_log_file_size determines the size of the redo log. In short, this is a temporary storage zone on disk where data changes are buffered before being inserted in the actual table data files. It also stores temporary data that may be generated during the course of a transaction.

The first, most obvious advantage of this buffer is that it mitigates the problem of random writes, which is what would happen otherwise in a typical real-life situation. Writes in the redo log are done sequentially, which is quicker. Randomly located changes may be committed to the actual data tables at a later time, when, hopefully, the activity decreases.

The second advantage is it provides protection against data corruption in case of crash. Since a coherent version of the data (almost) always exists, either in the data table, or in the redo log, the risk of data corruption is lowered, since it should alwys be possible to restore the database to a coherent state. Other mechanisms exist on top of this one for the same purpose (eg. double-buffering)

As the manual puts it:

  Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. (...) The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower.

innodb_log_buffer_size defines the size of the memory buffer where data is stored before being flushed to the redo log.

The larger the better, but make sure to leave enough memory for other buffers. Also, I do not think there is a point in allocating more than the total redo log size (ie. innodb_log_file_size x innodb_log_files_in_group.

Books the size of a dictionary can be written about database tuning, this cannot be addressed here. This manual page is a good starting point. If you have identified a specific bottleneck, please come back with relevant information and I (we) will be happy to help you.


Select row from one query where opposites are not present in second query Hopefully this explanation will clear up what the question is asking for ...

I have 2 tables (I'll populate them a little too):

inbox

---------------|-----------------
| to_USER_id   |   MESSAGE_id   |
---------------|-----------------
|       2      |         1      |
|       3      |         2      |
|       4      |         3      |
---------------------------------

message

---------------|----------------------------------
|     id       |  from_USER_id   |    content    |
--------------------------------------------------
|      1       |        4        |      Hey      |
|      2       |        1        |     Hello     |
|      3       |        2        |   How's it..  |
--------------------------------------------------

Basically, these are the tables that hold data when messages are 'sent' from one user to another. I need to work out a query (MySQL) that will return the message.id, which does not have an inbox.to_USER_id the same as the  message.from_USER_id (Basically, which message has not been replied to)

So, as you may be able to see, user 4 messages user 2. Then user 1 messages user 3. Then user 2 replies to user 4.

How do I query these tables to only return the message.id which has not yet had a reply. So, in this case it will return the row where a message was sent from user 1, which has not got a reply from user 3.

It's quite hard to explain ... But hopefully someone understands

SELECT  m.id
    FROM  message AS m
    WHERE  NOT EXISTS
      ( SELECT  *
            FROM  inbox AS i
            WHERE  i.message_id = m.id
              AND  i.to_user_id = m.from_user_id
      )

I think it could also be cone with a LEFT JOIN (with similar efficiency), but NOT EXISTS more closely matches your description of the task.


Writing SQL Query Results to Event Log --- PowerShell + SQL Server 2012 I have a PowerShell script that queries a database and returns two columns. Depending on when this script is run, the query can return no rows, one row, or multiple rows. With these two columns, I write the output to the event log.

Here is the part that writes to event log:

    if($missing) {
    # foreach easch row in missing, write to the event viewer eventlog

    ForEach ($row in $missing) {

        $serNames = $row.ServerName
        $names =  $row.name

        $output =  "Following backups missing for longer than threshold:   '$serNames' - '$names'"

        Write-EventLog -ComputerName DB0000 -LogName Application -Source $source -EntryType Error -EventID 9901 -Message $output
        }
    }
    else {
        continue
    }

My question is... Is there a way to have the result table be written as my output to the event log? Currently with my foreach loop, operational DBAs are getting spammed with emails so I would like to have the table written to event viewer as opposed to a foreach writing separate event logs. I tried removing the for each and playing with formatting but I have had no luck.

EDIT: Sorry I should specify further... Currently each row is separately written to the event viewer and SCOM picks these up and sends via email an alert. Would like the full result set of server/db pairs emailed in one single alert. Thank you.

Add a FOR XML (or JSON, if that's your thing) clause to the query. This will output all columns and rows in a single string, which can be written to the event log. Be aware of the size so you don't lose information.

If changing the query isn't possible, Powershell has a full set of XML features. Loop through the results and write one event. You can detect the risk of xml truncation in the event store and write further events to avoid loss.


Cannot connect to SQL Server from Management Studio (both 2012) I installed SQL Server 2012 Express. It seems to be running fine. My SQL Server Configuration Manager looks like this:

From what I understand it means that my instance is called MSSQLSERVER.

Now in Management Studio when I try to log in it can't find the instance. I've tried .\MSSQLSERVER but it doesn't work:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (Microsoft SQL Server, Error: 87)

I don't understand what I'm doing wrong. Why can't I connect to the server?

With a default instance,  specify only the host name when connecting.  As a shorthand when connecting locally, specify only ..