Sub HandleError(ByVal customMessage)
Dim logger
If Err.number <> 0 Then
Set logger = new ScriptLogger
logger.LogError(customMessage)
If Not USE_ERROR_LOG Then
Wscript.Quit 0
Else
Wscript.Quit 1
End If
End If
End Sub
Sub HandleErrorInMonitoring(ByVal customMessage, ByRef oBug, ByRef oAPI)
Dim logger
If Err.number <> 0 Then
Set logger = new ScriptLogger
logger.LogError(customMessage)
Call oAPI.Return(oBag)
Wscript.Quit 0
End If
End Sub
Sub HandleErrorInDiscovery(ByVal customMessage, ByRef oDiscovery, ByRef oAPI)
Dim logger
If Err.number <> 0 Then
Set logger = new ScriptLogger
logger.LogError(customMessage)
oDiscovery.IsSnapshot = false
HandleDebugMessage("Discovery: IsSnapshot = false")
oAPI.Return(oDiscoveryData)
Wscript.Quit 0
End If
End Sub
Sub HandleDebugMessage(ByVal debugMessage)
Dim logger
Set logger = new ScriptLogger
logger.LogDebug(debugMessage)
End Sub
Private Sub Class_Initialize()
sourceLogEvent = "Managegement Group: " + ManagementGroupName + ". Script: " + WScript.ScriptName
Set oAPI = CreateObject("MOM.ScriptAPI")
End Sub
Private Function LogEvent(ByVal message, ByVal eventType, ByVal scriptEventID)
On Error Resume Next
Call oAPI.LogScriptEvent(sourceLogEvent, scriptEventID, eventType, message)
End Function
Public Function LogDebug(ByVal message)
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
Call oAPI.LogScriptEvent(sourceLogEvent, DEBUG_SCRIPT_EVENT_ID, INFO_EVENT_TYPE, message)
End If
End Function
Public Function LogWarning(ByVal message, ByVal eventID)
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
End If
LogEvent message, WARNING_EVENT_TYPE, eventID
End Function
Public Function LogError(ByVal customMessage)
Dim message
If Err.number <> 0 Then
message = Replace(" Error Number: #P1# " & VbCrLf & " Description: #P2# ", "#P1#", CStr(Err.number and 65535) )
message = Replace(message, "#P2#", Err.Description )
message = customMessage & VbCrLf & message & VbCrLf
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
End If
If USE_EVENT_LOG Then
LogEvent message, ERROR_EVENT_TYPE, SCRIPT_EVENT_ID
End If
If USE_ERROR_LOG Then
WScript.StdErr.WriteLine message
End If
End If
End Function
End Class
Function IsStringEmpty(ByVal sValue)
If sValue = EMPTY Or Trim(sValue) = "" Then
IsStringEmpty = true
Else
IsStringEmpty = false
End If
End Function
' ##### ..\Scripts\Common\AdoDB.vbs
'This class is wrapper for ADODB typical operations (Connect and Run SQL query)
Class SQLQuery
PRIVATE adodbConnection
Private Sub Class_Initialize()
On Error Resume Next
Set adodbConnection = CreateObject("ADODB.Connection")
Call HandleError("Cannot create ADODB.Connection")
End Sub
Private Sub Class_Terminate()
On Error Resume Next
'adodbConnection.State = 0 - Closed
If adodbConnection.State > 0 Then
Call adodbConnection.Close()
End If
Call HandleError("Cannot close ADODB.Connection")
End Sub
'This method create and open connection to sql Server
'<connectionString> - name of the server and instance, e.g. "server1.mydomain.com\Instance1";
'use only server name to connect to default instance
Public Sub Connect(ByVal connectionString, ByVal databaseName)
On Error Resume Next
Dim providerString
providerString = "Provider=SQLOLEDB;Server=" & EscapeConnStringValue(connectionString) & ";Database="& EscapeConnStringValue(databaseName) &";Trusted_Connection=yes"
Call adodbConnection.Open(providerString)
End Sub
'This function execute <query> with <parameters> and returns ADODB.RecordSet
'<parameters> must be array, and must be with the same order as sql query <?> paremetrs
Public Function ExecuteQueryToRecordSet(ByVal query, ByVal parameters)
Dim adodbCommand, oResults
Set adodbCommand = CreateObject("ADODB.Command")
Call HandleError("Cannot create ADODB.Command")
adodbCommand.ActiveConnection = adodbConnection
adodbCommand.CommandText = query
Dim i, dataType, dataSize, parameter, length
length = UBound(parameters)
'Put parameters in to ADODB.Command
For i=0 To length
parameter = parameters(i)
dataType = GetDataTypeEnum(parameter)
dataSize = GetDataTypeSize(parameter)
adodbCommand.Parameters.Append(adodbCommand.CreateParameter("p"&i, dataType, 1, dataSize, parameter)) '1 - this is adParamInput in ADODB. see http://www.w3schools.com/ado/met_comm_createparameter.asp#parameterdirenum
Next
Set ExecuteQueryToRecordSet = adodbCommand.Execute()
End Function
'returns size of string or 0 for others types
Private Function GetDataTypeSize(ByVal vValue)
If TypeName(vValue)="String" Then
GetDataTypeSize = Len(vValue)
Else
GetDataTypeSize = 0
End If
End Function
'this function returns int value of some DataTypeEnum
'values of adodb types see on http://www.w3schools.com/ado/met_comm_createparameter.asp#datatypeenum
Private Function GetDataTypeEnum(ByVal vValue)
If TypeName(vValue) = "Byte" Then
GetDataTypeEnum = 2 ' adSmallInt=2
End If
If TypeName(vValue)="Integer" or TypeName(vValue)="Long" Then
GetDataTypeEnum = 3 ' adInteger=3
End If
If TypeName(vValue)="Double" Then
GetDataTypeEnum = 5 ' adDouble=5
End If
If TypeName(vValue)="String" Then
GetDataTypeEnum = 8 ' adBSTR = 8
End If
End Function
'This function escapes string value for connection string
Private Function EscapeConnStringValue (ByVal strValue)
On Error Resume Next
EscapeConnStringValue = "{" + Replace(strValue, "}", "}}") + "}"
End Function
End Class
' ##### Scripts\SQLTableFragmentedIndexes.vbs
On Error Resume Next
Const SCAN_MODE = "SAMPLED" 'DEFAULT, NULL, LIMITED, SAMPLED & DETAILED
DIM SQL_QUERY
'Main SQL query <?>-parametr
SQL_QUERY = "WITH FragmentedIndexes (table_id, table_size_in_mb, avg_fragment_size_in_pages) " & _
" as " & _
" ( " & _
" select Top(?) " & _
" object_id, " & _
" page_count * 8/1024, " & _
" avg_fragment_size_in_pages " & _
" from " & _
" sys.dm_db_index_physical_stats (DB_ID(?), OBJECT_ID('$(TABLENAME)'), NULL, NULL, ?) " & _
" where index_type_desc = 'CLUSTERED INDEX' " & _
" and index_level = 0 " & _
" and avg_fragment_size_in_pages > 0 " & _
" order by page_count desc, avg_fragment_size_in_pages " & _
" ) " & _
" select " & _
" t.name, " & _
" i.name, " & _
" pis.table_size_in_mb, " & _
" pis.avg_fragment_size_in_pages " & _
" from " & _
" FragmentedIndexes pis " & _
" inner join sys.tables t " & _
" on pis.table_id = t.object_id " & _
" inner join sys.indexes i " & _
"on t.object_id = i.object_id " & _
"and i.[type] = 1 " & _
" where " & _
" pis.table_size_in_mb >= ? " & _
" and pis.avg_fragment_size_in_pages <= ? " & _
" order by avg_fragment_size_in_pages "
'Dim logger
'Set logger = new ScriptLogger
Call Main()
'Build string fixed length aligned right
Function RightAlignFixedString(ByVal sValue, ByVal nWidth)
If Len(sValue) > nWidth Then
RightAlignFixedString = Left(sValue, nWidth)
ElseIf Len(sValue) < nWidth-1 Then
Dim i, length, sResult
length = nWidth - Len(sValue)-1
sResult = sValue
For i=0 to length
sResult = " " & sResult
Next
RightAlignFixedString = sResult
End If
End Function
'Build string fixed length aligned left
Function LeftAlignFixedString(ByVal sValue, ByVal nWidth)
If Len(sValue) > nWidth+1 Then
LeftAlignFixedString = Left(sValue, nWidth+1)
ElseIf Len(sValue) < nWidth Then
Dim i, length, sResult
length = nWidth - Len(sValue)
sResult = sValue
For i=0 to length
sResult = sResult & " "
Next
LeftAlignFixedString = sResult
End If
End Function
'This function connect to sql server and run sql query for calculating count of fragmented indexes per DB
Sub GetFragmentedIndexes(ByVal sConnectionString, ByVal sDataBaseName, ByVal nTop, ByVal nMaxTableSize, ByVal nThreshold)
On Error Resume Next
Dim oSqlQry, oRecorSet
Dim sTableName, sIndexName, nTableSize, nFragmentCount
Set oSqlQry = new SQLQuery
Call oSqlQry.Connect(sConnectionString, sDataBaseName)
Call WriteErrorIfOccured("Cannot connect to the database on the " & sConnectionString)
Set oRecorSet = oSqlQry.ExecuteQueryToRecordSet(SQL_QUERY, Array(nTop, sDataBaseName, SCAN_MODE, nMaxTableSize, nThreshold))
Call WriteErrorIfOccured("Cannot execute query")
WScript.Echo " Fragmented Tables"
WScript.Echo " -------------------"
WScript.Echo
WScript.Echo " Connection String: ", sConnectionString
WScript.Echo " Database Name: ", sDataBaseName
WScript.Echo
WScript.Echo "+------------------------------------+--------------+-----------------+--------------------------------------------------------------------------------------+"
WScript.Echo "| | Table Size |Average Fragment | |"
WScript.Echo "| Table Name | (MB) | size (pages) | Index Name |"
WScript.Echo "+------------------------------------+--------------+-----------------+--------------------------------------------------------------------------------------+"
If Not IsEmpty(oRecorSet) Then
Do While Not oRecorSet.EOF
sTableName = oRecorSet(0)
sIndexName = oRecorSet(1)
nTableSize = oRecorSet(2)
nFragmentCount = oRecorSet(3)
Call WriteErrorIfOccured("Cannot read data from record set")
WScript.Echo "|", LeftAlignFixedString(sTableName, 33) , "|" , RightAlignFixedString(Round(nTableSize,2), 12) , "|" , RightAlignFixedString(Round(nFragmentCount,2), 15), "|" , LeftAlignFixedString(sIndexName, 83) , "|"
Call oRecorSet.MoveNext()
Loop
End If
WScript.Echo "+------------------------------------+--------------+-----------------+--------------------------------------------------------------------------------------+"
End Sub
Sub Main()
On Error Resume Next
Dim sConnectionString, sDataBaseName, nTableSize, nTop, nThreshold
Dim oParams
Set oParams = WScript.Arguments
sConnectionString = oParams(0)
Call WriteErrorIfOccured("Cannot read ConnectionString from params")
sDataBaseName = oParams(1)
Call WriteErrorIfOccured("Cannot read DataBaseName from params")
nTop = cint(oParams(2))
Call WriteErrorIfOccured("Cannot read Top from params")
nTableSize = cdbl(oParams(3))
Call WriteErrorIfOccured("Cannot read TableSize from params")
nThreshold = cdbl(oParams(4))
Call WriteErrorIfOccured("Cannot read Threshold from params")
Call GetFragmentedIndexes(sConnectionString, sDataBaseName, nTop, nTableSize, nThreshold)
End Sub
Sub WriteErrorIfOccured(text)
If Err.number <> 0 Then
WScript.Echo "Error: " & text
WScript.Quit 0
End If
End Sub
Home
Show References: