SQL Admin Provider::ModifyDatabaseFile

  • Article

The ModifyDatabaseFile method modifies a database file (data or log) on a SQL Server.

Arguments

Input argument Required Description

fileGrowth

Yes

Designates a growth increment in MB, KB, GB, TB, or percentage (you must specify the unit). The default is 10 percent and the minimum value is 64 KB.

fileMaxSize

Yes

Maximum size in KB, MB, GB, or TB (you must specify the unit) for the file. The default is MB and must be a whole number. The default is unlimited.

filePath

Yes

The operating system file name (full path). This can only be specified for files in the tempdb database, and the new name does not take effect until Microsoft SQL Server is restarted.

fileSize

Yes

The file size in MB (you must specify the unit). The new size must be larger than the current file size.

logicalName

Yes

The logical name for the file.

name

Yes

The name of the database to be modified.

newLogicalName

Yes

The new logical name for the file. This will cause the logical name of the file to be changed to this value.

serverName

Yes

A string representing the name of the server. This can be a NetBIOS name, a fully qualified domain name (FQDN) or an IP address. It is vital that the server upon which MPS is running be able to resolve server names using whichever naming convention you employ.

sqlUserName

Yes

User name for connecting to the SQL server via SQL Authentication. If this node is present, the connection will be made via SQL Authentication rather than Windows Authentication. The existence of this node also requires that the sqlUserPass node exist.

sqlUserPass

Yes

The password for connecting to the SQL server via SQL Authentication, using the username indicated by sqlUserName. If the sqlUserName node is not present, this node is ignored.
Output argument Description

fileGrowth

Designates the growth increment in MB, KB, GB, TB, or percent set for the file.

fileMaxSize

Maximum size in KB, MB, GB, or TB set for the file.

filePath

The operating system file name (full path).

fileSize

The file size in MB.

logFile

Contains the properties set for the log file.

logicalName

The logical name for the file.

Remarks

Rollback Calls

The ModifyDatabaseFile method has no transaction rollback support.

Comments

Per Microsoft SQL Server documentation, only one of these values should be changed at a time.

T-SQL Issues

There are T-SQL issues when modifying database attributes with the ModifyDatabaseFile method.

In SQL Server 2005 Standard Edition with SP2, T-SQL calls that alter the file growth size to unrealistic numbers, such as growing the log file of the database by 3TB, will result in a log file growth of 1MB. This is an inconsistency of the SQL Server since the SQL Admin Provider is merely passing through the statements to the server. Also, this is not an API issue, rather it is the result of the server not processing the "corrupt" value.

Values of file growth size over 1 TB will not generate an error message, but rather will report a completed transaction and leave the log file growth at the default of 1MB.

Sample Code

Example XML Request

The following is an example of an XML request for the ModifyDatabaseFile method:

<?xml version="1.0" encoding="iso-8859-1" ?> 
<request> 
        <data> 
                <name>BVT002</name> 
                        <serverName>CSPROV</serverName> 
                        <sqlUserName>sa</sqlUserName> 
                        <sqlUserPass>eQuest</sqlUserPass> 
                        <logicalName>BVT002_dat</logicalName> 
                        <fileMaxSize>150MB</fileMaxSize> 
        </data> 
        <procedure> 
                <execute namespace="SQL Admin Provider"  
                procedure="ModifyDatabaseFile"> 
                <before source="data" destination="executeData" mode="merge"/> 
                <after source="executeData" sourcePath="dataFile"  
                 destination="data" mode="insert"/> 
                <after source="executeData" sourcePath="logFile"  
                 destination="data" mode="insert"/> 
                </execute> 
        </procedure> 
</request>

Example XML Response

The following is an example of an XML response for the ModifyDatabaseFile method:

<?xml version="1.0" encoding="iso-8859-1" ?> 
<response> 
    <data> 
        <name>BVT002</name> 
        <serverName>CSPROV</serverName> 
        <sqlUserName>sa</sqlUserName> 
        <sqlUserPass>eQuest</sqlUserPass> 
        <logicalName>BVT002_dat</logicalName> 
        <fileMaxSize>150MB</fileMaxSize> 
        <dataFile> 
            <logicalName>BVT002_dat</logicalName> 
            <filePath>C:\BVT002_dat.mdf</filePath> 
            <fileSize>25600 KB</fileSize> 
            <fileMaxSize>153600 KB</fileMaxSize> 
            <fileGrowth>10%</fileGrowth> 
        </dataFile> 
        <logFile> 
            <logicalName>BVT002_log</logicalName> 
            <filePath>C:\BVT002_log.LDF</filePath> 
            <fileSize>6400 KB</fileSize> 
            <fileMaxSize>Unlimited</fileMaxSize> 
            <fileGrowth>10%</fileGrowth> 
        </logFile> 
    </data> 
</response>

Applies To

for:

  • Hosted Messaging and Collaboration version 4.5

  • Hosted Messaging and Collaboration version 4.0

  • Hosted Messaging and Collaboration version 3.5

  • Hosted Messaging and Collaboration version 3.0

  • Windows-based Hosting version 4.5

  • Windows-based Hosting version 4.0

  • Windows-based Hosting version 3.5

  • Windows-based Hosting for Applications version 1.0

See also

Tasks

SQL Admin Provider::CreateDatabase
SQL Admin Provider::DeleteDatabase
SQL Admin Provider::ListAllDatabases