Hot answers tagged datafile
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 ...
7
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 ...
6
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
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
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
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 ...
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 ...
2
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
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, ...
2
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 ...
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 ...
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 ...
1
Basic function to return rows from multiple SELECT statements:
CREATE OR REPLACE FUNCTION f_get_rows()
RETURNS TABLE(a_id integer, txt text) AS
$func$
BEGIN
RETURN QUERY SELECT a.a_id, a.txt FROM tbl_a a;
RETURN QUERY SELECT b.a_id, b.txt FROM tbl_b b;
END
$func$ LANGUAGE plpgsql;
You could do the same with UNION ALL.
Simple & quick demo to ...
1
For a SQL Server solution, FILESTREAM (or FILETABLE if you can use SQL Server 2012) was built to solve this type of problem.
This solution will allow you to store the files in a central network location (most importantly, not on a web server box) outside the database, and manipulate metadata information within the database.
As mentioned in my comment, ...
1
You need one file.
Unless you have specific requirements to have multiple files, you only need one file for the filegroup. The 'specific' requirements would be things like requirement to spread the filegroup on multiple disk locations or a requirement for round-robin allocation. You did not mention any such requirement, you only give information about the ...
1
Here is the solution I came up with to shrink the LDF files.
Detach Database
Rename LDF file to *_old.ldf
Attach Database
Remove the refernece to the missing LDF
This recreates an LDF file that is 504K in size.
Delete *_old.ldf
Empty recycle bin
This has reclaimed a significant amount of disk space on the server. It works for us because all of these ...
Only top voted, non community-wiki answers of a minimum length are eligible
