All Questions
Tagged with datafile sql-server
57 questions
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
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
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
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
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
1
answer
269
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
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
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
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
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
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
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
2
votes
0
answers
50
views
What is using up space in database file? [closed]
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 ...
- 189
2
votes
1
answer
124
views
Database Size Troubleshooting [closed]
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 ...
- 121
1
vote
1
answer
520
views
Database file reserved space
What are the possible reasons that a SQL Server database file reserved space is 95 percent of the total space? My database file has increased drastically and it shows that the actual space used is 5 ...
1
vote
1
answer
7k
views
'Logical file name "MyDB" is already in use' Even though the database doesn't exist [closed]
I recently was given a SAN with a couple of RAID arrays to apply to my database server to increase performance. I have (and will only ever have) one user database called DDAS, in addition to the ...
- 618
1
vote
1
answer
101
views
SQL Server Express mdf file is 50+gb
I have a SQL Server Express database running on a POS register where the mdf file is 50+gb. When I run select @@version I get:
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:...
1
vote
1
answer
136
views
65G SQL Server database has 5.5G of data in tempdb when not in use
I have a SQL Server instance with 1 production database (also ReportServer dbs) that is 65G. tempdb is 5.5G (well, actually much bigger but 5.5G is in use) when server is not in use. I believe this ...
- 11
1
vote
1
answer
8k
views
In what situation the .NDF files are created?
I'm not a db administrator but from a quick search I found .NDF files are secondary data files.
I'm curious to know under what conditions the .NDF files are generated ?
If it is generated, how we can ...
- 115
1
vote
2
answers
3k
views
Detached database file permissions
I have a script that detaches databases for archival when they meet certain criteria. The script is executed by SSIS under the local Administrator account by the Ghost of DBA Past. Per design it (by ...
1
vote
4
answers
2k
views
Script to merge-replicate two MDF database files?
I have a program on a computer and on a tablet. The program uses SQL Server Express to interact with a local database (the same information on both) from a .MDF file. So if I plug the tablet into the ...
- 195
1
vote
1
answer
1k
views
How many files should I create for one filegroup and 9 weekly partitions?
I have 9 weekly partitions on one filegroup. How many files do I need to create?
There are 3 tables containing 46 million, 16 million and 10 million rows.
- 11
1
vote
0
answers
132
views
Transactional Replication and distribution database size
I have a transactional replication set up with initialize from backup.
In the secondary database server,i have a drive(d:) for data files alone.
I have noticed that this drive is getting filled and i ...
- 1,335
1
vote
0
answers
60
views
Is there a program I can use to redistribute DB files across multiple volumes efficiently?
I'm working with a VLDB instance with over 60 DBs and close to 200 DB files, allocated over nearly 20 volumes. The instance is a Dev server getting periodic restores from a production instance with a ...
- 1,935
0
votes
2
answers
971
views
Why is it not important whether data and log files share the same disk or not in a SAN? [closed]
I have a hard disk. I create 3 drives on it. One for OS. One for sql data files. One for sql log files.
However I am reading that suppose my storage layer is not a hard disk, but a SAN based ...
- 3,489
0
votes
1
answer
4k
views
move AG database files between disks
Environment: SQL 2016 SP1, AG with 2 replicas, sync commit, no readable secondary. 1 file in the PRIMARY FG, 1 for the index FG.
Problem: How to move the files for a database in this AG, from the ...
- 1,014
0
votes
2
answers
10k
views
How can I delete a file from the primary filegroup?
In dealing with fragmented disk space, I added a second data file to one of my databases. Most of the data is now in the the second file, and I'd like to drop the first to simplify things. However, ...
- 5,957
0
votes
2
answers
46
views
SQL Server: which data file extension should be used when adding a primary file on another drive?
If I have a Windows disk with a capacity of 1T, for example, and the primary datafile (.mdf) for my database reaches the disk's maximum size, I want to add a second datafile to the primary group.
As ...
- 1
0
votes
1
answer
602
views
SQL Server data drive (.mdf files) running out of space
We have a D drive that has 999 GB of total space. Currently the production database data files are being stored there. It is running out of space.
A few weeks ago DB files were shrunk to temporarily ...
- 83
0
votes
1
answer
273
views
Multiple mdf, empty one is missing
Hi guys i have a big problem. I know i did such stupid and unforgivable mistake.
And know please help me if you can.
I wanted to make copy of database. I should make it by backup and then restore as ...
0
votes
1
answer
772
views
Identify Full Text Catalog Files
I'm trying to write a query for a report to show how much free space is in our data files so we can plan storage for the future. I'm pulling data out of sys.database_files, and I can narrow it down to ...
- 3,272
0
votes
2
answers
223
views
How would I move SQL MSSQL.1 and MSSQL.2 location?
We have a SQL instance where the MSSQL.1 and MSSQL.2 were installed on a SAN drive. We are moving away from this SAN do to faults and we wish to move the files and folders to a new SAN. How would we ...
- 95