Adding Space to the Database

Question

Adding Space to the Database

GA_SQL

Hall of Fame

Points: 3166
More actions
November 2, 2016 at 5:42 am

#337083

Adding space to the Database , when the db is 75% full
here i done some script for finding free space
select
s.FILEID AS [File ID],
[Free Space in %] = convert(decimal(12,2),round(100*(f.size-fileproperty(s.name,'SpaceUsed'))/128.000,2)/(s.size/128.000)) ,
[File Name] = left(s.NAME,30)
from dbo.sysfiles s
order by fileid asc

Viewing 14 posts - 1 through 13 (of 13 total)

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

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

GA_SQL (11/2/2016)
Adding space to the Database , when the db is 75% full
here i done some script for finding free space
select
s.FILEID AS [File ID],
[Free Space in %] = convert(decimal(12,2),round(100*(f.size-fileproperty(s.name,'SpaceUsed'))/128.000,2)/(s.size/128.000)) ,
[File Name] = left(s.NAME,30)
from dbo.sysfiles s
order by fileid asc

What is your question?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

GA_SQL Hall of Fame Points: 3166 More actions · Answer 2

need a script or an idea to create script when my db is full by 75% i want add some space to the db

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

GA_SQL (11/2/2016)
need a script or an idea to create script when my db is full by 75% i want add some space to the db

Is that because you have set the Maxsize property of the database and you want to detect when the database is getting close to hitting it?

Otherwise, I do not know what you mean by "75% full".

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

GA_SQL Hall of Fame Points: 3166 More actions · Answer 4

for any db when allocated disk size is 75% full, then we want to add space

MadAdmin SSChampion Points: 11261 More actions · Answer 5

you didnt specify which full you mean, is the F drive 75% full and you want to add disk, or the MDF is 75% full and you want to grow the file.

For data, here is a script to go through all DB's and find space used.

http://www.sqlservercentral.com/Forums/Topic670489-146-1.aspx

For Disks,

SELECT distinct volume_mount_point,total_bytes/1024/1024 total_bytesInMB ,available_bytes/1024/1024 available_bytesInMB,1.0*available_bytes/total_bytes FreeSpaceRatio

FROM sys.master_files AS f CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

GA_SQL Hall of Fame Points: 3166 More actions · Answer 6

i am asking on MDF when it 75% full i want add the space to the DB

Step 1: if my MDF is 75% full then

Step 2: add some space

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

GA_SQL (11/2/2016)
need a script or an idea to create script when my db is full by 75% i want add some space to the db

This could actually be a very bad idea, especially for large databases. It's also a bad idea when it comes to restores because you might not have the required disk space to build all of the empty space. If you have an accidental runaway query, that could also play hell with this.

My recommendation (unless you have DOD security issues) is to enable "Instant File Initialization" and setup a reasonable growth setting for each database (that will never be a "percentage" growth, IMHO) and let Mother-Nature handle it for you. It's a whole lot less work and has the advantage of being able to RESTORE to a smaller footprint, if needed.

A better thing to be concerned with is the size of the Log files. I don't recommend shrinking them all of the time but, if you've had a runaway query or a query with an accidental many-to-many join (think accidental Cartesian Product), the log file(s) could be unnecessarily huge and THAT's a huge problem with restores because "Instant File Initialization" doesn't help so much with log files. It would be far better to make sure the initial size and growth settings are correct to "right size" the number of VLFs that will be made and make sure they're not currently a train wreck.

--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)

MadAdmin SSChampion Points: 11261 More actions · Answer 8

Jeff Moden (11/2/2016)
GA_SQL (11/2/2016)
need a script or an idea to create script when my db is full by 75% i want add some space to the db
This could actually be a very bad idea, especially for large databases. It's also a bad idea when it comes to restores because you might not have the required disk space to build all of the empty space. If you have an accidental runaway query, that could also play hell with this.

Yeah, an alert or a mail with a list of the DB's and the free space in the data files would be better than automatically growing gigs.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

HanShi SSC-Dedicated Points: 33507 More actions · Answer 9

GA_SQL (11/2/2016)
Adding space to the Database , when the db is 75% full
here i done some script for finding free space
select
s.FILEID AS [File ID],
[Free Space in %] = convert(decimal(12,2),round(100*(f.size-fileproperty(s.name,'SpaceUsed'))/128.000,2)/(s.size/128.000)) ,
[File Name] = left(s.NAME,30)
from dbo.sysfiles s
order by fileid asc

You already have the script to find the usage of each datafile (either the code above or the script MadAdmin has posted). Next thing is to put it into a stored procedure and script some additional checks about available diskspace, if database is allowed to grow, etc (see the posts by all others). If your checks are all green and the datafile is allowed to grow, you must calculate the desired space of the databasefile. Finally you can use the following syntax to set the new size of the datafile:

SET @SQLCommand = 'ALTER DATABASE [' + @DBNaam +

'] MODIFY FILE (NAME = N' + char (39) + rtrim(@LogicalFileName) + char(39) + ',

SIZE = '+ convert(varchar(8), @NewSize) + 'MB)'

EXEC (@SQLCommand)

Take notion the code above is just a very basic sample that won't run untill you provide the proper variable declaration and values, but you'll get the idea to build upon. It is also wise to wrap the code above in a loop (with increasing @NewSize) to prevent the database from growing in one very large chunk when the desired size is way larger then the current size. In such case you want to grow the database in smaller steps (with delays in between) to prevent negative impact on the performance/availability of the database.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

With "Instant File Initialization", there just isn't a need to do any type of "manual" or "scheduled" growth.

--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)