July 31, 2014 at 2:18 am
Hi,
I'm working on a database that has its size set to auto grow of 10%. Obviously this is not ideal and could potential bring down the application using it. Its currently sitting at 55 GB.
Is there a script to check available free space and if under a certain amount grow DB by 'X' GBs.
I could then set this up as a scheduled job running every couple of weeks in quiet time. ( 1-2 am)
Many Thanks
B
July 31, 2014 at 3:30 am
This is the script I've come up with, will this suffice? I will run at daily to check for available space.
DECLARE @free_space DECIMAL(18,2);
DECLARE @db_size DECIMAL(18,2);
DECLARE @sqlcommand nvarchar(MAX);
--GET DB SIZE AND AVAILABLE FREE SPACE
SELECT
@db_size = CAST( as DECIMAL(38,0))/128.,
@free_space = (CAST( as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.)
FROM [sys].[database_files]
WHERE file_id = 1 AND type = 0;
SET @db_size = @db_size + 3000.00
SET @sqlcommand = 'ALTER DATABASE MyDatabase
MODIFY FILE (NAME = MyDatabase_data, SIZE = ' + CAST(@db_size AS NVARCHAR(50)) + 'MB )'
--PRINT @db_size;
--PRINT @free_space;
--PRINT @sqlcommand;
IF @free_space < 1000
BEGIN
PRINT 'Expanding Database ...'
EXEC (@sqlCommand);
PRINT 'Expanding Database DONE'
END
ELSE
BEGIN
PRINT 'No expansion of MyDatabase required'
END
July 31, 2014 at 3:31 am
I'd say a better solution is to pre-size your data files to allow for a year or so of growth and then have monitoring in place to alert you when the free space is getting low. When this time comes around then extend for another 6months/1 year, based on the latest growth figures that you have.
I've attached a bit of a rough and ready script that might do as you originally wished but would definitely need to test and ensure does what you want.
July 31, 2014 at 3:47 am
MrG78 (7/31/2014)
I'd say a better solution is to pre-size your data files to allow for a year or so of growth and then have monitoring in place to alert you when the free space is getting low. When this time comes around then extend for another 6months/1 year, based on the latest growth figures that you have.I've attached a bit of a rough and ready script that might do as you originally wished but would definitely need to test and ensure does what you want.
Thanks, I will use your suggestion of pre-sizing but space is a little limited on the server so I cant go over board that's why i though a check daily would be good.
July 31, 2014 at 5:11 pm
SQL Server provides a stored proc, "'sp_spaceused", to return space info. But annoyingly and frustratingly, it returns two separate result sets.
For my use, I cloned their proc, adjusting it as needed for the functionality I wanted; that's the code below. Naturally adjust the table location and default bit settings as you prefer.
Then, when you need to get space info for a db, you can run the code using some variation of this:
EXEC userdb1.dbo.sp_spaceused_db 1 --change to your db name
EXEC userdb2.dbo.sp_spaceused_db 1 --change to your db name
EXEC ('SELECT * FROM tempdb.dbo.sp_spaceused_db')
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_spaceused_db]
@load_results_into_table bit = 0,
@delete_previous_rows_for_this_db bit = 1
AS
declare @pagesbigint-- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(data_pages) + sum(text_used)
** index: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
select
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2)),
[unallocated space] = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2)),
reserved = ltrim(str(@reservedpages * 8192 / 1048576.,15,2)),
data = ltrim(str(@pages * 8192 / 1048576.,15,2)),
index_size = ltrim(str((@usedpages - @pages) * 8192 / 1048576.,15,2)),
unused = ltrim(str((@reservedpages - @usedpages) * 8192/ 1048576.,15,2))
if @load_results_into_table = 1
begin
if OBJECT_ID('tempdb.dbo.sp_spaceused_db') IS NULL
CREATE TABLE tempdb.dbo.sp_spaceused_db (
ident int IDENTITY(1, 1) NOT NULL,
date_captured datetime NULL,
database_name sysname NULL,
database_size decimal(15, 2) NULL,
unallocated_space decimal(15, 2) NULL,
reserved decimal(15, 2) NULL,
data decimal(15, 2) NULL,
index_size decimal(15, 2) NULL,
unused decimal(15, 2) NULL
)
else
if @delete_previous_rows_for_this_db = 1
delete from tempdb.dbo.sp_spaceused_db
where database_name = DB_NAME()
insert into tempdb.dbo.sp_spaceused_db
select
GETDATE(),
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2)),
[unallocated space] = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2)),
reserved = ltrim(str(@reservedpages * 8192 / 1048576.,15,2)),
data = ltrim(str(@pages * 8192 / 1048576.,15,2)),
index_size = ltrim(str((@usedpages - @pages) * 8192 / 1048576.,15,2)),
unused = ltrim(str((@reservedpages - @usedpages) * 8192/ 1048576.,15,2))
end --if
RETURN
GO
EXEC sp_MS_marksystemobject 'sp_spaceused_db'
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply