Questions tagged [datafile]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
37
votes
5answers
121k views

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

This question is not about bytea v. oid v. blobs v. large objects, etc. I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This ...
13
votes
6answers
53k views

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

I will migrate from a database. There is one column of type image that I would like to export to binary files on the file system. One file for each record. How can I do this with SQL Server?
10
votes
2answers
14k views

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

I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files): DBCC SHRINKFILE('tempdbfile8', EMPTYFILE); ...
9
votes
4answers
14k views

Do I really need to keep .LDF files?

Each month we make an end of month snapshot of our production database. These month end snapshots are strictly for reporting purposes, there are no inserts, updates or deletes ever done on them. Each ...
9
votes
1answer
16k views

Identify File Growth Events

I'm discovering a number of DBs in my portfolio that have been created with default autogrow settings (either 1 MB or 10% increments) that have expanded for an extended period of time. If I wanted to ...
8
votes
4answers
27k views

What's better/faster? MySql or FileSystem?

Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography. I'll admit my SQL queries could be better but in general what would be faster and ...
7
votes
2answers
2k views

How to Move TempDB Files to a Different Drive or Folder? [duplicate]

How do I move my TempDB Data or Log File(s) from wherever they are now to a different Drive or Folder?
6
votes
2answers
3k views

Multiple SQL Server data files on same SAN disk

I'm currently in the process of creating a new database, and have previously only ever used a single data file and a single log file. I've done some research online regarding the benefits of multiple ...
5
votes
2answers
12k views

Disk space full but logical space available in database

We have a fairly big MS SQL 2008R2 database that resides on a SSD drive. The drive itself only has ~110Gb of space, and the database files are the only files on the drive. The database is in "Simple" ...
5
votes
1answer
9k views

Why is MySQL database data-files deployed under /var/lib/mysql?

I was wondering why do we deploy data files under /var/lib/mysql/. Is there a logical reason for that, or is that just a "traditional" place for the data? I will value more answers based on standards ...
5
votes
2answers
190 views

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

TL\DR I'm looking for a way to efficiently identify the object located closest to the end of a SQL Server data file. This approach needs to remain performant against large data files. What I have ...
5
votes
1answer
744 views

Find what data is placed in a datafile

I have a database with 5 .ndf (secondary) datafiles on SQL Server 2008 R2. (Each of these datafiles is assigned to a separate filegroup.) I want to know what data is there in those files. Is it ...
5
votes
1answer
150 views

Difficulty removing files from filegroup

I am trying to delete all files from my database from an unused filegroup. There are two files totaling aroun 1.5TB. I can see that there is now approx 400mb still in these files but I cant see what ...
5
votes
2answers
3k views

Splitting a large SQL Server MDF file

I have a large (1.2 terabyte) SQL Server database that I need to migrate to a new server. Most of the database lives on a single, 1.25 TB data file, and a little bit sits on a much-more-manageable 550 ...
4
votes
6answers
17k views

SQL Server split mdf into multiple files

I have a database called example.mdf with a total size of 1GB which suffers from performance issues. I checked the allocated hardware and it is higher than required, I double checked the design and ...
4
votes
3answers
3k views

Releasing free space in mdf file to OS [duplicate]

We have a 120GB database. There was table with 60GB of data which is useless and we have truncated it. Now database size is 120GB with free space 60GB. The database will not grow up to 60GB in at ...
4
votes
1answer
2k views

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

