Issue Of Blank Emails When Checking For Free Space On Disk Of Servers
Sep 22, 2006
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.
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.
insert into #FreeSpace exec xp_fixeddrives -- Free Space on F drive Less than Threshold if @MB_Free < 4096 exec master.dbo.xp_sendmail @recipients ='dvaddi@domain.edu', @subject ='SERVER X - Fresh Space Issue on D Drive', @message = 'Free space on D Drive has dropped below 2 gig' drop table #freespace
-- Initialize Control Mechanism DECLARE@Drive TINYINT, @SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area DECLARE@Drives TABLE ( Drive CHAR(1), Info VARCHAR(80) )
WHILE @Drive <= 122 BEGIN SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives ( Info ) EXEC(@SQL)
UPDATE@Drives SETDrive = CHAR(@Drive) WHEREDrive IS NULL
SET@Drive = @Drive + 1 END
-- Show the expected output SELECTDrive, SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes, SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes, SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes FROM( SELECTDrive, Info FROM@Drives WHEREInfo LIKE 'Total # of %' ) AS d GROUP BYDrive ORDER BYDrive
I need to check the space available on a specific disk (D on a remote server, again, this task will be executed from an SSIS package, if I have less than 60GB available, I have to delete some files.
How would you guys do this using VB.NET? If I had SQL Server installed on that box, I could achieve this executing a DOS command... But I don't...
does anyone know if tempdb can be physically moved to a different partition on a disk drive on SQL Server 7.0? Since it can't be backed up I'm hesitant to use the sp_detach/sp_attach procedure because I don't want to crash it. If nothing else is available, I can attempt moving it this way at the end of the day and then just reboot to get tempdb back up again if the server fails, but I'd really appreciate a suggestion from someone who has more know-how than I do about system table operations. Thanks again
What is the percentage of FREE disk space that is needed for a backup? I have backups that are failing with no disk space errors. But there is enough disk. Does SQL Server need a percentage of free space all the time?
I was wondering if anybody has the script to view the free disc space. Currently I am using xp_fixeddrives stored procedure, but it is undocumented and might not be supported in SQL Server 2005.
I would like to know how to determine how big log and data space is, and how much of this space is free. I would like to create a script to warn me when less than 20% free space is left in log as well as data.
I've gone through the forum and have seen several others with a similar situation. I recently noticed my .mdf file grew to 200GB yet the data in the file is only about 40GB. I run sp_spaceused and get the following: database_size = 176GB, unallocated space = 134GB, reserved = 43GB, data = 17GB, index_size = 19GB, unused = 6GB.
I've tried "dbcc shrinkdatabase (mydb, truncateonly)", tried restarting server, tried running a maintenance plan including a reindex of the database, and I don't know what to do next.
While I am importing few data to my SQL Server (2000) database, the free space is gettig increased in GBs. The database's File Growth is 1 MB (same for both Data Files and Transaction Files). The same database I restored on another PC and did the same process, it is working fine as there is no enormous growth in Free Space.
My disk drive was full. So I deleted some records. Then I tried to shrink and it indicated available free space of around 5GB as indicated in the below image.
Once I ran the shrink for data file, though it completed successfully I didn't get the 5GB space as indicated. For log file shrink it shows available space of 200MB and when I run a log file shrink it does free up 200MB as indicated. So why is it not working with data file shrink? What am I doing wrong?
I have some space available in the database, I tried dbcc shrink database and srrink file. I am not getting the disk space. But the amount of free space on the database sometime get increased.
I have a database with around 2 GB space for the data and 5.8 GB for the transaction log . Now the problem is i do not have any more space on the system and data files requires more space than 2 G.B to execute some stored procedures .
Is it possible to decrease some space of the transaction log , say from 5.8 GB to 2 GB and allocate it to the data files . My data and log files are on different drives . I did not find anything related to this topic in the BOL .
Can somebody help me with this problem ? Anthing related to this issue will be of great help to me since i have no expertise in this field .
I'm trying to calculate how much unused space i have on one datafile. My main goal is to determine the max space i can save by doing a dbcc shrink. Any help is greatly appreciated.
I inherited a SQL server (2005) that sits on a Windows 2003 box (upgrading to 2008 R2 and Win2008, ASAP) and on our SQL Data Drive (260GB - 22GB free) we have 16 databases (that are associated with our primary application). One database in particular is causing me quite a bit of pain - on Tuesdays. This table will consume all available free space (save 700kb) and place it within it's 'reserved' space. It's not being used and I can reclaim it by shrinking. I have done this for 3 consecutive weeks now and I have always thought that shrinking is not really a best practice.
Based on a job I scheduled to spit out various database stats I discovered the following:
Somewhere after 11pm on Monday (and on or before 11:30pm) the reserved space goes from 9GB down to 23 then 37MB on this particular database.
After 1:30am on Tuesday (and on or before 2:00am) the database consumes nearly all available disk space and thus expands its reserved space to 28GB
I believe it has something to do with the Indexes being rebuilt - per the Maintenance Plan the Index Rebuild is supposed to occur every Monday @ 11pm. On 6/18 it ran from 11pm - 11:44pm with no problems. Starting on 6/26 (and subsequently 7/2 and 7/9) they have started at 11 and ended anywhere between 2 - to 3 hours later. And with the following error:
Failed-1073548784) Executing the query "ALTER INDEX [PK_activityLog] ON [dbo].[activityLog] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF ) " failed with the following error: "Could not allocate a new page for database 'FA_PROD_SDDS' because of insufficient disk space in filegroup 'PRIMARY'.
Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The database 'FA_PROD_SDSS' is the one that I am having issues with.
Hi, In sql 7.0 , i would like to create a database with the size of 10Gb, in my server couple of databases already exist. How do i know how much free space is there in File group. we are having only one file group i.e PRIMARY. Could anyone pls tell me about this. Thank u.
I saw something strange this morning that I can not work out in my head.
Customer contacted us saying that the login timeout for one of our web apps was timing out.
The login does a simple check of user name and password against a table and inserts the event into a tracking table. I traced the login routine and the query took zero seconds in the QA on the db server. Everything looked fine in the Task Manager and the Performance monitor except that the disk que spiked out when the login page was used. I pinged between the web and database server and that was fine. The disk had plenty of free space for the log and the mdf files. The db had about of 15mb of unallocated space. Nothing seemed to work until I allocated another 100 MB to the db on their test site. Suddenly no more time out. I tested the live site again and it was still happening (same db and web server). Bumped up the db size on the live site and boom no more problem.
The problem is "fixed" for the moment but I can not seem to reason why so I can actually fix the problem in the long run. I do not know why a simple little password routine would care about the difference between 15mb of unallocated space and 115 mb of unallocated space.
Having just archived quite a bit of data from the main Production DB, I now have around 15% free, reclaimable space sat in the data file.
I'm reluctant to run DBCC SHRINKFILE as that apparently causes a lot of Index fragmentation which will cause issues for performance - how else can the space be allocated back to the OS?
We've got an alert setup on our production database to warn us when the log file(s) exceeds 7 gigs. The Alert is triggering:
"The SQL Server performance counter 'Log File(s) Size (KB)' (instance 'Lexus') of object 'SQLServer:Databases' is now above the threshold of 7000000.00 (the current value is 7057656.00)."
However, according to the file system, the database properties screen files, and properties tabs, the log files combined are at under 5 gigs so this alert should not be going off.
The scary part is, when going to right-click on the database, choosing "tasks" and going to "Shrink > Files", the "Free Space" shows negative numbers for the first log file:
Log 1 Currently Allocated space: 118.69 MB Available Free Space: -5323.24 MB (-4485%)
Log 2 Currently Allocated space: 4853.13 MB Available Free Space: 411.20 MB (7%)
Has anybody ever run into this? Should I be worried that there is a bigger issue at hand?