Perspectives

  • Favorite Tools and Favorite Articles
  • This & That

  • 10 Troubleshooting Tips
  • Bone Up on PowerShell
  • Talk Tech With Some Tech Experts
  • Script Watch

  • Handy Script Reports Mailbox Statistics
  • Scripter's Toolkit

  • Extend the sp_spaceUsed Stored Procedure's Usefulness
  • Info To Go


    Perspectives
    by Karen Bemowski, kbemowski@windowsitpro.com

    Favorite Tools and Favorite Articles
    Scripting tools and scripting articles are a few of my favorite things. I'm not familiar with too many scripting tools, so last month I asked you to tell me about your favorite utilities. Unfortunately, I received only one response. (Thanks Jim!) I asked for a rather short turnaround time, so I'm guessing that's why I didn't receive more.

    Because I feel strongly that this information will be helpful to all scripters, I'm going to ask you again to tell me about your favorite utilities. This round, I'll give you lots of time--until December 15-- so that you can procrastinate a bit. Just send me an email (kbemowski@windowsitpro.com) telling me about one or more utilities you often use in your scripting endeavors. The utilities must be freeware or shareware so that all scripters can have access to them. However, because many people already know about the various Microsoft utilities, please don't include any Microsoft tools. After I receive your list of favorite utilities, I'll enter your name in a drawing for $100. I'll announce the lucky winner of the drawing and share the list of readers' favorite tools in the January 5, 2007, issue of Scripting Central.

    Although I'm not familiar with too many scripting tools, I am familiar with many excellent scripting articles. Having worked on the Windows Scripting Solutions newsletter for almost 8 years, I have a long list of favorites. I'll be narrowing that list to two articles, which I can honestly say won't be easy. In the November 3 and December 1 issues of Scripting Central, I'll tell you about those articles

    And provide a link to them. Maybe they'll become some of your favorite scripting articles, too.

    This & That

    10 Troubleshooting Tips
    If you've run into problems when writing command shell scripts, you might want to check out Bill Stewart's article "10 Keys to Command Shell Scripting" in the October 2006 issue of Windows IT Pro (http://www.windowsitpro.com/Windows/Issues/IssueID/855/Index.html). These tips can help you avoid common problems and create more robust scripts. The tips include instructions on how to use double quotes correctly and when to use the escape character.

    Bone Up on PowerShell
    If you want to learn about Windows PowerShell or are simply curious as to what it's all about, check out Windows PowerShell Week. Microsoft will be holding five Webcasts at the beginning of November:

    • And Now for Something Completely Different: Introducing Windows PowerShell (Monday, November 6)
    • One Cmdlet, Two Cmdlet, Three Cmdlet, Four: An Introduction to Windows PowerShell Commands (Tuesday November 7)
    • Objects, Objects Everywhere: Working With Objects in Windows PowerShell (Wednesday November 8)
    • New Kid on the Script Block: Writing Scripts with Windows PowerShell (Thursday November 9)
    • Amazing But True: Things You Never Dreamt You Could Do With Windows PowerShell (Friday November 10)

    For more information about Windows PowerShell Week, go to http://www.microsoft.com/technet/scriptcenter/webcasts/ps.mspx.

    Talk Tech With Some Tech Experts
    Spend a day with technical experts Michael Otey, Gil Kirkpatrick, Dustin Puryear, and Randy Dyess. Designed specifically for IT professionals who work in a "Windows Plus" environment, TechX World is a four-track, one-day event featuring information about OS interoperability, data interoperability, directory and security integration, and virtualization. The content will focus on interoperability tips to help make disparate systems work well together.

    The regional event series will visit four cities between October 24 and November 2, including Washington DC, Chicago , Dallas, and San Francisco. For complete agenda and speaker details, go to http://www.techxworld.com.

    TechX World is brought to you by people who understand that the world you live in never fits the textbook IT infrastructure.

    Script Watch

    Handy Script Reports Mailbox Statistics
    If you regularly perform analyses on mailboxes in your Exchange Server 2003 machine, you'll want to check out MailboxSizes.vbs. This script retrieves mailbox data about all the mailboxes on all the mailbox stores from an Exchange 2003 server, then outputs this information to an XML file. You can then import the XML file into Microsoft Excel or Microsoft Access for analysis. Learn more about MailboxSizes.vbs in the Reader to Reader article "Script Lets You Easily Review Mailbox Data." This article, which will appear in the November issue of Exchange & Outlook Administrator, is now available for public viewing at http://www.windowsitpro.com/MicrosoftExchangeOutlook/Article/ArticleID/93350/93350.html.

    Scripter's Toolkit

    Extend the sp_spaceUsed Stored Procedure's Usefulness
    SQL Server supplies the sp_SpaceUsed stored procedure for getting information about database size. With this procedure, you can obtain the current database's name, size, and amount of unallocated space, reserved space, space used by data, space used by indexes, and unused space. However, sp_SpaceUsed doesn't provide information about the transaction log's size or the amount of used space in it. However, you can extend the ability of sp_SpaceUsed to obtain transaction log statistics by compiling a stored procedure named sp_LogSpace in a master database and calling it from an updated version of sp_SpaceUsed. Here's the code for sp_LogSpace:

    USE MASTER
    GO
    CREATE PROC sp_LogSpace (@dbName  sysname = NULL)
    AS
      BEGIN
        SET NOCOUNT ON
        IF (@dbName IS NULL) SET @dbName = DB_NAME()
        CREATE TABLE #tempLogSpace (dbName sysname,
          LogSize real, LogSpacePctUsed real, stat int)
        INSERT INTO #tempLogSpace EXEC ('DBCC SQLPERF (LOGSPACE)')
        SELECT CAST (convert (decimal (8,3),
          ROUND (LogSize,3)) as varchar(20)) + ' MB' AS LogSize ,
            CAST (convert (decimal (8,3),
              ROUND (LogSize * LogSpacePctUsed / 100.0,3)) AS varchar(20))
                + ' MB' AS LogSpaceUsed
        FROM #tempLogSpace
        WHERE LOWER (rtrim(ltrim(dbName))) = LOWER (rtrim(ltrim(@dbName)))
        DROP TABLE #tempLogSpace
        SET NOCOUNT OFF
      END
    GO

    As this code shows, sp_LogSpace uses the DBCC SQLPERF statement to get the transaction log statistics. The procedure enters the DBCC SQLPERF data into a temporary table and extracts the log-size and space-used statistics, which it outputs to the varchar(20) string. The size is specified in megabytes (MB).

    To use sp_LogSpace, you provide the database name as an input parameter. If you don't provide this parameter, the current database name is used. For example, the code

    USE northwind
    GO
    EXEC sp_LogSpace
    EXEC sp_LogSpace 'pubs'
    EXEC sp_LogSpace 'msdb'

    first uses sp_LogSpace to get the transaction log statistics for the current database (in this case, Northwind), then uses sp_LogSpace to get the transaction log statistics for the pubs and msdb databases. The sp_LogSpace has been tested on a Windows XP machine running SQL Server 2000 Service Pack 1 (SP1). You can download a commented version of the sp_LogSpace.sql at http://www.sqlmag.com/Article/ArticleID/49252/sql_server_49252.html.

    Thanks to Eli Leiba for writing and sharing his sp_LogSpace stored procedure.

    Info To Go

    Join experts Douglas McDowell from Solid Quality Learning and Andrew Sisson from Scalability Experts, as well as Intel insiders and other database professionals, to learn the latest about SQL Server and Oracle database mirroring, BI, 64-bit database computing, and high-availability. Coming to cities across the US in this fall. Visit http://www.windowsitpro.com/roadshows/sqloracle/?code=1004emailannc

    Your business, like most today, relies upon its computing systems to store financial information, house proprietary data, and maintain communications channels. This increasing reliance also increases the dangers to your systems from security breaches, including viruses, spyware, spam, and hackers. Visit the Windows Protection Site at http://www.windowsitpro.com/go/protection for the latest tips on safeguarding your system.

    Learn all you need to know about code-signing technology, including the goals and benefits of code signing, how code signing works, and the underlying cryptographic and security concepts and building blocks. Download the full eBook today--it's free!
    http://www.windowsitpro.com/go/ebooks/codesigning?code=1004emailannc

    Learn from industry expert Michael Otey about different approaches to server consolidation and how to stop server sprawl by using consolidation and virtualization. Find out how to run legacy OSs, Linux, and Windows together and more using virtualization. You'll even get step-by-step instructions on building a virtual machine for Windows Server 2003. Live Event: Wednesday, October 18
    http://www.windowsitpro.com/go/seminars/microsoft/serverconsolidation/?partnerref=1004emailannc

    Uncover Essential Windows Knowledge Through Excavator
    Try out the ultimate vertical search tool--Windows Excavator. Windows Excavator gives you fast, thorough third-party information while filtering out unwanted content. Visit http://www.winexcavator.com today!

    Examine the threats of allowing unwanted or offensive content into your network and learn about technologies and methodologies for defending against inappropriate content, spyware, IM, and P2P.
    http://www.windowsitpro.com/whitepapers/stbernard/internetaccess/index.cfm?code=1004featwp

    Monthly Online Pass--only $5.95 per month!
    Includes instant online access to every article ever written in Windows IT Pro, as well as the latest digital issue. Sign up now: https://store.pentontech.com/index.cfm?s=1&promocode=eu206Aum

    Save $40 off on SQL Server Magazine
    Subscribe to SQL Server Magazine today and SAVE up to $40! Along with your 12 issues, you'll get FREE access to the entire SQL Server Magazine online article archive, which houses more than 2,300 helpful SQL Server articles. This is a limited-time offer, so order now:
    https://store.pentontech.com/index.cfm?s=9&promocode=eu216Aus

    Contact Us

  • About Scripting Central -- scriptingcentral@windowsitpro.com
  • About product news -- scriptingcentral@windowsitpro.com
  • About your subscription -- scripting_central@list.windowsitpro.com
  • This email newsletter is brought to you by Windows IT Pro, the leading publication for IT professionals deploying Windows and related technologies. Subscribe today!
    https://store.pentontech.com/index.cfm?s=1&promocode=eu205xfb

    View the Windows IT Pro Privacy Policy.
    http://www.winnetmag.com/aboutus/index.cfm?action=privacy

    Windows IT Pro is a division of Penton Media, Inc. 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

    Copyright 2006, Penton Media, Inc. All Rights Reserved.