DB Log file details Using VB Script & DMO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DB Log file details Using VB Script & DMO

Author

Topic

yupsay
Starting Member

5 Posts

Posted - 2008-11-24 : 06:56:04

Not able to initialise DMO objects in VB Script for
Set var1 = CreateObject("sqldmo.transactionlog")
Set var2 = CreateObject("SQLDMO.Logfiles")

What I'm looking for is to get details about the log files as in
1. Size
2. Increment Type & Increment values

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-24 : 07:54:47

Dim oServer
Dim oDatabase
Dim sResults

Set oServer = CreateObject("SQLDmo.SqlServer")
Set oDBFileData = CreateObject("SQLDMO.DBFile")
Set oLogFile = CreateObject("SQLDMO.LogFile")

oServer.LoginSecure = True

'this time Im specifying which server to connect to

oServer.Connect

'this will return the name of each database along with the total free

'space in megabytes. The Chr(9) is a tab, the chr(13) and the chr(10) map

'to carriage return and line feed respectively

For Each oDatabase In oServer.Databases
For Each oLogFile In oDatabase.TransactionLog.LogFiles
'Msgbox(oDBFileData.Size) 'Data file size
Msgbox(oLogFile.Size) 'Log file size
Next
sResults = sResults & oDatabase.Name & Chr(9) & oDatabase.SpaceAvailableInMB & Chr(13) & Chr(10) 'space available

Next

'show the answer - could easily send using xp_sendmail if we wanted

MsgBox sResults

oServer.DisConnect

Set oServer = Nothing

yupsay
Starting Member

5 Posts

Posted - 2008-11-24 : 23:03:24

Hey thanks a lot.
But how can I get the free left in the LDF files ?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-25 : 09:59:06

I've outputed % space used (the 3rd column of dbcc sqlperf(logspace) command.
The second column would give amount used. You already have total amount.
If "% space used" does not suit - you need you could do a little subtraction.

Dim QueryResult

Dim oServer
Dim oDatabase
Dim sResults

Set oServer = CreateObject("SQLDmo.SqlServer")
Set oDBFileData = CreateObject("SQLDMO.DBFile")
Set oLogFile = CreateObject("SQLDMO.LogFile")

oServer.LoginSecure = True

oServer.Connect

'this will return the name of each database along with the total free

'space in megabytes. The Chr(9) is a tab, the chr(13) and the chr(10) map

'to carriage return and line feed respectively

For Each oDatabase In oServer.Databases
For Each oLogFile In oDatabase.TransactionLog.LogFiles
'Msgbox(oDBFileData.Size) 'Data file size
'Msgbox(oLogFile.Size) 'Log file size
Next
sResults = sResults & oDatabase.Name & Chr(9) & oDatabase.SpaceAvailableInMB & Chr(13) & Chr(10) 'space available

Next

'MsgBox sResults
'get space used
Set QueryResult = oServer.ExecuteWithResults("dbcc sqlperf(logspace)")

For num = 1 To QueryResult.Rows
iSpaceUsed =QueryResult.GetColumnString(num, 3)
strDB = QueryResult.GetColumnString(num, 1)
Msgbox( strDB & ", ldf % space used: " & iSpaceUsed )
Next

oServer.DisConnect
Set oServer = Nothing

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-25 : 10:28:03

moved from script library

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources