All Questions
Tagged with datafile sql-server
57 questions
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
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:...
-2
votes
1
answer
40
views
How to modify filepath of a database file that is inside a file group [duplicate]
How do you modify filepath of a database file that is inside a filegroup?
Just the normal and direct way?
Alter database []
Modify file (name='', newfilepath='')
Or
Alter database []
Modify filegroup ...
0
votes
0
answers
40
views
Does adding a new data file (without partitioning) have any pro/con compared to increasing space on existing data file?
I have a database data file with 1 data file in the default filegroup. It is reaching to 500GB in size and I'm trying to learn about whether to increase the size of existing data file or add a new ...
- 3,489
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 ...
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
0
votes
2
answers
970
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
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
0
answers
344
views
How to open .bin SQL database?
I'm stuck and not sure if somebody had a similar issue.
I have an old database stored as .bin files. It dates back to 2012. I can see an xml catalogue file which indicates to me that it is some kind ...
- 1
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 ...
0
votes
0
answers
491
views
SQL Server File or Filegroup Creation Date
Is there a way to find out when a File was added to a filegroup in SQL Server. Or how to determine when a filegroup was created?
I've tried querying the sys tables looking at the lsn information and ...
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
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
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 ...
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
581
views
SQL Server Storage for multiple data files
Recently, our company wants to add 4TB on the drive size for SQL Server. We have two drives now which have different size and data files. In order to make sure our SQL Server has the best performance, ...
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
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
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
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
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
0
votes
1
answer
116
views
Database still reports 600GB after removing 500GB File
I have a database with three files
Log
Primary Data File
Blob Data file
the database was becoming too big to manage (600GB) so we removed the blob data (500GB) into FileStorage outside the DB.
we ...
- 121
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
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
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
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 ...
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
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
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
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
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
-9
votes
2
answers
3k
views
Wrong Last Modified Date of .ndf file on SQL Server
I have SQL Server Database and we have many tables. To improve performance, I have created different filegroups with files on different disks just for indexes. I have created new index today in a ...
- 111
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