Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.
31 votes

How to insert (file) data into a PostgreSQL bytea column?

Use pg_read_file('location_of file')::bytea. For example, create table test(id int, image bytea); insert into test values (1, pg_read_file('/home/xyz')::bytea); Manual
user avatar
  • 521
15 votes
Accepted

How to export an image column to files in SQL Server?

This is the solution I came up with: Enable xp_cmdshell with EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO If needed create a ...
user avatar
  • 29.7k
13 votes
Accepted

How to Move TempDB Files to a Different Drive or Folder?

Moving the TempDB files is a 2-step process: Tell SQL where you want your new TempDB files to go to Restart the SQL Server service for the change to take effect To tell SQL where to create the new ...
user avatar
  • 1,478
11 votes
Accepted

Two SQL Server databases share the same MDF and LDF files but have different data

It is not possible to have two databases point to the same database files, except in the weird Autoclose case that you linked to, which no one should be using that setting anyway. Run the below query ...
user avatar
  • 5,766
10 votes

How to export an image column to files in SQL Server?

I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp ...
user avatar
8 votes
Accepted

where is the index physically located in MySQL database

For the MyISAM engine, a table's indexes are stored in the .MYI file (in the data directory, along with the .MYD and .frm files for the table). For InnoDB engine, the indexes are stored in the ...
user avatar
8 votes
Accepted

How to recover from a SQL server log file drive crash?

How to recover the database in this scenario? Restore from your backups. Read on and you'll understand why we recommend the restore route. Will it be correct to say that data loss will be only those ...
user avatar
6 votes
Accepted

Difference in space used

No different. Space Reserved for your MDF file: 100.21 GB, which is equal to 105,082,944 KB (105,082,944 / 1024 / 1024 = 100.21) Space Reserved for *_1.ndf: 148.62 GB, which is equal to 155,843,968 ...
user avatar
6 votes
Accepted

Howto query all files, their fill level and additional drive info with one query?

To avoid CROSS APPLY the simplest way I can think of is to call sys.dm_os_volume_stats with explicit parameters for database_id and file_id. This means executing a single-row result for every db/file ...
user avatar
6 votes
Accepted

what's extsort.* (8 - 11 MB) in MongoDB dbpath? Directory _tmp

These files are used when the sort you are attempting in an aggregation exceeds the maximum allowed memory and you have allowDiskUse : true specified. The relevant pieces of the code are here (where ...
user avatar
  • 9,065
6 votes

Releasing free space in mdf file to OS

What exactly you are trying to achieve by shrinking the database? You should plan for the database growth and have reserved space for this growth. So why don't you leave it as it is to have ...
user avatar
  • 2,193
6 votes
Accepted

How to set a filesystem size limit for DB2 database? Is it possible?

Yes. Tablespaces in DB2 have a MAXSIZE attribute that you can use. You can specify these size attributes in the CREATE DATABASE statement (or when creating additional tablespaces), or use ALTER ...
user avatar
6 votes
Accepted

How Database Formats Files for Disk

I think it is opportune to start pointing out that, in a relational database —as per Dr. E.F. Codd— information is represented by means of one and only one structure, the relation, which is usually ...
user avatar
  • 8,210
5 votes
Accepted

Find what data is placed in a datafile

The following SQL will show you which file groups your tables and indexes are in which will make it easy to see if there is data in any file group that shouldn't have data. SELECT f.[name] AS ...
user avatar
5 votes

Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

Restarting the server should be enough - those worktables should clear out. But I'd probably start it up in single user mode (-m) to prevent other processes from creating worktables before you ...
user avatar
5 votes

Releasing free space in mdf file to OS

Please read my post on how to shrink a database, which I'll summarize here: Look at your data file size Look at the size of all files in your filegroup. You want all files within the group to be ...
user avatar
  • 14.4k
5 votes
Accepted

Migrating Postgres server from Windows to Linux

No, you cannot do that. The files in the data directory are not compatible across operating systems (or different architectures). You will need to use pg_dump and pg_restore
user avatar
4 votes

Repair SQL Server Database

Please run below command in your SQL server database to check the level of corruption.If database is showing suspect it wont allow you to run this command DBCC CHECKDB (DB_NAME) WITH ALL_ERRORMSGS,...
user avatar
  • 18.3k
4 votes

how to change the file directory of your log and data files and what are the effects

how to change the file directory of your log and data files and what are the effects Can I just change the settings of the instance to point it to the new directory, detach the existing ...
user avatar
4 votes

How to Move TempDB Files to a Different Drive or Folder?

The calculation of the size(MB) in the script above is off. Don't forget that size in sys.master_files is the number of 8KB pages. Here is the correct script: DECLARE @newDriveAndFolder VARCHAR(8000)...
user avatar
4 votes
Accepted

SQL Server on Linux: Allocation unit on ext4

I learned at SQL Server on Windows the Allocation Unit Size (sector size) must be 64K because of the pages. It doesn't have to be 64k, in fact you'll generally get the best performance based on your ...
user avatar
4 votes

How to recover from a SQL server log file drive crash?

You can try with this undocumented command after having unattached the mdf file. USE master; CREATE DATABASE [Mydb] ON ( FILENAME = N'E:\xxx\Mydb.mdf' ) FOR ATTACH_FORCE_REBUILD_LOG; GO ...
user avatar
  • 3,417
3 votes
Accepted

'Logical file name "MyDB" is already in use' Even though the database doesn't exist

If i am able to figure out correctly from below query ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'DDAS_log', FILENAME = N'G:\DDAS_log.ldf') I believe tempdb to be [DDAS] here.. Also, in the ...
user avatar
  • 6,950
3 votes

How to copy tablespace from one database to another database in Oracle 11g

While copying a tablespace, it automatically includes data files and indexes associated with it. You need to make sure that tablespace is self contained. Confirm that tablespace is self contained ...
user avatar
  • 94
3 votes
Accepted

Discover the original OS of a datafile from a oracle backup

Mount the database and make a level 3 datafile header dump: alter session set tracefile_identifier = 'header_dump'; alter session set events 'immediate trace name file_hdrs level 3'; In the trace ...
user avatar
  • 37.9k
3 votes
Accepted

MariaDB ALTER TABLE produced a separate .ibd file, how can I re-integrate the data into the shared table space?

While there are many reasons why you may want separate tablespaces, I will assume you are aware of all the disadvantages of having a single shared tablespace, specially in terms of administration (for ...
user avatar
  • 14k
3 votes
Accepted

How can I delete a file from the primary filegroup?

The primary database file can never be removed because it contains special page structures that exist only on that file and cannot be relocated. If you want a single database file, move all data from ...
user avatar
  • 24.2k
3 votes

Storing large image files in database vs system files

Second option is much better that first for sure as for first option, there will be conversion of images to binary while saving and decoding the same while retiriving which is overhead. So i would ...
user avatar
  • 141
3 votes
Accepted

move AG database files between disks

Took me some time to test it but this actually works. Step 1 Add the new disks to both nodes of the SQL Server Availability group Step 2 Stop the SQL Server service of your passive node Step 3 ...
user avatar
3 votes
Accepted

SQL Server: Identifying object closest to the end of a datafile

I think this will do it. This code basically does the following: Retrieve highest allocated page ID from the last GAM interval in the file Retrieve highest allocated page ID from the last SGAM ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible