I'd like to find out how people handle monitoring the disk space used and available per database across all of their databases and severs. The information returned in sp_spaceused is what I'm looking for, but it is for one databases only. How is everyone managing many databases on many different severs?
Are there scripts or tools available for this? Has anyone written any vb code using SQL-DMO?
Thank you for your help...
Hi
I have 600 instances on my network .... I need to monitor the Disk Space ... how should I do that I mean the best way for this would be ???
By Disk Space I mean the SQL Instance should atleat have 10% of free disk space ... If it is less , maybe an alert can be sent or something of that sort .
Now it would be a pain configuring alerts on each machine.
Is there some good way to this
Thanks in advance.
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?
View 9 Replies View RelatedHi all,
I've been digging around the last few days, looking for the best way to monitor all the jobs on all my prod servers. Using EM or SSMS is BS. I've been thinking that perhaps the best strategy is to write or modify a script that gathers information from sysjobs and sysjobhistory on all servers and then inserts it into my main DBA database on my "admin" server. Of course this script would have to be on all servers and be in a scheduled job itself. I'm thinking that I should probably categorize every job in the company and break out my reports that way too. I'll probably throw all the data into a cube so I can do some at a glance checks to see that everything is running ok. I'd like to trend run times with the data too.
Anyway as with most fun projects there are many different way to accomplish this. I've hit on one above and I've seen several others. What are some of the solutions you guys have come up with, what have you tried that didn't work so well and what are some pitfalls that I should avoid in setting this up?
Cat
Hi All,
I am doing some design work on a new system. The plan is to use clustered servers accessing the same backend instance. I want to prepoulate the table with our unique id (for say 5M rows for sake of discussion). Where I am puzzled is how do I guarantee unique ids to the application when both servers are looking for the same next available row? The sql could be something like
select min(id) from table where user_id is null
The trouble our current system runs into is that there are occasions where the users generate the same id which of course messes up the application.
Is this a case where a store procedure would do the insert and return the id to the user is a better solution?
Thanks
Hello
I have got a script which checks the percentage of free space on the drivers of the servers and mails the DBA when it falls below a certain percentage , which can be set according to our requirements.
But I am getting blank emails even when , there is no issue of low free space.
Please help me out.
The code , 1st part of it is:
use master
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create PROCEDURE usp_diskspace
@Percentagefree int,
@error2 varchar(8000) OUTPUT
AS
SET NOCOUNT ON
DECLARE @hr int, @fso int, @drive char(1), @odrive int, @TotalSize varchar(20), @MB bigint , @COUNT int, @Maxcount int,@error varchar(700), @errordrive char(1),@errortotalspace varchar(20), @errorfreespace varchar(20), @free int, @date varchar(100), @query varchar(1300)
SET @MB = 1048576
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB, ServerName = replace( @query , 'master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
set @maxcount =(select max(id) from #drives)
set @count=1
while @count <=@maxcount
begin
select @errortotalspace =convert(varchar(20),Totalsize), @errorfreespace =freespace, @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),@errordrive=Drive from #drives where id = @count
if @free<@percentagefree
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB ate =' +@date
set @error2=@error2+@error+char(13)
end
else
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB ate =' +@date
end
set @count=@count+1
end
DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The 2nd step which sends the mail is :
set nocount on
declare @msg varchar(8000)
declare @minimumspace int
set @minimumspace = 10
set @msg = 'Running out of Hard Disk space on the Server: '+@@servername
exec usp_diskspace @minimumspace,@msg OUTPUT
print @msg
if @msg is not null
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'fromaddress',
@TO = N'toaddress',
@server = N'smtp address',
@subject = N'Free Space of Drivers Test sqlserver2000!',
@type = N'text/html',
@message = @msg
How can I change to get the mail only when there is a free space issue.
Thanks
Hello
I have got a script which checks the percentage of free space on the drivers of the servers and mails the DBA when it falls below a certain percentage , which can be set according to our requirements.
But I am getting blank emails even when , there is no issue of low free space.
Please help me out.
The code , 1st part of it is:
use master
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create PROCEDURE usp_diskspace
@Percentagefree int,
@error2 varchar(8000) OUTPUT
AS
SET NOCOUNT ON
DECLARE @hr int, @fso int, @drive char(1), @odrive int, @TotalSize varchar(20), @MB bigint , @COUNT int, @Maxcount int,@error varchar(700), @errordrive char(1),@errortotalspace varchar(20), @errorfreespace varchar(20), @free int, @date varchar(100), @query varchar(1300)
SET @MB = 1048576
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB, ServerName = replace( @query , 'master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
set @maxcount =(select max(id) from #drives)
set @count=1
while @count <=@maxcount
begin
select @errortotalspace =convert(varchar(20),Totalsize), @errorfreespace =freespace, @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),@errordrive=Drive from #drives where id = @count
if @free<@percentagefree
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB :Date =' +@date
set @error2=@error2+@error+char(13)
end
else
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB :Date =' +@date
end
set @count=@count+1
end
DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The 2nd step which sends the mail is :
set nocount on
declare @msg varchar(8000)
declare @minimumspace int
set @minimumspace = 10
set @msg = 'Running out of Hard Disk space on the Server: '+@@servername
exec usp_diskspace @minimumspace,@msg OUTPUT
print @msg
if @msg is not null
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'fromaddress',
@TO = N'toaddress',
@server = N'smtp address',
@subject = N'Free Space of Drivers Test sqlserver2000!',
@type = N'text/html',
@message = @msg
How can I change to get the mail only when there is a free space issue.
Thanks
We are in plan to build a Monitoring tool using PowerShell and Performance Monitor which could monitor 10 to 20 servers. Do you have any reference of any existing tool using Performance Monitor to monitor the SQL Server and available for free? I didn't want to put some effort, if something is available already.
View 2 Replies View RelatedHi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedHello,
Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?
3700 MB allocated but only 100 MB used and 3600 MB are free !
Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)
Thank you for your precious help !
Khaix from Brussel.
How do we suppress multiple spaces to a single space in T-SQL
E.G.
Field: FullName
e.g.
WOMENS HEALTH RIVER VALLEY
JOHN FAMILY MED GROUP
HERSH STWEART P.
PARK HEIGHTS MEDICAL CENTER
KOPP WHITEFIELD E
The o/p wanted is
HERSH STWEART P.
Thank you.
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
Thanx,
Darren
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server.
2) Linking x64 or IA64 servers to x86 servers.
3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are:
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure".
(Microsoft SQL Server, Error: 10054)
------------------------------
And
------------------------------
The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB".
OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification".
(Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
Hello,
Is there a way I could get the total cpu usuage of the box other than Task Manager and Profiler
Thanks
Rea
I understand the rule of thumb that the CPU should not be over 90%. If you take the four counters (%processor time,%privileged time, %user time, %interrupt time, and interrupt seconds), what combination gives you your CPU
time ?
Hello,
I am looking for a reliable and easy manner to poll a domain for any box running the sql server service. Our current monitoring tool doesn't do this.
Many thanks!
I have been asked to monitor SQL to tell me when we are performingbetter than others. Can anyone tell me what kinds of scheduled jobs orscripts they utilize?
View 1 Replies View RelatedHi ,Is there a way/tool in Sql Server 2000 SP3 tomonitor all activities going on in the Database ?For example, I first create an empty database.Then I have an ERWIN generated DDL to createall views and tables. After that, I have INSERTscripts that populate all the base tables. What Iwant to monitor is success or failure for eachscript.Thanks,N.
View 2 Replies View RelatedI made some copy of table and I have this error but on my hard disk i have 4 gig of empty space.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Backup_Date_11_24_00_Time_9_08_34_AM' in database 'LogActiviteIntramedia' because the 'PRIMARY' filegroup is full.
/Intranet_API/Forms/videTableLog.asp, line 16
My question is how can I increase the space of primary filegroup?
Thanks and have a good friday
Hi,
I have implemented health monitoring for my web-site, using the SQL provider.
Health monitoring works fine when the website is run from VS2005, using the built in web server, all the expected events are inserted into the aspnet database. However when I deploy the site onto IIS, no events are ever inserted into the database.
I would appreciate some help figuring out why this is happening! The code that implements the health monitoring in my web.config file is:1 <healthMonitoring
2 enabled="true"
3 heartbeatInterval="0">
4 <bufferModes>
5
6 <remove name="Analysis"/>
7
8 <add name="Analysis"
9 maxBufferSize="10"
10 maxFlushSize="2"
11 urgentFlushThreshold="2"
12 regularFlushInterval="00:00:02"
13 urgentFlushInterval="00:00:01"
14 maxBufferThreads="1"/>
15
16 </bufferModes>
17
18 <providers>
19
20 <remove name ="SqlWebEventProvider"/>
21
22 <add name="SqlWebEventProvider" type="System.Web.Management.SqlWebEventProvider,
23 System.Web,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a"
24
25 connectionStringName="SQL_ASPNET"
26 maxEventDetailsLength="1073741823"
27 buffer="true"
28 bufferMode="Analysis"
29
30 />
31
32 </providers>
33
34 <eventMappings>
35
36 <remove name ="All Events"/>
37 <add name="All Events"
38 type="System.Web.Management.WebBaseEvent, System.Web,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a"/>
39
40 </eventMappings>
41
42 <profiles>
43
44 <remove name="Default"/>
45 <add name="Default"
46 minInstances="1"
47 maxLimit="Infinite"
48 minInterval="00:10:00"
49 />
50 </profiles>
51
52 <rules>
53
54 <add name="All Events"
55 eventName="All Events"
56 provider="SqlWebEventProvider"
57 profile="Default"
58 minInterval="00:00:01"
59 minInstances="1" />
60
61 </rules>
62
63 </healthMonitoring>
64
Thank you in adance!
Jon
Can anyone show me in SQL7 how to obtain Available Space on a particular filegroup in a database (not the database or datafile).
I am trying to include this in a script to monitor my database which uses Filegroups and I have every other info that I need (from the sysfiles table) except the available space. Thanks in advance!
Hello,
I do all my monitoring locally for disk space, locks, blocking, I've 10 production servers, We need to centralised the monitoring server so from one server all the monitors can be done. Does anyone has any ideas how memory, cpus consumption, disk space, all alerts, locks, blocking, log space and job completition monitoring can be handled.
Any idea or guidance is appreciated
Thanks
Roma
In Sql 7 What is the easiest way to monitor the number of connections? I have been asked to create a report that monitors the number of logins every hour.
Please Advise, and thanks in Advance.
Hi,
I am new to sql server and sould like to know how I can monitor the server performance on sql servers 6.5 and 7. In sybase we can run sp_sysmon. Is there anything similar to this for sql server.
Thanks.
Hi
I am learning SQL SERVER ..Can anyone tell me how do I tune a given stored procedure in 6.5 and 7.0.?
In performance tunning which counters are really necessary to watch and the remedies to be taken?
Thanks
Reddy
Folks!
Is it possible to monitor several SQL servers in one window and notify operator about error messages.
May be some new software can make it possible?
Thank you.
Can anybody help me with the following on my MS SQL Server 2000 database.
1. All tables should have a lastModificationDate column. Any changes and inserts should have the system time updated with a trigger or so. We shouldn’t be inserting the value using SQL statements into this column.
2. There shouldn’t be any deletes on the table. Any deleted records should be marked as inactive or deleted, so it won’t come in queries, but should be physically present in the tables.
3. A modification log table, which will carry the table name, the column identifier, user modified, old value and the timestamp.
Thanks
Kishore
I would just like to know what everyone uses to monitor SQL usage? We have a SQL 2000 server that already has several applications sharing it and everyone wants to keep forcing more onto it.
I want to be able to judge when this server has reached it's capacity or how much more it can allow. Can SQL profiler alone do this for me?
Thanks.