texts: sql server 2008 r2 DBCC checktable error

texts:
This morning my database went into suspect mode.  I reverted back to last night's full backup to get it back online.  I went through the error log and found the last command that appeared to cause the suspect mode was on a MERGE on a particular table.  I ran DBCC CHECKTABLE on the table and got back the following

Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1253579504, index ID 7, partition ID 72057594230341632, alloc unit ID 72057594364887040 (type In-row data). Keys out of order on page (1:14254237), slots 184 and 185.
There are 366539002 rows in 3425599 pages for object "PJM_Prices".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'PJM_Prices' (object ID 1253579504).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (CaisoAnalyzer.dbo.PJM_Prices).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

in ERRORLOG

Internal database snapshot has split point LSN = 001935ad:00001bf6:0001 and first LSN = 001935ad:00001bf5:0001

Is there possibly a way to put just that table in single user mode or some other trick where I don't have to go into single user mode for the whole database?


texts: How to check when a record is replicated in SQL Server

texts:
I have configured a database installed On-Premise to replicate certain tables on a Cloud SQL Server database hosted on Azure.

The replication task is scheduled to run at specific time everyday and I am looking for a way to check when a record was updated and what kind of operation was performed (Create, Update or Delete).

Is there any system table, stored procedure, or log that contains this information?


texts:
How do I explicitly define data types for a MongoDB collection?

I understand that in Mongo if a collection doesn't exist it will create it when you insert the document, but I know the fields and data types of the collection before hand and I want to create it.
I have created an example collection using:

db.createCollection("log", { capped : true, size : 5242880, max : 5000 } )

According to this document from the MongoDB documentation, there are various data types in MongoDB, but again,
How do I explicitly define data types for a MongoDB collection?


texts: SQL Server - Order by case statement very slow

texts:
I have the following query in a SP which executes fine:

SELECT * FROM MyTable u
ORDER BY
u.[Id] DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

However, when I make the ORDER BY configured from a SP parameter the query goes from executing in ms to several seconds:

SELECT * FROM MyTable u
ORDER BY
CASE WHEN @Sort = 0 THEN u.[Id] END DESC,
CASE WHEN @Sort = 1 THEN u.[Id] END ASC,
CASE WHEN @Sort = 2 THEN u.[LastName] END ASC,
CASE WHEN @Sort = 3 THEN u.[LastName] END DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

What is the reason now for the slowdown even though the same field is being used for the order? (ID DESC?)

Is there a way to make this perform better?

I have tried in SQL Server 2016 and SQL Azure.

EDIT:
Execution plan/live stats without case ordering:

Execution plan/live stats with case ordering:


texts: If a transaction is "committed" , is it then saved for sure?

texts:
If a transaction is committed successfully, can I then be 100% sure that it has been written to the database AND to the log files? Can I be sure that the data is SAVED ?

Today our log files had reached the limit of the drive and we got a lot of errors.  Furthermore, some other services crashed. We increased the disk and restarted the server.

During startup, the server did a "database recovery" -- can I be sure that everything is okay again ?


texts: mariadb slow when query works update or Insert

texts:
My boss said database is bad when query works in update and insert commands. What can I do for tuning?

normally queries respond in select commands, It respond little results but page updated about 4-5 seconds;

# Query_time: 0.000232  Lock_time: 0.000062  Rows_sent: 27  Rows_examined: 27
# Query_time: 0.000173  Lock_time: 0.000056  Rows_sent: 27  Rows_examined: 27
# Query_time: 0.000387  Lock_time: 0.000069  Rows_sent: 79  Rows_examined: 106
# Query_time: 0.002147  Lock_time: 0.000116  Rows_sent: 9  Rows_examined: 27
# Query_time: 0.000765  Lock_time: 0.000100  Rows_sent: 18  Rows_examined: 54
# Query_time: 0.014601  Lock_time: 0.000061  Rows_sent: 1131  Rows_examined: 2262
# Query_time: 0.000207  Lock_time: 0.000069  Rows_sent: 48  Rows_examined: 48
# Query_time: 0.000972  Lock_time: 0.000071  Rows_sent: 32  Rows_examined: 64
# Query_time: 0.000419  Lock_time: 0.000103  Rows_sent: 0  Rows_examined: 0
# Query_time: 0.000202  Lock_time: 0.000064  Rows_sent: 4  Rows_examined: 4
# Query_time: 0.000755  Lock_time: 0.000113  Rows_sent: 8  Rows_examined: 24

Insert and Update commands, Boss said when he update table, database are slow. it has nearlly 5 millions rows;

# User@Host: koha[koha] @  [x.160.x.x]
# Thread_id: 323047  Schema: kohadata  QC_hit: No
# Query_time: 194.853241  Lock_time: 0.000091  Rows_sent: 0  Rows_examined: 5900006
SET timestamp=1448804624;
UPDATE zebraqueue SET done = 1
                             WHERE server = 'test'
                             AND done = 0;
# User@Host: koha[koha] @  [x.160.x.x]
# Thread_id: 393768  Schema: kohadata  QC_hit: No
# Query_time: 117.138732  Lock_time: 0.000299  Rows_sent: 0  Rows_examined: 0
SET timestamp=1448946829;
INSERT INTO sessions (id, a_session) VALUES('123sadas12dsadas', '--- \n_SESSION_ATIME: \'12311111\'\n_SESSION_CTIME: \'1448946718\'\n_SESSION_ID: 1231sadsadas\n_SESSION_REMOTE_ADDR: x.160.x.x\nip: x.160.x.x\nlasttime: \'1448946718\'\nsessiontype: anon\n') ON DUPLICATE KEY UPDATE a_session = '--- \n_SESSION_ATIME: \'1448946718\'\n_SESSION_CTIME: \'1448946718\'\n_SESSION_ID: 123sadasaaasadasa\n_SESSION_REMOTE_ADDR: x.160.x.x\nip: x.160.x.x\nlasttime: \'1448946718\'\nsessiontype: anon\n';
# User@Host: koha[koha] @  [x.160.x.x]
# Thread_id: 393762  Schema: kohadata  QC_hit: No
# Query_time: 0.000681  Lock_time: 0.000086  Rows_sent: 3  Rows_examined: 9
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1448946829;
SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs WHERE vs.category=2 ORDER BY vs.lastmodified DESC LIMIT 10;

my.cnf parameters;

max_connections         = 1000
max_connect_errors      = 1000000
connect_timeout         = 20
wait_timeout            = 600
max_allowed_packet      = 1G
thread_cache_size       = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 32M
max_heap_table_size     = 32M
query_cache_limit               = 256K
query_cache_size                = 1M
innodb_log_file_size    = 2G
innodb_buffer_pool_size = 75G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size  = 256M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT
innodb_thread_concurrency = 8
innodb_read_io_threads = 32
innodb_write_io_threads = 16
innodb_purge_threads    = 4
innodb_buffer_pool_instances = 3
innodb_table_locks = 0
innodb_locks_unsafe_for_binlog = 1
log-queries-not-using-indexes = 1


texts:
I am trying to understand how SQL Server access data from the clustered index. My understanding is when the table has a clustered index SQL should be able to seek into the single page that holds the record using the seek predicate.
However, my test showing me when the query is executed it loads a few more data pages.
Setup
CREATE TABLE t2(id INT IDENTITY PRIMARY KEY CLUSTERED,col2 VARCHAR(500),col3 VARCHAR(500));

INSERT INTO [dbo].[t2]([col2],[col3])
SELECT TOP 10010 REPLICATE('z',490),REPLICATE('*',490)
FROM sys.all_columns c1,
     sys.all_columns c2

The following query
select *
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.t2'),DEFAULT,null,'DETAILED');

shows the output as

I then cleared the cache with
CHECKPOINT

GO

DBCC DROPCLEANBUFFERS

And ran the following SELECT seeking into a single row
SELECT  [fplc].*,[t2].[col3] FROM [dbo].[t2] AS [t2]
CROSS APPLY sys.[fn_PhysLocCracker](%%physloc%%) AS [fplc]
WHERE id=4582

The above query tells me the record is located on the page 1061
I use the code below check how many pages have been loaded into the buffer to get the result of my SELECT
SELECT buffers.* FROM sys.dm_os_buffer_descriptors  buffers
INNER JOIN sys.allocation_units AS au
    ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
INNER JOIN sys.partitions AS p
    ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
    ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100
and [database_id] = DB_ID () AND i.[object_id]=OBJECT_ID('t2')
 ORDER BY [page_level] desc

Why SQL is loading all the pages marked in green? Are the pages loaded by READ AHEAD ?


texts: Is there a setting in SQL Server to keep triggers from firing SELECT @@IDENTITY

texts:
This is a SQL Server 2014 Server and we have application code the does a Select @@Scope_Identity after an insert into a table.

Recently other developers added triggers to this table and ever since, we are having problems with the triggers firing a Select @@Identity.

I know that the default for SQL Server is @@Identity, but is there a way to either change the default system wide, or is there a way to keep the triggers from firing the Select @@Identity?

When the app connects, Identity="@@SCOPE_IDENTITY()" is part of the connection string and it works fine.


texts: Will MyISAM prevent multiple users inserting data at the same time?

texts:
I am using a MyISAM database to store user data because I am using full text searching. I have looked in to options like Sphinx but cannot install this on my shared hosting plan which uses MySQL 5.5.36.

I have been researching the problems of MyISAM and have come across the full table locking.

By using MyISAM, will this mean that, say if multiple users are on my website and two users are searching and two users are trying to INSERT into the same table that they will be unable to do so?

Is anyone able to explain to me how this works?

Thanks


texts: Stored Procedure to Rename Table and Column names

texts:
There is an example dataBase in which we have some tables.

The word "Account" is included in the names of 5 tables in that database.

For example we have:

Table1: FactAccount
Table2: FactAccountBalance
Table3: DimAccountTime

"Account" is also included in the name of some of the columns.

We need a stored procedure to replace the word "Account" with "Items" in both column and table names, so that at the end of the stored procedure we have:

Table1: FactItems
Table2: FactItemsBalance
Table3: DimItemsTime

I have tried a combination of sp_rename and the replace function but I was not successful.


texts: What Backup Compression Rate Can I Expect for a Database with Mostly JPG Data?

texts:
I am considering using a database to store one or more images (.JPG) associated with a record identifier and filename.

CREATE TABLE Images (
    RecordID BIGINT NOT NULL,
    ImageFilename VARCHAR(50) NOT NULL,
    ImageData VARBINARY(MAX) --expected avg. size: 256K
)

I anticipate that 90% of the space utilization on the database will be coming from the BLOBs. I know that SQL Server data compression will not compress the BLOB data through row or page compression. Can I expect any savings when compressing the backups, other than the compression taking place on the RecordID and ImageFilename fields?


texts: Comparing DISTINCT, GROUP BY and ROW_NUMBER() in SQL Server 2008 with data warehouse workloads

texts:
I recently heard the advice: "If you're using DISTINCT, I challenge you to fix your code."  However, I'm wondering what considerations are behind this advice, and whether it means that I should approach the following problem differently.

Problem context

I have a denormalised date dimension table (Kimball star schema).  In this table, I have collapsed several 3rd normal form tables: date, absolute week, absolute month and year.  For each of these collapsed tables, I have a column that represents a unique identity, a textual name column and a numeric manual sort ordering index.  Here's an example row to show the structure, the numbers are illustrative only (in vertical form for ease of reading):

DateId: 20110507
DateName: 7th May 2011
DateOrder: 17813
WeekId: 201118
WeekName: Week 18, 2011
WeekOrder: 617
MonthId: 201105
MonthName: May 2011
MonthOrder: 74
YearId: 2011
YearName: 2011
YearOrder: 41

I've chosen to use a date dimension table for ease of understanding, but the problem can be transposed to any other dimension table involving hierarchies.

Problem synopsis

I want to retrieve the distinct set of week and month combinations with the related textual fields.

Possible solutions

1. DISTINCT

SELECT DISTINCT MonthId, WeekId, YearName, MonthName, WeekName
  FROM DimDate

Note that I cannot exclude MonthId or WeekId because I cannot assume that free text fields are unique.

2. GROUP BY

SELECT MIN(YearName), MIN(MonthName), MIN(WeekName)
  FROM DimDate
 GROUP BY MonthId, WeekId

3. ROWNUMBER

WITH grp AS (
  SELECT YearName, MonthName, WeekName
       , ROW_NUMBER() OVER (PARTITION BY MonthId, WeekId) AS r
    FROM DimDate
)
SELECT YearName, MonthName, WeekName
  FROM grp
 WHERE grp.r = 1

4. ETL

In this approach, indexed views of every combination of non-leaf attributes are precalculated and queried directly.  Obviously this is fastest at query time, but could require many duplications of the dimension table, depending on the complexity of the hierarchies in the dimension.

Comments

I would expect that the ROW_NUMBER solution is the fastest (excluding the ETL approach), but it also requires a subquery or CTE in the query specification.  Although this is not a serious problem, it does make the query harder to read.  I would expect that the GROUP BY and DISTINCT methods to be almost equivalent, both suffering from the fact that every column has to be used in some form of sort operation, whereas the ROW_NUMBER solution only requires the partitioning columns to be sorted.

Am I missing something?  Can SQL Server make more effective use of non-unique indexing of denormalised column identities?

--edit - additional notes:

The week and month identifiers are absolute, so they imply the year in this scheme. I enforce that weeks are only in a single year. In SSAS, would be two hierarchies: date, week, year and date, month, year.
I believe DISTINCT gives the same output because the columns requested only vary by week, month or year and year is implied by both week and month.
Indexing is currently on the identifier columns only (single column non-unique)

In general, I'm performing an aggregate query on a fact table but reporting by a set of non-leaf dimension attributes which are chosen by an end-user and not known in advance. I was trying to reduce the problem space for this question.


texts: Which query performs better in MongoDB ($or or $facet)?

texts:
I have a query similar to the following;
    db.getCollection('list').aggregate([
    {
        $facet: {
            "events":[{
                $match: {
                   'type': 'Event'
                }
            }],
            "tasks": [{
                $match: {
                   'type': 'Task'
                }
            }]
        }
    },
    {$project: {activity:{$setUnion:['$events','$tasks']}}},
    {$unwind: '$activity'},
    {$replaceRoot: { newRoot: "$activity" }}
]);

One another easier approach to write this query is by using $or operator.
    db.getCollection('list').aggregate([{
    $match: {
        $or: [{
            type: 'Event'
        }, {
            type: 'Task'
        }]
    }
}]);

Source: https://gist.github.com/cthurston/7aead8229e10caa0be175babf7e8ddf1
The query that I'm working with is very similar, except I have other fields, not just 'type' going through this example. My idea is that, if I use $facet I'd get better performance if I create correct indexes for each query (that is inside $facet). On the other hand, if I use $or query as it is in the example, Mongo may struggle while trying to find the correct index for the query. Is this how it works?
Edit: It seems MongoDB can handle indexes for $or statements;
https://docs.mongodb.com/manual/reference/operator/query/or/#or-clauses-and-indexes


texts:
I'm in a bit of a pickle, I have to perform a query on a database with rather poor data quality. I am trying to evaluate a field which contains a date, but which was defined as a nvarchar. I thought I would be able to cast the value to an integer but this fails as there also non-date values in the date field (random letters and signs,etc...).

What I decided to do is as follows:

SELECT q.foo, cast( q.bar as int)
FROM ( SELECT foo,bar FROM table where ISNUMERIC(bar) = 1) as q
WHERE q.bar > 20140401

But this still crashes stating conversion failed on the nvarchar value '.       '. I expected that all values would  be castable to int as they were numeric?


texts: Get actual Job Name of the running SSIS package from Program Name column of the results while running sp_whoisactive or sp_who2

texts:
When I run sp_whoisactive or sp_who2 and if there is a SSIS Package running it would always display in the Program Name as the name of the running SSIS Package like SSIS-Package-{063BC46B-1F74-4207-A263-4F78A705RAE4}ServerName.dbname.dw.
Instead of it I want the name of the SQL Agent Job which is running.
Since I offload the data for monitoring I want the SQL Job name to be displayed instead of the Package Name.
Can any one help me with it.


texts:
I have a tablespace has size 307,200 MB and "Used (MB)" is 103 229,81. This tablespace was created with "BIG" Datafile. For recent time "Used (MB)" value increase insignificantly. So I want to shrink the tablespace.

alter tablespace MYTABLESPACE shrink space keep 130G

raises

  ORA-12916: cannot shrink permanent or dictionary managed tablespace

Yes. This tablespace is "permanent", but its size is bigger in three times than the used size is, and this fact is a problem for me. How can I shrink it?


texts:
How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu.

What happens if,

I issue or schedule mysqldump on master servers.
I allow developers to make changes directly connecting to master servers with the GUI tools(workbench).

Any help is appreciated. Thank you!


texts: how to backup remote mysql server to local direcotry using innobackupex

texts:
I am using innobackupex to backup remote mysql 5.7 like this:
innobackupex \
--host="mysql.google.com" \
--port=3306 \
--user="root" \
--password="passwod" \
--stream=xbstream "/Users/dolphin/data/xtradb"

but the result is:
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
200906 14:12:05 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

200906 14:12:06  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=mysql.google.com;port=3306' as 'root'  (using password: YES).
200906 14:12:06  version_check Connected to MySQL server
200906 14:12:06  version_check Executing a version check against the server...
200906 14:12:12  version_check Done.
200906 14:12:12 Connecting to MySQL server host: mysql.google.com, user: root, password: set, port: 3306, socket: not set
Using server version 5.7.27-log
/usr/local/Cellar/percona-xtrabackup/2.4.14_3/libexec/bin/innobackupex version 2.4.14 based on MySQL server 5.7.19 osx10.15 (x86_64) (revision id: ef675d4)
innobackupex: Can't change dir to '/var/lib/mysql/' (Errcode: 2 - No such file or directory)
xtrabackup: cannot my_setwd /var/lib/mysql/

I have read the docs and shows run backup command in remote server and using mysql server's config. is it possible to backup remote server to local directory? what should I do to fix this?


texts: issues updating records in ssis using lookup and oledb command

texts:
im working on an ssis project loading data between two sql azure databases that is updating matching records and inserting new records between them using a lookup and ole db command but I'm getting an error on the ole db command as the following, any ideas what it may be? trying to update over 20,000 records
[OLE DB Command [28]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: The semaphore timeout period has expired.


texts: Microsoft Access: Using other fields in an Autonumber

texts: I'm trying to use Microsoft Access to start an employee database. However, I want the display of the Employee ID to be the initials and then their number. For example, John Smith with the ID 0002 is JS0002. I've been playing around with format but it doesn't seem I can do this if I put my EmployeeID as an autonumber. Thanks in advance!


texts: Is it possible to drop tablespace CASCADE in MySQL?

texts:
I am newbie with MySQL. I have created a tablespace with a datafile, and I have tried to drop this tablespace without drop any object in that tablespace by using that SQL query:

DROP TABESPACE TBS_TEST;

  ERROR 1529 (HY000): Failed to drop TABLESPACE

DROP TABESPACE TBS_TEST CASCADE ; ---also got ERROR

So, is it possible to drop tablespace CASCADE in MySQL?


texts: How to change user privileges from 'Y' to 'N' in MySQL Server?

texts:
I want to change a privilege to my glpiuser from 'N' to 'Y'.
In MySQL server, what is the command to do this task?
Here's the image that display my users.
I'm using Ubuntu 20.04.1


texts: How to get OUTPUT $action, Inserted.*,Deleted.* records in In SSIS

texts: I was just wondering, in MERGE in T-SQL, you can output the OUTPUT $action, Inserted.* and Deleted.* records. I want to ask how can I get OUTPUT $action, Inserted.*, Deleted.* records in SSIS?


texts:
I have this table id, teama, teamb, pointa, pointb.

Columns teama and teamb containing the same names. I want to output the sum grouped by teama from teama where pointa and where teamb pointb.

for example:

id, teama, teamb, pointa, pointb
1,  aa,    bb,    1,      2
2,  bb,    aa,    2,      1

What  want as result:

aa, 2
bb, 4

I'm really stuck on this one. I'm using MySQL and PHP.


texts: Is there a way to pass to execute immediate only once a variable but use it several times?

texts:
I have a Cursor that gets its data from a script along these lines:

OPEN myCursor FOR
SELECT value1 AS Value1,
  myPackage.function1 (value1, :myId) AS Result1,
  myPackage.function2 (value1, :myId) AS Result2,
  myPackage.function3 (value1, :myId) AS Result3
FROM mySchema.myTable
WHERE tpk = :myPk
USING myId, myId, myID

As you can see I use the myId variable 3 times and I have to pass it 3 times in the using statement for it to work.
I couldn't find it anywhere, but is there a way I could pass the myId variable only once?


texts: Ola Hallengren index maintenance - long periods of time between commands?

texts:
I run the Ola Hallengren scripts on all of my servers for index and statistics maintenance. When I look through the command log table, I notice long periods of time between a command ending, and the next command starting. Sometimes this gap is over an hour.

Does anyone else observe this on their systems? Is there something I can do to shorten the (I'm guessing) discovery time between items to maintain? Below is the parameter set I'm running them with.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y' " -b

So when I run this:

SELECT DATEDIFF(MINUTE, cl.StartTime, cl.EndTime)
, *
FROM master.dbo.CommandLog AS cl
WHERE cl.StartTime > '2014-12-13'
ORDER BY cl.ID

I see this:


texts: Can't run the newest version of MySQL on phpMyAdmin

texts:
I am building a database on phpMyAdmin. However, it doesn't include the latest version of MySQL:

I downloaded the latest version of MySQL from this link: MySQL Community Server 8.0.16

However, even after I installed it I cannot find a way to change it on phpMyAdmin since when I open wamp, go to MySQL --> Version the only version that appears is 5.7.26. So how can I update it to the latest one? I am running it on Windows 10.


texts:
Whenever I enter SQL Server Configuration Manager and choose SQL Server Services I would always get the following error message: "The remote Procedure call failed. [0x800706be]"
everything else works fine but not able to configure SQL Services anymore. has anyone came across this and managed to fix the problem?


texts: Dealing with large transaction logs in a cluster

texts:
Disclaimer: I'm a dev, not a DBA. I've gleaned what I can from MSDN, but could do with some expert guidance!

I'm working with a SharePoint 2013 installation on a SQL Server 2012 cluster of two DB servers. It's only been up for a week or so and my transaction logs have already filled the 25GB allocated for them on each box. This is only a test environment, so I don't really need to keep the transaction logs (normally I just set the logging to "Simple" and let nature take its course) but obviously the logs are needed to ensure everything is replicated properly. What I'd like is for the logs to be dumped as soon as they are no longer needed, but I'm not sure how to accomplish this (or if it's even possible).

Based on my current (limited) understanding, I've set up a Maintenance Plan using the wizard that runs every day to backup the transaction log (so SQL Server will allow it to be deleted). I've set the backups to expire after a couple of days so that hopefully they don't grow too large. Do I then need steps to shrink the databases and cleanup after the plan to get rid of the logs?

I'm not expecting a complete solution (but that would be nice!) but any guidance as to whether I'm on the right lines would be greatly appreciated.


texts: SQL Server Row Count sp_spaceused rows discrepancy

texts:
I am researching a performance problem with an application running on SQL Server 2005.  I've conducted performance tuning, and I realize that a key table is poorly structured(poorly chosen clustered index).  Unfortunatly, I've been asked to hold off on structural changes until some scheduled down time so I must improve performanc without fixing the root structural problem.

Thus, my question is related to the rowcount returned in sp_spaceused.  I understand that this value may not be current, but it should be close.  The discrepancy is 6 rows returned vs. an actual of over 18k rows.   I suspect that this discrepancy is causing some interesting execution plans (index scans instead of index seeks).  All other indexes on the table appear to have a correct row count.

Can anyone help explain this? Is this taken into consideration when selecting an execution plan?  If so, please provide some direction to updating this value?  I've tried rebuilding indexes and updating statistics.

EXEC sp_spaceused @objname=N'dbo.tblEMPLOYEES_MASTER';

Returns rows:  6

SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary,
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber;

All other indexes(non-clustered) on the table return a 18,163 row count, however the clustered index (IndexID of 1) has a row count of 6.

Thanks everyone for your feedback!


texts:
I have a SQL Server database with an update sentence ran very often. I've seen on Activity Monitor that this update sentence trigger 27ms and I want to improve it.

I have checked execution plan and I have seen that the update sentence updates view's indices:

the update query is this:

UPDATE Code
    SET CommissioningFlag = @newCommFlagValue
                , Source = @source
                , UserName = @username
                , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
                , SentToNextLevel = 0
                , HelperCodeId = @helperCodeId
WHERE CodeId = @codeId

And the view highlighted script is:

CREATE VIEW dbo.PrintedCodesStatistics
WITH SCHEMABINDING
AS
    SELECT AggregationLevelId, COUNT_BIG(*) as CodesPrinted
    FROM dbo.Code
    WHERE   (CommissioningFlag != 255)
    GROUP BY AggregationLevelId

GO
CREATE UNIQUE CLUSTERED INDEX [IX_Printed_Statistics]
    ON [dbo].[PrintedCodesStatistics]([AggregationLevelId] ASC);

And finally, Code table script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)

Do the views slow-down the update query?


texts: Which database management system type to choose for price tracking?

texts:
I'm writing a price tracking application.

Every day I need to store price information about ~15M products. (So it's about 15M * 365  = 5.5B records per year).

One product has many prices over history. My main table (if I opt for a SQL DBMS) should include the following attributes:

product_id (8bytes int)
price (4bytes int) can be NULL
date (only date without time since daily update)
currency (3byte string)

E.g. (9223372036854, 85.41, 14-01-2019, "USD")

I don't want to mark the latest price as 'active' or something, so I'm interested only in price history itself. The database should have high IO throughput (~1000 simultaneous reads).

Queries I'm planning to run:

Get all price for the specific product_id for the specific time range
Basic math operations like min, max, avg

So, I am curious:

Should I stick to a SQL DBMS (Postgres) or use some NoSQL product (e.g. Amazon Redshift)
Which DBMS can you recommend?


texts: Cursoring over sys.databases skips databases

texts:
I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem:

I have the following code to perform database backups.

DECLARE @Filename   VARCHAR(256)
DECLARE @FileDate   VARCHAR(15)
DECLARE @Path       VARCHAR(50)
DECLARE @Name       VARCHAR(50)

-- specify database backup directory
SET @Path = '\MyPath'

-- specify filename date
SELECT @FileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108),':','')

DECLARE db_cursor CURSOR FOR
    SELECT [name]
        FROM master.sys.databases
        WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb')
            AND [state_desc] = 'ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @Filename = @Path + @Name + '_Full_Backup_' + @FileDate + '.bak'
    BACKUP DATABASE @Name
    TO DISK = @Filename
    WITH CHECKSUM,
        COMPRESSION

    FETCH NEXT FROM db_cursor INTO @Name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Sometimes, only some databases are backed up, suggesting that the cursor is not iterating over all of the rows returned or that the query itself is not returning the names of all the databases it should be.

I am trying to understand why this happens. I know that the fix is to use a STATIC cursor, suggesting the issue is with the results in the underlying query

SELECT [name]
    FROM master.sys.databases
    WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb')
        AND [state_desc] = 'ONLINE'

are changing, but I can't see what would be changing (no database names would change and the Error log doesn't suggest that the database state has changed)


texts: Can I run concurrent backups of multiple read only filegroups?

texts: I have 62 readonly filegroups in a SQL server 2008 enterprise database. Can I backup multiple read only filegroups at the same time? I would assume this to be the case but given this database is over 20TB in size I do not want to invalidate any files by giving it the ol' college try.


texts: CMS - Database Schema Review / Questions

texts:
Database Schema Review for CMS

I'm in the midst of creating a Laravel application that will (hopefully) rival WordPress. The thought process is quite similar. Developers are able to create resources (custom post types), modify users, & roles and add custom fields with flexible content (similar to that of Advanced Custom Fields).

As this is one of my first big builds I have a couple of questions regarding fields & categories:

Categories:
A developer is able to add a resource (such as news, or posts) and each resource can have a category which is labelled and inputed into the category table. A category can belong to more than one resource. What I can't wrap my head around is how to separate these two tables (categories and category content), what might feature in a different table and what I might even name it!

Fields An administrator is able to create field groups that can belong to resources or pages. A field group would contain an array a fields, which would contain data such as type (rich text, name, required, conditional logic etc..). How would this data look in a schema? Would would be best practice?

Any help would be greatly appreciated, I'm new to this world!
Please feel free to provide any constructive criticism regarding this design.

The ERD Diagram can be found here - CMS - SqlDBM Link


texts: A lot of data in the table is only accessed for 4 days or so, how to handle it afterwards?

texts:
We have a table that accumulates messages from local air traffic. It's approximately 5k-20k data rows a day that will only be actively accessed for a week at most. Afterwards it's just a log in case of some emergency (like inspection) and basically a very dead chunk of data. Like, archaeologically dead.

What is the optimal way to deal with it? Our "DB guy" argues that "it's all indexed" and there is no harm in having millions upon millions of accumulated unused records, but I am not really convinced. Is this really a good idea to allow the table to grow without limits?

UPD: We are using Oracle 11. The access patterns are like this:

1) A couple of applications keep track of the new rows added, querying on id > lastId and indexed TM interval of last day each approximately  2-3 times a minute

2) A couple of rows per minute are added to this table and then actively parsed into other tables

3) every time a user selects a message or does a search (I guess 10-20 times a minute per app running) a query is done into this table with custom where on approx. 10 indexed fields.


texts: Determine the PackageFormatVersion for multiple .DTSX packages files in a folder

texts:
Kenneth Fisher put a blog post out about how to determine What SQL Version is My SSIS Package? in April, 2015.

It has a table of which SQL versions map to which PackageFormatVersion of the SSIS package found in the XML metadata. This is useful when looking at 1 single individual package.

I have a folder of about 100 SSIS .DTSX packages that I need to know which SQL Version they all are.

How can I determine, in bulk, what the PackageFormatVersion (i.e. the SQL version) for multiple .DTSX packages in a folder (file system)?

The end goal is to determine which is the proper TFS version to get and implement to put these packages into, as no source control system exists currently. The table Kenneth presents will help me answer this question, but first I need to confirm what the package SQL versions are.

Assume that I do NOT have BIDS nor SSDT installed.

Assume a desired output would be something like this, where pipe designates a new column:

PackageFilename | PackageFormatVersion
--------------------------------------
Package1.dtsx   | 3
Package2.dtsx   | 4

PowerShell, TSQL, 3rd party tools that can crawl a directory structure, or other tools are welcome.


texts: Postgres COPY with on conflict ignore - possible?

texts:
I want to add on conflict ignore to the Postgres copy command.
I know I can copy the data to a table without unique index /primary key and then use insert with the on conflict syntax.
But I wanted to know if this is possible directly from COPY?


texts: After doing latest windows10 update 1909 SQL Server Management Studio 17 not working

texts: yesterday I have an update for windows version 1909, after updating to the latest version, When I tried to open SSMS it shows me an error like this(please check the screenshot).


texts: Occasional error when running sp_BlitzFirst

texts:
I have set up sp_BlitzFirst to run as a job (for the PowerBI Dashboard). Occasionally, when it runs, it gets the following error:

Msg 8152, Sev 16, State 13, Line 2980 : String or binary data would be truncated. [SQLSTATE 22001]

This is actually happening in the sp_BlitzCache procedure at this line:

INSERT #stored_proc_info ( SPID, SqlHandle, QueryHash, variable_name, variable_datatype, compile_time_value, proc_name )
SELECT vi.SPID, vi.SqlHandle, vi.QueryHash, vi.variable_name, vi.variable_datatype, vi.compile_time_value, vi.proc_name
FROM #variable_info AS vi
OPTION ( RECOMPILE );

How can I resolve this? Thanks.

Darrell


texts: Is ALTER TABLE ... DROP COLUMN really a metadata only operation?

texts:
I've found several sources that state ALTER TABLE ... DROP COLUMN is a meta-data only operation.

Source

How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying non-clustered indexes and clustered index / heap?

In addition, why do the Microsoft Docs imply that it is a fully logged operation?

  The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. Run these ALTER TABLE statements with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

As a secondary question: how does the engine keep track of dropped columns if the data isn't removed from the underlying pages?


texts:
Today I noticed that the backup of the log of our database was 18GB but the size of the log file is only 8GB.

I used the following command to backup:

BACKUP LOG [myDB]
TO DISK = N'g:\sqlbackup\LOG\myDB_LOG_20160329_084501.trn'
WITH CHECKSUM
    , COMPRESSION;

Can anyone explain how it's possible that the backupfile is larger than the logfile?

Update:

The database is in full recovery mode and here are 216 VLF's in my log file.
I just looked to the size of the ldf file to determine the size of the log (I know this is not the right way but you would expect if the backup is 18GB the ldf file would be greater than 8GB).

I asked my colleague and he shrunk the log file manually before backup, the size changed from 10GB to 8GB (only 2% of the logfile was being used).
Why would a shrink (after or before backup) of the log file cause this?
Also there is only one backup in my backup file.


texts: Using IMPORT FOREIGN SCHEMA with Greenplum database

texts:
I have setup a PostgreSQL 12 database and am trying to connect to a Greenplum database in order to create proxy tables.  I am able to connect to the Greenplum db, but I get an error when I try to use the IMPORT FOREIGN SCHEMA command.

IMPORT FOREIGN SCHEMA remote_schema FROM SERVER "remote_server" INTO schema_test_1;

returns:

    ERROR:  Greenplum Database does not support REPEATABLE READ transactions. (variable.c:570)
CONTEXT:  remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
SQL state: XX000

I read that REPEATABLE READ is not supported in Greenplum and to use SERIALIZE instead.  Is there a way to edit the IMPORT FOREIGN SCHEMA command so that I can replace REPEATABLE READ with SERIALIZE?

I am using PGadmin 4.

Update:

I found that I can get commands to work if I write them as complete transactions and include the following before any commands:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Is there a way to set this as the default value for all transactions going through the Foreign Server?


texts: Why would I download CA certificate when starting a managed Postgres cluster on DigitalOcean?

texts:
I am trying for the first time a Postgres cluster provided as a managed service by DigitalOcean.com.
As part of configuring the new cluster, their web interface gives me a user name, password, port number and such. On that web page is a tiny link saying Download the CA certificate. I assume CA means Certificate Authority.
➥ Why would I use or not use that link to download the certificate?
When I click the link, I end up with a file on my machine named ca-certificate.crt. How would I use this file?


texts: Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

texts:
I dropped my UNIQUE KEY index 'myindex' on a table
and I wanted to change the UNIQUE KEY to something else,
so after dropping it I did:

alter table mytable add unique key anykey (add1, city, state);

I now get the following message on a key that does not exist
(it doesnt seem to matter what key I change 'anykey' to, I always
get the same error:

ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

I dumped the entire DB and reloaded it, but still cannot seem to set
a unique key on 'mytable'.

Anyone know what I should check or how to get around this issue?
I WANT a unique key on this table.

The tables important parts:

PRIMARY KEY (`id`)
ENGINE=MyISAM AUTO_INCREMENT=3433 DEFAULT CHARSET=latin1

Thank you in advance for any help!

EDIT:  here's show create table

| mytable | CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`name_aka` varchar(100) DEFAULT NULL,
`in_add1` varchar(50) NOT NULL,
`in_add2` varchar(50) NOT NULL,
`in_city` varchar(50) NOT NULL,
`in_state` varchar(2) NOT NULL,
`in_zip` varchar(5) NOT NULL,
`in_zip4` varchar(4) NOT NULL,
`ship_add1` varchar(50) NOT NULL,
`ship_add2` varchar(50) NOT NULL,
`ship_city` varchar(50) NOT NULL,
`ship_state` varchar(2) NOT NULL,
`ship_zip` varchar(5) NOT NULL,
`ship_zip4` varchar(4) NOT NULL,
`phone` varchar(14) NOT NULL,
`phoneExt` varchar(6) NOT NULL,
`phone2` varchar(14) NOT NULL,
`phone2Ext` varchar(6) NOT NULL,
`fac_fax` varchar(6) NOT NULL,
`gender` varchar(7) NOT NULL,
`newsletter_ok` enum('Y','N') DEFAULT 'Y',
`notes` varchar(300) NOT NULL,
`fac_type` varchar(14) NOT NULL,
`fac_contact_email` varchar(50) NOT NULL,
`ind_eng_bks_shipped` varchar(6) NOT NULL,
`ind_span_bks_shipped` varchar(6) NOT NULL,
`ind_eng_s_bks_shipped` varchar(6) NOT NULL,
`ind_span_s_bks_shipped` varchar(6) NOT NULL,
`indiv_other3_bks_shipped` varchar(6) DEFAULT '0',
`cases_eng_bks_shipped` varchar(6) DEFAULT '0',
`cases_span_bks_shipped` varchar(6) DEFAULT '0',
`cases_other1_bks_shipped` varchar(6) DEFAULT '0',
`cases_other2_bks_shipped` varchar(6) DEFAULT '0',
`cases_other3_bks_shipped` varchar(6) DEFAULT '0',
`last_update_by` varchar(20) NOT NULL,
`last_update_date` datetime NOT NULL,
`rev` varchar(9) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3435 DEFAULT CHARSET=latin1 |

I removed the ---- stuff obviously.


texts: Change a specific line of a view with conditions

texts:
I've recently started working at a company that maintains between four hundred and five hundred client databases, and am doing various maintenance tasks on code (I am junior level after all).  I've been tasked with identifying a particular bug in a group of views across the entire scope of the company, and changing each one. I was able to identify all of the culprits already via SQLSearch (a lifesaver), but am now tasked with modifying one particular line of code to a new standard across almost 1,200 views.

I know:

The wrong line of code (there are 3 variations, i know all three)
The correct line of code I need to replace the wrong lines with
Which views and which databases have the error.
How many views in each database are currently 'wrong'

How can I easily modify the views to correct the error without doing it by hand?


texts: highlight an instance where a volume reported is unchanged from the previous month

texts:
I have a table as follows:

Fields:         Name   Month   Amount
Instance1:      Alan   Jan2018  120
Instance2:      Alan   Feb2018  140
Instance3:      Alan   Mar2018  140
Instance4:      John   Jan2018  50
Instance5:      John   Feb2018  50
Instance6:      John   Mar2018  60

In these set of values, I would like to highlight Instance 3 and Instance 5 because the amount remains unchanged from previous month for a same individual. Can someone please help?


texts: How to pattern match against Unicode characters by PostgreSQL `SIMILAR TO` expressions?

texts:
In a PostgreSQL database, I have a column named metaid of type text whose value contains digits as specified in the third edition of XML 1.0 standard:
Digit ::= [#x0030-#x0039] | [#x0660-#x0669] | [#x06F0-#x06F9] | [#x0966-#x096F] | [#x09E6-#x09EF] | [#x0A66-#x0A6F] | [#x0AE6-#x0AEF] | [#x0B66-#x0B6F] | [#x0BE7-#x0BEF] | [#x0C66-#x0C6F] | [#x0CE6-#x0CEF] | [#x0D66-#x0D6F] | [#x0E50-#x0E59] | [#x0ED0-#x0ED9] | [#x0F20-#x0F29]

In the documentation of PostgreSQL for SIMILAR TO expressions, matching against such Unicode characters by their range is not mentioned.
I need to put a CHECK constraint on the column for ensuring the pattern of the entered value conforms to the required format.
How can I accomplish this?


texts: alter primary id field to uniqueidentifier GUID as default in sql server

texts:
I have tables with id(primary key - int) with identity specification (1,1) .
I want to change this column to uniqueidentifier with default ( newid() ) .

What I've tried is

ALTER TABLE myTable ALTER COLUMN id uniqueidentifier default NEWID();

but I got this message

Incorrect syntax near the keyword 'default'.

Additionally ,every tables of my database has id primary key column and I want to change them to uniqueidentifier with default newID() , by looping or something .


texts: Converting ER diagram to tables and relationships in sql

texts:
Department : Branch is 1 : M

Branch : Course is 1 : M

Branch : Student is 1 : M

Branch : Applicant is M : N

Applicant : Student is 1 : 1

These are the conditions prescribed in the ER diagram.

This is the ER diagram, for which tables have to be made in SQL code implementing all the constraints. I made tables and tried implementing all the relationship via foreign keys, i just wanted to confirm, whether these tables are correct or not.

1) Department table:

create table department(dpet_id number primary key, dept_name varchar2(15)
not null);

2) Branch table:

create table branch(branch_id varchar2(5) primary key, electives varchar2(10),
dept_id number references department(dept_id));

3) Course table:

create table course(course_id number primary key, course_name varchar2(10)
not null,branch_id varchar2(5) references branch(branch_id));

4) Student table:

create table student(stud_id number primary key, stud_name varchar2(30) not null,
branch_id varchar2(5) references branch(branch_id);

5) Applicant table:

create table applicant(app_id number primary key, stud_id number constraint fk
references student(stud_id) constraint stu_unq unique);

6) Applicant_branch table:

create table applicant_branch(app_id number references applicant(app_id),
branch_id varchar2(5) references branch(branch_id));

Do these tables conform to the ER diagram ?

I have tried to give a picture of ER diagram above giving cardinality conditions. Now please tell me if there is any mistake in my SQL code.


texts:
My backup plan is:

Full backup - once a day
Differential backup - every four hours
Transaction Log backup - every 30 minutes.

All database backup are stored on my computer.

I add some wrong changes to my database and now I need to restore it to point-in-time (SQL Server 2012) with the help of SSMS.

Firstly I restored the last full backup WITH NORECOVERY, it restored successfully, then I tried to restore the last differential backup WITH NORECOVERY and received the following error message:

  Unable to create restore plan due to break in LSN chain.

Please advise. What am I doing wrong?


texts: Select the greatest value from each column, even if one of the values is null

texts:
I have a road_age_test table:

create table road_age_test (
    surface_year int,
    base_year int
);

insert into road_age_test (surface_year, base_year) values (10,20);
insert into road_age_test (surface_year, base_year) values (30,null);
insert into road_age_test (surface_year, base_year) values (null,40);
insert into road_age_test (surface_year, base_year) values (null,null);
insert into road_age_test (surface_year, base_year) values (50,50);

commit;

select * from road_age_test;

SURFACE_YEAR  BASE_YEAR
------------ ----------
          10         20
          30       null
        null         40
        null       null
          50         50

I would like to select the greatest value from each column, even if one of the values is null:

GREATEST_YEAR
-------------
           20
           30
           40
         null
           50

What's the simplest way to do this?

