Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [datafile]

142 questions
58
votes
6 answers

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 can, presumably, be done by one of the PL/…
SabreWolfy
  • 949
  • 1
  • 7
  • 16
17
votes
6 answers

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?
Jonas
  • 33,945
  • 27
  • 62
  • 64
12
votes
4 answers

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); and then delete the file: ALTER DATABASE tempdb…
AdamL
  • 415
  • 1
  • 5
  • 12
12
votes
4 answers

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 use less processing power. To check if a file…
BlueBerry - Vignesh4303
  • 333
  • 1
  • 5
  • 13
11
votes
2 answers

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

How do I move my TempDB Data or Log File(s) from wherever they are now to a different Drive or Folder?
Oreo
  • 1,566
  • 1
  • 10
  • 22
10
votes
1 answer

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 like the LSB or the POSIX, and would appreciate…
LMC
  • 305
  • 1
  • 3
  • 12
10
votes
4 answers

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 of these snapshots has an .MDF and .LDF file. I…
Michael Riley - AKA Gunny
  • 945
  • 2
  • 7
  • 20
9
votes
1 answer

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 get a gauge of the amount of external…
MattyZDBA
  • 1,955
  • 3
  • 20
  • 32
7
votes
2 answers

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 data files, but I've found mixed opinions…
Shadowfoxx
5
votes
2 answers

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 spacial column to it; stay separately from that…
5YrsLaterDBA
  • 171
  • 1
  • 2
  • 6
5
votes
2 answers

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 GB file (which is practically empty). Now, the…
Garrett
  • 151
  • 1
  • 2
5
votes
2 answers

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" recovery mode, and only has two files, .MDF and…
KenD
  • 313
  • 2
  • 5
  • 19
5
votes
2 answers

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 beyond requested RESIZE value What should I do…
user1617237
  • 61
  • 1
  • 2
  • 6
5
votes
1 answer

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 it is or how to remove it. select * from…
James Lester
  • 189
  • 1
  • 1
  • 6
5
votes
2 answers

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 so far The following query utilizes an undocumented…
John Eisbrener
  • 9,547
  • 6
  • 31
  • 65
1
2 3
…
9 10