sp_dbcc_alterws

Changes the size of the specified workspace to a specified value, and initializes the workspace.

Syntax

sp_dbcc_alterws dbname, wsname, "wssize[K|M]"

dbname – is the name of the database in which the workspace resides. Specify either dbccdb and dbccalt.
wsname – specifies the name of the workspace to alter.
wssize – is the new size of the workspace, specified by K (kilobytes) or M (megabytes). If you do not specify K or M, wssize specifies the number of pages. Page size is platform-dependent. The minimum size for a workspace is 24 pages.

Example 1 – Changes the size of the scan_ws_000001 workspace on dbccdb to 30MB:

sp_dbcc_alterws dbccdb, scan_ws_000001, "30M"

Workspace scan_ws_000001 has been altered successfully to size 30MB

There are additional considerations when using sp_dbcc_alterws:

sp_dbcc_alterws changes the size of the specified workspace to the specified value and initializes the workspace.
To achieve maximum performance, make sure you have configured a buffer pool of at least 16K before you alter a workspace.
Use sp_plan_dbccdb to determine size estimates before altering the workspace.
The workspace must exist before it can be altered. For information on creating workspaces, see sp_dbcc_createws.
To delete a workspace, in dbccdb issue:
```
drop table workspace_name
```

The permission checks for sp_dbcc_alterws differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be the database owner of `dbccdb` (or `dbccalt`).
Disabled	With granular permissions disabled, you must be the database owner of the specified database, or a user with sa_role.