1
vote
0answers
541 views

rman 'delete archivelog all’ very slow

On our automated test system we use flashback all the time to reset our Oracle 11g database to a state before a test run. We noticed some of the scripts involved, started to become slower and slower. ...
6
votes
6answers
1k views

Find procedures that haven't been called in <n> days

We are deleting old stored procedures and tables. How can I know what procedures haven't been called recently? dm_exec_procedure_stats and dm_exec_query_stats aren't reliable, since they only return ...
4
votes
1answer
764 views

Cannot Delete FileStream Group from Sql Server 2008

We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when ...
0
votes
2answers
95 views

Move database files if database runs

is possible, is there internal procedure to moving with database files if database runs, is not detached on Sql Server 2012/2008, .... EDIT.... database is online, accesible for users, services ...
-1
votes
0answers
58 views

Optimize SQL Select Command [closed]

Is there anyway to speed this up by optimizing/condensing the code? SELECT fullName, email_address, pos_desc, EXT FROM dbo.BTC_WEB_EMPLOYEE_VIEW WHERE grp_cde = 'STAFF' OR grp_cde = 'PROF' OR ...
3
votes
1answer
600 views

Are there tips for writing Oracle DDL Triggers for auditing? Or should I just use AUDIT?

I've been tinkering with writing a trigger to log my table creation and manipulation for later tracking and review. The challenge is that my trigger logic is stored in the same schema on which I've ...
1
vote
1answer
124 views

Force View's query plan to update?

I have a View whose query plan appears to be cached, is there a way to force the View's plan to be recalculated on each access?
4
votes
2answers
2k views

What is the best way to migrate multiple databases?

I want to migrate my database server to a new server. Right now I have a database server with Windows Server 2008 and now I am migrating to a new, separate server with Windows Server 2012. There are ...
1
vote
1answer
333 views

Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

Environment: Ubuntu 12.04 (and 13.04) MySQL 5.6.11 I have a table which has a full text index on it (real table has much more columns and rows): DROP TABLE IF EXISTS articles; CREATE TABLE ...
7
votes
2answers
28k views

Can I install SQL Server 2012 on a machine with 2008 R2 already?

I have been using SQL Server 2008 R2 for quite a while, and it is working great. Now I need to use SQL Server 2012. Will this effect my existing databases? How much risk is involved in installing SQL ...
0
votes
0answers
137 views

Synchronization between master and backup server

I am searching for an option to synchronize data between production server and backup server automatically. Does somebody know a method, how I can do this: if I delete, insert or update a record it ...
2
votes
2answers
252 views

Can I restore an uncompressed differential backup after a compressed full backup?

If we enable compression on database backups (because the database backup size is huge) but don't enable it on differential which are relatively small in size, will a restore work? Because the full ...
4
votes
1answer
3k views

Import Data replaces NULL with 0

I am using sql server 2008 r2 express. I am getting some problems when i use import data feature to import data from one database to another. I am using SSMS to do it. While using the wizard i choose "...
4
votes
3answers
3k views

MYSQL wont use index in “IN(SELECT ..)” sub clause

I have two tables in MYSQL with target fields set as index / primary index already. Then I ran the below sql query. EXPLAIN SELECT charge FROM ec_provider_account_option WHERE ...
2
votes
3answers
2k views

Is it worth to separate columns into multiple tables for one-to-one relational table

I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table. ...
1
vote
1answer
520 views

how to rebuild / reinstall ssrs (reportserver, reportservertempdb) databases?

Our server crashed. We got it back up and running however, the mentioned databases have been corrupted. Is there a programmatic / automatic way of rebuilding or reinstalling the SSRS databases? If ...
0
votes
1answer
178 views

Select unique value whereas the time is highest in the most optimal way

Given a simple, with a text and time column, I want to select X unique values from the text column, whereas that row contains the highest value for time. INSERT INTO `test` (`id`, `text`, `time`) ...
1
vote
1answer
6k views

