SQL Server Performance

Database Properties

Discussion in 'SQL Server 2005 Database Mirroring' started by jori, Apr 4, 2007.

  1. jori New Member

    Hi!
    I try to readout some database properties from a server with a vb.net script.
    I need the properties
    - Last Database Backup
    - Last Database Log Backup
    - Name
    - Status
    - Owner
    - Size
    - Space Available
    - Number Of Users
    - Collation
    - Recovery Model
    - Compatibility level

    Some properties I already got with SQLDMO. But I can't figure out how I get the properties
    - Number Of Users
    - Last Database Log Backup

    Can anyone help me?

    Here's my code


    Dim oSQLServer As SQLDMO.SQLServer
    Dim oDatabase As SQLDMO.Database2
    Dim oDBOption As SQLDMO.DBOption2
    Dim dbName As String
    Dim dbStatus As String
    Dim dbOwner As String
    Dim dbSize As Double
    Dim dbSpaceAvailable As Double
    Dim dbCompatibilityLevel As String
    Dim dbLastBackup As String
    Dim dbNumberOfUsers As Integer
    Dim dbRecoveryModel As String
    Dim dbCollation As String

    oSQLServer = New SQLDMO.SQLServer()

    oSQLServer.LoginSecure = True
    oSQLServer.Connect(ServerName)

    Dim colDatabases = oSQLServer.Databases

    For Each oDatabase In colDatabases
    oDBOption = oDatabase.DBOption
    dbRecoveryModel = oDBOption.RecoveryModel
    dbName = oDatabase.Name
    dbStatus = oDatabase.Status
    dbOwner = oDatabase.Owner
    dbCompatibilityLevel = oDatabase.CompatibilityLevel
    dbLastBackup = oDatabase.TransactionLog.LastBackup
    dbNumberOfUsers = oDatabase.Users.Count
    dbCollation = oDatabase.Collation
    dbLastBackup = oDatabase.TransactionLog.LastBackup
    dbSize = CType(oDatabase.SizeInKB / 1024 * 100, Integer) / 100.0
    dbSpaceAvailable = CType(oDatabase.SpaceAvailableInMB * 100, Integer) / 100.0
    Next


    The current dbNumberOfUsers value isn't right. The MS SQL Server Management Studio shows an other value.

    [V]
  2. satya Moderator

    http://www.yukonxml.com/articles/smo/
    http://www.aspfree.com/c/a/MS-SQL-Server/Retrieving-SQL-Server-2005-database-information-using-SMO-properties-of-database-objects/
    ..see any help.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. GRayburn New Member

    The SQLServer object does not contain properties for "NumberOfUsers" or "LastBackup", nor does the "Database" object contain what you're looking for either.

    Closest I can find in the DMO model is the "TransactionLog" object has a property of "LastBackup" which obviously will tell you when the last t-log backup occurred. However, I can't find one for a database full backup...

    The list of DMO objects is long, and I can usually find what I need by digging around a bit through the various "Properties" and work my way back up to the object I need that way.


    Here's the current link for the DMO Reference Guide in MSDN:

    http://msdn2.microsoft.com/en-us/library/aa258911(SQL.80).aspx

Share This Page