Showing posts with label batch. Show all posts
Showing posts with label batch. Show all posts

Monday, December 13, 2010

Free Space on all servers for all fixed drives? Done.

A while ago I posted on querying free disk space using WMI and batch.

I wouldn't query free disk space using batch anymore.  The PowerShell script I tweaked and currently use to query free disk space is displayed below.  The script truncates for readability – the rounding methods are commented out if that’s your preference:
# spaceused.ps1
# Lars-UT http://larsrasmussen.blogspot.com
(Get-Date -format 'yyyy-MM-dd hh:mm:ss').ToString() | Out-Default
$outData = @("")
$server = $args[0]
$dataFromServer = Get-WmiObject Win32_Volume -ComputerName $server | Select-Object SystemName,Label,Name,DriveLetter,DriveType,Capacity,Freespace

foreach ($currline in $dataFromServer) {
if ((-not $currline.name.StartsWith("\\")) -and ($currline.Drivetype -ne 5)) {
[float]$tempfloatcapacity = [math]::truncate($currline.Capacity/ 1000000000)
#[float]$tempfloatcapacity = [math]::round(($currline.Capacity/ 1000000000),1)
add-member -InputObject $currline -MemberType NoteProperty -name Capacity_in_GB -value "$tempfloatcapacity GB"

[float]$tempfloat = ($currline.Freespace / 1000000) / ($currline.Capacity / 1000000)
$temppercent = [math]::round(($tempfloat * 100),2)
add-member -InputObject $currline -MemberType NoteProperty -name FreePercent -value "$temppercent %"

[float]$tempfloatfreespace = [math]::truncate($currline.Freespace/ 1000000000)
#[float]$tempfloatfreespace = [math]::round(($currline.Freespace/ 1000000000),1)
add-member -InputObject $currline -MemberType NoteProperty -name Freespace_in_GB -value "$tempfloatfreespace GB"

$outData = $outData + $currline
}
}

$outData | Select-Object SystemName,Label,Name,Capacity_in_GB,Freespace_in_GB,FreePercent | sort-object -property Name | format-table -autosize

To run this against muliple servers just put the server names, one per line, in a text file and save that file. You'll then assign that file to a variable in PowerShell and loop through each line for each server:

PS C:\> $serverlist = Get-Content .\ServerNames-Prod.txt
PS C:\> foreach ($server in $serverlist) { .\spaceused.ps1 $server }


Here’s the pretty output!  Yep, those are sub-second times for querying multiple servers.



spaceused.ps1



Need to document this?  Use Start-Transcript.  I’ve seen auditors use it like so:



PS C:\> $strTimestamp =  [string](Get-Date -format "yyyy-MM-dd_hh-mm")
PS C:\> start-transcript .\ServerSpace-$strTimestamp.txt
PS C:\> foreach ($server in $serverlist) { .\spaceused.ps1 $server }
PS C:\> Stop-Transcript


Enjoy.

Friday, February 20, 2009

Automation Using SQLCMD with Batch

In the January 2009 Meeting of the Utah County SQL Server Users Group I gave a presentation on using SQLCMD.EXE with batch files for automating T-SQL scripts.

Automation Using SQLCMD with batch

  1. Why SQLCMD?

    1. Repetitive processes, same targets, deployment tasks

      1. Scenarios

        1. Run schema change on multiple server/db targets

        2. CREATE/ALTER stored proc on multiple server/db targets

        3. Query Permissions on multiple server/db targets

        4. Speed! Can be much faster than GUI

  2. SQLCMD basics

    install location:
    C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE

    -S SERVERNAME
    -d DBname
    -i inputfile
    >> append operator


  3. CMD Batch Overview (2K, XP, 2003, Vista, Server 2008)

    REM I love comments!
    :: Protect yourself at the command line with this!

    %1 first argument passed
    %2 second argument passed


    1. Arguments examples – copying files to a server

      copy-backups.cmd
      robocopy /MAXAGE:1 "%1" "%2" "*.bak"
      copy-winrar.cmd
      robocopy /S "%programfiles%\WinRAR" "\\%1\c$\Program Files\WinRAR" *.exe


    2. use self-documenting batch files - commented examples or output when no arguments are passed

      IF "%~1"=="" goto help




  4. Putting it all together

Examples:

Demonstrate sqlcmd to multiple targets, then parse command line by copying everything before the append operator.

The batch file, ONEOFF.CMD contains the targets for the T-SQL to be executed.
ONEOFF.CMD accepts two arguments: the name of the .SQL file containing the T-SQL commands, and a log file for output.

I saved all 3 files to the same directory( C:\BIN ) to simplify the example and ran the following:

C:\BIN>ONEOFF.CMD ONEOFF.SQL ONEOFF.LOG

The log file can be very helpful in determining which servers/databases successfully parsed the commands, and which targets failed. The first part of the .SQL file contains the following 5 lines:

SET NOCOUNT ON;
GO
SELECT
'-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate()
SET
NOCOUNT OFF;
GO

These 5 lines above format the outputted log file with the server name, database name, and timestamp. In the example log file output below, the "server" name is my laptop(T60-LAPTOP) since I ran the demo locally. The first 3 lines show that database Chile accepted the .SQL without errors. Lines 4-7 show errors when running against database Cuba.
------------------- -------------------- -----------------------
-- T60-LAPTOP Chile 2009-01-29 18:43:22.793

------------------- -------------------- -----------------------
-- T60-LAPTOP Cuba 2009-01-29 18:50:13.543
Msg 156, Level 15, State 1, Server T60-LAPTOP, Procedure sp_BackupNow, Line 7
Incorrect syntax near the keyword 'BEGIN'.




Links:

SQLCMD standalone installer(Microsoft SQL Server Native Client required for install, found on same page)
http://bit.ly/sqlcmd-and-sql-native-client

Using the sqlcmd Utility
http://technet.microsoft.com/en-us/library/ms180944.aspx

Command line reference
http://www.ss64.com/index.html

NotePad ++
http://notepad-plus.sourceforge.net

Friday, July 18, 2008

Querying Disk Space on Remote Servers using Batch with WMIC

Time to check the disk free space in GB and percentage on a server volume...

WMIC(Windows Management Instrumentation Command-line) makes another appearance!

Thanks to Tipsmark for this syntax (Response number 17 in this post). I added the /node switch and some error handling / usage to have this batch file work on remote machines.

@ECHO OFF
IF "%~1"=="" goto help
IF "%~2"=="" goto help

@SETLOCAL ENABLEEXTENSIONS
@SETLOCAL ENABLEDELAYEDEXPANSION

@FOR /F "tokens=1-3" %%n IN ('"WMIC /node:"%1" LOGICALDISK GET Name,Size,FreeSpace | find /i "%2""') DO @SET FreeBytes=%%n & @SET TotalBytes=%%p

@SET /A TotalSpace=!TotalBytes:~0,-9!
@SET /A FreeSpace=!FreeBytes:~0,-10!
@SET /A TotalUsed=%TotalSpace% - %FreeSpace%
@SET /A PercentUsed=(!TotalUsed!*100)/!TotalSpace!
@SET /A PercentFree=100-!PercentUsed!

IF %TotalSpace% LSS 0 goto error

@ECHO Total space: %TotalSpace%GB
@ECHO Free space: %FreeSpace%GB
@ECHO Used space: %TotalUsed%GB
@ECHO Percent Used: %PercentUsed%%%
@ECHO Percent Free: %PercentFree%%%

@SET TotalSpace=
@SET FreeSpace=
@SET TotalUsed=
@SET PercentUsed=
@SET PercentFree=
goto end

:error
echo.
echo *** Invalid server or drive specified ***
echo.
goto help

:help
echo.
echo diskfree.cmd
echo.
echo Queries remote server for free disk space.
echo Specify a MACHINENAME and a drive letter to be queried
echo.
echo Example: diskfree.cmd MACHINENAME c:
echo.
goto end


:end


Here's an example of the script being run with a target computer named 'LARS', checking for free space on the [F:] volume:





If parameters are not passed or passed incorrectly(wrong drive letter) the script outputs the following or similar: