texts: TRY .. CATCH sometimes fails to catch PRIMARY KEY VIOLATION

texts:
I wrote code to upsert records to the database. The database is Azure DB, S4 tier.

Updates and inserts are sometimes are highly concurrent since it's coming from the application that doing too many updates.

The code works successfully most of the times, but sometimes it throws 2627 exception (that handled inside of the CATCH statement). Cannot figure out so far what could be the issue.

I can replace it with MERGE statement, but I tbh don't favor it much, and also want to figure out what's happening with this try and catch.

SET nocount ON;
IF NOT EXISTS(SELECT * FROM MyTable WHERE ID = @ID)
BEGIN TRY
INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', GETDATE())
END TRY
BEGIN CATCH
IF @@Error <> 2627 ----Violation of PRIMARY KEY constraint
THROW;
END CATCH

UPDATE MyTable SET Value = 'xxx' WHERE ID = @ID AND TimeChanged < GETDATE()


texts: What is different between SQL-Server versions 2008R2 and 2012

texts:
Dear SQL Server experts,

Can you tell me the different between SQL-Server 2008 R2 and SQL-Server 2012 versions? I designed my product with version 2008 but when I tried to buy a licence, Microsoft said they won't sell it anymore, buy 2012 instead.

So I need proof that our system can run with 2012. I know it's backward compatible but I need official proof. Please help me.


texts:
I have a big DELETE query (deleting 20M rows from a 600M row table with condition according to indexed column) that is stuck - running already for 17hrs. Because it's an InnoDB table, killing it will result in a rollback, which as far as I understood will at least double the time I've waited so far. I'm trying to figure a way to recover from this situation. I don't care if the query will only update some of the rows it was supposed to, or if it will actually rollback, just that the table won't be corrupted.

The Table:

CREATE TABLE `conv_with` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id_from` int(11) NOT NULL,
 `user_id_to` int(11) NOT NULL,
 `counter` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `user_id_to` (`user_id_to`,`user_id_from`),
 KEY `from` (`user_id_from`)
) ENGINE=InnoDB AUTO_INCREMENT=643019605 DEFAULT CHARSET=utf8

The Delete Query: DELETE FROM conv_with WHERE user_id_from IN (uid1, uid2, ..., uid100000)


texts: Running an SSIS package without the SSIS service installed

texts:
On a SQL Server 2005 instance in our DMZ, we have BIDS installed but not the SSIS service.

If I have an SSIS package on that server, can I still schedule to run it without SSIS service?

I tried using DTexec but it is not able to find the package.


texts: How to load textfiles into SQL Server Database?

texts:
Sometimes I have to load csv-files to database. These files might be really huge (> 1TB).

I did not export them to this file and I cannot change anything about the format. The files just "fly in".
In most of the cases the files are seperated by "pipe".
Sometimes text is qualified. Within qualified text there might be "pipes", as well as there might be CR/NL-characters...
Sometimes text is not qualified. Within the text pipes are escaped with an backslash. A backslash might be escaped with another backslash...

The first thing I do to load these files is to get all content changed so that one line for the table is within on line of text.
The row delimiter must not remain within any textfield. I do this job with a commandline script using sed and/or tr.

The next step is to split the file in hany pices (< 50 MB). They would fit into any editor (more confort than less) and I can proceed them parallel.
(For a short time I need double the space of the original file in the filesystem).

Depending on the text formating I convert special characters within the text fields using commandline script using sed, if it gets more complex I use awk.
As the original file is split to hundreds of small files I can do this parallel.
To run (lets say 20) tasks parallel I use another commandline script.

The next step is to load these files into the database. For loading into MS-SQL-Server I use an SSIS-package with a for-each loop over all files
within a filesystem directory. For each file a table is created and the data of this file is loaded. If successfull the file is moved to a subfolder "success",
if an error occured the file is moved to a subfolder "error". So if something would stop the process, you could always restart at a defined point.

I build some copies of this package and filesystem-structures (load-folder_x, load-folder_x/success, load-folder_x/error) to start as many packages in parallel
as my I/O would accept.

When every file is in "success" I am almost done: At least data is inside the database. So I can loop through all the tables and do whatever I can do within a database.
All in all I need about 3 times the size of the original file for filesystem data and database file while the process is running.
A single process from file in filesystem to table in database would run with about a 20 to 30 MB/s. It does scale up to the maximum I/O of the server/disk-subsystem/network-connection.

My question is: How to improve this process?

The used tools are all "handmade", not documented and not so easy to understand for third parties.
errorhandling and logging is all "handmade" ... I guess there is a lot of potential improvement and I guess I am not alone with this kind of tasks.


texts: SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

texts:
While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has LOG_BACKUP for the log_reuse_wait_desc?

SQL Server 2012 SP1. Database created just a few weeks ago. No replication, no mirroring, no log shipping, and never has had any of those.

We did backup the database and restore to another instance, it shows SIMPLE and NOTHING in log_reuse_wait on the other instance. But I don't think restore to another instance is a good way to reproduce the problem as restore operation rolls-forward/rolls back transactions.


texts: Multiple Inserts vs. Bulk Insert From Disk for hydrating SQL Server reference tables

texts:
When doing database design, I often use reference/support tables, as we all do. Each time I start up a new project there are inevitably tables which:

Have a predefined set of values.
Will never change, and likely never encounter new records (or very infrequently).

A perfect example of this might be a Country table,.

create table Country
(
  CountryKey  int         not null identity
 ,CountryName varchar(64) not null
 ,IsoNumber   int         not null
 ,Iso3        varchar(8)  not null
 ,constraint pk_country primary key clustered (CountryKey)
);

I have used  a number of different approaches to "hydrate" these kinds of tables, primarily:

A hydration SQL script with insert statements.

insert into Country (CountryName, IsoNumber ...) values ('Canada', ...)

A hydration SQL script, reading from disk, using bulk insert.

bulk insert MyDatabase.dbo.Country from ...

And in extreme cases a programmatic script (f#, python).

My preferred approach is the first if all tables only contain a few records. If the tables are beyond this limit, I typically like a script reading from CSV files. I elect to use CSV in that it's both compact and human-readable.

How is everyone else handling this situation?

  I realize that this is a somewhat opinionated question. But I figured it was worth asking, since it will inevitably have concrete answers with technical rationale.


texts: How to recover a crashed innodb table...?

texts:
I Have a Innodb table i got the error as

mysql> check table City;
+-------------------+-------+----------+----------------------------------------------------------+
| Table             | Op    | Msg_type | Msg_text                                                 |
+-------------------+-------+----------+----------------------------------------------------------+
| world_innodb.City | check | Error    | Incorrect information in file: './world_innodb/City.frm' |
| world_innodb.City | check | error    | Corrupt                                                  |
+-------------------+-------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)

I don't have changed anything in my.cnf.I don't know how it get crashed.
How can i recover it
...

Content of error log are as

120207 11:50:47 InnoDB: highest supported file format is Barracuda.
120207 11:50:47  InnoDB: Waiting for the background threads to start
120207 11:50:48 InnoDB: 1.1.8 started; log sequence number 5713326
120207 11:50:48 [Note] Event Scheduler: Loaded 0 events
120207 11:50:48 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.20-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
120207 11:50:57 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information in file: './world_innodb/City.frm'

EDIT

Someone has opened the City.frm file and modified it's content.So How will I recover it.? I am using innodb_file_per_table.

UPDATE: Adding my.cnf as asked by some peoples.

Innodb Specific variables  in My.Cnf

innodb_file_per_table
innodb_data_home_dir            = /usr/local/mysql/data
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_log_group_home_dir       = /usr/local/mysql/data
innodb_buffer_pool_size         = 1G
innodb_additional_mem_pool_size = 2M
innodb_log_file_size            = 128M
innodb_log_files_in_group       = 2
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 1
innodb_lock_wait_timeout        = 50
innodb_force_recovery           = 4


texts: Can't find table/object error when running an index rebuild

texts:
I am running a index rebuild script:

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

    DBCC DBREINDEX(@TableName,' ',90)

    FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor;
DEALLOCATE TableCursor;

but it fails with the error "Msg 2501, Level 16, State 45, Line 13
Cannot find a table or object with the name "ServiceDeploymentsTable". Check the system catalog."

The script is from https://gallery.technet.microsoft.com/scriptcenter/Script-for-rebuilding-all-8d079754

I have a felling the table might belong to a different object (like dbo or something else) but I don't know how to confirm that or modify the script to deal with that. Or maybe I am compleatly off base.

How can I best deal with this error and rebuild all the indexs in the database?


texts: Adding a primary key to an existing table in mysql is taking time

texts:
22 hours ago I started a script that executes an alter table to add a primary key to a table which has 225 millions of records. MSISDN is the only column.

In showproceslist its process status is "copy to tmp table". Can anyone tell me if this is the correct behaviour and how long it will take to complete?


texts:
I have got the Following Error When i ran the checkDB over the database.

Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=645577338,referenced_minor_id=7) of row (class=0,object_id=821577965,column_id=0,referenced_major_id=645577338,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=645577338,column_id=7) in sys.columns.
Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=757577737,referenced_minor_id=7) of row (class=0,object_id=821577965,column_id=0,referenced_major_id=757577737,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=757577737,column_id=7) in sys.columns.

What are the steps can be taken to resolve the issue. Without affecting the users?


texts: How to grant permission to execute stored procedure in master database from another database

texts:
Our developers have a domain account used by their application that needs to be able to execute stored procedures created in the master database using the "sp_" naming convention from other databases on the server (SQL Server 2012 Enterprise Edition).

I understand this isn't a recommended practice, but the decision to do so was made by someone else with more authority than me.

I have no problem executing such stored procedures myself from any database on the server, but I also have sysadmin privileges and would prefer to grant this user as few permissions as possible.

Here's what I've tried:

--create procedure for purpose of testing
USE [master]
GO
CREATE PROCEDURE dbo.sp_HelloWorld
AS
PRINT 'Hello World!'
GO

--create login for test user
USE [master]
GO
CREATE LOGIN [DOMAIN\user] FROM WINDOWS
GO

--create user in master database
USE [master]
GO
CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user]
GO

--create user in another database on server
USE MyDB
GO
CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user]
GO

--grant execute permission on stored procedure in master database
USE [master]
GO
GRANT EXECUTE ON dbo.sp_HelloWorld TO [DOMAIN\user]
GO

I'm able to run EXEC dbo.sp_HelloWorld myself from any database on the server without issue.

I'm also able to run the following:

USE [master]
GO
EXECUTE AS USER='DOMAIN\user'
EXEC dbo.sp_HelloWorld
REVERT

But trying to execute the stored procedure from within the context of any other database while impersonating the user:

USE MyDB
GO
EXECUTE AS USER='DOMAIN\user'
EXEC dbo.sp_HelloWorld
REVERT

Results in the following error:

  Msg 229, Level 14, State 5, Procedure sp_HelloWorld, Line 1 [Batch Start Line 15]
  The EXECUTE permission was denied on the object 'sp_HelloWorld', database 'master', schema 'dbo'.

What am I missing?


texts: In Postgres, what should be the autovacuum strategy for big tables (hundreds of millions rows) that get added/removed thousands of rows every day?

texts:
I have quite a big table with a lot of daily traffic for reads, inserts and deletions. Currently, it has 392 million of live tuples and 27 million of dead ones. Vacuum settings (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor, etc) are set to the defaults.
Occasionally I do get some performance issues that make queries last >2 minutes when they usually take couple of seconds.
At first, I'd have thought about lowering the vacuum scale factor from the current 0.2 to 0.05 or even 0.01. But, because the autovacuum runs already several times a day (and they may run for a while, >1 hour), I'm not sure if lowering the scale factor would make it worse as it would run even more often, despite meaning that less number of dead tuples the autovacuum will work on.


texts:
Im not even close to a DBA, so plain English please. I recently needed to look into a performance issue on a relatively small PG database, but yet is performing very slow. the server is running PGSql 9.0.22. Its largest table is about 30Mb, and the entire db is hardly 80Mb. However, i found that one of the tables has a text column (which i assume has no width limit) which probably stretches about 10 miles long. I copied the text from one column into Word, an i got 377 pages and a word count of 69814 (as in sixty eight thousand). Again, im not im not expert, but it just doesnt seem right to me. Can PG handle such large text columns? Is this considered a normal design/practice?

EDIT

Thank you both for your response. I would have to research its purpose and types of queries running against it. For the sack of argument, lets assume the column has a list of fruits, and is just a point of reference. Meaning, there's a query which just returns a True or Flase if Avocado is on that list, and then moves on to another action based on the True/Flase results. So theres a text "Fruits" column in the "Foods" table, the text in the column might look like this (with just spaces between each fruit):

Apple Avacado Mango Orange Cherry Grape Peach Srawberry ... and on listing about 68,000 fruits

Would it have been beneficial, or perhaps considered a better design, if each fruit was in its own field. In other words, have 68000 fields under the "Fruits" column, as opposed to one text column with 68000 words?

EDIT
I didn't mean a column for each fruit. I meant a row for each fruit under the fruit column

**Columns - Fruits, Vegetables**

  Rows      -Apple     -Corn
            -Avocado   -Tomatoes
            -Mango
            -Orange
            -Cherry
            -Grape
            -Peach
            -Strawberry

And so on...a list of about 1.5 million

EDIT

In short, I mean having each piece of data in its own field as opposed to using a large text column. So for example, if this was a spreadsheet, the cell A:1 would be the column (header) and A:2, A:3, A:4 the rows (each holding a fruit name). Not all fruits in A:2.


texts: Can I reference a specific column in a remote table (MySQL)?

texts:
I have two databases (MySQL) on two different servers, and one table references a table in the other. However, I am unable to point the reference to a specific column in the remote table for some reason. Can someone help?

Thank you!


texts:
We have SQL Server 2005. Our main table is the archive table which has nearly 200 million rows in it. There are 2000 clients that connect a service so the service writes the information to the archive. We have also another service which gets the clients information from archive as batches and calculate some another information for each row and rewrite them as batches again.

On the webhand-side we have 100-200 users online at a time and most of the queries depends on archive table. I built all possible indexes on archive and I'm using .NET Framework 3.5. I am connecting the database with standard connection string.

The problem is when a user request for one day long report it returns in 10-15 seconds for 50 rows. The one month long reports take more time like 2-3 min for 5k-6k rows. I am not a DBA but we don't have one so i am expected to tackle this problem. Can you make any suggestions for my problem?

Thanks.


texts: Oracle database silent install failing on shared pool/sga size

texts:
I am running the following command:

dbca -silent -createDatabase -templateName foobar.dbt -responseFile NO_VALUE -gdbname foo1 -sid foo1 -emConfiguration NONE -automaticMemoryManagement true -initParams memory_max_target='2048M',memory_target='1638M',sga_max_size='1638M',sga_target='0M',pga_aggregate_target='0M',pga_aggregate_limit='0M'

This fails and I receive the following in the log:

The specified shared pool size or SGA size "80MB" does not meet the recommended minimum size requirement "331MB". This will make database creation fail. Do you want to continue?

Unique database identifier check passed.

/apps/oradata/ has enough space. Required space is 1775 MB , available space is 2507 MB.
File Validations Successful.
Cleaning up failed steps
DBCA_PROGRESS : 5%
Creating and starting Oracle instance
DBCA_PROGRESS : 6%
DBCA_PROGRESS : 8%
DBCA_PROGRESS : 9%
ORA-01078: failure in processing system parameters

DBCA_PROGRESS : 10%
ORA-01034: ORACLE not available

ORA-01034: ORACLE not available

DBCA_PROGRESS : DBCA Operation failed.

The parameters specified are our standard. (1638M is 80% of MAX_MEMORY_TARGET). Usually we set the memory parameters after the database is created, I'm trying to do it during creation instead.

I do not know where the log is grabbing "80MB" from. To my knowledge, it should be set to 0 because I am setting SGA_TARGET to 0.

I suspect that maybe I am doing something contradicting, or maybe I should be considering other parameters to set manually. Could it be that by setting SGA_TARGET to 0 and MEMORY_TARGET to 0, that I am confusing Oracle on whether or not it should be using Automatic Memory Management, and it tries using it anyway which is setting the shared pool to 80MB?


texts: meaning code at comment part from mysqldump result

texts:
on the results from mysqldump i got this part from create procedure before line BEGIN
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `spPayrollProcess` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'PIPES_AS_CONCAT' */ ;
DELIMITER ;;

what mean of part !50003 ?
where i can see meaning of this code ?


texts: Large discrepancy between information_schema database size vs mysqldump database size

texts:
We currently have a database on our server that seems to be excessively large. We run the following query to see all the databases sizes on our server -

SELECT Round(Sum(data_length + index_length) / 1024 / 1024, 1) 'DB Size in MB' FROM information_schema.tables where table_schema like 'some_db' GROUP BY table_schema;

It returns the following -

+---------------+
| DB Size in MB |
+---------------+
| 23663.4       |
+---------------+

However, we also take nightly backups. Looking at this nightly backup for the specific database, it is only 6.5 GB, rather than the 23 GB that the query is reporting. Every other database that we are checking for appears to be roughly the same.

I saw this - https://stackoverflow.com/questions/4709521/mysql-database-size, however we arent using phpmyadmin to takle the backups.  The backups are done inside of a RHEL7 server running MariaDB, and are taken using mysqldump.

The command being run when taking the backup is -

mysqldump -uroot -p --single-transaction some_db > /tmp/somedb_backup.sql


texts: Viewing execution plans for queries with parameters

texts:
I have a sample query generated by an application (Microsoft Dynamics AX 2012 in this case) which is ineffective performance-wise (cross joins, order by etc.)
I would like to display its execution plan and attempt to tune it by indexing or rewriting some parts of it.
I cannot just copy/paste it into SSMS because there are numerous parameters of many data types. I don't even know what are the values for these parameters.

Is there a way to quickly identify the execution plan of this query? Maybe by querying some DMVs? I got the query text from monitoring software which must have done it.

SELECT 2                                 AS f1,
       T3.RECID                          AS f2,
       T4.RECID                          AS f3,
       T4.GENERALJOURNALACCOUNTENTRY     AS f4,
       T4.LEDGERDIMENSION                AS f5,
       Sum(T6.TRANSACTIONCURRENCYAMOUNT) AS f6,
       T6.TRANSACTIONCURRENCY            AS f7,
       T6.MONETARYAMOUNT                 AS f8,
       Sum(T7.ACCOUNTINGCURRENCYAMOUNT)  AS f9,
       N'aaa'                            AS DATAAREAID,
       1                                 AS RECVERSION,
       5637144576                        AS PARTITION,
       IDENTITY(bigint, 1, 1)            AS RECID
INTO   [##ax_tmp_tim99_151_7623]
FROM   SUBLEDGERJOURNALENTRY T1
       CROSS JOIN ACCOUNTINGDISTRIBUTION T2
       CROSS JOIN TAXTRANS T3
       CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRY T4
       CROSS JOIN ACCOUNTINGDISTRIBUTION T5
       CROSS JOIN ACCOUNTINGDISTRIBUTION T6
       CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRYDISTRIBUTION T7
WHERE  ( ( T1.PARTITION = @P1 )
         AND ( ( ( ( T1.TRANSFERID = @P2 )
                   AND ( T1.LEDGER = @P3 ) )
                 AND ( T1.TYPE <> @P4 ) )
               AND ( T1.TYPE <> @P5 ) ) )
       AND ( ( T2.PARTITION = @P6 )
             AND ( ( T2.ACCOUNTINGEVENT = T1.ACCOUNTINGEVENT )
                   AND ( ( ( T2.MONETARYAMOUNT <> @P7 )
                           AND ( T2.MONETARYAMOUNT <> @P8 ) )
                         AND ( T2.MONETARYAMOUNT <> @P9 ) ) ) )
       AND ( ( ( T3.PARTITION = @P10 )
               AND ( T3.DATAAREAID = @P11 ) )
             AND ( T3.SOURCEDOCUMENTLINE = T2.SOURCEDOCUMENTLINE ) )
       AND ( ( T4.PARTITION = @P12 )
             AND ( ( ( ( T4.SUBLEDGERJOURNALENTRY = T1.RECID )
                       AND ( T4.POSTINGTYPE <> @P13 ) )
                     AND ( T4.POSTINGTYPE <> @P14 ) )
                   AND ( T4.POSTINGTYPE <> @P15 ) ) )
       AND ( ( T5.PARTITION = @P16 )
             AND ( T5.RECID = T2.PARENTDISTRIBUTION ) )
       AND ( ( T6.PARTITION = @P17 )
             AND ( ( ( T6.SOURCEDOCUMENTLINE = T5.SOURCEDOCUMENTLINE )
                     AND ( T6.PARENTDISTRIBUTION = T5.RECID ) )
                   AND ( ( ( T6.MONETARYAMOUNT = @P18 )
                            OR ( T6.MONETARYAMOUNT = @P19 ) )
                          OR ( T6.MONETARYAMOUNT = @P20 ) ) ) )
       AND ( ( T7.PARTITION = @P21 )
             AND ( ( ( T7.SUBLEDGERJOURNALACCOUNTENTRY = T4.RECID )
                     AND ( T7.ACCOUNTINGDISTRIBUTION = T6.RECID ) )
                   AND ( ( ( T7.ACCOUNTINGCURRENCYAMOUNT >= @P22 )
                           AND ( T6.TRANSACTIONCURRENCYAMOUNT >= @P23 ) )
                          OR ( ( T7.ACCOUNTINGCURRENCYAMOUNT < @P24 )
                               AND ( T6.TRANSACTIONCURRENCYAMOUNT < @P25 ) ) ) ) )
GROUP  BY T3.RECID,
          T4.RECID,
          T4.GENERALJOURNALACCOUNTENTRY,
          T4.LEDGERDIMENSION,
          T6.TRANSACTIONCURRENCY,
          T6.MONETARYAMOUNT
ORDER  BY T3.RECID,
          T4.RECID,
          T4.GENERALJOURNALACCOUNTENTRY,
          T4.LEDGERDIMENSION,
          T6.TRANSACTIONCURRENCY,
          T6.MONETARYAMOUNT


texts: SQL SERVER Verifying mirror status from 2 weeks ago

texts:
How we can verify if the mirror was working fine two weeks ago ??

I was checking the sqlerror log, I'm not sure whether the mirroring sync status will be logged, if so, I don't have that log. because the log was reinitialized. we do take backups. So, my other question is what to restore from tap backup to retrieve error log ( is it master db, or msdb )?

Your help is appreciated!!


texts:
In MS SQL Server 2008 R2 there is replication of table with fulltext index on it.
But stoplist that is associated with replicated fulltext index doesn't replicate.

Is there any possibility to replicate stoplist also?


texts: How to store/organize all of these scripts?

texts:
I am about 10 months in to my DBA role and I am already accumulating quite the collection of scripts. Does anyone have any best practices or recommendations on how to store all of these scripts? I manage about 70 instances of SQL server and am looking for a way to have quick access to all of these scripts as I need them throughout the day and right now they are just piling up in a file system folder.

Is this the best way? Are there 3rd party tools that help organize them? Anyone have an amazing solution/recommendation?

Thanks ahead of time!


texts: Not able to access stored procedure from my application

texts:
I have written few stored procedures and executed in my server that is having  php myadmin  now while calling this stored procedure from my application  it is showing
following exception

SQLException1 java.sql.SQLException: User does not have access to metadata required to
determine stored procedure parameter types. If rights can not be granted, configure
connection with "noAccessToProcedureBodies=true" to have driver generate parameters that
represent INOUT strings irregardless of actual parameter types.

While searching i find following query to resolve this exception:

GRANT SELECT,INSERT,UPDATE ON mysql.proc TO 'user'@'localhost';

my problem is that while running this query i am getting following error

#1142 - SELECT,INSERT,UP command denied to user 'qqq'@'localhost' for table 'proc'

but i have given all permission to user

 GRANT ALL ON *.* TO 'qqq'@'localhost';

So how to access stored procedure from my application
I am using shared hosting server .


texts: Does CREATE INDEX on Aurora MySQL 5.6 block INSERT / UPDATE?

texts:
I want to add a secondary index to a table on AWS Aurora MySQL engine 5.6.10a in production. The MySQL doc says for an index create like this:
CREATE INDEX name ON table (col_list);

The table remains available for read and write operations while the index is being created. The CREATE INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html#online-ddl-index-operations
Is this also the case for the Aurora flavor? Do I need any special syntax to ensure INSERT / UPDATE don't block? Are there any gotchas that I need to be aware of?
This question seems to indicate that presence of a foreign key constraint might affect the behavior but am I missing anything else? MySQL create index with LOCK=NONE still locks a table


texts: Trying to start a newly created cluster in PostgreSQL on Windows

texts:
I just created a new cluster using PostgreSQL 8.3 and looks like everything worked.  What I am having issues with is trying to start the server of this new cluster.  My two clusters are on different ports.  When looking online to start the new cluster, I tried executing the following line:

postgres -D C:\my\new\cluster\data\directory

This is the error I get:

  The server must be started under an unprivileged user ID to prevent
  possible system security compromises.  See the documentation for more
  information on how to properly start the server.

So I know I am running the command from administrator but I don't know how to run it from an "unprivileged user ID".  Any help would be great!


texts: Will a non-clustered index on a column with covering index on all the columns of table affect the Delete statement Records)?

texts:
I am trying to delete thousands of records say 35K from a table which consists like 10 million records.

Delete * from Table where ScenarioID=859

I have also tried bulk deletion

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM tableWHERE ScenarioID= @ID
    SET @RowsDeleted = @@ROWCOUNT
END

I would like to ask

Generally How much time it would take to delete such no of records?
If there is any Index on that table then is it gonna affect the delete statement?
Note: I have a non-clustered index on a column ID. Does this have any affect on performance of if i delete records based on that ID
are there any common bottle-necks that may cause delete to take long time than it should take ?

Is there any thing going wrong in this plan ?

PS: My database is hosted in Azure and it takes just below 10 seconds to execute the same command in Azure portal


texts: Manage Postgres user permissions with many databases and users

texts:
How do you manage users' permissions efficiently when your company has a lot of databases and users?

My company has > 20 reporting databases. Whenever I need to change permission for some users, I need to use psql \dp and fire some queries to alter their roles.

It's ok for now since we only have 5 users, but surely the number would increase and make it very hard to manage it properly via the terminal. Could you suggest me some ways (or tools) to efficiently manage database permissions?


texts: How to preserve the original order of elements in an unnested array?

texts:
Given the string:

  'I think that PostgreSQL is nifty'

I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details and would like to join an unnested array of that string on this dictionary.

So far I have:

select word, meaning, partofspeech
from unnest(string_to_array('I think that PostgreSQL is nifty',' ')) as word
from table t
join dictionary d
on t.word = d.wordname;

This accomplishes the fundamentals of what I was hoping to do, but it does not preserve the original word order.

Related question:
PostgreSQL unnest() with element number


texts: How do I manage SQL Server Management Studio (SSMS) Add-In?

texts:
A variety of vendors provide SQL Server Management Studio (SSMS) Add-Ins, such as Red Gate's SQL Prompt and the SSMS Tools Pack.

Is there a way in SSMS to control or configure these add-ins (enable/disable, install/uninstall)?

Is there an add-in or plug-in manager?

If not, is there another way to review and control them?

An old Stack Overflow question references some registry keys, but they appear to be relevant only to a 2008 version of SSMS. They aren't present on my system. Another answer references some folders, but only one of them exists on my system (ProgramData\Application Data\Microsoft\MSEnvShared\Addins) and it has only one of several installed add-ins (Sentry One Plan Explorer).

Other keywords: addin manager, addon manager, add-on manager, plugin manager, plug-in manager, extension manager


texts:
I am trying to run the query on an Oracle database

select
  to_date(trunc(sysdate) || '13:00:00','DD-MON-YY HH24:MI:SS')
from dual;

and get the error

ORA-01830: date format picture ends before converting entire input string

Please suggest.


texts:
I'm trying to replace multiple lines using the mariadb regex_replace function.

CREATE TABLE `test` (
  `entry` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`entry`) VALUES ('LINE1\r\nLINE2\r\nLINE3\r\nLINE4\r\nLINE5');

This creates one entry which the following content:

LINE1
LINE2
LINE3
LINE4
LINE5

Let's say I want to replace LINE2 through LINE4 with ''. This should be the result:

LINE1
LINE5

I've tried so many different regexes, but I'm out of ideas, as none of these work (no lines are removed).

select regexp_replace(entry, 'LINE2.*LINE4', '') from test
select regexp_replace(entry, 'LINE2[.\r\n]*LINE4', '') from test
select regexp_replace(entry, 'LINE2[.\R]*LINE4', '') from test
select regexp_replace(entry, '(*CRLF)LINE2.*LINE4', '') from test
select regexp_replace(entry, '(*ANYCRLF)LINE2.*LINE4', '') from test

I've also tried selecting using RLIKE, but it also does not work (no entries returned):

select entry from test where entry RLIKE 'LINE1.*LINE4'
select entry from test where entry RLIKE '(*ANYCRLF)LINE1.*LINE5'

It should be possible, so I don't really know why this isn't working.

I'm using latest mariadb on debian (10.3.22).

To make sure this has nothing to do with the switch to PCRE2 in 10.5.1., I also check the historical mariadb pcre docs, but the syntax did not change.


texts: Always fetch 3 records in a particular order

texts:
I have a query which gets 3 books (ABC1,ABC2,ABC3) and their details. I need them in a particular order for pivoting so I have a Row number column for the same. People read none/some/all books.

Table:

StudentID|||BookName|||RowNumber
1000|||ABC1|||27
1000|||ABC2|||31
1000|||ABC3|||28
1005|||ABC2|||28
1005|||ABC3|||20
1005|||ABC1|||25
1006|||ABC1|||12
1006|||ABC2|||14
1006|||ABC3|||15

So my data would be something like the above. I need for each student, ABC1,ABC2,ABC3 should come in the order AND Rownumber to be UPDATED in descending order. So for student 1000:

ABC1||27,ABC2||28,ABC3||31

For student 1005:

ABC1||20,ABC2||25,ABC3||28

For 1006: no change (they're already in the right order).

I have all the above data into a temp table and master data into another temp table. I'm trying to write an update statement but nothing works as expected. I need some help.


texts: How to connect to a local server in Oracle SQL Developer like SQL*Plus with no TNSlistener?

texts:
How can I use SQL Developer to connect to a local Oracle 11g server?  I didn't enable TNS Listener, but SQL*Plus works well.

@update1:

I copied all *.jars from $ORACLE_HOME/app/product/11.2.0/dbhome_2/jdbc/lib/ to jdbc directory in SQLDeveloper

And I get error: no ocijdbc11 in java.library.path

So I traced down sqldeveloper.sh , and add some parameters to it:

However , another error discovered:

Certain wired string kept changing each time i connect

@update2:

If only I add

export CLASSPATH=$CLASSPATH:/Application/Binaries/oracle_11g/app/product/11.2.0/dbhome_2/jdbc/lib

I'll get the following error:


texts: Sql Server Agent is part of the sysadmin role even tough Sql Server says it's not

texts:
I created a Windows account with no extra rights other than domain user and set it as the SQL Server Agent service account with the SQL Server Configuration Manager.

The BoL/MSDN says that this user should be part of the sysadmin role to work but at this point I haven't taken any action to make this happen.

I expected that the account could do nothing but it appears it's ... a sysadmin! If you put the following statement in a job step it will confirm this:

IF IS_SRVROLEMEMBER ('sysadmin') = 1 print 'sysadmin'

The job step is a t-sql step with sa as owner so the SQL Server Agent service account is used.

Now for the question. It seems SQL Server Configuration Manager does something that makes this a sysadmin or at least when it is executing the step. BUT at the same time it does not show up in the list of the sysadmins in the GUI.

All the reports or queries I come up with (including SSMS) do not list this Windows account as part of the sysadmin role.

I have the feeling I am overlooking something.


texts:
I have a node table that looks like this, plus it has an inserted_at column:

id        |  parent_id
----------+-----------
1         |  nil
2         |  1
3         |  1
4         |  2
5         |  2
6         |  3
7         |  4
8         |  5
9         |  6
10        |  3

It's a representation of a binary tree that looks like this:

             1
          /     \
        2         3
     /   \       /   \
    4    5      10    6
   /     /             \
  7     8               9

I'm trying to write a recursive SQL query that gets a list of nope nodes - nodes that have 1 or 2 open places below them - that is the list of nodes that aren't listed as a parent_id to other nodes 2 times.

This "open_list" would contain 4,5,10,6,7,8,9 because all those nodes have less than two children. So is this possible in SQL? One query that produces the "open list" of a binary tree.

Maybe I'd have to be a recursive CTE table using a WITH statment that some how has a join on itself ?

With recursive (
   select *
   join on self... recursive
)
select * from recursive

That's where my totally amateur intuition goes. Can someone who has a bit more experience guide me to the answer?


texts: Union of data from all tables with name containing a substring

texts:
I have 500+ tables all containing a column called COLUMNDATA. The table names are like: TABLE_FIRST, TABLE_SECOND, ... ,TABLE_FIVE_HUNDREDTH. What I am trying to get is a column containing all the unique entries from COLUMNDATA across all of these tables. The problem is made a little more challenging because all tables in the DB contain this column, and I am not interested in data from the tables which aren't called TABLE_NUMBER.

I have tried various recipes including UNION and INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'TABLE%' but to no avail.

If anyone has an idea that would be great! Otherwise I'll just stick with the 1000 line script I've got already. For now I have a nasty but gets-the-job-done

CREATE TABLE TAB_NAME
AS
SELECT COLUMNDATA FROM TABLE_FIRST
UNION
SELECT COLUMNDATA FROM TABLE_SECOND
UNION . . .

ad infinitum


texts:
I have a replica-set which consists of 3 amazon ec2 nodes : primary, secondary and an arbiter
the data volume size is ~400GB and currently it is 90% full. i need to resize the volumes to a more appropriate size.
I'm looking for the best solution to do it with minimum downtime/no-downtime and minimum resource overhead - meaning use the least amount of CPU/IO/network needed for the resize process. time is not a big issue as i believe it wouldn't take more than 24 hours.

Here are the logical steps i have come up with so far:

create, attach & mount new volume for secondary
stop mongod process on secondary
copy data from old volume to new volume
restart mongod process on secondary with new dbpath directing to the new volume  OR  replace mounting points for old and new volumes, and then restart mongod process
ensure secondary is finished syncing the remaining data

===============================================================

create, attach & mount new volume for primary
stepdown primary
ensure all processes bounced correctly to the new primary
repeat 2-5 (on the current secondary)
[optional] stepdown primary - return to original setup

my questions:

a) when copying files from one volume to another, assuming the bigger one is also faster (more PIOPS), is it efficient to use parallelism ? as in copy a maximum of ~10 files at a time?

b) some would say if i already put the effort for copying the data then i should simply wipe the data and let it resync anew - however i'm worried about the network and read impacts on the "living" primary which may degrade performance for my applications. is that a right assumption?

c) how can i measure/verify that the oplog retention is sufficient for resyncing the database after copying the files?

d) is there a better idea/solution ?

mongo version - 3.0.10
storage engine - mmapv1
os - amazon linux
journaling is on

thanks


texts:
I am writing some T-SQL code to retrieve the file names that make up a file group so that I can remove the files and the related filegroup.

The way that I coded it is below:

set @cnt = (select COUNT(*)
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id
where sysFG.name = @FGName)

set @rep = 0
select @cnt

    while @rep < @cnt
    begin
        set @rep = @rep + 1
        set @FileName = (SELECT top 1 dbfile.name
            FROM sys.database_files AS dbfile
            INNER JOIN sys.filegroups AS sysFG
            ON
                dbfile.data_space_id = sysFG.data_space_id
            where sysFG.name = @FGName)

        set @sql = 'ALTER DATABASE '  + @DBName
        set @sql = @sql + ' REMOVE FILE '
        set @sql = @sql + RTRIM(@FileName)
--      set @sql = @sql + ' (''' + rtrim(@FileName) + ''')'
        exec sp_executesql @sql
        print @sql
    end

Thought was to get the file count, then loop through the file names and remove them one by one.  The problem is the TOP 1 code and the fact that the first file of the file group is always returned.

Any assistance is greatly appreciated.


texts:
I cannot use wiredTiger as a service for MongoDB.

I followed this step by step guide;
http://docs.mongodb.org/manual/release-notes/3.0-upgrade/

Step-1: Start 3.0 mongod.OK, I did.
Step-2: Export the data using mongodump. OK
Step-3: Create data directory for WiredTiger. OK - user permissions were adjusted.
Step-4: Restart the mongod with WiredTiger. OK

mongod --storageEngine wiredTiger --dbpath <newWiredTigerDBPath>
And yes, mongod worked. But the Step-5 says that "Upload the exported data using mongorestore." However my console remains busy and says "waiting for connections on port 27017" because the last command does not include a service like service mongod start.

Then I tried editing my /etc/mongod.conf file to run as a service. But I couldn't. I added wiredTiger directory as dbpath but I guess I need to edit /etc/init/mongod.conf file because it contains some codes that change the permissions of the db directory.

After spending 8 hours, I don't think there's sufficient documentation to help,
 so I'd like to ask;
What should I do in order to be able to use wiredTiger with MongoDB?
I'm wondering if there's anybody who has managed to achieve this?

console

Edit on 2015-04-01:
I tried change-storage-engine-to-wiredtiger section that is on the documentation.


texts: Run a specific SQL Server job step from a stored procedure

texts:
I have a job with 5 steps that are scheduled to run after each other.

I want to have a stored procedure where a user can execute the stored procedure and give the step number as a parameter.

Only this job step then will be executed and no further steps.

What would be the best way to accomplish this?


texts:
I had a table to store user votes for other users. So user1 can vote for user2 (vote up or vote down). One user should be able to vote for many users, but not repeatedly to the same user; user1 can only vote once for user2. I do not think my table is properly structured to reflect this. Currently each time a user gets a vote up/down an ew row is created for this with either a 1 in the vote_up/vote_down column. What changes should I make to properly structure this table?

vote_id              int
voter_id         int
voted_for_id         int
vote_down        int  //stores the up votes
vote_up              int  //stores the down votes

To clarify users 1-19 can all vote for user 20 once each and user 20 can vote for 1-19 once each. At the end of the day I want to get a tally of how many votes each user has for both up votes and down votes each.


texts: Need to list SQL Windows auth users on each SQL server and rename the domain name

texts:
We are doing an AD Domain NetBIOS rename. We have about 20 SQL servers, and on each we have a series of logins like OLD.DOM\sqluser1 etc. I want to do two things, and a third would be a bonus. I'm not sure how to put these together:

SELECT * FROM Sys.login_token

I think this will list the users I mentioned above? I'd like to put that into a variable and then run this I think:

ALTER LOGIN "OLD.DOM\variable_holding_username"
WITH NAME="NEWDOM\variable_holding_username"

So if I can go on a server, list out all of those OLD.DOM\ logins and ALTER each of them, I'd be happy.

I'd be extra happy if I could hit each SQL server remotely and run through this, instead of doing each one separately.


texts: Does CommVault use native SQL Server backup COM objects or the VSS?

texts: We are being told by a parent company that we will be transferring our backups to COMM Vault and are trying to gather some information regarding how the product works as far as whether it is using snapshots or the native SQL Server backup objects.  Can anyone tell me a little more about how COMM Vault backups work underneath?  A couple of us here have gone through the documentation and it appears that snapshots are not the only choice but I thought I would post the question here as well.  Thank you in advance.


texts: Log shipping with a monthly archival process

texts:
We will be implementing log shipping in our production environment in the next weeks.

We perform a monthly "cleanup" of the database, whereby we set the database to the SIMPLE recovery model, copy records to an historic database, then delete from the original to free up space.

I know that changing the database recovery model to simple will break log shipping (because the LSN chain will be broken). In that case, I would have to restore a full backup on the secondary server and start again with the log shipping process. I don't want to do this.

I am an 'accidental DBA' and inherited this archiving process. The process is as simple as selecting rows from the live database, inserting them into an archive database, and then deleting them from the live database.

My concern is that if I keep the database in the full recovery model during the deletion, the transaction log might grow in such a way that it could cause space issues and/or make the secondary server fall considerably behind the primary.

What options do I have here? Any advice will be appreciated.


texts: I'm currently studying Oracle Architecture and Database Memory Structures. I'm a newbie in the subject and I just read that a "Shared Pool" is "a data structure that caches various constructs that can be shared among users". So my question is: what is the meaning of a "construct" in this context? Thanks!


texts: Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?

texts:
Setup

DROP TABLE IF EXISTS #EmptyTable, #BigTable

CREATE TABLE #EmptyTable(A int);
CREATE TABLE #BigTable(A int);

INSERT INTO #BigTable
SELECT TOP 10000000 CRYPT_GEN_RANDOM(3)
FROM   sys.all_objects o1,
       sys.all_objects o2,
       sys.all_objects o3;

Query

WITH agg
     AS (SELECT DISTINCT a
         FROM   #BigTable)
SELECT *
FROM   #EmptyTable E
       INNER HASH JOIN agg B
                    ON B.A = E.A;

Execution Plan

Problem

This is a simplified repro for a phenomenon I hadn't noticed before today. My expectation for an inner hash join would be that if the build input is empty the probe side should not be executed as the join can return no rows. The above example contradicts that and reads the 10 million rows from the table. This adds 2.196 seconds to the execution time of the query (99.9%).

Additional Observations

With OPTION (MAXDOP 1) the execution plan reads no rows from #BigTable. The ActualExecutions is 0 for all operators on the inside of the hash join.
For the query SELECT * FROM #EmptyTable E INNER HASH JOIN #BigTable B ON B.A = E.A- I get a parallel plan, the scan operator on the inside of the hash join does have ActualExecutions of DOP but still no rows are read. This plan has no repartition streams operator (or aggregate)

Question

What's going on here? Why does the original plan exhibit the problem and the other cases don't?


texts: SQL Server 2012 Free Pages in Buffer Cache Not Being Used

texts:
SQL Server Version:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

This instance is on a shared physical server with 256 GB of Physical Memory.
- The 2012 instance is set at a maximum memory of 97.7 GB.
- The 2008 R2 enterprise instance with a maximum memory of 97.7 GB.

Patches were deployed over the weekend and the database server was rebooted.

Prior to the patches I was observing Page Life Expectency from Spotlight on the 2012 instance.  I observed that the 2012 instance was using ~30 GB for Buffer Cache and ~3 GB for Proc Cache.  What confused me is that Spotlight showed the instances was using 100% of its available memory, 97.7 GB.

30 + 3 < 97 I said to myself so I began to look at the memory drill downs in Spotlight.  What I was shown was that there was 55 GB of free pages in the buffer cache.

As a sanity check I checked out what the 2008 R2 instance was doing, compared to the 2012 instance it handles much less volume.  I was shocked when I saw the 2008 R2 instance using a total of 70 GB of it's 97.7 GB and 35 GB of buffer cache.  The PLE on this instance was 37 Days!

Back to the 2012 instance, while the PLE was below 300 seconds, the Buffer Cache Hit Ratio never dropped more then a .1 of a percent.  However we were getting I/O wait alarms and I felt this was due to making too many trips to disk.

After the patching and the server with both instances was rebooted I began to watch the 2012 instance again.  In the morning buffer cache was above 80 GB and PLE was doing very well.  Around 4 PM, I see a large drop (~20 GB) in buffer cache and a corresponding increase in free pages.  As this was occuring, PLE started to suffer and I/O started to pick up again.

So all that long winded story to the question, what causes SQL Server, possibly specific to 2012 to dump it's buffer cache into free pages while PLE is nosediving?

Doesn't that seem to achieve the opposite effect?


texts:
I am working for a mortgage company in migrating a legacy application to new application for managing claims, means lender fore close a loan and request for a claim to get its funds from government or private firm based on the insurer. If government is insurer, claim is made to government, if private firm is insurer its made to private.

Currently in the legacy application, claim is taken as parent and loan is taken as child. But i want just opposite.I want loan as parent and  claim as child. There will be many claims for a loan, but single loan for a claim.

Current structure is claim id and loan id will be there in claim table.In case of different types of claims, there are different tables (claim type tables), which i need to eliminate. Means, suppose it is a government claim, it has another table with government insurance related columns , plus the claim related columns. Similarly for private claim,  it has another table with private insurance related columns , plus the claim related columns. So the claim related columns appear in each type of claim related tables, here it is government and private which are common.

So my approach to design the database to make loan as parent and claim as child is as below:

Group all loan related columns to a table called loan
Group all claim related columns from different claim type tables to another table called claim
Remaining columns in claim type tables will be group as different tables along with loanid and claimid as forien key.

Is this approach is correct or is there any better approach?


texts: Check if database backup using pg_dump is in progress

texts: I am using PostgreSQL 9.2 and using pg_dump to create DB backup. How could I determine is db backup and restore is in progress. So I can prevent another user in initiating another.


texts: How to keep row ordering with DISTINCT command or how to add row number to cross apply

texts:
I have problem that after distinct, order direction of Variable column change.

Here is example

Data Source

DECLARE @Table TABLE
(
    FirstName NVARCHAR(255),
    LastName NVARCHAR(255),
    Variable1 NVARCHAR(255),
    Variable2 NVARCHAR(255),
    Variable3 NVARCHAR(255)
)

INSERT INTO @Table VALUES

('FirstName 1','LastName 1', 'D', 'B','A'),
('FirstName 1','LastName 1', 'D', 'C','A'),
('FirstName 2','LastName 2', 'A', 'B','C')

First select with proper ordering (order is done by Cross Apply sequence)

/* Ordering is okay, but table containts duplicates */
SELECT
    FirstName, LastName, v.VariableName, v.Value

FROM @Table
CROSS APPLY (
    VALUES
        ('Variable1',Variable1),
        ('Variable2',Variable2),
        ('Variable3',Variable3)
) AS v(VariableName, Value)

Second select is reordered by Distinct command

/* Ordering is okay, but table containts duplicates */
SELECT DISTINCT
    FirstName, LastName, v.VariableName, v.Value

FROM @Table
CROSS APPLY (
    VALUES
        ('Variable1',Variable1),
        ('Variable2',Variable2),
            ('Variable3',Variable3)
) AS v(VariableName, Value)

What I want

I need second select but with ordering of cross apply sequence. I thought about adding row number for every person but I don't know how to do it

By "cross apply sequence" I mean ordering by cross apply order in code (ordering by syntax). I don't know how to describe it better, therefore I attached an image.


texts: How to clear temp database content in SQL Server?

texts:
As the title implies, I am going to clear the content of my temp database in SQL Server.  SolarWinds is generating a critical alert and I want to clear the tempdb workfiles and worktables.

How can I do this? Can I restart the MSSQLSERVER service to clear the content?


texts:
Apologies for asking a question that I suppose is very basic. I'm just starting out with Streams.
I'm trying to read a sequential  files containing a timestamp field and a value field, like this:

150601172623+0530
150601172041+0530

I want add this value to oracle 11g database as a timestamps. Is there any way in datastage 9.1.2 for this conversation...?

Result should be like below

150601172623+0530 -> 2015-06-01 05:26:23 PM
150601172041+0530 -> 2015-06-01 05:20:41 PM


texts: mysqldump - select table in database only if it contains a specific string

texts:
(I'm new to Database Administrators, and am not a Database Administrator. However I originally asked this question on Stack Overflow (here) but didn't get any answers. Sorry if this is not the right site to ask this question, but I really need an answer).

I have a pretty big local WordPress Multisite installation, and this is mirrored on a  live server, so I can develop a site locally and upload it for a client to see (it's sub-domain based). I have written a custom shell script to upload the database from my local machine to the live server, which uses sed to change all instances of the local domain name to the live domain name. It then commits and pushes it by git and the live server automatically pulls it, takes a backup and applies the new file.

However, this has become problematic in the last month or so. As I've got more client work, and they have been editing their sites live on the server, and I've been pushing new work to the server for other clients to take a look at, I've been overwriting changes on the live database.

What I need is to be able to add a flag or something when I call the shell script file (sh push.sh) that indicates what tables in the database need to be pushed live. Each site in the WordPress Multisite database has a number, e.g network_17_posts. So if I could a table number state when I called my shell script, like sh push.sh --table=17 and it would only upload site 17's data, and not overwrite anything else, that would be awesome. As a bonus, if I could specify multiple numbers, so I could upload multiple sites at a time, that would be amazing!

As a reference, here is my current shell script for pushing the database live (it could probably be 10x better, but I'm primarily a front-end/PHP dev, not shell script!):

rm -rf db_sync.sql
mysqldump -u root -ppassword db_name > db_sync.sql
sed 's/localdomain.dev/livedomain.com/g' db_sync.sql > new_db_sync.sql
rm -rf db_sync.sql
mv new_db_sync.sql db_sync.sql
git add db_sync.sql
read -p "Enter Commit Message: " commit_message
git commit -m "$commit_message"
git push -u web master

Thanks for any help :)


texts: What should my SPN entries look like for each SQL instance?

texts:
I'm finding contradictory information for how exactly to format SPNs (Service Principle Names) to get the proper Kerberos connections, and how many I need for each SQL instance.
This 2017 MS document contains the following:

Beginning with SQL Server 2008, the SPN format is changed in order to support Kerberos authentication on TCP/IP, named pipes, and shared memory. The supported SPN formats for named and default instances are as follows.

Named instance: MSSQLSvc/FQDN:[port|instancename]
Default instance: MSSQLSvc/FQDN:port|MSSQLSvc/FQDN

The new SPN format does not require a port number. This means that a multiple-port server or a protocol that does not use port numbers can use Kerberos authentication.

I took this last paragraph to mean that I only need a single entry, one of the following:

Named instance: MSSQLSvc/sqlbox1.mydomain.org/instance2
Default instance: MSSQLSvc/sqlbox1.mydomain.org

That seems to contradict this older (2011) MS document, not just about the port number, but also concerning what name to use:

To create the SPN, you can use the NetBIOS name or the Fully Qualified Domain Name (FQDN) of the SQL Server. However, you must create an SPN for both the NetBIOS name and the FQDN.

When I look at the SPNs that already exist in my environment, I see a wide variety of combinations, some servers have up to 4 entries:

MSSQLSvc/sqlbox1
MSSQLSvc/sqlbox1:1433
MSSQLSvc/sqlbox1.mydomain.org
MSSQLSvc/sqlbox1.mydomain.org:1433

Even MS's own Kerberos configuration manager seems to want to generate the last two versions (with appropriate obfuscation):

Similarly for existing named instances, I see a weird mix, some of them almost certainly invalid:

MSSQLSvc/sqlbox1:1522
MSSQLSvc/sqlbox1:instance2
MSSQLSvc/sqlbox1.mydomain.org:1522
MSSQLSvc/sqlbox1.mydomain.org:instance2
MSSQLSvc/sqlbox1.mydomain.org/instance2
MSSQLSvc/sqlbox1.mydomain.org:1522:instance2

So what should my DSNs actually look like, for both default and named instances, if I just use TCP in my environment?
Should I include the port, or not? Or include one with the port and one without?
Use the FQDN only, or do I need the entries with just the Netbios name? Or would that only be if we were using named pipes (which we are not)?
(For context, we run SQL 2005 through 2014, some clustered, others standalone. Connectivity is via TCP only, named pipes is disabled in config manager. We will be fixing/creating these manually instead of allowing the SQL service account to create them upon server start.)


texts: SQL server create temporary function as a CTE

texts:
I need to create a temp function,
for use in a bigger query but I don't have create permissions on the database (except for #TEMP tables).

Is there any way that I could use a CTE or a #temp query for this purpose.
Maybe I'm missing something really simple here.

Example (of what it could look like):-

with add1(x) as
  return x+1

select add1(v.Value1), add1(v.Value2)
from Values v

Values table

Id Value1 Value2
1  1       4
2  2       5
3  3       6

EDIT

As per Aaron Bertrand's answer I managed to get something working.

CREATE TABLE #myTempTable
(
  id int identity(1,1) primary key,
  amount  int,
  col1  varchar(10),
  col2 varchar(4)

);
-- quite a few more cols in my actual temp table,
-- omitted to show the real issue

INSERT #myTempTable(amount,col1, col2) VALUES(10,'a1', 'b1'),(15,'a2','b2');

;WITH processed AS
(
  SELECT * FROM #myTempTable AS r
  UNPIVOT (Result FOR [Value] IN r.Amount) unp
  CROSS APPLY
  (

     /******** COMPLEX FUNCTION HERE ********/
     /**** Applies to output of unpivot *****/

     SELECT unp.Result + 10 [Processed_amount]

  ) AS a
  --PIVOT (max(orig) FOR Value IN ( amount)) AS p2
)

select top 10 [Processed_amount], * from processed

The final PIVOT is messing up the results though.
I was wondering why that is required.

I am still trying to wrap my head around the various parts of UNPIVOT and PIVOT.

EDIT2

Please see my answer,
I got it to work where we need only column to be processed ..


texts:
In Sqlite 3 I'm trying to figure out how to select rows based on a minimum value.  I think that I'm limited by not knowing enough of the related terminology to effectively search google.

The table looks like:

num         text        num2
----------  ----------  ----------
0           a           1
0           a           2
1           a           3
1           b           4

I want to get the rows where num2 is 1, 2, and 4.  I want to do the selection based on the minimum value of num for each unique value of the text column.

So, for text = 'a', the minimum value of numis 0, so I want rows 1 and 2.  For text = 'b', the minimum value of num is 1, so I want row 4.

Using various combination of group by, I'm able to get either rows 1 and 2 or rows 1 and 4.  I feel like I'm missing a SQL component that would do what I want, but I haven't been able to figure out what it could be.

What is the proper way to do this type of query?

Possible Solution

I've found a way to do this.  I'm not reputable enough to answer my own question, so I'm doing the update here.  I'm not sure if it is always correct or what the efficiency is like.  Any comments are welcome.

I used a compound select statement, where one query finds the minimum value of num for each unique value of text:

sqlite> select num, text from t group by text having num = min( num );
num         text
----------  ----------
0           a
1           b

Then I joined this with the full table to get all rows matching these two columns.

sqlite> with u as
      ( select num, text from t group by text having num = min( num ) )
        select t.* from t join u on t.num = u.num and t.text = u.text;
num         text        num2
----------  ----------  ----------
0           a           1
0           a           2
1           b           4


texts:
I'm having an issue with a SQL Server log file which will not maintain a steady amount of space held on a HDD which it has been issued. The drive is ~50GB and the log file is growing to occupy this quite often.

The database is set to FULL recovery mode, with hourly differential backups. I understand that this should allow the log file to truncate and use space already allocated to it?

When checking the status of the log file using DBCC LOGINFO it returns that the status of most VLF's within the files are set to '2' which shows them as being active? We've 537 VLF's in the log file. I don't know why all these would be active at one point using 15GB of space?

I've checked the replication through DBCC OPENTRAN and it reports that there are no actively open transactions on the file.

Is there a method of maintaining this file I may of overlooked?

Is there anywhere I can go to determine exactly what the log file is doing?

Would replication interfere with truncating/shrinking the log file?

At present, it grows around 1.67GB per day. I've ~18 days before having to perform some emergency work on shrinking the file. I'd like it to stay below 25GB if possible so there is plenty of room to grow if this has a sudden need for space.


texts: VarChar to NVarChar causing truncate error

texts:
I am importing some data from a temporary table which has a column defined as VARCHAR(150).  The destination tables column is defined as NVARCHAR(200).  I'm using an INSERT INTO statement, but when I run the SQL statement commenting out this column the query runs correctly.  When I uncomment this line, I get:

  Msg 8152, Level 16, State 4, Line 26

  String or binary data would be truncated.

  The statement has been terminated.

Any ideas as to why this is happening?

INSERT INTO table (col1, col2)
SELECT column1
      ,column2
  FROM tmpTable

col1 is defined as NVARCHAR(200) and col2 is a DateTime.
column1 is defined as VARCHAR(150) and column2 is a DateTime.

When I comment out column1 and col1 the import statement runs fine.


texts:
I am seeing calls like those shown below in my SQL Server Audit logs but have no idea what it means. A google search turned up a possible relationship with compiles/recompiles. The line (or similar lines) can also be found inside several SQL Server internal sprocs.

  EXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)

Does anyone have an understanding of this process?


texts: "Restore With Replace" or drop/restore with Instant Initialization on?

texts:
What exactly does the restore argument "With Replace" do? I'm looking at needing to restore a database back to a beginning point on a regular basis, and I've been trying to figure out if there are any disadvantages to using Restore With Replace versus Dropping/Deleting the database entirely and restoring it.

Will "With Replace" wipe the log files and reset whatever bits might be left in system databases as well? It seems that it would be much quicker, as I don't have to wait for the database to finish dropping (the database in question is around 2TB). I've already checked the TechNet article on the Restore arguments, it doesn't go into this specific question.


texts: Check mysql domain that is using resources

texts: I have a webserver hosting about 300 websites and sometimes my CPU usage gets close to 100% and when I use "top" in my CENTOS to check who is using all CPU, I discover it's mysql. I would like then to know what website is causing that high mysql CPU usage. With Apache I can know exactly which virtual domain is consuming my cpu, ram, bandwidth... but I have no idea how to do that with mysql. Any idea?


texts: Running pt-table-checksum with replicate-do-db option on

texts:
I've read the documentation on percona page, but still a bit confused, especially about the --replicate option.

My setting is that the slave is only replicating certain databases from master, say db1 and db2. So I have this following line in the my.cnf file on slave host:

replicate-do-db=db1
replicate-do-db=db2

So when I use the tool, I know I need to pass --databases=db1, db2 as an option, but do I need to write the resulting checksum table into one of these dbs with --replicate=db1.checksum? I know by default it creates a database called percona and writes to percona.checksum, but since I'm not replicating all the databases, will it also create and write such a db on slave?

And after using the tool, should I be running USE db1 before dropping the checksum table on master so that this is logged and sent to slave?

I know this can be easily tested but when I read the documentation mentioning of causing replication failure scared me. I somehow screwed my local MySQL and only having access to this two servers. Appreciate your help.


texts:
I am not very good with DB so please bear with me.

I am trying to put a very long JSON data to a table, this table was created by Django framework.

I am using Postgres on Heroku. So, when I try to put the data I get the following error:

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.OperationalError: index row size 3496 exceeds maximum 2712 for index "editor_contentmodel_content_2192f49c_uniq"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

My DB and table looks something like this:

gollahalli-me-django-test::DATABASE=> \dt
                      List of relations
 Schema |            Name            | Type  |     Owner
--------+----------------------------+-------+----------------
 public | auth_group                 | table | ffnyjettujyfck
 public | auth_group_permissions     | table | ffnyjettujyfck
 public | auth_permission            | table | ffnyjettujyfck
 public | auth_user                  | table | ffnyjettujyfck
 public | auth_user_groups           | table | ffnyjettujyfck
 public | auth_user_user_permissions | table | ffnyjettujyfck
 public | django_admin_log           | table | ffnyjettujyfck
 public | django_content_type        | table | ffnyjettujyfck
 public | django_migrations          | table | ffnyjettujyfck
 public | django_session             | table | ffnyjettujyfck
 public | editor_contentmodel        | table | ffnyjettujyfck
(11 rows)

gollahalli-me-django-test::DATABASE=> \d+ editor_contentmodel
                            Table "public.editor_contentmodel"
  Column   |           Type           | Modifiers | Storage  | Stats target | Description
-----------+--------------------------+-----------+----------+--------------+-------------
 ref_id    | character varying(120)   | not null  | extended |              |
 content   | text                     | not null  | extended |              |
 timestamp | timestamp with time zone | not null  | plain    |              |
Indexes:
    "editor_contentmodel_pkey" PRIMARY KEY, btree (ref_id)
    "editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id)
    "editor_contentmodel_ref_id_8f74b4f3_like" btree (ref_id varchar_pattern_ops)

It looks like I have to change "editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id) to take md5(content)

Can anyone help me with this? I have no clue on how to do it.

Update:

JSON content - https://gist.github.com/akshaybabloo/0b3dc1fb4d964b10d09ccd6884fe3a40

Update 2:

I have created the following UNIQUE index, what should I remove in this?

gollahalli_me_django=> create unique index on editor_contentmodel (ref_id, md5(content::text));
CREATE INDEX
gollahalli_me_django=> \d editor_contentmodel;
        Table "public.editor_contentmodel"
  Column   |           Type           | Modifiers
-----------+--------------------------+-----------
 ref_id    | character varying(120)   | not null
 content   | jsonb                    | not null
 timestamp | timestamp with time zone | not null
Indexes:
    "editor_contentmodel_pkey" PRIMARY KEY, btree (ref_id)
    "editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id) <---- 1
    "editor_contentmodel_ref_id_md5_idx" UNIQUE, btree (ref_id, md5(content::text))
    "editor_contentmodel_ref_id_8f74b4f3_like" btree (ref_id varchar_pattern_ops) <----2

Should I remove 1 or 2 (See the arrows)?


texts: Why SQL Server 2008 R2 do full installation for just adding another instance on the same machine

texts:
Why SQL Server 2008 R2 does a full installation for just adding another instance (only database engine) on the same machine? I thought it will be quick a installation, but it's not. What is SQL Server doing, copying/registering all the files again for this new instance?

Please clarify me.


texts: How to create a graph connecting predecessors and successors in time from a timeseries table in PostgreSQL 10?

texts:
I have the following table:

╔════╦═══════════╦═════════════════════╦═══════╗
║ id ║ sensor_id ║        time         ║ value ║
╠════╬═══════════╬═════════════════════╬═══════╣
║  1 ║         1 ║ 2018-01-01 00:00:01 ║     1 ║
║  2 ║         1 ║ 2018-01-01 00:00:02 ║     2 ║
║  3 ║         1 ║ 2018-01-01 00:00:03 ║     3 ║
║  4 ║         1 ║ 2018-01-01 00:00:03 ║     4 ║
║  5 ║         1 ║ 2018-01-01 00:00:04 ║     3 ║
║  6 ║         2 ║ 2018-01-01 00:00:01 ║     1 ║
║  7 ║         2 ║ 2018-01-01 00:00:01 ║     2 ║
║  8 ║         2 ║ 2018-01-01 00:00:02 ║     3 ║
║  9 ║         2 ║ 2018-01-01 00:00:03 ║     4 ║
║ 10 ║         2 ║ 2018-01-01 00:00:04 ║     5 ║
╚════╩═══════════╩═════════════════════╩═══════╝

CREATE TABLE sensor_time_series
(
    id SERIAL PRIMARY KEY,
    "time" TIMESTAMP NOT NULL,
    sensor_id INTEGER NOT NULL,
    value NUMERIC NOT NULL,
);

It's a timeseries table that represents the value of a sensor at a specific time. Yes, I know it's strange that "time" is not unique inside each "sensor_id", that's an error from the dataset.

What I want is to make a new table/view with a graph structure, connecting each "sensor_id" sample to its successor in "time". The table should look something like this:

╔════════════╦══════════════╗
║ current_id ║ successor_id ║
╠════════════╬══════════════╣
║          1 ║           2  ║
║          2 ║           3  ║
║          2 ║           4  ║
║          3 ║           5  ║
║          4 ║           5  ║
║          6 ║           8  ║
║          7 ║           8  ║
║          8 ║           9  ║
║          9 ║          10  ║
╚════════════╩══════════════╝

CREATE TABLE sensor_time_series_graph
(
    current_id INTEGER,
    successor_id INTEGER,
    FOREIGN KEY (current_id) REFERENCES sensor_time_series(id),
    FOREIGN KEY (successor_id) REFERENCES sensor_time_series(id)
);

Both columns (current_id and successor_id) FOREIGN KEY's id from the first table
How can I create something like this in PostgreSQL 10?

I was looking into PostgreSQL window functions and I think they can help me, but did not realized how yet.


texts: Retrieving What variables mean when retrieving view definitions?

texts:
I'm working on a small script to transfer views over from one database to another. I can get the view definition by using select view_definition from information_schema.views where table_name = 'actor_info', however the result is

SELECT a.actor_id,
       a.first_name,
       a.last_name,
       group_concat(DISTINCT (((c.name)::text || ': '::text)
       || ( SELECT group_concat((f.title)::text) AS group_concat
              FROM ((film f
                     JOIN film_category fc_1 ON ((f.film_id = fc_1.film_id)))
                    JOIN film_actor fa_1 ON ((f.film_id = fa_1.film_id)))
             WHERE ((fc_1.category_id = c.category_id) AND (fa_1.actor_id = a.actor_id))
             GROUP BY fa_1.actor_id))) AS film_info
  FROM (((actor a
          LEFT JOIN film_actor fa ON ((a.actor_id = fa.actor_id)))
         LEFT JOIN film_category fc ON ((fa.film_id = fc.film_id)))
        LEFT JOIN category c ON ((fc.category_id = c.category_id)))
 GROUP BY a.actor_id, a.first_name, a.last_name;

I'm looking for a way to figure which tables a and c refer to. Is there a SQL statement that can be used?


texts:
The previous Senior DBA set up mount points for all of our drives across every SQL Server throughout the company. The new Senior DBA is horrified by mount points wants to change our standard (mainly, I think, because he has no experience with them).

Based on the results of numerous internet searches, I can't find any (post-SQL Server 2000) reason to not use mount points.

Is anyone aware of Windows OS limitations regarding this topic?

I've been hearing the claim "the OS doesn't recognize mount points" a
lot lately. (Untrue, based on my research into the versions of Windows Server we use).

Is there any evidence- or experience-based reason NOT to use mount points with SQL Server?

Assume that running out of drive letters is not an issue for us.

It's my understanding that mount points are incredibly useful for segregating workloads.

Can anyone confirm or refute my understanding that mount points actually segregate/isolate workloads of the different types of data and log files (system database files, user database files, tempDB) more efficiently than one drive each for data files, log files, and tempdb?


texts:
Table A has columns (P1, P2, Value1) where P1 and P2 are a multi column primary key.
Table B has columns (P3, P1, Q1) where P3 is the primary key.
Table C has columns (P2, Q1, Value2) where P2 and Q1 are a multi column primary key.

I need to delete all rows from table A which don't have corresponding data in table B and C meaning to delete (p1,p2,v1) from A if there is no (p3,p1,q1) in B and (p2, q1, v2) in B for any q1 and v2.

Currently the query is:

DELETE FROM A where concat(P1, P2) NOT IN
(
SELECT Distinct On (A.P1, A.P2)
        concat(A.P1, A.P2)
        from C
        INNER JOIN B
        ON C.Q1 = B.Q1
        INNER JOIN A
        ON A.P1 = B.P1
        WHERE A.P2 = C.P2
)

but this query never finishes on the tables where table A and C have millions of rows.
How can I optimize the query?

Edit:  I am trying to achieve what I wrote with the purpose of cleaning up table A periodically when its data becomes irrelevant which is determined by whether the data appears in tables B and C or not.

Edit2: Improved version:

DELETE FROM A where NOT EXISTS
    (
    SELECT 1
            from C
            INNER JOIN B
            ON C.Q1 = B.Q1
            AND A.P1 = B.P1
            AND A.P2 = C.P2
    )

Three questions:

Is using AND inside ON like that faster than using WHERE?
Can "with" be utilized to improve this further?
Any other suggestions of optimization?


texts: Filling two tables with a Foreign Key Relationship

texts:
I need to create a star schema of table and apply on SSMS. To be simplier I focus on two tables:

One containing general informations about clients. Each client has a particular ID but can appear several times because he can be of multiple type.
One containing financial informations about them that are gathered every 3 months, each set of info about one client at a specific time will have an unique ID.

I set the client table as the parent table. I create it that way:

CREATE TABLE Customers
(
CostumerID INT IDENTITY (1,1) PRIMARY KEY,
Info 1 nvarchar,
    Info 2 nvarchar,
    Info 3 nvarchar
)

I create the financial informations table this way:

CREATE TABLE FinInfo    (
 FinInfoID INT IDENTITY (1,1) PRIMARY KEY,
 CustomerID INT,
 Date date,
 Info1 INT,
 Info2 INT,
 CONSTRAINT FK_Customers FOREIGN KEY(CustomerID)
 REFERENCES Customers(CustomerID)
 ON DELETE CASCADE
 ON UPDATE CASCADE     )

The thing is I don't know how to fill the costumerID in the FinInfo table without knowing it in the first hand.


texts: Automatically restore SQL Server database from file from another server

texts:
We have a daily task to overwrite a number of development databases using backups of the associated production databases. The backups are produced by maintenance plans on the production server then transferred to the dev server by FTP. Each day we run a SQL statement similar to this to overwrite each database:

RESTORE DATABASE [Database1]
FROM DISK = N'D:\path\to\Database1_backup_2015_02_05_190004_7401803.bak'
WITH FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Each time we run this we have to replace the file name with the correct most recent file. I would like to automate this somehow to minimise the chance of operator error. The problem is that we can't control the name of the .bak file (although the format is consistent - database name, date, time and whatever that seven digit number is), and the folder will usually contain several days worth of backups.


texts: SUSPECT Status Of Databases, but Ola Hallengren backup job completed SUCCESSFULLY

texts:
We are using the SQL Server Backup solution from Ola Hallengren in our environment for SQL Servers. Today, there were 2 database which were in SUSPECT mode and a backup was not possible for them. A job is scheduled to take backup of these databases (two of which were in Suspect mode) and one another database. The backup of the online DB got completed since it was first in the Alphabetical order and for the other two, it failed. However, the job did not fail and showed the job as successful! The job should have failed if it fails to take the backup of any database.

https://i.stack.imgur.com/n8Ww1.png

Message from the Log:

Date and time: 2018-03-18 03:12:55
Database: [ABC]
Status: SUSPECT
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Differential base LSN: 5334862000009909000054
Last log backup LSN: N/A

Date and time: 2018-03-18 03:12:55
Database: [CDE]
Status: SUSPECT
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Differential base LSN: 4543119000022597000098
Last log backup LSN: N/A

Date and time: 2018-03-18 03:12:55

One more scenario, where the DB backup fails but it does not report it as [FAILED] in the Log unlike to when it logs when the DB backup is successful - Outcome: Succeeded.

How can we get the information of failed backups from the logs? There is no Tag or Keyword which can be searched for when looking for failed backups in the Log.  There are only incomplete command statements which can tell that the backup has failed and for that you have to search the whole log and any kind of reporting is not possible where we can use the concept of keyword searching in log content for failures.

It would be great if you can please let us know what is the solution to these.


texts: How to have SQL Server's SQL Agent pick up a new Powershell module

texts:
I have a Powershell module that I've written and uploaded to my SQL Server at C:\Program Files\WindowsPowerShell\Modules.I have a SQL Agent job with a powershell step that calls the module.  This all works and has been working.

However, I recently made a change to the way the Powershell module works.  I updated the code and uploaded it to the server, but SQL Server seems to be executing the outdated version of the code still, even though the file has been replaced.

Do I need to tell SQL Server or Powershell to "refresh" the module somehow?  How can I do that?


texts: Is it possible to install a service pack to a SQL 2012 Express instance?

texts:
I have a SQL 2012 Express instance that is on Service Pack 3. I am trying to find a way to update it to Service Pack 4 but this doesn't seem to be possible or maybe I am just not finding the right answer? Anyone know if this is possible and if so, can you provide a little guidance?

Thanks!


texts: Is it better to store one tsvector column for all fields or one tsvector column per field?

texts:
Consider a simple movies table like this:

CREATE TABLE movies(
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title character varying(255),
    categories character varying(255),
    summary text,
    description text
);

Suppose I am interested in doing full-text search on all the fields like this:

SELECT *
FROM movies
WHERE to_tsvector(title) @@ to_tsquery('Lumberjack')
OR to_tsvector(categories) @@ to_tsquery('Lumberjack')
OR to_tsvector(summary) @@ to_tsquery('Lumberjack')
OR to_tsvector(description) @@ to_tsquery('Lumberjack')

Clearly I should not be using to_tsvector at runtime like that and should cache the tsvector representation of the fields instead. But I see two ways of doing that:

Store one tsvector-representation column for all of the fields
Store one tsvector-representation column per each field

Which is more performant and why?


texts: How to determine the read / write ratio of your server?

texts:
When looking at the read / write ratio of your server would you look at the NUMBER of reads and writes or the number of BYTES read and written? For example, consider the following numbers from sys.dm_io_virtual_file_stats:

93384 reads, 1080818 writes, = 8% reads, 92% writes

30630654464 bytes read, 60955863552 bytes written, = 33% bytes read, 67% bytes written


texts: Which is better: one database per application, or just one database?

texts:
I ask this specifically for SQL Server, the advantages that I can see for multiple databases are:

Logs are smaller (so one runaway transaction cannot cause others (replication etc) to stall)
A page corruption does not affect multiple applications (can this be isolated using filegroups?)

I can't seem to find any benefits from a single database architecture (maybe the code management is simpler).

Which architecture did you choose and why?


texts: How to find out if a row with a specific primary key exists in a table in SQL

texts:
I have created a table in SQL which is named testTable like this where I have defined the:
PRIMARY KEY (FeatureNbr, DateTime)

CREATE TABLE testTable
(
    [DateTime]    SMALLDATETIME  NOT NULL,
    [FeatureNbr]  SMALLINT       NOT NULL,
    [Value]       FLOAT (53)         NULL,
    [Bool]        BIT                NULL,
    CONSTRAINT UC_testTable UNIQUE (DateTime),
    CONSTRAINT PK_testTable PRIMARY KEY (FeatureNbr, DateTime)
);

I have now inserted 4 rows to the table like this:

INSERT INTO testTable(DateTime, FeatureNbr, Value, Bool) VALUES('10/22/2010 22:47', 0, 0.7556864869907925, 0);
INSERT INTO testTable(DateTime, FeatureNbr, Value, Bool) VALUES('10/22/2010 22:40', 5, 0.7556864869907925, 0);
INSERT INTO testTable(DateTime, FeatureNbr, Value, Bool) VALUES('10/22/2010 21:48', 0, 0.7556864869907925, 0);
INSERT INTO testTable(DateTime, FeatureNbr, Value, Bool) VALUES('10/22/2010 18:48', 20, 0.7556864869907925, 0);

Now assume that this table has 100 million rows or more. What is the fastest way to return if the above rows EXIST by looking at the primary key FeatureNbr, DateTime
We are then looking for exactly the rows which has those primary keys:

0, '10/22/2010 22:47'
5, '10/22/2010 22:40'
0, '10/22/2010 21:48'
20, '10/22/2010 18:48'

I will for example look for 5000 rows like this. Do I need to in some kind of loop look for each row one by one(perheps an exact search is extremely fast in this case? or how do I return a result for all lines or for each search one by one?).
I will know the FeatureNbr which always are all integers between 0-5000. So we have only 1 of each here which makes it 5000 rows. However, - DateTime will be different for each `FeatureNbr.
I have found this line but I don't know how to apply this , -or if this is the correct thing to use?
If IndexProperty(Object_Id('testTable'), 'MyIndex', 'IndexID') Is Null


