How to increase data and log files by 25% of their total size for all databases?
One day my colleague asked me if I have a script that increases the size of all databases on SQL Server. Therefore I wrote this little script which generate ALTER DATABASE command for extending data or log files by 25% of total size of database.
Let's say that our monitoring tool send alert over night regarding lack of space for most databases of production SQLServer. We'll inform customer databases are almost full and need to be extended. Customer require to extend all data files by 25% of their total size and log files by 25% of their total size.
So, I wrote little script which based on table sys.database_files where we can find out a couple of informations for example size and name of all databases on SQL Server. Also you can find system store procedure sp_MSforeachdb which means that we are able to execute select statement on table sys.database_files for each databases of instance.
Script for extending of Data Files:
As you can see there is a few condition in clausule WHERE:
Size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 IS NOT NULL - This ensure that we will not extend for In - Memory OLTP or FileStream data file.
type_desc = ''ROWS'' - This filters only data file(.mdf, .ndf files).
state_desc = ''ONLINE''' - Choose only databases which are only in online state.
You can adjust percentage by your own opinion here:
(click on picture)
And for log files script looks like that:
Only set variable @Newsize = 0.10 and modify type_desc = ''LOG'' in where clausule. That's all.
Have a nice day!!!