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 ...


11

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 ...


7

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 ...


6

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, ...


6

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 ...


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 ...


5

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 ...


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

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

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 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

(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

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

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 ...


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

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

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 ...


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

The setting is innodb_file_per_table, and it's default 0 (ie. use one big file) up to MySQL 5.1, and then in either 5.5 or 5.6 the default changed. Changing this value will not affect tables which already exist. Even with innodb_file_per_table set to ON you still need the shared tablespace file (ibdata1 file by default). There is no one recommended ...


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 ...


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

The 42MB unit (Hitachi calls this a page too, how convinient). is the basic allocation unit at which space is allocated to a LUN. So with Dynamic provisioning, a LUN will always grow in 42MB steps. These, Hitachi 42MB pages are taken from different Array groups with in a pool. So whenever more space is needed for the LUN a new 42MB Hitachi page is ...


2

Restoring your backup will in fact bring you right back to where you were when the backup was taken. This is actually one of the few cases where a shrink is appropriate. Fortunately it's a background process and shouldn't affect your system to much unless you are running a really heavy OLTP system. Once the shrink is done, and if I was you I should shrink ...


2

Mount the database and make a level 3 datafile header dump: alter session set tracefile_identifier = 'header_dump'; alter session set events 'immediate trace name file_hdrs level 3'; In the trace file, you will find this section: Platform Information: Creation Platform ID: 13 Current Platform ID: 13 Last Platform ID: 13 The platform ID identifies ...


1

SQL Server allocates disk space for a file (.mdf or .ldf) when the space is needed (according to your settings of course), then the file remains the same size even if transaction log (.ldf) is cleared or data (.mdf) is deleted. That explains why you see 18Gb of "Space Available", and that space is only available inside your file, not on the disk (it is ...



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