texts: Create smaller row from alias and preserve column names

texts:
Using Postgres:

SELECT users."name" AS "name"
    , array_to_json(array_agg(sites)) as sites
FROM remodel.users AS users
JOIN remodel.user_sites AS user_sites
    ON users.id=user_sites.user
JOIN remodel.sites AS sites
    ON sites.id=user_sites.site
GROUP BY "users".id
;

currently produces

"Toby";"[{"id":1,"name":"Village","created":"2015-08-10T15:22:36.622298"},
         {"id":2,"name":"Manor","created":"2015-08-10T15:22:43.614551"}]"
"Amy";"[{"id":3,"name":"Park","created":"2015-08-10T15:22:48.810872"}]"
"Anne";"[{"id":2,"name":"Manor","created":"2015-08-10T15:22:43.614551"},
         {"id":1,"name":"Village","created":"2015-08-10T15:22:36.622298"},
         {"id":3,"name":"Park","created":"2015-08-10T15:22:48.810872"}]"

however I would like not to have the "id" field in the JSON output.

changing the sites selection to

array_to_json(array_agg(row(sites."name", sites.created))) as sites

causes the fields to lose their names

"[{"f1":"Village","f2":"2015-08-10T15:22:36.622298"},
  {"f1":"Manor","f2":"2015-08-10T15:22:43.614551"}]"

