November 7, 2006 at 11:59 pm
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
November 8, 2006 at 4:45 am
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
November 8, 2006 at 7:26 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply