mdf space balance

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

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

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

    1. Separating data and indexes into different files and filegroups will not improve performance on Enterprise SAN's.
    2. Multiple data files in a filegroup will not improve performance on Enterprise SAN's.
    3. 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

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

  • 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)
    Intro to Tally Tables and Functions

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

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

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

  • 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
  • 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)
    Intro to Tally Tables and Functions

  • 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

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

  • 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)
    Intro to Tally Tables and Functions

  • 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

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

     

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

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