How to run a SELECT query within while loop in PHP?

Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as $result1 = $mysqli->query("SELECT * FROM table1"); while ( $row = $result->fetch_assoc() ) { if ( $row['X'] == '...
4
votes
4answers
1k views

In Memory Database for high transaction and volume spikes on a website [closed]

I am looking at the IA for a hypothetical application scenario, for example: A high demand application such as an online ticketing website where there can be demand for 100,000 tickets to be ...
5
votes
2answers
723 views

If I update a column record in a table, will indexes that do NOT have this column in it be affected?

In terms of performance if I have a table like so: CREATE TABLE [TESTDATA].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [float] NULL CONSTRAINT [TableA_PK] PRIMARY ...
1
vote
1answer
9k views

Using wm_concat to concatenate rows, but in an order determined by another column

Let's say I have 3 columns: p_id, description, order_by. I am trying to do the following: I would like to concatenate the description for all like p_id values. So we are talking a group by p_id ...
1
vote
0answers
367 views

Firebird database performance after server upgrade/restart

Got a 350 GB database (more than 40M records plus 0 - 1000 BLOBs for each record in another table). After upgrading Firebird to version 2.1.5 (mainly because of filesystem cache issue) database became ...
4
votes
1answer
3k views

SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

SQL Server 2008R2 PowerShell 2.1 I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I ...
0
votes
1answer
858 views

Database replication using wamp?

I have created a POS system for our corporation, in the HQ we have a wamp server with the main database, we also have more than 25 branches across the country. I will setup a wamp server on each ...
22
votes
5answers
19k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
2
votes
2answers
81 views

Postgresql constrains on FK

I am trying to design a (part of a) database which has to accomplish the following: There is a students table, containing a bunch of students. There are educations in the database. Each student can ...
8
votes
2answers
32k views

How can I reset a mysql table auto-increment to 1 in phpMyAdmin?

I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this: ALTER TABLE tablename AUTO_INCREMENT = 1 I am curious if there is a way to do this from within ...
1
vote
2answers
125 views

interpreting how much data has been lost by analyzing dbcc output

We had a SAN outage and this corrupted several databases with no backups. We ran this to do a repair: EXEC sp_resetstatus 'test' ALTER DATABASE test SET EMERGENCY DBCC CheckDB ('test') ALTER ...
2
votes
1answer
1k views

Convert Oracle database to Derby

I need to migrate an existing Oracle Database into a Derby one. I want to know if there's a tool, a script or another way to do that work. It is using any of the interesting features of Oracle, as I ...
1
vote
1answer
12k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
5
votes
4answers
18k views

How to block user access to perform database application update?

Where I work we have a SQL Server database (Microsoft SQL Server 2008 R2) that serves as the back-end with two different user interfaces, a .NET web interface and a FoxPro interface. Every month we ...
0
votes
1answer
205 views

What structure should be used to store data with multiple category identifiers?

I have a table within a MySQL database with various data in it (for the sake of example, let's give it the following: ID | Place | Phone | Email | Category ) Now, I want to redesign ...
5
votes
2answers
39k views

How to grant all privileges on all tables in a schema to a user in IBM DB2?

At first place, I want to export database from IBM DB2 AIX into IBM DB2 windows. Unfortunately, I can't use BACKUP and RESTORE command because of difference OS issue. So I have to resort to db2move ...
2
votes
1answer
935 views

Create a sub query for sum data as a new column in SQL Server

Suppose that I have a table named tblTemp which has this data: | ID | AMOUNT | ---------------- | 1 | 10 | | 1-1 | 20 | | 1-2 | 30 | | 1-3 | 40 | | 2 | 50 | | 3 | 60 | ...
0
votes
1answer
3k views

Cannot Connect to Oracle Database running on Windows 8 Hyper V Virtual Machine

I have oracle server (standard edition) installed on a guest VM (Windows 8 Hyper V). I have mapped a host entry to the VM as "brettvm". I am able to log into the VM and connect to the default ...
0
votes
4answers
269 views

SQL Server transaction log growing without control

We have a database growing uncontrolled and filling the hard drive every 2 days. Database maximum log size is set to 6 gb but id doesn't seem to be working because it continues growing beyond 6 gb. ...
5
votes
2answers
226 views

Indexing strategy for dynamic predicate

Assume SQL Server 2012 Standard edition. My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints). The ...
2
votes
1answer
58 views

Strategies for summary data in parent record based on detail data?

I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all ...
3
votes
1answer
1k views

Index on foreign key makes query extremely slow

We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem. The queried table (table3) has an indexed PK, three FK with indexes ...
23
votes
6answers
3k views

Why don't databases create their own indexes automatically?

I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly ...
7
votes
3answers
3k views

How should I best handle a rapidly growing database?

I have a database that I need to maintain. Sadly, the setup and use of that database I can't change, much (thanks to some internal politics). It's running on SQL Server 2008r2. Its only been live ...
1
vote
2answers
1k views

Increasing timeout period for schema compare

I need to increase the timeout period for schema comparison. I'm using Visual Studio 2012 to compare two SQL Server 2008 R2 databases on Windows Server 2008. I believe this is something I need to ...
1
vote
1answer
114 views

Stored Procedure Not working as Expected

When I create the stored procedure without parameter it works as expected, but when I create it with parameter it doesn't return any row. DELIMITER // CREATE PROCEDURE search() BEGIN SELECT * FROM ...
0
votes
1answer
78 views

Need an idea for a complex SQL question

I have had my cup of coffee, but cannot come up with a good solution for the following problem - so I am here to pick your brain. ;-) Here is what I am trying to accomplish (for PostgreSQL): Got a ...
4
votes
2answers
13k views

SQL SERVER - Linked Server and query performance

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =). I have a database K with 3 linked servers L1,L2,...
1
vote
1answer
103 views

Sql Server Server Group query

Scenario I have 4 front-end sql server. All of them have the same db instances. Data are replicated from back-end databases. Issue In SQL Management Studio I have registered the servers and I have ...
2
votes
4answers
296 views

What happens when I try to store more 8019 bytes in a SQL Server table row?

I read - total fixed and variable length data are still limited to 8019 bytes total But my data can possibly be more than this. What happens if it's more. Does it still store the data correctly? ...
3
votes
1answer
152 views

Increment of integer PK values by 1 doesn't throw duplicate key error. Why?

As stated in the title I am wondering why the following piece of code will note fail with duplicate key error. As a result of the following test, obviously, the update does not check for duplicity ...
0
votes
4answers
2k views

How do I backup a database from MySQL 32-bit and restore it to MySQL 64-bit?

I'm currently using MySQL version 5.0.16, on my local computer (which runs a 32-bit OS), for easier access while developing my program. Now that I'm ready to deploy my program, I'd like to test the ...
2
votes
2answers
2k views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...

15 30 50 per page