20
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 ...
17
Use pg_read_file('location_of file')::bytea.
For example,
create table test(id int, image bytea);
insert into test values (1, pg_read_file('/home/xyz')::bytea);
Manual
16
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 ...
16
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 ...
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 ...
13
This is the solution I came up with:
Enable xp_cmdshell with
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
If needed create a directory with xp_cmdshell to get the needed permissions.
EXEC master..xp_cmdshell 'mkdir C:\exportdir'
Use BCP with queryout
EXEC master..xp_cmdshell 'BCP "...
12
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, ...
11
It is not possible to have two databases point to the same database files, except in the weird Autoclose case that you linked to, which no one should be using that setting anyway.
Run the below query to view what physical files the databases are pointing to:
SELECT database_id, name, physical_name
FROM master.sys.master_files
With that information, you ...
10
I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp will be prompted with the following questions:
Enter the file storage type of field FileData [image]:
Enter prefix-length of field FileData [4]:
Enter length of ...
9
As mentioned in another answer, 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 ...
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 ...
9
Moving the TempDB files is a 2-step process:
Tell SQL where you want your new TempDB files to go to
Restart the SQL Server service for the change to take effect
To tell SQL where to create the new TempDB files, you can use:
DECLARE @newDriveAndFolder VARCHAR(8000);
SET @newDriveAndFolder = 'Z:\YourTempDBfolder';
SELECT [name] AS [Logical Name]
,...
8
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 ...
8
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 ...
8
For the MyISAM engine, a table's indexes are stored in the .MYI file (in the data directory, along with the .MYD and .frm files for the table).
For InnoDB engine, the indexes are stored in the tablespace, along with the table. If innodb_file_per_table option is set, the indexes will be in the table's .ibd file along with the .frm file.
No, it's not ...
6
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 ...
6
No different.
Space Reserved for your MDF file: 100.21 GB, which is equal to 105,082,944 KB (105,082,944 / 1024 / 1024 = 100.21)
Space Reserved for *_1.ndf: 148.62 GB, which is equal to 155,843,968 KB (155,843,968 / 1024 / 1024 = 148.62)
The numbers match up just fine. The Operation System is going to see Space Reserved, and that's how you would need to ...
6
To avoid CROSS APPLY the simplest way I can think of is to call sys.dm_os_volume_stats with explicit parameters for database_id and file_id. This means executing a single-row result for every db/file combo.
First, create a #temp table to hold results:
CREATE TABLE #x(dt datetime, srv nvarchar(520), logical_name sysname,
physical_name sysname, FileSizeMb ...
6
What exactly you are trying to achieve by shrinking the database?
You should plan for the database growth and have reserved space for this growth.
So why don't you leave it as it is to have allocated space for new data coming to your DB.
It has been discussed so many times that you do not want to shrink your databases. Check this out and think twice ...
6
Yes. Tablespaces in DB2 have a MAXSIZE attribute that you can use. You can specify these size attributes in the CREATE DATABASE statement (or when creating additional tablespaces), or use ALTER TABLESPACE to set them later.
create database mydb
automatic storage yes
on C:\mydb
user tablespace managed by automatic storage
initialsize 20 M ...
5
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 ...
5
The database engine won't care about the extension, it is just a logical name for humans to understand what they are seeing.
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
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 ...
5
(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 ...
5
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 ...
5
The following SQL will show you which file groups your tables and indexes are in which will make it easy to see if there is data in any file group that shouldn't have data.
SELECT f.[name] AS FileGroupName
, o.[name] AS ObjectName
, o.[type] AS [Type]
, i.[name] AS IndexNAme
, i.[index_id] AS IndexId
...
5
Please read my post on how to shrink a database, which I'll summarize here:
Look at your data file size
Look at the size of all files in your filegroup. You want all files within the group to be evenly sized, and you want to allow space for growth, index maintenance, etc. It makes more sense to err on the side of leaving the database a little too large ...
5
I think it is opportune to start pointing out that, in a relational database —as per Dr. E.F. Codd— information is represented by means of one and only one structure, the relation, which is usually portrayed as a table.
The relational model deals exclusively with aspects of the logical level of abstraction, so a table (an abstraction) is part of the ...
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
Only top voted, non community-wiki answers of a minimum length are eligible
