August 27, 2008 at 12:27 am
Have you ever tried the free tool "SQL DBA Dashboard"? You might find the information there.
Download it from here http://www.sqlserverexamples.com/v2/Products/tabid/76/Default.aspx
August 27, 2008 at 6:40 am
DTS Package for Reporting Low Disk Space
This VBscript code will check for low disk space on the local SQL Server. Schedule the package to run once a day, and let the job report to you by e-mail when a drive runs low on free space.
The script will check all disks except for the C:\ drive and will trigger an alert to be sent when the available disk space on any drive falls below 20% remaining. The code uses the WMI object library which comes with Windows 2000 and XP. WMI can also be installed on NT.
Author: Lennart Gerdvall
Function Main()
REM WHAT IT DOES FOR YOU:
REM This code will check for low disk space on the local SQL Server.
REM Schedule the package to run once a day and let the job report to
REM you by e-mail when it fails. The code below checks all disks but
REM the C-disk and fails when disk space is below 20% remaining.
REM HOW TO INSTALL IT:
REM Create a DTS package with the VB cript code below.
REM Set up a connection to the local server with Windows Authenication.
REM The Windows user running the package must be admin on the machine being checked.
REM OTHER REQUIREMENTS:
REM The package requires Windows Management Instrumentation (WMI), which is
REM included in Windows 2000 and later. If you have Windows 95/98/NT 4.0, install
REM the WMI core library (wmicore.exe).
REM More information - http://msdn.microsoft.com/downloads/sdks/wmi/default.asp
DIM DiskSet
DIM myDisk, test
myDisk = ""
test = 0
Set DiskSet = GetObject("winmgmts:{impersonationLevel=Delegate}").ExecQuery("select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3")
For Each Disk In DiskSet
if disk.name <> "C:" then
test = (Disk.FreeSpace / Disk.Size)
If test <= 0.20 then
myDisk = myDisk + Disk.Name
End If
end if
Next
If myDisk <> "" Then
REM Yes, there are disks with LOW SPACE
Main = DTSTaskExecResult_Failure
Else
REM No, there no are disks with LOW SPACE
Main = DTSTaskExecResult_Success
End If
End Function
August 27, 2008 at 3:17 pm
Very good...I tested and it works...thanks for your inputs...:cool:
August 27, 2008 at 3:22 pm
very good---i tested and it works....thanks for your inputs....:D
August 27, 2008 at 3:54 pm
send me dts package that you develop on my input,
i will give it to my friends..........
September 5, 2008 at 2:55 pm
rbarryyoung (8/26/2008)
10. Click the Configure... button and select the System DSN that points to your Server & Database.
What is a System DSN... Are you talking ODBC?
I would like for it to be a little more fluid than that. I don't want to create 30 ODBC connections on a server.
I have a SQL Server table, containing all of the server addresses. Can I do something that is based on that?
September 5, 2008 at 3:04 pm
That is where it logs To: you can monitor all 30 servers and log them all to the same place if you want.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 3:11 pm
How do I create an ODBC connection again? 🙂
September 5, 2008 at 3:17 pm
It's under control panel; Administrative tools; Data Sources.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 3:35 pm
I set the ODBC connection up.
I went to run it and got an error.
Need to run; will try it later.
Thanks a lot for helping.
September 7, 2008 at 8:24 am
Jason Wisdom (9/5/2008)
I set the ODBC connection up.I went to run it and got an error.
Need to run; will try it later.
Thanks a lot for helping.
If you want to enable CLR on your instance - and that's a big IF - I have a solution that might suit your needs.
C# code:
using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using Microsoft.SqlServer.Server;
namespace SQLCLR
{
public sealed partial class FileIO_PermSet_UNSAFE
{
[Microsoft.SqlServer.Server.SqlFunction]
public static Int64 GetShareAvailableFreeSpace(string share)
{
using (SqlConnection conn = new SqlConnection("context connection = true"))
{
try
{
string arg = @"use B: " + share;
Process p = Process.Start("net.exe", arg);
p.WaitForExit();
DriveInfo di = new DriveInfo("B");
return di.AvailableFreeSpace;
}
catch (Exception e)
{
SendError(e);
return -1;
}
finally
{
// Delete mapped drive
Process p = Process.Start("net.exe", @"use B: /DELETE");
p.WaitForExit();
}
}
}
private static void SendError(Exception e)
{
SqlContext.Pipe.Send("CLR Error :");
SqlContext.Pipe.Send(e.Message);
}
}
}
Generate the dll in Visual Studio.
Then create the assembly etc. like this:
-- Script to install CLR stored procedure for File-System Operations
-- Drop existing sproc and assembly if any.
USE dbName
GO
-- Drop pre-existing objects if any:
IF EXISTS (SELECT * FROM sys.objects WHERE [name] = 'CLR_udf_GetDriveFreeSpace')
DROP FUNCTION dbo.CLR_udf_GetDriveFreeSpace;
GO
-- Drop assembly:
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'FileIO_PermSet_UNSAFE')
DROP ASSEMBLY FileIO_PermSet_UNSAFE;
GO
use [master]
go
-- Drop login mapped to asymmetric key:
IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'FileIO_PermSet_UNSAFE_Login')
DROP LOGIN FileIO_PermSet_UNSAFE_Login;
GO
-- Drop asymmetric key:
IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'FileIO_PermSet_UNSAFE_Key')
DROP ASYMMETRIC KEY FileIO_PermSet_UNSAFE_Key;
GO
-- Create asymmetric-key login and asymmetric key
-- (enter correct path of dll - must be local to server instance on which you are doing this install):
CREATE ASYMMETRIC KEY FileIO_PermSet_UNSAFE_Key FROM EXECUTABLE FILE = 'd:\SQLCLR\FileIO_PermSet_UNSAFE.dll';
CREATE LOGIN FileIO_PermSet_UNSAFE_Login FROM ASYMMETRIC KEY FileIO_PermSet_UNSAFE_Key
GRANT UNSAFE ASSEMBLY TO FileIO_PermSet_UNSAFE_Login
GO
USE dbName
GO
-- Create the assembly:
CREATE ASSEMBLY FileIO_PermSet_UNSAFE
FROM 'D:\SQLCLR\FileIO_PermSet_UNSAFE.dll' -- Enter correct path of dll (must be local to server instance on which you are doing this install)
WITH permission_set = UNSAFE ;
GO
-- Create the database objects:
--CLR_udf_GetDriveFreeSpace
CREATE FUNCTION dbo.CLR_udf_GetShareFreeSpace
(
@Share NVARCHAR(255)
)
RETURNS BIGINT
AS EXTERNAL NAME FileIO_PermSet_UNSAFE.[SQLCLR.FileIO_PermSet_UNSAFE].GetShareAvailableFreeSpace;
GO
USE [master];
GO
Then use like this:
select dbName.dbo.CLR_udf_GetShareFreeSpace('\\...\...\ ')
The advantage of this solution is that, after the initial implementation, getting the information you want requires just this 1 line of code.
The disadvantage is that, by necessity, the assembly is on the UNSAFE permission set, meaning that you need to ensure only processes that are "fully trusted" make use of it; in our shop, only DBAs can use it.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 7, 2008 at 9:42 am
For this type of process, I use a powershell script that runs on my central management server. The powershell script opens a connection to the database on that server, reads from the list of defined SQL Servers and then queries each SQL Server for various information including space utilization.
Here is an example script:
param(
$inputServers
)
$errorActionPreference = "SilentlyContinue"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.SMO") | Out-Null
"$(Get-Date): Starting script";
$dbadb = (New-Object "Microsoft.SqlServer.Management.Smo.Server" "**cmserver**").Databases["**cmdatabase**"];
$ignoreDatabases = "tempdb", "model", "pubs", "Northwind", "Quest", "AdventureWorks", "AdventureWorksDW";
if ($inputServers -eq $empty) {
$servers = $dbadb.ExecuteWithResults("Select ServerName
From dbo.SQLServerInfo
Where SystemStatus = 'Active'").Tables[0]
} else {
$inputServers = ($inputServers | % {"'$($_)',"});
$servers = $dbadb.ExecuteWithResults("Select ServerName
From dbo.SQLServerInfo
Where SystemStatus = 'Active'
And ServerName In ($inputServers'NoServerMatch')").Tables[0]
}
& { $servers | % {
Trap [System.UnauthorizedAccessException] {
continue;
}
Write-Output "$(Get-Date): Processing Server...$($_.Servername)";
$sql = New-Object ('Microsoft.sqlserver.management.smo.server') $_.ServerName
$sqlNetName = $sql.Information.NetName;
if ($sqlNetName -eq $null) {
Write-Output "Error connecting to SQL Server: $($_.ServerName)";
return;
}
$os = Get-WMIObject -class "Win32_OperatingSystem" -namespace "root\CIMV2" -computername $sqlNetName
$comp = Get-WMIObject -class "Win32_ComputerSystem" -namespace "root\CIMV2" -computername $sqlNetName
# Start building the query to update this server
$query = "
-- Updating Server information...
Update dbo.SQLServerInfo
Set Product = 'SQL Server $(switch ($sql.Information.Version.Major) {10 {2008} 9 {2005} 8 {2000} default {7}})'
,Edition = '$($sql.Information.Edition)'
,ProductLevel = '$($sql.Information.ProductLevel)'
,VersionString = '$($sql.Information.VersionString)'
,Caption = coalesce(nullif('$($os.Caption)', ''), Caption)
,CSDVersion = coalesce(nullif('$($os.CSDVersion)', ''), CSDVersion)
,Model = coalesce(nullif('$($comp.Manufacturer + ' ' + $comp.Model)', ''), Model)
,PhysicalMemory = '$($sql.Information.PhysicalMemory)'
,Processors = $($sql.Information.Processors)
,IsClustered = '$($sql.Information.IsClustered)'
Where ServerName = '$($sql.Name)';`n
-- Delete previous Database Information for this server
Delete From dbo.DatabaseInfo Where ServerName = '$($sql.Name)'`n";
if ($sql.Status -eq "Offline, Shutdown, AutoClosed") {
Write-Output "`tDatabase $($_.Name) is '$($_.Status)' - cannot update information."; return;
}
$sql.Databases | % { if (!($ignoreDatabases -contains $_.Name)) {
trap [Microsoft.SqlServer.Management.Common.ExecutionFailureException] {
Write-Output $("Exception: $($_.Exception.Message)");
Write-Output $("Base Exception: $($_.Exception.GetBaseException().Message)");
return;
}
if ($_.Status -eq "Offline, AutoClosed") {
Write-Output "`tDatabase $($_.Name) is '$($_.Status)' - cannot update information."; return;
}
$logSize = 0; $_.LogFiles | %{$logSize += ($_.Size / 1024)};
$dbSize = 0; $_.FileGroups | %{$_.Files | %{$dbSize += ($_.Size / 1024)}};
if ($dbSize -eq 0) {$dbSize = $_.Size};
$query += "
-- Insert Database Information
Insert Into dbo.DatabaseInfo
(ServerName
,DatabaseName
,DatabaseId
,Owner
,CreateDate
,CompatibilityLevel
,LastBackupDate
,RecoveryModel
,DatabaseSize
,LogFileSize)
Values ('$($sql.Name)'
,'$($_.Name)'
,$(if ($_.Id) {$_.Id} else {999})
,'$(if ($_.Owner) {$_.Owner} else {'Unknown'})'
,'$($_.CreateDate)'
,'$($_.CompatibilityLevel)'
,'$($_.LastBackupDate.ToString('s') -replace '0001-01-01', '1900-01-01')'
,'$($_.DatabaseOptions.RecoveryModel)'
,$dbSize
,$logSize);
-- Insert Data Space usage
Delete From dbo.DataSpaceUsage
Where ServerName = '$($sql.Name)'
And DatabaseName = '$($_.Name)'
And IndexedDTTM = dateadd(day, datediff(day, 0, getdate()), 0);
Insert Into dbo.DataSpaceUsage
(ServerName
,DatabaseName
,DatabaseSize
,DataSpaceUsage
,IndexSpaceUsage)
Values ('$($sql.Name)'
,'$($_.Name)'
,$($dbSize * 1024)
,$(if ($_.DataSpaceUsage) {$_.DataSpaceUsage} else {0})
,$(if ($_.IndexSpaceUsage) {$_.IndexSpaceUsage} else {0}));`n"
# Get backup history for this database
$backupHistory = $sql.Databases["msdb"].ExecuteWithResults("
Select database_name
,type As BackupType
,backup_size
,backup_start_date
,backup_finish_date
From msdb.dbo.backupset
Where database_name = '$($_.Name)'
And backup_start_date >= (select min(backup_start_date)
from (select top 5 backup_start_date
from msdb.dbo.backupset
where database_name = '$($_.Name)' and type = 'D'
Order By backup_start_date desc) h)").Tables[0]
$backupHistory | % {
$query += "
-- Insert Backup History
Insert Into dbo.BackupHistory
(ServerName
,DatabaseName
,BackupType
,BackupSize
,StartDate
,FinishDate)
Values ('$($sql.Name)'
,'$($_.database_name)'
,'$($_.BackupType)'
,$($_.backup_size / 1024)
,'$($_.backup_start_date)'
,'$($_.backup_finish_date)');`n"
}
}
}
$query += "
-- Purge data space usage
Delete From dbo.DataSpaceUsage
Where CollectedDTTM <= dateadd(year, -1, getdate());`n"
$query > ".\Logs\GetSQLServerInfo_$($sql.Name -replace '\\', '_').sql.log";
&{$dbadb.ExecuteNonQuery($query)} #comment out this line to build the sql script only
trap [Microsoft.SqlServer.Management.Smo.FailedOperationException] {
Write-Output $("Exception: $($_.Exception.Message)");
Write-Output $("Base Exception: $($_.Exception.GetBaseException().Message)");
continue;
}
}
"$(Get-Date): Script Completed";
}
In this script, I am not getting specific drive space information - but you can easily add that by pulling the information for the WMI counters. This script is run daily and I have several reports built in Reporting Services so we can see the growth trend and a backup history report. I only pull the last five full backups for the report - but you can easily change that also.
Note: **cmserver** and **cmdatabase** need to be changed to your own server and database. This also requires the tables be built, but I think you can see how to build them from the script.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 7, 2008 at 1:35 pm
Create a SP to and schedule it as a job, I use this and works very fast..
CREATE PROCEDURE dbo.usp_DriveStats
@drive char(1) = null,
@ShowOutput int = 1,
@TotalBytes bigint = null OUTPUT ,
@FreeBytes bigint = null OUTPUT,
@UsedBytes bigint = null OUTPUT,
@PctFree float = null OUTPUT,
@PctUsed float = null OUTPUT
AS
BEGIN
SET NOCOUNT ON
declare @thisDrive char(1)
declare @driveStats table (
Drive char(1),
TotalBytes bigint,
FreeBytes bigint,
UsedBytes bigint,
TotalMB int,
FreeMB int,
UsedMB int,
PctUsed float(2),
PctFree float(2)
)
create table #t_drives
(drive char(1),
freesizemb varchar(100) )
insert into #t_drives
exec master.dbo.xp_fixeddrives
--if( @drive is not null )
--begin
--delete from #t_drives
--where UPPER(drive) != UPPER(@drive)
--end
if( @TotalBytes is not null
or @UsedBytes is not null
or @PctFree is not null
or @PctUsed is not null )
begin
set @ShowOutput = 0
end
--select * from #t_drives
declare c_drives cursor for
select drive from #t_drives
open c_drives
fetch next from c_drives into @thisDrive
while( @@fetch_status = 0 )
begin
declare @totalSize varchar(200),
@freeSpace varchar(200),
@i_totalSize bigint,
@i_freeSpace bigint
exec usp_GetOAProperty
'Scripting.FileSystemObject',
'GetDrive',
@thisDrive,
'TotalSize',
@totalSize output
exec usp_GetOAProperty
'Scripting.FileSystemObject',
'GetDrive',
@thisDrive,
'FreeSpace',
@freeSpace output
--print 't=' + @totalSize
--print 'f=' + @freeSpace
declare @pct float,
@pctStr varchar(100)
select
@i_totalSize = cast(@totalSize as bigint),
@i_freeSpace = cast(@freeSpace as bigint),
@pct = (cast(@freeSpace as float) / cast(@totalSize as float)) * 100.0
-- set @pctStr = cast(@pct as varchar(100))
-- print 'pct=' + @pctStr
insert into @driveStats
(Drive, TotalBytes, FreeBytes, UsedBytes, PctUsed, PctFree)
values
(@thisDrive, @i_totalSize, @i_freeSpace, (@i_totalSize - @i_freeSpace), (100 - @pct), @pct)
fetch next from c_drives into @thisDrive
end
close c_drives
deallocate c_drives
-- select * from #t_drives
drop table #t_drives
update @driveStats
set TotalMB = (TotalBytes / 1024 / 1024),
UsedMB = (UsedBytes / 1024 / 1024),
FreeMB = (FreeBytes / 1024 / 1024)
if( @drive is not null )
begin
select
@TotalBytes= TotalBytes,
@FreeBytes= FreeBytes,
@UsedBytes= UsedBytes,
@PctFree= PctFree,
@PctUsed= PctUsed
from @driveStats
where Drive = @drive
end
if( @ShowOutput != 0 )
select * from @driveStats
-- create a static list
if( object_id('StaticDriveStats') is not null )
begin
-- print 'dropping table'
drop table StaticDriveStats
end
select * into DB_Jobs.dbo.StaticDriveStats from @driveStats
END
GO
%%%%% USP_GETOAPROPERTY%%%%%%%%%%%
create proc usp_GetOAProperty
@ScriptObject varchar(200),
@InitMethod varchar(200) = null,
@InitValue varchar(200),
@PropertyName varchar(200),
@ReturnValue varchar(200) output
as
begin
declare @FS int,
@rc int,
@driveid int,
@outvalue varchar(100)
-- create fs object
exec @rc = sp_OACreate
@ScriptObject
, @FS out
if @rc <> 0
begin
raiserror('Error: Creating the file system object', 12, 1)
return
--print 'Error: Creating the file system object'
end
-- open text file for writing
exec @rc = sp_OAMethod
@FS,
@InitMethod,
@driveid output,
@InitValue
if @rc <> 0
begin
raiserror('Error: Unable to get drive info for c', 12, 1)
return
end
-- write the message
exec @rc = sp_OAGetProperty
@driveid,
@PropertyName
, @outvalue out
if @rc <> 0
begin
print cast(@rc as varchar)
raiserror('Error: Error getting property: ', 12, 1)
return
end
-- close file handle
exec @rc = sp_OADestroy @driveid
-- destroy object
set @ReturnValue = @outvalue
--print 'value: ' + cast(@freespace as varchar)
end
/*
begin
declare @totalSize varchar(200),
@freeSpace varchar(200)
exec usp_GetOAProperty
'Scripting.FileSystemObject',
'GetDrive',
'c',
'TotalSize',
@totalSize output
exec usp_GetOAProperty
'Scripting.FileSystemObject',
'GetDrive',
'c',
'FreeSpace',
@freeSpace output
print 't=' + @totalSize
print 'f=' + @freeSpace
declare @pct float,
@pctStr varchar(100)
set @pct = (cast(@freeSpace as float) / cast(@totalSize as float)) * 100.0
set @pctStr = cast(@pct as varchar(100))
print 'pct=' + @pctStr
end
*/
GO
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 7, 2008 at 5:25 pm
talentguy123 (9/7/2008)
Create a SP to and schedule it as a job, I use this and works very fast..
That works great, but if you go back to the very first post, the OP specifically stated that he could not use sp_OA* sprocs.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2008 at 5:43 pm
I like the powershell idea though, but I'm not proficient enough to evaluate it yet.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply