Scheduled database size expansion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Scheduled database size expansion

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-06-24 : 07:14:19

Anyone got some code to expand/grow SQL database files once they fall below, say, 100MB?

I'd like to run something overnight to make the extension, but leave the "auto grow" in place for unforeseen situations

Kristen

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-24 : 12:38:47

You could setup a job that runs sp_helpfile every so often and depedning on the results of that fires off the ALTER DATABASE stuff.

or

It ain't pretty, but I suppose you could set up an alert in Performance Monitor using the SQL Server Database Object and the Data File size counter and then you could set the alert execute a bat file containing an osql command to do the ALTER DATABASE stuff.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-24 : 12:43:54

Correction, the latter will not work because that counter sums up the size of all of the files in a database. Had to check, sorry.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-06-24 : 12:57:25

You can tell how full a single filegroup is, but not how full a single file is. The other problem is that you would probably want to run dbcc updateusage(0) on the database in question, before you check the amount of space used. SQL Server has not historically been too good about accounting. If you have lots of insert/update/delete action in your database, the numbers in sysindexes (or sp_helpfile) will slowly diverge from reality. Lastly, I have been told that you want to keep all data files in a filegroup about the same size, so the load balancing of inserts does not favor one file over another too much. I think it (the insert load balance algorithm) goes by %free space in the file, but I am sure I will be corrected if that is wrong.

As for actual code, sorry. Now I bet you are sorry for having read this far ;-)

Kristen
Test

22859 Posts

Posted - 2005-06-24 : 13:55:36

Good news!

Space Used calculation is fixed in SQL2005, so a single dbcc updateusage(0) after the upgrade will then mean that figures are correct thereafter.

Now then, where's that code you are all promising?

Following chatter elsewhere I'm worried that the "grow by 10%" setting on my DBs (the default, after all) might be killing performance when an expansion becomes necessary.

So I want to trigger a 200MB extension overnight if less them 100MB of free space remains - but leave the "automatically grow" setting on the DB in case it still runs out of space during the day. (I figure I'll change the Automatically grow from 10% to, say, 100MB as that will be less of a server-killer than 10% once we get into the multi-GB file sizes).

So ...

I can use sp_helpfile to find out what logical files I have, but not how full they are.

I can used sp_spaceused to find out how much free space there is in the database, but not each file (and it returns multiple recordsets so its a pain to grab the results from it) [and is the "unallocated space" just for the data files, or the Log file too? - I think just the data]

DBCC SQLPERF (LOGSPACE) gives me the percentage of Log Space used ...

and that's about all I've been able to turn up :-(

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-24 : 13:56:24

If you've got the autogrow enabled, then why would you want to add this expansion?

Tara

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-24 : 14:09:19

I think auto grow is causing her serious problems and this is an outgrowth of thread at dbforums with Paul Randall.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.

Kristen
Test

22859 Posts

Posted - 2005-06-24 : 14:16:44

Yup, that's it.

Kristen

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-06-24 : 15:50:45

Small problem with the code. OR at least I think it is a small problem. If you have two filegroups (say one for tables, and one for non-clustered indexes), I do not think there is a way to isolate the size of the index filegroup. I usually go by the query in spaceused which is something like:


select sum(reserved) * 8 as KB
from sysindexes
where indid in (0, 1, 255)

Unfortunately, the reserved number for indid 0 or 1 includes all of the reserved space for the non-clustered indexes. Anyone have a nice way around that?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-24 : 20:02:39

I think you will find that this script gives you exactly the detailed file size and space used info by file that you need.

It should be very easy to adapt this to trigger your auto-expansion.

I would have posted this earlier, but I didn't realize that you needed to get the spaceused in each file.

I pulled this from a script I run to analyze space usage on a server various ways. That is why there are columns for data files and log files, so I can sum up by filegroup, drive letter, database, etc.



select
	[DATABASE_NAME] 	= db_name(),
	[DATABASE_FILEID]	= a.fileid,
	[DATABASE_DB_NAME]	= a.name,
	[DATABASE_FILENAME]	= a.filename,

	[FILE_SIZE_MB] = 
		convert(int,round((a.size*1.000)/128.000,0)),
	[FILE_SIZE_USED_MB] =
		convert(int,round(FILEPROPERTY(a.name, 'SpaceUsed')/128.000,0)),
	[DATA_SIZE_MB] =
		convert(int,round((
		case
		when a.groupid <> 0
		then a.size
		else 0
		end
		*1.000)/128.000,0)) ,
	[DATA_SIZE_USED_MB] =
		convert(int,round((
		case
		when a.groupid <> 0
		then FILEPROPERTY(a.name, 'SpaceUsed')
		else 0
		end
		*1.000)/128.000,0)) ,
	[LOG_SIZE_MB] = 
		convert(int,round((
		case
		when a.groupid = 0
		then a.size
		else 0
		end
		*1.000)/128.000,0)) ,
	[LOG_SIZE_USED_MB] =
		convert(int,round((
		case
		when a.groupid = 0
		then FILEPROPERTY(a.name, 'SpaceUsed')
		else 0
		end
		*1.000)/128.000,0)),
	[DATABASE_GROUPID]	= a.groupid,
	[DATABASE_GROUPNAME]	= isnull(b.groupname,''),

	[MAX_SIZE_MB] =
		convert(int,round(
		(case a.maxsize when -1 then 0 
		else a.maxsize end*1.000)/128.000
		,0))

from
	dbo.sysfiles a
	left join
	dbo.sysfilegroups b
	on ( a.groupid = b.groupid )

CODO ERGO SUM

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-06-24 : 21:07:36

FILEPROPERTY? Dang new functions cropping up everywhere.....OK, I amit, I just never looked for it ;-). Gonna start experimenting with this script. Thanks, Michael Valentine Jones.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-24 : 21:30:22

If you happen to wonder where the divide by 128.000 comes from, it is the number of pages per megabyte:



select 
	MB_A = pages/128.000 ,
	MB_B = (pages*8192)/(1024.00*1024.00),
	Pages_Per_MB =(1024.00*1024.00)/8192
from 
	(select pages = 200 ) a


MB_A       MB_B              Pages_Per_MB         
---------- ----------------- --------------- 
1.5625000  1.56250000000000  128.000000000

(1 row(s) affected)

quote:
Originally posted by mcrowley

FILEPROPERTY? Dang new functions cropping up everywhere.....OK, I amit, I just never looked for it ;-). Gonna start experimenting with this script. Thanks, Michael Valentine Jones.

CODO ERGO SUM

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-24 : 22:26:21

quote:
Originally posted by mcrowley

I think it (the insert load balance algorithm) goes by %free space in the file, but I am sure I will be corrected if that is wrong.

You're right. Every file is weighted (a higher number meaning lower freespace, and at least one file always having a weighting of 1) and whenever a new extent has to be allocated, the file list is checked. If the file has a weighting of 1, the extent can be allocated from it. If not, its weighting is decremented and the next file is considered. (So the fact that at least one file always has weighting=1 is very important). Every so often (on file size changes/additions/removals or after so many allocations - can't remember off the top of my head) the weightings are recalculated so proportional fill is maintained.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

Kristen
Test

22859 Posts

Posted - 2005-06-24 : 23:30:33

MVJ: thanks, that's just the bunny I need. I was thinking along the lines of some system procedure, rather than attacking the SYS tables direct.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources