4
votes
4answers
1k views

Where to look for a bottleneck after elimitating CPU, memory, and disk

I have a database job that runs each night to create a warehouse table. Using my new database server and SAN I have gotten the process down from 4 1/2 hours to 35 minutes. I have optimized it to run ...
10
votes
2answers
19k views

Why “Begin Transaction” before “Insert Query” locks the entire table?

I am using SQL Server 2005 Express. In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire ...
10
votes
2answers
4k views

Load balancer versus connection pooling — is there a difference?

I am working on a project that is expected to serve millions of users shortly after launch. The database is postgres, and for now I am assuming at least two servers will be needed. A sys admin (who is ...
5
votes
2answers
4k views

Running multiple instances on the same host

I need to set up multiple MySQL instances on the same host for one of our customers. As far as I see I could go with: mysqld_multi MySQL Sandbox This should be a long term solution for an ...
23
votes
5answers
114k views

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...
6
votes
2answers
4k views

How do I clear a table from SQL Server's cache?

I have a few tables in my database that should not be cached. How do I tell SQL Server not to cache a table's pages or how do I flush a single table from the cache? Flushing all the cache is not an ...
2
votes
3answers
18k views

How to get table columns with datatypes (and lengths) using PowerShell?

I'm trying to get column names with data types (and lengths) for SQL Server instance using PowerShell. I got this far: #Load PSSnapin Add-PSSnapin *SQL* #Get column names $colNames = dir ...
4
votes
1answer
4k views

Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL

I wanted to capture only status/result of Slave_IO_Running and Slave_SQL_Running column values, which I will be using to integrate into our application. Is there any way to get only the values of ...
4
votes
1answer
5k views

Consistent logical backup of databases that use MyISAM and InnoDB engines

I have a question regarding the logical backup of MySQL databases that use both MyISAM and InnoDB. The mysqldump utility supports these two options: --single-transaction - Creates a consistent ...
4
votes
1answer
83 views

Internals of Windows-to-Windows SQL Server data transfer

This is a sort of generic question and I don't require a very specific answer, but let me ask it by describing a scenario. You connect to a remote db in SQL Server Studio 08, and run a select * ...
3
votes
5answers
5k views

MySQL Performance Monitoring

I already have system availability monitoring for all my MySQL instances in Nagios. I'm looking to get some trending monitoring going for activity. It'd be nice to see how all the information_schema ...
1
vote
2answers
3k views

I cannot access local database on terminal (MAC)

I have access to my databases on phpmyadmin and mysql Workbench on my local computer but I have a problem when I try to access the server using Terminal. Originally, I had uploaded the databases on ...
4
votes
1answer
251 views

SQL Server transactional replication - behaviour of a MERGE command on the subscriber(s) vs. publisher

Background I have two instances of SQL server running on two different machines: One is a publisher, the other is a subscriber for a single (large) database. The replication type is transactional. ...
5
votes
1answer
753 views

SQL Server 2008 Maintenance Plan Failure

Instead of using my old school index rebuild script I had decided to use 2008's built-in Maintenance Plan to do a simple Rebuild Index task. The details are: Connection = Local server connection ...
2
votes
1answer
2k views

Automated UML2 Class Diagram generation from SQL?

How can I generate a class diagram from an SQL database or script? I'll add the functions and set whether the attributes are public, private or virtual once the initial diagram is generated. I've ...
3
votes
3answers
405 views

Same tables with same data, different SQL Server stored procedure performance

I have 2 SQL Server 2008 instances installed on the same server. They have same table structures with same data. I have a stored procedure deployed on both of them. The stored procedure performs ...
4
votes
5answers
2k views

Modifying table structure within a transaction?

In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction. We have a tool (written in Java) that should modify a schema by: adding ...
3
votes
3answers
6k views

Login Problem as SYSDBA ORA-01031

I have the following problem: I can connect to an Orcale database as normal user or as sysdba: sqlplus myuser/myPw@myDB sqlplus "sys/sysPw@myDB as sysdba" But when another (Windows-User) is logged ...
2
votes
2answers
258 views

How to compare the database of a backup from another environment

Has anyone had any luck with taking a backup (approx 30 tables) from an Oracle database's live environment, then loading a test environment with this backup and doing a comparison between the test ...
6
votes
5answers
116k views

How to show the column names of a table with a simple SQL query?

The case is simple: You have a MySQL database where you have only an SQL query interface and you want to know the database structure with queries. You can list tables with show tables; command, but ...
4
votes
3answers
608 views

Should I use MongoDB or a NoSQL solution at all for this?

I apologize if this belongs in web applications or something. I'm creating a web application that is sort of a similar concept to github. Basically I plan to allow someone to create a design of ...
17
votes
3answers
16k views

Is it possible to restore sql-server bak and shrink the log at the same time?

We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it ...
9
votes
5answers
6k views

Parallelism Best Practices

What are the best practices with setting parallelism in general? I know that SQL Server defaults to 0 to use all available processors, but in what instance would you want to change this default ...
3
votes
1answer
239 views

How do I configure a MySQL Cluster with a redundant mysqld host?

this is my first post here, so go easy on me. I'm trying to rebuild my data architecture (which is currently a set of one primary MySQL server and a redundant fallback; the tables are primarily ...
4
votes
1answer
3k views

Is there a way to force sp_send_dbmail to use ANSI, or to not include the Unicode prefix?

This is pretty trivial, but if someone has the answer handy, I'd appreciate it. I use the msdb..sp_send_dbmail sproc as a lightweight way to distribute simple reports. This often entails producing a ...
2
votes
2answers
3k views

Query data from multiple linked remote databases

Using SQL Server 2005 databases and SQL Server Management Studio 2008, I need to query a single value from each of n linked databases at a regular interval. The servers are physically remote and I can ...
5
votes
2answers
78 views

Using stored procedures

I have a task which receives an input and performs a series of tasks on the SQL server, which some depend on the content of the server and on the input. For that I use a lot of selects and then logic ...
3
votes
1answer
1k views

SQL 2000 Maintenance Scripts

Hi I've stumbled into the administration of a Legacy SQL 2000 server that has had next to no maintenance for the past 8 years (and is very slow). As I have only been doing DBA / PT for the past 2 ...
2
votes
1answer
159 views

Merge Replication between two Embedded Databases?

I have an application that uses SQL Server CE 3.5. I am trying to expand the database to have a remote database accessible from various locations. I will need both the local (which might or might ...
8
votes
6answers
4k views

How do you submit a List of items to a Stored Procedure to limit results? [duplicate]

Possible Duplicate: Passing array parameters to a stored procedure Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read: WHERE ...
3
votes
1answer
2k views

Restore dropped mysql database

I create a database each year from the previous year, back up and then clean up the tables. So I have databases for the past 3 years. This year I did not do a backup and accidentally dropped the ...
2
votes
4answers
524 views

MySQL Database Poisoning: How to recover to a known state quickly

If you want to avoid database poisoning (ie want to recover back to a point-in-time quickly) which methodology do you prefer? Let me define data poisoning. You insert some things in your database ...
4
votes
1answer
35k views

How to Recover an InnoDB table whose files were moved around

So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for ...
3
votes
1answer
473 views

How can I alter the output for the sql desc command?

When I do desc table, I get back the columns Name, Null? and Type. What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique. I realize that this is a ...
2
votes
1answer
625 views

MySQL proxy and SHOW PROCESSLIST query time

I have MySQL replication currently occurring between two masters bidirectionally, one in a data center locally, another over at Amazon EC2. Everything seems to function normally replication wise, no ...
9
votes
2answers
2k views

How does using separate schemas affect SQL Server 2008 performance?

I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
8
votes
4answers
358 views

Is this a standard way to design a database?

I recently learned about how relationships are defined in the database at work, and was wondering if this is a standard practice. Let's say we have two processes: Process A, and Process B. Process B ...
0
votes
1answer
808 views

What network access do I need when I restoring files using RMAN?

I am restoring files from one server to another server using Oracle RMAN, what network accesses do I need?(between these servers) ftp? port 1521 should be open? or what? I appreciate your answer ...
0
votes
1answer
43 views

Should I record ID numbers in a table where I record who look whom profile page

This is how I have designed: visitorID visitedprofileID datetime Is it a good idea to add an ID (primary key) to the table, I mean recordID (primary key). (MySQL with PHP)
10
votes
3answers
1k views

SQL Server 2008/R2 recovery model

Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the ...
1
vote
1answer
179 views

Oracle Backup/Recovery architecture

When we write the following: allocate channel ch1_tape type sbt; set controlfile autobackup format for device type sbt to '%F'; restore controlfile from autobackup; RELEASE CHANNEL ch1_tape; By ...
6
votes
4answers
5k views

What should I ask at a MySQL DBA interview?

I am a software developer and am helping my team hire a MySQL DBA. The core challenges that we are facing are: Slower queries and performance due to Hibernate. Database management (backups, tuning, ...
3
votes
3answers
339 views

How to send+update on duplicate a big time series to SQL server

I use a SQL Server 2008 I would like to send (and update on duplicate datetime index) a big time series (more than 1000 datapoints and eventually much more than that). What is the best way to do so? I ...
2
votes
1answer
3k views

“Linked server does not contain table” error when using VFPOLEDB provider linked server

I am receiving the below error when attempting to run any queries via a linked server in SQL Server 2008 R2 using the Visual Fox Prox OLE DB Provider v9.0.0.3504. I am attempting this as I need to ...
5
votes
1answer
2k views

sqlcmd to run large SQL file causes high CPU usage

I am using sqlcmd.exe to run a large SQL script, and the server CPU usage has been at 100% for the past 5 hours. The SQL script file has a size of 156 MB. I am running the script on SQL Server 2005. ...
2
votes
3answers
7k views

sql server 2008 r2 mirroring

I am trying to set up mirroring between two servers behind the same firewall on the same subnet. I backed up mainserver and restored to mirrorserver. I'm using the wizard on "mainserver" and ...
5
votes
1answer
474 views

Design for file based with many events

I'm not a database guy but I need to design a database to store events for our application. Our client app connects to remote hardware and syncs count data. The count data are csv files split into ...
4
votes
2answers
891 views

Add constraint to ensure flag column is <= sum of column in other table

I have a column RSEFlag that contains a flag value that is the sum of one or more of 1, 2, 4. I have separate table that contains the description of each of those flag values: RSECode RSEDescription ...
3
votes
3answers
1k views

Convert Oracle query to MySQL

I've just tried to convert this update command, which is Oracle sql, to MySQL with SQLWays and it failed with the following error: You can't specify target table 'animal_table' for update in FROM ...
6
votes
1answer
1k views

Mirroring problems after removing domain

I successfully created a mirror with certificates between two servers. One of those servers was a domain controller. After removing the domain the mirror was interrupted. After hours of searching I ...

15 30 50 per page