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
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 109
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 ...
- 367
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
...
- 14.1k
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 ...
- 41.3k
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 ...
- 176k
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 ...
- 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 ...
- 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 ...
- 1,960
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 ...
- 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 ...
- 5,635
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 ...
- 176k
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 ...
- 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
- 70.7k
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,...
- 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 ...
- 2,145
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)...
- 41
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 ...
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
...
- 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 ...
- 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
...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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
...
- 1,712
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 ...
- 9,010
Only top scored, non community-wiki answers of a minimum length are eligible