Questions tagged [storage]
For questions about the persistent storage of database data.
266
questions
1
vote
1answer
24 views
Pointing the database secondary data file towards a network drive
I have a 2 TB database and the Data Drive has filled up. My network team cannot expand the drive nor provide another drive so that the secondary data file can be pointed towards that. My question is ...
4
votes
3answers
702 views
SQL Server - Benefits of splitting databases across different logical drives
We're about to start a project to migrate a large DWH to new physical servers in a new data centre. The current server spec is SQL Server Enterprise 2016 SP2 running on Windows 2012 R2. The new ...
1
vote
1answer
356 views
Azure Virtual Machine - local temp storage (D: drive) - how much IOPS it can handle?
Virtual machine size DS3 (under older generation sizes)
Azure Portal (when deploying VM) shows that DS3 supports up to 16 data disks / up to 12800 IOPS - thats fine,
But what I am interested in is it'...
1
vote
2answers
156 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
1answer
93 views
SQL Server storage - evaluate the number and types of read write operations for the existing deployment
Training for SQL Server, one of the questions in Learning material says:
Before migrating on-premise SQL Server deployment (single server) to Azure VM, you need to evaluate "number and types of ...
0
votes
1answer
50 views
ORACLE ASM present one or more LUNs?
I have the following question regarding the allocation of disks or LUNs to ASM in Oracle, specifically, I have an Oracle 18c DB in Cluster and I have created several diskgroups, now, I need to ...
0
votes
1answer
55 views
How do you change the storage parameters of a already made table (sql developer)?
Summarize the problem
I have been given 6 tables that have all been created already and I was told to change the storage parameters of those tables. the issue I run into is that I can only change ...
0
votes
1answer
32 views
Issues (Other Than Disk Space) For Using Operating System Partition
Other than accidentally filling your entire C:\ drive with data, are there any other considerations for using the OS drive for data files? Most answers are simply "don't do it", but I'd ...
0
votes
0answers
212 views
SQL Server placement of Files SAS SSD vs SAS HDD
I've got a new server which will host MS SQL Server Virtual Machine along with Other VMs on HyperV. SQL Server will account for most(70%) of the Load.
The server has below Storage:
a. 2 x 240GB Read ...
1
vote
0answers
44 views
SQL Server & Storage Question - What's the right choice? [duplicate]
I am a young DBA from Germany and I am the administrator for our customers SQL Servers.
I have a question and I really hope that I can explain this in English words. 😃
At the moment I am reading ...
0
votes
1answer
48 views
What is the point of encryption at rest?
I am wondering what is the point of encryption at rest?
It is said that a hacker cannot steal the database and look through it. I'm afraid that it does not protect the database while the key is stored ...
5
votes
1answer
1k views
Best practices for SQL Server block size while formatting disks for data and log files
What is the current best practice or general guidance on formatting the filesystem for SQL server database files.
Currently we have SQL Server file system, NTFS formatted with 64KB block size based on ...
0
votes
0answers
24 views
db choice for activity log
I'm working on a geo-replicated web platform, composed of an ecosystem of microservices.
We need to improve and rework the user activity tracing pipeline and I'm looking for "the best" ...
0
votes
1answer
330 views
TempDB Log File Growth - questions
Problem:
On one of the production boxes, TempDB transaction LOG file grew unexpectedly, filling the drive
It was 32 GB but now it is 240 GB
Questions:
What is the mechanism of TempDB log file usage ?
...
0
votes
2answers
234 views
varchar(n) size?
When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?
1
vote
2answers
85 views
Provisioning SQL Server 2019
I'm provisioning a SQL Server 2019 build and have been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with.
My build has to ...
0
votes
2answers
61 views
Does large text datatypes reserves storage on disk?
I'm design an DB for an app, in a table there is a column MEDIUMTEXT which according to mysql doc can storage on disk upto 16MB.
So my question is if I'm storing a text of 1Mb in this column, will it ...
0
votes
2answers
39 views
SQL Server sharing drive location
DBA newbie here. Did some searching online but cannot find a definite answer on this.
Is it acceptable to share the drive where .MDF files reside with other non-SQL Server files such as applications ...
1
vote
0answers
25 views
MySQL - storing 162 million rows in 1 table as the result of an IFNULL query
I'm working on a legacy web application which uses a MySQL (5.5.65-MariaDB) database which is hosted on a server running CentOS Linux 7.7.1908.
The application is a database of chemical substances and ...
0
votes
0answers
52 views
Storing data for machine learning in MySQL
I am currently in the process of developing a machine learning application, where my users can upload their own data to train models. Furthermore, the users must be able to extend their uploaded data ...
0
votes
0answers
31 views
MYSQL storage increasing unexpectedly (RDS)
I am total newbie to database game.
& I am using mysql 8.0 RDS 20GB free tier.
I don't have much data on it.(just 300 Mb)
But I always get a mail that you have exceeded 85% of storage limit.(...
1
vote
2answers
105 views
MySQL 8.0 table with JSON column that uses JSON “merge” operations has 500k rows but analyze table shows 7MM
I have a database table that according to TABLE STATUS has 7MM rows, but when I SELECT COUNT(*) it only has 500k rows.
This is a problem because table growth is increasing and we're running low on ...
5
votes
1answer
140 views
What would cause a table with 80,000 records to use 145GB of space?
I have a table that gets inserted and deleted from every day. On average the record count is 80,000. Roughly 20 fields, 10 of which are VARCHAR(4000) however most of the records have those fields ...
1
vote
2answers
91 views
What is the difference between PAGEIOLATCH_* and IO_COMPLETION?
according to this blog:
SQL Server Books online defines IO_COMPLETION as “Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O ...
1
vote
1answer
91 views
SQLite Internals - Records
Hey I'm trying to wrap my head around SQLite data storage, specifically how it is storing Records. I've found a book The Definitive guide to SQLlite, where the author explains internal record format (...
2
votes
1answer
256 views
Is it possible to Increase storage performance in azure vm using disk striping?
Given the following Azure VM:
E8s_v3 (8 cpu, 64 GB RAM, 12800 IOPS, 192 MBps)
I added 2 SSD Premium drives, one for data (1024 GBs / 200 MBps / Host Caching enabled read-only) and one for Logs (256 ...
-1
votes
1answer
224 views
How does MySQL store the row data internally?
Does a RDBMS store the row data one after another in a data block? Once data block is full, is the new block assigned continuous?
Per my understanding when table is created, DBMS vendor must be ...
1
vote
0answers
14 views
How to calcuate current IOPS consumed in AWS
We have a SQL Server hosted on AWS using EC2 and EBS. SQL Files are hosted on EBS. EBS vol type is GP2 and drive IOPS is 480.
At an instance of time Write Througput in AWS is showing is 725 and Read ...
0
votes
1answer
75 views
Why VolumeQueueLength goes high for SQL Server in AWS
We have one SQL Server hosted on AWS EC2 machine. In the monitoring tab of one of the volume, we are seeing that VolumeQueueLength is periodically reaching to around 32 and then coming back.
So ...
1
vote
0answers
148 views
MySQL Large History list length & Storage Space
Currently, my MariaDB database server is running out of space, hence I am trying to delete old records (using multiple threads with DELETE FROM tbl WHERE index > ? and index < ?).
My ...
1
vote
1answer
88 views
Cannot take backup into NAS storage at specific time
I have a job which runs every 30 minute and takes t-log backup of the database suing Ola's script. Every Thursday and Friday at 22:45 I get an error:
11/07/2019 22:45:00,Backup,Unknown,Error: 3041<...
0
votes
0answers
219 views
Which MySQL settings are the closest equivalent to RDS' “Queue depth”?
I'm trying to find out which MySQL status variables can be used to get as close as possible to the RDS value of "Queue depth".
On a test system, I reckon that %pending% variables may be at least a ...
0
votes
1answer
34 views
MySQL datafiles on SMB3 network storage
We have MySQL-server on Ubuntu with InnoDB-datafiles on a local disk. As part of an upgrade program, the company is planning to put the datafiles on a Windows fileserver where they can be mounted with ...
0
votes
1answer
84 views
How to grant xp_delete_file to non-sysadmin login?
I want to delete old backups from shared folder, which are located on separate NAS storage, using newly created SQL login. Is there any workaround to execute xp_delete_file using another account which ...
1
vote
1answer
67 views
Physical co-clustering of two or more logical relations/tables in modern RDBMS
One possible approach to optimize search queries is (a) to store the records that retain data that corresponds to different relations/tables in (b) the same file → on the same pages. That way, a join ...
2
votes
2answers
139 views
SQL Server on Windows Server (VMware) - online storage swap
I have a VM cluster designed to store multiple SQL Server instances. We use variety of storage providers (i.e. 3PAR, XIV) which have to be changed now (not relevant to what brand). On Oracle on Linux ...
0
votes
0answers
74 views
Partition Alignment in AWS SSD EBS
I'm setting up a new SQL Server 2008 r2 in AWS with IO1 SSD EBS volumes. My smaller EBS volumes are aligned correctly when they were formatted but the larger volumes, 3TB, are not because they used ...
-1
votes
2answers
406 views
EAV in postgresql
I'm building an application that needs to be deployed several times, with a relatively well organized data model. A table needs to be configured for each deployment with a small number of additional ...
15
votes
5answers
966 views
Attempts to reclaim unused space causes the used space to increase significantly in SQL Server
I have a table in a production database that has a size of 525 GB, of which 383 GB is unused:
I'd like to reclaim some of this space, but, before messing with the production DB, I'm testing some ...
0
votes
3answers
341 views
Replace column by LEFT OUTER join to save space
I have a table t1 with 100M row. For column A, 95% of the rows have value NULL0, the other records have values from 1 to 9.
To shrink my table, I'm thinking about moving column A to table t2. t2 ...
19
votes
4answers
14k views
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
On Production SQL Server, we have following config:
3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to single Dell SAN storage unit which is a RAID array
From ...
0
votes
1answer
169 views
Backup Compression SQL Server vs PURE
We are having a SQL 2016 Standard Edition Server holding 4TB of data. And the compressed backup using maintenance plans is taking around 1.5 hrs. Our storage team claims that as PURE FlashArray is ...
0
votes
1answer
50 views
Is it better to install a very large Postgres database (billions of rows) in a real or a virtual machine? [closed]
From my common sense, my physical machine hosting a very large Postgres database (for big data) should be real rather than virtual or container.
Some people say it is better to install on a virtual ...
2
votes
1answer
96 views
Understanding the differences in the storage space used by two tables
I have two tables in which, more or less, one contains a subset of data of the other one. There is some data I delete from the first table, which is inserted in the second one as historical data.
...
2
votes
0answers
305 views
Postgres configuration for high-latency storage
I'm trying to setup a postgres server using Ceph as storage. After taking benchmarks, it was obvious that even though the storage has a really good throughput, the higher latency brought by network ...
1
vote
1answer
283 views
Which Oracle datafiles grow in size?
I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to move datafiles that can grow to a separate mount point (setup as a vmdk on a HDD. more ...
2
votes
1answer
89 views
How to point tablespace in Oracle's pre-built Developer Day VM to another mount point
I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to load some data for development purposes. I am planning to create a new user and load ...
6
votes
2answers
2k views
Slow checkpoint and 15 second I/O warnings on flash storage
Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints.
At first glance it looks ...
0
votes
1answer
971 views
SQL Server Logical IO Block Sizes and Physical Reads
I am trying to understand cache churn caused by SQL statements that are doing some table scans.
Assuming LRU caches, if something is doing 400K logical IOs, how much memory is streamed across (IO ...
1
vote
1answer
1k views
Should TempDB SSD Drive be formatted with 64 Kb Allocation Unit Size?
We have installed dedicated SSD drives for TempDB
First question - should these drives be formatted with 64 Kb allocation size ? Currently it shows 4 Kb (Bytes per Cluster = 4096)
Does TempDB ...
