Tag Info

Hot answers tagged

15

Think of the data file as a container, like your fridge. The fridge doesn't tell you when the milk is bad (ok, maybe the Jetsons' fridge does) - you need to check inside. For SQL Server, the timestamp in Windows has nothing to do with the data you are changing inside the file; it has to do with the last time the file itself has changed, such as a service ...


12

You shouldn't delete the log file. If you are trying to reattach a data file without the log, SQL Server can technically recreate it, but there are a few potential issues, like if there were open transactions when the database was detached. In which case, you'd have total data loss. Consume the space, and don't delete your log files. You're asking for ...


10

The file system is useful if you are looking for a particular file, as operating systems maintain a sort of index. However, the contents of a txt file won't be indexed, which is one of the main advantages of a database. Another is understanding the relational model, so that data doesn't need to be repeated over and over. Another is understanding types. If ...


9

The number of files is irrelevant from a performance perspective. The number of spindles those files are distributed over is, on the other hand, critically important to performance. If you are using a reasonably modern SAN and the additional files would be created on the same mountpoint, there will be no meaningful performance difference since the data is ...


7

It really depends on what you are doing. In general the speed at which you can open a file for reading will be better than the speed at which you can establish a network connection. So for very simple operations, the filesystem is definitely faster. Filesystems will probably beat an RDBMS for raw read throughput too since there is less overhead. In fact, ...


7

As @Shark mentioned, you can't delete the log file. What you could do is set the database to READ_ONLY. With the database in READ_ONLY, no modifications are allowed and the log file will not grow. You could reduce the size of the log file to a minimal size and achieve your goal of a minimal footprint. To set the database in READ_ONLY run the following ...


6

Who ever created the database did this on purpose. Assuming that all the database files are part of the same file group (the database properties will tell you this) then all you need to do is do a DBCC SHRINKFILE any use the EMPTYFILE parameter. DBCC SHRINKFILE (Example_1, EMPTYFILE) GO ALTER DATABASE MyDatabase REMOVE FILE Example_1 GO Do that for each ...


5

You can get autogrowth events information from the default trace if it is enabled: select distinct ei.eventid, e.name from sys.fn_trace_geteventinfo(1) ei inner join sys.trace_events e on e.trace_event_id = ei.eventid where name like '%grow%'; You can see from this that the default trace does have the Data File Auto Grow and Log File Auto Grow ...


5

Space is available inside the database because data has been moved around. Perhaps you have very high levels of page splits, or have recently deleted a large portion of data that had previously caused the data file to grow. SQL Server does not shrink database files automatically when you've freed up space within them, because the logical assumption is that ...


5

SQL Server does not use the default access path when opening the database files. Instead it uses its own low-level driver to get the highest performance possible. As a result of that you cannot rely on any information that windows is displaying about the files. instead consult the appropriate system views and DMVs. I am however not aware of any way to ...


4

DBCC SHRINKFILE -- will be single-threaded – which will contribute to the long run-time. Also, Sql Server Integration Services Data Export will be slower due to massive database size (1 TB) ! Instead, you should look for BCP OUT (in binary format) and BULK INSERT in the database. BCP can read the SQL Server native format from text files. This is a very ...


4

The file system might be faster initially, but I doubt it. However, as your data size increases you will likely have to restructure your file system to maintain performance. Besides their obvious ability to index on multiple attributes, databases tend to scale better. Web caches which work similarly to what you are considering use directory tree to ...


4

These mdf files can also be of other databases... to check your database files use script: USE [DatabaseName] GO SELECT name, physical_name FROM sys.database_files GO


4

The database engine won't care about the extension, it is just a logical name for humans to understand what they are seeing.


4

The extension and the file path don't really matter – the content does. Moreover, when you use Oracle Managed Files (see "Using Oracle Managed Files" in Database Administrator's Guide), Oracle automatically generates file names, you only have to specify the destination path on the file system or on ASM (see "Oracle Automatic Storage Management (Oracle ASM)" ...


4

If you have a single RAID10 volume then as far as SQL Server is concerned you have one volume and you can't control how things are stored, splitting things into extra files unnecessarily will likely have detrimental effects as it would on a single disk. If you wish to try gain performance benefits from segregating data between spindles then you need to ...


4

Please run below command in your SQL server database to check the level of corruption.If database is showing suspect it wont allow you to run this command DBCC CHECKDB (DB_NAME) WITH ALL_ERRORMSGS,NO_INFOMSGS This command can be used to check corruption in SQL Server database. Also I must say you are running very old version of SQL server if you are ...


4

(Consolidating the comments into an answer, so it's more easily consumable by searchers.) It appears this is related to not granting the appropriate permissions to the data container when moving the location of the physical files. Without having tried it, I believe this would also apply to user databases, not just tempdb. I have an old, but still ...


3

But I see nothing that tells it whether to grow the primary MDF, or grow by adding additional NDFs. There is NO option native in SQL Server that will allow you to do this, unless you explicitly specify to add additional secondary files (.ndf) using Alter database command. More info Files and Filegroups Architecture & Database Files and Filegroups. Is ...


3

Like @JonSeigel said above in his comment, this is not a SQL Server action. Your third party application(s) must be doing this on their own, or somebody manually creating the additional data files. And, is there a way to combine these NDF files back into the primary MDF? What you're looking for is DBCC SHRINKFILE(YourDataFileName, EMPTYFILE); where ...


3

You want to recombine the files because you think that is the cause of your performance issue. Do you have any proof that it actually is? It is a lot more likely that the problem is somewhere else, as distributing a database across multiple files actually increases performance in many cases. Things to look at first: do you have appropriate indexes are ...


3

It's typically good practice to seperate your OS installation from your database installation to isolate OS disk issues from database disk issues. The primary reason for this is to reduce the change that a problem with the OS could prevent recovery of the database, or vice versa. If your database fills up the free disk space, it can crash both the database ...


3

It is certainly possible to delete data from a random access file. Realistically, though, virtually any database will do a soft delete and mark data as deleted rather than physically deleting data. At some point, something else will then reuse the space that the deleted row had been using (what operations reuse the space will depend on the database among ...


3

Restarting the server should be enough - those worktables should clear out. But I'd probably start it up in single user mode (-m) to prevent other processes from creating worktables before you successfully remove those files. Then redefine the files required for tempdb; perhaps deleting unnecessary files, changing sizes, etc. You should also ensure you have ...


2

Those three files represent one MyISAM table For the MyISAM table, mydb.TableName, here is what they are TableName.frm : Table format file (all Storage Engines have this one) TableName.MYD : MyISAM data for the table TableName.MYI : MyISAM Indexes for the Table Click to read MySQL Documentation for MyISAM Storage Engine. In part, it says: Each ...


2

Even multiple tempdb files on a single LUN should be a last resort compared to one tempdb per LUN. If there are multiple database files on a LUN, and they are allowed to auto-grow, their contents will be interleaved in the filesystem and on the storage. Hit them both heavily with read or write requests, and a situation known as IO weaving, or disk head ...


2

You probably won't see any benefit from having multiple data files on a single LUN for a user database. TempDB is a special case because of the massive amount of objects which are created within the tempdb database during normal operations.


2

Unfortunately, there is really not an easy way to get this table back. Although the data is likely still in the ibdata1 file, only specialized tools can recover it. If this data is business critical, some consulting companies such as Percona can help you to recover it, but that will be quite expensive (thousands of dollars at least), and not all of the data ...


2

Shut the database down. Start it up again in mount mode: connect / as sysdba; startup mount; Drop the datafile: ALTER DATABASE DATAFILE '/full/path/of/file.dbf' OFFLINE DROP; Open the DB: alter database open; Drop the tablespace: DROP TABLESPACE <TS Name> INCLUDING CONTENTS;


2

In the Filesystem Hierarchy Standard, /var/lib is listed as containing: State information. Persistent data modified by programs as they run, e.g., databases, packaging system metadata, etc. So it is very much because it is the traditional/documented place to put such things. If a database system or similar is installed under /opt it is common for the ...



Only top voted, non community-wiki answers of a minimum length are eligible