2
votes
1answer
810 views

Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields

I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted). Furthermore I'd like to show ...
1
vote
3answers
1k views

Transaction log for SQL Server is too big

I have a database that has a 33 gb transaction log. It is caused by the fact that the database is in full recovery mode has been used for over 5 years prior to the first backup. I want to truncate ...
4
votes
3answers
512 views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
5
votes
1answer
1k views

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. The problem is, the differential backups don't always seem to ...
2
votes
0answers
440 views

How much disk space needed for 100GB of DBs?

Hard to explain in the title, but I'm planning a migration from SQL Server 2000 databases to SQL Server 2008 R2. The databases are under 100GB total space, but this is only with the Simple recovery ...
1
vote
1answer
206 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
0
votes
2answers
3k views

Using a Select in a Where clause for another Select

I have made a draft remote application on the top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functionning of the application. For each final business result that I ...
6
votes
1answer
2k views

Can I retrieve all database objects owned by a particular user?

We have a user who is leaving and I need to know every database object that he owns. Is there a query that will provide this information?
6
votes
1answer
575 views

Is that a good idea to use one database for 50.000+ shops?

I know Shopify use only one database for all shops. But how can they handle their database with such big data? Is that a good idea to use one database for 50.000+ shops?
1
vote
1answer
344 views

Set based solution to group multiple rows within a sequence

Using SQL Server 2008 and a set based solution, how could I manipulate the following data: GroupID Code StartSeq EndSeq StartDayNo EndDayNo 1755549 3506 0 0 59442 ...
3
votes
1answer
946 views

sql error 1241 operand should contain 1 column

This is my query select * from players where sport='football' and position='DEF' and pname!='Binoy Dalal' and pname not in (select ...
8
votes
2answers
3k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
4
votes
1answer
1k views

Oracle extract slow function call from a WHERE clause

I have an oracle table in which dates are stored as the number of minutes since a specific date. It looks something like this: CREATE TABLE MY_TABLE ( SOMETHING NUMBER(15,1) NOT NULL, ...
0
votes
2answers
74 views

What to have in the main table and what to have in meta_table?

I'm try to figure out the best scheme for the users table for my software. I'm trying to separate the main data and auxiliary data into 2 table called users and user_meta for the sake of extensibility ...
3
votes
1answer
207 views

Query optimization with extreme statistic

I have a table: mysql> describe table_1; +---------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default ...
6
votes
1answer
611 views

Warning in query plan “Cardinality Estimate”

create table T(ID int identity primary key) insert into T default values insert into T default values go select cast(ID as varchar(10)) as ID from T where ID = 1 The query above has a warning in ...
0
votes
2answers
2k views

Oracle 10g slow performance on first query [duplicate]

We have an application which we recently upgraded and is having bad performance issues. Any ideas would be much appreciated. The main issue is windows taking ages (minutes) to open because the ...
2
votes
0answers
82 views

Oracle 10g weird behaviour

We have a thick client application using an Oracle 10g database. We recently upgraded the application and in the process migrated the database tables from a latin to unicode character set. After the ...
0
votes
2answers
613 views

Database design of Ledger Account and Balance sheet generation

I am building an application which has deposit and loan accounts management, for which i have to develop ledger accounts and then generate balance sheet. Being new to database design, i am having a ...
3
votes
1answer
165 views

Which MySQL table to use for read-only, bulk-loaded tables?

So I have a workload on 2 key tables in my application with the following characteristics: READ-ONLY DATA The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...
3
votes
1answer
319 views

Query Performance Issue

I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this. Version : Sql Server 2008 R2 Enterprise No fragmentation . ...
1
vote
2answers
486 views

History table design for supertype/subtype

I'm designing an asset management database that tracks IT hardware. I decided to use a supertype/subtype design. I'm at a point where I want to track history of changes for devices. I wanted to use a ...
1
vote
2answers
213 views

What exactly do database passwords protect?

I would like to know what exactly I am password protecting on my server when I set a database password. I am unclear as to what people are referring to when saying to password protect a database. Are ...
2
votes
1answer
3k views

Active/active SQL Server Clustering in Installation Center (setup.exe)

I like to know which option to choose in the setup.exe in SQL Server 2012/2008? What I have here is Windows Active/Active Cluster setup with one quorum and one msdtc configured. I installed my first ...
2
votes
2answers
339 views

Is there a way to get instance_id from msdb.dbo.sysjobhistory during the execution of a SQL Job step?

I'm writing a logging process that will be stored in tables so that the information can be easily referenced and reported. Most of the structure already exists in the system tables for the SQL jobs ...
2
votes
1answer
4k views

invalid object on a table that exists

I am getting an "invalid object" warning error in the Management Studio query. Any query against the table works and it links correctly to another table. I am investigating an error that I'm getting ...
3
votes
2answers
2k views

Query: All employee with sum of salary > 500

In my MYSQL Database COMPANY, I have a Table: Worker : mysql> DESC `Worker`; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
7
votes
2answers
549 views

Increased RAM, Worse Performance

Setup: Windows Server 2008 R2 SQL Server 2008 R2 SP1 240GB RAM TempDB is 8x16GB data files w/out auto-grow (128GB total) Physical/Stand-alone Server This server is used for ETL processing. We ...
4
votes
1answer
70 views

SP for Restoring Database and performing other tasks on it

I am creating a stored procedure for our QA dept that will do the following: Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for ...
0
votes
1answer
508 views

Proper procedure for migrating a MySQL database to another Debian machine?

I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL. I've created a backup of all databases on the first server like so: mysqldump -uuser ...
1
vote
1answer
938 views

Changing InnoDB data directory prevents MySQL service from starting

I have a MySQL instance installed on Windows 7 and I wanted to change the directory of InnoDB tables data. I modified the "my.ini" adding the following line after "[mysqld]": ...
1
vote
2answers
1k views

Differential backups in SQL Server 2008 R2

I created a maintenance plan to backup all user databases (full) and set them to expire after three days. This has proved to be cost inefficient in regards to disk space so I want to switch to ...
2
votes
0answers
415 views

Importing Excel files with SSIS

I'm trying to import an Excel spreadsheet into SQL Server 2012 using SSIS. There is only one sheet in the file. The file is a SalesForce export. I believe the Sheet name changes depending on when the ...
4
votes
1answer
515 views

Enable TDE for existing AlwaysOn databases

I need to do a little benchmarking of AlwaysOn, both with and without TDE enabled. All the instructions I see cover adding encrypted databases to an availability group, but I see no mention of ...
3
votes
3answers
141 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million). We're ...
2
votes
2answers
530 views

what is relation between sql server back file size and space required for restoring database

I am using sql server 2008. I have taken full backup of my database. Its backup file file (.bak) is around 750 MB. Sql Server is installed on c drive which contains 5 GB free space. When i try to ...
1
vote
2answers
1k views

How to add 'root' MySQL user back on MAMP?

On PhpMyAdmin, I removed 'root' user by mistake. I was also logged in as 'root'. How can I add the user 'root' back, on MAMP?
0
votes
0answers
121 views

XML errors while copying dependents during a redefinition to an interval partitioning scheme

I am partitioning an existing table. I used the following process: Checks DBMS_REDEFINITION.CAN_REDEF_TABLE(...); Successful Create duplicate table CREATE TABLE "USER"."MYTABLE_ONLINE" ( ...
0
votes
2answers
132 views

looping through a table type variable

I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value. CREATE OR REPLACE FUNCTION ...
9
votes
3answers
234 views

How to tackle a large undocumented database

I recently got hired as the only IT Guy in a certain Company X and I am tasked to fix their applications, and in my opinion, the best way to start is by understanding the database. Their current ...
2
votes
1answer
58 views

Query difference with where condition

I need to find the ids of all rows in a table A without a matching row on table B. Following the answer on this question I'm using a left join like this: select A.id from A left join B on B.id_A = ...
4
votes
1answer
666 views

Why can't I read my transaction log backup file using fn_dump_dblog?

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery. After backing up the transaction log, I attempt to read it ...
4
votes
2answers
2k views

Splitting TempDB into multiples files equal to number of CPUs

The article SQL Server tempdb Best Practices Increase Performance suggests that I should split tempdb into a number of files equal to the number of cores. So for 4 cores you get 4 files. By ...
0
votes
1answer
4k views

Connect Error (2054) - mysql_old_password issue still not solved

i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way: $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); ...
1
vote
2answers
373 views

Should a SQL Server TempDB space be allowed to be reclaimed?

If I don't have space concerns at the moment, should I size my TempDB as a large space initially and not let it's space be reclaimed? What's the best way determine what the optimal initial size for ...
6
votes
2answers
449 views

How should I configure these disks on a SQL Server for a BI configuration?

Assuming constant memory (32gb) and CPU (4), 2 x disk arrays, I have the following disks 2 x 150 (10k) 6 x 150 (15k) They are all local disks. My requirements My DB is 350gb and set to default ...
2
votes
1answer
4k views

How to list all users that have grants over a package

I'm looking for some query to list all users that have grants over a package. For example, user Schema_A has been granted to execute package: B.MyPackage. I'm querying views or tables like: ...
2
votes
1answer
979 views

Select multiple columns in subquery

I am having trouble selecting all cloumns in a sub query and can't seem to get no where with joining for replacement. http://sqlfiddle.com/#!2/2481e/1 The link is to the schema with my current ...
0
votes
1answer
609 views

consequences of using “innodb_flush_method = O_DIRECT” without having a battery backed write cache? or on a KVM guest?

Mysql 5.5.29 Innodb- 128GB Ram - 32 cores - Raid 10 SSD. Our server which is a dedicated KVM guest on a 'baremetal' is hosting our heavy read-write DB server. Everything is file-per-table. ...
4
votes
1answer
69 views

does a subquery pull the entire table?

i am attempting to run a subquery in a table: SELECT t1.*, t2.* FROM t1 LEFT JOIN (SELECT * FROM table2 GROUP BY col1) AS t2 on t1.col1 = t2.col1 WHERE ... My main question is, since that subquery ...

15 30 50 per page