Which edition of SQL Server express I am looking to learn SQL server - specifically T-SQL programming, writing stored procedures etc and getting familiar.

I had a look at the MS site to download SQL Server express, however it gives me the below choices. Can someone please advise which should be the correct option considering my learning goal ?

SQL Server Express with Tools (with LocalDB, Includes the database
engine and SQL Server Management Studio Express)
SQL Server Management Studio Express (Tools only)
SQL Server Express LocalDB (MSI installer)
SQL Server Express with Advanced Services (contains the database
engine, Express Tools, Reporting Services, and Full Text Search)
SQL Server Express (Containing only the database engine)

These are present on below site -

http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx

I would go for:

SQL Server Express with Tools (with LocalDB, Includes the database engine and SQL Server Management Studio Express)

This gives you MS SQL Express and the Management Studio which will give you all the playground to experiment.


Problem with enabling System Audit (Firebird's Trace and Audit Services) I would like to enable System Audit on my Firebird 2.5.9.27139 (x64) database (Windows 10), to inspect all queries performed on it.
I've already read some docs about what the configuration file should look like, but most likely I have some trivial problem with setting up all the paths and entries.
I would be very grateful if someone could have a glance at my settings and point out what should I change.
My database is located in: C:\KSBAZA\KS-APW\WAPTEKA.FDB
I would like to store all logs and trace config file in the directory: C:\DB_trace
In the main Firebird directory, I uncommented and set the line of firebird.conf file to:
AuditTraceConfigFile = C:\DB_trace\apw_trace.conf
Content of C:\DB_trace\apw_trace.conf:
<database WAPTEKA.FDB>
  enable true
  log_filename C:\DB_trace\audit_trace_database.log
  max_log_size 0
  include_filter %(INSERT|UPDATE|DELETE)%
  log_connection true
  connection_id 0
  log_transaction true
  log_statement_prepare true
  log_statement_free true
  log_statement_start true
  log_statement_finish true
  log_procedure_start true
  log_procedure_finish true
  log_trigger_start true
  log_trigger_finish true
  log_errors true
  log_warnings true
  log_initfini true
  log_sweep true
  time_threshold 0
  max_sql_length 65536
</database>

<services>
  enabled true
  log_filename C:\DB_trace\audit_trace_services.log
  max_log_size 0
  log_services true
  log_errors true
  log_warnings true
  log_initfini true
</services>

After saving the changes to the firebird.conf file, I completely rebooted the machine (the editing was performed while the Firebird service was down.)
Moreover, I tried to place the apw_trace.conf file in the same dir as the firebird.conf one (now AuditTraceConfigFile = apw_trace.conf), but without any result - they're no log files created in the C:\DB_trace dir (should I touch these empty *.log files?).
Thank you in advance for your help.

There are a number of things wrong with your config file:

enable true should be enabled true
log_filename C:\DB_trace\audit_trace_database.log should be log_filename C:\\DB_trace\\audit_trace_database.log
log_connection true should be log_connections true
log_transaction true should be log_transactions true

Most of these errors could have been detected by inspecting the firebird.log file.
And although the documentation in fbtrace.conf says:

Pattern is either database name without path or "SIMILAR TO"-based
regular expression which is matched against fully qualified database
path name.

It looks like you always need to actually specify a SIMILAR TO pattern matching the path of the database, for example %[\\/]WAPTEKA.FDB.
The corrected entry, which works on my machine, is:
<database %[\\/]WAPTEKA.FDB>
  enabled true
  log_filename C:\\DB_trace\\audit_trace_database.log
  max_log_size 0
  include_filter %(INSERT|UPDATE|DELETE)%
  log_connections true
  connection_id 0
  log_transactions true
  log_statement_prepare true
  log_statement_free true
  log_statement_start true
  log_statement_finish true
  log_procedure_start true
  log_procedure_finish true
  log_trigger_start true
  log_trigger_finish true
  log_errors true
  log_warnings true
  log_initfini true
  log_sweep true
  time_threshold 0
  max_sql_length 65536
</database>

The syntax in Firebird 3 is slightly bit different, check the Firebird 3 release notes for details


How to use the output of one query in other I am using following query to get the output as JobId, JobName and Start Execution date

 SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
stop_execution_date is null

But i want to use Start_Execution_Date column in another select statement, how to do that.Please suggest

Use a CTE (common table expression):

with myquery as (
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
stop_execution_date is null
)
SELECT *
FROM myquery
JOIN ....

Documentation link.


How to do a restore of a large postgresql database? I'm working on restoring a backup from on-premise postgresql to another postgresql database hosted in AWS (rds-aurora postgresql, serverless). The data size is 170gb of compressed data.

I earlier thought to do using pg_restore because I can pass the -j option, but it's taking a lot of time and space to extract the files.

My file is in the format =  tar.gz  (20200204_data_tar.gz)

I tried to extract and pass it to pg_restore using pipe to save time , but got the below error. Note : backup was generated using pg_start_backup.

tar -xzOf 20200204_data_tar.gz | pg_restore
    --host=my-test.us-east-1.rds.amazonaws.com --port=5432 --username=postgresql--dbname=mytest -j 16 --password --verbose --exit-on-error --data-only

pg_restore: [archiver] input file does not appear to be a valid archive

On-premise version 9.3.4
New Cluster version is using 10.7

Can someone please suggest me options on how to do a restore for this large database?

Of course the best option would be to remember how exactly you took that backup...

If you want to restore the backup on a hosted database, it had better be a logical backup created with pg_dump. So make sure it is not a physical backup:

Run tar -tzvf 20200204_data_tar.gz and make sure it does not contain files like postgresql.auto.conf or PG_VERSION.

Once you have ascertained that, unpack the archive to a new directory and check what you have got:

mkdir x && cd x
tar -xzvf ../20200204_data_tar.gz

Then check out which dump format the file is and restore it accordingly:

“plain” format:

The archive contains a single text file with SQL statements, and somebody was weird enough to pack a single file into a tar archive.

Such a dump is restored with

psql -f <SQL-file> -h <hostname> -p <port> -U <user> -d <target-database>

“custom” format:

The archive contains a single binary file, and somebody was weird enough to pack a single file into a tar archive.

Such a dump is restored with

pg_restore -h <hostname> -p <port> -U <user> -d <target-database> <dump-file>

“directory” format:

The archive contains a file toc.dat and many files of the form number.dat.gz.

Such a dump is restored with

pg_restore -h <hostname> -p <port> -U <user> -d <target-database> <directory-containing-toc.dat>

“tar” format:

The archive contains the files toc.dat and restore.sql and many files of the form number.dat.

Such a dump can be restored just like the directory format above.


Transaction independent writes inside a trigger I am using sql express 2008 r2 express edition.

Is it possible to do writes inside a trigger independent from the the transaction of the statement which causes the trigger to fire?

I have triggers working for simple auditing on some tables in my DB. I have one table for keeping the auditing data (Look here for details) It works fine except i don't want the auditing data be vanished when the transaction rolledback. So I want to write audit data to audit table independent from the transaction of the statement that causes my auditing trigger to fire.

One solution is to add a linked server to your own sever.  On the linked server, disable transactions.  Any calls made to the linked server will now run in their own transaction.

Example setup:

-- Add linked server called LOCALHOST
USE [master]
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LOCALHOST')
    EXEC master.dbo.sp_dropserver @server=N'LOCALHOST', @droplogins='droplogins'

EXEC master.dbo.sp_addlinkedserver @server = N'LOCALHOST', @srvproduct=N'SQL Server'
-- Allow procedure calls
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'rpc out', @optvalue=N'true'
-- But do not allow the procedure calls to enlist in our transaction
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'remote proc transaction promotion', @optvalue=N'false'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LOCALHOST', @locallogin = NULL , @useself = N'true'

-- Create a test procedure
use TestDatabase
if exists (select * from sys.tables where name = 'Log')
    drop table log
if exists (select * from sys.procedures where name = 'WriteLog')
    drop procedure WriteLog
go
create table log (id int identity, message varchar(256))
go
create procedure WriteLog
as
insert Log (message) values ('Hello World!')
go

Test code:

-- Insert through a direct call, and then through a linked server call
begin transaction
exec TestDatabase.dbo.WriteLog
exec [Localhost].TestDatabase.dbo.WriteLog
rollback transaction

select * from Log

The select will show only 1 row.  The rollback has not undone the effefcts of the linked server call.

While a nice trick, this is probably not something you'd want to do inside a production environment.  Personally I'd only allow data modifications through a stored procedure, and add logging in the stored procedure itself, before it starts a transaction (if any.)


How to delete the old backup differencial files? I'm (trying) managing a large number of sql server instances among a number of machinces running SQL Server 2005 and 2008. Almost all the DB are in Simple Recovery Model.
I'm doing full backups every Sunday and differencial backups every day.
Even though I am using compressed backups, there are databases which are nearly a TB. Therefore, I need to have a proper use of the backups regarding the space they are allocating.

My goal: I just need to have one full and one differencial backup for each database every single moment.

My question : What actions should I perform in order to achieve my goal? I guess something i need to do with the expiration of the backups and the "Clean up history", but I am not sure what exactly i need to do.

Method1: The Best method to achieve this would be using Olla hallengren Backup scripts.

You can find the scripts at https://ola.hallengren.com/downloads.html

First thing you need to do is execute  CommandExecute.sql script and then run the databasebackup.sql script.

Once done you can execute below via SQL Agent job as per you're required schedule:

  EXECUTE dbo.DatabaseBackup
  @Databases = 'USER_DATABASES',
  @Directory = 'C:\Backup',
  @BackupType = 'FULL',
  @Verify = 'Y',
  @Compress = 'Y',
  @CheckSum = 'Y',
  @CleanupTime = 24

Here Backup type can be changed from FULL, DIFF to LOG(If any DB in full recovery model exist) as per the need. And fro @ cleanup time- Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

Refer to below link on further details:

https://ola.hallengren.com/sql-server-backup.html

Method 2: You can always refer to Maintenance plans for clean up within SQL server

Use below query as an example to delete the old backup files as per the need:.

 EXECUTE MASTER.dbo.xp_delete_file 0,N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup',N'bak',N'2014-09-30T10:14:59'
GO

Refer to below link for using setting up cleanup task.

http://www.biztalkadminsblogging.com/index.php/blog-archive/item/20-automatically-remove-old-backup-files


Is there a way to disable / disallow someone from using View Creator using Diagram Tool in SQL Server? We have a lot of DB and BI analysts who regularly create views. Some folks create these views using SSMS' view creator which allows someone to drag and drop tables. This creates a lot of problems when we need to review or modify their code as the diagram tool in SSMS messes up the indentations and formatting, making it impossible to review the code. Is there an option or permission that I can revoke/change that disallows everyone from using it?

The view designer comes with SSMS and cannot be managed by any database engine. So, you can completely block them from creating views with DENY permission (as long as they are not sysadmin role members), but you cannot restrict them using SSMS features as the features installed in their local PC:
DENY create view to ApplicationUser


MariaDB 'You do not have privileges to manipulate with the users!' Apache/2.4.41
PHP 7.3.9
Windows 10 64bit

I have been using XAMPP without issue for months now, and this morning when I turned on my PC adn tried to accedd my databases via HeidiSQL I received some strange errors. They all seem to relate to users / permissions. I haven't (or don't recall!) changed any database user accounts, so this is confusing me.

