SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring File Sizes in SQL Server Part 2


Monitoring File Sizes in SQL Server Part 2

Author
Message
rscarlson
rscarlson
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 6
Comments posted here are about the content posted at temp



Guus Kramer
Guus Kramer
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 12

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
Alan Brownlow
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 371

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
german torrealba ramirez
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search