Monitoring File Sizes in SQL Server Part 2

• rscarlson

  SSC Eights!

  Points: 856

  More actions

  October 26, 2006 at 2:59 pm

  #170763

  Comments posted here are about the content posted at temp

• Guus Kramer

  Ten Centuries

  Points: 1031

  More actions

  November 7, 2006 at 11:59 pm

  #670477

  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  

• Alan Brownlow

  SSC-Addicted

  Points: 423

  More actions

  November 8, 2006 at 4:45 am

  #670529

  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

• german torrealba ramirez

  SSC Rookie

  Points: 25

  More actions

  November 8, 2006 at 7:26 am

  #670570

  Hi:

  this example them can be of utility

  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