October 31, 2019 at 12:45 am
Hi All ,
I have a problem with big database that occupies some space in a drive , This drive is running out of space so I want to add more data file in a new added drive
I tested a big insert ( million record ) into a table in this database HOWEVER the weird thing is data file size doesn't change at all
How come ?
Initial size is 257 GB , autogrowth by 100 MB , limited to 270 GB
and
I add 1 more data file ( path points to a new Drive ) with initial size 250 MB , autogrowth by 100 MB , unlimited
Your feedback is much appreciated
thank you
October 31, 2019 at 1:18 am
Google some script on the web to check free space of the 257GB data file, I guess there will be some free space available. Until it's all used, autogrowth will not kick in.
October 31, 2019 at 5:35 am
Thanks mate !
October 31, 2019 at 10:42 am
Hi All ,
I have a problem with big database that occupies some space in a drive , This drive is running out of space so I want to add more data file in a new added drive
I tested a big insert ( million record ) into a table in this database HOWEVER the weird thing is data file size doesn't change at all
How come ?
Initial size is 257 GB , autogrowth by 100 MB , limited to 270 GB
and
I add 1 more data file ( path points to a new Drive ) with initial size 250 MB , autogrowth by 100 MB , unlimited
Your feedback is much appreciated
thank you
You can run this to find the space usgae:
https://www.sqlserverblogforum.com/dba/drive-space-check-sql-server-steps-and-scripts/
Edited: To add file and file group method
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
October 31, 2019 at 5:37 pm
SQL Server uses a proportional fill algorithm when the filegroup has multiple files. This means it will check the available space in each file an proportionally use each data file when inserting/updating data.
If - for example - you have a second data file at 256MB and there is 100GB available in the first file, the SQL Server will place most of the inserts/updates on the first file.
The general recommendation is to have each file in a filegroup sized the same, and when adding a new file you want to then rebuild all indexes in the database so that SQL Server can then use this proportional fill algorithm to spread the data in each index (clustered and non-clustered) evenly across both files.
If your tables do not have a clustered index - then you need to rebuild them using ALTER TABLE ... WITH REBUILD.
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
November 4, 2019 at 3:59 am
Many thanks All
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply