Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
39
votes
2 answers

How do you stop, take offline, and delete a SQL Server database in recovery?

I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so. I need to either stop the recovery, or kill it in some way. I don't care one bit about the database or any of the data, I have…
CrapHands
39
votes
2 answers

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 options: timestamp NOT NULL DEFAULT (now() AT TIME ZONE…
Bam
  • 569
  • 1
  • 5
  • 10
39
votes
1 answer

Why is my PostgreSQL ORDER BY case-insensitive?

I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior: veure_test=# show LC_COLLATE; lc_collate ------------- en_US.UTF-8 (1 row) veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY…
Curtis Poe
  • 505
  • 1
  • 4
  • 6
39
votes
3 answers

Warning for missing statistics in execution plan

I have a situation that I can't understand. My SQL Server execution plan tells me that I have missing statistics on the table, but the statistics are already created: But if we look at the table, we will see that there is a statistic that has been…
Artashes Khachatryan
  • 1,461
  • 1
  • 12
  • 23
38
votes
3 answers

Is a Postgres long-running query aborted if the connection is lost/broken?

If I open a connection to Postgres and issue a long-running query, and then break the connection (e.g., kill the client process that opened the connection), will the long-running query continue to run, or will it be automatically aborted? Is this…
Rob Bednark
  • 2,163
  • 5
  • 21
  • 22
38
votes
2 answers

Oracle: Quick way to list all database links

The title says it all, is there a way to quickly list all the current database links in oracle? Something along the same lines as this, which lists the current user's tables: select * from user_tables; Thanks, GC.
Clarkey
  • 1,063
  • 3
  • 12
  • 19
38
votes
5 answers

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) the newbb_post table to a new table called…
jollyroger
  • 563
  • 1
  • 4
  • 9
38
votes
5 answers

In PostgreSQL, is there a type-safe first() aggregate function?

I'm looking for a first() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement) RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $1; $$; -- And then wrap…
Alexandre Neto
  • 557
  • 2
  • 5
  • 11
38
votes
1 answer

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some performance issues. What is a good way to determine if…
Juan Carlos Coto
  • 1,558
  • 4
  • 18
  • 25
38
votes
6 answers

The smallest backup possible ... with SQL Server

Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever. We don't mind if our backup process takes a bit longer; as it stands we need to move 30gigs of compressed backup across…
Sam Saffron
  • 1,104
  • 1
  • 10
  • 12
38
votes
2 answers

DELETE vs TRUNCATE

I am trying to get a greater understanding on the differences between the DELETE and TRUNCATE commands. My understanding of the internals goes something along the lines of: DELETE -> the database engine finds and removes the row from the relevant…
Stuart Blackler
  • 4,542
  • 7
  • 29
  • 43
38
votes
3 answers

How do I list all tables in all schemas owned by the current user in Postgresql?

I can list all tables in all schemas using > \dt *.* but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by me in the public schema and any schemas I've…
Peter Groves
  • 1,125
  • 2
  • 9
  • 7
38
votes
2 answers

How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy search. Can anyone tell me how I would check this?
Jeremy Holovacs
  • 1,241
  • 4
  • 20
  • 27
38
votes
2 answers

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and when I type \i "C:\Users\Work\Desktop\School…
Celeritas
  • 883
  • 3
  • 11
  • 16
38
votes
7 answers

SQL Server database size didn't decrease after deleting large number of rows.

I'm no good in SQL, but I've got a database to maintain. There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had about 10 000 000 rows cleaned) and cleaning…
Marat
  • 483
  • 1
  • 4
  • 7
1 2 3
…
99 100