2
votes
2answers
2k views

MySQL general log

Is there away to have MySQL dump its log to a remote MySQL server? I would like to audit MySQL connections but I don't want the server to store all the information local on its own hardware.
3
votes
1answer
306 views

SQL Server: Duplicate Index Question

We recently has a massive performance problem on our system, which we narrowed down to a problem with I/O on the LUN. IOPS increased twenty-fold during the period when the problem was occuring. The ...
2
votes
1answer
46 views

Changing the SQL Server Compatibility setting for individual tables/columns, which have retained older settings

Is there a clear and methodological method to change the compatibility mode of a databases individual Tables & Columns? A 3rd party database was upgraded and migrated to SQL 2005, the SQL Server ...
1
vote
1answer
80 views

User login error when trying to access secured SQL Server database

We have a username that was recently renamed from one username to another (think getting married). The Active Directory admin renamed the user because "it has always worked in the past". One vendor ...
0
votes
1answer
366 views

Differences between CMS and MSX/TSX?

What is the differences between Central Management Servers and Master Server (MSX) / Targer Server (TSX) concepts in SQL Server? I know that MSX/TSX is related to SQL Agent and the basic idea is to ...
12
votes
3answers
3k views

Is there a reason to update statistics manually?

In SQL Server, statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?
15
votes
2answers
4k views

Why is IDENTITY_INSERT ON only allowed on one table at a time?

It is the case that IDENTITY_INSERT can only be set to ON in one database table at a time, but why? Since IDENTITY columns aren't globally unique I can't think of any dangerous situation that could be ...
16
votes
1answer
8k views

Should I use many single field indexes, instead of specific multi column indexes?

This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection". I'm working with an existing SQL Server (2008) application that has a number ...
2
votes
2answers
3k views

Single Identity column and composite key, which to make primary?

I've got a table that's a working copy subset of a base table. The base table has a composite key with a clustered index. However I need a single column unique identifier for my framework to work ...
3
votes
2answers
497 views

Performance differences between executing via Linked Server vs. SSMS

We've had an issue reported where a stored procedure was behaving badly when called from some .Net code. On the face of it, that sounds similar to this case, which turned out to be due to EDMX, but in ...
1
vote
1answer
976 views

SQL Server sp_send_dbmail Query Error

EXEC msdb.dbo.sp_send_dbmail @recipients= 'test@test.com', @copy_recipients = 'test@test.com', @query = 'SELECT * FROM INSERTED', @attach_query_result_as_file = 1, @subject = 'Auto-Generated Deleted ...
7
votes
1answer
227 views

What is the expected performance impact when dropping a large table

I have a 100G+ innodb table that needs to be dropped in a production environment. I am trying to do this without interruption to service. The table is no longer being written to, and needs to be ...
1
vote
1answer
220 views

Randomly generated password for mysql admin over time?

I have this idea of making my database's Admin account secured by creating the following: Trigger is activated every hour in mysql this trigger activates a stored procedure Stored procedure ...
3
votes
1answer
567 views

Dropped database still shows but causes errors PostgreSQL

I had dropped a database a couple weeks ago but it still shows with \l. I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was ...
1
vote
2answers
6k views

What would be the fastest approach to bulk update this mysql table (Innodb)?

I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up? I am using following approach: mysql -uroot < query_file where query_file contains 1M ...
1
vote
1answer
213 views

postgresql - transaction log - versioning?

Is it possible to "version" your database so that any change can be reviewed or roll backed? Obviously backups are important but being able to go back to any previous state of the database (at least ...
4
votes
3answers
249 views

how to copy data from SQL Server table to excel Spreadsheet thru Linked Servers

I try to copy data from a table in SQL Server 2005 into a excel spreadsheet. Since that sheet is linked to some external charts, so I have to overwrite the existing data with the updated data from SQL ...
4
votes
1answer
2k views

Problems opening standard reports

I am using MS Sql Server Mgt Studio. I select a database. I right click and select Report / Standard Reports / Index Usage Statistics I get Error: Incorrect syntax near '(' I check some of the ...
0
votes
1answer
593 views

Some Ideas In Database Design

I am creating a site where users can sell their products(let say a little version of eBay, just assumption). Concerning the database design am a little bit stuck and looking for some ideas how it is ...
1
vote
3answers
95 views

Need to figure out what FKs point to a specific table

In SQLServer 2008 r2. I need to figure for a specific table what FK's in other tables point to it. I try: SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS But this does not give me what I ...
2
votes
2answers
447 views

Looking for a way of moving millions of record without impacting the transaction log

I need to move around 10 million records out of a table into a new "optimized" table, but I need to keep the database online. What would be the best method in doing this without impacting the ...
8
votes
4answers
13k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
1
vote
1answer
327 views

Why are Oracle processes only using half the SGA set?

I have an Oracle set up on a remote server that's maintained by a third party. Recently it became corrupt and we replaced it with a new instance. Since then it's been running slower when performing ...
2
votes
2answers
3k views

When to create multiple-column index?

When to create multiple-column index in Mysql? Should I measure the slow queries and create multiple-column index for them?
25
votes
3answers
27k views

Is it safe to use innodb_flush_log_at_trx_commit = 2

I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
2
votes
1answer
61 views

Any suggestions on having master-master replications around the world by nosql solutions?

I would like to set up databases that are put at different data centres around the world. For this, I think some nosql solutions may suit the case. For example, couchdb, maybe. Eventual consistency is ...
5
votes
1answer
1k views

Ways to speed up IN queries under PostgreSQL

I have a query of the following form: SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000) The IN query has anywhere from 10 to 25000 values. The query runs for minutes ...
5
votes
2answers
706 views

Unexpected extremely long query time (~5 minutes using nested WHEN-INs)

For the first time, I am running up against a horrifically long execution time of a MySQL query (~5 minutes). The data in the database is highly (and not arbitrarily) normalized. It very efficient ...
0
votes
2answers
225 views

Which is the best tool to monitor replication on MySQL 5.5.20? [closed]

I have just installed and setup replication for 3 MySQL 5.5.20 server( 1 Master/ 2 slaves). I intend to add more slaves to the equation. Which is the best software tool to monitor the replication ...
10
votes
3answers
3k views

using *tables* as Table-Valued Parameters (TVP)

MS SQL 2008 supports TVP: a useful feature for bulk uploading data to a stored proceedure for processing. Rather than create a user-defined type, is it possible to leverage an existing table ...
3
votes
1answer
136 views

Do operations on indexed views happen in parallel?

When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel. Is ...
5
votes
1answer
1k views

How does objectproperty() figure out if a function is deterministic in sql server?

How does sql server determine if a function is deterministic or not? Consider the following function, I think it's deterministic (as it's ultimately an integer operation), but sql server returns 0 ...
2
votes
1answer
458 views

Is there a way to view the body / contents of a system function in sql server 2008

For the sake of leaning and exploring, I'd like to look at how some of the system functions are built. Is there a way to do this in sql server 2008 or R2? I've tried sp_helptext 'Sum' but I get an ...
6
votes
2answers
1k views

What's a good relational structure for units and complex unit conversions?

My company is in the Energy industry and I need to come up with a good way to represent the conversion of units of measurement. I've done some searching and have yet to find a good article covering ...
4
votes
1answer
13k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
1
vote
1answer
149 views

Two MySQL query results are different, but shouldn't be

To the best of my knowledge, these results (especially the data in the 'destination' field) should be the same, but I can't find out why they're different: mysql> select * from cc_did_destination ...
2
votes
2answers
541 views

Is it possible to view the maintenance plans created in SQL Server 2000 in SSMS 2005?

Is it possible to view the maintenance plans created in SQL Server 2000 (also running on the same SQL server 2000) in SQL 2005 Server Manangement Studio? I have the server added in my SSMS, but when I ...
2
votes
2answers
1k views

How to specify trigger execution order under PostgreSQL?

I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table. CREATE TABLE twitter_interactions(...); CREATE OR REPLACE ...
10
votes
4answers
46k views

Easily show rows that are different between two tables or queries

Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the ...
1
vote
1answer
262 views

Do you feel like DBA/DBD Is a “secure Field” still? [closed]

I noticed recently alot of Database jobs/Development have kind been pushed onto general developers, not to mention with NoSQL Solutions (Which IMO are probably a little easier for Non-DBA's to throw ...
1
vote
0answers
338 views

Update excel sheets from SQL Server 2005 linked servers

I use SQL Server linked servers to connect to Excel 2003 multiple tabs workbook. After connected, I have not problem to use select statement like: select * FROM OPENQUERY(TEST, 'select * from [fist ...
8
votes
2answers
20k views

How to cast an integer to a boolean in a MySQL SELECT clause?

I'm new here so be kind to me. I have the following scenario: I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value ...
2
votes
2answers
271 views

SQL Server 2008R2 Ent + Win2003 Ent R2 32bit will not use all available memory

Server: HP ProLiant DL380 G5 2 proc -2x QuadCore Intel Xeon E5450, 3000 MHz Memory- 12288 Мб (DDR2-667 Fully Buffered ECC DDR2 SDRAM) Settings: /3GB in the boot.ini. /PAE in the boot.ini. ...
4
votes
2answers
1k views

why we can specify table names enclosing them with []

Is there a reason we can specify a tablename with [schema].[tableName] instead of just schema.tablename? is it JUST to allow spaces in a tableName?
4
votes
2answers
1k views

What are the available options to backup DB2 Express-C?

I want to use DB2 Express-C edition with my application. I have never used DB2 but now want to use the Express Edition in a production environment to learn more about it. I want to know what are the ...
5
votes
2answers
901 views

Restrict SQL Server 2008 R2 User to restore only one database (but not others)

It is my understanding that a user has to be a "dbcreator" in order to restore databases. However, what if I want a specific user to restore only one specific database, but not gain access or restore ...
2
votes
2answers
3k views

How to re-create the ##MS_PolicyEventProcessingLogin## principal

I'm getting a bunch of errors in my MS SQL Server logs about a missing ##MS_PolicyEventProcessingLogin## principal. I went to check the master database and yes, it is indeed missing. I don't know how ...
7
votes
1answer
1k views

How to setup local database development process for small web team?

Background I am working on creating a new development process for a small web team of about 4 programmers and 4 designers, with the obvious potential to grow the team in the future. Our product is a ...
2
votes
1answer
546 views

Programatically testing slave status - What fields in SHOW SLAVE STATUS indicated slave status is OK / in ERROR?

We use pingdom and a few internal heartbeat mechanisms on our product. We would like to add a 'are our slaves running and in good shape' check. What fields in SHOW SLAVE STATUS indicate that the ...
8
votes
2answers
682 views

Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting

While writing a query other day a thought came to me and have stuck in my mind. What is preferable, first checking if a value for a unique column exists and then inserting or insert and let db raise ...

15 30 50 per page