Using HeidiSQL I can access my localhost and view the available databases. I can create a new database. When I click on the 'manage users' tabe the program freezes and i have to force close.

Using Phpmyadmin I can't get any further. Except there's a message displayed;

  You do not have privileges to manipulate with the users!

I have a root user with no password as this is a local testing environment.

The whole localhost database setup is running painfully slow. My localhost has 25 databases, mosts Joomla / Wordpress so not huge.

I've checked that everything is running on the correct port, and I don't have multiple instances opened. I've restarted my PC numerous times, no change.

The mysql error log doesn't have anything unusual.

Is there anything else I should be looking at?

Can you login with a command line client? (just to have a more clear picture of what is happening)
If yes :
   What does "select user, host, db, select_priv from db;" tell you?

Are you able to start the database with the --skip-grant-tables option?
If yes :
   Now have a look at mysql.db and mysql.user to check permissions.

-- EDIT:
Just wondering: Can you do something like :
grant ALL on . to ''@'';
grant ALL on . to ''@'localhost';

I have the feeling your user lacks the appropriate permissions.
(If the above works you would probably be able to do more with 'your_user' now.


Deleting old data in MySQL becomes slower over time I have an application running on Amazon MySQL/RDS that needs to maintain 2 weeks of transactional history after which time data can (usually) be purged. Because this isn't a hard rule I cannot use partitioning by day or week as a means to delete old data most efficiently.

There are several tables that need purging, many of them have multiple indexes and foreign key constraints (parent/child).

I'm deleting data in chunks (1000-3000 rows at a time) and committing after each chunk. After a set number of chunks have been deleted the purge code pauses for a set amount of time.

My issue is that the process is very efficient for 10-15 minutes, after which I begin to see large delays in deleting data chunks. I believe my SQL is as efficient as it can be. Where in MySQL can I look to better understand the bottleneck? If I stop my code and wait 15-20 minutes the delete performance is restored for another 10 minutes.

I would look to the InnoDB redo log file size.

The symptoms you describe is typical if you fill up the redo log with changes, which forces a "synchronous flush" — MySQL blocks further changes until it can free up a portion of the redo log by flushing dirty pages from the buffer pool.

RDS used to use an absurdly small redo log file size by default, 128M if I recall. For years they did not allow changing the size. But in the last couple of years they do allow changing it.

Here's how to check the size of your redo log file in megabytes:

mysql> SELECT @@innodb_log_file_size / 1024 / 1024;

To change it, I think you'd use the RDS parameter groups UI, then restart your RDS instance to apply the change.

To monitor this, I'd watch the number of bytes written to the redo log:

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

Measure that every 10 minutes or so, and plot it. The redo log files are of fixed size, and writes will eventually reach the end and wrap around to the beginning of the file. They must not overwrite changes in the log that represent dirty pages in the buffer pool, so before they get close to doing that, MySQL forces a synchronous flush.

So you can watch the rate of Innodb_os_log_written, by reading that variable periodically at even intervals. Compare this rate of log writes to the log file size (remember that you have two redo log files by default, so your redo log capacity is Innodb_log_file_size * 2).

This allows you to estimate "we overwrite the whole redo log file(s) every N minutes." This should correlate (roughly) to your 10-15 minutes time period when deletes are fast.

I think I recall there are some nuances to this calculation ... the Innodb_os_log_written might include some overwrites, i.e. some writes seek backwards to re-write a block under some circumstances. So there might be some cases where the numbers don't add up. I don't know deep details here.

In any case, InnoDB has long been known to be better able to handle heavy write workload if you increase the size of redo logs. It's tempting to increase it as large as you are allowed, but this may be overkill for most of your day-to-day workload with more modest write traffic.

See also:

https://www.percona.com/blog/2011/04/04/innodb-flushing-theory-and-solutions/
https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/


How to stop decrypting data after opening master key in SQL Server? I have implemented data encryption using SQL Server symmetric key encryption technique using a certificate, master key with password.

When we backup database from PROD server and restore to a different server, we open the master key using OPEN MASTER KEY DECRYPTION BY PASSWORD = ''... statement.

Password for master key is being managed by a SQL admin.

After opening the master key, we are able to decrypt the column data using DecryptByKey function. However, once we have done some analysis, we want to stop decrypting the data. No developer should be able to decrypt the data after a while.

How to stop decrypting the data or close the master key so that no developer can see data in plain-text?

The command you're looking for is CLOSE MASTER KEY;

From docs.microsoft.com:

  This statement reverses the operation performed by OPEN MASTER KEY. CLOSE MASTER KEY only succeeds when the database master key was opened in the current session by using the OPEN MASTER KEY statement.


How to prevent a trigger from running on certain statements? I'm looking for an answer that satisfies my need on both SQL Server and MySQL.

I have a small, custom audit table that I will be writing to through triggers on various tables. There are situations where I would like to bypass those triggers. I know that I can make a column on the tables and use it as a sort of flag for the trigger, but I'm hoping to find something that generates less clutter.

Is it possible to prevent a trigger from being called using a variable in my statement or some other query side thing?

EDIT: To clarify, I'm not expecting an actual trigger/query that works on both platforms. I'm looking for a solution that fits the desired goal of not having to use an extra column in each table.

I answered a question like this back on Jan 20, 2012 : Disable trigger for just one table

It was a workaround for MySQL I suggested. No negative feedback so far.


What is the best alternative for MySQL Query Cache? The Query Cache has been deprecated in MySQL 8. Is there any better alternative for Query Cache in MySQL?
Please help.

The alternative to query cache is to write code to cache data yourself, in the application. You decide which query results need to be cached, for how long, and in what format.

Many queries are against constantly-changing data, and your application requires viewing the current data.

Other queries are tolerant of viewing data that is not strictly current, so it's fine to use the cached copy for a few minutes, even though the underlying data has been updated.

Caching the data in the form of a result set isn't the only option. I have an application where I format results from several queries into one string which is a fragment of HTML, that store that string in the cache. It avoids having to re-format that part of the HTML every time someone views the page.

You can also use a shared cache like Redis or Memcached so if your app is deployed as multiple appserver instances, they can all share the same cache.
This is more work for you as a developer. There's no automatic solution for a server like MySQL to know which queries deserve to be cached.
Optimizing is often more work than simple, non-optimal solutions.


Re-create sql log using detach OK, before I start, I know you're not supposed to do this...

I have inherited a SQL Server instance (2008 R2 SP2 x64) which has a DB with a very large transaction log (full mode, with no tlog backups), and the log is in a compressed folder (don't ask). I need to sort this out and was thinking my best approach would be to detach the database (no reason why it shouldn't checkpoint) and re-attach the mdf ignoring the ldf so a new one is created in a proper folder structure. I can then implement a decent backup strategy.

I really don't want to backup this tlog as it's huge, and in a compressed folder so who knows how big it really is. Another option was to simply truncate the tlog but that's deprecated... Maybe backup the tlog to /dev/null (whatever the windows equiv is)?

Obviously I'd do a full backup before anything - these occur daily anyway.

I guess what I'm asking is, what is the safest approach here?

EDIT: actually, I've just thought - if I'm doing daily full backups, would the tlog not be useless immediately after the BU anyway?

No, please do not detach your database and move the log file. Follow the advice from Kin, spaghettidba, and others, but take a full backup first (you'll need to adjust db to your database name, and db_log and db.ldf to the log file logical and physical names, according to sp_helpfile):

BACKUP DATABASE db TO DISK = 'backup location' WITH INIT;

-- set it to single user
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- set the recovery mode to simple
ALTER DATABASE db SET RECOVERY SIMPLE;

-- run checkpoint twice
CHECKPOINT;
CHECKPOINT;

-- shrink the log file to something more reasonable (please not 1 MB):
DBCC SHRINKFILE(db_log, 1024); -- 1 GB

-- take the database offline
ALTER DATABASE db SET OFFLINE;

-- tell the system catalogs that you're moving the file
ALTER DATABASE db MODIFY FILE
  (NAME = db_log, FILENAME = 'x:\other_drive\db.ldf');

Stop here. This should tell you:

  The file "db_log" has been modified in the system catalog. The new path will be used the next time the database is started.

So, move the LDF file (which should now be about 1 GB) to x:\other_drive\ and then bring the database back online and multi-user:

ALTER DATABASE db SET ONLINE;
ALTER DATABASE db SET MULTI_USER;

(To be honest, I'd feel safer restoring the backup WITH MOVE; any time you start moving files around, you're opening yourself up to loss/corruption. But as long as you took your full backup right before you started this process, that should protect you.)

Now, leave the thing in simple recovery if you don't care about transaction logs and don't care to learn why they're important. But I really, really, really strongly recommend you read this post in full:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Try to understand your employer's tolerance for data loss, because I can assure you that most businesses cannot afford to be laissez-faire about this, and 24 hours of loss is absolutely not acceptable. If this is true in your workplace, then set the database back to full recovery, and immediately take a full+log backup to start the log backup chain:

ALTER DATABASE db SET RECOVERY FULL;

BACKUP DATABASE db TO DISK = N'backup_location\db_full.bak';
GO
BACKUP LOG db TO DISK = N'backup_location\db_init.trn';

Then create some kind of job that will take regular log backups throughout the day. Every hour might not be enough; again, make sure the owners of the data understand the trade-offs involved with not backing up the log often enough. I doubt that even an hour of data loss will be acceptable to them. And no, the .trn files will rarely be as large as the LDF itself; this will only happen in extreme scenarios where the log file blew up because of activity that happened since the last log backup.

Also, don't let SQL Server anywhere near compressed drives. Disk space is cheap; have them invest in enough space to adequately store your data and copies related to fault tolerance.


How to find out the total no of cell nodes configured as part of a rack in exadata? I want to know the exact total no cell nodes configured as part of the rack by login into the first db node of Exadata machine and run some commands or check some files.

We are not able to believe the output of /opt/oracle.Support.Tools/onecommand/cell_group file or the cellip.ora files or ibnetdiscover cmd ,since in some customer environment those files outputs are not giving the exact no of cell node details.

So is there any other command or file to fetch the exact no of cell nodes as part of the rack on the first db node ,even in the case like customer added additional storage nodes as part of that rack?


How can I replace constants 5,6 & 8 in sql instead of in condition In the following query, I would like to make the values in the IN dynamic.

SELECT DISTINCT party_id
INTO #tmp_new_part
FROM #party_approval
WHERE party_approval_type_id in (5,6,8)

It depends whether the values you want to go in the IN list are from an existing table or from a SQL parameter. If they are from an existing table or query you can do something like this:

SELECT DISTINCT party_id
INTO #tmp_new_part
FROM #party_approval
WHERE party_approval_type_id in (select party_approval_type_id from some_table)

If you want it to be based on parameters to a stored procedure, you can pass a varchar containing a delimited list of values, and use one of the many existing UDFs to split it into a table variable, similar to below:

SELECT DISTINCT party_id
INTO #tmp_new_part
FROM #party_approval
WHERE party_approval_type_id in (select * from dbo.CsvToInt(@values))


Log-in as service account in SQL Server Management Studio The stored procedure that performs a CTE in production is being executed using a service account. However, the stored procedure returns empty and I tried checking this using SSMS but I my domain account has no execute functionality in production.

I tried logging into SQL Server Management Studio using the service account but I ran through some problems. First was resolved by following this link - The certificate chain was issued by an authority that is not trusted

However, once that is executed, it returns as if no user name was provided:

Question is, can I login using a service account in SQL Server Management Studio?

Question is, can I login using a service account in SQL Server
  Management Studio?

How To Add A Service Account To Microsoft SQL Server

Open SQL Management Studio using SQL Authentication SysAdmin (SA)
Account.
Open up Security then Right Click on Logins, choose “New Login”
On the new login screen choose “Search”
On the search screen ensure you are searching the Entire Directory,
type in the user name, choose Check Names, then choose ok.
After you click OK, Click Server Roles and check “sysadmin”
Click OK and you are done
Test out by logging into SQL Management studio using Windows
Authentication.

For further your ref here and here


When do I need to backup the Service Master Key? I'm reading some documentation and whitepaper about Transparent Data Encryption. Some documentation mention to backup the Service Master Key as well (For clarification I'm not talking about the Database Master Key). I just don't exactly understand why this is necessary, because I was able to backup/restore a Database with TDE Encryption from Server A (backup) to Server B (restore) without using any Service Master Key.

In what scenario do I need to restore the Service Master Key?

If you are talking about the SQL service master key, then there is a rare occurrence where you really need to restore it.

I'm thinking of a couple of scenarios where you need to restore the SMK...

Somehow it got corrupted.
You are rebuilding your SQL server and planning to restore every database including system databases from backup. Usually in this case also you might not need to restore the SMK if you are using the same SQL service account and password.

In TDE you don't need to restore the SMK. As everyone said, you just need the certificate and the private key. You don't need to have same database master key, also as when you create the certificate from backup it gets encrypted by the DMK of destination machine.


Postgres: "Pivot" unioned table based on one column I have a query that does this sort of thing:

(SELECT rel_id, timestmap, y FROM table_1 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)

UNION ALL

(SELECT rel_id, timestamp, y FROM table_2 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)

UNION ALL

...

Which results in this form:

rel_id   timestamp    y
1        2013-01-01   a
1        2013-01-02   b
1        2013-01-03   c
1        2013-01-04   d
2        2013-01-01   e
2        2013-01-04   f
2        2013-01-06   g

What I'm doing here is directly selecting from partition tables, which are partitioned based on the ID of a related table (so, the first part selects from table_1, and that is all the rows that have a relationship to another table's row with ID 1, and so on). This works very well, and is fast (and avoids having to get the query planner to figure out which partition it should be looking at).

The problem is, to process it (using python pandas library), I need it in this form:

timestamp   1    2
2013-01-01  a    e
2013-01-02  b    null
2013-01-03  c    null
2013-01-04  d    f
2013-01-06  null g

I know it is possible to select the y column of each sub query as the rel_id (so as to make the name of the column the same as that of the relationship ID) - e.g. SELECT timestamp, y AS 1... - but I don't know how to 'merge' the resulting series of timestamp/values together in this way so that there are null values for a series that is missing a value at a given timestamp index.

The Pandas library can do this really easily, but it is pretty slow (slower than fetching the data in the first place), so I want to see if it is faster to make use of postgres's power...

Is this possible?
Is it likely to be fast?
Is it likely to be faster than processing in Python (my feeling is it will be)?

If it is faster you have to measure for yourself. However, doing it on the db side sends less data across, so I would assume it to be faster.

The pivot itself is fairly simple. I put your query's result in a table to make the example simpler.

SQL Fiddle

PostgreSQL 9.1.9 Schema Setup:

CREATE TABLE your_query
    ("rel_id" int, "timestamp" timestamp, "y" varchar(1))
;

INSERT INTO your_query
    ("rel_id", "timestamp", "y")
VALUES
    (1, '2013-01-01 00:00:00', 'a'),
    (1, '2013-01-02 00:00:00', 'b'),
    (1, '2013-01-03 00:00:00', 'c'),
    (1, '2013-01-04 00:00:00', 'd'),
    (2, '2013-01-01 00:00:00', 'e'),
    (2, '2013-01-04 00:00:00', 'f'),
    (2, '2013-01-06 00:00:00', 'g')
;

First step is to only return one row per date. That is simply done with a group by:

Query 1:

SELECT timestamp
  FROM your_query
 GROUP BY timestamp
 ORDER BY timestamp

Results:

|                      TIMESTAMP |
|--------------------------------|
| January, 01 2013 00:00:00+0000 |
| January, 02 2013 00:00:00+0000 |
| January, 03 2013 00:00:00+0000 |
| January, 04 2013 00:00:00+0000 |
| January, 06 2013 00:00:00+0000 |

Now wee need to pull the "correct" value into each column. For that we combine an aggregate with a case. The case returns null for all rows for which the condition is not met. the aggregate ignores nulls. That leaves the one value we are looking for:

Query 2:

SELECT timestamp,
       MAX(CASE WHEN rel_id = 1 THEN y END ) AS "1",
       MAX(CASE WHEN rel_id = 2 THEN y END ) AS "2"
  FROM your_query
 GROUP BY timestamp
 ORDER BY timestamp

Results:

|                      TIMESTAMP |      1 |      2 |
|--------------------------------|--------|--------|
| January, 01 2013 00:00:00+0000 |      a |      e |
| January, 02 2013 00:00:00+0000 |      b | (null) |
| January, 03 2013 00:00:00+0000 |      c | (null) |
| January, 04 2013 00:00:00+0000 |      d |      f |
| January, 06 2013 00:00:00+0000 | (null) |      g |

To make this work with your original query just replace your_query in my example with

(
(SELECT rel_id, timestmap, y FROM table_1 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)

UNION ALL

(SELECT rel_id, timestamp, y FROM table_2 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)

UNION ALL

...
) AS your_query


Determining SQL Server memory requirements I just a bit of an issue there where a SQL Server database was eating up quite a lot of RAM for its size.

A bit of background:

The database is for a small content managed website. The CMS is custom built in ASP which a bit of unoptimised queries littered throughout, but nothing too bad. The actual database takes up 132MB on disk.

Now back to SQL Server.

When looking through the server options I came across the Memory section. I noticed it was set to 2147483647MB which got me thinking that setting this value to something smaller could be a way to deal with the issue.

I set it to 500MB, which is quite a big difference.

So far the website is running great and there has been no more issues with memory on the server.

My question:

How can I determine what is the best value to set this to? Is 500MB to low? There is going to be another database on this server. It will be much smaller though, about 50MB.

EDIT (More Information)

