How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)? Struggling to execute PowerShell commands in SqlServer Agent job of MS SQL Server 2012 R2 (Windows Server 2008R2, Windows 7 Prof)

I need to delete (.bak files older, than, say, 7 days in remote file share.

Before inserting the command into SqlServerAgent job, I tried to execute it from Windows command line:

using forfiles :
  forfiles /P "\\RemoteHostName\sharedFolderName" /s /m *.bak /D -7 /C "cmd /c del @path"

gives the error:
"ERROR: UNC paths (\machine\share) are not supported"
using Windows PowerShell:

dir \\RemoteHostName\sharedFolderName |? {$_.CreationTime -lt (get-date).AddDays(-7)} | del

The latter deletes the required remote files when used from Windows PowerShell command prompt but when executed in/from an SqlServerAgent job - fails

The history of such SqlServerAgent jobs shows, quoting:

The job succeeded. The job was invoked by User domain\domainAccountName. The last step to run was step 1

Executed as user: localHostName\sqlbackup. The job script encountered the following errors. These errors did not stop script: A job step received an error at line in a PowerShellScript. The corresponding line is 'dir "\\RemoteHostName\sharedFolderName\" |? {$_.CreationTime -lt (get-date).AddDays(-7)} | del'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path '\\RemoteHostName\sharedFolderName' because it does not exist. ' Process Exit Code 0. The step succeeded.

Note the green check-marks on failed job and job steps

Again, zoomed-in the text output of the result:

Also, when executed this SqlServerAgent job step shows: "processing", then "Success"

though it is persistingly failing to delete required files.

The question(s):

How to make the job and step, containing errors,  to show "Failed" instead of of "Succeeded"?
Why does the purging of older files using Windows PowerShell commands succeedes from command line prompt and fails from within SqlServerAgent job&step?

2a. How succeed with removing of older remote files on schedule from SQL Server 2012?

PS
I am using SQL Server Agent Job proxy according to this answer using local Windows user accounts on remote/target and local/source hosts with the same/equal names sqlbackup and the passwords

The same story with scheduling in Windows Task Scheduler - the job and steps are "succeeding" without deleting anything though copying to remote location (out of description here) by previous steps are successful. And I am interested to centralize all in SQL Server SqlServerAgent job (but not outside)

In order to make PowerShell errors cause a SQL Agent step to fail you have to make an adjustment to the $erroractionpreference setting. By default this value is set to Continue so the steps will show successful because no error is raised up for SQL Server Agent to detect an issue in the actual PowerShell command occurred.

You can put this at the top of your script that should fix that:
$erroractionpreference="stop".
This will allow the error PowerShell is returning to trickle up to SQL Server Agent process.

Create a maintenance plan for deleting backups from folder having age greater than N days

Create Maintenance Cleanup Task >
Select connection type as a User having sufficient rights(sysadmin user credentials) >
Select the folder location containing  backups >
select the days or weeks till which you want the backups >
OK

Now schedule this plan on daily/weekly basis.....

T-SQL created by maintenance plan is

EXECUTE master.dbo.xp_delete_file 0,N'BACKUP FOLDER LOCATION',N'bak',N'DATE FOR N DAYS T TIME ',1

But above script doesn't work properly if executed in query window, So use Maintenance plan.......


MySQL do not accepts wildcard for tables name level grant Trying to grant a user select privilege on multiple tables using wildcards as following:
GRANT SELECT ON 'DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password';

But keep giving the following error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password'' at line 1

Server version: 5.7.30-log MySQL Community Server (GPL)
I'm sure that the syntax of the query is correct, but for some reason it won't accept it, Any advise?

The MySQL 5.7 GRANT Syntax Documentation says something interesting about wildcards
Under the subheading Object Quoting Guidelines, Paragraph 1 says:

Several objects within GRANT statements are subject to quoting, although quoting is optional in many cases: Account, database, table, column, and routine names. For example, if a user_name or host_name value in an account name is legal as an unquoted identifier, you need not quote it. However, quotation marks are necessary to specify a user_name string containing special characters (such as -), or a host_name string containing special characters or wildcard characters such as % (for example, 'test-user'@'%.com'). Quote the user name and host name separately.

Paragraphs 3 and 4 say

The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level (GRANT ... ON db_name.). This means, for example, that to use a _ character as part of a database name, specify it as _ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern (for example, GRANT ... ON foo\_bar. TO ...).
When a database name not is used to grant privileges at the database level, but as a qualifier for granting privileges to some other object such as a table or routine (for example, GRANT ... ON db_name.tbl_name), wildcard characters are treated as normal characters.

What about table privileges ? Please note the Docs on Table Privileges:

Table privileges apply to all columns in a given table. To assign
table-level privileges, use ON db_name.tbl_name syntax:
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

If you specify tbl_name rather than db_name.tbl_name, the statement
applies to tbl_name in the default database. An error occurs if there
is no default database.
The permissible priv_type values at the table level are ALTER, CREATE
VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES,
SELECT, SHOW VIEW, TRIGGER, and UPDATE.
Table-level privileges apply to base tables and views. They do not
apply to tables created with CREATE TEMPORARY TABLE, even if the table
names match. For information about TEMPORARY table privileges, see
Section 13.1.18.2, “CREATE TEMPORARY TABLE Statement”.
MySQL stores table privileges in the mysql.tables_priv system table.

As shown from the MySQL 5.7 GRANTS Documentation

Wildcards are applicable to hostname (which are bold italiczied)
Wildcards are applicable to database name (which are bold italiczied)
Nowhere do the docs specify wildcards for table names
Wildcard characters are treated as normal characters for all other database objects

The workaround given in the comment is what I used to do when giving away SELECT on views.

you need to use skip special characters \ in your command
GRANT SELECT ON 'DBname'.'foo\_%' TO 'username'@'%' IDENTIFIED BY 'password';


SQL Server 2012 error when exporting to Excel Let me start off by saying I know little and less about SQL. I received a .bak file from a client earlier today and they want me to move it to a new database (not SQL). I've created a SQL Server 2012 instance and restored the .bak file to a database and now it is .mdf file.

The last step of this process is getting it into either Excel or a .csv file (the inputs accepted by the new database system). I tried following the basic "right click and then select export and a destination" instructions, but I keep getting an error kicked back to me. I've copy/pasted the report below:

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Copying Rows (Error)
Messages
Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE `aspnet_Profile` (
`UserId` GUID,
`..." failed with the following error: "The Microsoft Jet database engine could not find the object 'aspnet_Profile'.  Make sure the object exists and that you spell its name and the path name correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 (SQL Server Import and Export Wizard)

- Post-execute (Stopped)

Seems like more often than not I run into issues trying to export to excel using the SSMS import/export wizard and I've never taken the time to learn and understand the quirks.

This doesn't necessarily answer your question, but might be a viable alternative.
Instead of exporting to excel, consider importing into excel. This YouTube video appears to demonstrate it:
http://youtu.be/VQrKg0GgZR0

Of course, if you need to export a lot of tables or need to do this often this is a rather tedious approach, but it has worked very well for me with a lot fewer headaches when only exporting a few tables.

Make sure the excel file to which you are exporting the data is not open.


MySQL cannot create foreign key, errors 1005, 150 I'm trying to add a foreign key to a MySQL InnoDB table, linking a BIGINT with a table within the same database. I'm using MySQL Workbench to do this. It generates a script:

ALTER TABLE `MyDatabase`.`MyTable`
  ADD CONSTRAINT `myNewForeignKey`
  FOREIGN KEY (`TheId` )
  REFERENCES `MyDatabase`.`MyReferencedTable` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `myNewForeignKey_idx` (`TheId` ASC) ;

When applied I get:

ERROR 1005: Can't create table 'MyDatabase.#sql-1ab6_5c3df97' (errno: 150)
ERROR: Error when running failback script. Details follow.
ERROR 1050: Table 'MyReferencedTable' already exists

I even tried removing the table (could do that as it's empty) and recreating it with foreign keys and all, but it leads to the same errors.

I've seen reports of bugs relating to this, but they are several years old and should've been fixed by now. Surely foreign keys must be possible to add. What am I doing wrong?

I confused my databases and incorrectly thought I was working with InnoDB databases. Turns out one of them was MyISAM and then foreign keys don't work. Unfortunately, MySQL Workbench doesn't inform the user of this on Windows, but it does on the Macintosh version. (I guess it's good after all to have developers on your team with Macs.)

Hope this helps somebody with the same error message.

If anyone encounters this problem in phpmyadmin, it could be due to the fact you have multiple identical constraints on a field. That can happen sometimes.

Check with SHOW CREATE TABLE  to obtain all constrains. In case you find duplicates, erase one of them.

Then, in phpMyAdmin, you will probably need to setup all constraints from beginning for that table.So, erase and re-create all FKs through phpmyadmin for that table.

And then, it should work.


Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL.
In our application, we are planning to upload customer’s data using a Excel sheet and these data will be displayed on custom build interactive dashboard.

I have few questions before I start working on this application

1) For the mass data upload from excel, which db is suitable MySQL or PostgreSQL?

2) At some point is it possible that multiple customers can start data upload at same time ?. To deal with such scenario is it advisable to create a separate db for each customer or to use a single db for application(single tenancy vs multi-tenancy). Please tell me pros and cons if any for each scenario?

3) How it would affect my dashboard performance, if I go with multiple databases design and I join tables across multiple databases?

Thanks

For a data warehousing/analytics type application (DW/OLAP), I would go with PostgreSQL. It has set operators, windowing functions (also known as analytic functions) and common table expressions. You will most likely have to implement some or all of these in your own code with MySQL with the attendant possibility of bugs. MySQL is more suited to read-heavy OLTP type applications, whereas PostgreSQL is better (IMHO) for OLAP work.

With respect to "multi-tenancy" - why not use the same database for all and simply identify different customers by a customer_id - or (depending on the number of customers) have separate tables for each customer and non-customer specific data in other tables? How many customers do you have and how much data do you expect to be storing/analysing  in the next 10 years? Modern RDBMSs can store large amounts of data and retrieve quickly (with suitable disk configuration), but I would favour PostgreSQL for large databases.

Sharding might be interesting if you only occasionally query across customers and most of your analysis is done per customer - you could set up a table (or even schema) per customer (on different disks). Again, knowledge of data volumes would help here.

Furthermore, with respect to multi-tenancy, see my question to jynus about querying across servers - or even different schemas within the same server.

I take jynus' point about "holy wars" - this post is just my own opinion about your particular question - were I to be asked about a different scenario, I might well recommend MySQL.

[EDIT]

What, exactly, is "my dashboard performance"?

1) Any modern RDBMS system will allow you to import and work with Excel-like files (usually, we use them as CSV files, for which both databases have import support: COPY and LOAD DATA).

If you had to choose, worry about in-house technology knowledge and advanced features, but nothing here suggest any impossible tasks. Please be wary of people promoting holy wars among database vendors.

2 and 3) All modern RDBMS allow for transactional, concurrent writes of different rows in a single table. Your design concerns should be directed about estimated write load/data size and its implications on performance, like -Do you need partitioning/sharding? Those questions are usually asked when you have to deal with huge numbers. Make sure you are not falling into the premature optimization anti-pattern.

There is a difference regarding joins, as schemas/databases on MySQL are mostly a logical categorization, and there is effectively no difference in performance between joing tables in the same or different databases inside the same instance. In Postgres, while it is tecnically possible (dblink, etc.), it is not an optimal action by design -different databases are "different entities"- obviously you do not put tables that you plan to join on different databases knowing that (the same way that you do not put them on different instances).


Low Cardinality of Index performing better than High Cardinality? For testing purposes I have 2 Tables TableA and TableB in an mysql-InnoDB, both with the same columns

id bigint not null auto_increment,
depot smallint not null,
custno bigint not null,
range_from bigint not null,
range_to bigint not null,
depot2 smallint not null,
primary key (id)

and the same data (~5M rows). Now TableA has the index

unique index (range_from, range_to, custno, depot)

and TableB has the index

unique index (depot, range_from, range_to, custno)

The cardinality of "depot" is very low (~100 different values), the cardinality of "custno" is medium, and the cardinality of range_from and range_to is very high.

The Select

select sql_no_cache custno,depot,range_from,range_to
from TableB
where $value between range_from
                 and range_to
  and depot=$other;

takes about .8 seconds.
Explain says:

+----+-------------+-----------+-------+---------------+------------+---------+------+---------+--------------------------+
| id | select_type | table     | type  | possible_keys | key        | key_len | ref  | rows    | Extra                    |
+----+-------------+-----------+-------+---------------+------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | TableB    | range | range_from    | range_from | 8       | NULL | 1992762 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------------+---------+------+---------+--------------------------+

Now the same select on TableA takes .03 sec and here is what explain says:

+----+-------------+----------+-------+---------------+-------+---------+------+--------+--------------------------+
| id | select_type | table    | type  | possible_keys | key   | key_len | ref  | rows   | Extra                    |
+----+-------------+----------+-------+---------------+-------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | TableA   | range | depot         | depot | 10      | NULL | 107736 | Using where; Using index |
+----+-------------+----------+-------+---------------+-------+---------+------+--------+--------------------------+

Even though the Index starts with a low cardinality column the select outperforms the other index by a factor of at least 20.
Can someone please explain this to me like i am 4 years old? Every article I read so far advocated for using the high cardinality values first in indices.

Mysql version is 5.5.32 btw.

The between condition is short form for:

 ... range_from <= $value
 and $value <= range_to

In this context range_from and range_to have an effective cardinality of 2, in range or out of range.

Given your indexes the range_from needs to be searched for all values up to and including $value.  If value is close to the end the possible values, the number of records that need to be checked is quite high.  If you are looking for recent records, this is will be close to all the records.

Using the index with the depot column first, far fewer records match the range_from value.  If you have 100 depots, you have 1% of the records to check compared to not using it.  Depots with few records will be faster than those with more records.  Your results on table A will vary depending on the value of $other.

In either case, the query could be completed within the index.  Without depot in the index, the query optimizer might decide to scan the table rather than the index.  Some SQL dialects allow a hint to the optimizer indicating the number of rows the query will match.  This can influence the resulting query plan.

While you consider the cardinality low, from an optimization standpoint, it is quite high it reduces the number of index rows to be scanned by a factor of 100.

While the cardinality of the from_range may be high from a values standpoint. When running a range search it is of little value in searching for records.  For high values (which may be the most common case), it will match close to 100% of the records.  Its high cardinality, is of more use when running a range check of the form:

where from_range between $low and $high

The index may be more useful if the range columns are ordered range_to, range_from.

The $depot is the first column, it is used to find all rows that match the $depot = condition, then the range arguments are applied. This cannot happen on the high cardinality index since $depot would be evaluated after the first 2 columns. This is a situation where Search Argument (SARG) and Cardinality collide. The data usage is what is the determining factor here. If the query that uses $depot = is the mosted executed, and by far, the most urgent one to use, factor that into which index to keep.


suddedenly increased in size of Log files form 32GB to 70 GB I am not DBA ,but I need to check on the size of database files .
my database log files earlier was around 32 GB but from last few days it starts increasing and finally reached to 70 GB . first I thought it was natural , but soon I realized  that I was wrong  .

For shrinking the size of log file ,firstly I used to determine the % of space occupied in log file  by using .

        DBCC SQLPERF(logspace)

Result was :- log size(MB) > 70 and Log Space Used > 94%
 with

          DBCC LOGINFO

I found more than 700 transactions having Status = '2'

After that I took "log as well as full backup"
and run above query again but still got >700  transactions having Status = '2'
detached and reattached , but still no difference in size .

Then I went for Shrinking log files and for testing purpose I gave the size 60GB .
 Then run following query again

   DBCC SQLPERF(logspace)

and result was :- log size(MB) > 66 and Log Space Used =  99.1258%.

Question is : How can I bring the size of log files near to earlier size i.e. around 32 GB or near by and how to keep check on it so that its size does not change dramatically .

** Recovery Mode :- FULL

Thanks in advance ....

A full database backup won't help your log size.

Full Recovery Model means "Can restore to any point in time." - including before that new database backup was taken.

Simple Recovery Model means "Can restore to the last database backup".

So... have a think about whether you need to restore to this morning or not (before your database backup). If you don't, then put your database into Simple Recovery Model, then back into Full. Then take a differential backup. Once you've done those three steps you will be able to recover to your last full database backup, the differential backup you just took, and any point in the future. Take regular log backups - try every ten-to-fifteen minutes.

And then look at your log. You should find it's mostly empty.

If not, look in sys.databases to see why it's not truncating. Other possible reasons include Replication that's broken, for example.

You can't shrink a full log, shrinking will only reclaim empty space and even then you should be very liberal in how you use the shrink feature.

The first thing I'd recommend checking is that your database is having transactional log backups taken as well as full backups. Full backups won't clear the transactional log. These allow point in time backups and the typical restore process will be the last full backup and then all subsequent transactional log backups up until the point you wish to restore to. You can also take differential backups which effectively group all database changes into one backup file so you don't need to restore all previous trans log backups but don't worry too much about that for now.

Of course if you don't need point in time recovery and a daily (or however regular your full backups are) backup is sufficient then you can just change your database recovery model to simple.

The other area to consider is if you use some form of replication to copy your data elsewhere for HA/DR purposes. This will also have to be copied across before a truncation of your trans log can take place. Therefore if you have for example Always On Availability Groups setup but they aren't replicating correctly then this will cause your log to fill.

Take a look through your configuration for the above and if you get stuck come back here.


Where can I find the SQL-89 standard? This question is different but similar to this request looking for SQL-86.

SQL 1989 is almost 30 years old. Is this spec freely available anywhere. The standard does not have a Wikipage or the like?

The spec goes by

ANSI X3.135-1989 Database Language - SQL With Integrity Enhancements
ISO 9075-1989
SQL-1989

The need for it came up while researching the question, Does the SQL-1992 standard restrict naming identifiers to 18 characters?

This one looks like the document you are after (although French, not English, and not free)
https://iteh.it/catalog/standards/iso/7c7fa915-f99f-4ce9-8aa4-0e896d89ed84/iso-iec-9075-1989
Or this one seems to have English and Russian versions:
https://gostperevod.com/st-iso-iec-9075-1989.html

This what you are looking for?

Information Systems - Database Language - SQL (includes ANSI X3.168-1989) (formerly ANSI X3.135-1992 (R1998))
Defines the data structures and basic operations on SQL-data. Provides functional capabilities for creating, accessing, maintaining, controlling, and protecting SQL-data.


Optimize SSIS package for fewer queries I need to associate customers' order with their "level" (Silver, Gold, etc.) when they placed the order:

CRM server::CRM db::CRM table
----------
CustomerID   PreviousLevel    NewLevel    NewLevelGrantedOn

Order server::Order db::Order table
----------
OrderID     CustomerID    OrderPlacedOn

In an SSIS package I did this:

Execute SQL against Order db to extract the orders and put them in an object variable;
Loop through each order using "foreach container", in which I put a data flow task select top 1 * where CustomerID = ? and LevelGrantedOn < ? order by LevelGrantedOn desc that extract data from CRM db (both parameters come from step one), derive some columns and write the output to another table.

There are more then twenty thousand records in the order db, which means the data flow task will be executed for more than twenty thousand times. The CRM db will be queried for more than twenty thousand times, too. It takes more than an hour to do these.

Can I utilize some built-in features to speed up these (or do it in a "smart" way)? And, is an hour a long time, in the context of ETL and / or SSIS?

You can achieve this without using Foreach enumerator, just use one data flow task to achieve this.

Building the Package

First of all, add a Data Flow Task to the control Flow

1. OLEDB Sources

In the DataFlow task add an OLEDB Source that Read from Orders Table (the same command used in the Execute SQL Task (first step in your question)

Select * FROM Order

Also add a second OLEDB Source that read from customer table:

select * FROM CRM

2. Sort

After each OLEDB Source add a source component:

The First (order table) you have to select CustomerID and OrderID columns for sorting and select the sort type as ascending
The Second (customer table) you have to select CustomerID (sort type
= ascending) and LevelGrantedOn (sort type
= descending) columns for sorting

3. Merge Join

Add a merge Join component to join both sorted outputs. and select CustomerID column from both outputs as Join Key and select the output columns you need from both tables

4. Conditional Split

After merge Join add a conditional split to filter rows only that match the following expression

[LevelGrantedOn] < [orderdate]

5. Script Component

The final component we have to use is a script component to get only the first row for each customerID (because both source are well sorted just grabbing the first row is similar for Select top 1 ... ORDER BY LevelGrantedOn desc)

In the script component add an Output Column OutFlag of type DT_BOOL and Use the following script:

This script will set OutFlag to True when the customerID occur for the first time (similar to TOP 1)

    Public Class ScriptMain
        Inherits UserComponent

        Dim lstCustomerID As New System.Collections.Generic.List(Of Integer)
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

            If lstCustomerID.Contains(Row.ID) Then

                Row.OutFlag = False
            Else

                lstCustomerID.Add(Row.ID)
                Row.OutFlag = True

            End If

        End Sub

    End Class

6. Conditional Split

Add a second Conditional Split to filter Top 1 rows:

[OutFlag] == True

7.OLEDB Destination

Connect the Conditional Split Output top the OLEDB Destination and map the columns.

The Dataflow task must looks like

Consider using SQL Server "Index with included columns" feature which can speed up the query performance during the data collection process.


Auditing AS 400 DB2 library user access As part of the testing to check for authorized users having access to applications and libraries on AS 400 systems, I need help to figure out a list of users having access to applications hosted on the AS 400 systems, need to understand is there way to list down users grouped to access specific libraries for certain applications and all users given access to and revoked access and also the privileges assigned to each users, over a period of time for the testing.

This depends on the specific application. Most AS400 DB2 applications have initial  Application Administration which configures privileges assigned to each application user. The administration role is normally a DBA or programming staff responsibility. To extract a list of Groups and/or users accessing specific functions speak to the DBA or Application Administrator.


Repairing buggy query server-side – without changes in the app More external apps are accessing our ERP SW database. As we were adding some columns into our structure, some queries from the external app become buggy because they are not specifying the targeted table in the query and errors out with Ambiguous column name… message.
The app vendor is inaccesible. We cannot repair the query from the app point nor change our structure (it’s for more customers).
Can we do something at the database server level?

You are facing the error Ambiguous column name… because the app is quering your database this way:
SELECT
[Name],
[Salary],
[Name],
[Age]
FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.EmployeeID = B.EmployeeID

As you can see there are two [Name] in the SELECT. You will resolve the issue by recreating the query this way:
SELECT
A.[Name],
[Salary],
B.[Name],
[Age]
FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.EmployeeID = B.EmployeeID

Yes, you are facing this because, as you said, you are adding some columns in your DB. You have 2 choices:

Contact the vendor and make it aware of the Ambiguous column name… error
Recreate the structure of your database and avoid using column names that were already used.


Refresh postgres database on nightly basis I've got a Postgres 9.2 database (production) which is almost 90 GB, I need to restore production database to development postgres server every day, what I think is to schedule a backup an restore script as cron job on linux, but the problem is it won't work in long term, as the database is growing too fast, so base backup and restore every night would take longer and longer as the size of database is increasing.

Is there a better way to refresh the development database on daily basis? for example using log shipping or something which only update the development database with delta changes.

PS: for some security reason, I can't use replication during business hours.

If you're ok with the development db being read only you could use replication using wal shipping. Instead of continuously shipping, do it on a daily basis.

See: 25.2. Log-Shipping Standby Servers

If you need it in write mode it makes things a bit more complex as you'll need to keep the prod copy in read and clone it.
If your storage subsystem support snapshots than it's trivial.

Btw, 90gb should clone pretty fast - how long does it currently take? What disks are you using?

90 gb, sounds like the db design is not 3rd normal form relational, or has a lot of blobs which might be better off as files, if you've got a growing db problem like this your bank account must be also growing, maybe invest in some design optimisations.

To mitigate short term you should try dumps by table, one at a time, spread out cron jobs by table by the time necessary.


INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query I've come across people recommending using ON DUPLICATE KEY UPDATE to UPDATE multiple rows using a single query. Unfortunately no one seems to be using clean SQL (defining an obvious primary key of id and using at least two rows) and so I have not been able to confirm the effect to a satisfactory degree.

Presume all the data in the table exists and that some of the quantity rows will be different:

INSERT INTO variations (id, name, quantity)
VALUES
(1, 'John', 4),
(2, 'Amy', 5),
(3, 'Elizabeth', 6)
ON DUPLICATE KEY UPDATE
id=VALUES(id),
name=VALUES(name),
quantity=VALUES(quantity);

If the quantities in the table are 1, 2 and 3 then what I've constituted is that the above query should UPDATE them to 4, 5 and 6. However manually running the command returns Affected rows: 0 and the data in my application is beyond any doubt not changing.

Clarifications:

The name rows may have their values change, I'm trying to keep the code simple.
The id is the primary key and there are no other keys.
No UPDATE has to happen if the all of the data for all of the row are the same between the query and table row.
Sometimes only one column of data might change, sometimes all columns except id might change.
There is no clarification in regards to VALUES() if it is using the $row (e.g. from PHP) data to UPDATE if it differs from what is already in the row.
There is no clarification in regards to if all the columns must be specified (besides the the primary or a unique key).
The documentation at both MariaDB and MySQL leaves a nebulous mystical cloud of unknowns.
I'm running MariaDB 10.2.6 (MySQL 5.5.5).

I'm tempted just to dump the code in to a loop to run an UPDATE query per row as I don't have time to create a huge project out of this. None of the existing threads here or at SO properly address the multiple UPDATE single query issue (examples of using case look like monstrous nightmares to construct) and so I'm hoping for an answer that can clarify this beyond any doubt so I can learn from this frustrating challenge.

Thanks to #danblack and dbfiddle.uk in short I determined that I was missing one of the columns in the ON DUPLICATE KEY UPDATE.

Every single column must be defined for ON DUPLICATE KEY UPDATE to work!

I effective had columns id, a, b, c and d in the table. I forgot to define one of the columns (that wasn't id as that is the first half of the requirement).

Won't Work (missing column):

ON DUPLICATE KEY UPDATE
id=VALUES(id),
a=VALUES(a),
c=VALUES(c),
d=VALUES(d);

Will Work (has all table columns):

ON DUPLICATE KEY UPDATE
id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c),
d=VALUES(d);


MySQL 5.5.58 Installing Multiple Instances on RHEL7 I am Avinash.

I am willing to install an additional MySQL instance on a UAT server for testing purposes. I am using MySQL Community Server 5.5.58 installed on RHEL7.2.

I am going through the MySQL Docs https://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html , which tells me that I would require certain unique operating parameters such port, socket, pid, datadir and log files related parameters. To achieve better performance, it also recommends to use different tmpdir. This is well understood.

I can either use different binaries or the same binary for this purpose.

If I am using same binary, I'll have to use mysqld_multi and use the same config file. If I use mysqld_multi, I'll have to put different sections for mysqld daemon such as mysqld1, mysqld2 and so on. All using unique values for above mentioned operating parameters.

Please help me out here. Am I correct about mysqld_multi?

My second question is about bind-address variable.
https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address
Here, it explains that "The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces."

My question is that will I need an additional NIC and an additional IP address for the second MySQL instance?

For the existing instance, I have disabled the bind-address option.
Do I need to add the bind-addresses for both the instances.

Also, the second instance will be used only by me (for now).

Thanking you.

why dnt you start new instance with different cnf file (keeping diffrent PORT and diffrent socket file)

for example :

cd /opt/mysql_installation/u01/mysql_5.7.17/bin/
./bin/mysqld_safe --defaults-file=/opt/mysql_installation/my.cnf &


What are the prime and non-prime attributes in this relation? R(HIJKLMNO)

L -> MNO
HI -> JKLMNO
J -> KL
K -> H

HI is a key.

So, H is a prime attribute.

But, H is also in non-key part.

What are the prime and non-prime attributes in this relation?

Your schema has three (candidate) keys:

HI
IJ
IK

While it is immediate to discover tha HI is a candidate key since it determines all the other attributes, you can see that this is true also for IJ and IK by calculating the closure of those attributes:

IJ+ = IJ
IJ+ = IJKL     (by adding the right part of J → KL)
IJ+ = IJKLH    (by adding the rigth part of K → H)
IJ+ = IJKLHMNO (by adding the right part of L → MNO)

analogously for IK:

IK+ = IK
IK+ = IKH      (by adding the rigth part of K → H)
IK+ = IKHJLMNO (by adding the right part of HI → JKLMNO)

For this reason, the prime attributes of the relation are:

HIJK

while the non prime attributes are:

LMNO

Note that there are no other candidate keys since M, N and O appear only on the right side of functional dependencies, so that they cannot “contribute” to any key. L appears both on a left part and on a right part of functional dependencies, but it is determined by HI, IJ and IK, and does not determine any of these attributes, so it cannot be part of a key. Finally, you cannot remove any attribute from HI, IJ and IK without losing the key property.

You are trying to find out possible candidate keys. HI is the only possible candidate key or prime attribute since it independently identifies all other attributes or fields due to the functional dependency HI -> JKLMNO.

All the other attributes are non-prime attributes. Again, here HI is a composite candidate key and both H and I are key attributes together forming the primary key.

All other attributes are dependent on HI together, else it will result in partial function dependency (PFD) or, in other words, the criteria for 2nd Normal Form won't be satisfied.


Using system tables to identify the columns of a table index in Sybase Is there a means of identifying the columns of table index using systematically populated system tables such as: syscolumns, sysindexes, sysobjects, etc.

I have tried to use syskeys unfortunately this table is only populated by sp_primarykey.

So far I have the following query:

SELECT
    i.name,
    o.name
FROM
    sysobjects o
    INNER JOIN sysindexes i on i.id = o.id
WHERE
    o.type = 'U'
    and o.name = 'XXXXX'
      and i.indid > 0
      and i.status & 2 = 2

I came up with the following solution - thanks to the comment by Phil:
SELECT
     distinct i.name as index_name, index_col(o.name, i.indid, c.colid) as name
FROM
    sysobjects o
    INNER JOIN sysindexes i on i.id = o.id
    INNER JOIN syscolumns c on c.id = o.id
WHERE
    o.type = 'U'
    and o.name = 'XXXXXX'
      and i.indid > 0
      and i.status & 2 = 2 -- Gives only system indexes. Skip it, and get all
      and index_col(o.name, i.indid, c.colid) <> null
    order by 1, 2

In Sybase version SAP IQ/16, you can get list of indexes with following (table name my_table is case-sensitive):

select *
from sys.sysindexes
where tname = 'my_table';


How to create multiple graphs with TitanDB (+Cassandra) in the same machine? I'm experimenting with TitanDB (with Cassandra as storage) + Rexter.

I've been reading the documentation during hours and trying to create multiple graph DBs on the same machine, but I didn't find the way to do it.

I find two main problems:

In the TitanDB docs, the Cassandra section tells how to create a graph on Cassandra, but the only parameters they comment are the host and the port, there isn't any reference to the graph name :s).
In the Rexter section we have essentially the same problem. We can define a list of named graphs, but every graph only can be mapped to a 3-tuple (backend type, host, and port).

The only way I can imagine is to instantiate more than one Cassandra server, and make that each Cassandra server listens to a different port. Then, configure Rexter to map every graph to a different cassandra instance.

There is a way to create more than one graph using the same Cassandra instance?

See https://github.com/thinkaurelius/titan/wiki/Using-Cassandra
scroll down to "Cassandra Specific Configuration", and
use property "storage.keyspace=" to use multiple keyspaces in cassandra.
Each keyspace will hold a graph. (default keyspace is 'titan'.

So, if you want 3 graphs, create 3 config files, with diff "storage.keyspace" property.

When you open a graph from your client using TitanFactory.open(config), use the appropriate config file.

There is a way to create more than one graph using the same Cassandra instance?

This useful tutorial is about precisely that:

  Define each Titan graph storage and indexing backends
  Configure Gremlin Server to initialize the graphs at launch
  Reference each graph Traversal object in the Gremlin Server .groovy bootstrap script


What is the best way to recover from a mysql replication fail? Today, the replication between our master mysql db server and the two replication servers dropped.
I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to recover for this issue.
I'd like to share with you the procedure and I'd appreciate if you could give your thoughts about it and maybe even tell me how it can be done quicker.

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
And copy the values of the result of the last command somewhere.

Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump mysq
Now you can release the lock, even if the dump hasn't end. To do it perform the following command in the mysql client:

UNLOCK TABLES;
Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;
Load master's data dump with this console command:

mysql -uroot -p < mysqldump.sql
Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
Where the values of the above fields are the ones you copied before.

Finally type

START SLAVE;
And to check that everything is working again, if you type

SHOW SLAVE STATUS;
you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
That's it!

At the moment i'm in the stage of copying the db from the master to the other two replication servers and it takes more than 6 hours to that point, isn't it too slow?
The servers are connected through a 1gb switch.

We can start replication from the position from where it stopped. You should check the Mysql  log on slave servers log must have the last mysql bin log postion where the replication stopped.

 STOP SLAVE;

 RESET Slave;

 FLUSH TABLES WITH READ LOCK;

 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

 Where the values of the above fields are the ones where replication stopped.

Finally type

 START SLAVE;

Really depends on what failed and why. If it's only connection problem and no data has been damaged on the master, I usually just check 'show slave status' and see what statement has failed. In my case, usually I just remove a row if it's trying to insert the same row and 'start slave' again.

If it's the master that failed (e.g. power failure), I'd promote the slave to be the master while I synchronise the old master with the new master. This way you keep your down time minimal and avoid transferring log file.

Only copy log file when you really need to, like after a disk crash.


database design question with Partition and Replication I have a main database that runs on a server with good resources and, let's say, a dozen satellites that replicate data. Satellites have less resources than main server.

In that database there is a BIG table, that I want partitioned in ranges because in fact each satellite cares about his own specific range.

PARTITION BY RANGE( ID ) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN ...
);

Let's say satellite 1 cares about table IDs that start with 1, satellite 2 cares about IDs that start with 2 and so on. (all IDs are numbered in millions).

Is it possible to replicate on each satellite only data that are relevant to the satellite? I mean I don't want p2 and p3 on satellite 1, only p1 is relevant for satellite 1. But I don't know how to say to the replication engine that I want only such a part of the data.

Is it only possible to replicate only one partition of the partition scheme?

Looks like you are looking for NoSQL DB with Data shard across nodes. Check the MySQL NDB Cluster.


Deleted MySQL "root" user (using phpMyAdmin)...how to restore? I'm test-running a Joomla installation (locally, using WAMP) with an MySQL database supporting it...this is in preparation to moving everything online once I have it configured.

I was working in phpMyAdmin to change the password of a superuser I had set up with a login-name other than "root."  While doing so, I saw three additional "root" users, and on the phpMyAdmin home page, a warning about these root users not having a password.  I thought that since I created my own superuser with "All Privileges," I didn't need the root users...so I deleted them.  Immediately things stopped working...and now, when I click on the "Users" tab in phpMyAdmin, it simply returns a red bar that says "No Privileges"...my specific superuser is gone.  However, it seems I can still move around in phpMyAdmin (maybe because I'm still logged in?).

Could someone please tell me step-by-step how (if?) I can re-add the root users using phpMyAdmin?  I see instructions posted in various places, but they seem to address users who are using terminal windows or some other program.  I'd  hate to see months of work go down the drain!

EDIT:  Using Windows, I've tried the following:

There is no file called "resetroot.bat" anywhere in the wamp/sql folders, as someone suggested...so that option is out.
I've tried to access MySQL using a terminal connection:  in the MySQL root folder there is a file called mysql.exe that seems to grant this when executed (there is no "mysql_safe.exe" as some solutions have suggested).  However, I have been unsuccessful in starting mysql with the --skip-grant-tables option from outside mysql.exe, and when I try to do it from within mysql.exe, I just get an odd prompt ("->") and no other commands seem to work.

Please keep in mind that I am a newbie at this!  I am trying different things, but without any success...that is why I'll probably need step-by-step instructions.

OK, I finally found a solution.  To give proper credit, I had to blend answers provided by two people:  RiggsFolly's answer to this question on resetting the root password in wamp, which is what I'm using, and jm666's answer for this question on restoring a deleted root user in MySQL using MAMP on OS X.
For Wamp Users:

Click on the Wamp system tray icon to bring up the tray menu.  Click
MySQL > Service > Stop Service.

Edit your MySQL's my.ini file...that's your configuration file for your SQL database, and it's generally located in the root folder for your MySQL database (try looking in wamp > bin > mysql > mysql5.5 [or whatever version]).  Use a text editor, and scroll down in the my.ini file to [wampmysqld].  Immediately after this entry, insert a new line containing the following command:
skip-grant-tables
This tells the MySQL database that you want to override any restrictions such as root user or password parameters.  This is a major security hole, so once we are done with our repairs, we'll need to undo this!  Save the my.ini file.

Resume MySQL services in Wamp (click MySQL > Service > Start/Resume Service).

Now open the MySQL console:  click MySQL > MySQL Console

A "DOS-like" terminal window will open up (if it asks you for a password, simply press enter).  You should see a MySQL prompt.  Copy the following block of code and paste it at the prompt.
INSERT INTO mysql.user
SET user = 'root',
host = 'localhost',
password = '',
Select_priv = 'y',
Insert_priv = 'y',
Update_priv = 'y',
Delete_priv = 'y',
Create_priv = 'y',
Drop_priv = 'y',
Reload_priv = 'y',
Shutdown_priv = 'y',
Process_priv = 'y',
File_priv = 'y',
Grant_priv = 'y',
References_priv = 'y',
Index_priv = 'y',
Alter_priv = 'y',
Show_db_priv = 'y',
Super_priv = 'y',
Create_tmp_table_priv = 'y',
Lock_tables_priv = 'y',
Execute_priv = 'y',
Repl_slave_priv = 'y',
Repl_client_priv = 'y',
Create_view_priv = 'y',
Show_view_priv = 'y',
Create_routine_priv = 'y',
Alter_routine_priv = 'y',
Create_user_priv = 'y',
Event_priv = 'y',
Trigger_priv = 'y',
Create_tablespace_priv = 'y';

Press enter.  You should get some sort of message saying that one line (of database code) was affected, with some warnings (probably because you are messing with the root user...I think you can safely ignore these).  Close the MySQL terminal.

Remember that security hole we opened in step two?  Time to fix that.  Reopen your my.ini SQL configuration file, and remove the skip-grant-tables line you added.  Save the my.ini file and close.

RESTART your MySQL Service (same as before, but click on MySQL > Service > Restart Services instead).

THAT should do the trick!
A note on the root user in MySQL for newbies:  for some strange reason, even if you create another user as a superuser, MySQL seems to need this user to operate...however, if you don't set up a password for root, it notes this as a security hole.  Whatever you do, don't delete it:  you're better off just setting some wildly obscure password for it and leaving it alone if you want to use another superuser account.  It would seem that there should be more protections to help prevent unwary users from doing this (a warning, or something)....that's my twelve cents on the issue.

I assume you mean MySQL.

Open your local folder for MySQL look for the bin folder then run the resetroot.bat file.


Very slow WHERE IN query on very large table - postgresql 9.6 My table (with 140 000 000 rows) :

CREATE TABLE attendees(
  attendee_id                 VARCHAR NOT NULL,
  name                        VARCHAR NOT NULL,
  status                      VARCHAR NOT NULL,
  event_id                    VARCHAR NOT NULL,
  PRIMARY KEY (attendee_id, event_id)
);

CREATE INDEX attendees_event_id ON attendees(event_id);
CREATE INDEX attendees_attendee_id ON attendees(attendee_id);

My query :

SELECT event_id FROM attendees
where attendee_id IN (SELECT attendee_id FROM attendees where event_id=$eventId)

This query can take several minutes.
I saw here: Optimizing a Postgres query with a large IN I can change the IN by IN (VALUES (..)...) but I don't know how to change this sub query :

SELECT attendee_id FROM attendees where event_id=$eventId

to have (VALUES (..)...)

So, my question is, what is the best way to make this query faster ?

edit (I run the explain (analyze, verbose) on smaller table):

Hash Join  (cost=5296.72..14097.38 rows=209116 width=16) (actual time=177.516..362.851 rows=121564 loops=1)
  Output: attendees.event_id
  Hash Cond: ((attendees.attendee_id)::text = (attendees_1.attendee_id)::text)
  ->  Seq Scan on public.attendees  (cost=0.00..5051.21 rows=230721 width=33) (actual time=0.572..90.302 rows=230721 loops=1)
        Output: attendees.event_id, attendees.attendee_id
  ->  Hash  (cost=4664.54..4664.54 rows=50574 width=17) (actual time=176.611..176.611 rows=49650 loops=1)
        Output: attendees_1.attendee_id
        Buckets: 65536  Batches: 1  Memory Usage: 2917kB
        ->  Bitmap Heap Scan on public.attendees attendees_1  (cost=1288.37..4664.54 rows=50574 width=17) (actual time=14.141..151.031 rows=49650 loops=1)
              Output: attendees_1.attendee_id
              Recheck Cond: ((attendees_1.event_id)::text = '1193751294041282'::text)
              Heap Blocks: exact=595
              ->  Bitmap Index Scan on attendees_event_id  (cost=0.00..1275.73 rows=50574 width=0) (actual time=13.265..13.265 rows=49650 loops=1)
                    Index Cond: ((attendees_1.event_id)::text = '1193751294041282'::text)
Planning time: 1.611 ms
Execution time: 371.431 ms

To Speed up the query,

We may use join instead of subquery

Try this query:

SELECT a1.event_id
FROM attendees a1
JOIN attendees a2
    ON a1.attendee_id = a2.attendee_id
        and a2.event_id = $eventId

To make is faster check that if you can create CLUSTER on that table with index on Primary key

Note: cluster a table according to an index reference


Specify type on SQL Server Computed Column I have just added a computed column to a table, and it is showing up as an int and I'd like to make it a bit is this possible? I'm having a hard time finding any info on this. Below is the computed column:

IsActive  AS case when DateDecommissioned is null then 0 else 1 end PERSISTED

You should be able to CAST to  your preferred data type inside the CASE:

...IS NULL THEN CAST(0 as BIT) ELSE CAST(1 as BIT) END

Try this:
ALTER TABLE dbo.MyTablr
ADD  DateDecommissioned AS (cast(Value1 AS bit) * cast(Value2 AS bit)) PERSISTED

This way you can determine the Data Type on the fly while you create the computed column


Did anybody use sqlcmd mode in practice? SQLServer 2005 introduced something called  SQLCMD Mode msdn link.

At first glance this mode adds variable subsitution from command line batch files,  and some escaping to OS commands.

Is this feature used in your environments, production or test?

I use the sqlcmd mode in SSMS on occasion. Typically when migrating databases from one environment to another.  For example if I need to backup the database from production, then restore it to dev, I'll do this all in one script using SQLCMD mode to switch servers.  I've used to here and there for one thing or another, but I don't use it all that often.

I use sqlcmd for running admin scripts, batch jobs and reports. I also use sqlsplus, which is like Oracle SQL*Plus, but for SQL Server (found it at www.sqlsplus.com)


PostgreSQL (big data): search by tags and order by timestamp We need to add a search feature on a large table (200M+ rows):

item_id | tags                          | created_at          | ...
-------------------------------------------------------------------
1       | ['tag1', 'bar2']              | 2020-01-06 12:43:32 |
2       | ['example5', 'tag9', 'foo2']  | 2020-01-10 10:40:00 |
3       | ['test1', 'tag5']             | 2020-01-11 12:43:32 |
...

The queries would be similar to this one:

SELECT * FROM items
WHERE tags @> ARRAY['t2', 't5']::varchar[]
ORDER BY created_at DESC
LIMIT 100;

Basically it's like searching some logs by tags and ordering them by timestamp. Seems a common scenario...

What index should we use? Have you ever tested something similar in production?

Example 1: create a GIN index on tags. The problem is that the search may return millions of results and in order to apply order / limit you need to make millions of reads from the table on disk (in order to get the created_at value for each row).
Example 2: add the btree_gin extension and create a composite index on created_at and tags. The problem is the same as above: I think that PostgreSQL cannot use ordering since the index is declared as a GIN index and not as a btree.
Example 3: create a btree index on created_at and tags. PostgreSQL needs to scan the whole index, since btree doesn't support array operators. I also fear that due to the SELECT * PostgreSQL will not use an index-only scan, thus resulting in millions of reads from disk (that would be actually useless since it only needs 100 reads from disk).

There are two approaches:

create an index on the array:

CREATE INDEX ON items USING gin (tags);

That allows the database to quickly find the matching rows, but then it has to perform a top-n sort.
create a B-tree index on created_at:

CREATE INDEX ON items (created_at);

That will allow the database to avoid the sort, but it has to scan and discard the rows that don't match the condition.

Unfortunately, the two strategies are mutually exclusive, and which is best depends on the data. You'll have to experiment.

Generate the current explain plan using explain select *  from table_name;
Create a gin index on tags column and btree index on created at column. Generate the new query/explain plan post index creation to notice the cost difference and execution times.


Counting occurrence of words in table is slow Consider these simplified tables:

CREATE TABLE dbo.words
(
    id bigint NOT NULL IDENTITY (1, 1),
    word varchar(32) NOT NULL,
    hits int NULL
)

CREATE TABLE dbo.items
(
    id bigint NOT NULL IDENTITY (1, 1),
    body varchar(256) NOT NULL,
)

The words table holds about 9000 records, each holding a single word ('phone','sofa','house','dog', ...)
The items table holds around 12000 records, each with a body text of no more then 256 characters.

Now, I need to update the words table, counting how many records there are in the items table that hold (at least once) the text in the word field. I need to account for partial words, so all these 4 records should be counted for the word dog:

'This is my dog'
'I really like the movie dogma'
'my cousin has sheepdogs'
'dog dog dog doggerdy dog dog'

That last example should count as just one record (contains the term 'dog' at least once).

I can use this query:

UPDATE dbo.words
SET hits = (SELECT COUNT(*) FROM dbo.items WHERE body like '%' + word + '%')

But, this is extremely slow, this will take over 10 minutes to complete on the not to heavy server I have for it.

AFAIK indexes won't help, I'm doing LIKE searches.
I also think full-text won't help me, since I'm looking for words starting, ending or containing my search term. I could be wrong here.

Any advise on how to speed this up?

The best way I have found to speed up leading wildcard LIKE searches is to use n-grams. I describe the technique and provide a sample implementation in Trigram Wildcard String Search in SQL Server.

  The basic idea of a trigram search is quite simple:

  Persist three-character substrings (trigrams) of the target data.
  Split the search term(s) into trigrams.
  Match search trigrams against the stored trigrams (equality search).
  Intersect the qualified rows to find strings that match all trigrams.
  Apply the original search filter to the much-reduced intersection.

It may be suitable for your needs, but be aware:

  Trigram search is no panacea. The extra storage requirements, implementation complexity, and impact on update performance all weigh heavily against it.

Test

I ran a quick test using the Complete Works of Shakespeare to populate the body column of the items table with 15,838 rows. I loaded the words table with 7,669 unique words from the same text.

The trigram structures built in around 2 seconds and the following update statement completed in 5 seconds on my mid-range laptop:

UPDATE dbo.words WITH (TABLOCK)
SET hits =
(
    SELECT COUNT_BIG(*)
    FROM dbo.Items_TrigramSearch
        ('%' + word +'%') AS ITS
);

A selection of the updated words table:

The modified trigram scripts from my article are below:

CREATE FUNCTION dbo.GenerateTrigrams (@string varchar(255))
RETURNS table
WITH SCHEMABINDING
AS RETURN
    WITH
        N16 AS
        (
            SELECT V.v
            FROM
            (
                VALUES
                    (0),(0),(0),(0),(0),(0),(0),(0),
                    (0),(0),(0),(0),(0),(0),(0),(0)
            ) AS V (v)),
        -- Numbers table (256)
        Nums AS
        (
            SELECT n = ROW_NUMBER() OVER (ORDER BY A.v)
            FROM N16 AS A
            CROSS JOIN N16 AS B
        ),
        Trigrams AS
        (
            -- Every 3-character substring
            SELECT TOP (CASE WHEN LEN(@string) > 2 THEN LEN(@string) - 2 ELSE 0 END)
                trigram = SUBSTRING(@string, N.n, 3)
            FROM Nums AS N
            ORDER BY N.n
        )
    -- Remove duplicates and ensure all three characters are alphanumeric
    SELECT DISTINCT
        T.trigram
    FROM Trigrams AS T
    WHERE
        -- Binary collation comparison so ranges work as expected
        T.trigram COLLATE Latin1_General_BIN2 NOT LIKE '%[^A-Z0-9a-z]%';
GO
-- Trigrams for items table
CREATE TABLE dbo.ItemsTrigrams
(
    id integer NOT NULL,
    trigram char(3) NOT NULL
);
GO
-- Generate trigrams
INSERT dbo.ItemsTrigrams WITH (TABLOCKX)
    (id, trigram)
SELECT
    E.id,
    GT.trigram
FROM dbo.items AS E
CROSS APPLY dbo.GenerateTrigrams(E.body) AS GT;
GO
-- Trigram search index
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.ItemsTrigrams (trigram, id)]
ON dbo.ItemsTrigrams (trigram, id)
WITH (DATA_COMPRESSION = ROW);
GO
-- Selectivity of each trigram (performance optimization)
CREATE OR ALTER VIEW dbo.ItemsTrigramCounts
WITH SCHEMABINDING
AS
SELECT ET.trigram, cnt = COUNT_BIG(*)
FROM dbo.ItemsTrigrams AS ET
GROUP BY ET.trigram;
GO
-- Materialize the view
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.ItemsTrigramCounts (trigram)]
ON dbo.ItemsTrigramCounts (trigram);
GO
-- Most selective trigrams for a search string
-- Always returns a row (NULLs if no trigrams found)
CREATE FUNCTION dbo.Items_GetBestTrigrams (@string varchar(255))
RETURNS table
WITH SCHEMABINDING AS
RETURN
    SELECT
        -- Pivot
        trigram1 = MAX(CASE WHEN BT.rn = 1 THEN BT.trigram END),
        trigram2 = MAX(CASE WHEN BT.rn = 2 THEN BT.trigram END),
        trigram3 = MAX(CASE WHEN BT.rn = 3 THEN BT.trigram END)
    FROM
    (
        -- Generate trigrams for the search string
        -- and choose the most selective three
        SELECT TOP (3)
            rn = ROW_NUMBER() OVER (
                ORDER BY ETC.cnt ASC),
            GT.trigram
        FROM dbo.GenerateTrigrams(@string) AS GT
        JOIN dbo.ItemsTrigramCounts AS ETC
            WITH (NOEXPAND)
            ON ETC.trigram = GT.trigram
        ORDER BY
            ETC.cnt ASC
    ) AS BT;
GO
-- Returns Example ids matching all provided (non-null) trigrams
CREATE FUNCTION dbo.Items_GetTrigramMatchIDs
(
    @Trigram1 char(3),
    @Trigram2 char(3),
    @Trigram3 char(3)
)
RETURNS @IDs table (id integer PRIMARY KEY)
WITH SCHEMABINDING AS
BEGIN
    IF  @Trigram1 IS NOT NULL
    BEGIN
        IF @Trigram2 IS NOT NULL
        BEGIN
            IF @Trigram3 IS NOT NULL
            BEGIN
                -- 3 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.ItemsTrigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                INTERSECT
                SELECT ET2.id
                FROM dbo.ItemsTrigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                INTERSECT
                SELECT ET3.id
                FROM dbo.ItemsTrigrams AS ET3
                WHERE ET3.trigram = @Trigram3
                OPTION (MERGE JOIN);
            END;
            ELSE
            BEGIN
                -- 2 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.ItemsTrigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                INTERSECT
                SELECT ET2.id
                FROM dbo.ItemsTrigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                OPTION (MERGE JOIN);
            END;
        END;
        ELSE
        BEGIN
            -- 1 trigram available
            INSERT @IDs (id)
            SELECT ET1.id
            FROM dbo.ItemsTrigrams AS ET1
            WHERE ET1.trigram = @Trigram1;
        END;
    END;

    RETURN;
END;
GO
-- Search implementation
CREATE FUNCTION dbo.Items_TrigramSearch
(
    @Search varchar(255)
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        Result.body
    FROM dbo.Items_GetBestTrigrams(@Search) AS GBT
    CROSS APPLY
    (
        -- Trigram search
        SELECT
            E.id,
            E.body
        FROM dbo.Items_GetTrigramMatchIDs
            (GBT.trigram1, GBT.trigram2, GBT.trigram3) AS MID
        JOIN dbo.Items AS E
            ON E.id = MID.id
        WHERE
            -- At least one trigram found
            GBT.trigram1 IS NOT NULL
            AND E.body LIKE @Search

        UNION ALL

        -- Non-trigram search
        SELECT
            E.id,
            E.body
        FROM dbo.Items AS E
        WHERE
            -- No trigram found
            GBT.trigram1 IS NULL
            AND E.body LIKE @Search
    ) AS Result;

The only other change was to add a clustered index to the items table:

CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.items (id);

SELECT /* THE TEXT TO BE SEARCHED */ AS searchText,
       SUM((LEN(fieldNameOfDB)
          - LEN(REPLACE(fieldNameOfDB, 'THE TEXT TO BE SEARCHED', '')))
           /LEN('THE TEXT TO BE SEARCHED')) AS noOfOccurances
FROM TableName


Effect of TRUNCATE TABLE different from DELETE on subsequent queries I always thought of TRUNCATE TABLE as an equivalent of DELETE, except that:

1) TRUNCATE can be used only in some cases (no FK referencing table, user must have ALTER permission etc)

2) TRUNCATE is faster to execute

Apart from that, I expected them to have identical results.
But from my experience, it seems that a TRUNCATE improves the performance of subsequent queries over a DELETE.
I have a script that clears some tables, then fills them with millions of rows from another database on the same server, like:

INSERT NEWDB..newtable(a,b,c) SELECT a,b,c FROM OTHERDB..oldtable

Switching the clearing of tables from DELETE to TRUNCATE TABLE made the deletion much faster (as obvious) but also made the subsequent data copying at least 10 times faster.
Interesting to note, the recovery model for the database is SIMPLE, so it is not a matter of logs.
Can someone explain why this is happening? Or maybe I am missing something and the gain in speed is caused by some other factor?

OTHERDB..oldtable --> the data could be in buffers from the first run.

Try running

DBCC DROPCLEANBUFFERS --Not in production though !!

and then your insert query.


Standalone MongoDB to MongoDB Shard Setup I'm learning MongoDB currently so maybe the title of this question is worded poorly, but I'm looking to understand the scaling of Mongo better. I did some research on what DB would be best for my current needs and currently a standalone instance of Mongo fills that, but I'd like to plan for the future.

I'm trying to figure out the process of scaling up MongoDB. In my case the issue I foresee is an extremely large number of documents requiring bigger servers. I began to look up information on ways to solve this and the best way I could see was using Mongo's sharing capability. The only issue though is I don't understand the process of going from standalone to shards. Is there a document that can explain the process or is there a better way to handle such scaling?

Any help is appreciated while I'm learning this.

How it is done

Basically, it is easy:

Set up three(!) config servers
Start a mongos query router and have the --configdb option string point to the three config servers. Note: the string has to be identical on all mongos
Restart your going to be shard with the shardsvr option set. This step isn't strictly necessary, but best practise. What this basically does is that mongod will start on a different port (27108), preventing accidental (and potentially problematic) direct access to the shard.
Connect to the mongos instance.
Add the shard via the sh.addShard() command.
Done. Start sharding the collections as needed.

Some advice

If it is foreseeable that you will have to scale out, I would go into production with a sharded cluster consisting of a single shard right away. This makes scaling out much easier and faster when you reach the limits of your single shard – simply fire up another shard, add it using the addShard command and you are done.
Always, always, always let your shards consist of replica sets. And from personal experience with one of my customers: While using a replica set with only two data bearing nodes and an arbiter is possible, I would not advice doing so. The reason for this advice is that if you are doing maintenance on one of the data bearing nodes, there is no failover possibility in case the other node fails. So your shards should consist of replica sets with (at least) three data bearing, non-hidden and non-delayed nodes with default priority.
While MongoDB is pretty easy to fire up and maintain in a standalone and even replica set configuration, things tend to get a lot more complicated when you start to shard in a production environment. Make sure you have a MongoDB specialist at hand when going into production.

Here is the mongodb documentation on how to covert replica set to a sharded cluster.
http://docs.mongodb.org/manual/tutorial/convert-replica-set-to-replicated-shard-cluster/

You can follow these steps except creating and adding replica set/sets you can add your standalone instance to the sharded cluster.


Improve sort performance in PostgreSQL? I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the number of comments for that article. It does that with the following query:

SELECT "articles"."id", "articles"."datestamp", "articles"."title", "articles"."shorttitle", "articles"."description", "articles"."markdown", "articles"."body", "articles"."idxfti", "articles"."published", "articles"."type", COUNT("comments"."id") AS "comment__count"
FROM "articles"
LEFT OUTER JOIN "comments" ON ("articles"."id" = "comments"."article_id")
WHERE ("articles"."type"='NEWS')
GROUP BY "articles"."id", "articles"."datestamp", "articles"."title", "articles"."shorttitle", "articles"."description", "articles"."markdown", "articles"."body", "articles"."idxfti", "articles"."published", "articles"."type"
ORDER BY "articles"."datestamp" DESC
LIMIT 1;

None of these tables is particularly large, and yet that query takes 46ms. The execution plan is:

Limit  (cost=119.54..119.58 rows=1 width=1150) (actual time=46.479..46.481 rows=1 loops=1)
   ->  GroupAggregate  (cost=119.54..138.88 rows=455 width=1150) (actual time=46.475..46.475 rows=1 loops=1)
     ->  Sort  (cost=119.54..120.68 rows=455 width=1150) (actual time=46.426..46.428 rows=2 loops=1)
           Sort Key: articles.datestamp, articles.id, articles.title, articles.shorttitle, articles.description, articles.markdown, articles.body, articles.idxfti, articles.published, articles.type
           Sort Method:  quicksort  Memory: 876kB
           ->  Hash Left Join  (cost=11.34..99.45 rows=455 width=1150) (actual time=0.513..2.527 rows=566 loops=1)
                 Hash Cond: (articles.id = comments.article_id)
                 ->  Seq Scan on articles  (cost=0.00..78.84 rows=455 width=1146) (actual time=0.017..0.881 rows=455 loops=1)
                       Filter: ((type)::text = 'NEWS'::text)
                 ->  Hash  (cost=8.93..8.93 rows=193 width=8) (actual time=0.486..0.486 rows=193 loops=1)
                       ->  Seq Scan on comments  (cost=0.00..8.93 rows=193 width=8) (actual time=0.004..0.252 rows=193 loops=1)
 Total runtime: 46.574 ms

The articles table has the following index defined (amongst others):

idx_articles_datestamp" btree (datestamp DESC) CLUSTER

Before I clustered it, the query execution was more in line with the estimates, around 119ms.

To my untrained eye, it looks like the sort is what's taking the most amount of time here. It also seems to be trying to sort on all the GROUP BY fields, the issue being that it's trying to sort on three relatively large fields, body, markdown and idx_fti.

My question is this: Is this an unreasonable amount of time for this query to take, or is there something obvious I've missed that I can use to speed this query up? All the other queries requested by this blog site take around 1-5ms to execute, so this one stands out as taking a long time. I appreciate there's an OUTER JOIN and a SORT, which don't really help. However, I'm not an expert, so if anyone has any suggestions, that'd be hugely useful.

Why is it slow?
I would advise to use the query @ypercube provided in combination with the mentioned indexes. But why is the query you had so slow in comparison?
You did not provide your table definition, but I assume from the column names and what you wrote that you have several (large) character type (text or varchar) columns in the table articles:
title, shorttitle, description, markdown, body, idx_fti

I further assume that you are running your database with a locale other than C. Sorting large text columns according to a locale is rather expensive. The relevant thing is the collation. Check your (current) setting for LC_COLLATE:
SHOW LC_COLLATE;

With Postgres 9.1 or later you can pick a collation for evaluating your expressions ad hoc. With PostgreSQL 8.4, however, this is set at cluster creation time and can't be changed later.
We recently had a related question on SO where after much deliberation and testing we found sorting according to a locale to be the major slowdown:

Slow query ordering by a column in a joined table

I expect @ypercube's query to solve that problem radically: No GROUP BY for the long text columns eliminates the expensive sort altogether. Problem solved.

Maybe you could do some tests modifying the value of work_mem. There you can find instructions in order to find how much memory is used in the sorting operations.


How to set the sequence to the last used id for all tables in Oracle? I'm working on an open source elearning system called Moodle which usually uses MySql or Postgresql, but can also use MSSQL and Oracle.

A client wishes to use Oracle, which I have limited experience of. I've got a local install of Oracle 11g r2.

I used a script to transfer data from MySql into Oracle, including the ID to keep the references.

But now when creating a record I get the error message : ORA-00001: unique constraint (SCHEMANAME.TABLENAME_ID_PK) violated

I'm guessing the sequences weren't updated when I inserted the ID's?

Is there an easy way to update all the table sequences to the last ID number used + 1 in each table?

Unless it's something else?

UPDATE : I can use this to get the last number in the sequence

SELECT sequence_name, last_number
FROM dba_sequences
WHERE sequence_owner = 'SCHEMA_NAME'
AND sequence_name LIKE 'V_%'

The last_number column has values but it looks like they are lower than the last id used in the table.

UPDATE 2: Found some existing code in Moodle that resets the sequence, I had wrongly assumed the naming convention for the sequences was created by Oracle but its Moodle. Here is some Moodle/PHP code that will reset all the sequences:

$todb = moodle_database::get_driver_instance($dbtype, $dblibrary);
$todb->connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
$totables = $todb->get_tables(false);
$tomgr = $todb->get_manager();

foreach ($totables as $table) {
    mtrace($table);
    try {
        $tomgr->reset_sequence($table);
    } catch (moodle_exception $e) {
        // There are a couple of temporary tables without indexes.
        mtrace($e->getMessage());
        continue;
    }
}

UPDATE 3 : This is basically what the reset sequence function does

$value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}');
$value++;

// Calculate a sequence name from the tablename.
$seqname = $this->getSequenceFromDB($xmldb_table);
if (!$seqname) {
    // Fallback, seqname not found, something is wrong. Inform and use the alternative getNameForObject() method
    $seqname = $this->getNameForObject($table, 'id', 'seq');
}

$this->mdb->execute("DROP SEQUENCE $seqname");
$this->mdb->execute("CREATE SEQUENCE $seqname START WITH $value INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size");

First you have to find out what the current sequence value is and then reset it. I would write a script which does the following tasks:

Get the max value from the table
update/recreate the sequence

The script could look like this:

declare
  maxval number;
begin
  select max(id) into maxval from <table_1>;
  execute immediate 'ALTER SEQUENCE <sequence_name_1> START WITH '|| maxval+1 ||';';

  select max(id) into maxval from <table_2>;
  execute immediate 'ALTER SEQUENCE <sequence_name_2> START WITH '|| maxval+1 ||';';
end;
/

I'm pretty sure there are better scripts but if you have to do this task frequently this might do the job without much development time.

declare
  maxval number;
begin
  select max(id) + 1 into maxval from <tablename>;
  execute immediate 'DROP SEQUENCE  <sequencename> ';
  execute immediate 'CREATE SEQUENCE  <sequencename>  MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 START WITH ' || maxval || ' CACHE 20 NOORDER  NOCYCLE ';
end;


PostgreSQL database does not exist error I am getting a "database does not exist error", even when the database exists:

postgres=# GRANT ALL PRIVILEGES ON DATABASE Owino to OdooD;

  ERROR:  database "owino" does not exist

Name    |  Owner   | Encoding |   Collate   |    Ctype    |    Access privileges

Owino   | dominus  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres| postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres           +
        |          |          |             |             | postgres=CTc/postgres  +
        |          |          |             |             | orokon=CTc/postgres    +
        |          |          |             |             | powerbidev=CTc/postgres+
        |          |          |             |             | odoodev=CTc/postgres

The database was created with double quotes, so the name is now case-sensitive and you are now stuck with the dreaded quoted identifiers. "Owino" is a different name than Owino

GRANT ALL PRIVILEGES ON DATABASE "Owino" to OdooD;

Depending on how you created the user, you might need to use the dreaded double quotes for the user name as well ("OdooD").

I highly recommend to simply never use double quotes in SQL.

See the manual for more details:
https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Dont use mixed case database names, there are several applications that will have problems with this

Owino is mixed case

The answer above imples that you created the database name using " ".

I have encountered this issue when I created a database name without " ", just using a mixed case name like MyDatabase


ERROR 1114 (HY000): The table is full I am trying create a table using a cross join or Cartesian Product subquery that creates a table with a large number of rows, like at least 148 million rows.  I get this

  ERROR 1114 (HY000): The table is full.

I plan on filtering and querying the table later on, but how can I create this table?

Check, if you did not run out of disk-space (this is the first step, check the tmp file)

From the comments, You seem to have a too low maximum size for your innodb_data_file_path, You cannot host more than 512MB of data in all innodb tables combined.

I think you should switch to an innodb-per-table scheme using innodb_file_per_table.


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


Duplicate error when update column in the same table I am getting error code Error Code: 1062 Duplicate Entry for KEY Primary.

The Procedure is suppose to increment workshift by 1 day each week. Ex, 6001 is Monday shift, 6002 Tuesday and so on each time it's called.

I know i'm getting the error due to as soon as 6001 == 6002 in first if , then there is 2 6002 with same PK. Is there anyway to fix this?

Here is current table.

roster
 employee  branch  workShift
   0001      01       6001
   0001      01       6002
   0001      01       6003
   0001      01       6004

Table after call to update roster:

roster
 employee  branch  workShift
   0001      01       6002
   0001      01       6003
   0001      01       6004
   0001      01       6005

CREATE PROCEDURE `UpdateRoster`(in employeeid char(8), in BranchID int(11))
BEGIN
 DECLARE hours_max INT(35);
 DECLARE fin INT DEFAULT 0;
 DECLARE empid CHAR(8);
 DECLARE brnid INT(11);
 DECLARE workshfid INT(11);
 DECLARE emp INT;

 DECLARE employee_roster CURSOR FOR
         SELECT EmployeeID, BranchID, WorkingShiftID
         FROM DutyRoster;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = 1;

 OPEN employee_roster;
 REPEAT
    FETCH employee_roster INTO empid, brnid, workshfid;
        IF workshfid = '6001' THEN
            SET workshfid = '6002';

        ELSEIF workshfid = '6002' THEN
            SET workshfid = '6003';

        ELSEIF workshfid = '6003' THEN
            SET workshfid = '6005';

        ELSEIF workshfid = '6005' THEN
            SET workshfid = '6007';

        ELSEIF workshfid = '6007' THEN
            SET workshfid = '6008';

        ELSE
            SET workshfid = '6001';
        END IF;

    UPDATE DutyRoster
    SET WorkingShiftID = workshfid;

  UNTIL fin
  END REPEAT;
  CLOSE employee_roster;
END

It appears that WorkingShiftID is the primary key of the DutyRoster table, and that you're trying to set this to the same value in all rows:

UPDATE DutyRoster SET WorkingShiftID = workshfid;

Presumably, the problem is you forgot to include a WHERE clause in that statement, so the update could be restricted to a single row.

In my case I would add new column with autoincrement numerical value. And then update the minimum or maximum value from that new added column.


What to do when the active log directory is full? Here's my database configuration:

 Log file size (4KB)                         (LOGFILSIZ) = 125000
 Number of primary log files                (LOGPRIMARY) = 10
 Number of secondary log files               (LOGSECOND) = 0
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /db2/C21/log_dir/NODE0000/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0025598.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = YES
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

 Percent log file reclaimed before soft chckpt (SOFTMAX) = 100

Now /db2/C21/log_dir utilization is 100%. What  to do in this situation?

the upper limit of log file size, combined with the upper limit of the number of log files (logprimary + logsecond), gives an upper limit of 1024 GB of active log space.

You can increase the size of the log file size setting by issuing the command:

db2 UPDATE db cfg for <databaseName> using LOGFILSIZ <new_value>


Is there a way to generate table create script in TSQL? Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a table name and returns a string that contains the create script for the given table?

Now let me describe the situation I'm facing, as there may be a different way to approach this. I have an instance with several dozen databases. These database all have the same schema, all the same tables, index and so on. They were created as a part of a third party software installation. I need to have a way to work with them so that I can aggregate data from them in ad-hoc manner. Nice people at dba.se have already helped me here How to create a trigger in a different database?

Currently I need to find a way to make a select from a table across all the databases. I have recorded all the database names into a table called Databasees and I wrote the following script to execute a select statement on all of them:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

select * into #tmp from Database1.dbo.Table1 where 1=0
DECLARE @statement nvarchar(max) =
  N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'

DECLARE @LastDatabaseID INT
SET @LastDatabaseID = 0

DECLARE @DatabaseNameToHandle varchar(60)
DECLARE @DatabaseIDToHandle int

SELECT TOP 1 @DatabaseNameToHandle = Name,
@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No

WHILE @DatabaseIDToHandle IS NOT NULL
BEGIN

  DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'
  EXEC @sql @statement

  SET @LastDatabaseID = @DatabaseIDToHandle
  SET @DatabaseIDToHandle = NULL

  SELECT TOP 1 @DatabaseNameToHandle = Name,
  @DatabaseIDToHandle = Database_Ref_No
  FROM Databasees
  WHERE Database_Ref_No > @LastDatabaseID
  ORDER BY Database_Ref_No
END

select * from #tmp
DROP TABLE #tmp

However the script above fails with the following message:

  An explicit value for the identity column in table '#tmp' can only be
  specified when a column list is used and IDENTITY_INSERT is ON.

Adding this:

SET IDENTITY_INSERT #tmp ON

does not help, since, I can't specify the column list and keep it generic.

In SQL there is no way to switch the identity on a given table off. You can only drop a column and add a column, which, obviously changes the column order. And if the column order changes, you, again, need to specify the column list, that would be different depending on the table you query.

So I was thinking if I could get the create table scrip in my T-SQL code, I could manipulate it with string manipulation expressions to remove the identity column and also add a column for the Database name to the result set.

Can anyone think of a relatively easy way to achieve what I want?

Back in 2007, I asked for an easy way to generate a CREATE TABLE script via T-SQL rather than using the UI or SMO. I was summarily rejected.
However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha:
CREATE TABLE dbo.whatcha
(
  id INT IDENTITY(1,1),
  x VARCHAR(MAX),
  b DECIMAL(10,2),
  y SYSNAME
);

The following script uses the new sys.dm_exec_describe_first_results_set dynamic management function to retrieve the proper data types for each of the columns (and ignoring the IDENTITY property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using a WHILE loop (that doesn't make it better, just simpler to look at and allows you to ignore Database_Ref_No entirely :-)).
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + name + ' ' + system_type_name
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

DECLARE @dbs TABLE(db SYSNAME);

INSERT @dbs VALUES(N'db1'),(N'db2');
  -- SELECT whatever FROM dbo.databases

SELECT @sql += N'
  INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
  FROM @dbs;

SET @sql += N'
  SELECT ' + @cols + ' FROM #tmp;';

PRINT @sql;
-- EXEC sp_executesql @sql;

The resulting PRINT output:
CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
  INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
  INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
  SELECT id,x,b,y FROM #tmp;

When you are confident it's doing what you expect, just uncomment the EXEC.
(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)

You can generate a rough CREATE TABLE using dynamic SQL from the data in INFORMATION_SCHEMA.COLUMNS.

If you need to add constraints etc you will need to add information from some of the other INFORMATION_SCHEMA views.

Microsoft documentation


Over use of Oracle With Clause? I'm writing many reporting queries for my current employer utilizing Oracle's With clause to allow myself to create simple steps, each of which is a data oriented transformation, that build upon each other to preform a complex task.

It was brought to my attention today that overuse of the with clause could have negative side effects on the Oracle server's resources.

Can anyone explain why over use of the oracle with clause may cause a server to crash? Or point me to some articles where I can research appropriate use cases? I started using the With clause heavily to add structure to my code and make it easier to understand, I hope with some informative responses here I can continue to use it efficiently.

Thanks!

Remember that the WITH clause is used to name sub-queries, and sub-query logic can be abused somewhat. The overhead for WITH shouldn't end up being much more than using a regular sub-query, but still don't try to have a ridiculous amount of nesting going on. So long as you have a good picture of how deep you're going with things, you should be fine.

With clause should generally be used if the resultant dataset is  below 999 rows.
Based on non-documented factors, Oracle decides whether it wants to store the data set in memory or in a oracle managed GTT. There is an undocumented way to force ORacle to store the result set in a Oracle defined GTT.
The result set created if stored in runtime memory can become a resource hog and penalize resources in PGA AND UGA. I have seen situations where PGA and UGA memory usage of a session has increased 400% with a With() executed and expected on reserve increased by 1800%.
If the result set is stored in a GTT the resource requirement would not go so high. My 2c


How to compare two SQL Servers instance level objects for a migration? I'm currently working on a SQL Server migration (from a SQL Server 2005 to a 2008 R2 server, on a new physical server).

The databases themselves will be transferred using a database backup and restore script.
I have tested this fine and it works without problems.

My worry is rather regarding all the other instance/system objects, like Logins, SQL Agent jobs.
These have been scripted out from the source server and reapplied on the new server, and I'm afraid I have forgot some.
I can obviously visually compare using SQL Management Studio, which is what I did, but it is time-consuming and error-prone.
So, before plugging the plug off the existing server in due time, I would prefer to have a more "scientific" way to confirm that everything is in there...

I've looked for software doing this kind of comparison and have found only schema or data compare tools.
I guess I could compare the system tables like sys.sql_logins and msdb.dbo.sysjobs, etc, but I don't know them all.

In short, is there a way to quickly compare the instance-level objects of two servers, and if possible, their contents as well rather than just by name?

Thank you.

If you're only concerned about Agent Jobs and logins, I think the easiest approach is to leverage a linked server and querying the relative tables.  I would do this by first creating a linked server on the old instance that points at the new instance.  Once I had that, I could run the following queries:

Logins

SELECT * FROM [SQL2005].[master].[sys].[server_principals]
WHERE name NOT IN (SELECT name FROM [SQL2008].[master].[sys].[server_principals])

Agent Jobs

SELECT * FROM [SQL2005].[msdb].[dbo].[sysjobs]
WHERE name NOT IN (SELECT name FROM [SQL2008].[msdb].[dbo].[sysjobs])

There might be some inconsistencies with some server specific accounts/logins, but these queries should show you everything that exists in the old server that doesn't exist in the new server.  You should be able to extend this logic to other facets as long as you had system tables to query for it.

As for objects that need to be synchronized, I think the best approach is to review your instance for the objects that should be migrated.  Typically this would only be logins and Agent Jobs, but could also include linked servers (sys.servers), end points (sys.endpoints), and other objects.

I believe that if you transfer the system databases to your new instance you will retain the system level objects you are referring to. This MSDN article should help (as suggested from a similar question posted here): http://msdn.microsoft.com/en-us/library/ms345408.aspx

There is also an article about transferring logins between instances: http://support.microsoft.com/kb/918992


Troubleshooting Powershell errors from SQL Agent job I have a powershell script that I am trying to add in to a sql agent job, but the job step is failing with the following error

    A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS  -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Could not load file or assembly 'file:///C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

I assumed that the error was being caused by differences in the module path, so I explicitly set the path via $env:PSModulePath to match the path of my powershell session where the code runs fine.  I am pretty new to powershell, so any help you could provide would be appreciated

I am just doing some testing right now, so everything is running locally on my desktop.  I am running SQL Server 2012.

When you run a SQL Agent job step with the "PowerShell" type, you aren't actually running in (full) PowerShell. This job step type actually runs under the sqlps.exe minishell, which is based on PowerShell 1.0 and an extremely limited number of cmdlets.

Practically speaking, the PowerShell job step type is not very useful. Instead, I recommend using an "Operating System/CMDExec" job step, and calling PowerShell.exe from the command line.

Using the cmdexec job step type, your job step would look something like this:

PowerShell.exe "D:\Scripts\MyScript.ps1" -NonInteractive

By calling PowerShell.exe, you get the full/current PowerShell shell (instead of the mini shell), and you get to use the full/current SQL Server PowerShell module. This is particularly important if you are using SQL Server 2016+, where the SQLSERVER module has been enhanced with a much longer list of cmdlets.

Remove the import-module from the job, SQL Server already starts up with SQLPS loaded, in 2012 it loads the 2.0 version of powershell so your scripts might need more adjustments.


Stop SQL Server service(s) before defragmenting drive? Our production SQL Server 2005 database's data files live on a separate physical drive, which Microsoft Windows 2003's Disk Defragmenter tool reports as 99% fragmented.

We scheduled a task to defragment this drive at 3:00 a.m. on a Saturday morning. The job completed after 40 minutes with no apparent errors. However, the drive remains heavily fragmented.

Should we have stopped SQL Server service(s) before defragmenting?

CONTEXT

Per requests for context: We have a Microsoft SQL Server 2005 instance (9.00.5324.00) running 32-bit Windows Server 2003 (SP2) on Dell PowerEdge 2950 hardware, circa 2007, with 4GB RAM. The PowerEdge 2950 has four 68GB drives configured as RAID-1 to create two 68GB virtual disks: (1) C (boot and OS) & D (pagefile, miscellaneous other data); and (2) E (SQL data). To my knowledge, IT staff have never defragmented any of these drives...Disk Defragmenter reports file fragmentation of 66% (C), 77% (D), and 99% (E). Performance Monitor reports the following average results: "Paging file: % usage" = ~6.8%; "SQL Server: Buffer Manager - Page life expectancy" = 20 seconds; and "PhysicalDisk: Avg. disk sec/write, drive E" = between 300 and 1,100 ms. We're due for a much-needed hardware and SQL Server upgrade in a few months time (viz., new hardware, 64-bit Windows Server 2012, 64-bit SQL Server 2012, 12GB RAM), but, due to end-user performance, want to alleviate the issue as much as possible. Thus the thinking a file defrag might help for drive E, the main SQL data drive.

As an aside, last week we pulled two failed drives and rebuilt the array...not sure that matters. We contract with another IT team to maintain the server, so we do not have direct access to the equipment...our organization just pays for services.

We can afford the downtime during regularly scheduled maintenance windows (weekly) as well as out-of-band downtime, as necessary, overnight.

Your problem is not disk fragmentation. Your problem is RAM and application table scans:

  4GB RAM ... 68GB ... Page life expectancy 20 seconds

You need way more RAM. As in your new server should have way, way, way, way more than 12GB. Start with 64 GB, it costs basically dimes. And yes, fix your app to use indexes. 20 seconds is very clear indication of table scans that trash the buffer pool. You need to fix the app, add the required indexes and fix your queries. For your case defragmenting the drives is as much a red herring as red herrings get.

Oh, and please move the log to separate physical spindles from data. Alone.

Brad McGehee describes it well here:

http://www.bradmcgehee.com/2011/06/do-you-ever-physically-defragment-your-sql-server-mdf-ldf-files/

...if you leave the SQL Server service running during your defrag, the database files are open and therefore not defragged at all.