Most Popular
1500 questions
37
votes
4 answers
I Need to Shrink My Database - I just freed a lot of space
This question is asked in various forms here but the question boils down to:
I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again.
How can I shrink my database? What files do I shrink?
What…
Mike Walsh
- 18,173
- 6
- 47
- 72
37
votes
2 answers
Index not making execution faster, and in some cases is slowing down the query. Why is it so?
I was experimenting with indexes to speed up things, but in case of a join, the index is not improving the query execution time and in some cases it is slowing things down.
The query to create test table and fill it with data is:
CREATE TABLE…
SpeedBirdNine
- 525
- 1
- 4
- 9
37
votes
5 answers
Restoring a backup to an older version of SQL Server
When trying to restore a backup to a SQL Server Express 2008 database I got the following error:
Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The database was backed up on a server…
rem
- 1,555
- 3
- 20
- 22
37
votes
2 answers
Does the order of columns in a PK index matter?
I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever loaded - its a historical record (SQL Standard,…
BlueChippy
- 857
- 6
- 12
- 17
37
votes
3 answers
Duplicate column for faster queries?
The title doesn't make too much sense, but I couldn't think a better title for this problem.
I have the following tables
Projects
id
name
Customers
id
id_project
name
Payments
id
id_customer
date
sum
When a users enters the system, he will…
Gabriel Solomon
- 985
- 2
- 10
- 13
37
votes
8 answers
media family on device is incorrectly formed. SQL Server cannot process this media family
I am trying to restore a .BAK in SQL server but get the following error:
Msg 3241, Level 16, State 7, Line 1 The media family on device 'c:\glyn\JA.bak' is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State…
Funky
- 471
- 1
- 4
- 5
37
votes
1 answer
VACUUM returning disk space to operating system
VACUUM usually does not return disk space to operating system, except in some special cases.
From the docs:
The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will…
Vadim Samokhin
- 615
- 1
- 8
- 12
37
votes
3 answers
Permission denied in Postgres
I added the user myuserto Postgres.
Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres.
Then I tried to give all rights to access and modify mydatabase to…
Aslan986
- 681
- 2
- 6
- 10
37
votes
2 answers
What is the difference between OPTIMIZE TABLE and ANALYZE TABLE table in MySQL
What is the difference between OPTIMIZE TABLE and ANALYZE TABLE table in MySQL? I have read the online documentation but not sure what the difference is.
Boolean
- 611
- 1
- 8
- 10
37
votes
2 answers
Is it generally faster to select into a temp table than selecting into an actual table?
I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity?
I thought I recall it saying something special about tempdb and storing the data in memory?
J.D.
- 37,483
- 8
- 54
- 121
37
votes
1 answer
What happened to MySQL 6 & 7?
With the recent release of MySQL 8, I can't seem to find any information about why version numbers 6 and 7 were skipped. Anyone know?
Talk Nerdy To Me
- 485
- 1
- 4
- 5
37
votes
3 answers
Why would a SELECT query cause writes?
I've noticed that on a server running SQL Server 2016 SP1 CU6 sometimes an Extended Events session shows a SELECT query causing writes.
For example:
The execution plan shows no obvious cause for the writes, such as a hash table, spool, or sort that…
James Lupolt
- 4,258
- 5
- 27
- 45
37
votes
7 answers
What are reasons **NOT** to use the MEMORY storage engine in MySQL?
I recently discovered that MySQL has a "memory" engine that I wasn't aware of (most of my database work is for hobby projects so I learn what I need as I go). It seems like this option should give me drastically improved performance, so I'm…
Michael McGowan
- 789
- 2
- 10
- 20
37
votes
1 answer
Number of active connections and remaining connections
I would like to get statistics about the peak number of connections over a period of time.
I know the pg_stat_activity view, like select count(*) from pg_stat_activity, but I think this method is not very smart.
Are there other views or tables that…
Jack Geller
- 473
- 1
- 4
- 5
37
votes
2 answers
What does 'ibfk' stand for in MySQL?
If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I have gathered that [tablename]_ibfk_constraintIndex…
Myer
- 475
- 1
- 4
- 8