and attempting (what would be a horrible) sub selection

, array_to_json(array_agg((SELECT "name", created FROM sites))) as sites

understandably throws an

  ERROR: relation "sites" does not exist

And removing the id with a select in the from clause prevents joining the tables.

I'm looking to preserve the column information as well as I can,  not just for conversion to JSON but for inclusion in much larger and complex queries,  so performing the array selection without using JSON is preferred.


texts: Where/how do I save extra data per column?

texts:
Next to the actual data I have in a table, I need to save some data about those fields. I'm hesitant to call it metadata, because while it is exactly that, metadata has a specific meaning within databases sometimes, and this is not exactly what I mean.

Example: I have a table, and for each field I want to know some sort of state. Lets say 'unchecked', 'ok', 'faulty' etc. I could just double the columns, add a new 'status-field' per column, but that feels wrong.
A different example would be to safe how the data currently came to be, for instance calculated, automatically retrieved of manually entered.

Some extra info: I might want to save all changed data later: a sort of history table. While this might be a completely different problem, I suppose that it might actually help in this case: if I for instance have a key-value storage for the history, I could maybe add the metadata there?

Is there any default solution to add this sort of data to a database? The final goal is to add these fields to an existing model, one that contains several tables/relations. I'm not even sure if a one-solution-per-table, or a solution-for-all is possible?

