Database script to alarm

Hi,
Somebody has some script to make this requirement?
Databases
When the space that the file of any one of the databases used for the
Biztalk to reach
a quota of occupation in superior record 80% of its maximum size.
FufillyHi
You can do this in many ways such as tools such using Perfmon or Microsoft
Operations Manager (MOM).
Looking at this in a different perspective you could monitor the amount of
free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
job which you force to fails and so alerts an operator. The same technique
used in the stored procedure can be used with sp_helpfile if you want actual
size.
John
"José Júlio Duarte" wrote:
> Hi,
> Somebody has some script to make this requirement?
> Databases
> When the space that the file of any one of the databases used for the
> Biztalk to reach
> a quota of occupation in superior record 80% of its maximum size.
> Fufilly|||Hello John Bell
I need this information by Database?
Fullfilly
José Júlio Duarte
"John Bell" wrote:
> Hi
> You can do this in many ways such as tools such using Perfmon or Microsoft
> Operations Manager (MOM).
> Looking at this in a different perspective you could monitor the amount of
> free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> job which you force to fails and so alerts an operator. The same technique
> used in the stored procedure can be used with sp_helpfile if you want actual
> size.
> John
> "José Júlio Duarte" wrote:
> > Hi,
> >
> > Somebody has some script to make this requirement?
> >
> > Databases
> >
> > When the space that the file of any one of the databases used for the
> > Biztalk to reach
> > a quota of occupation in superior record 80% of its maximum size.
> >
> > Fufilly|||Hi
If you are doing this per database you may to try something like this to the
the file sizes:
SET NOCOUNT ON
CREATE TABLE #filesizes ( dbname sysname, [filename] sysname, [sizeKB] int,
type char(4) )
DECLARE @.name sysname
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master..sysdatabases
ORDER BY dbid
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #filesizes ( dbname, [filename], [sizeKB], [type] )
EXEC ( 'SELECT ''' + @.name + ''', name, size*8, CASE WHEN status&0x40=0x40
THEN ''Log'' ELSE ''Data'' END FROM ['+ @.name + ']..sysfiles' )
FETCH NEXT FROM db_cursor INTO @.name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT * FROM #filesizes
DROP TABLE #filesizes
GO
Another alternative is to set the Alert in the Alerts section found in under
SQL Server Agent, you can set this for each data and log file for each
database using the type: SQL Server Performance Condition Object:
SQLServer:Databases Counter:
Data File(s) Size(KB) or Log File(s) Size(KB) and the instance is each
database.
John
"José Júlio Duarte" wrote:
> Hello John Bell
> I need this information by Database?
> Fullfilly
> José Júlio Duarte
> "John Bell" wrote:
> > Hi
> >
> > You can do this in many ways such as tools such using Perfmon or Microsoft
> > Operations Manager (MOM).
> >
> > Looking at this in a different perspective you could monitor the amount of
> > free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> > use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> > job which you force to fails and so alerts an operator. The same technique
> > used in the stored procedure can be used with sp_helpfile if you want actual
> > size.
> >
> > John
> >
> > "José Júlio Duarte" wrote:
> >
> > > Hi,
> > >
> > > Somebody has some script to make this requirement?
> > >
> > > Databases
> > >
> > > When the space that the file of any one of the databases used for the
> > > Biztalk to reach
> > > a quota of occupation in superior record 80% of its maximum size.
> > >
> > > Fufilly|||Hello John Bell
Like the first script i need all information about all database.
Fullfilly
José Júlio Duarte
"John Bell" wrote:
> Hi
> If you are doing this per database you may to try something like this to the
> the file sizes:
> SET NOCOUNT ON
> CREATE TABLE #filesizes ( dbname sysname, [filename] sysname, [sizeKB] int,
> type char(4) )
> DECLARE @.name sysname
> DECLARE db_cursor CURSOR FOR
> SELECT name
> FROM master..sysdatabases
> ORDER BY dbid
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> INSERT INTO #filesizes ( dbname, [filename], [sizeKB], [type] )
> EXEC ( 'SELECT ''' + @.name + ''', name, size*8, CASE WHEN status&0x40=0x40
> THEN ''Log'' ELSE ''Data'' END FROM ['+ @.name + ']..sysfiles' )
> FETCH NEXT FROM db_cursor INTO @.name
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> SELECT * FROM #filesizes
> DROP TABLE #filesizes
> GO
> Another alternative is to set the Alert in the Alerts section found in under
> SQL Server Agent, you can set this for each data and log file for each
> database using the type: SQL Server Performance Condition Object:
> SQLServer:Databases Counter:
> Data File(s) Size(KB) or Log File(s) Size(KB) and the instance is each
> database.
> John
> "José Júlio Duarte" wrote:
> > Hello John Bell
> >
> > I need this information by Database?
> >
> > Fullfilly
> >
> > José Júlio Duarte
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You can do this in many ways such as tools such using Perfmon or Microsoft
> > > Operations Manager (MOM).
> > >
> > > Looking at this in a different perspective you could monitor the amount of
> > > free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> > > use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> > > job which you force to fails and so alerts an operator. The same technique
> > > used in the stored procedure can be used with sp_helpfile if you want actual
> > > size.
> > >
> > > John
> > >
> > > "José Júlio Duarte" wrote:
> > >
> > > > Hi,
> > > >
> > > > Somebody has some script to make this requirement?
> > > >
> > > > Databases
> > > >
> > > > When the space that the file of any one of the databases used for the
> > > > Biztalk to reach
> > > > a quota of occupation in superior record 80% of its maximum size.
> > > >
> > > > Fufilly|||Hi
Run the script and you will see that it will produce information for each
file on every database.
John
"José Júlio Duarte" wrote:
> Hello John Bell
> Like the first script i need all information about all database.
> Fullfilly
> José Júlio Duarte
> "John Bell" wrote:
> > Hi
> >
> > If you are doing this per database you may to try something like this to the
> > the file sizes:
> > SET NOCOUNT ON
> >
> > CREATE TABLE #filesizes ( dbname sysname, [filename] sysname, [sizeKB] int,
> > type char(4) )
> >
> > DECLARE @.name sysname
> > DECLARE db_cursor CURSOR FOR
> > SELECT name
> > FROM master..sysdatabases
> > ORDER BY dbid
> >
> > OPEN db_cursor
> >
> > FETCH NEXT FROM db_cursor INTO @.name
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > INSERT INTO #filesizes ( dbname, [filename], [sizeKB], [type] )
> > EXEC ( 'SELECT ''' + @.name + ''', name, size*8, CASE WHEN status&0x40=0x40
> > THEN ''Log'' ELSE ''Data'' END FROM ['+ @.name + ']..sysfiles' )
> > FETCH NEXT FROM db_cursor INTO @.name
> > END
> > CLOSE db_cursor
> > DEALLOCATE db_cursor
> >
> > SELECT * FROM #filesizes
> >
> > DROP TABLE #filesizes
> > GO
> >
> > Another alternative is to set the Alert in the Alerts section found in under
> > SQL Server Agent, you can set this for each data and log file for each
> > database using the type: SQL Server Performance Condition Object:
> > SQLServer:Databases Counter:
> > Data File(s) Size(KB) or Log File(s) Size(KB) and the instance is each
> > database.
> >
> > John
> >
> > "José Júlio Duarte" wrote:
> >
> > > Hello John Bell
> > >
> > > I need this information by Database?
> > >
> > > Fullfilly
> > >
> > > José Júlio Duarte
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You can do this in many ways such as tools such using Perfmon or Microsoft
> > > > Operations Manager (MOM).
> > > >
> > > > Looking at this in a different perspective you could monitor the amount of
> > > > free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> > > > use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> > > > job which you force to fails and so alerts an operator. The same technique
> > > > used in the stored procedure can be used with sp_helpfile if you want actual
> > > > size.
> > > >
> > > > John
> > > >
> > > > "José Júlio Duarte" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Somebody has some script to make this requirement?
> > > > >
> > > > > Databases
> > > > >
> > > > > When the space that the file of any one of the databases used for the
> > > > > Biztalk to reach
> > > > > a quota of occupation in superior record 80% of its maximum size.
> > > > >
> > > > > Fufilly|||Hello Jonh Bell
but this don´t say when the database is 80% full
Result of Exemple script:
dbname filename sizeKB type
Northwind Northwind 3712 Data
Northwind Northwind_log 1280 Log
This is a Cluster SQl Active/Active and another thing this integrate to
alarm with HP Open View.
Regards
José Júlio duarte
"John Bell" wrote:
> Hi
> Run the script and you will see that it will produce information for each
> file on every database.
> John
> "José Júlio Duarte" wrote:
> > Hello John Bell
> >
> > Like the first script i need all information about all database.
> >
> > Fullfilly
> >
> > José Júlio Duarte
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > If you are doing this per database you may to try something like this to the
> > > the file sizes:
> > > SET NOCOUNT ON
> > >
> > > CREATE TABLE #filesizes ( dbname sysname, [filename] sysname, [sizeKB] int,
> > > type char(4) )
> > >
> > > DECLARE @.name sysname
> > > DECLARE db_cursor CURSOR FOR
> > > SELECT name
> > > FROM master..sysdatabases
> > > ORDER BY dbid
> > >
> > > OPEN db_cursor
> > >
> > > FETCH NEXT FROM db_cursor INTO @.name
> > >
> > > WHILE @.@.FETCH_STATUS = 0
> > > BEGIN
> > > INSERT INTO #filesizes ( dbname, [filename], [sizeKB], [type] )
> > > EXEC ( 'SELECT ''' + @.name + ''', name, size*8, CASE WHEN status&0x40=0x40
> > > THEN ''Log'' ELSE ''Data'' END FROM ['+ @.name + ']..sysfiles' )
> > > FETCH NEXT FROM db_cursor INTO @.name
> > > END
> > > CLOSE db_cursor
> > > DEALLOCATE db_cursor
> > >
> > > SELECT * FROM #filesizes
> > >
> > > DROP TABLE #filesizes
> > > GO
> > >
> > > Another alternative is to set the Alert in the Alerts section found in under
> > > SQL Server Agent, you can set this for each data and log file for each
> > > database using the type: SQL Server Performance Condition Object:
> > > SQLServer:Databases Counter:
> > > Data File(s) Size(KB) or Log File(s) Size(KB) and the instance is each
> > > database.
> > >
> > > John
> > >
> > > "José Júlio Duarte" wrote:
> > >
> > > > Hello John Bell
> > > >
> > > > I need this information by Database?
> > > >
> > > > Fullfilly
> > > >
> > > > José Júlio Duarte
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > You can do this in many ways such as tools such using Perfmon or Microsoft
> > > > > Operations Manager (MOM).
> > > > >
> > > > > Looking at this in a different perspective you could monitor the amount of
> > > > > free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> > > > > use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> > > > > job which you force to fails and so alerts an operator. The same technique
> > > > > used in the stored procedure can be used with sp_helpfile if you want actual
> > > > > size.
> > > > >
> > > > > John
> > > > >
> > > > > "José Júlio Duarte" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > Somebody has some script to make this requirement?
> > > > > >
> > > > > > Databases
> > > > > >
> > > > > > When the space that the file of any one of the databases used for the
> > > > > > Biztalk to reach
> > > > > > a quota of occupation in superior record 80% of its maximum size.
> > > > > >
> > > > > > Fufilly