Hi to all.

I think the title "Monitoring File Sizes in SQL Server" is not completely correct.

When noticing this title I thought '"this is what I'm looking for" but it seems to measure the fixed filesize from a file location but not the actual filesize.

For one of our apllication I have to measure the actual filesize due to some unexpected growth and shrinking so I was looking for a way to retrieve these sizes from the database.(Files must be fixed according the application vendor)
The table "database.sysfiles" is not exact enough; This sys-table only contains the fixed filesize for each file but not the actual filling of this fixed size.

so I tried to write me a script to retrieve the actual filesize from the filesystem but unfortunately it retrieves the fixed size as well !!! ;-(

This script is far more easy to use for other purposses as the VBS / HTML script you suggesting in part 1 and part 2.

I hope you ( and others  ) can use it in any way whatsoever;

create table #temp( col1 varchar(300))
insert into #temp exec master..xp_cmdshell' dir\\<servername>\<foldername>'

delete from #temp where ( col1 is null )
delete from #temp where ( col1 not like '%<discrimination on some file names>%' )

select  substring(col1,charindex('MMS',col1,1),50) as XX_filename,
           substring(col1,charindex('MMS',col1,1)-18,18)  as XX_filesize
from #temp

drop table #temp

From here you can hook up any other TSQL script.

Regards,

GKramer
THe Netherlands  

Hi,

I am fairly new to VBs scripts and thought is this just what I need to update Our old Batch file process that does the same thing. But when I try to switch drives and then run the code it fails with Acess denied for the other drive (H:\)  Any one with any ideas.

 Thanks

Hi:

Function BDSql(strComputer)

Dim strSql 'Used to hold the SQL Query
Dim objConn 'Used for the Connection object
Dim objRst 'Used for the Recordset object
Dim fecha

SQLDMOSecurity_Integrated  = 1
SQLDMOSecurity_Mixed         = 2
SQLDMOSecurity_Normal       = 0
SQLDMOSecurity_Unknown   = 9

strDBServerName = StrComputer

'On Error Resume Next

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=sqloledb;" & "Data Source=ccstnoc;" & "Initial Catalog=sistemanoc;" & "User Id=cuenta;" & "Password=password"

Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName

If Err.Number <> 0 Then
 EXPLANATION = "Proceso termino con problemas"
 BDSql = False

End If

Set colDatabases = objSQLServer.Databases

'WScript.echo "SQL Version String: " & objSQLServer.VersionString

Select Case objSQLServer.ServerLoginMode(strDBServerName)
   Case SQLDMOSecurity_Integrated
      Login = "Login Mode: Allow Windows Authentication only."
   Case SQLDMOSecurity_Mixed
      Login = "Login Mode: Allow Windows Authentication or SQL Server Authentication."
   Case SQLDMOSecurity_Normal
      Login =  "Login Mode: Allow SQL Server Authentication only."
   Case SQLDMOSecurity_Unknown
      Login = "Login Mode: Security type unknown."
End Select

'strSql3 = "delete from SqlServer where nombre = '" & strDBServerName & "'"
'Set objRst3 = objConn.Execute(strSql3)

For Each objDatabase In colDatabases
   'WScript.Echo objDatabase.Name
   strDBName = objDatabase.Name
   Set objDB = objSQLServer.Databases(strDBName)
   'WScript.Echo "Total Size of Data File + Transaction Log of DB " & strDBName & ": " & objDB.Size & "(MB)"
   'WScript.echo "Space Left (Data File + Transaction Log) for DB " & strDBName & ": " & objDB.SpaceAvailableInMB & "(MB)"
 
   strSql3 = "insert into SqlServer(Nombre, VersionSQL, BaseDatos, TamanoBD, EspacioLibre, Login, fecha ) values('" & strDBServerName & "', '" & objSQLServer.VersionString & "', '" & strDBName & "', '" & objDB.Size & "', '" & objDB.SpaceAvailableInMB & "', '" & Login & "', '" & Date & "')"
   Set objRst3 = objConn.Execute(strSql3)
Next

BDSql = True

EXPLANATION = "proceso termino ok."
End Function