wsspectacular

Hi All,

I'm sure most of you who play with SharePoint also wear the DBA hat from time to time (or the other way around).  I have been working with a customer who has hundreds of instances scattered across the enterprise.  This script customized to answer several questions, but mainly to report on the patching level of each instance.  Note that this is not a discovery tool; you have to know the instance names ahead of time, and have to have access to them.  

Anyway, I find it comes in handy when you have lots of instances to manage.

SQL Server Auditing Tool

This is a custom VBScript I developed to quickly audit a number of SQL Server instances at once.  The script uses SQL DMO libraries to connect to SQL 2000 and 2005 instances on the network, using Windows authentication to connect and gather properties of each instance to include:
 

• Instances:
• Name and SQL version (also indicating service pack)
• When the instance was installedo   The version and service pack of the Windows OS
• Databases:
• Name, file location, file size
• Tally of disk space consumed by all databases in instance

Paste the following code into a text file and save it as DBChecker.VBS.  You can then call it from a command line using CSCRIPT.  Don't just double-click on the VBS file because it will output the results in a series of Windows popups, which is annoying as hell.

You can “pipe” the output to a text file, e.g. CSCRIPT DBChecker.vbs sql01sharepoint sql01 igerpaw sql01 igerpawtest > dbcheck.txt

You can then paste the comma-delimited text into word and convert it to a table for reporting.  Or you can open the txt file in Excel. 

 To run, this script requires a local instance of SQL Server or MSDE.  It uses those libraries to instantiate.  If you have SQL 2000/MSDE, it will only connect to other SQL 2000 instances.   If you want to connect to SQL 2005 instances, you have to have SQL 2005 (which can connect to both SQL 2000 and 2005 instances).    

' Script name: DBChecker.VBS
' — SQL Server auditing script
' — Version 1.3a Written 5-16-08 by Greg Burns
'
' Description:
'               This script enumerates all databases in a SQL server instance,
'               then calculates the size of each database, and displays the
'               path to the database files on disk.  The Version of each instance is
'               also displayed.
'              
'               You can specify a
'               space-delimited list of SQL Server instances and the script will
'               process all of them in sequence. 
'
' Usage: CSCRIPT VBSIZE.VBS [<instancename>] [<instancename>] [<...n>]
' Notes:
'               1. Instance name uses the following syntax: HOSTNAMEINSTANCENAME
'               2. To capture output to text file, use the redirect,
'                               e.g. "CSCRIPT DBChecker.VBS myservermyinstance > dbcheck.txt
'               3. Leave <instancename> blank to assess only the default SQL instance.
'               4. This version of DBChecker uses Windows Authentication (SSPI) to
'                               connect to the SQL instance.  If you attempt to
'                               connect to a SQL instance or DB for which you do not have
'                               login access, the script will abort with an
'                               'unspecified error.'

wscript.echo "Database Size and Location Reporter"
wscript.echo "  Size = Total Size of Data File + Transaction Log of DB."
wscript.echo "————————————————-"

'Configure array based on command line arguments
'               If no arguments, assume local hostname
If Wscript.Arguments.Count = 0 Then
    arrComputers = Array(".")
Else
    Dim arrComputers()
    For i = 0 to Wscript.Arguments.Count – 1
        Redim Preserve arrComputers(i)
        arrComputers(i) = Wscript.Arguments(i)
    Next
End If
 
For Each strComputer in arrComputers

'Connect to instance:
                strDBServerName = strComputer
                Set objSQLServer = CreateObject("SQLDMO.SQLServer")
                objSQLServer.LoginSecure = True
                objSQLServer.Connect strDBServerName

'Instance information   
                wscript.echo "SQL Instance: " & strDBServerName
                wscript.echo "Version: " & objSQLServer.VersionString
                wscript.echo "Number of databases: " & objSQLServer.Databases.count

'Table header
                wscript.echo "Database, Size (MB), PrimaryFilePath, LogFilePath"

'Iterate through all Databases in instance
                for i = 1 to objSQLServer.Databases.count

                                set objDB = objSQLServer.Databases(i)
                                intSize = objDB.Size
                                intTotalSize = cdbl(intTotalSize) + cdbl(intSize)
                                strDBName = objDB.Name
                                WScript.Echo strDBName & ", " & intSize & ", " & objDB.PrimaryFilePath& ", " & objDB.TransactionLog.LogFiles(1).PhysicalName

                next

'Post total size of all databases in instance
                wscript.echo "   Total: ," & intTotalSize & ","
                wscript.echo "————————————————-"

intSize = 0
intTotalSize = 0

'Next Instance
Next

Added code to show path to LDF file as well.  This property isn't in the same place in SQL-DMO, as TransactionLog is a collection of its own.  The LogFiles(1).PhysicalName value will return the path to the first transaction log file in the collection, which should be close enough for government work, since most of the time, log files for a database are stored in the same subfolder (unless you're doing some really advanced scaling stuff with file groups). 

Filed under: Uncategorized