Restore SQL Server 2000 Database in use I need to restore a SQL Server 2000 database to a backup of about 2 days ago. I have just backed it up and have tried to restore it, but it says it is in use.

What is the best way to find out who is still using it and then how to disconnect them?

I imagine taking the DB offline is a simple way of doing it, but probably not the safest?

Running sp_who2 will show you who is connected to the database.

The safest way to disconnect users is to ask them to disconnect themselves. If that is not practical, you can take the database offline or set it to SINGLE_USER mode to force the other connections out before performing your restore.

See this for more information and examples:

ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Test4]
FROM DISK = 'c:\test4.BAK'
WITH MOVE 'Test4_Data' TO 'c:\data\Test4.mdf',
MOVE 'Test4_Log' TO 'c:\data\Test4_log.ldf'


What is faster, one big query or many small queries? I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, we only need to use only 1 column.
So would it be faster to just make simple selects, and then "join" them in the system code?
The system could be in php, java, asp, or any language that connect to the database.
So the question is, what is faster going from a server side (php, java, asp, ruby, python...) to the database and running one query that gets everything we need or going from the server side to the database and running a query that only gets the columns from one table at a time?

What would address your question is the subject JOIN DECOMPOSITION.
According to Page 209 of the Book

You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query:
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';

You might run these queries:
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

Why on earth would you do this ? It looks wasteful at first glance, because you've increased the number of queries without getting anything in return. However, such restructuring can actually give significant performance advantages:

Caching can be more efficient. Many applications cache "objects" that map directly to tables. In this example, if the object with the tag mysql is already cached, the application will skip the first query. If you find posts with an ID of 123, 567, or 908 in the cache, you can remove them from the IN() list. The query cache might also benefit from this strategy. If only one of the tables changes frequently, decomposing a join can reduce the number of cache invalidations.
Executing the queries individually can sometimes reduce lock contention
Doing joins in the application makes it easier to scale the database by placing tables on different servers.
The queries themselves can be more efficient. In this example, using an IN() list instead of a join lets MySQL sort row IDs and retrieve rows more optimally than might be possible with a join.
You can reduce redundant row accesses. Doing a join in the application means retrieving each row only once., whereas a join in the query is essentially a denormalization that might repeatedly access the same data. For the same reason, such restructuring might also reduce the total network traffic and memory usage.
To some extent, you can view this technique as manually implementing a hash join instead of the nested loops algorithm MySQL uses to execute a join. A hash join might be more efficient.

As a result, doings joins in the application can be more efficient when you cache and reuse a lot of data from earlier queries, you distribute data across multiple servers, you replace joins with IN() lists, or a join refers to the same table multiple times.
OBSERVATION
I like the first bulletpoint because InnoDB is a little heavy-handed when it crosschecks the query cache.

Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?
Jun 07, 2014 : Why query_cache_type is disabled by default start from MySQL 5.6?

As for the last bulletpoint, I wrote a post back on Mar 11, 2013 (Is there an execution difference between a JOIN condition and a WHERE condition?) that describes the nested loop algorithm. After reading it, you will see how good join decomposition may be.
As for all other points from the book, the developers really look for performance as the bottom line. Some rely on external means (outside of the application) for performance enhancements such as using a fast disk, get more CPUs/Cores, tuning the storage engine, and tuning the configuration file. Others will buckle down and write better code. Some may resort to coding all the business intelligence in Stored Procedures but still not apply join decomposition (See What are the arguments against or for putting application logic in the database layer? along with the other posts). It's all up to the culture and tolerance of each developer shop.
Some may be satisfied with performance and not touch the code anymore. Other simply don't realize there are great benefits one can reap if they try join composition.
For those developers that are willing ...
GIVE IT A TRY !!!


SQL Server 2008 R2: query RECENT locks, NOT current locks The database had average lock wait time of 30 seconds for a period of about a minute.
I didn't run my lock query in time to see what was happening.
I can't seem to find anything that shows recent locks, just current locks.

some (most) X locks are recorded in the log, see How to read and interpret the SQL Server log. You can see what xact acquired them and how long the xact lasted (see the link). You won't find the conflicts, but you can make some guess.


Using (NOLOCK) in a READ COMMITTED transaction block We're using SQL Server 2000.

I'm wondering what the risks are if we use a NOLOCK hint within a READ COMMITTED transaction block, for example:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
IF EXISTS(SELECT * FROM clients (NOLOCK) WHERE clientID = 23249)
    BEGIN
        PRINT 'TODO Update client'
    END
ELSE
    BEGIN
        PRINT 'TODO Insert client'
    END

COMMIT TRANSACTION

My concern is that the NOLOCK hint will allow a ditry read, despite the isolation level of the transaction block.

Is this possible?

PS: I know there is a debate to be had about why I am using a locking hint the in first place, but I would just like to know what the risks are in this scenario.

The hint overrides the isolation level of the connection so you would indeed be allowing a dirty read from the clients table.


behavior of a Sql Server availability group listener and a jdbc client during a failover? i have a question about the behavior of the AG listener during a failover... when the failover happens, I realize that all the connections on both primary and secondaries are closed.
if the client apps are using one of the blessed client libraries (such as microsoft jdbc > 6.0) they will then start immediately trying to reconnect to the ip addresses associated with the listener. But what happens during the 10 second period (or whatever it is) for the failover to complete? Might a client successfully get a connection, only to get a fatal error message when it tries to use it? Or is something done to ensure that no client will get a connection successfully during failover itself, so that that when a client does successfully get a connection, it is actually usable?
(FYI regarding the behavior of the microsoft jdbc driver, judging from the source code at:
https://github.com/microsoft/mssql-jdbc/blob/dev/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java
It will divide the configured loginTimeout by 8 and so make 8 attempts before timing out.)

My understanding is that no client is going to be able to connect to the Listener and get a database connection until the failover completes. Look at the section "How Automatic Failover Works" in the following link. See here


Force MariaDB Galera to only create InnoDB tables I successfully run a cluster of 3 DB servers using MariaDB Galera 10.0.16. This setup handels a couple of web apps we have created, plus a dozen medium traffic Joomla websites.

My problem is that Galera doesn't like MyISAM. Pretty often, when a plugin or component in Joomla gets installed, it sets the MySQL engine in its install.sql like so:

ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

When that happens, a simple Joomla component installation can (and has) break the replication.

Is there a way that I can force the server to ignore the ENGINE setting?

Thanks.

This is a good question. People tend to want that when using NDB cluster and Galera.

The right answer is that you should report the plugin creators to create the tables with no engine definition, so you can set it with "default_engine" option. While on other cases you could disable the engine and force the engine substitution with the default one, that is not a good practice, it is discouraged on the latest versions and impossible with MyISAM.

The truth is that once the developer writes ENGINE=X, it is forcing the creation of the engine, as written. The only way to accomplish that is by rewriting the query on-the-fly. That is available in MySQL 5.7 (not yet GA), with several plugins. The only way I can think of doing it now is with a SQL proxy like ProxySQL or MySQL Proxy. Please note that some of those are infamous for bad performance, so you may want to enable it only on plugin activation/disabling.

There is obviously another option: download the MariaDB Cluster sources, disable MyISAM on the CREATE TABLE Syntax, and compile!


Restoring from an LDF file A record was accidentally updated which I need to be restored. Now, please bear in mind I'm not super-experienced in restore/backing up databases...

I have a 7gb MDF file and a 10gb LDF file (Sql Express 2016) . The database's recovery mode was set as Simple - I have since changed this to FULL and performed a backup of the Transaction LOG.

I have restored the DB's original backup file (some 6 months ago - this is a development Sql Instance, so no backups are regularly taken of it). but when I try to restore the log file, I can select no timeline from the past.

My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file and if not, what possible use does this LDF file serve if I can't restore from it?

Thanks

My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file

No, You are in simple recovery, once the transaction is committed it is not kept in the .ldf there are no point in time recovery options.

  and if not, what possible use does this LDF file serve if I can't restore from it?

You do not differentiate between the allocated and used space on .ldf file.  My best guess is that long ago it was in full recovery, and no one was doing t-log backups so it grew huge.  Then is was changed to simple recovery and it became mostly empty.  See related Why Does the Transaction Log Keep Growing or Run Out of Space?


MySQL hangs with nothing in error log I'm having an issue where my MySQL server (part of a LAMP stack running WordPress) hangs every few hours (giving WordPress a "database connection error").

The error log shows nothing other than launching after a restart, and shutting down ("normal shutdown") when I need to restart it. The only thing of note is that it says it is "purging the queue" during a restart, and then has ~300 lines like this:

151026 19:04:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 31577  user: 'root'

The only other weird behaviour I've noticed is that running mysqladmin status shows "Questions" increasing rapidly, presumably until it hangs. I've looked online and can't find much discussion about what normal range or behaviour "Questions" should exhibit. Eg, this is after 10 minutes uptime:

Uptime: 521  Threads: 3  Questions: 16667  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 100  Queries per second avg: 31.990

I have query_cache_size set to 50M, otherwise my.cnf is default on Ubuntu 14.04. This is the performance analysis from the mysqltuner perl script:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 52s (29K q [31.475 qps], 1K conn, TX: 73M, RX: 4M)
[--] Reads / Writes: 88% / 12%
[--] Binary logging is disabled
[--] Total buffers: 226.0M global + 2.7M per thread (151 max threads)
[OK] Maximum reached memory usage: 290.5M (1.81% of installed RAM)
[OK] Maximum possible memory usage: 631.8M (3.94% of installed RAM)
[OK] Slow queries: 0% (0/29K)
[OK] Highest usage of available connections: 15% (24/151)
[OK] Aborted connections: 0.00%  (0/1515)
[OK] Query cache efficiency: 71.8% (17K cached / 24K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (10 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 59% (1K on disk / 1K total)
[OK] Thread cache hit rate: 97% (39 created / 1K connections)
[OK] Table cache hit rate: 93% (103 open / 110 opened)
[OK] Open file limit used: 4% (51/1K)
[OK] Table locks acquired immediately: 100% (8K immediate / 8K locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.0M
[OK] Read Key buffer hit rate: 100.0% (6 cached / 0 reads)
[OK] Write Key buffer hit rate: 100.0% (8 cached / 0 writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/292.9M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 75.48% (6183 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 99.70% (1960793 hits/ 1966769 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 894 writes)

The hanging has started happening more frequently since an increase in logged in users (people accessing the database) and changes to my Apache config, which solved Apache crashes - my Apache prefork MPM is set as follows:

StartServers              20
MinSpareServers           10
MaxSpareServers           20
ServerLimit               1000
MaxRequestWorkers         500
MaxConnectionsPerChild    10000

The server is a DigitalOcean droplet with 8 CPU cores and 16GB RAM.

Do not have WP connecting as root.  Create another 'user'
change innodb_buffer_pool_size to 1G.
Turn on the slowlog and set long_query_time = 1; you have some naughty queries that need to be investigated
What is MaxClients in Apache?  Not more than 20, I hope.


Create an alias for a SQL Server named instance pointing to sql default instance I have an installation of SQL Server 2012 Std Ed on Windows 2012 R2 running in a default instance, and an app that wants to connect to it using a named instance (can't be changed)

E.g.

ServerName - mySQLServer
Alias required by app - mySQLServer\CrazyApp

I've tried to create an alias as follows:

Alias Name - .\crazyapp
Port No - 1433
Protocol - TCP/IP
Server .

I've:

checked TCP/IP protocol is enabled
have started SQL browser (not needed, I believe)
restarted SQL (several times)
tried with the machine name instead of "."
tried with no port number instead of 1433.
banged head against wall

...all to no avail.

Is it possible to do this, if so, would some kind person tell me what I'm doing wrong.

Thanks!

My observation is it is necessary to use cliconfg.exe on the application server in order for it to map a specific instance (e.g. '\crazyapp') to the default instance on the remote SQL server.

On the application server, I have set aliases using cliconfg.exe, using both:

C:\Windows\System32\cliconfg.exe (64bit) and
C:\Windows\SysWOW64\cliconfg.exe (32bit)  (thanks, @DaniSQL)

From the application server, I have configured:

I then successfully tested this from the application server, using SSMS to the remote SQL instance.

Creating the alias on the server hosting SQL Server does not work, even if the application server can resolve the SQL Server name via DNS. In order to map from a named instance to a default instance, the alias must be created on the application server.

Thanks to @ypercube for guiding me in the right direction.


Creating/dropping MySQL users using "IF NOT EXISTS" when row based replication is in place I'd like to know the affect of using "IF NOT EXISTS" when creating or dropping users when row based replication is being used to replicate data to a mysql slave.
I know that in the past I have broken replication by creating or dropping users that already existed (or didn't exist) on the slave, even though row based replication was being used.
It seems that the user table isn't replicated in a row based fashion even though the database itself is.
Would adding "if not exists" to my create or drop statements prevent me from breaking replication in the future?

You are correct in that row based replication is solely for table changes. CREATE USER etc are replicated as statements.
CREATE USER IF NOT EXISTS does prevent a failure overwiting an existing user and a replication failure if the user exists. It doesn't guarantee that the new user definition is the same.
MariaDB's CREATE OR REPLACE USER ensures the user created can always replicatoin and that the user becomes the same.
Another technique that will work for MySQL is DROP USER IF EXISTS followed by CREATE USER.
The more thorough solution for consistency is to be strictly on how replicas are created and don't update replicas except through replication.


why doesn't this parameterized sql return anything while the non parameterized one does? I have tried to use sql parameter on one of my queries and noticed when ever i do so, it just doesn't return anything! while the none parametrized one works just fine.
Why is it ?
None parametrized one :

string columnCommentsQuery = string.Format("SELECT   * FROM   ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '{0}', 'column', default)", table[2].ToString());

    DataTable comments = new DataTable();
    SqlCommand sql = new SqlCommand(columnCommentsQuery, conn);
    conn.Open();
    SqlDataAdapter da = new SqlDataAdapter(sql);
    da.Fill(comments);
    conn.Close();

Parametrized one:

string columnCommentsQuery = "SELECT   * FROM   ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @tb, 'column', default)";
     DataTable comments = new DataTable();
     SqlCommand sql = new SqlCommand(columnCommentsQuery, conn);
     sql.Parameters.AddWithValue("tb", "'" + table[2].ToString() + "'");
     conn.Open();
     SqlDataAdapter da = new SqlDataAdapter(sql);
     da.Fill(comments);
     conn.Close();

Try changing

sql.Parameters.AddWithValue("tb", "'" + table[2].ToString() + "'");

to

sql.Parameters.AddWithValue("tb", table[2].ToString() );

hope this helps.


Best way to update list of rows in oracle database I need to update a variable list of database rows. The list of the affected ids will be generated by an application, the statement will be executed about 100 times a day (with an always different collection of ids)

The first approach to update the table was:

UPDATE mytable set mycolumn = 'X' where id in (1,2,3,4 .... 2345);

I know that this statement cannot be effectively cached in the Oracle SQL area since it does not use bind variables. And if the application needs to update very frequently, it could push other statements out of the SQL area, which would have an performance impact.

Another approach could be a series of update statements

UPDATE mytable set mycolumn = 'X' where id = 1;
UPDATE mytable set mycolumn = 'X' where id = 2;
UPDATE mytable set mycolumn = 'X' where id = 3;
UPDATE mytable set mycolumn = 'X' where id = 4;
....
UPDATE mytable set mycolumn = 'X' where id = 2345;

This approach can be cached in SQL Area as 'UPDATE mytable set mycolumn = :1 where id = :2', but it might become a problem concerning the network traffic.

So - which approach should I select?

Here's one idea that may or may not fit your needs. Create, say, 3 statements:

s1: UPDATE mytable set mycolumn = ? where id in (?, ?, ...,?) -- 100 ?
s2: UPDATE mytable set mycolumn = ? where id in (?, ?, ...,?) -- 10 ?
s3: UPDATE mytable set mycolumn = ? where id = ? -- 1 ?

As long as you have more than 100 parameters unprocessed, use s1. When there are 10-99, use s2, and for the remaining ones use s3. This can be thought of as a compromise between the number of statements that have to be cached in SQL area, and the number of trips to the database. If we have 963 parameters that mean that 3 statements will be cached, and there will be 9+6+3=18 updates sent over to the database.

Many host languages also have some kind of batchexecute so that you pass
 an array of statements, if that exists in your host language the code is likely to be cleaner than what I suggested above.


Expand sparse table with self outer join on distinct values I have this:

g1       | g2        | x
---------+-----------+-----
New York | Monday    | 210
New York | Tuesday   | 258
Chicago  | Monday    | 30
Chicago  | Wednesday | 25
LA       | Thursday  | 40

But I want this:

g1       | g2        | x
---------+-----------+-----
New York | Monday    | 210
New York | Tuesday   | 258
New York | Wednesday | NULL
New York | Thursday  | NULL
Chicago  | Monday    | 30
Chicago  | Tuesday   | NULL
Chicago  | Wednesday | 25
Chicago  | Thursday  | NULL
LA       | Monday    | NULL
LA       | Tuesday   | NULL
LA       | Wednesday | NULL
LA       | Thursday  | 40

In other words, I want a record for every combination of DISTINCT g1 and DISTINCT g2. I should note that g1 and g2 are arbitrary groupings (not always location- or date-related). How do I fill in the table with these NULL values based on the cross product?

I don't know if this is any more straightforward, but I would change the syntax to use ANSI JOIN syntax instead of the (+)

select t1.g1,
  t1.g2,
  t2.x
from
(
  select distinct t1.g1, t2.g2
  from yourtable t1
  cross join (select g2 from yourtable) t2
) t1
left join yourtable t2
  on t1.g1 = t2.g1
  and t1.g2 = t2.g2
order by t1.g1, t1.g2

See SQL Fiddle with Demo


Oracle.exe (SHAD) process keeps increasing I have an Oracle Server 11g that we use for integration tests. For some reason, the processes count [select count(*) from v$process;] keeps increasing up to the limit, causing ORA-12514: TNS:listener does not currently know of service requested in connect descriptor error when the limit is reached. I tried increasing the processes limit to 300. But after a while the database will still reach this limit and have the same error. When I tried querying

select program, count(*) from v$process group by program order by count(*) desc;

I found that almost all of the processes are owned by Oracle.exe (SHAD)

PROGRAM                                                            COUNT(*)
---------------------------------------------------------------- ----------
ORACLE.EXE (SHAD)                                                       135
ORACLE.EXE (J001)                                                         1
ORACLE.EXE (Q001)                                                         1
ORACLE.EXE (PMON)                                                         1
ORACLE.EXE (GEN0)                                                         1
PSEUDO                                                                    1
ORACLE.EXE (LGWR)                                                         1
ORACLE.EXE (Q000)                                                         1
ORACLE.EXE (W000)                                                         1
ORACLE.EXE (DBRM)                                                         1
ORACLE.EXE (DBW0)                                                         1
ORACLE.EXE (DIAG)                                                         1
ORACLE.EXE (DIA0)                                                         1
ORACLE.EXE (MMAN)                                                         1
ORACLE.EXE (SMON)                                                         1
ORACLE.EXE (RECO)                                                         1
ORACLE.EXE (SMCO)                                                         1
ORACLE.EXE (W001)                                                         1
ORACLE.EXE (J000)                                                         1
ORACLE.EXE (PSP0)                                                         1
ORACLE.EXE (MMNL)                                                         1
ORACLE.EXE (D000)                                                         1
ORACLE.EXE (S000)                                                         1
ORACLE.EXE (CJQ0)                                                         1
ORACLE.EXE (MMON)                                                         1
ORACLE.EXE (QMNC)                                                         1
ORACLE.EXE (VKTM)                                                         1
ORACLE.EXE (CKPT)                                                         1

Any idea what's causing this process spam and how to fix it? Note, I found many users reporting the issue in the internet, see for example:

https://forums.oracle.com/forums/thread.jspa?threadID=839381
https://forums.oracle.com/forums/thread.jspa?threadID=969909
https://forums.oracle.com/forums/thread.jspa?threadID=2409737

But so far I haven't found any solution or even a sensible theory

If you're on Windows and have DCD (dead connection detection) enabled then this could be relevant to your case.  There is a note on Oracle support that talks about poor implementation of DCD on windows leaving behind a bunch of shadow processes.  Without going into details as I don't think I can republish that info here, the solution was to disable DCD.  So keep a monitor on the number of shad processes and disable DCD in sqlnet.ora.  Fairly quick and painless to test if that is the cause..


Fastest way to check if InnoDB table has changed My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance.

My application does about 500-1000 CHECKSUM TABLE statements per second in peak times, because the clients GUI is polling the database constantly for changes (it is a monitoring system, so must be very responsive and fast).

With MyISAM, there are live checksums that are precalculated on table modification and are VERY fast. However, there is no such thing in InnoDB. So, CHECKSUM TABLE is VERY slow.

I've hoped to be able to check the last update time of the table, Unfortunately, this is not available in InnoDB either. I'm stuck now, because tests have shown that the performance of the application drops drastically.

There are simply too many lines of code that update the tables, so implementing logic in the application to log table changes is out of the question.

Is there any fast method to detect changes in InnoDB tables?

For the table mydb.mytable, run this query:

SELECT update_time
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';

If you want to know what tables have changed in the last 5 minutes, run this:

SELECT table_schema,table_name,update_time
FROM information_schema.tables
WHERE update_time > (NOW() - INTERVAL 5 MINUTE);

Give it a Try !!!

UPDATE 2011-12-21 20:04 EDT

My employer (DB/Wweb hosting comany) has a client with 112,000 InnoDB tables. It is very difficult to read INFORMATION_SCHEMA.TABLES during peak hours. I have an alternate suggestion:

If you have innodb_file_per_table enabled and all the InnoDB tables are stored in .ibd files, there is a way to ascertain the time of the last update (up to the minute).

For the table mydb.mytable, do the following in the operating system:

$ cd /var/lib/mysql/mydb
$ ls -l mytable.ibd | awk '{print $4,$5}'

This timestamp is from the OS. You can't go wrong on this one.

UPDATE 2011-12-21 22:04 EDT
    [mysqld]
    innodb_max_dirty_pages_pct=0;

Add this to my.cnf, restart mysql, and all InnoDB tables will experience fast flushes from the buffer pool.

To avoid restarting, just run

mysql> SET GLOBAL innodb_max_dirty_pages_pct=0;

UPDATE 2013-06-27 07:15 EDT

When it comes to retrieving the date and time for a file, ls has the --time-style option:

$ cd /var/lib/mysql/mydb
$ ls -l --time-style="+%s" mytable.ibd | awk '{print $6}'

You can compare the timestamp of the file against UNIX_TIMESTAMP(NOW()).


How to create a view that refresh automatically I was triying to create a View by the simplest way like this:

Use SoccerDB;
GO
CREATE VIEW ExampleDBaseII
AS
SELECT ID, Cast(Name AS Varchar) as Name,Cast(City AS Varchar) as City,
FROM Team
GO

How can I do, so that view keeps its link to the table so if a change the table the view also changes, without creating it again or creating a new one. Is that possible? Im working with Sql Server 2008 R2 thanxs

Use WITH SCHEMABINDING in the view

CREATE VIEW ExampleDBaseII
WITH SCHEMABINDING
AS
SELECT T.ID, Cast(T.Name AS Varchar) as Name, Cast(T.City AS Varchar) as City,
FROM Team T
GO

This will disallow any changes to the underling tables that could affect the view

It also requires the use of qualifiers (schema, alias) and disallows the use of SELECT *. Which is a good thing (SO link)


SQL Server Job History Disappears After 5 Days I feel like this is a silly question but for the life of me, I can't track down what is going on. I have just taken over administration of several servers and on all of them, the job history is being retained for only 5 days. There is a cleanup maintenance plan but it's set for 5 week retention.

EXEC msdb.dbo.sp_syspolicy_purge_history is being run daily however, the History Retention Days is set to 0 so my understanding is that it's not doing anything anyway.

There has to be something really simple I'm missing here. Can anyone point me in the right direction?

That job has to do with policy-based management, not SQL Server Agent jobs in general. In Object Explorer right-click SQL Server Agent and select Properties, then move to the History tab. What does it say here:

You can adjust those settings so that it keeps more rows overall, more rows per job, and keeps history longer. I am guessing either you have a lot of jobs on this server or these settings have been changed.


How can I load 'utf8' into plperl by default? I am attempting to solve a problem explained in another question here.  On Debian 7.4 with Postgres 9.1.12, I am able to use the Perl qr operator just fine.  On Solaris 5.11 with Postgres 9.2.4, however, I am not able to.

As detailed in the question above, the following stored procedure fails on the Solaris installation:

REATE FUNCTION foo(VARCHAR) RETURNS VARCHAR AS $$
    my ( $re ) = @_;
    $re = ''.qr/\b($re)\b/i;
    return $re;
$$ LANGUAGE plperl;

With the following error:

ERROR:  Unable to load utf8.pm into plperl at line 3.
BEGIN failed--compilation aborted.
CONTEXT:  PL/Perl function "foo"

It is apparent that in the Debian installation of Postgres, 'utf8' is already loaded by default. I ran the following stored procedure on both systems:

CREATE FUNCTION perl_modules() RETURNS VOID AS $$
    warn join(', ',sort keys %INC);
$$ LANGUAGE plperl;

On the Debian system:

  WARNING:  Carp.pm, Carp/Heavy.pm, Exporter.pm, feature.pm, overload.pm, strict.pm, unicore/Heavy.pl, unicore/To/Fold.pl, utf8.pm, utf8_heavy.pl, vars.pm, warnings.pm, warnings/register.pm at line 2.

On the Solaris system:

  WARNING:  Carp.pm, Carp/Heavy.pm, Exporter.pm, feature.pm, overload.pm, overloading.pm, strict.pm, vars.pm, warnings.pm, warnings/register.pm at line 2.

I tried to load the utf8 module on the Solaris system by adding this to the postgres config file:

plperl.on_init = 'use utf8; use re;'

And that successfully loads the utf8 and re modules, as shown here:

  WARNING:  Carp.pm, Carp/Heavy.pm, Exporter.pm, XSLoader.pm, feature.pm, overload.pm, overloading.pm, re.pm, strict.pm, utf8.pm, vars.pm, warnings.pm, warnings/register.pm at line 2.

However the foo() stored procedure still fails:

dc=# select foo('foo');
ERROR:  Attempt to reload utf8_heavy.pl aborted.
Compilation failed in require at /opt/perl-5.18.0/lib/utf8.pm line 17.
CONTEXT:  PL/Perl function "foo"

What is the secret to successfully loading utf8, and its dependencies, in this Solaris environment?

The solution I found involes mimicing the utf8 module's AUTOLOAD function in the plperl.on_init setting:

plperl.on_init = 'use utf8; use re; package utf8; require "utf8_heavy.pl";'

This successfully loads and initializes the utf8_heavy.pl file before dropping into Postgres's safe mode for running perl scripts.


postgres ip address on auth logs I am looking how to strong security on my Postgres database implementing fail2ban

But checking on logs i can't see the ip adress of failed connection

2020-03-30 11:19:50.929 CEST [21167] postgres@test_ecofil FATAL:  password authentication failed for user "postgres"
2020-03-30 11:19:50.929 CEST [21167] postgres@test_ecofil DETAIL:  Password does not match for user "postgres".

checking for something  like this

Mar 30 11:29:19 xxxx sshd[13605]: Invalid user xxxxx from xx.xxx.xx.x

There is a way to do that? thanks

Try to add %h to log_line_prefix parameter (see https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LINE-PREFIX): it should display the host name or IP address.


MySQLRouter on Windows 10 Unable to connect to the metadata server I have set up a set of three MySQL 8.0.17 databases using group replication. They are all on my Windows 10 PC, using ports 3306,3307,3308.

I am now trying to set up mysqlrouter. I have installed it in c:/mysqlrouter/.

But when I run:

mysqlrouter --bootstrap root@localhost:3306

I simply get:

Error: Unable to connect to the metadata server: Error connecting to MySQL server at 127.0.0.1:3306:
Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060 (2013)

This is the same on all three ports, and using each of the different users (localhost, 127.0.0.1 etc)

I have confirmed that the MySQL user exists (I have root @ localhost / 127.0.0.1 / ::1 / % all set up) and I can login to MySQL using each of them.

Is there something I need to configure? I can't see anything obvous in the documentation?

I think I have solved it.

It appears that there is a difference between Group Replication and Innodb Cluster that perhaps needs to be made clearer in the documentation.

Anyway, I ran:

mysqlrouter --bootstrap root@[::1]:3306

and got:

Error: Expected MySQL Server '::1:3306' to contain the metadata of MySQL InnoDB Cluster, but the schema does not exist.
Checking version of the metadata schema failed with: Error executing MySQL query: Unknown database 'mysql_innodb_cluster_metadata' (1049)

This was the first clue that something was missing. Then I eventually found the following:

https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-from-group-replication.html

This tells you that you need to "convert" group replication to an Innodb Cluster by running:

var cluster = dba.createCluster('prodCluster', {adoptFromGR: true});

This tells you that:

A new InnoDB cluster will be created based on the existing replication group on instance 'localhost:3306'.

After that I tried bootstrapping mysqlrouter, and it worked.


Query or ETL process to insert data into fact tables in SSMS I'm working on creating a star schema for my project. I want to use it for reporting. I have used Google Play Store dataset from Kaggle.

The thing that I'm having trouble loading data into my fact tables. Below is my schema design. I was able to load data into my dimension table:

While you don't specify the specific problem related to loading your fact table, I'm going to assume the question approximates "how do I translate AppName to an AppKey so I can store that ID into my fact table?"

What you're looking to do is search your dimension table for a given value and return the key if it's found. Otherwise, we typically have a sentinel value to indicate this is an unknown value. Since you say you've loaded your dimension tables, then this shouldn't be an issue but it's a best practice.

If you are doing this through SSMS, then you would be writing a query that approximates the following

INSERT INTO dbo.Sentimental_Fact
SELECT
   URD.UserKey
,  AD.AppKey
,  D.Positive
,  D.Negative
,  D.Neutral
FROM
    dbo.kaggle_data AS D
    LEFT OUTER JOIN
        dbo.User_Rating_Dim AS URD
        -- I assume the appname in user rating is a typo and
        -- should be UserName
        ON URD.AppName = D.UserName
    LEFT OUTER JOIN
        dbo.App_Dim AS AD
        ON AD.AppName = D.AppName

If you were looking to use SSIS, I suggest you read the excellent Stairway to Integration Services by Andy Leonard. In short, you'll pull in in your base kaggle_data table into a Data Flow Task via an OLE DB Source Component and then use a series of Lookup Components to augment or enrich your data flow with the keys from the dimension tables. Finally, you'd land that into an OLE DB Destination (not an OLE DB Command)


What is the difference between optimize table and analyze table in mysql What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.

To expand on @MitchWheat's answer (+1 for directly answering first):

ANALYZE TABLE examines key distribution and stores them in INFORMATION_SCHEMA.STATISTICS.

OPTIMIZE TABLE performs ANALYZE TABLE after doing some table compression. The equivalent of OPTIMIZE TABLE mydb.mytable; if the table was MyISAM is this:

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

MyISAM

For the MyISAM table mydb.mytable in datadir /var/lib/mysql, you have the following files:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.MYD (data)
/var/lib/mysql/mydb/mytable.MYI (indexes)

OPTIMIZE TABLE mydb.mytable would shrink the .MYD and .MYI files for the table.

This is not the same for InnoDB. Here is how it is different:

InnoDB (innodb_file_per_table enabled)

Each table's data and indexes are stored in an external tablespace file. For datadir is /var/lib/mysql  and the table mydb.mytable, it would be stored as follows:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

When OPTIMIZE TABLE mydb.mytable is executed, mytable.ibd gets shrunk.

InnoDB (innodb_file_per_table disabled)

Only /var/lib/mysql/mydb/mytable.frm would exist. All the data and index pages for the table mydb.mytable are stored in the system tablespace file /var/lib/mysql/ibdata1.

When OPTIMIZE TABLE mydb.mytable is executed, the data and index pages are written contiguously in ibdata1. Unfortunately, this make ibdata1 grow in leaps and bounds.

See the Pictorial Representation from Percona CTO Vadim Tkachenko

UPDATE 2013-02-26 22:33 EST

You comment was

  I think, optimize table for innodb is not supported. I got a message, index will be recreated. How does it work?

I tried this out

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table dat (a int, primary key (a));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into dat values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.04 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> analyze table dat;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.dat | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.06 sec)

mysql> optimize table dat;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.dat | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.dat | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.14 sec)

mysql>

You are correct. You cannot run OPTIMIZE TABLE as a single operation. What InnoDB does instead is the following:

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;

You could also just run these steps yourself.

However, in all honesty, you should not have to run ANALYZE TABLE against an InnoDB table because each time a query is executed, the InnoDB Storage Engine performs an estimation on table cardinality based on passing through pages in the indexes. If there are a high number of INSERTs, UPDATEs, and DELETEs, then you will need to ANALYZE TABLE. When there is a high number of DELETEs, then ALTER TABLE mydb.mytable ENGINE=InnoDB; is needed to shrink the table.

I actually wrote posts about the futility of ANALYZE TABLE on InnoDB in certain instances:

Oct 16, 2011 : Suddenly have to rebuild indexes to prevent site from going down
Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?


Detect if transaction request for a certain database Query has come from a Virtual Machine? The application is deployed on different systems but I have to restrict the application to get run on VMs. Provided that I need not to formulate the strategy in code to detect it. Is there any way I can figure out at Database level if the transaction request is from VM to restrict application use?

Hint: I was googling and came across Logon Triggers. But I'm not sure if I could map this in my situation to figure out VM request!

Updated Actually the situation is: We have clients not paying their dues and still using our application illegally. We have built up the mechanism to restrict them on code level. But that mechanism,unfortunately, has been just merged in the code. The client could easily delude us by running old copy of our application on VM and they are good to go. We could have restricted them implementing firewall rules but they have their own network team. We are just taking protective measures to minimize such breaches at maximum.

There is no specific, built-in, method for determining if a client connection is coming from a virtual machine.  You'll need to identify the client connections via some other method, such as the name of the client machine.

For instance, if the VMs are named like "VM01", "VM02", etc, you could use a server-level logon trigger to deny connections from machines not named like "VM%".   Be aware, if the logon trigger doesn't work correctly you run the risk of preventing all client connections.  If the trigger doesn't accept connections from the server machine, SQL Server Agent won't be able to connect.  In short, there are a lot of "gotchas" using a logon trigger.


How to write this SQL query for a XML type? I am using SQL Server 2012 in which I have a table [abc] which  has  a column called [Information] of the [XML] type.

This column has values like -

<Information>
  <Groups>
    <Group Name="Monitor">
      <Items>
        <Item>
          <Name>Cid</Name>
          <Value>2323232323</Value>
        </Item>
        <Item>
          <Name>Tid</Name>
          <Value>6656565656</Value>
        </Item>
      </Items>
    </Group>
  </Groups>
</Information>

How can I write a SQL Query which can query this column and further extract the value [Tid] ?

What you want here is not entirely clear from your question. My guess is that you want the value from the <Value> node where the <Name> node is Tid.

Shred the XML using the nodes() function on /Information/Groups/Group/Items/Item and add a predicate [Name/text()="Tid"] to check the value for <Name>.

declare @T table(X xml not null);
insert into @T(X) values
('<Information>
  <Groups>
    <Group Name="Monitor">
      <Items>
        <Item>
          <Name>Cid</Name>
          <Value>2323232323</Value>
        </Item>
        <Item>
          <Name>Tid</Name>
          <Value>6656565656</Value>
        </Item>
      </Items>
    </Group>
  </Groups>
</Information>');

select I.X.value('(Value/text())[1]', 'bigint') as Value
from @T as T
  cross apply T.X.nodes('/Information/Groups/Group/Items/Item[Name/text()="Tid"]') as I(X);


Optimizing an audit table for inserts and deletes I'm trying to identify the best way to design a table for audit purposes. Basically I log several last events for many users in one table. There's a limit on the number of records for each user. The new records come, the older go.

Something like this:

CREATE TABLE Audit
(
  UserId INT NOT NULL,
  EventId INT NOT NULL,
  CreationDate DATETIME NOT NULL,
  UpdateDate DATETIME NULL,
  -- Other data fields
)

The question is what to do with indexes. I'm thinking of creating the clustered index on (UserId, EventId). But since user activity happens independently it would mean  inserts in the middle of the table and deletes in the middle of the table. Probably not good.

Another thought is to add an artificial AuditId field just to have new records get increasing numbers. Like this:

CREATE TABLE Audit
(
  Id INT, -- Becomes the clustered index
  -- The same as above
)

This way new audit entries will be appended to the end but deletes will still happen in the middle of the table. It's probably better than the first option but I'm not sure.

This table will be used frequently, basically every user activity is going to be logged (1 insert) and the oldest activity gets deleted in the same transaction (1 delete). This needs to be fast. Well, I want it to be instantaneous ideally and not noticeable performance-wise.

I also need to be able to retrieve the set of records for a particular user quickly. It can probably be covered by a non-clustered index.

I'm asking for advice in designing this table for optimal performance.

EDIT: I think I have missed something important to mention.

What I'm trying to track is not instantaneous in time but rather a period in time. There are several places in the system where I need this. Consider what the user is doing an activity of some sort that may span some period of time. If certain conditions are met then an existing activity is reused (refreshed, updated). I only wish to delete older sort of abandoned activities. For example, within 2 weeks one user may have issued like 50 of activities, but for another user to produce than many may take over a year. That's why I don't want a generally ordered log for all users together.

It is also not clear how should I cluster per datetime (as suggested). Do I do it on initial creation event or on the update event?

Typically to optimize inserts/deletes for such a table you would cluster on the datetime column. (You are tracking when these events happen, I presume.)

This way, inserts go to the end of the table always, minimizing the "bad" kind of page splits. Deleting or archiving data is easy because the clustered index will support range deletes, and these operations will not typically be locking the pages where you're performing inserts (unless you have escalation for some reason).

You will probably want other indexes like what you're describing to support queries, and maintenance of these of course will interfere somewhat with DML.

I'm not sure I understand why you only want to keep a certain number of rows for any UserId/EventId combination. If one user does a whole bunch of stuff today, do you really want to remove all the stuff they did yesterday, last week, etc., while you keep older data for users that are less active? Wouldn't it make more sense to just keep data based on time (e.g. retain two weeks of history)?

I'm also not sure why the delete absolutely needs to be coupled to the insert. Can't the delete be deferred to a background process instead of holding up the insert transaction?


Percona MySQL 5.7 | XtraBackup 2.4 - xb_load_tablespaces() failed witherror code 11 I am attempting to upgrade MySQL from 5.6 to 5.7 and everything seems to be working, but when I try to run xtrabackup I am now getting this error:

InnoDB: Allocated tablespace ID 12 for percona/checksums, old maximum was 0
InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to the directory.
xtrabackup: error: xb_load_tablespaces() failed witherror code 11

Nothing has changed, mysql user perms, system user perms, directory perms, nor structure, but now I am seeing this error. I cannot replicate the problem when I run the command as root, so it leads me to believe it is a system permission issue.

I temporarily disabled selinux to remove it as a distractor. I also granted ALL to the databacker mysql user in case it was a new permission that I was missing.

Here is the command I use:

su -s /bin/bash databacker -c "innobackupex --user=databacker --password=p4ssw0rd --socket=/var/lib/mysql/mysql.sock --stream=tar /tmp | gzip - > /tmp/wtf.tgz"

I tested perms as the databacker user and it all seems to work as expected:

su -s /bin/bash databacker -c "cd /tmp && touch test.txt"
su -s /bin/bash databacker -c "cd /var/lib/mysql && touch test.txt"

What am I missing?

I had a similar issue with an upgrade where I ended up with mixed libraries. Make sure you don't have dated library files still out there. I had to completely uninstall MySQL and reinstall it from scratch, and then it worked.


Update if no lock in postgres? Is there a way to issue a postgres update query that just does nothing if the row it wants to update is locked?  Or perhaps waits a very short time before giving up, just for this query?

I have a table with an updated_at field which is used for Russian doll caching.  Russian doll caching basically uses updated_at data in cache keys so you can invalid the cache by simply modifying the updated_at field. So for example if you have 2 tables:

books:
 id: int
 name: string
 updated_at: datetime

pages:
 id: int
 book_id: int
 text: text
 updated_at: datetime

You can now cache a book based on books.id and books.updated_at.  If a page changes you change the updated_at for the parent book and that book's cache is invalidated.

The issue is if a large number of pages for the same book are being modified at the same time, you end up with a flood of queries to update updated_at for the same book record.  As result, there is a ton of blocking while each update queries waits for a lock.  We actually don't need all the updates to happen since it is just being used to invalid that cache.  So if only one actual update happens per sec we are fine.

Any thoughts on how to achieve this?

I think what you want is NOWAIT

  To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table. Note that NOWAIT and SKIP LOCKED apply only to the row-level lock(s) — the required ROW SHARE table-level lock is still taken in the ordinary way (see Chapter 13). You can use LOCK with the NOWAIT option first, if you need to acquire the table-level lock without waiting.

That said, I'm not sure why you would want to do this. I've never had to do this. It sounds like a bad idea. It's quite normal to wait for a lock to update the rows. Retrying transactions is usually a longer-process. If you're afraid of issuing an update you don't want to a modified row, you don't have to worry about it. Dirty-writes are prevented.

UPDATE foo
SET baz = 1
WHERE bar = 2;

Will never update where a bar isn't 2. If it's waiting for a lock, after it gets the lock it'll check bar=2 and set baz=1 by rewriting the row.

I just can't see this as being more than trivia, or an extremely esoteric use-case.

I would keep it simple if you go the NOWAIT method becuase I don't think it matters anyway.

BEGIN;
  SELECT *
  FROM foo
  WHERE bar=2
  FOR UPDATE NOWAIT;

  UPDATE foo SET baz=1 WHERE bar=2;
END;

That whole transaction will rollback if it can't get the lock right away.


One table to rule them all, or thousand smaller? I have 10k users in my MySQL database and for every user i need to store the last 500 events.

Please advise me, how I should design my database? One MegaEventsTable or dedicated tables for every user?
Pros of many small tables:

I can pick fast, for example, 50 events by type from 500 entries than from 500*10k entries.
It is faster get top 500 entries, and kill all other entries in table for this user
I can shard my entries database some time in future

Contras:

i feel that 10k tables is HORRIBLE idea (and what if there will be 100K)

Exactly NOT 10k! - it always bad idea, for each open table MySQL will spend memory, cpu time, disk resources and etc

Much better give all this resources back to MySQL and it will manage queries for big table. With proper queries and indexes - millions and billions of rows in production databases.

One single table or may be few depend from other logic.

Possible solutions:
1 Table for active events
Second for archived

Other split ways like event types - also possible. But generally - for open table MySQL can spend same or more time than for search single event from VERY big table


SQL performance over MPLS GI have a question about performance of SQL over MPLS.

I have a client with 3 localizations. We want to centralize the server environment and migrate all of SQL databases to headquarter. Our client uses aplication based on SQL Server 2017 Standard. Between locations are MPLS Connection 100/100, latency (ping) between locations and headquarter are 5-7 ms. We anticipate that there will be about 150 connections to the database at one time. We're worried about the performance of this.

Of course we want to build cluster SQL based on SQL 2017 Standard and Dell storage array with SSD disks for SQL DB. Does anyone have experience in similiar scenario and could tell about it?

Generally, you want the applications as close as practical to the database server. (This advice holds true regardless of the app & database, so even though I'm a SQL Server guy, I'm going to try to write this to help folks looking for similar answers for MySQL, PostgreSQL, etc.)

Network issues boil down to two things: throughput at latency.

Throughput: the more data the app moves, the slower performance will feel. If the app only does small, targeted queries to bring back the minimum number of rows & columns that they need, then it'll feel fast. On the other hand, if the app has been doing SELECT * FROM TABLE with no filtering, and doing the filtering client-side, then the reduced network bandwidth can be a problem.

Good design: only bring back the minimum data you need for a screen
Bad design: bring back the entire table, filter it client-side (MS Access apps do a lot of this)

Latency: if the app manages its own transactions, latency will be a problem. Some applications start a transaction, then give the database server one row at a time to accomplish a goal. They parcel work out in small doses, and they handle failure/retry logic inside their code (rather than a stored proc.) This used to be fine when the database server was within 1-10 milliseconds of the app, but if you start to stretch that to 50-100ms, transactions will feel exponentially slower to end users.

Good design: build a stored procedure, have your app call it with all the parameters necessary to accomplish a business transaction
Bad design: start a transaction, insert one row, wait for the database server to acknowledge it, then send another row in another table, and another row, holding locks open the whole time

Throughput: the more concurrency you have, the slower performance will feel. If everyone at every office has the exact same usage patterns - like they all come back from lunch at exactly 1PM and start back working at the same time - then the throughput of your network pipe can be a problem. Now you can start to see how the above can start to snowball: if you have a chatty app that manages its own transactions, then it'll have an even tougher sending/receiving the data it needs to the SQL Server, and locks will be held open even longer as the shared pipe throughput gets smaller.

To monitor for that, start by looking at each SQL Server's network port utilization today. You can track that via Perfmon counters or Task Manager, looking to see what percentage of its existing network port it uses - and how that relates to the MPLS connection that you're proposing.


How to best manage Index Builds with Stats Updates? This is a follow-up to what I read on Is there any reason to stop transaction log backups during a maintenance window? in the answer proposed by sp_BlitzErik.

I use Ola Hallengren's indexing script and have the settings specified as per the below. I run this once a week through an Agent job.

@Databases nvarchar(max),
@FragmentationLow nvarchar(max) = NULL,
@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 500,

I know as apart of an Index Re-build an update stats is done automatically, but I have the following database properties set against all my databases which I believe should take care of updating stats:

Auto Create Statistics = True
Auto Update Statistics = True
Auto Update Statistics Asynchronously = True

What is generally best practice here for these settings and regularly updating stats though? Should you update stats on a nightly basis? I'm not sure how to measure whether stats should be updated or not which is why I have those database properties set.

I see in the answer by sp_BlitzErik he mentions "You're still going to want to update stats regularly though, and you can do that with this command:"

with the following command, but update stats regularly is very general.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
                          @FragmentationLow = NULL,
                          @FragmentationMedium = NULL,
                          @FragmentationHigh = NULL,
                          @UpdateStatistics = 'ALL',
                          @OnlyModifiedStatistics = 'Y',
                          @StatisticsSample = NULL,
                          @LogToTable = 'Y';

I use Ola Hallengren's indexing script and have the settings specified as per the below. I run this once a week through an Agent job.

Let's break down the command you're running:
@FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 500,

Even once a week, this is absurdly aggressive. I know the 5/30% comes from ancient Microsoft advice, but it's just that --ancient. It's time to move on.
You're taking the time to defragment indexes that are 500 8KB pages. That's a 4MB table. If your hardware has issues reading 4MB into memory, or keeping a 4MB table in memory, the answer isn't index maintenance. That's why I crank this up to at least 5000.
Modern hardware like SANs, SSDs, and non-32-bit servers that can house amounts of RAM > 4GB simply don't have the same data access issues that spinning platter drives from the early 2000s had.
We're talking record players vs. CDs.
Index rebuilds should happen extremely infrequently, and only to either correct a problem with an index, or change a setting. Here's the thing: 30% fragmentation isn't a real problem. It's just something DBAs do because they've been told to, and it's something they can measure.
So here's my question to you: how much time and resources does your server expend on index maintenance, and how much does that reduce time and resources consumed by queries?
If you can measure that, you can rebuild and reorg as much as you want.

... but I have the following database properties set against all my databases which I believe should take care of updating stats...

Kinda sorta. Auto-update stats (even async) happens when 20% of the table data + 500 rows (assuming the table is > 500 rows) changes. If you have a table with a million rows in it, that's 200k rows. You can dynamically decrease that threshold by using Trace Flag 2371, but auto update stats uses the default sampling algorithm, which may not be enough for tables with heavily skewed data.

but update stats regularly is very general.

Well, yeah, I'm answering questions about a server I've never looked at. I prefer nightly stats updates, but I've seen servers that needed them more regularly than that.
So what should you do?

Start by dialing back index maintenance to the commands I posted in the question you're referencing
If you run into problems, decrease fragmentation thresholds until they stop. If no one says anything, run index maintenance less frequently, until maybe, just maybe, you never run it at all.

Update stats nightly
Start with the default threshold. If you find that the full stats update that occurs with a rebuild is necessary, use the CommandLog table to find which tables and indexes were regularly being rebuilt, and start focusing on those. They'll typically be indexes for "big" tables, with tens of millions of rows.

This is about as specific as I can get about a server I've never seen. You're going to have to take the experimentation from here.
See also my post Why most of you should leave Auto-Update Statistics on


How to rank an aggregation in each newly formed group? The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL.

Let's say I have a simplified ERD that looks as follows:

This is a database of a hypothetical movie streaming website that keeps the data about its customers, invoice, movies and their genres. Each customer has a unique CustomerID, each purchase has a unique InvoiceID, each movie has a unique MovieID, each genre has a unique GenreID. One MovieID is only associated with one GenreID. One customer can purchase many movies, so one Invoice can contains many MovieIDs.

I would like to show the top 2 most popular genres (in terms of total count of InvoiceID) in each of the following continents/regions:
Africa (Egypt, South Africa), Asia (China, Japan), Europe (France, Germany).

An example of the Customer and Genre tables are as follows:

The resulting table that I would like to have looks more or less like this (Region means Continent):

How exactly the resulting table look like is not important, I would just want the top 2 genres grouped by each region.

Here are some of my effort in producing the query:

WITH t1 AS (
  SELECT c.Country,
  CASE
    WHEN (
      (c.Country = "China" OR c.Country = "Japan") THEN "Asia"
    )
    WHEN (
      (c.Country = "Egypt" OR c.Country = "South Africa") THEN "Africa"
    )
    ELSE "Europe"
  END AS region
FROM Customer c
GROUP BY 1
)

SELECT DISTINCT region
FROM t1;

I could only produce the new region groups but got stuck at how to rank the genres grouped by the regions. I have a difficulty in showing the ranking of genres (based on total count of InvoiceID) in each region. In other words, in each of the regions, how many customers purchased each genre. I have just started learning SQL that includes different kinds of JOINS, SUBQUERY, AGGREGATIONS, GROUP BY, ORDER BY and Common Table Expression but somehow I cannot produce the result I want. Could somebody please give some light on this? Thank you in advance for the help.

Use grouping to get the count per country, and a window function to get the rank:

WITH t1 AS (
  SELECT CASE
         WHEN Country IN ('France', 'Germany')     THEN 'Europe'
         WHEN Country IN ('China', 'Japan')        THEN 'Asia'
         WHEN Country IN ('Egypt', 'South Africa') THEN 'Africa'
         END AS Region,
         GenreName,
         COUNT(*) AS Views
  FROM Customer
  JOIN Invoice USING (CustomerID)
  JOIN Movie USING (MovieID)
  JOIN Genre USING (GenreID)
  GROUP BY Region, GenreName
),
t2 AS (
  SELECT Region,
         GenreName,
         Views,
         row_number() OVER (PARTITION BY Region ORDER BY Views DESC) AS Rank
  FROM t1
)
SELECT Region, GenreName
FROM t2
WHERE Rank <= 2
ORDER BY Region, Rank;


Monitor disk usage with MongoDB Ops Manager We use MongoDB Ops Manager 1.6.2.251.

I searched Activity -> Alert Settings. Is there a way to monitor disk usage?

To get an alert if this changes to 90%:

# df -h /data/
Filesystem                Size  Used Avail Use% Mounted on
/dev/mapper/vdatavg-data   99G  295M   99G   1% /data

MongoDB Ops Manager 3.4.0 (released November, 2016) includes monitoring for hardware metrics (cpu, disk usage, and I/O utilization) via the Automation agent. For more information, see: System and Disk Alerts.

Prior versions of Ops Manager did not include hardware monitoring and required the use of an external tool (munin-node) for CPU & disk metrics. If you are still using an older version of Ops Manager and have installed Munin-node for monitoring CPU & I/O stats you could look into using the Munin df plugin to track free disk space. Munin supports sending alerts via email as well as through Nagios and external scripts.


Import/export wizard code page error Using the Import/Export Wizard, I ended up with an annoying problem:

  Error 0xc02020f4: Data Flow Task 1: The column "pinNumber" cannot be processed because more than one code page (874 and 1252) are specified for it.

Looks like a different codepage (SQL_Latin1_General_CI_AS vs Latin1_General_CI_AS), so I changed those to match: Latin1_General_CI_AS for both.

However, the wizard still throws an error and detects two codepages - I even checked the tables and both source and destination show the same Latin1_General_CI_AS collation.

Problem can be in field level as it was for me.

In a source database, a field in a table had a different collation in a varchar datatype.

If you choose < database default>  for collation property of the fields, the problem may be solved.


Large difference in disk space used by partitions I have a partitioned table that contains about 155 million records total. It's partitioned by year and the partitions for both the current and previous years contain about 25 million records each.

However, the current year partition is using over 15 GB of disk and the previous year partition is only using 2.5 GB.

Each night, roughly 80 k records are loaded by SSIS into the current year partition. I also have a partition aligned index that I drop and rebuild before and after loading the table. I don't have too much practice with partitioned tables, so what might cause the big discrepancy in disk space?

[int] IDENTITY(1,1) NOT NULL,
[varchar](10) NULL,
[datetime] NULL,
[datetime] NULL,
[int] NULL,
[int] NULL,
[varchar](2) NULL,
[varchar](20) NULL,
[int] NULL,
[int] NULL,
[varchar](11) NULL,
[int] NULL,
[varchar](10) NULL,
[int] NULL,
[varchar](80) NULL,
[varchar](10) NULL,
[varchar](max) NULL,
[varchar](10) NULL

What might cause the big discrepancy in disk space?

Typically this is caused by rows that have been deleted. Heaps do not reclaim space for data pages emptied by DELETE operations unless a table lock is taken at the time of the deletion. Even then, other factors like the possibility of row-overflow data, or an enabled row-versioning isolation level can prevent space being reclaimed.

Another factor that usually has a much smaller impact on space usage is that rows with variable-length columns that expand may not fit on the original heap page, in which case the row is forwarded to another page. Forwarded rows are more usually associated with scan performance issues than pure space usage, however.

Overall, tables that experience significant deletions (without the space being reused quickly by new rows) and/or variable column updates that do not fit in place are often better configured with a clustered index. Clustered tables usually deallocate empty pages very quickly.

You can use the sys.dm_db_index_physical_stats DMV to see physical details for a heap or index:

SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.index_type_desc,
    DDIPS.alloc_unit_type_desc,
    DDIPS.avg_fragmentation_in_percent,
    DDIPS.fragment_count,
    DDIPS.avg_fragment_size_in_pages,
    DDIPS.page_count,
    DDIPS.avg_page_space_used_in_percent,
    DDIPS.record_count,
    DDIPS.avg_record_size_in_bytes,
    DDIPS.forwarded_record_count
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.t1', N'U'),
        0,
        NULL,
        'DETAILED'
    ) AS DDIPS;

The following (trivial) example shows a heap DELETE not releasing any empty pages:

SET NOCOUNT ON;
CREATE TABLE t1 (c1 char(8000) DEFAULT 'a');
GO
INSERT INTO t1 DEFAULT VALUES;
GO 1000

-- 1000 pages allocated
SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.page_count
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.t1', N'U'),
        0,
        NULL,
        'DETAILED'
    ) AS DDIPS;

-- Delete all the data from the heap
DELETE FROM t1;

-- Still 1000 pages allocated
-- Even though the table is empty
SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.page_count
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.t1', N'U'),
        0,
        NULL,
        'DETAILED'
    ) AS DDIPS;
GO
DROP TABLE dbo.t1;

If you try the example again, but with a table lock (DELETE FROM t1 WITH (TABLOCK)) the deletion frees all empty pages (assuming the database does not have the READ_COMMITTED_SNAPSHOT option set to ON etc.)


How to decide when is use ERD or UML for data modeling? A UML diagram can be used both to model database designs and class designs, but an ER diagram should be used exclusively in database problems. How do you pick one graphical representation over the other?

I think you answered your own question. E-R diagrams are meant to be problem solving diagrams for the sake of creating a database schema, whereas UML are supposed to model a completed db. I personally don't place a huge amount of value on ER diagrams, as I would personally do such brainstorming in my head, but I imagine many people would swear by them. As such, the answer is UML as a general rule, but ER can hypothetically be useful in the construction of databases.


Two-way replication between SQL Server 2005 and SQL Server 2008 We currently have a SQL Server 2008 and SQL Server 2005 participating in one-way transactional replication of a single database.

If possible we would like to set this up as a Two-way replication. What would be the preferred method of replication to achieve this? There is a moderately high latency between these servers. However, the growth of the database to be replicated is minimal.

From your comment update, it sounds like Merge replication could work well for you, mostly due to the minimal updates. It's a lot simpler to set up and manage than two-way transactional (which is what the scary link I mentioned deals with--I should have left that for later).

In a nutshell: https://blogs.technet.microsoft.com/meamcs/2011/01/06/merge-replication-step-by-step/


How to transfer files at remote location using sql server job? I have a job configured in which it transfers the file at remote location.
My command is running absolutely fine from the command prompt but when I configured it in sql server job it gives log on failure unknown user name or bad password.
I am not able to find out the exact reason that why this file is not being transferred.

If there is an error in my code then it should give the same error from the command prompt.
Can anyone help me understand why I am getting the error?

that's probably because the user trying to move the files is the user that is running the job (probably the user that SQL Server agent is configured to run). Try giving read permission to this user on the folder you are reading the files from and write on the folder you are writing the files.


sp_WhoIsActive vs sp_BlitzFirst @ExpertMode = 1 I use sp_WhoIsActive and sp_BlitzFirst (with ExpertMode paramter set to 1) to get an overview of what is happening on the SQL Server in real time. I find these to be an upgrade on sp_who, sp_who2 etc...

Can anyone explain what, if any difference there is between the two. They seem to show the same thing, albeit with a slightly different set of columns. Also, BlitzFirst shows some other information in it's output as a separate output table

In this video, Brent shows his triage process in which he uses both sp_WhoIsActive and sp_BlitzFirst which makes me think there must be some difference but unless I missed something in the video I can't see why sp_WhoIsActive is required

They're just different scripts.

Adam's sp_WhoIsActive is powerful, free, and copyrighted. I've used it for at least a decade, and I love it, and I still continue to use it personally. However, it's not open source, which means you can't contribute code to it, and you're not allowed to distribute it to others.

We needed something we could redistribute to our clients for commercial purposes, and let them distribute it to THEIR clients (because some of our clients are software vendors and service providers). That's why we wrote sp_BlitzWho and licensed it with the MIT License, enabling us (and others) to do just about anything with it.

They're both great tools. I still teach people how to use sp_WhoIsActive because most folks don't need the ability to redistribute code or make improvements to it.

About why I still use sp_WhoIsActive - when I troubleshoot blocking chains, I prefer sp_WhoIsActive because of its get_locks parameter that shows locking in objects. We don't need the ability to edit that code or redistribute it, so we haven't bothered building that logic into sp_BlitzWho.


During table migration of tables from one tablespace to another, does it needs free space to do the alter table move? During migration of tables from one tablespace to another (all datafiles in the same disk/partition/mount), does it needs free space to do the alter table move?

exemple:. I have 30gb of free space on that disk, table of 1TB in tablespace_1, i wanna move the table using alter table move to tablespace_2.. can i do it or do i need to have more space available? if so, how much free space available?

Thx a lot :)

Table move copies the whole table into a new segment, then drops the old segment. This means you need enough space to store the old segment and the new segment at the same time. For a 1 TB table full of data, you need another 1 TB free space for the move to succeed.


DBCC CheckDB Consistency error on a empty table What could be the reason behind a DBCC CheckDB reporting a consistency error on a empty table in sql server 2008? This DB is restored from sql server 2000. DBCC CheckDB on sql server 2000 doesnot report any errors.

It would be a lot easier if you post what error are you getting?

Usually the corruption was already there in the 2000 version but the latest and improved version of DBCC catches the errors that werent reported by the 2000 version of DBCC. Usually running DBCC UPDATEUSAGE might fix it and if the error doesnt go away you have to check what the specific error is and try to solve it accordingly. For all things CHECKDB i suggest you dig into all paul randal checkdb related blogs.


Design a table of items with many features; how to group those features I have this table Storeswhich represents stores. Those stores are managed for many types of persons, like Administrators, Managers, Supervisors and so on, i.e. Each store has an administrator, a manager, a supervisor

I could make N tables for each type/position and link them by their respective id to the Stores table, but the requirement says it is possible that the positions could increase.

With that being said, is the following approach correct? Create a Stores_structure table with a foreing key 'id_position_fk' linked to a Positions table. That Positions table has listed:

id_position | description
_______________________________
1           | Administrator
2           | Manager

and so On. And the Stores_structure (still looking for a better table name) has:

id     | id_position_fk  | Name         | phone_number
_____________________________________________________
1      |  1              | John Smith   |
2      |  1              | Paul Smith   |
3      |  2              | Robert Smith |

And the id from Stores_structure is linked in the Stores table with a many to many relationship

The first red flag I see is with that approach: it is posible to have two 'Administrators' for a single store; other thing I'm concerned about is the 'excessive' "normalization" of the many-to-many table

Is there a better approach?

Up front disclaimer: I am a SQL DBA, so exact solutions may differ.

Two approaches that I have seen to this. The first is to ask if a single person can be an admin for more than one store. Can Paul Smith have store 27 and 19? or just one? If that is the case, then I would add the needed fields to that persons' profile.

A second way of approaching it is to have a join table that would look like this:

Join_ID |  Admin_ID |  Store_ID | Admin_Type_ID

This way would be a many-to-many situation, where a single person can be assigned to multiple stores, while a single store can have multiple admins of the same or different types. You could also use this to have one person be a general manager at one store, but a network admin at another.

The overhead for this should not be too different than anything else going on in the database, and should only take a few extra minutes to plan out how the stored procedures would read and write to it.


Specified key was too long; max key length is 1000 bytes in mysql 5.6 one of the application server is internally creating the database on my mysql  but when ever the following create table command gets executed

 CREATE TABLE ofRoster (

    rosterID              BIGINT          NOT NULL,
    username              VARCHAR(64)     NOT NULL,
    jid                   VARCHAR(1024)   NOT NULL,
    sub                   TINYINT         NOT NULL,

    ask                   TINYINT         NOT NULL,
    recv                  TINYINT         NOT NULL,
    nick                  VARCHAR(255),
    PRIMARY KEY (rosterID),
    INDEX ofRoster_unameid_idx (username),
    INDEX ofRoster_jid_idx (jid)

 )

i am getting following error

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

i have set my default engine to MyISAM because i was getting following error in InnoDB

specified key was too long max key length is 767 bytes

my current engines are as follows

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

now i really don't know how would i get rid off it as the application server itself is creating database automatically in mysql so i dont have control over it.

i am using Server version: 5.6.10 MySQL Community Server (GPL) version

character set of column

+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | latin1                                                 |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | latin1                                                 |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.6.10-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+

You can increase the maximum InnoDB index prefix size in MySQL 5.6 to 3072 bytes by setting innodb_large_prefix to ON along with other settings that you'll also need in order to enable that one, discussed here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

These changes should allow these indexes to be valid for InnoDB tables.

With a character set of (I assume) utf8, a VARCHAR(1024) would need 1024 x 3 = 3072 bytes for its index.

Updates:

I incorrectly showed the value of the variable as Yes when it should have been ON.

But also:

  I think it needs 3072+2 – ypercube

When I thought about this, it sounded correct, because 1 byte is needed to store the size of the value when the value is 255 bytes or less, and 2 bytes are needed otherwise.

However, testing reveals that this isn't the case, in this case -- InnoDB using the COMPRESSED row format from Barracuda can actually index the full size of a VARCHAR(1024)... so either they've documented it strangely or the COMPRESSED row format stores the length out-of-band along with another block of metadata, so it doesn't count in the total bytes in this format.

COMPRESSED doesn't truncate the index into a prefix index and throw a warning until you go to VARCHAR(1025).

+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 3072 bytes |
+-------+------+----------------------------------------------------------+

It's nice the way it throws a warning instead of throwing an error, but that doesn't help us here because this still requires the explicit ROW_FORMAT declaration to trigger this behavior.

So, my initial answer is still wrong, because you have to explicitly add ROW_FORMAT=COMPRESSED to the end of the table definition.  Otherwise you still get the same old error.

Actually, you get two different errors in 5.6.10.  If you try to create a table with a fully-indexed VARCHAR(1024) you get this:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

...but if you try with a fully-indexed VARCHAR(1025) you get this:

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

That's sloppy code but the bottom line is that my answer doesn't actually fix this problem.

I don't see a way to use ROW_FORMAT=COMPRESSED by default, nor does it seem like a good idea if it were possible, and I'm inclined to suggest that the answer is...

...there's not a readily available workaround here.  My other thought was "character sets" but the difference between latin1 and utf8 still isn't sufficient to explain 1,024 vs 1000 or 767.  I'll happily get behind a better idea but at the moment, I can't think of a version of MySQL Server that this code would work properly on.


Databases in Suspect state after enabling TDE A few of my databases are in a suspect state. How can I get them online? I don’t know what info is relevant and what isn’t, but I’ll give as much as I can. Here is what happened:

I enabled TDE in about 100 databases by the below. (The cert tde_certificate was created on another SQL server, same version, if it matters.)

use master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPass'
--the key/cert was generated on another sql server (same version), if it matters
CREATE CERTIFICATE tde_certificate
FROM FILE = '\\dir\cert.bak'
WITH PRIVATE KEY (
file = '\\dir\key.pvk'
,DECRYPTION BY PASSWORD = 'StrongPass')

--then for each database:
use [SomeDb];
create database encryption key with algorithm = aes_256
encryption by server certificate tde_certificate

alter database [SomeDb] set encryption on

Then TDE appeared to be “stuck” at 0 percent, for all TDE enabled databases. It was in this state for a while, at least a couple hours (some dbs are really small too).

The error message in log showed:

  Please create a master key in the database or open the master key in the session before performing this operation. Database encryption scan for database ‘dbname’ was aborted. Reissue ALTER DB to resume the scan.

After there was a failover to the other node. All databases, except for the system and non-tde enabled ones were stuck in "RECOVERY_PENDING”.

I was able to get most of the databases online by opening the master encryption key and setting the db’s online by:

use master
open master key decryption by password = 'ReallyStrongPassword'
alter database [SomeDb] set online

This worked for all of the databases except for 3. Those db’s are now in a SUSPECT state.

I tried a repair with no luck:

use master
open master key decryption by password = 'ReallyStrongPassword'

ALTER DATABASE [SomeDb] set emergency
ALTER DATABASE [SomeDb] set single_user
DBCC CHECKDB ([SomeDb], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
alter DATABASE [SomeDb] SET multi_user
alter DATABASE [SomeDb] SET online

  Results:
  Msg 926, Level 14, State 1, Line 30
  Database 'SomeDb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
  DBCC results for 'SomeDb'.
  CHECKDB found 0 allocation errors and 0 consistency errors in database 'SomeDb'.

Also, I tried to restart the encryption scan, no luck:

use master
open master key decryption by password = 'StrongPass'
DBCC TRACEON(5004)
DBCC TRACEOFF(5004)

exec sys.sp_resetstatus
    @DBName = 'SomeDb'

ALTER DATABASE SomeDb
SET ENCRYPTION on

  Result:
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Warning: You must recover this database prior to access.

  Msg 5069, Level 16, State 1, Line 131
  ALTER DATABASE statement failed.
  Msg 9001, Level 21, State 1, Line 131
  The log for database 'SomeDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

I noticed that whenever I fail over to the other node, all databases (except system) are all back in a RECOVERY_PENDING state. I need to open the encryption key and explicitly set the database online to get them online (all except for the 3 SUSPECT dbs). This happens every time.

I am on SQL Server 2008 R2 Enterprise, clustered with 2 nodes. I was able to enable TDE using the same cert in 4 other environments without any issues.

Any ideas on what I can do to get these databases online?

Edit: Here are the results from the error log. I filtered it down to "one of each" databases. One that was suspect and another that was in a recovery_pending state.

http://pastebin.com/wasNk7Xw

Edit: The files referenced within sys.master_files physically exist. I noticed that the STATE = ONLINE as well, sys.databases.state_desc = SUSPECT.

For the 3 suspect databases can you check the log files referred to in sys.master_files physically exist on disk and have the same access permissions as the other working database log files? Just that one of the error messages you posted said the log file is missing, and unless it's a red herring, that would make their problem separate to your TDE issues and explain the suspect status.

I've noticed this in your log: 04/13/2016 15:18:23,spid61,Unknown,Database SuspectDb was shutdown due to error 9001 in routine 'XdesRMReadWrite::RollbackToLsn'.

This can happen if there are transactions in the log file which cannot be rolled back due to a lack of space, forcing the db into suspect mode. Can you confirm the underlying disk has free space, and also that you have not set and hit a size limit on that database's files (e.g. that auto-grow is enabled and set to grow by a sane fixed amount)?


Split rows and assign a specific value to each group I have a scenario where i need to split rows in a table and assign certain values to a column for each group.
 The table name is customers(contains around 300K rows) and i need to split the customers in this table into 9 groups and update each group with a specific value in the "Value" column.

Anyone have any idea on how to do this using PostgreSQL?

This is the table structure(only a part)

Create table customers
(Customer_id  integer,
Customer_email TEXT,
updated_time TIME STAMP WITHOUT TIME ZONE,
Value TEXT
)

Insert into customers Values(1,'sss@gmail.com',now(),'')
Insert into customers Values(2,'abc@gmail.com',now(),'')
Insert into customers Values(3,'retro@gmail.com',now(),'')
Insert into customers Values(4,'als@gmail.com',now(),'')
Insert into customers Values(5,'s123@neferce.com',now(),'')
Insert into customers Values(6,'tt@gmail.com',now(),'')
Insert into customers Values(7,'a123@gmail.com',now(),'')
Insert into customers Values(8,'sabc@gmail.com',now(),'')
Insert into customers Values(9,'67890@gmail.com',now(),'')
Insert into customers Values(10,'qwert@gmail.com',now(),'')
Insert into customers Values(11,'dfg@gmail.com',now(),'')
Insert into customers Values(12,'fvc@gmail.com',now(),'')

The above is the sample table structure and values and now i want the customers in this table to be split into 4 groups(in real i have to split into more groups say like 9 or 10) and update the Value column with a specific text for the split groups.

so the first 4 rows will be assigned with a value 'dos-1-1'
second set of 4 rows with a value 'dos-2-2'
Third set of 4 rows with a value 'dos-3-3'
Fourth set of 4 rows with a value 'dos-4-4'

Below is the desired result,

ID        Email              Date         Value
1   "sss@gmail.com"     "2019-06-14"    "dos-1-1"
2   "abc@gmail.com"     "2019-06-14"    "dos-1-1"
3   "retro@gmail.com"   "2019-06-14"    "dos-1-1"
4   "als@gmail.com"     "2019-06-14"    "dos-2-2"
5   "s123@neferce.com"  "2019-06-14"    "dos-2-2"
6   "tt@gmail.com"      "2019-06-14"    "dos-2-2"
7   "a123@gmail.com"    "2019-06-14"    "dos-3-3"
8   "sabc@gmail.com"    "2019-06-14"    "dos-3-3"
9   "67890@gmail.com"   "2019-06-14"    "dos-3-3"
10  "qwert@gmail.com"   "2019-06-14"    "dos-4-4"
11  "dfg@gmail.com"     "2019-06-14"    "dos-4-4"
12  "fvc@gmail.com"     "2019-06-14"    "dos-4-4"

Above is the situation and in real the table consists of around 300k rows and need to split it into 9 groups and update each group with a  specific text.

WITH cte AS ( SELECT Customer_id, NTILE(4) OVER ()::TEXT "Value" FROM customers )
UPDATE customers
SET "Value" = 'dos-' ||  cte."Value" || '-' ||  cte."Value"
FROM cte
WHERE customers.Customer_id = cte.Customer_id;

fiddle

PS. It's recommended to use some ORDER BY in OVER clause (for example by Customer_id) - without it the result is not deterministic...


Transfer all tables from a schema to other schema I have two schema. Schema1 and Schema2

How would I transfer all the tables from schema1 to schema2?

Execute this for all of the tables:

alter schema2
transfer schema1.table1;

To programmatically get the statements to execute:

declare
    @SourceSchemaName sysname,
    @DestinationSchemaName sysname,
    @AlterStatements varchar(max);

set @SourceSchemaName = 'NewSchema';
set @DestinationSchemaName = 'HumanResources';
set @AlterStatements = '';

select
    @AlterStatements +=
        'alter schema ' + quotename(@DestinationSchemaName) +
        ' transfer ' + quotename(schema_name(schema_id)) + '.' + quotename(object_name(object_id)) +
        char(13) + char(10)
from sys.tables
where schema_id = schema_id(@SourceSchemaName);

print @AlterStatements;


Using SQLCMD with Variables Im trying to do some general script that i can use to run the same INSERT/UPDATE or DELETE statements on different site databases.
This was my first attempt which works just fine, but i have to copy and paste everything.

:CONNECT czasql-001
USE [Lps_FinishPack_Cz]
GO
SELECT * FROM [config].[LpsPlant]

GO

:CONNECT LS_LPS_DK
use [LPS_FINISHPACK_NY]
GO
SELECT * FROM [config].[LpsPlant]

use [LPS_FINISHPACK_DK]
GO
SELECT * FROM [config].[LpsPlant]

use [LPS_FINISHPACK_SUPPLIER]
GO
SELECT * FROM [config].[LpsPlant]

GO

:CONNECT LS_LPS_372
use [LPS_FINISHPACK_MO]
GO
SELECT * FROM [config].[LpsPlant]

GO

:CONNECT LS_LPS_678
use [LPS_FINISHPACK_678]
GO
SELECT * FROM [config].[LpsPlant]
GO

But i like to have something where it's more general, were i don't have to copy and paste. I have tried something like this:

 DECLARE @tbl TABLE (Id int IDENTITY (1, 1),ServerName sysname, DbName sysname, IsDone BIT DEFAULT(0))
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'CZASQL-001', N'[Lps_FinishPack_CZ]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_DK]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_NY]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_Supplier]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_372', N'[Lps_FinishPack_MO]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_678', N'[Lps_FinishPack_678]')

  WHILE (SELECT COUNT(*) FROM @tbl WHERE IsDone = 0) > 0
    BEGIN

    DECLARE @selectedRow INT = (SELECT TOP 1 Id FROM @tbl WHERE IsDone = 0)
    --DECLARE @ServerName NVARCHAR(50)= (SELECT ServerName FROM @tbl WHERE Id = @selectedRow)
    --DECLARE @DatabaseName NVARCHAR(50) = (SELECT DbName FROM @tbl WHERE Id = @selectedRow)
     DECLARE @ServerName sysname= (SELECT ServerName FROM @tbl WHERE Id = @selectedRow)
    DECLARE @DatabaseName sysname = (SELECT DbName FROM @tbl WHERE Id = @selectedRow)

    --:SETVAR DatabaseName @DatabaseName
    --:SETVAR ServerName @ServerName
    print CONVERT(NVARCHAR(100),@selectedRow)
    :CONNECT @ServerName

       USE @DatabaseName
       GO
      SELECT * FROM [config].[LpsPlant]
     GO

     UPDATE @tbl SET IsDone = 1 WHERE Id = @selectedRow
END;

I have also tried where ServerName and

You could try a "script the script" approach by using the SQLCMD :out command to redirect output, with :connect and optionally :r to read the output, something like this:

SET NOCOUNT ON

DECLARE @tbl TABLE (Id int IDENTITY (1, 1),ServerName sysname, DbName sysname, IsDone BIT DEFAULT(0))

INSERT INTO @tbl (ServerName,DbName) VALUES (N'CZASQL-001', N'[Lps_FinishPack_CZ]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_DK]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_NY]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_Supplier]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_372', N'[Lps_FinishPack_MO]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_678', N'[Lps_FinishPack_678]')

:out d:\temp\temp.sql

SELECT
    ':connect ' + serverName + '
USE ' + dbName + '
SELECT * FROM [config].[LpsPlant]
GO
'
FROM @tbl
GO

:out STDOUT
GO

-- Optionally read/run the output
--:r d:\temp\temp.sql

Run the script then go and have a look in the temp.sql file. Mine looks like this:

If you are happy with the output, uncomment the :r to run it, or just run/edit the script you have created.

Hope that makes sense.


SQL Server Wildcardless LIKE Optimization In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query.

If I was to have all queries generated as like clauses, such as:

AND Uri LIKE @Uri

If Uri contains no % , is the optimizer smart enough to treat that as WHERE Uri = @Uri or do I have to handle that application side?

I realize that the optimizer could change out from under me someday and this is a bit sloppy, but I am still curious.

Example Plans

I have done a one off test that seems to suggest that this is maybe the case with the >= ... <= conditions:

Vs. Where Clause:

So they both trigger index Seeks on that contrived Index. Is >= ... <= the same as = in this case, and will this always happen?

Most likely yes, if there are proper indexes in place and statistics are up to date, then the query optimizer should be able to use the existing indexes and best operation when possible (seek).
You will have detailed information in these 2 articles:
Index usage by %Like% operator – Query Tuning on MSDN and
SQL Performance - Indexes and the LIKE clause  on myitforum.com.