mdf space balance

Question

Post reply

mdf space balance

Bruin

SSCrazy

Points: 2058
More actions
November 24, 2022 at 1:15 pm
Go to Answer
#4117575

I have a DB that is running out of space on a drive... If I add another data file will the drive that is running low run out of space, or will
it use the new data file just created.
How does it balance data consumption.
Thx.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

An additional data file will be used if it is in the same filegroup as the existing data files.

😎

You might want to look into creating a new filegroup for indices on a different storage if applicable 😉

What are the storage and file configurations for this server and its databases?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

When you add additional files to a file group - SQL Server uses a proportional fill algorithm. You can review further here: https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver16

The ideal scenario is to have files all the same size in a filegroup - and to have the data spread evenly across all files. One way to achieve that would be to add 2 new files to the file group each with the same size on separate volumes (drive/mount point) and then perform an empty file on the original file in the group. SQL Server would then move the data from the original file into the 2 new files using proportional fill to evenly distribute the data across both files.

If you just add a single new file of the same size - and then rebuild indexes, eventually the same thing will happen and data will be mostly evenly distributed across both files.

Notes:

Separating data and indexes into different files and filegroups will not improve performance on Enterprise SAN's.
Multiple data files in a filegroup will not improve performance on Enterprise SAN's.
If you have multiple files defined for a database, regardless of the number of filegroups - and place those files on the same drive you eliminate any possibility of improvements to performance for integrity checks. There is a trace flag that can be enabled *if all files are on separate drives (mount points)* that can significantly improve the performance of CHECKDB.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2058 More actions · Answer 3

Currently just one file and the default file group...

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

I don't know your database but I'd take advantage of this as an opportunity to improve. I'd move my two to four largest fragmenting clustered indexes each to their own separate file group on the new drive. When it comes time to defrag one, create yet another file group and file, rebuild the clustered index onto that new file group using CREATE INDEX WITH DROP_EXISTING = ON, and then drop the now empty file/file group. It keeps you from having a shedload of allocated but unused database that nothing but the one database can use for such optimizations.

That will also free up some space on the original drive for such maintenance and growth.

There's also the concept of keeping only the most recent data on the primary file group, which may require different indexing than years old legacy data, in a different file group either as "online" with a partitioned VIEW, of "available if needed" with a rolling delete as you send it older data from the primary and then delete from the primary.

Lot's of opportunities here instead of just adding a file to the primary file group.

Let you imagination start clicking on such possibilities because such opportunities aren't real common.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2058 More actions · Answer 5

Thanks for suggestion. I think that's what I'll do

There's also the concept of keeping only the most recent data on the primary file group, which may require different indexing than years old legacy data, in a different file group either as "online" with a partitioned VIEW, of "available if needed" with a rolling delete as you send it older data from the primary and then delete from the primary.

Bruin SSCrazy Points: 2058 More actions · Answer 6

Any rule of thumb to use or steps

Example:

USE master

GO

ALTER DATABASE TestDB ADD FILEGROUP HISTORY

ALTER DATABASE TestDB

ADD FILE

(

NAME='History_Data',

FILENAME = 'E:\DATA\TesDB_2.mdf'

)

TO FILEGROUP HISTORY

GO

USE TestDB

GO

ALTER TABLE UserLog

DROP CONSTRAINT PK__UserLog__7F8B815172CE9EAE WITH (MOVE TO HISTORY)

USE TestDB

GO

CREATE UNIQUE CLUSTERED INDEX UIX_UserLogID

ON UserLog(UserLogID) ON [PRIMARY]

Bruin SSCrazy Points: 2058 More actions · Answer 7

should this process fill up the Transaction Log for the DB if it's set to SIMPLE?

ALTER TABLE UserLog

DROP CONSTRAINT PK__UserLog__7F8B815172CE9EAE WITH (MOVE TO HISTORY)

thanks.

Bruin SSCrazy Points: 2058 More actions · Answer 8

I was thinking maybe the way to go would be create a new tmp table and transfer the table data from Primary FileGroup to the History in batches say 150,000 records at a time inserts...

My source table is just History data so wouldn't have to worry about locking... do I need to worry about TEMPDB size?

If my table layout is like below could someone share a table copy script that shows progress as I have over 2 trillion rows

to move from this table..

After script completion I could just rename table and then truncate source table after validation...

To regain that space from Primary filegroup and free space on that volume(Shrinkdb) ?

Current space consumption:

34437.5625 -- free space 34MB

2,048,165 -- db size 2TB

2.013.727.5 -- dbused size TB

Appreciate help and suggestions from prior posts.

CREATE TABLE [dbo].[tmp_sqlt_data_1_2022_10](
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
[tagid] ASC,
[t_stamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [History]
GO

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Bruin wrote:

ALTER TABLE UserLog DROP CONSTRAINT PK__UserLog__7F8B815172CE9EAE WITH (MOVE TO HISTORY)
USE TestDB GO
CREATE UNIQUE CLUSTERED INDEX UIX_UserLogID ON UserLog(UserLogID) ON [PRIMARY]

That'll certainly fill up your log file! When you drop the constraint, you're also dropping the underlying Clustered Index. That causes the data to be copied to a new HEAP table and ALL of the non-clustered indexes to be rebuilt to key of the RID for the Heap instead of the Key from the Clustered Index. Then you're turning right around and converting the Heap back to a Clustered Index, which causes a rebuild of all the Non-Clustered indexes, again!

Don't drop any more constraints, eh? 😀

Stop writing code. Write down a simple plan of WHAT you want to do and NOT HOW you think you'd do it and let's see it. Start with telling me the end result is supposed to be. If it's what I said, you've started out all wrong. And, I/we need more details about the table if you want some real help. Things like size in bytes and rows and which temporal column you intend to use to decide what to keep in the PRIMARY and what to copy to the HISTORY. I also need the CREATE TABLE statement for the table including all indexes and constraints.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2058 More actions · Answer 10

I had a drive start filling up and it couldn't be expanded... 2TB defined on the backend storage.

I created another 1TB drive and then a filegroup called History and defined a 600gig secondary file to that area...

Now my plan is to start migrating the larger tables over to the History FileGroup.. I've been using SSMS and just doing an export to

a copy of the table I defined in the History FileGroup. I have that DDL attached above ..

Some of the tables I'm moving have over 1 billion records and consuming (few) are over 200gig in size...

I thought I could create a small SP that did the table copies have 2 parms of old-table new-table and maybe put messages to a table to show progress... When it completed, I would simply rename tables and drop the source after verification...

To release the space back to the OS would I have to issue a ShrinkFile and slowly increment the MB down?

I get sent alerts from the Low Disk space from Network guy

I hope I explained well enough. There are no Constraints of the table and a single non-clustered index on t_stamp

Thanks for replies

Bruin SSCrazy Points: 2058 More actions · Answer 11

I have 10-15 tables I would like to move to help balance the drive usage on that server. The tables I'm moving are just for reporting by the front-end application.

Thanks.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Bruin wrote:

I have 10-15 tables I would like to move to help balance the drive usage on that server. The tables I'm moving are just for reporting by the front-end application.
Thanks.

If you're just moving them, then do like I originally said. Use CREATE INDEX with the DROP_EXISTING option turned on. You do NOT need to first drop the index or any related constraint. If your database is in the FULL Recovery Model and you're not doing something that relies on the log file (Replication, AG, etc), then you can temporarily switch the Recovery Model to Bulk-Logged without destroying the log-file chain when you do the "move" and the "move" will be "Minimally Logged". I strongly recommend that you take a log-file backup immediately before and after the "move" because any "Minimal Logging" will mess up the ability to do a Point-in-Time restore to the middle of any log file that records a "Minimally Logged Event" (also know as a "bulk" operation).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

You should also look into page compression for those tables - not sure if that is available in 2012 for all versions, but if so it could save a lot of space on those tables.

I am curious as to why there is a 2TB limitation on the storage. The only limitation I recall around that was related to an earlier version of VMWare that couldn't support volumes larger than 2TB. However, that limitation was resolved a long time ago and shouldn't be an issue today.

Either way - as @JeffModen has stated, to 'move' a table from one filegroup to another is simply an index rebuild specifying the new filegroup. You don't have to create a new table and insert the data from the old table into the new table - and it can be done index by index and using bulk-logged recovery model to reduce the impact on the transaction log.

As for returning space to the OS - why would you need to do that? That volume should be dedicated to hosting SQL Server data files only - so there is no reason to give that space back to the OS. And you will still need that space available in the file to perform future maintenance. If the concern is that some monitoring tool is triggering an alert because there is no space available on the drive - then it really isn't anything you need to worry about because you are addressing the issue.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2058 More actions · Answer 14

Just so I understand I just need to rebuild Index with:

CREATE UNIQUE CLUSTERED INDEX PK__UserLog__7F8B815172CE9EAE ON UserLog (UserLogID)

WITH (DROP_EXISTING = ON)

ON HISTORY

Thx.