Questions tagged [datafile]
The datafile tag has no usage guidance.
142 questions
58
votes
6
answers
220k
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 ...
- 939
17
votes
6
answers
84k
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?
- 33.6k
12
votes
4
answers
35k
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 ...
12
votes
4
answers
22k
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);
...
- 415
11
votes
2
answers
11k
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?
- 1,554
10
votes
4
answers
23k
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
1
answer
18k
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 ...
- 1,935
9
votes
1
answer
20k
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 ...
- 195
7
votes
2
answers
4k
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 ...
Shadowfoxx
5
votes
2
answers
14k
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" ...
- 313
5
votes
2
answers
943
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 ...
- 9,497
5
votes
2
answers
12k
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 ...
- 171
5
votes
1
answer
989
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 ...
- 1,550
5
votes
1
answer
243
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 ...
- 189
5
votes
2
answers
4k
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 ...
- 151
5
votes
2
answers
14k
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 ...
- 61
4
votes
3
answers
2k
views
How to recover from a SQL server log file drive crash?
Assuming data files are on D: and Log files are on E:
Say the E drive crashes and the log files (.ldf) are lost. I attach a new empty disk to the E: and start the sql server.
Upon startup, SQL Server ...
- 3,489
4
votes
6
answers
21k
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 ...
Mohamed Sawaf
4
votes
3
answers
6k
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 ...
- 1,399
4
votes
1
answer
4k
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. ...
- 143
4
votes
2
answers
8k
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.
...
- 143
4
votes
1
answer
268
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,807
4
votes
1
answer
994
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 ...
- 41
4
votes
1
answer
214
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 ...
- 10.7k
4
votes
1
answer
284
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,807
3
votes
1
answer
2k
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?
- 192
3
votes
2
answers
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
1
answer
3k
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 ...
- 1,051
3
votes
1
answer
1k
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 ...
- 135
3
votes
3
answers
2k
views
Creating/restoring mdf/ldf to non-default file location giving access denied
Default folder is typical C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA. But C: drive on server is almost full.
So, in restoring an 11gb database from another server, I ...
- 131
3
votes
1
answer
4k
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 ...
- 1,013
3
votes
1
answer
725
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 ...
- 2,558
3
votes
1
answer
656
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 ...
- 31
2
votes
1
answer
3k
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?
- 141
2
votes
1
answer
106
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 ...
- 221
2
votes
1
answer
656
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,558
2
votes
1
answer
361
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 ...
- 33
2
votes
1
answer
10k
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 ...
- 617
2
votes
1
answer
345
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 ...
- 51
2
votes
2
answers
4k
views
SQL Database size incorrect and database file growing while empty
RESOLVED - SOLUTION AT BOTTOM OF POST
PROBLEM
I have a database data file showing as 200GB on disk, and 200GB allocated space in the database properties. sp_spaceused also shows 200GB allocated. All ...
2
votes
1
answer
826
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 ...
- 121
2
votes
1
answer
469
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 ...
- 236
2
votes
1
answer
1k
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 ...
- 58
2
votes
2
answers
2k
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 ...
- 1,960
2
votes
1
answer
1k
views
Post data deploy in SSDT
I need to create post deployment script which will upload data from .csv file to my database. But I can't understand how can I do this.
When I tried to deploy my project on my server I receive this ...
- 169
2
votes
1
answer
370
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 ...
- 21
2
votes
1
answer
637
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,...
- 771
2
votes
2
answers
5k
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 ...
- 21
2
votes
1
answer
78
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 ...
- 121
2
votes
1
answer
2k
views
DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1
In following a recommendation from our new storage vendor, I'm trying to split up some large & busy databases into multiple data files, each to live on a separate VDisk that has a separate ...
- 804