In the simple case of "ok/not ok" statusses, the logical query belonging to this would be "get me all records that are ok (meaning: with all rows begin ok). With all the statusses on a distinct location one could do a count on those. When adding an extra field-per-column, I can't seem to think of quicker query then just adding a whole bunch of WHERE x_status=ok AND y_status=ok AND... etc.  The difference in information-type is lost, so there is a hidden property that I feel might need it's own... place. The fact that it is data-about-data shouldn't be lost, but I'm not sure how exactly.


texts:
I am planning to buy SQL Server 2012 for development and I have gone crazy with Microsofts complicated licencing policies.I had been searching the internet for valid answers from last few days and finally decided to post a question for expert advice.

The Requirement:

I have a small team of developer who will work on different applications. The applications require SQL SERVER as the backend.Following are the things I want to do with SQL Server

The application will interact with the basic tables, SP, functions and views.
Developing basic SSIS packages and SSRS reports.
I do NOT intend to use the database for Mirroring, Replication, Snapshots or any other complicated stuff but just the basic development.

I approached the Microsoft vendors for quotes for SQL SERVER 2012. They told me that I can go with the Standard Edition with 5 CALS (I have no idea what is CAL and why do I need it). I searched the internet and found that there is a BI edition also which is much costlier than the standard edition.

