Does anyone know a utility/batch script to Warn when close to Max DB size

Hello Group
Does anyone know of a utility or Batch script (osql or Windows cmd or mix)
to Warn an Admin when a MSDE DB is closing in on 2 GB in size (or 2005
Express closes in on 4 GB).
Any leads or tips will be appriciated highly
Best regards
Uffe
hi Uffe,
Uffe Bak wrote:
> Hello Group
> Does anyone know of a utility or Batch script (osql or Windows cmd or
> mix) to Warn an Admin when a MSDE DB is closing in on 2 GB in size
> (or 2005 Express closes in on 4 GB).
> Any leads or tips will be appriciated highly
> Best regards
> Uffe
in MSDE you can perhaps schedule a job defined to include a way to gather
that kind of data, using direct access to the sysfiles database table or via
DBCC SHOWFILESTATS..
you can then validate that data against a userdefined treshold and notify
someone via NET SEND or mail via SMPT alternative, like exploded in
http://www.karaszi.com/sqlserver/info_no_mapi.asp,
http://www.dbmaint.com/SmtpAlerter.asp , thus defining a kind of alert..
PRINT 'MSDE';
PRINT '--';
USE Northwind;
PRINT 'sp_spaceused';
EXEC sp_spaceused @.updateusage = 'TRUE';
PRINT '--';
PRINT 'DBCC SHOWFILESTATS';
CREATE TABLE #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
);
INSERT INTO #tmp_sfs
EXECUTE('DBCC SHOWFILESTATS');
SELECT LEFT(DB_NAME(),10), (SUM(totalextents) * 64) / 1024 AS [totalextents
in MB], (SUM(usedextents) * 64) /1024 AS [usedextents in MB]
FROM #tmp_sfs;
DROP TABLE #tmp_sfs;
PRINT '--';
PRINT 'sysdatabase query';
SELECT LEFT(DB_NAME(),10) AS [Database], sum(convert(float,size)) * (8192.0
/1024.0) /1024.0 AS [Size in MB for Data files]
FROM dbo.sysfiles
WHERE (status & 0x40) <> 0x40;
SQLExpress does not provide the SQL Server Agent, so you can perhaps rely on
the native OS scheduler for something similar.. as regards the SMPT
integration, you have to (possibly) write your own CLR based mailer
solution.. I personally will... then, from the OS scheduler, you'll execute
SqlCmd command line tool to perform the SQLExpress connection and data
retrival...
just an idea..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks to Andrea and Satya for quick answers
I will look into your suggestions and return if I find a solution.
I also looked up some Scripting options, so there are as usual more than one
road to Rome
Best regards
Uffe Bak