I should add that the issue that caused me to seek out this memory setting was that of SQL Server not releasing memory back to the OS when it needed it. I run an old site that requires pages to be compiled on the fly (i.e. doesn't have a bin folder) and there was an issue with some pages not being compiled when needed. There was a server out of memory error.

This particular server has 2GB of RAM.

I'm not sure I believe there was ever an actual issue. Just because max server memory was set to 2 petabytes doesn't mean it would ever use that much. This is the functional equivalent of setting it to infinity, and just means you trust SQL Server to use as much memory as it needs.

Setting it to 500 MB seems artificially low to me. While your database is currently 128 MB on disk, you are probably not running any query patterns that lead to any performance issues even with that limit on memory. But when SQL Server needs more than 500 MB of memory, you're going to start noticing. SQL Server is designed to behave as if it is the only server on the machine, so it will take memory as it needs it, and won't release it unless the operating system needs it and applies pressure to SQL Server (discussions of LPIM aside). Restricting SQL Server's memory this way (or taking it away) will just cause performance degradation as it is much more expensive to page data in and out of memory, onto disk, etc. which will most certainly be required as your database grows. Think about trying to put a pig into a tube sock - eventually you're going to need a bigger sock. Why wait until the current sock rips?

I suggest a higher limit, but I can't tell you what that would be. You didn't disclose how much memory is on the server, whether it is x86 or x64, or what else is going to be running there. If the server is dedicated to SQL Server, I see absolutely no reason to save memory - what are you saving it for?

For a bit of an anecdotal observation, I don't think I've ever seen any instance of SQL Server on any kind of physical or virtual machine limited to 500 MB, and I have very serious doubts that this could possibly be the optimal setting for you in the long run.

I'd also suggest doing research into SQL Server settings and then changing them, instead of the other way around. This situation sounds a lot like saying "What does this button do?" after pressing it...


mySQL query optimizer not using indexes In our production environment (mySQL 5.6.40 on Amazon RDS), for certain queries the query optimizer ignores table indexes and takes several minutes to complete, whereas in our non-RDS test environment (mySQL 5.7), the same queries take a fraction of a second - due to using the indexes.

Rather than immediately upgrading from mySQL 5.6.40 to 5.7 on RDS (assuming the optimizer simply works better!), I wondered if there was something we're missing as the queries appear straight forward... I'm a bit surprised the optimizer doesn't pick the indexes.

Example query:

select id, username, agent, app_instance_id, end_time, event_time,
last_modified
from event where username='joebloggs' and app_instance_id<>'ad69469a-
d73f-4b33-a1c5-bde03c765d89' and last_modified>'2018-08-20 20:14:51.394'
order by last_modified asc limit 20

Execution time:

mySQL 5.6.40: 6m+
mySQL 5.7: 0.02s

EXPLAIN:

On mySQL 5.6.40 EXPLAIN shows that the optimizer doesn't use any of the indexes (5th column 'possible keys', 9th column 'extra')

'1','SIMPLE','event','range','last_modified,username,appinstanceid,user_app_last','last_modified','6',NULL,'1128956','Using where'

Whereas on mySQL 5.7 EXPLAIN shows the optimizer picks an index.

'1','SIMPLE','event',NULL,'range','username,last_modified,appinstanceid,user_app_last','last_modified','6',NULL,'1030540','0.54','Using index condition; Using where'

Long story short, our RDS instance used to run mySQL 5.5 and all worked fine... but with a forced upgrade to 5.6.40 due to end of life on 5.5 this problem has bubbled to the surface.

I've also run ANALYZE TABLE to ensure stats etc updated for optimizer. Any insight would be greatly appreciated. Thanks!

UPDATE:

Our client app is using JPA, which can be configured to use index hints. Is it simply that the query optimizer is better on 5.7 and that we should upgrade, or should we be providing hints after all.. and not relying on the query optimizer?
After running further tests on a new TEST instance (mySQL 5.7.23 upgraded from 5.6.40) on RDS it appears to be hit and miss on the same query as to whether the optimizer chooses to use indexes.

Edit:

As commented by user @Rick James, it is always better to try to improve the indexes first before using MySQL Index Hints. Further description for finding the matching INDEX can be found in his excellent cookbook for building the best INDEX for a given SELECT in MySQL. Also please do not add INDEX(a) if you already have INDEX(a,b).

See his answer for a better solution.

Original answer:

Native SQL queries

You can use the MySQL Index Hints to show the optimizer how to choose the indexes.

Queries created by JPA

You can use subqueries to use the preferred index first and to reduce the number of lines for the main query.

Example:

SELECT e.id, e.username, e.agent, e.appInstanceId, e.endTime, e.eventTime, e.lastModified
FROM Event e
WHERE e.username = 'joebloggs'
AND e.appInstanceId <> 'ad69469a-d73f-4b33-a1c5-bde03c765d89'
AND EXISTS(SELECT esub FROM Event esub WHERE esub = e AND esub.lastModified > '2018-08-20 20:14:51.394')
ORDER BY e.lastModified


Checking whether two tables have identical content in PostgreSQL This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table.

A PostgreSQL solution would be fine, but a generic one would be better. I found http://www.besttechtools.com/articles/article/sql-query-to-check-two-tables-have-identical-data, but I don't understand the logic used.

Background: I re-wrote some database generating code, so I need to check whether the old and new code produce identical results.

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data):

(TABLE a EXCEPT TABLE b)
UNION ALL
(TABLE b EXCEPT TABLE a) ;

Or with EXISTS to return just a boolean value or a string with one of the 2 possible results:

SELECT CASE WHEN EXISTS (TABLE a EXCEPT TABLE b)
              OR EXISTS (TABLE b EXCEPT TABLE a)
            THEN 'different'
            ELSE 'same'
       END AS result ;

Tested  at SQLfiddle

Also not the that EXCEPT removes duplicates (that should not be a worry if your tables have some PRIMARY KEY or UNIQUE constraint but it may be if you are comparing results of arbitrary queries that can potentially produce duplicate rows).

Another thing that EXCEPT keyword does is that it treats NULL values as identical, so if table A has a row with (1,2,NULL) and table B has a row with (1,2,NULL), the first query will not show these rows and the second query will return 'same' if the two tables have no other row.

If you want to count such rows as different, you could use a variation on gsiems' FULL JOIN answer, to get all the (different) rows:

SELECT *
FROM a NATURAL FULL JOIN b
WHERE a.some_not_null_column IS NULL
   OR b.some_not_null_column IS NULL ;

and to get a yes/no answer:

SELECT CASE WHEN EXISTS
            ( SELECT *
              FROM a NATURAL FULL JOIN b
              WHERE a.some_not_null_column IS NULL
                 OR b.some_not_null_column IS NULL
            )
            THEN 'different'
            ELSE 'same'
       END AS result ;

If all the columns of the two tables are not nullable, the two approaches will give identical answers.


SQL Server WMI Event Alert and BROKER_TASK_STOP waits In SQL Server 2008 I created a Sql Server Agent Alert last week to monitor deadlock events. The Alert calls a job with no schedule sending the xml deadlock graph token obtained through WMI SELECT * FROM DEADLOCK_GRAPH. The job calls a SP that stores the graph and an email is sent.

I have been monitoring the Sql Server Waits and have found that after I implemented the alert described above, the wait categories for BROKER_TASK_STOP and SQLTRACE_WAIT_ENTRIES have sky rocked. Those values went from not even being on the radar to contributing 42.59% and 5.11% of total waits respectively.

Should I be concerned? I read somewhere that high BROKER_TASK_STOP's may not be of real concern.

These two wait types are excluded from Paul Randal's wait stats analysis query, and he is far more of an authority than me.

I would not be concerned about these, and exclude them from your analysis.


DB_RECOVERY_FILE_DEST_SIZE automatically decrease size after shutdown I am using oracle database 11gR2.

I increased db_recovery_file_dest size with alter command

alter system set db_recovery_file_dest_size=60G scope=both;

When I shutdown database and again open database then db_recovery_file_dest_size automatically changed to default size 10G.

Now I am getting error

the database is not available due to the following conditions stuck archiver

How to solve this error

This is normal. If you set a parameter without specifying sid, then it will create/modify the entry in spfile with sid=*. But if you have another value set with the actual sid (instance name), that takes precedence over *, and the value remains unchanged.

Demonstration:

SQL> show parameter db_reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /fra
db_recovery_file_dest_size           big integer 20G

SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        db_recovery_file_dest_size    big integer 20G

SQL> alter system set db_recovery_file_dest_size =10G sid='MIN19' scope=spfile;

System altered.

SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
MIN19    db_recovery_file_dest_size    big integer 10G
*        db_recovery_file_dest_size    big integer 20G

My instance is called MIN19, so even though 20G is set for *, it will start with 10G:

SQL> startup force nomount
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             339738624 bytes
Database Buffers          180355072 bytes
Redo Buffers                7876608 bytes

SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 10G
SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
MIN19    db_recovery_file_dest_size    big integer 10G
*        db_recovery_file_dest_size    big integer 20G

I may change it to whatever I want, it will not have effect:

SQL> alter system set db_recovery_file_dest_size = 30G scope=spfile;

System altered.

SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
MIN19    db_recovery_file_dest_size    big integer 10G
*        db_recovery_file_dest_size    big integer 30G
SQL> startup force nomount
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             339738624 bytes
Database Buffers          180355072 bytes
Redo Buffers                7876608 bytes
SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 10G

Make sure to clean up overlapping parameters and use only 1 of them to avoid confusion. Example:

SQL> alter system reset db_recovery_file_dest_size sid = 'MIN19';

System altered.

SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        db_recovery_file_dest_size    big integer 30G
SQL> startup force nomount
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             339738624 bytes
Database Buffers          180355072 bytes
Redo Buffers                7876608 bytes
SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 30G
SQL>


Is there a way to enumerate all locks that are currently being held via get_lock()? I'm not seeing a way to list all locks currently being held by get_lock.  I see you can check is_free if you know the name and release as well.

I don't see an obvious function or infomration_schema table that would hold that information.

You should create your own lock repository

CREATE DATABASE IF NOT EXISTS getlock;
CREATE TABLE IF NOT EXISTS getlock.lockbox
(
    conn_id BIGINT NOT NULL,
    name VARCHAR(64),
    PRIMARY KEY (name,conn_id)
    UNIQUE KEY (name)
) ENGINE=MEMORY;

Then, whenever you need to create a lock, you perform this sequence

SET @lockname = 'whateverlocknameihavechosen';
SELECT GET_LOCK(@lockname) INTO @x;
REPLACE INTO getlock.lockbox VALUES (CONNECTION_ID(),@lockname);
DELETE FROM getlock.lockbox WHERE conn_id NOT IN
(SELECT id FROM information_schema.processlist);

This will acquire the lock, insert or overwrite the previous lock owner, and cleanup the lockbox by locating all entries whose connection had terminated.

Since GET_LOCK() is unsafe for statement-based replication, you can locate the warnings written in the error log and should see the names of the locks.


mysqldump error on common_schema I tried to take the backup of common_schema in Server version: 5.5.44 MySQL Community Server (GPL).

mysqldump -u root -p common_schema > common_schema_bkup.sql

I got the following error:

  "mysqldump: Got error: 1356: View 'common_schema._bare_grantee_grants'
  references invalid table(s) or column(s) or function(s) or
  definer/invoker of view lack rights to use them when using LOCK
  TABLES"

How can I rectify this?

Perhaps you should use --single-transaction
mysqldump --single-transaction -u root -p common_schema > common_schema_bkup.sql

Why ? According to the MySQL Documentation on mysqldump, you need the following rights:

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.
To reload a dump file, you must have the same privileges needed to create each of the dumped objects by issuing CREATE statements manually.

Please note the common_schema is comprised of one table and 27 views. You must have the necessary grants in order to be able to dump them, whether or not you dump with --single-transaction. This might include having the CREATE VIEW and SHOW VIEW grants.
UPDATE 2015-07-24 23:09 EST
I think the problem rests with mysqldump itself. Many metacommands are written around the dump of the definitions that could be interfering with rendering output that can logically represented. In some cases, a mysqldump of common_schema could not be reimported.
Your best chance would be to import common_schema from scratch. Right from the common_schema Documentation, it says on troubleshooting installation:

Since installation is merely an import act, you should only expect trouble if schema generation is unable to execute on your server.

Are you executing on a 5.0 MySQL server? This version is not supported.
Were errors reported during installation process?
What was common_schema's last message during install?
What is the output of SELECT * FROM common_schema.status?

Since unique metadata is not in common_schema, you should be able to reimport it rather that risk backing up with mysqldump with the possibility of not reloading it.


How to know which query is resulting in errors in the trace? So, I got this:

A simple trace with user errors.

But, how can I know the query that has the problem listed here?

I don't think DBCC INPUTBUFFER(X) is appropriated to this.

EDIT 1:

Well, I'm using:

STORED PROCEDURES:

RPC: COMPLETED
RPC: STARTING
SP: COMPLETED
SP:STARTED

TSQL:

SQL:StmtCOMPLETED
SQL:StmtSTARTED

And I can see this now:

is there a better approach for this?

I could find the query, because it was the only statement using TOP.I think it was luck.

I would add the events SQL:BatchStarting and/or SP:StmtStarting as well. That way, you can compare the SPID (the session ID) and find which query executed on which SPID.

For the record, Profiler is being replaced by Extended Events (XE for short) as of SQL Server 2008, though the GUI is really only usable as of 2012 in my opinion.


Uniqueness constraint in union of two columns' values Using postgresql 9.5, assume I have a table:

CREATE TABLE t (
 id INT,
 primary TEXT,
 secondary TEXT
)

I want to guarantee that the union of values in primary and secondary does not contain duplicates. Is this doable via index/checks or do I have to go to a trigger?

For example,

INSERT INTO t(id, primary, secondary) VALUES(1, 'a', 'b');
INSERT INTO t(id, primary, secondary) VALUES(1, 'x', 'y');
INSERT INTO t(id, primary, secondary) VALUES(1, 'a', 'z');
INSERT INTO t(id, primary, secondary) VALUES(1, 'z', 'a');

The latter two INSERTs should fail.

A trigger solution might work but I'd prefer to normalize the design and have simple constraints:

The table that is now:

CREATE TABLE t (
 id INT PRIMARY KEY,
 t_primary TEXT,
 t_secondary TEXT
) ;

would become:

CREATE TABLE tt (
 id INT,
 t_no SMALLINT NOT NULL,
 t_text TEXT,
 PRIMARY KEY (id, t_no)
 UNIQUE (t_text),
 CHECK (t_no IN (1,2))
) ;

Then your inserts

INSERT INTO t (id, t_primary, t_secondary) VALUES (1, 'a', 'b');
INSERT INTO t (id, t_primary, t_secondary) VALUES (2, 'x', 'y');
INSERT INTO t (id, t_primary, t_secondary) VALUES (3, 'a', 'z');
INSERT INTO t (id, t_primary, t_secondary) VALUES (4, 'z', 'a');

would become:

INSERT INTO tt (id, t_no, t_text) VALUES (1, 1, 'a'), (1, 2, 'b');
INSERT INTO tt (id, t_no, t_text) VALUES (2, 1, 'x'), (2, 2, 'y');
INSERT INTO tt (id, t_no, t_text) VALUES (3, 1, 'a'), (3, 2, 'z');  -- will fail
INSERT INTO tt (id, t_no, t_text) VALUES (4, 1, 'z'), (4, 2, 'a');  -- will fail

Finally you can have the original output with:

CREATE VIEW t AS
SELECT
    a.id,
    a.t_text as t_primary,
    b.t_text as t_secondary,
FROM tt AS a
  JOIN tt AS b
    ON a.id = b.id AND a.t_no = 1 AND b.t_no = 2 ;

SELECT *
FROM t ;


How to divide data per client from a single database? I am looking for best practices to separate data from a single database into multiple client specific databases. Currently my application dumps data for all the customers into single database and I need to separate data for each client(for security reasons I can't use single database) into respective client databases.

Can we use SSIS effectively to perform above task? Is there any way this SSIS package will understand that new customer is added (New ClientID) and create a new database for it?

I think you could definitely use SSIS to do this task.

I would start by selecting your distinct ClientID's into an object variable and then using a ForEachLoop with a Foreach ADO Enumerator and then specify that variable and load each row one at a time into a string variable. You could then used an Execute SQL Task that would check to see if a database exists for that ClientID and, if not, create the database and subsequent tables.

Then you could dynamically build your destination connection based on that ClientID as the database name. You also would want to dynamically build your SQL for your extract from your source and then do a simple extract and load into your new or existing database.


MySQL wait_timeout setting ignored I have a MySQL 5.5 instance running on a RedHat distro.  I have edited my.cnf with the following setting:

wait_timeout=30

However, client connections are sleeping and never getting killed.  The server has been restarted with the new setting in place.

What am I not understanding?

my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
performance_schema=off
innodb_buffer_pool_size=64M

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

wait_timeout = 30

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 500
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M

expire_logs_days    = 10
max_binlog_size         = 100M

bind-address=192.168.0.11
interactive_timeout = 60
max_connections = 500

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[isamchk]
key_buffer      = 16M

EDIT

Interestingly per @RolandMySQLDBA comments:

It appears none of the cnf file settings are being considered.

and the proof:

[root@localhost ~]# locate my.cnf
/etc/my.cnf
/opt/rh/mysql55/root/etc/my.cnf

My immediate suspicion is that there is another my.cnf present on the system. You should run

locate my.cnf

and see if the reports multiple my.cnf files

If there is only one my.cnf, then my.cnf is most likely in the wrong folder.

It should be in one of the following places

/etc/my.cnf
/etc/mysql/my.cnf
/var/lib/mysql/my.cnf
~/my.cnf

Please check for these. Remove extras.


Trying to load a file into a database on virtual machine I have set up a Postgres db on a linux vm and have been having no issues using a GUI to connect to it. However, I am trying to load a large, 32GB, file onto it and so am skeptical of the old way I was doing as it takes a lot of bandwith. I set up a dropbox folder to sync on the VM, which it has, and tried to COPY the file and got error message:

ERROR:  could not open file "~/Dropbox/0ptimus-Jaspin/nation/VoterMapping--NH--03-17-2014-HEADERS.tab" for reading: No such file or directory

I used the following to try to do the copy:

COPY nh FROM '/Dropbox/0ptimus-Jaspin/VoterMapping--NH--03-17-2014-HEADERS.tab';

Thanks!

COPY doesn't understand the shell-script shorthand ~ for "my home directory". You must specify the path explicitly, e.g.

COPY nh FROM '/home/MYUSERNAME/Dropbox/0ptimus-Jaspin/VoterMapping--NH--03-17-2014-HEADERS.tab';

at which point you'll probably get a "permission denied" error, because COPY runs as the postgresql server user, which can't normally read files from user homedirs. If you're on RHEL/CentOS/Fedora then setting permissions won't be enough, you'll need to change SELinux rights too.

Instead, I suggest using \copy from psql.

cd ~
psql
\copy nh FROM 'Dropbox/0ptimus-Jaspin/VoterMapping--NH--03-17-2014-HEADERS.tab';


autogrowth set higher than available disk First all our autogrow settings are either turned off or the file size is set to the max autogrow size. I guess same difference as I came in after the setup.

Although I've never had this problem, I've been looking for an answer to a possible condition: I need my file to grow for example a tiny amount, say 100 MB for a insert to happen.  However my autogrow is set to say 500MB.  The disk has 450 MB available.  What would happen in that scenario.  And just say this insert happens once in a while at night, so I had no idea that it was going to happen, so I couldn't or didn't pre grow the file.

I'm really just curious about how sql server would handle this.

SQL server would handle this aborting your transaction and throwing the following error:

I got this message from a test where I designed a scenario close enough to what you described:

I placed the database MyDB in a small disk with 100MB in size. Inserts in the table dbo.TextTable worked fine until the file needed to grow (500MB) in order to acomodate more data, but failed due to the lack of space in the disk.

Microsoft says that

  The autogrow setting cannot grow the database size beyond the limits
  of the available disk space on the drives for which files are defined.
  Therefore, if you rely on the autogrow functionality to size your
  databases, you must still independently check your available hard disk
  space. The autogrow setting is also limited by the MAXSIZE parameter
  you select for each file. To reduce the possibility of running out of
  space, you can monitor the Performance Monitor counter SQL Server:
  Databases Object :Data File(s) Size (KB) and set up an alert for when
  the database reaches a certain size.


DATETIME2FROMPARTS alternatives for SQL Server 2008 I am trying to part a datetime value and build it together removing minutes, seconds and so on. I want the values to be hourly.

Usually this works on a SQL Server 2012 machine:

DATETIME2FROMPARTS( DATEPART(year, datum), DATEPART(month, datum), DATEPART(day, datum), DATEPART(hour, datum), 0, 0, 0, 0) as dtHour

But unfortunately in SQL Server 2008 it does not! Which function do I need, to fulfill the same job?

I also looked up this reference: Date&Time functions but this were all only SQL Server 2012 compliant.

You can make it a bit shorter.

dateadd(hour, datediff(hour, 0, datum), 0)

This calculates the number of hours between 1900-01-01 00:00:00.000  and datum and adds that value to 1900-01-01 00:00:00.000.


Issue with Maximo KPI Returning "0" Instead of a Calculated % I am attempting create a KPI in Maximo that will count the work orders completed on time and divide it by the number of work orders due for the current month, then multiply it by 100 to return a percentage. And I'm failing. The queries will return the correct values independently, but do not seem to work in this situation. Please help.

select distinct (select count(wonum)
                   from workorder
                  where((status = 'CLOSE' or status = 'COMP')
                    and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
                    and upper(crewid) = '17D02'
                    and istask = 0
                    and siteid = 'INDPLS'
                    and worktype = 'PM')
                    and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0))
                    and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001)
                    AND ACTFINISH < TARGCOMPDATE) /
                (select count(wonum)
                   from workorder
                  where((woclass = 'WORKORDER' or woclass = 'ACTIVITY')
                    and upper(crewid) = '17D02'
                    and istask = 0
                    and siteid = 'INDPLS'
                    and worktype = 'PM')
                    and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0))
                    and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001))) * 100
  from dummy_table