The Question:
The question is that what version should I use considering my requirement?

Thanks


texts: SQL Server 2008 R2 - Capture or be notified when an sql handle is ran

texts:
I'd like to monitor a particular SQL handle and find out who (process id) is running the SQL.

Is there a way to retrieve either a list of proc ids who executed the SQL handle in last N minutes or be automatically notified when that query is ran? or is only option is to run sys.dm_exec_requests continuously?


texts: Change the same field from all the tables from a particular database

texts:
Suppose I have a database sitedb and in that I have 20 tables and a primary key(say uid) for one table is used in other tables as dependencies.
uid is of type int with length 10. Lets say that the number of entries in the table is full and I need to increase the capacity of the uid to the length 11.
Is it right to do so? What will happen to the related tables and how will it affect the relation?
What if I change the value of uid in all the tables to length 11. Is there a tool which will allow me to check and change the value of every related uid in all the tables in the database? Updates?


texts: Count records with same id but different column value

texts:
I have the following table:

+------+------+
|userID|Name  |
+------+------+
|1     |sport |
+------+------+
|2     |it    |
+------+------+
|3     |game  |
+------+------+
|1     |sport |
+------+------+
|1     |game  |
+------+------+
|3     |it    |
+------+------+
|3     |sport |
+------+------+
|2     |it    |
+------+------+

I want to count the total number of records per UserID and if all of those are unique/have the same Name.
Example:

userID 1: sport, sport, game
userID 2: it, it
userID 3: game, it, sport

I should get:

userId 1: 3, False
userID 2: 2, True
userID 3: 3, False


texts: psql: Could not connnect to the server: No such file or directory

texts:
I know that this is a question that has been posted repeatedly, but I have not been able to find an answer that suits me.

PostgreSQL 9.5 was running fine, someone decided we needed to edit the pg_hba.conf file and restart the service, so we did. From then on, this ominous error message comes up when trying to connect to the databases (we tried to revert the changes and restart the service, but this did not solve it). It looks like the process is running since

service --status-all

Shows that postgres is running, as in [ + ]   postgresql

But, if I run

service postgres status

The output looks like this (the black dot at the begining is actually green):

● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2016-11-04 11:49:18 UTC; 27min ago
Main PID: 1079 (code=exited, status=0/SUCCESS)
Tasks: 0
Memory: 0B
  CPU: 0
CGroup: /system.slice/postgresql.service

Nov 04 11:49:18 kissdb systemd[1]: Starting PostgreSQL RDBMS...
Nov 04 11:49:18 kissdb systemd[1]: Started PostgreSQL RDBMS.

However, when I try to look for the PID of the process in ps output... it is not to be found, so my best guess is that something is telling the service is running while not actually being running and I don't know how to get my hands around this.

Also, I don't seem to have access to the pg_ctl, postgres commands, and there is no /var/ directory in /usr/local/, which I guess that should be worrying since almost every given answer I have looked into mentioned it.

Please, somebody help me :(
I can give whatever more information you may need, I don't really know what is going anymore so I don't know for sure that I have given the necessary information for someone to know what the problem is.


texts: Having read Using Replication for Scale-Out, how can I route different queries to different servers, for example, SELECTI want to route to slaves and NON-SELECT to master. I assume as loadbalancer I can use haproxy, but I didn't find it's possible to distict between queries on the level of haproxy? In addition, let's say someone have reached master directly, how can master identify that this is SELECT query and show be sent to slave, or to loadbalancer.


texts: MySQL - get latest record when date and time are two fields

texts:
I have a DB in which date and time are two columns. I need to get the latest (by latest I mean the record with the highest time) row. This is used as a sub query to feed another query.

DB

id      date       time    currentOdometer    totalOdometer       vno
 1   2020-03-21    10:00      250                  3001          ABCD
 2   2020-03-21    09:59      249                  3000          ABCD
 3   2020-03-21    10:01      251                  3002          ABCD
 4   2020-03-21    10:02      252                  3003          ABCD

My requirement is that when I get a new record of vno ABCD, i need to find the latest record and manipulate the data.

For instance, when i get a new record at 10:03, i need to find the latest record, ie 10:02 and the value.

My query was

(
SELECT Y.currentOdometer+(3200-Y.totalOdometer)
FROM tbl Y
WHERE timeStamp(date,time) =
(
  SELECT MAX(timeStamp(X.date,X.time))
  FROM tbl X WHERE vno= 'ABCD') AND vno= 'ABCD'
)

This query is part of an INSERT statement like this

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno)
VALUES ('2020-03-21', '10:03',(SELECT Y.currentOdometer+(3200-Y.totalOdometer)
FROM tbl Y
WHERE timeStamp(date,time) =
(
  SELECT MAX(timeStamp(X.date,X.time)) from tbl X
  WHERE vno= 'ABCD') AND vno= 'ABCD'),3003,'ABCD'
)

When I run the query in this manner, i get the error the Subquery returns more than one row.

The i re framed the query with LIMIT 1 in each of the select statemtn and then there is a deadlock on the

ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
ER_LOCK_DEADLOCK
undefined


texts: Does concating a string like this open me up to SQL injection?

texts:
I recently answered a question with the following code sample:

Create Table #Testing
(
  emaildomain varchar(100) -- Still bigger than functionally needed but better than MAX
);
INSERT INTO #Testing VALUES (REVERSE('@myfreepaysite.com')); -- Changed

Create Table #DataToCheck
(
  fullemail varchar(200) -- Still bigger than functionally needed but better than MAX
);
Insert Into #DataToCheck VALUES(REVERSE('rr1234@myfreepaysite.com')); --Changed

Select Top 1
  REVERSE(fullemail)
  , REVERSE(emaildomain)
FROM #DataToCheck
  INNER JOIN #Testing ON fullemail LIKE emaildomain + '%';

As I was writing that last line I wondered if concating EmailDomain column with the wildcard symbol was an opening for SQL injection. Is this something I should watch out for this type of code or will SQL Server evaluate this as a single unified parameter so I don't need to worry about using this approach?