Monitoring the db size

Hi All,

I'm wonderring if someone has the script which can run on each server to get all dbs size , free space on this server ? Curently I am using the enterprise manager to check the db space usage manually, but this is very frustrated due to a server has many dbs located on it

Thanks,Hi All,

I'm wonderring if someone has the script which can run on each server to get all dbs size , free space on this server ? Curently I am using the enterprise manager to check the db space usage manually, but this is very frustrated due to a server has many dbs located on it

Thanks,

This (http://www.dbforums.com/t1006334.html) might be what you are looking for...

[Edit: some caveats]
1. Consider also using DBCC SHOWCONTIG WITH TABLERESULTS. You could embed this in the sp_MSForEachDB.
2. Note that the data may not match reality; you may need to run dbcc updateusage before you run the sizing extract.
3. YMMV

Regards,

hmscott|||Thanks a lot hmscott, I'll try it. I found a website which has a few scripts , they are very good, for people referecn if need auto manage db size, drive size and free space. It is:

http://www.sqlservercentral.com/columnists/mnash/monitoringdriveanddatabasefreespace.asp

monitoring sql server log

can somebody help me with script to monitor the sql server
log for errors/messages? thanks.use xp_readerrorlog
>--Original Message--
>can somebody help me with script to monitor the sql
server
>log for errors/messages? thanks.
>.
>

monitoring servers

Is there a way (script, tool, whatever) that I can monitor sql servers
and services on multiple different servers. Basically I want on screen
that can display that status of many sql servers and services on
multiple servers.
Thanks !bringmewater@.gmail.com a crit:

> Is there a way (script, tool, whatever) that I can monitor sql servers
> and services on multiple different servers. Basically I want on screen
> that can display that status of many sql servers and services on
> multiple servers.
>
Hello,
I've used Nagios for this purpose. You can have a look at
http://www.babaluga.org/doku.php/sq...r/outils/nagios for some notes
I've taken about how to do it.
Rudi Bruchez, MCDBA
http://www.babaluga.com/|||excellent! thanks
Rudi Bruchez wrote:
> bringmewater@.gmail.com a =E9crit:
>
> Hello,
> I've used Nagios for this purpose. You can have a look at
> http://www.babaluga.org/doku.php/sq...r/outils/nagios for some notes
> I've taken about how to do it.
>=20
> --=20
> Rudi Bruchez, MCDBA
> http://www.babaluga.com/

Monitoring More Than 1 DB's Growth Rate

Hello, Is there a Best Practice or a script I can run daily to monitor the
rate of growth of about 12 databases? Thanks, PanchoHi
Vyas' script may help!
http://vyaskn.tripod.com/track_sql_...file_growth.htm
John
"Pancho" wrote:

> Hello, Is there a Best Practice or a script I can run daily to monitor the
> rate of growth of about 12 databases? Thanks, Pancho|||Thanks, John. I'll give this a try!
"John Bell" wrote:
> Hi
> Vyas' script may help!
> http://vyaskn.tripod.com/track_sql_...file_growth.htm
> John
> "Pancho" wrote:
>

Monitoring Inserted data and comparing against selected data

I made ahuge load script in SQL Server 2000 as i load data from many
tables(select some of each one collumns) into one single table and i
want to test the loaded data against the selected data to make sure
that the loaded data is the same the selected data
is there a code or tool to make this test or monitoring ?? please
urgent ...tamatem wrote:

Quote:

Originally Posted by

I made ahuge load script in SQL Server 2000 as i load data from many
tables(select some of each one collumns) into one single table and i
want to test the loaded data against the selected data to make sure
that the loaded data is the same the selected data
is there a code or tool to make this test or monitoring ?? please
urgent ...

So you did a select ... into query? I'd be curious to know why exactly
you want to compare the two tables. Do you not trust the server to do
it correctly? It isn't really like a file copy or network transfer
where it's subject to errors.|||I agree.

You can trust SQL to do the job correctly or give an error. Just check
for @.@.ERROR to make sure that no error happened. I dont think you would
actually need to check whether the data got inserted properly.

ZeldorBlat wrote:

Quote:

Originally Posted by

tamatem wrote:

Quote:

Originally Posted by

I made ahuge load script in SQL Server 2000 as i load data from many
tables(select some of each one collumns) into one single table and i
want to test the loaded data against the selected data to make sure
that the loaded data is the same the selected data
is there a code or tool to make this test or monitoring ?? please
urgent ...

>
So you did a select ... into query? I'd be curious to know why exactly
you want to compare the two tables. Do you not trust the server to do
it correctly? It isn't really like a file copy or network transfer
where it's subject to errors.

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
Mark
SQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>
|||Hi Mark
"Mark" wrote:

> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script this?
> Thanks in advance.
> Mark
>
You may want to look at
http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx
John

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
MarkSQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>|||Hi Mark
"Mark" wrote:
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script this?
> Thanks in advance.
> Mark
>
You may want to look at
http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx
John

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
MarkSQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>|||Hi Mark
"Mark" wrote:

> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script thi
s?
> Thanks in advance.
> Mark
>
You may want to look at
[url]http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx[/u
rl]
John

Monitoring failovers on clustered servers

I've been asked to write a script to monitor whether a clustered server is up and alive and if so which node it's actually running on. Apparently there's been some problems of failover to the passive server without anyone knowing that it happened and they want to know. Any suggestions?The script below will result in capturing the currently active node:

begin tran
create table #tmp (hostname varchar(128) null)
commit tran
insert #tmp exec master.dbo.xp_cmdshell 'hostname' , no_infomsgs
select * from #tmp where hostname is not null
go
drop table #tmp
go|||We're in an active/passive environment, so when I run this script I just get the virtual servername back.

Originally posted by rdjabarov
The script below will result in capturing the currently active node:

begin tran
create table #tmp (hostname varchar(128) null)
commit tran
insert #tmp exec master.dbo.xp_cmdshell 'hostname' , no_infomsgs
select * from #tmp where hostname is not null
go
drop table #tmp
go|||And what do you get when you run HOSTNAME from command line? I am getting the active node name. In fact, I went to our lab environment and ran it, then I forced a failover and got the other node's name - which proves that it workes as expected in identifying the active node.|||From the command line I do get the active server name, but when I run that script I get the virtual server name.

Originally posted by rdjabarov
And what do you get when you run HOSTNAME from command line? I am getting the active node name. In fact, I went to our lab environment and ran it, then I forced a failover and got the other node's name - which proves that it workes as expected in identifying the active node.|||SQL Service account should belong to local administrators group on the box.|||Yes, it does.

Originally posted by rdjabarov
SQL Service account should belong to local administrators group on the box.|||When you were testing the script, did you run it from QA or scheduled it as a job? As I said, I ran this script in both prod and lab environments, and even did a failover on the lab cluster, and hostname reported the correct active node. Anyone else sees something I missed?|||I've done it both ways - both through QA then through a ksh script and I'm still getting the virtual server name back. How odd!

Originally posted by rdjabarov
When you were testing the script, did you run it from QA or scheduled it as a job? As I said, I ran this script in both prod and lab environments, and even did a failover on the lab cluster, and hostname reported the correct active node. Anyone else sees something I missed?|||Yup, it sure is.

Monitoring disk space

I am trying to write a script to properly monitor disk space for MSSQL
server.
Is there a stored procedure that tells me the separate space usage of
the database and the log?
The procedure sp_spaceusage seems to give an "overall" figure.
However, is it not possible (albeit unwise) that the user could create
the log on a separate filesystem, maybe even with a fixed file size,
so the log could potentially run out of space whilst there is still
space available overall.
Any thoughts?
thanks,
NeilConsider a combination of DBCC SQLPERF(LOGPSACE) and DBCC SHOWFILESTATS. The later is not documented
so use at your own risk...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:3631594f-2ecb-44db-8dd5-c0e1346ed7e4@.m34g2000hsb.googlegroups.com...
>I am trying to write a script to properly monitor disk space for MSSQL
> server.
> Is there a stored procedure that tells me the separate space usage of
> the database and the log?
> The procedure sp_spaceusage seems to give an "overall" figure.
> However, is it not possible (albeit unwise) that the user could create
> the log on a separate filesystem, maybe even with a fixed file size,
> so the log could potentially run out of space whilst there is still
> space available overall.
> Any thoughts?
> thanks,
> Neil|||> Consider a combination of DBCC SQLPERF(LOGPSACE) and DBCC SHOWFILESTATS. The later is not documented
> so use at your own risk...
>
Thanks, that got it.
I am using DBCC SQLPERF(LOGSPACE) and a query on the SYSFILES table.

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.
Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegr oups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>
|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegr oups.com...
>

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>>I want an SQL script that will monitor free space on certain drives of
>> my SQL2000 server.
>> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
>> a
>> SELECT freespace FROM xxxx WHERE (mydrive='C')
>> method that I can use?
>>
>> TIA
>> Dave.
>

Monitor database not backup

Hello,

I'm monitor my SQL 2005 using MOM 2005.

I would like to create a VB script to get database list that are not backup in the last X days.

how can I do that ?

http://www.extremeexperts.com/SQL/Scripts/Lastbackup.aspx fyi.|||

Thanks,

How do I get a list of SQL instance in order to monitor all the instance I have

Also I think this not retun database that are never backup

|||

isql -L

Lists the locally configured servers and the names of the servers broadcasting on the network.

MOM blocking analysis

MOM contains its own Block Analysis script. It is a script written on
VB, that looks for the monitored servers, creates for each one
MomCreateObject("SQLDMO.SQLServer"), afterwards connects to each
database on each server and executes SELECT GETDATE() query. If MOM
doesn't receive answer for 6 minutes, it sends alert. Several times
since I started to monitor my servers I received the next alert:

The program "SQLDMO_789" has been blocked for 6 minutes on database
BurstingDataWarehouse in the SQL instance MSSQLSERVER. The defined
acceptable blocking threshold is 1 minute(s). "SQLDMO_789" is running
on SPID 134 as login NT AUTHORITY\SYSTEM and is blocked by SPID 133.
The resource id is KEY: 10:2:1 (a2007950f190)

SQLDMO_789 - is the MOM itself (number varies from time to time). 10 -
is BurstingDataWarehouse database. As far as I can judge, MOM connects
to the server succesfully (otherwise, how can it know SPID?) The server
itself worked fine at that time - nothing unusual, all the jobs
finished succesfully including the heavy ones. What can block SELECT
GETDATE() query for 6 minutes?I had a similar issue and it turned out that some scrappy application
held thousands of locks on the tempdb. Run sp_who2 in combination with
sp_lock to see which process is blocking the MOM agent.

M