I'll add an answer since I believe you can improve the efficiency of your query by doing 1 scan instead of 2:

select 1.0*count(case when ACTFINISH < TARGCOMPDATE then wonum end)
     / count(wonum)
from workorder
where (status = 'CLOSE' or status = 'COMP')
  and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
  and upper(crewid) = '17D02'
  and istask = 0
  and siteid = 'INDPLS'
  and worktype = 'PM'
  and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0))
  and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001)

I guess the original problem is integer division, i.e 1/4=0 while 1.0/4=0.25. If you want the result as a percent, multiply with 100.0 instead of 1.0:

select 100.0*count(case when ACTFINISH < TARGCOMPDATE then wonum end)
     / count(wonum)
from workorder
where (status = 'CLOSE' or status = 'COMP')
  and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
  and upper(crewid) = '17D02'
  and istask = 0
  and siteid = 'INDPLS'
  and worktype = 'PM'
  and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0))
  and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001)


Parse message_body(XML) I know the contents of @message_body are:

<inserted DocNo="xxxx99999"/>

I want to put 'DocNo' into a variable I created @DocNo and have tried various this such as:

SET @DocNo = (SELECT ref.value('@DocNo', 'nvarchar(255)')
              FROM @message_body.nodes('//Sample_Message') T(ref))

with no luck. I don't have much experience parsing XML with SQL. Any suggestions?

DECLARE @docNo nvarchar(255)
DECLARE @message_body xml = N'<inserted DocNo="12345"/>'

SELECT
    @docNo = @message_body.value('/inserted[1]/@DocNo', 'nvarchar(255)')

SELECT @docNo


SQL Server Restore a SINGLE table from a backup on SQL Server 2005, we do a weekly full backup with nightly incremental backups.

I want to know if it is possible to restore a single table from a backup, either to the source database or a different one.

I can not find any clear answer online.

Thanks in advance.

You can't restore a single table directly from your backup to a database.

You could restore your complete backup to new database and then copy your table from there to the desired database.


Hash index for InnoDB I saw a database schema that looked like this

CREATE TABLE foo (
    name_hash bigint,
    name varachar(500),
    a varchar(500),
    b varchar(500),
    ...
    PRIMARY KEY (name_hash),
    KEY ...
);

This seems to be an attempt to limit index size, by using a 8-byte integer rather than a 100-byte string. When looking up a value by name, the application hashes it, and then uses that hash in the SQL query instead of the name.

This is rather tedious, and I'm not sure if it is necessary.

Is there a feature of MySQL InnoDB that does something similar -- that looks up a string by its much shorter hash in order to fit index into memory?

Or does it do something like that already?

InnoDB has no tool to do anything like what you describe.

InnoDB has a limit of 767 bytes per column in an index.  That has room for a VARCHAR(255) utf8 or a VARCHAR(191) utf8mb4.  Also, InnoDB wants to put long VARCHARs in a different block if the whole record is bigger than about 8KB.  Will this be common?  (Don't blindly use VARCHAR(500) when you can reasonably state a smaller limit.)

Plan A:  Compress (in the client) the name and store it into VARBINARY(255).  Assuming it is typical text, the compression will be about 3:1.  Use that instead of the Hash.

Plan B:  Split the name into 2 or 3 columns so the index limit is abided by.  (An ugly solution!)

Plan C:  Change the 767 limit.  (It's possible, but I forget the details at the moment.)

Something to note:  Any decent "hash" is very random.  That is, each new row will land in some random place in the table.  Once the table exceeds innodb_buffer_pool_size, you will be doing more and more I/O, thereby slowing down.

What will the SELECTs be like?  Will you have other indexes on this table?  Will you JOIN on name_hash?  All of these may have a bearing on the 'best' way to design the schema.


Why does MySQL slow down in loading data? We have a really large file (26GB) that we need to upload to MySQL. In order to do so we split it into nine 3GB files.

What's confusing is that the first file took only 2 minutes to load, whereas the second file took 16 minutes and the third file took 40 minutes.

We're loading these files using load data local infile syntax, without any indexes or keys. The table engine is MyISAM.

Does anyone know why MySQL slows down on each successive load?

You might need to LOCK the tables before you insert the data into the database and then you unlock after the LOAD statement.

There are some tips on MySQL manual as well, check it out:

http://dev.mysql.com/doc/refman/5.6/en/optimizing-myisam-bulk-data-loading.html


Attempting to avoid SORT in query planner I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically):

SELECT foo from big_table, status_types
WHERE big_table.attr=1 AND
  lower(big_table.groupname) LIKE lower('Bar%') AND
  big_table.type = status_types.typeid
ORDER BY big_table.groupname DESC offset 0 limit 20;

 Limit  (cost=24511.42..24511.45 rows=14 width=150)
 ->  Sort  (cost=24511.42..24511.45 rows=14 width=150)
      Sort Key: big_table.groupname
     ->  Nested Loop  (cost=0.00..24511.15 rows=14 width=150)
           ->  Seq Scan on big_table (cost=0.00..24495.23 rows=14 width=72)
                 Filter: ((big_table.attr = 1) AND (lower(groupname) ~~ 'Bar%'::text))
           ->  Index Scan using typeid on status_types (cost=0.00..1.12 rows=1 width=86)
                 Index Cond: (status_types.typeid = big_table.type)

I have indicies on big_table for lower(groupname) and typeid is a PK for table status_types.
I would think it should be using the index on big_table (on groupname) to avoid sorting the results. I have tried several different options, even changing the query to something as simple as "select * from big_table where groupname LIKE 'foo%' order by group_name". Still performs the sort!!

I must be doing something wrong. Shouldn't the query planner favor the use of the index and NOT sorting?

You may get an ordered reverse index scan if you had an index on big_table( attr, lower(groupname) ) for your example query.  The explain you posted estimates 14 rows output from the query, in which case the final sort is trivial and not worrying about. Note the sort estimation cost "Sort  (cost=24511.42..24511.45 rows=14 width=150)" shows there's a big cost generating input to the sort (24511.42) and very little incremental cost for the sort (24511.45-24511.42 => 0.03).

You should generally run EXPLAIN with the ANALYZE option so you can see real times, not just estimates.  Divergences between the actual time/rows from the estimated costs/rows is a signal that the planner has bad statistics or lacks adequate rules for proper estimation. DEPESZ's explain explainer tool is often handy for hunting the real problems with your queries.


"Invalid object name" error when executing query I connect database with Visual Studio 2017. When I am trying to execute a query then it shows the following error:

The connection string I am using is:

SqlConnection con = new SqlConnection("Data Source=ANUPAM-DESKTOP\ANUPAM;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");

My code:

public void exicuteDatabaseQuery(String query)
    {

        con.Open();
        SqlDataAdapter sda = new SqlDataAdapter(query, con);
        sda.SelectCommand.ExecuteNonQuery();
        con.Close();
        MessageBox.Show("Successfull");

    }

    private void Button_Click(object sender, RoutedEventArgs e)
    {
        int id = Convert.ToInt32(___Id_.Text);
        int number = Convert.ToInt32(___Number_.Text);
        String InsertQuery = "INSERT INTO Member (id, number)
        values('"+id+"','"+number+"')";

        exicuteDatabaseQuery(InsertQuery);
    }

Database explorer image:

I'd guess the problem is your SQL Server login does not have it's "default database" properly set.

This will show you what your "default database" is set to:

SELECT sp.name
    , sp.default_database_name
FROM sys.server_principals sp
WHERE sp.name = SUSER_SNAME();

You can either change the default database of your login, or you can specify the database in the connection string.

This will modify your default database:

ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = [testDB];

If you want to specify the database in the connection string, make your connection string:

SqlConnection con = new SqlConnection("Data Source=ANUPAM-DESKTOP\\ANUPAM;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;Initial Catalog=testDB");


Audit Plug-in for MYSQL 5.7.24 error I have tried installing MariaDB audit plugin for MySQL 5.7.24.
I have downloaded the 'server_audit.so' from  MariaDB-10.3.11 and I think it is compatible with MySQL 5.7.24.
But I have encountered this error while running command

  INSTALL PLUGIN server_audit SONAME 'server_audit';

  ERROR 1126 (HY000): Can't open shared library
  '/usr/lib/mysql/plugin/server_audit' (errno: 2
  /usr/lib/mysql/plugin/server_audit: cannot open shared object file: No
  such file or directory)

thanks for any help

INSTALL PLUGIN server_audit SONAME 'server_audit.so';

According to the documentation, the SONAME should include the extension - '.so' in this case.


removing group by clause with SUM So lets say I have a table, MyBigTable that has student scores of an examination. So lets say the structure looks something like

id
name
school
board
exam_partA_score
exam_partB_score

Then I need to run some script on it at certain levels. So lets say I have to run report at the school level so I will have

select
 school,
 board,
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.school, A.board

Then I need to run another script at the board level so

select
 school = null,
 board,
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.board

Then at the end I need to combine them all so something like

select * into BigReport
from First_Select Statement UNION Second_select_statement

Problem:
I have about 6 fields in the group by clause. They change depending on the type of report I need (school level, board level, state level).

I also have about 200 cnt_* fields.

Right now I have those "select" statements three times at the different levels which makes my script around 900 lines.

I dont see why I have to compute counts three times when the logic they use is the same all three times. I just need to change the headers (school, board, id..) and the group by clause. Not only that, if a spec changes for a count, or another count field is needed then I am going to have to change it in three different places which is very error prone.

What is the best and most efficient way of doing this.

Take your most complex (most GROUP BY columns) then use the OVER clause

SELECT
   school, board, col3, col4, ...,
   cnt_whoPassedA, ...,
   SUM(cnt_whoPassedA) OVER (PARTITION BY board) AS cnt_whoPassedA_ByBoard,
   SUM(cnt_whoPassedA) OVER (PARTITION BY school) AS cnt_whoPassedA_BySchool,
   SUM(cnt_whoPassedA) OVER (PARTITION BY school, board) AS cnt_whoPassedA_BySchoolBoard,
   ...
FROM
(
select
 school,
 board,
 col3, col4, ...
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.school, A.board, A.col3, A.col4...
) T

However, I would consider doing this in my report layer (say in SSRS): do you need all levels of grouping at the same time?


Oracle 12c default sys password I'm new to database and Oracle, but I need to learn it.

I just following this tutorial to install Oracle 12c, I almost on at the end of the steps.

I have finishing install the database with GUI interface, and now I want to start the database that I have installed with this following command:

sqlplus /nolog

and yea, I have entered to the database, and then I run this following command to connect as sys:
connect sys as sysdba

But it prompt Enter password:

Unfortunately, I don't know what is the default password for that.

I have try the password with CHANGE_ON_ INSTALL base on this following documentation, but it seems not work actually.

So, what is the default password..?, or any way to bypass that password and then change it if I forgot..?

EDIT: I following @Tejash----ORA-00980 instructions to use this command to directly log into DB as a sysdba:

sqlplus / as sysdba

And the result is like this:

What username should I use..?, I have try it with sys and sysdba but it still prompt a Enter password:

EDIT 2: Here is my sqlnet.ora and tnsnames.ora files

and here is my $ORACLE_SID and $ORACLE_HOME values.

If you are on the same system where Oracle database is installed and you have logged in to the system using a correct privileged user then you can bypass the password.

  Please note that the path of the ORACLE_HOME and ORACLE_SID is
  properly configured. Also, TNSLISTNER and SQLNET files are properly
  configured.

You can directly log into DB as a sysdba using the following command:

sqlplus / as sysdba


Can there be situations where using (noexpand) on an indexed view is undesirable? From the past experience using the schema bound and indexed view in a query with (noexpand) hint usually dramatically improves performance if the view is designed well.
Apparently using the indexed views is not implicit, as it requires a table hint.
But can there be any scenarios where supplying (noexpand) hint may be undesirable while the view remains schema bound and indexed?
In other words, should schema bound indexed views be "not expanded" implicitly at all times instead of a developer having to remember to supply the hint to turn off view expansion?
The way I read BOL it states that Enterprise is supposed to imply (noexpand). But I know for certain that it does not, as I am on Enterprise and adding (noexpand) provides dramatic performance improvement, it never failed to. So it is not correct to state that noexpand is an Enterprise feature.

Firstly, NOEXPAND is an enterprise edition feature of SQL Server, that is why you have to specify it manually if you are not on this edition.

Based on the optimizer's prediction there may be a situation, where NOEXPAND would be harmful. But it's pretty rare and most likely NOEXPAND isn't the issue in such cases. Most likely the issue is poorly chosen indexes. For instance, an index on a column with low cardinality.

Additionally, have a look at this noexpand hint for non-enterpise edition and performance  post as it answers the rest of your questions. And also provides you with a workaround.


Inefficient Indexes on Primary Keys So I was cleaning some indexes,with some queries I made, and some that I found over the internet with ineficient indexes ( lots of updates and no seeks, scans, etc.)

But something that I'm not sure is, almost all primary keys have no seeks, scans or lookups and thousands of updates.

I think this is a normal behavior since we have most of these keys only to have an ID on a table.

Am I right?

Like this one:

ID_X Seeks=0, Scans=12, Lookups=8, Updates=31453

This does not seem wrong. Though because most people define private keys as their clustered indexes by default unless they have specific reason to chose something else as the clustering key, it might be unusual. It very much depends on the other indexes that are in place, and how the table is used by your application(s).

If a query can be entirely satisfied using a non-clustered index, then there is not need to query the clustered index (or heap, if there is no clustered index defined) for extra information. If I have a table T with columns a, b, c, d, e, f, g, h, i, with a being defined as the primary key and there being a non-clustered index on b,c,d, the query SELECT b,c,d FROM T WHERE b='some-value' ORDER BY b,c,d can be satisfied by the index and nothing else needs to be referred to. On the other hand SELECT * FROM T WHERE b='some-value' ORDER BY b,c,d will need to refer to the data pages (and therefore the primary key if it is the clustered index for this table) in order to read the columns other than b, c, d, and a.

Also the primary key might not be the clustering key. In this case even if another index is used that is not entirely sufficient so that other information from the main data pages is needed, the clustered index (or heap if there is no clustered index) will be used not the primary key's index.

So if you have a sufficient selection of covering indexes defined, it might be the case that is it rare for your queries to need to touch the clustered index, and if your PK isn't the clustered index it might not be touched even if there are no other useful indexes for a statement to use.

In the example that you give, the index does see some action. It could also be that this table is usually write-only and only occasionally read from. This is not uncommon for logging and auditing tables where data is constantly written as users take actions but is only read once in a while when someone runs off an audit report.


It's possible to recover a deleted pg_hba.conf linux postgresql file? If anyone delete the pg_hba.conf file, and don't run "pg_ctl reload", the configuration of the deleted file is still working.
With that in mind, could someone put in a new file that info wherever it is?

If you are using Postgres 10, then this is possible through the view  pg_hba_file_rules

For earlier versions, I am not aware of any way to retrieve that.


Reclaiming space from half filled huge FGs having Partitioned and LOB Data in it We have a database with multiple filegroups. Out of which, there are 4 filegroups(Non-Primary) which has most of the data. These filegroups have partitioned tables into it. It also contains LOB Data as well.
Now as per customer request, we have purged 50% of data. Because of it, these 4 Filegroups are half empty.
For example these FGs are of 400 GB each and around 200 GB is free.
My requirement is to reclaim the space from these 4 FGs as we are expecting that it will not grow that much in near future. Shrinking is not an option as it is time consuming. Any other faster way to do it.
Can someone please help me. Thanks in advance.

The time-consuming part of shrinking is moving the data out of the latter parts of the file into spaces nearer the beginning so that the rear parts can be truncated, you have to move the data before reclaiming the space because SQL Server doesn't support sparse files (it can't just tell the OS "I don't need that bit, feel free to allocate it elsewhere for now"). This means that there is nothing you can do in-place that will be any faster.
You may be lucky though and find that most of the back end of the files is empty, allowing you to simply truncate them to release some space to the filesystem (DBCC SHRINKFILE (<file>, TRUNCATEONLY);).
You could migrate the data to fresh filegroups then drop the existing filegroups. If you do this on the same volume though, given that you are trying to release 50% or less this will be no quicker, and possibly much slower (it must move 200Gb to the new files, where a shrink will move at most 200Gb around the files). If you do it between volumes though you might see this work much faster if you are using spinning-disk based media, because there is likely to be a lot less head movement latency compared to the shrink. If you are using low-latency storage this benefit will not be significant.

Shrinking is not an option as it is time consuming.

One thing to note is that shrinking is effectively a resumable process - you do not lose all your progress to a rollback if you stop it part way through. So if you have short maintenance windows where you can accept the performance impact of running shrink operations you could start the process and cancel it at the end of your window, and start it again next time. This may take several such windows but would eventually get the job done.
Another issue to mention is that both shrinking and moving data between filegroups work at the page level so will not release unallocated space within pages. This is not likely to be a massive problem for you if most of the free data was off-page LOBs, but if a low of row data was removed to you might want to rebuild your indexes (and heaps, especially heaps, if you have any such tables with no clustered index) before shrinking/migrating the data. You may even find that this will effectively move the data mostly to the front of the files anyway allowing you to truncate them significantly without needing to do a full rearrange and shrink. Depending on your SQL Server version and edition, you can use resumable online index rebuilds to split this process over multiple maintenance windows as described for the shrink operation above.


mysql replication successful but slave not replicating I have created a mysql master-slave configuration and things look fine .
the  " show master status;" on slave doesnt show any error . this is the output

 Slave_IO_State: Waiting for master to send event
                  Master_Host: 109.123.100.58
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

However on replication on slave doesnt seem to be happening when master undergoes change

On master . SHOW MASTER STATUS

+------------------+----------+--------------+-------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB        |
+------------------+----------+--------------+-------------------------+
| mysql-bin.000001 |   639495 |              | mysql,informationschema |
+------------------+----------+--------------+-------------------------+

on slave SHOW PROCESSLIST yields

+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| Id  | User        | Host      | db   | Command | Time  | State                                                                 | Info             |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| 174 | system user |           | NULL | Connect | 25777 | Waiting for master to send event                                      | NULL             |
| 175 | system user |           | NULL | Connect | 25777 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 199 | root        | localhost | NULL | Query   |     0 | NULL                                                                  | show processlist |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

Am i missing something

The output you presented is very confusing. On one hand, the master shows:

mysql-bin.000001   639495

The slave's PROCESSLIST indicates that replication works (the IO thread is connected to the master, the SQL thread is waiting).

The slave's SHOW SLAVE STATUS again claims all is well, but the master's position is 106.