I have a strange circumstance where I have a production database and a restored copy as another database on the same SQL Server instance, and both of them are using the same MDF and LDF files (i.e. ...
4
votes
2answers
6k views

where is the index physically located in MySQL database

Just wondering where is the index info physically located in MySQL database if I have indexed a table with ALTER TABLE contacts ADD KEY (columnName); ? it will stay with that table and add a new ...
4
votes
1answer
104 views

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

I need a query that puts together the following results: one row per database file across all databases additional columns with information about the locigal drive the file is on information about ...
4
votes
1answer
945 views

Repair SQL Server Database [closed]

Can anyone give me a complete guide to verifying/repairing a SQL Server database? At the moment our backup is failing on the Solarwinds database saying the database NetPerfMon is corrupted (we are ...
4
votes
1answer
176 views

Should I align my SQL Server file and growth sizes with the block sizes on my SAN?

It was brought to my attention that our (Hitachi) SAN handles data in 42MB blocks. It is tiered storage so each block is evaluated when the director makes decisions as to when it should promote/demote ...
4
votes
1answer
83 views

Why isn't transaction log free space always reused?

After monitoring the Transaction log files for a while I noticed that some of them sometimes behave in a way I did not expect. Here are two samples for comparison. All data is based on EXEC('DBCC ...
4
votes
2answers
11k views

Shrinking the SYSTEM tablespace in Oracle

Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table. We have truncated SYS.AUD$ but the datafile is still very big (~30G). Resize doesn't work because the file contains used data ...
3
votes
1answer
511 views

Difference in space used

When reviewing the space used by datafiles in SQL Server, there is a large difference betweens what SQL reports and Windows. How can this be explained?
3
votes
2answers
2k views

Autogrow. Primary vs additional data files

My databases all use autogrow, which grows the primary MDF file on a percentage. But one of the databases, from a third party application grows by adding additional .NDF files. Where is this option ...
3
votes
1answer
1k views

Tempdb data file disappearing

I recently moved my Temp database files onto a separate drive (I had two data files and one log file). However, after restarting the server, my second data file disappeared both from the file list on ...
3
votes
1answer
941 views

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

We have a multi-GB InnoDB MariaDB (recently switched from MySQL). For some reason, the value of innodb_file_per_table changed to ON (we wanted only one innodb data file explicitely). So when I ran an ...
3
votes
1answer
970 views

DBCC SHRINKFILE for Log and Data when using Availability Groups

We had an issue where one of our database tables grew by 40GB by mistake. The database is part of an Availability Group with one primary and two replicas. While trying to delete the extra records we ...
3
votes
1answer
543 views

cleaning up failed repair db: path: /var/lib/docker/wesfgr/repair/_tmp_repairDatabase_0

We run MongoDB 3.0.6 with MMAP in Docker container (single node). The filesystem was full and I did a repair with other path. 2016-10-03T08:58:51.186+0200 I INDEX [initandlisten] building ...
3
votes
1answer
580 views

Database for store images

Actualy I'm using MongoDB with embedded images next to other data in same documents. Images count actualy are more than 14000 and each image size is less than 1MB. Images are readed frequently and ...
2
votes
2answers
6k views

Oracle datafile extension .dat or .dbf

I have seen these 2 extensions used for datafiles, .dat and .dbf while creating and/or altering a tablespace. I'm not sure what the difference between the 2 extensions is, or if .dat is incorrect. ...
2
votes
1answer
2k views

Are multiple dbf files better than one?

Is it better to distribute a large database over several dbf files than over a single one?
2
votes
1answer
137 views

SQL Server on Linux: Allocation unit on ext4

I'm testing SQL Server on Linux and now on the following point: Allocation Unit Size of the partition: I learned at SQL Server on Windows the Allocation Unit Size (sector size) must be 64K because of ...
2
votes
1answer
253 views

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

I never saw this in my dbpath and didn't found any explanation with a search engine. We run MongoDB 3.2.8 # ls -alh; pwd total 151M drwxr-xr-x. 2 mongod mongod 4.0K Sep 12 22:18 . drwxr-xr-x. 6 ...
2
votes
1answer
3k views

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

I have inherited a SQL server 2012 instance where the file directories of the log and data files are on the same folder on the same drive(all are in the C:\ drive). My task that I will be doing is ...
2
votes
1answer
228 views

SQL Server data files and RAID

I have a server with a RAID1 volume (OS + apps) and a RAID10 volume (database). The RAID10 volume has 6 physical disks. Should I split the SQL Server database into 6 datafiles (one for each physical ...
2
votes
1answer
538 views

Storing large image files in database vs system files

I need to store many large photos that will be uploaded daily in my application and retrieve those with id. There is no need to any special queries. What is the best approach for storing this files ...
2
votes
1answer
386 views

Restore InnoDB tables / database using only data files

I have MySQL Server 5.1.51 installed on Windows Server 2003 as Operating System and using InnoDB as database engine. I have run DROP DATABASE dbName statement by mistake. I have no backup of that ...
2
votes
1answer
85 views

Disk Architecture on SQL Server [closed]

I am building a new environment using the SQL Server 2017 Always-On system. This will be active 24/7 and there will be lots of DML processes. I have a 100GB SAS for the OS, a 1TB SAS for Data and a ...
2
votes
2answers
907 views

Backup and Restore SQL server files and filegroups

I have a database called Admin. It has only one default filegroup called primary. File group: primary files in the file group: Admin_data.mdf Now I have taken a full filegroup backup. Backup file ...
2
votes
1answer
113 views

Pausing the import of an SQL file in Postgresql?

I am currently importing a large database (6 - 7 GB's) into my postgresql on Ubuntu. It's taking sometime to do. A problem has come up where I need to physically relocate the laptop the postgresql is ...
2
votes
1answer
521 views

How does subpartitioning actually work and what physical files are created?

I can't understand how the subpartitioning works in mysql. I am reading the following example: create table invoices ( customer_id int not null, product_id int not null, amount decimal (8,...
2
votes
1answer
3k views

PowerShell and SQL Server export XML

I have a stored procedure that ends with FOR XML EXPLICIT and I need to put the xml file in a folder. I've set up the following PowerShell, and run the script providing the two parameters. The ...
2
votes
1answer
70 views

Adding specific information to a local file

I have several files on a webserver (not stored in the database). I'd like to store several information to this specific file. The user should be able to post comments about this file. My question ...
2
votes
1answer
518 views

Mysql cannot import from text file

I have a table called person setup like this (person_id SMALLINT UNSIGNED, fname varchar(20), lname varchar(20), gender enum('M','F'), birth_date date, race enum('B','W','A','J'), ethnicity varchar (...
2
votes
0answers
44 views

What is using up space in database file?

I am attempting to empty a database file Data has been shifted from a database file by using the emptyfile shrink command and then rebuilding/reorganising all heaps and indexes so that the data is ...
2
votes
0answers
336 views

SQL Server 2008 R2 - Two Files Stuck In Restoring Status

Background: SQL Server 2008 R2 mirrored and clustered. We recently failed over the databases via mirror and everything went smoothly. When it was time to do a differential backup, I got an error so I ...
2
votes
1answer
75 views

Database Size Troubleshooting

I have a SQL Server database and the .MDF file is almost 9GB in size. However, the data and indexes only seem to be taking up a little over 1GB. I've shrunk the database (with and without ...
1
vote
1answer
182 views

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

I'm looking for a way to limit a DB2 database size (in disk) at creation time. But I couldn't find it. At SQL server we can do by this way: Create Database MyDatabase on (Name='MyDatabase_Data', ...
1
vote
1answer
55 views

How Database Formats Files for Disk

This SO answer outlines how MySQL organizes its database into files, but doesn't describe the structure of the files themselves. I am wondering, at the physical level, how relational database ...