Note: Where both values are null, I want to return null, not 0.


texts: meaning of ## in SQL Server Profiler trace data

texts:
I'm running a trace using the SQL Server Profiler.  The trace data contains the following lines:

WHERE CAST(LEFT(pc.Time_stamp,11) AS DATETIME >= ##
AND CAST(LEFT(pc.Time_stamp, 11) AS DATETIME) <= ##

What does the ## mean


texts: Why do I get "The initial snapshot for publication is not yet available."?

texts:
We are using Transactional Replications with updatable subscriptions. 1 publisher, 1 subscriber.

SQL Replication started encountering a constraint error on 3/6 at 10AM because people at two different offices entered data that violated a unique constraint. Totally expected.

However, at 12:08PM on 3/6, SQL Replication stopped reporting that error and instead said:

"The initial snapshot for publication 'TheDB' is not yet available."

Why would it stop retrying replication and instead start throwing this error? Now we need to regenerate the snapshot.

No DB backups or restores were performed at this time
The snapshot was successfully applied at the subscriber months ago when replication was initially set up.
There is a message in the log from 12:07 on 3/6 saying

[000] Request to run job COTOPSVR\TheDB-TheDB-COTCPSVR\DB-3 (from User CTOB\Administrator) refused because the job is already running from a request by User sa

No one manually initiated any job or did anything at all at 12:06PM.

Any ideas? I know how to fix this...I want to prevent this from happening again.


texts: Assign most elevated permission to Sql Server DB

texts:
I am using SQL server 2008 R2, on my development machine. I had to delete the old windows user account, with which I had installed SQL Server.

I have created a new admin account.
When doing any DB related work in Visual Studio with new user, I get DB Access denied error on master table.

As this is basically for development purpose, I need most elevated permission for this user.

How do I grant a windows user admin rights to all the tables?


texts: How much disk space do I need to add a primary key to a very large table?

texts:
I'm using Microsoft SQL Server 2014 - 12.0.4100.1 Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

I have a 491 GB table with 3.6 billion rows that does not have a primary key. The primary key I wish to add will only cover a single bigint column which contains unique values. The database has 477 GB of free space, but that's not enough it seems.

Trying to add it fails with the following error:

  Could not allocate space for object 'dbo.SORT temporary run storage:
  424251614560256' in database '[Redacted]' because the 'PRIMARY'
  filegroup is full. Create disk space by deleting unneeded files,
  dropping objects in the filegroup, adding additional files to the
  filegroup, or setting autogrowth on for existing files in the
  filegroup.

The database name in the error is the DB to which I'm trying to add the PK, not 'tempdb', which is what I often see in this sort of error.

The command I'm using:

ALTER TABLE REDACTED ADD CONSTRAINT [PK_REDACTED] PRIMARY KEY CLUSTERED
(
    [RedactedId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

At first, the command was failing after 8-9 minutes, then my DBA added another data file to the DB on a drive with about 150 GB of free space. Running it a second time failed after 24 minutes. That's progress, but I have no idea how much free space is needed, or how long it will actually take to add this index. There is currently 477 GB free in this database, but that's still not enough to run this sort it seems.

Is there any way to calculate how much space is needed to run this command on 3.6 billion bigint values? Or is there some other trick that will help this command succeed? What does that large number mean in the error?


texts: SQL Schema - Messed up my schema and want to revert

texts:
I was playing around a bit with my database.table schema (keys) and I have now messed it up. This is the original schema:

CREATE TABLE IF NOT EXISTS `xeon_stats_clicks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `typ` enum('1','2','3','4','5','6','7','8','9') COLLATE utf8_bin NOT NULL,
  `user` varchar(20) COLLATE utf8_bin NOT NULL,
  `data` varchar(10) COLLATE utf8_bin NOT NULL,
  `value` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `typ` (`typ`,`user`,`data`),
  KEY `data` (`data`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

This is the new one:

CREATE TABLE `xeon_stats_clicks` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `typ` enum('1','2','3','4','5','6','7','8','9') COLLATE utf8_bin NOT NULL,
 `user` varchar(20) COLLATE utf8_bin NOT NULL,
 `data` varchar(10) COLLATE utf8_bin NOT NULL,
 `value` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `idx_value` (`value`),
 KEY `idx_typ` (`typ`),
 KEY `idx_data` (`data`),
 KEY `idx_user` (`user`),
 KEY `data` (`data`),
 KEY `data_2` (`data`)
) ENGINE=MyISAM AUTO_INCREMENT=991799 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

The table contains over 300k rows. I took a backup of the table before the schema was changed. I tried creating a new table using the old schema design, but I was unable to import my backup due to this error:

#1062 - Duplicate entry 'kate18' for key 'user'

Can someone please assist me on how I can revert back to my original schema?


texts:
I need to check the status of SQL Server. When I say status its not the status for each database, but rather for the whole SQL Server.

I need to know what are the queries running in the server and no. of connections, which query is taking too long.

Can anyone please help me with this..


texts: FileTable in 2016 and Transaction Log Backups larger than expected

texts:
I am testing using FileTable in SQL Server 2016 to store files of different sizes (could be pdf documents sized around 1mb to video files that are 200mb).
When adding a file to the FileTable, I take a trans backup and it is always around 2x the size of the files added.

Ex: 42mb file results in a 87mb tlog backup, 13mb file results in 27mb tlog backup.

Has anyone used FileTable and noticed transaction log backups that were much larger than the files added. Files are only being added to the share. Nothing is being updated or edited between adding and taking a log backup.


texts: Include fields from a table function in a select

texts:
I'm running this query on SQL Server 2008 R2:

SELECT
   orr.Rate, orr.ID, orr.UserKey, fn.NetRate
FROM
   OngoingRate orr
CROSS APPLY
   dbo.GetRateNetView(orr.UserKey) as fn
WHERE
   orr.UserKey = 5

I get the following error:

  Incorrect syntax near '.'.

Please help


texts:
I've done lots of research on the topic but I'm drawing a blank.  Are their any database engines that can return rendered images in the query response?

For example:

Select images from fruit where type = "apple";

Would return rows of the apple type as rendered images, png, jpg, etc.


texts: Difference between commit and checkpoint

texts:
I was confused about the difference between a commit operation and a checkpoint. I referred to some of the articles like This Article

But they are advanced for my understanding. I mean both appear to be same i.e. saving data like a "Save" action. If we rollback, we can rollback only to the most recent checkpoint/commit. Am I right in my statements? Can someone explain?


texts:
How to find offset/index base on id with order?

SELECT p.*
FROM product p
WHERE p.id = $1
ORDER BY p.id desc NULLS LAST

id:1 return 0

id |
1  |
2  |
3  |


texts: SQL Server - MERGE duplicate SOURCE rows validation is only evaluated on UPDATE and DELETE clauses

texts:
I want SQL Server to throw an error at me if my SOURCE table contains duplicates as per the ON clause in every case, including INSERT, but it only does for the UPDATE and DELETE cases. Why doesn't it?

For example, it takes two executions of this statement to get any sort of validation error. First, let's define the table:

--assume this is a permanent table that starts empty:
CREATE TABLE #MyTable(KeyID1 INT, KeyID2 INT, SomeValue REAL)--no primary key on purpose for example

And the MERGE statement I wish would throw the error at me on the first execution:

MERGE #MyTable AS TARGET
USING
(
    SELECT
         KeyID1 = 1
        ,KeyID2 = 1
        ,SomeValue = 1
    UNION ALL SELECT
         KeyID1 = 1
        ,KeyID2 = 1
        ,SomeValue = 2
) AS SOURCE ON
    TARGET.KeyID1 = SOURCE.KeyID1
    AND TARGET.KeyID2 = SOURCE.KeyID2
WHEN MATCHED THEN UPDATE SET
    TARGET.SomeValue = SOURCE.SomeValue
WHEN NOT MATCHED BY TARGET THEN INSERT
    (
         KeyID1
        ,KeyID2
        ,SomeValue
    )
    VALUES
    (
         SOURCE.KeyID1
        ,SOURCE.KeyID2
        ,SOURCE.SomeValue
    );

After the first execution, the contents of #MyTable are

The error message that only comes on the second and subsequent executions:

  Msg 8672, Level 16, State 1, Line 4
  The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

The wording of this error message seems to imply my desired validation should be occurring already.

Of course, one obvious thing to do is make the primary key on the TARGET table reject the INSERT, but that is unrelated to my question.

P.S. I came across this list of MERGE bugs that doesn't seem to apply here.


texts: Creating Temporal table without adding DateTime columns

texts:
I'm an SQL server DBA and new to Temporal tables. We have the following issue:
As part of implementing temporal tables mechanism , there's a need to add to datetime columns (so DWH will have the ability to investigate only last modified columns).

1st problem is that our R&D are trying to avoid from altering table structure (adding columns) as it is a sensitive and big table.

2nd problem is that the original table contains MODIF DATE column.
This column is being updated at the datetime value of the update transaction beginning time warriors I need the COMMIT time.(gaps are critical)

My question:
Is there a possibility to turn the original table to Temporal one , without adding columns and only by using the CREATION date and MODIF date built-in columns?
Will appreciate any support here!


texts: Risks when running a MySQL Galera Cluster in production without binlogs?

texts:
First of all: I assume that this is no real good idea.

However, this Bug in MariaDB 10.1.xx crashes single cluster nodes.

https://jira.mariadb.org/browse/MDEV-9510

One of the workarounds is to disable the binlogs.

I personally think, that this comes with a lot of other risks, like inconsistent databases or not being able to get the database into a consistent state after restarting.

I need to understand the risks and drawbacks before eventually taking the decision to disable the binlogs.


texts: Cannot start psql with user postgres “Could not change directory to /home/user ”

texts:
I did a silly mistake. I wanted to set postgres permissions to a folder for exporting data from my database. So I set something like sudo chmod 777 -u postgres /home/user and then my home partition only has root permissions and nothing worked without root permissions. I solved this problem by using sudo chown user /home/user and then I set the permissions manually in the file manager on linux. It was very stupid, I didn't knew very much about allocating permissions to directories and users.

The Problem is now, that I can't connect to my database anymore.

 ~ $ sudo -u postgres psql
[sudo] password for user:
could not change directory to "/home/user"
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I think I want to connect in a folder where the postgres user can't read.

Restarting the server with sudo service postgresql restart doesn't work as well.

I'm using PostgreSQL 9.1.


texts: Difficult Query: Can Only Modify The WHERE Clause

texts:
Table tblDistResell (the first row is the column headers):

DISTRIBUTOR RESELLER    STATE
ABC         BestBuy     CA
ABC         RadioShack  NJ
XYZ         BestBuy     CA
XYZ         RadioShack  MO

Query:

SELECT Distributor
WHERE
(
(Reseller = 'BestBuy' AND [State]='CA') OR (Reseller = 'RadioShack' AND [State]='NJ')
);

Resultset:

DISTRIBUTOR  RESELLER    STATE
ABC          BestBuy     CA
ABC          RadioShack  NJ
XYZ          BestBuy     CA

When EVERY row for a distributor is returned, we want the count (or the resultset; whatever's easier).

But if only ONE (or more) distributor record(s) aren't returned, we want to ignore all of that distributor's results.

In other words, given the example above, we want to see either,

DISTRIBUTOR  RESELLER    STATE
ABC          BestBuy     CA
ABC          RadioShack  NJ

or

count == 2

(No XYZ records, since the RadioShack, MO record is missing from the resultset.)

Here's one more example for clarity...

Let's use the same table, tbltblDistResell, but a modified query:

SELECT Distributor
WHERE
(
(Reseller = 'BestBuy' AND [State]='MO') OR (Reseller = 'RadioShack' AND [State]='NJ')
);

In this case, the following is returned:

DISTRIBUTOR  RESELLER    STATE
ABC          RadioShack  NJ

But since there is some ABC data that wasn't returned, we want to see either:

(0 row(s) affected)

or

count == 0

The challenge is that the tool we're using only allows us to modify the WHERE clause!

Any help would be greatly appreciated.

Greg


texts:
I have a situation here. Currently I have

two polling tables (poll_one and poll_two) which will poll data from outside data-source. This two tables will keep interchange between each other.
one checking table (check_poll) which tells me which tables (poll_one or poll_two) is active
one user edit table (edit) which stores exact same columns and names as the two poll tables.

Basically I wan to create a dbview that joins the edit table and the active poll table. However, I am not sure how to do a conditional dbview to first check which polling table is active before performing my query.

I cannot use stored procedures as this dbview will be used for my hibernate mapping.

Below is a sample definition of my dbview:

SELECT
CASE
WHEN u.name IS NULL THEN p.name ELSE u.name
END AS name,

CASE
WHEN u.size IS NULL THEN p.size ELSE u.size
END AS size,

//same for the rest of columns

FROM user_edit_table u
LEFT JOIN poll_one p //I wan to make this conditional
ON p.id = u.id

Is it possible? Is there any other ways?

Thanks for all the replies,
An example is shown in the Image Below:

So based on the check table, I will choose which table I wan to left join. There is no foreign relation between the tables. All are stand alone.


texts: SqlLocalDB.msi and DBNETLIB / PowerShell SqlServer ServerConnections

texts:
I'm trying to setup a development SQL server using SqlLocalDB.msi which installs a local version of SQL Server 2017.

I can connect to the SQL engine with SSMS (v14.0.17277.0) using MDAC (v10.17134.1).

But, when I open SQL Server Configuration Manager and click on SQL Server Network Configuration (32-bit), it says "There are no items to show in this view."

This appears to be a real problem because when a third-party app I have to use tries to open a connection to the database, it fails withthe following message:

But, the Server does exist.  Here's a screen shot from SSMS:

Another clue to what may be going on is that no ServerInstances are listed when when I open an administrator Power Shell and do the following:

PS C:\Windows\system32> import-module "sqlserver"
PS C:\Windows\system32> $smo = 'Microsoft.SqlServer.Management.Smo.'
>> $wmi = new-object ($smo + 'Wmi.ManagedComputer').
>>
>> # List the object properties, including the instance names.
>> $Wmi

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           : {}
ClientProtocols    : {np, sm, tcp}
ServerInstances    : {}
ServerAliases      : {}
Urn                : ManagedComputer[@Name='WINDEV1806EVAL']
Name               : WINDEV1806EVAL
Properties         : {}
UserData           :
State              : Existing

Does anyone know how to get SQL Server Express to work with DBNETLIB?


texts:
We are experiencing deadlocks in our production environment. Seems to be the victim and winner are the same spid's. We rebuild/reorganize indexes daily and we dont find any missing indexes.

Have you guys ever encountered this scenario and if so how did you guys solve it?  Any suggestions are appreciated.

Here's the deadlock graph:

Additionally, here's

the deadlock XML: https://justpaste.it/6xasd
and the query plan: https://www.brentozar.com/pastetheplan/?id=ryBbs-XPQ

We are on SQL Server 2017 SP2.


texts: Update all rows when sort/ordering value is changed for single row?

texts:
I am in the process of creating a GIS application with multiple layers and many pieces of content shapes on each layer. Currently each layer/shape is drawn in the order that it was returned from the database, but I want to give the user some control over the ordering. So the user should be able to drag and drop layers/shapes up and down in the layer tree, which should change the drawing order.

In order to persist these changes, each layer/shape must have som sort/ordering index in the database. However, if I use integers for the sorting key, whenever a layer/shape is sorted into a new position, this would require updating all of the following rows with +1.

Is there a good way to avoid this?

The reason I would like to avoid this issue is that there might potentially be a lot of content (thousands of shapes) in a layer, and I am concerned about the performance impact of updating that many rows. Furthermore, it's just not a very elegant solution.

I have considered using floating point values instead, which would make it possible to simply create a new value between two existing values, but at the risk of eventually running into precision problems.

Another approach I am considdering is storing the sort order as a kind of linked list, where each row points to the previous or following row, which would reduce the required number of updates, but would make construction of the tree in the GUI more complex. Even worse, if not all shapes/layers are displayed at all times, creating the layer/shape tree would become impossible.

Is there a commonly accepted good solution for this kind of problem?


texts: Clustered index on a column with low selectivity

texts:
I have a table with a lot of columns. Two lookup functions are performed: 1) lookup by using column name (this happens rarely, maybe twice a season), and 2) lookup by column date. The column name has a very high selectivity measure (almost all rows are distinct), whereas the date column has very low selectivity measure (only several distinct columns over a table size of 300,000 records)

I need to optimize the performance by creating an index. I wanted to create a clustered index on the date column, but it is my understanding that doing so will even slow down the performance of my queries given the low selectivity measure.

What should I do - Clustered index on date, or just clustered index on name (or no indexes at all) ? The name lookup happens rarely, but date lookup queries run all of the time.
Thanks a lot!

I'm using SQL Server 2012.


texts: How to restore operational Oracle 11g Database from full backup?

texts:
Imagine that things gone bad and all we have is a fresh Windows Server and full cold backup of our Oracle database.

In case of SQL Server 2000/2005/2008/2012 it was really a matter of few mouse clicks: copy backup files, install db instance, restore files with few clicks, recreate users and we are good to go.

But now we are setting up Oracle 11g DB Standard as our database solution and from what I've seen over Internet it's a really complicated issue up to the point where this cold backup files might become completely useless without "actions" taken outside "usual" backup procedure.

While looking for answers, I've ran across certain topics like "db duplication form backup without access to flash recovery area" and etc which looks like to be close to what we were doing in SQL Server. Problem is all this things are far outside of my own experience (SQL Server, remember?) so I can't even tell if I'm looking in a right direction.

So my question is (I'll split it in two, because I think it matters in Oracle):

1) What should I do in order to properly "backup" my production database and restore it lately on different machine in case of emergency if we have not got yet any emergency instance installed?

2) What should I do in order to clone my production database and restore it on prepared instance (to clone production db).

What's most important: is there a common approach to both this tasks like in SQL Server?


texts: sp_stop_job reports Success, but the jobs are still running

texts:
We have some jobs that run via the scheduler. We noticed today that some are still running from the weekend, and we need to kill them.

We have used sp_stop_job and it reports that the job has been stopped sucessfully, but looking at the Job Activity Monitor that status is still Executing: 1 (Subplan_1). I am using various queries to get the jobs that are currently running and the jobs still appear.

SELECT [sjv].[name], [sjv].[description], [sja].[run_requested_date], [sja].[last_executed_step_id]
FROM [msdb].[dbo].[sysjobs_view] sjv
JOIN [msdb].[dbo].[sysjobactivity] sja
ON [sjv].[job_id] = [sja].[job_id]
WHERE [sja].[run_requested_date] is not null
AND [sja].[stop_execution_date] is NULL

How is this possible? Is it just sending an ABORT command or similar? And can I really kill the jobs immediately?

I'm rather new to this side of SQL.


texts: Why ORA-00932: Inconsistent Types error?

texts:
Sorry for the meaningless title, but I couldn't think up a better one. I'm using Oracle 11g r2.

The following query raise ORA-00932 error:

SELECT BBB.FIELD1 FROM TABLE0 AAA
  JOIN (SELECT * FROM (SELECT AAA.*, RANK() OVER (PARTITION BY SUBSTR(FIELD1,1,1) ORDER BY SUBSTR(FIELD1,1,1),ROWNUM) AS RANK
      FROM TABLE1@DBLINK AAA WHERE UPPER(NAME)!='XXX')
    WHERE RANK<=1) BBB ON CAST(CAST(CAST(SUBSTR(BBB.FIELD1,2,LENGTH(BBB.FIELD1)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(BBB.FIELD1,LENGTH(BBB.FIELD1)) AS VARCHAR2(4000))=CAST(CAST(CAST(SUBSTR(AAA.FIELD0,2,LENGTH(AAA.FIELD0)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(AAA.FIELD0,LENGTH(AAA.FIELD0)) AS VARCHAR2(4000));

However, the following query works perfectly fine.

SELECT * FROM TABLE0 AAA
  JOIN (SELECT * FROM (SELECT AAA.*, RANK() OVER (PARTITION BY SUBSTR(FIELD1,1,1) ORDER BY SUBSTR(FIELD1,1,1),ROWNUM) AS RANK
      FROM TABLE1@DBLINK AAA WHERE UPPER(NAME)!='XXX')
    WHERE RANK<=1) BBB ON CAST(CAST(CAST(SUBSTR(BBB.FIELD1,2,LENGTH(BBB.FIELD1)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(BBB.FIELD1,LENGTH(BBB.FIELD1)) AS VARCHAR2(4000))=CAST(CAST(CAST(SUBSTR(AAA.FIELD0,2,LENGTH(AAA.FIELD0)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(AAA.FIELD0,LENGTH(AAA.FIELD0)) AS VARCHAR2(4000));

The only difference is that I selected all (*) columns instead of specifying one specific column. Why is this happening?

EDIT

The error raised is (exactly as it is):

ORA-00932: inconsistent datatypes: expected  got
ORA-02063: preceding line from DBLINK
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:


texts: Migration from SQL Server 2008 to Postgres 9.2

texts:
I have a rather large database (2 GB of data, 25 million rows in the largest table) on a shared web hosting. The database engine is Microsoft SQL Server 2008.

Due to high fees for the SQL Server web hosting, I would like to migrate my data from SQL Server to a Postgres database.

In my SQL Server database I have tables, indexes, constraints and stored procedures. The data types in my tables are VARCHAR, TINYINT, SMALLINT, INT, SMALLDATETIME, REAL and FLOAT.

In my stored procedures I am using some built-in T-SQL Date/Time manipulation functions like DateAdd() or DatePart().

Is there any FREE tool available that would help me migrate my data (tables, constraints, and if possible also the stored procedures) from SQL Server to Postgres?


texts: LEFT JOIN with WHERE (Microsoft Access SQL)

texts:
Given following 2 tables a, b:

table a

ID job
1  farmer
2  physician
3  bank clerk

table b

bID ID lang job
1   1  en   farmer
2   2  en   physician
3   3  en   bank clerk
4   2  de   Arzt
5   1  fr   paysan

Query

SELECT a.ID, a.job, b.job FROM a LEFT JOIN b ON a.ID=b.ID WHERE b.lang='de'

wanted:

a.ID a.job        b.job
1    farmer       -
2    physician    Arzt
3    bank clerk   -

actual result:

2    physician    Arzt

What's wrong with the LEFT JOIN? Do I need some (())?


texts: Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key

texts:
When we are trying to deploy an assembly with PERMISSION_SET=UNSAFE or EXTERNAL_ACCESS, we are getting following error message by SQL Server:

 CREATE ASSEMBLY for assembly 'System.ServiceModel.Internals' failed because
 assembly 'System.ServiceModel.Internals' is not authorized for
 PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the
 following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission
 and the database has the TRUSTWORTHY database property on; or the assembly
 is signed with a certificate or **an asymmetric key that has a
 corresponding login with UNSAFE ASSEMBLY permission.**

We are not setting database trustworthy=ON, instead we are creating asymmetric key using  ALGORITHM = RSA_2048. Following is the sample code:

USE master;
GO

IF NOT EXISTS (SELECT 1 FROM sys.asymmetric_keys WHERE [name]='SampleClrKey')
BEGIN
      CREATE ASYMMETRIC KEY [SampleClrKey] WITH ALGORITHM = RSA_2048
      CREATE LOGIN [SampleClrLogin] FROM ASYMMETRIC KEY [SampleClrKey];
      GRANT UNSAFE ASSEMBLY TO [SampleClrLogin];
 END
GO

But this error is not getting resolved. I even provided sysadmin access to SampleClrLogin login for my database and master database, but it did not work. Can you guys please provide some insight to solve this issue.


texts:
My company has many branch offices and each branch has a DB server. Some have SQL Server 2005 and some others have SQL Server 2008. The schema is the same for all these servers.

Whenever I want to do DDL like adding column, etc., I do it either by connecting to each server one by one, or by running the DDL script with OSQL through a batch file.

Is there a built in tool to do DDL at once on several servers? How about third party tools? (preferably free ones)


texts: Different queries result in big difference in performance

texts:
I have 2 similar functions, different way result in big diff in performance.

The PostgreSQL version: 9.2.1.

Function 1

create or replace function func_1()  returns text  as
$$
declare
   v_i integer;
   v_md5 varchar;
begin
   for v_i in 1..1000000 loop
       v_md5:='a'|| v_i::character varying;
   end loop;
   return 'ok';
end;
$$
language plpgsql;

Function 2

create or replace function func_select()  returns text  as
$$
declare
   v_i integer;
   v_md5 varchar;
begin
   for v_i in 1..1000000 loop
      select 'a'|| v_i::character varying into  v_md5;
   end loop;
   return 'ok';
end;
$$
language plpgsql;

Function Timings

francs=> \timing
Timing is on.
francs=> select func_1();
 func_1
--------
 ok
(1 row)

Time: 1467.231 ms
francs=> select func_1();
 func_1
--------
 ok
(1 row)

Time: 1383.424 ms
francs=> select func_select();
 func_select
-------------
 ok
(1 row)

Time: 22176.600 ms
francs=> select func_select();
 func_select
-------------
 ok
(1 row)

Time: 23265.755 ms

From the above, function func_1() only takes about 1383 ms, but function func_select() takes about  23265 ms, anybody can explain this?


texts:
I am trying to delete some entries from table on a cluster and I get this error

ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER

I have increased MaxNoOfConcurrentOperations=1000000

but I still get this error

I never get this error when selecting or inserting.


texts: Different Results between stored procedure and UDF

texts:
I am getting different results between my stored procedure and my UDF.

The jist of of the both of them is that, it queries a remotely linked server, jams those results into a temp table/table variable, then replaces the NULLs with '00:00:00' so that I can sum the amounts. Then takes that date, and does a DATEDIFF on it to give me the total hours worked.

If I input the values ('John Doe', '04/05/2013', '04/16/2013'), the stored procedure is returning the answer I am looking for, the SVF is not. Where the stored procedure would result in "23" for hours worked, the SVF returns "1900-01-24 00:00:00".

I realize that it is with what I am returning in the SVF, but I can't figure it out. I have tried to return TIME(0), VARCHAR(5). It is as if the SVF isn't doing the DATEDIFF bit.

I am working with SQL Server 2012 (11.0.3000)

Below is the SVF and the stored procedure.

The SVF

ALTER FUNCTION [dbo].[fnFindHoursWorked]
    (@Name varchar(50)
     ,@Start_Date DATE = NULL
     ,@End_Date DATE = NULL)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    DECLARE @HoursWorked DATETIME

    -- Add the T-SQL statements to compute the return value here
    /** create a table variable to store the results of the query **/
    DECLARE @Duty TABLE (Time_on_Duty TIME(0))

    INSERT INTO @Duty(Time_on_Duty)

    /** The actual query that grabs the data from the  server **/
    SELECT
        CAST(UL.[Date_Time] - LAG(UL.[Date_Time], 1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME(0)) AS 'Time On Duty'
    FROM [LinkedServer].[database].dbo.[tablename] AS UL
    WHERE UL.[Department] = 'Department'
      AND ((UL.[Action] = 'OnDuty' OR UL.[Action] = 'Login') OR UL.[Action] = 'OffDuty')
      AND (UL.[Name] = @Name)
      AND ((CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)
            )

    /** Setting all of the NULLS in the table variable to a value on which we can do math **/
    UPDATE @Duty
    SET Time_on_Duty = '00:00:00'
    WHERE Time_on_Duty IS NULL

    /** The select statement to grab the total hours worked for the date range **/
    SET @HoursWorked = (SELECT DATEDIFF(hour,'1900-01-01 00:00:00',CAST(SUM(CAST(CAST(Time_on_Duty AS DATETIME) AS FLOAT)) AS DATETIME))  AS 'Time_On_Duty' FROM @Duty)

    -- Return the result of the function
    RETURN @HoursWorked
END

Here's the stored procedure:

ALTER PROCEDURE [dbo].[rptTotal_Time_On_duty]
    @Name varchar(50)
    ,@Start_Date DATE = NULL
    ,@End_Date DATE = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF OBJECT_ID (N'tempdb..#Duty', N'U') IS NOT NULL
       TRUNCATE TABLE #Duty
    ELSE
       CREATE TABLE #Duty (Time_on_Duty TIME)

    INSERT INTO #Duty(Time_on_Duty)
        SELECT
            CAST(UL.[Date_Time] - LAG(UL.[Date_Time],1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME) AS 'Time On Duty'
        FROM [LinkedServer].[database].dbo.[tablename] AS UL
        WHERE UL.[Department] = 'Department'
          AND ((UL.[Action] = 'OnDuty' OR UL.[Action] = 'Login') OR UL.[Action] = 'OffDuty')
          AND (UL.[Name] = @Name)
          AND (
           (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)
        )

    UPDATE #Duty
    SET Time_on_Duty = '00:00:00'
    WHERE Time_on_Duty IS NULL

    SELECT DATEDIFF(hour,'1900-01-01 00:00:00',CAST(SUM(CAST(CAST(Time_on_Duty AS DATETIME) AS FLOAT)) AS DATETIME))  AS 'Time_On_Duty'
    FROM #Duty

END


texts: Clustered index creation fail on table creation

texts:
We've got an error when running the following script;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Table_Name')
BEGIN
CREATE TABLE Table_Name
(
    Field_Name_1 binary(32) NOT NULL CONSTRAINT PK_Name_Goes_Here PRIMARY KEY NONCLUSTERED
    , Field_Name_2 int NOT NULL
    , Field_Name_3 datetime NOT NULL INDEX IX_Name_Goes_Here CLUSTERED
)
END

In particular it's the clustered index creation throwing the following error:

  Msg 1018, Level 15, State 1, Line 15
  Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

This is odd as it works on all of our servers except for a particular QA server. The fix we've put in place is to create the clustered index outside of the table creation statement but I'd be interested if anybody has come across this problem before?


texts:
I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know what the keys or values are, I have to use wildcards.

Regretably, my secret decoder ring seems to have let me down, because nothing I've tried has worked, possibly because all my expertise is in vanilla sql and mysql/mariadb.

Many thanks for help with this.


texts: PreparedStatement caching size on Oracle and SQL Server

texts:
I understand that PreparedStatements are complied and cached by database servers.

I would like to know if all the preparedStatements are complied and cached on the server, in which case what is the cache size on database?


texts: Pragmatic approach to k-means clusters with PostgreSQL

texts:
I am looking for a super simple and very pragmatic approach to k-means clustering of questions using PostgreSQL database alone.

While I am fully aware that this method may not yield meaningful results if my assumptions are off, it may be a good and first attempt at categorizing my data.

Imagine a small online forum, where users are free to ask brief questions about ANY topic they want but without the need to categorize them, and other users need to be notified about a new question if it matches the topics of interest to which they have previously subscribed.

My plan is to first break down each incoming question into lexemes using to_tsvector but in all honesty I am a bit lost about what to do afterwards.

Even assuming I have correctly identified the k categories to which the question may be matched, how would I go about deciding whether a questions should fall into one (or more) category?


texts: MySQL EXPLAIN gives more rows then the COUNT with WHERE on the indexed col


texts:
We are executing a large number of INSERT operations (140k circa) on a table during an ETL process and this error pops out:

Caught SQLException for insert into DMALM_USER_ROLES [...]
Caused by: java.sql.SQLException: ORA-01502: index 'DM_ALM.DM_ALM_USER_ROLES_PK' or partition of such index is in unusable state

The DBMS is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Any hint?

edit: after this process, the table DMALM_USER_ROLES is empty


texts: Select maintenance subplans via database_id

texts:
I have a SQL Server 2012 maintenance plan called 'backup databases'. This plan has two subplans 'weekly' and 'daily'. I would like a list of databases by ID associated with each of the subplans.

Or, more accurately, I would like to associate database ids with a subplan on a true/false basis.

I can query msdb.dbo.sysmaint.. like so:

SELECT
    *
FROM msdb.dbo.sysmaintplan_plans smp
INNER JOIN msdb.dbo.sysmaintplan_subplans smsp ON smp.id = smsp.plan_id

But I don't see anywhere I can join to master.sys.databases. Is there a way that I can do this?

I can see on this page here: https://technet.microsoft.com/en-us/library/ms188062(v=sql.110).aspx, reference to views that contain information on past backups. But I'm specifically looking for information on what databases are currently scheduled to be backed up.

I can find a list of backups in the msdb.dbo.backupset table, however the backups are not associated to databases, but rather database_guids (https://msdn.microsoft.com/en-us/library/ms186299.aspx).

This is useful in that I know what databases have been backed up, but I would still like to know what databases are registered in the current maintenance plan to be backed up.


texts: Select Unique Fields with Max Values in Each?

texts:
I have a table with names, numbers, and timestamps on the data. I want to know how to select a unique instance of each name based on how recent the timestamp is.

Addy (text), NW (number), UDate (timestamp), Stance (text).

I want to pull only one instance of Addy, and I want the one I pull to have the most recent UDate value. I'd like to make this into a separate table so that I've got the original for historical information and a more usable table with the most recent intel.

I'm using Microsoft Access 2013.


texts: Find and replace database name in views and stored procedures

texts:
I'm in a bit of a predicament. There are over 1,000 views and stored procedures in a handful of databases that are hard-coded to go after another database.

In my example, the database names in SQL Server are HOST_1 and LOYALTY_1. The views and procedures are trying to go after HOST_PROD and LOYALTY_PROD in the FROM and JOIN statements. Is there any way that I can do a global find and replace on all the views and procedures to replace the PROD with 1?

There has to be an easier way than to right click on each one, create the alter script to a new window, change the text, and press F5. Even if I can create all of the necessary alters and run them at once, that would work.


texts: I have an instance of RDS of MySQL 5.7.10

texts: I do not understand why memory bar seems to exceed to the red zone with 24.5 MB while RDS server should be with 1Gib


texts: How to select rows based on other tables - MySql

texts:
I have tables like this:

CheckTable

(id) (user_id)
[0]  [403]

UserTable

(id)  (username)
[403] [hello]

I want to select only the rows from the UserTable that do not have the user id row in the CheckTable

I could just select all the users, loop through the rows and then run another query checking if the user_id is in the UserTable, but that would be slow.

Is there any MySQL query that could join and select.


texts: Is there a Postgres client (for Windows) that can save customized sorting when viewing tables? Obviously I could write and save a query for this, but perhaps there's a client that can do this for me. I know MS Access can do this (via ODBC), but I'd prefer a better client.


texts: Why does postgresql differentiate between named and numeric time zone notations?

texts:
Why is there a difference between named timezone and numeric timezone offset, both of which yield different UTC timestamps for TIMESTAMPTZ?

Assume Europe/Prague timezone, which is currently +02:00 (DST); time of writing is 2018-08-25.

As you can check e.g. via google:

Also, in my postgres installation I can query it:

SELECT utc_offset FROM pg_timezone_names where name = 'Europe/Prague';

Which also yields 02:00:00.

Then, why on earth, do I get two different UTC timestamps when running following two queries?

The following yields 2018-08-25T20:00:00.000Z (2018-08-25 18:00:00-02):

SET TIME ZONE '+02:00'; -- with or without the plus
SELECT '2018-08-25 18:00:00'::TIMESTAMPTZ;

The following yields 2018-08-25T16:00:00.000Z (2018-08-25 18:00:00+02), which is correct:

SET TIME ZONE 'Europe/Prague';
SELECT '2018-08-25 18:00:00'::TIMESTAMPTZ;

Why does postgres differentiate those two notations?


texts: Can't recycle the ERRORLOG - error 17049

texts:
When we attempted to run sp_cycle_errorlog we get the following:

  Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 9
  Unable to cycle error log file from
  'G:\SQL_DATA1\MSSQL10_50.ABC\MSSQL\Log\ERRORLOG.15' to
  'G:\SQL_DATA1\MSSQL10_50.ABC\MSSQL\Log\ERRORLOG.16' due to OS error
  '1392(failed to retrieve text for this error. Reason: 15105)'. A
  process outside of SQL Server may be preventing SQL Server from
  reading the files. As a result, errorlog entries may be lost and it
  may not be possible to view some SQL Server errorlogs. Make sure no
  other processes have locked the file with write-only access."
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So far we have tried:

Failing over the SQL cluster resource to second node, rebooting server, failing back and rebooting second node
Using Process Explorer tool - this identified the only process using the ERRORLOG file to be sqlservr.exe

SQL version details:

  Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)  Aug 19 2014
  12:21:34  Copyright (c) Microsoft Corporation Enterprise Edition
  (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

There are two SQL instances on this two node SQL cluster, on the other instance the log cycles over with no issue.

I have seen a couple of posts on this subject but both appear to have been resolved by killing the non-sqlserver process. I'm presuming that the sqlsrvr.exe process is expected to be locking the file?

Any help appreciated.


texts: Database Tuning Advisor CLI "dta" missing

texts:
My Windows workstation can launch the GUI for the Database Engine Tuning Advisor, but the CLI called "dta" is missing.  Why?

Background

The Database Tuning Advisor is a SQL Server tool for suggesting changes to indexing and other things.  It can be run two ways: A GUI, and a CLI.  The GUI can be launched by searching for "Database Engine Tuning Advisor," or from SSMS.  The CLI can be accessed with the "dta" command.

The GUI runs just fine, but not the CLI.  According to the dta Utility documentation, the CLI is called "dta"

dta
[ -? ] |
[
      [ -S server_name[ \instance ] ]
      { { -U login_id [-P password ] } | -E  }
...

But when I try to run it, from either a user or Administrator command window, it's not found:

C:\WINDOWS\system32>dta
'dta' is not recognized as an internal or external command,
operable program or batch file.

What files named "dta" are present?

I used cygwin to search for all files which name starts with "dta".  Some are found, but no "dta.exe":

$ find -name 'dta*' -ls
562949953516183    0 drwxrwx---   1 SYSTEM   SYSTEM          0 Jun 12 09:03 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server/140/Tools/Binn/schemas/sqlserver/2004/07/dta
1125899906937496   52 -rwxrwx---   1 SYSTEM   SYSTEM      52868 Aug 22  2017 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server/140/Tools/Binn/schemas/sqlserver/2004/07/dta/dtaschema.xsd
1688849860388529   20 -rwxrwx---   1 SYSTEM   SYSTEM      16813 Jun  5 06:14 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server\ Management\ Studio\ 18/Common7/dta.exe.config
1688849860388532  220 -rwxrwx---   1 SYSTEM   SYSTEM     225080 Jun  5 06:04 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server\ Management\ Studio\ 18/Common7/dtaengine.exe
1688849860388534   20 -rwxrwx---   1 SYSTEM   SYSTEM      17902 Jun  5 06:14 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server\ Management\ Studio\ 18/Common7/dtashell.exe.config
199565758487855892    1 -rwxrwx---   1 wayne    None          592 Jun 11 09:22 ./Users/wayne/AppData/Roaming/Microsoft/Windows/Recent/dta_tutorial.lnk
10414574138517174    4 drwxrwx---   1 wayne    None            0 Jun 11 09:47 ./Users/wayne/Documents/dta_tutorial

What's installed

Windows 10.0.18363 Build 18363
SQL Server 1027 Standard Edition (14.0.2027.2)

SQL Server Replication
Full Text and Semantic Extractions for Search

SQL Server Management Studio 18.5.1
Database Engine Tuning Advisor v18.5.1

History

The workstation had SQL Server 2014 installed
It had a prior version of SSMS installed
I upgraded to SQL Server 2017
I upgraded SSMS to version 18.1
dta was available, but it's -g (GUI) switch wasn't working.  It complained about an expired or missing license.
A license error is what I've seen when SSMS is updated but the prior version of SSMS is invoked, very similar to how the "dta" tool was behaving
Under the hypothesis that the upgrade had not gone well and my dta was associated with a prior version of SQL server, I decided to start from a fresh install
I uninstalled anything associated with SQL Server 2014, SQL Server
2017, and SSMS.
I did a fresh install of SQL Server 2017
I installed all updates for SQL Server 2017
I installed SSMS 18.1
I found that the dta command was no longer present.


texts: Restore a database backup on another server without sysadmin account

texts:
I need to restore my own database backup on another server.

On both servers I have the same user account name with limited privileges (eg WebUser).

On the target server I can't use a sysadmin account, so how can I restore my backup and give access to limited user account WebUser?

Of course I can't use SQL Server Management Studio but only code in my app.

I've tried with an account with this server role

[serveradmin]
[diskadmin]
[dbcreator]

I can restore the backup but can't access it nor give permissions on the database.


texts: How to get notices to the output stream?

texts:
I have debugging messages in functions. Those message are raised like

RAISE NOTICE 'Value of id : %', id;

I set my log file with \o messages.txt

Then I do what I need to do with \i process.sql

And when the execution is terminated, \o.

The problem is that I don't have the messages raised by notices into messages.txt. The messages are displayed on the screen but I want them to be written in messages.txt

How could I do that ?

I tried to use RAISE LOG... and messages are written in log file... It's not what I want.

I have a work around with

plsql -f /path/to/process.sql > messages.txt 2>&1

but I would like to know how I could use \i and \o on plsql client having the messages into the file specified in \o

My client, on cygwin is psql (PostgreSQL) 8.2.11 and the server version is 9.0.7


texts: is there a GUI client that can execute multiple queries and show its multiple results?

texts:
I'm currently using pgadmin and it appears that it is not possible to send more than one query and retrieve multiple data sets back.

For example, if I try to do this:

select travelerId, name from travelers;

select dateCreated from carDealerships;

I would expect to see TWO sets in my data output window.  As you can see, these two queries have nothing to do with each other so I'm not trying to do anything fancy by using UNION or anything like that.

I really do want to see more than one result set in the data output.  In pgadmin, in the message pane, it shows that the first query result with XX rows from table "travlers" are discarded.  Then it says that the next set of XX rows from table "carDealerships" are retrieved.

Why does pgadmin discard the first query set?

More importantly, can anyone recommend a GUI client that can output multiple data sets based on the number of queries executed?  Doesn't matter if it's open source or proprietary.


texts:
I tried to follow this official documentation to install unix_socket plugin using mysql_plugin utility but getting an error that

  Cannot read plugin config file unix_socket. Bad format in plugin
  configuration file.

FROM alpine:edge

RUN set -ex \
&& apk add mariadb mariadb-client \
&& mysql_install_db --user=mysql --skip-name-resolve \
&& echo 'auth_socket' >> /usr/lib/mariadb/plugin/unix_socket.ini  \
&& mysql_plugin --basedir=/usr --datadir=/var/lib/mysql --plugin_dir=/usr/lib/mariadb/plugin unix_socket ENABLE

CMD ["mysqld"]

I cross checked the ini file.

cat /usr/lib/mariadb/plugin/unix_socket.ini
auth_socket