Are all these outputs issued at roughly the same time? If not, then they are not valuable.

I would check the following:

The obvious: is 109.123.100.58 really your master?
Does SHOW PROCESSLIST on master list the connection made by the slave under the replica user?
Do you have different server-id settings for master and slave?
Do you have any replicate-do-* or replicate-ignore-* settings in your mysql.cnf file?


Database Compatibility Requirement for Transactional Replication Does the subscriber "within two version requirement" for transactional replication from this MSDN article apply to the SQL Server Instance, Database Compatibility Level, or Both?

For example, would replication work with this environment setup?

Publisher and Subscriber SQL Instance: SQL Server 2014 Enterprise Edition 64-bit (12.0)
Distributor Database Compatibility Level: 110
Publisher Database Compatibility Level: 110
Subscriber SQL Instance: SQL Server 2017 Enterprise Edition 64-bit (14.0)
Subscriber Database Compatibility Level: 140

Does the subscriber "within two version requirement" for transactional
  replication from this MSDN article apply to the SQL Server Instance,
  Database Compatibility Level, or Both?

Yes. No. No.

The database compatibility level is irrelevant, and you would never set the database compatibility on the distribution database, as it's a system database.

A SQL Server 2014 publisher/distributor can have a SQL Server 2017 subscriber.

SQL Server 2017 supports compatibility levels back to 100, so you can set the subscriber database to 110 compatibility level if you want to.  And you might need to if you publish views, stored procedures, stored procedure executions, or have triggers on subscriber tables that aren't NOT FOR REPLICATION.


Return multiple rows from matching on multiple CASE matches? I want to add a column to my query which will specify one or more categories a row matches. I want to take this:

    +--------------+---------------+
Row | Product      | Quantity_Sold |
    +--------------+---------------+
1   | Coca-Cola    | 15            |
2   | Cigarettes   | 4             |
3   | Pretzel      | 6             |
4   | Beer         | 25            |
5   | Popcorn      | 10            |
6   | Candy Bar    | 10            |
    +--------------+---------------+

And return this:

    +--------------+---------------+----------------------+
Row | Product      | Quantity_Sold | Category             |
    +--------------+---------------+----------------------+
1   | Coca-Cola    | 15            | Beverages            |
2   | Cigarettes   | 4             | Controlled Substance |
3   | Pretzel      | 6             | Snacks               |
4   | Beer         | 25            | Beverages            |
5   | Beer         | 25            | Controlled Substance |
6   | Popcorn      | 10            | Snacks               |
7   | Candy Bar    | 10            | Snacks               |
    +--------------+---------------+----------------------+

Notice on line 4-5 of the output, "Beer" is on two lines, because it fits in two categories.

If I try to do this with CASE, only the first match will be counted.

This query

SELECT
    Product,
    Quantity_Sold,
    CASE
        WHEN
            Product IN ('Coca-Cola', 'Beer')
        THEN
            'Beverages'
    CASE
        WHEN
            Product IN ('Pretzel', 'Popcorn', 'Candy Bar')
        THEN
            'Snacks'
    CASE
        WHEN
            Product IN ('Cigarettes', 'Beer')
        THEN
            'Controlled Substance'
    END
        AS Category
FROM sales_table;

Would only return this output

    +--------------+---------------+----------------------+
Row | Product      | Quantity_Sold | Category             |
    +--------------+---------------+----------------------+
1   | Coca-Cola    | 15            | Beverages            |
2   | Cigarettes   | 4             | Controlled Substance |
3   | Pretzel      | 6             | Snacks               |
4   | Beer         | 25            | Beverages            |
5   | Popcorn      | 10            | Snacks               |
6   | Candy Bar    | 10            | Snacks               |
    +--------------+---------------+----------------------+

(Notice "Beer" only appears once)

So how can I get it to show up on separate lines for all categories it matches?

A join would be the best solution here.

Create a table that contains a list of categories and which products are associated with them, something like Product, Category we'll call it ProductCategories. Then join on this table.

SELECT p.Product, p.Quantity_Sold, pc.Category
FROM sales_table p
JOIN ProductCategories pc ON pc.Product = p.Product'

This has the added benefit of not having to modify your case statement as you add more categories (which quickly gets difficult/impossible to maintain), and only requires a simple insert into the new table.


Add a new Column and define its position in a table I have Table A with 5 columns:

TableA
--
Name
Tel
Email
Address

I want to add a new column (mobile) in between Tel & Email:

TableA
--
Name
Tel
Mobile
Email
Address

If I use

ALTER TABLE TableA
ADD COLUMN Mobile INT NOT NULL

the mobile column is added to the end of the table.

Is there a way to achieve this without dropping the table and moving data to a new table?

No. Rebuilding the table is the only way. See this Connect Item for confirmation.

You could use SSMS to script this for you if you trust the somewhat buggy table designer.

Apart from that you could declare a view with the desired column order as a way of grouping logically related columns together.


How to select max value faster from large size table I have query

SELECT max(ParameterID) FROM modelparam_ems

Table Name: modelparam_ems

No Of Rows: 9000

It takes time to retrieve max value of parameterId, ParameterId in Unique value.

Output Time :

(Below row copied from Mysql Workbench).

13:51:15    SELECT max(ParameterID) FROM modelparam_ems 1 row(s) returned   0.109 sec / 0.000 sec

You have two options:

as TomBom Mentioned, you need to create an index on ParameterID column using this statement

create index idx_modelparam_ems_parameterid on modelparam_ems(ParameterID);

If your ParameterID column is AUTO_INCREMENT,

you can get your max id by getting the next AUTO_INCREMENT value from the following query

SELECT AUTO_INCREMENT-1
FROM information_schema.tables
WHERE table_name = 'modelparam_ems'
AND table_schema = 'YourDBName' ;

If the engine is MyISAM and you are sure that there is no gap between two ids, you can find your max id by getting number of rows in the table as below. ROWS column contains the value for number of rows.

EXPLAIN SELECT * FROM modelparam_ems;

Note that In Innodb the value returned in rows is an estimation of total rows. So it is not as accurate as MyISAM.

Disclaimer

Option 2 may be both unreliable, and subjected to failure. Use it only if option 1 is not possible and you can tolerate the failure.


Count Consecutive Codes I'm searching for a query (cannot figure it out myself at the moment with the help of already found queries around here. The quest I have is the following one: I have a view that gives following output

  ResourceID | ReferenceDate | CodeID
 ------------|---------------|--------
         893 | 2018-05-14    |      1
         656 | 2018-05-07    |      1
         656 | 2018-05-22    |      1
         353 | 2018-05-07    |      1
         530 | 2018-05-07    |      1
         541 | 2018-05-07    |      1
         755 | 2018-05-07    |      1
          55 | 2018-05-07    |      1
         382 | 2018-05-07    |      1
         224 | 2018-05-07    |      1
        1115 | 2018-05-07    |      1
         191 | 2018-05-07    |      5
         191 | 2018-05-08    |      5
         191 | 2018-05-09    |      5
         655 | 2018-07-06    |      6
         655 | 2018-07-10    |      6
         892 | 2018-07-06    |      6

I want to group the CodeID's with a sort by ResourceID and then ReferenceDate. With this result I want to get something like the following table

  ResourceID | CodeID | Count
 ------------|--------|-------
         893 |      1 |     1
         656 |      1 |     2
         353 |      1 |     1
  ...
         191 |      5 |     3
         655 |      6 |     2
         892 |      6 |     1

This to get to a result where we can say that ResourceNumber x has y consecutive days CodeID z.

EDIT: The question looks like this one but with the difference (as far as I see it) that I need to have the count combination or ResourceID and CodeID: Determine consecutive occurrences of values

Code to create sample data for testing:

CREATE TABLE dbo.Resources
(
    ResourceID INT NOT NULL,
    ReferenceDate DATE NOT NULL,
    CodeID INT NOT NULL
);
GO

INSERT INTO dbo.Resources
    (ResourceID, ReferenceDate, CodeID)
VALUES
        ( 893, '2018-05-14',1),
        ( 656, '2018-05-07',1),
        ( 656, '2018-05-22',1),
        ( 353, '2018-05-07',1),
        ( 530, '2018-05-07',1),
        ( 541, '2018-05-07',1),
        ( 755, '2018-05-07',1),
        (  55, '2018-05-07',1),
        ( 382, '2018-05-07',1),
        ( 224, '2018-05-07',1),
        (1115, '2018-05-07',1),
        ( 191, '2018-05-07',5),
        ( 191, '2018-05-08',5),
        ( 191, '2018-05-09',5),
        ( 655, '2018-07-06',6),
        ( 655, '2018-07-10',6),
        ( 892, '2018-07-06',6);

If I've understood correctly you need to set a group every time CodeID changes. You can try by using LAG() function.

WITH ct1 AS
(
  SELECT
      ResourceID, ReferenceDate, CodeID,
      IIF(COALESCE(LAG(CodeID) OVER (ORDER BY ResourceID, ReferenceDate), 0) <> CodeID
          AND
          COALESCE(LAG(ResourceID) OVER (ORDER BY ResourceID, ReferenceDate), 0) <> ResourceID,
         1, NULL) Rst
  FROM
      Resources
)
, ct2 AS
  (
    SELECT
        ResourceID, ReferenceDate, CodeID,
        SUM(Rst) OVER (ORDER BY ResourceID, ReferenceDate) as Grp
    FROM
        ct1
   )
   SELECT
       ResourceID, CodeID, COUNT(*) CntCodeID
   FROM
       ct2
   GROUP BY
       ResourceID, CodeID, Grp;

GO

ResourceID | CodeID | CntCodeID
---------: | -----: | --------:
        55 |      1 |         1
       191 |      5 |         3
       224 |      1 |         1
       353 |      1 |         1
       382 |      1 |         1
       530 |      1 |         1
       541 |      1 |         1
       655 |      6 |         2
       656 |      1 |         2
       755 |      1 |         1
       892 |      6 |         1
       893 |      1 |         1
      1115 |      1 |         1

db<>fiddle here