-->
APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
Modifies the files and filegroups associated with the database. Adds or removes files and filegroups from a database, and changes the attributes of a database or its files and filegroups. For other ALTER DATABASE options, see ALTER DATABASE.
For more information about the syntax conventions, see Transact-SQL Syntax Conventions.
Click a product!
In the following row, click whichever product name you are interested in. The click displays different content here on this webpage, appropriate for whichever product you click.
* SQL Server * | SQL Database managed instance |
Syntax
Arguments
<add_or_modify_files>::=
Specifies the file to be added, removed, or modified.
database_nameIs the name of the database to be modified.
ADD FILEAdds a file to the database.
TO FILEGROUP { filegroup_name }Specifies the filegroup to which to add the specified file. To display the current filegroups and which filegroup is the current default, use the sys.filegroups catalog view.
ADD LOG FILEAdds a log file be added to the specified database.
REMOVE FILE logical_file_nameRemoves the logical file description from an instance of SQL Server and deletes the physical file. The file cannot be removed unless it is empty.
logical_file_nameIs the logical name used in SQL Server when referencing the file.
Warning
Removing a database file that has
FILE_SNAPSHOT
backups associated with it will succeed, but any associated snapshots will not be deleted to avoid invalidating the backups referring to the database file. The file will be truncated, but will not be physically deleted in order to keep the FILE_SNAPSHOT backups intact. For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. Applies to: SQL Server ( SQL Server 2016 (13.x) through SQL Server 2017).MODIFY FILESpecifies the file that should be modified. Only one <filespec> property can be changed at a time. NAME must always be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.
To modify the logical name of a data file or log file, specify the logical file name to be renamed in the
NAME
clause, and specify the new logical name for the file in the NEWNAME
clause. For example:To move a data file or log file to a new location, specify the current logical file name in the
NAME
clause and specify the new path and operating system file name in the FILENAME
clause. For example:When you move a full-text catalog, specify only the new path in the FILENAME clause. Do not specify the operating-system file name.
For more information, see Move Database Files.
For a FILESTREAM filegroup, NAME can be modified online. FILENAME can be modified online; however, the change does not take effect until after the container is physically relocated and the server is shutdown and then restarted.
You can set a FILESTREAM file to OFFLINE. When a FILESTREAM file is offline, its parent filegroup will be internally marked as offline; therefore, all access to FILESTREAM data within that filegroup will fail.
Note
<add_or_modify_files> options are not available in a Contained Database.
<filespec>::=
Controls the file properties.
NAME logical_file_nameSpecifies the logical name of the file.
logical_file_nameIs the logical name used in an instance of SQL Server when referencing the file.
NEWNAME new_logical_file_nameSpecifies a new logical name for the file.
new_logical_file_nameIs the name to replace the existing logical file name. The name must be unique within the database and comply with the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier.
FILENAME { 'os_file_name' | 'filestream_path' | 'memory_optimized_data_path'}Specifies the operating system (physical) file name.
' os_file_name 'For a standard (ROWS) filegroup, this is the path and file name that is used by the operating system when you create the file. The file must reside on the server on which SQL Server is installed. The specified path must exist before executing the ALTER DATABASE statement.
Note
SIZE
, MAXSIZE
, and FILEGROWTH
parameters cannot be set when a UNC path is specified for the file.System databases cannot reside in UNC share directories.
Data files should not be put on compressed file systems unless the files are read-only secondary files, or if the database is read-only. Log files should never be put on compressed file systems.
If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be put on each raw partition.
'filestream_path'For a FILESTREAM filegroup, FILENAME refers to a path where FILESTREAM data will be stored. The path up to the last folder must exist, and the last folder must not exist. For example, if you specify the path
C:MyFilesMyFilestreamData
, then C:MyFiles
must exist before you run ALTER DATABASE, but the MyFilestreamData
folder must not exist.This Game Is Cracked And Highly Compressed Game. Freedom fighter 2 game highly compressed. Freedom Fighters Free Download FULL Version PC GameFreedom Fighters Free Download Full Version RG Mechanics Repack PC Game In Direct Download Links.
Note
The SIZE and FILEGROWTH properties do not apply to a FILESTREAM filegroup.
'memory_optimized_data_path'For a memory-optimized filegroup, FILENAME refers to a path where memory-optimized data will be stored. The path up to the last folder must exist, and the last folder must not exist. For example, if you specify the path
C:MyFilesMyData
, then C:MyFiles
must exist before you run ALTER DATABASE, but the MyData
folder must not exist.The filegroup and file (
<filespec>
) must be created in the same statement.Note
The SIZE and FILEGROWTH properties do not apply to a MEMORY_OPTIMIZED_DATA filegroup.
For more information on memory-optimized filegroups, see The Memory Optimized Filegroup.
SIZE sizeSpecifies the file size. SIZE does not apply to FILESTREAM filegroups.
sizeIs the size of the file.
When specified with ADD FILE, size is the initial size for the file. When specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.
When size is not supplied for the primary file, the SQL Server uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB.
The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. To specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number by 1024. For example, specify 1536 KB instead of 1.5 MB (1.5 x 1024 = 1536).
Note
SIZE
cannot be set:- When a UNC path is specified for the file
- For
FILESTREAM
andMEMORY_OPTIMIZED_DATA
filegroups
MAXSIZE { max_size| UNLIMITED }Specifies the maximum file size to which the file can grow.
max_sizeIs the maximum file size. The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. If max_size is not specified, the file size will increase until the disk is full.
UNLIMITEDSpecifies that the file grows until the disk is full. In SQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB. There is no maximum size when this option is specified for a FILESTREAM container. It continues to grow until the disk is full.
Note
MAXSIZE
cannot be set when a UNC path is specified for the file.FILEGROWTH growth_incrementSpecifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. FILEGROWTH does not apply to FILESTREAM filegroups.
growth_incrementIs the amount of space added to the file every time new space is required.
The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.
A value of 0 indicates that automatic growth is set to off and no additional space is allowed.
If FILEGROWTH is not specified, the default values are:
Version | Default values |
---|---|
Starting with SQL Server 2016 (13.x) | Data 64 MB. Log files 64 MB. |
Starting with SQL Server 2005 (9.x) | Data 1 MB. Log files 10%. |
Prior to SQL Server 2005 (9.x) | Data 10%. Log files 10%. |
Note
FILEGROWTH
cannot be set:- When a UNC path is specified for the file
- For
FILESTREAM
andMEMORY_OPTIMIZED_DATA
filegroups
OFFLINESets the file offline and makes all objects in the filegroup inaccessible.
Caution
Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE.
<filespec> options are not available in a Contained Database.
<add_or_modify_filegroups>::=
Add, modify, or remove a filegroup from the database.
ADD FILEGROUP filegroup_nameAdds a filegroup to the database.
CONTAINS FILESTREAMSpecifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system.
CONTAINS MEMORY_OPTIMIZED_DATA
Applies to: SQL Server ( SQL Server 2014 (12.x) through SQL Server 2017)
Specifies that the filegroup stores memory optimized data in the file system. For more information, see In-Memory OLTP - In-Memory Optimization. Only one
MEMORY_OPTIMIZED_DATA
filegroup is allowed per database. For creating memory optimized tables, the filegroup cannot be empty. There must be at least one file. filegroup_name refers to a path. The path up to the last folder must exist, and the last folder must not exist.REMOVE FILEGROUP filegroup_nameRemoves a filegroup from the database. The filegroup cannot be removed unless it is empty. Remove all files from the filegroup first. For more information, see 'REMOVE FILE logical_file_name,' earlier in this topic.
Note
Unless the FILESTREAM Garbage Collector has removed all the files from a FILESTREAM container, the
ALTER DATABASE REMOVE FILE
operation to remove a FILESTREAM container will fail and return an error. See the Removing a FILESTREAM Container section later in this topic.MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME =new_filegroup_name }Modifies the filegroup by setting the status to READ_ONLY or READ_WRITE, making the filegroup the default filegroup for the database, or changing the filegroup name.
<filegroup_updatability_option>Sets the read-only or read/write property to the filegroup.
DEFAULTChanges the default database filegroup to filegroup_name. Only one filegroup in the database can be the default filegroup. For more information, see Database Files and Filegroups.
NAME = new_filegroup_nameChanges the filegroup name to the new_filegroup_name.
AUTOGROW_SINGLE_FILEApplies to: SQL Server ( SQL Server 2016 (13.x) through SQL Server 2017)
When a file in the filegroup meets the autogrow threshold, only that file grows. This is the default.
AUTOGROW_ALL_FILES
Applies to: SQL Server ( SQL Server 2016 (13.x) through SQL Server 2017)
When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.
<filegroup_updatability_option>::=
Sets the read-only or read/write property to the filegroup.
READ_ONLY | READONLYSpecifies the filegroup is read-only. Updates to objects in it are not allowed. The primary filegroup cannot be made read-only. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.
Because a read-only database does not allow data modifications:
- Automatic recovery is skipped at system startup.
- Shrinking the database is not possible.
- No locking occurs in read-only databases. This can cause faster query performance.
Note
The keyword
READONLY
will be removed in a future version of MicrosoftSQL Server. Avoid using READONLY
in new development work, and plan to modify applications that currently use READONLY
. Use READ_ONLY
instead.READ_WRITE | READWRITESpecifies the group is READ_WRITE. Updates are enabled for the objects in the filegroup. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.
Note
The keyword
READWRITE
will be removed in a future version of MicrosoftSQL Server. Avoid using READWRITE
in new development work, and plan to modify applications that currently use READWRITE
to use READ_WRITE
instead.Tip
The status of these options can be determined by examining the is_read_only column in the sys.databases catalog view or the Updateability property of the
DATABASEPROPERTYEX
function.Remarks
To decrease the size of a database, use DBCC SHRINKDATABASE.
You cannot add or remove a file while a
BACKUP
statement is running.A maximum of 32,767 files and 32,767 filegroups can be specified for each database.
Starting with SQL Server 2005 (9.x), the state of a database file (for example, online or offline), is maintained independently from the state of the database. For more information, see File States.
- The state of the files within a filegroup determines the availability of the whole filegroup. For a filegroup to be available, all files within the filegroup must be online.
- If a filegroup is offline, any try to access the filegroup by an SQL statement will fail with an error. When you build query plans for
SELECT
statements, the query optimizer avoids nonclustered indexes and indexed views that reside in offline filegroups. This enables these statements to succeed. However, if the offline filegroup contains the heap or clustered index of the target table, theSELECT
statements fail. Additionally, anyINSERT
,UPDATE
, orDELETE
statement that modifies a table with any index in an offline filegroup will fail.
SIZE, MAXSIZE, and FILEGROWTH parameters cannot be set when a UNC path is specified for the file.
SIZE and FILEGROWTH parameters cannot be set for memory optimized filegroups.
The keyword
READONLY
will be removed in a future version of MicrosoftSQL Server. Avoid using READONLY
in new development work, and plan to modify applications that currently use READONLY. Use READ_ONLY
instead.The keyword
READWRITE
will be removed in a future version of MicrosoftSQL Server. Avoid using READWRITE
in new development work, and plan to modify applications that currently use READWRITE
to use READ_WRITE
instead.Moving Files
You can move system or user-defined data and log files by specifying the new location in FILENAME. This may be useful in the following scenarios:
- Failure recovery. For example, the database is in suspect mode or shutdown caused by hardware failure.
- Planned relocation.
- Relocation for scheduled disk maintenance.
For more information, see Move Database Files.
Initializing Files
By default, data and log files are initialized by filling the files with zeros when you perform one of the following operations:
- Create a database.
- Add files to an existing database.
- Increase the size of an existing file.
- Restore a database or filegroup.
Data files can be initialized instantaneously. This enables for fast execution of these file operations. For more information, see Database File Initialization.
Removing a FILESTREAM Container
Even though FILESTREAM container may have been emptied using the 'DBCC SHRINKFILE' operation, the database may still need to maintain references to the deleted files for various system maintenance reasons. sp_filestream_force_garbage_collection will run the FILESTREAM Garbage Collector to remove these files when it is safe to do so. Unless the FILESTREAM Garbage Collector has removed all the files from a FILESTREAM container, the ALTER DATABASE REMOVE FILE operation will fail to remove a FILESTREAM container and will return an error. The following process is recommended to remove a FILESTREAM container.
- Run DBCC SHRINKFILE with the EMPTYFILE option to move the active contents of this container to other containers.
- Ensure that Log backups have been taken, in the FULL or BULK_LOGGED recovery model.
- Ensure that the replication log reader job has been run, if relevant.
- Run sp_filestream_force_garbage_collection to force the garbage collector to delete any files that are no longer needed in this container.
- Execute ALTER DATABASE with the REMOVE FILE option to remove this container.
- Repeat steps 2 through 4 once more to complete the garbage collection.
- Use ALTER Database..REMOVE FILE to remove this container.
Examples
A. Adding a file to a database
The following example adds a 5-MB data file to the AdventureWorks2012 database.
B. Adding a filegroup with two files to a database
The following example creates the filegroup
Test1FG1
in the AdventureWorks2012 database and adds two 5-MB files to the filegroup.C. Adding two log files to a database
The following example adds two 5-MB log files to the AdventureWorks2012 database.
D. Removing a file from a database
The following example removes one of the files added in example B.
E. Modifying a file
The following example increases the size of one of the files added in example B.The ALTER DATABASE with MODIFY FILE command can only make a file size bigger, so if you need to make the file size smaller you need to use DBCC SHRINKFILE.
This example shrinks the size of a data file to 100 MB, and then specifies the size at that amount.
F. Moving a file to a new location
The following example moves the
Test1dat2
file created in example A to a new directory.Note
You must physically move the file to the new directory before running this example. Afterward, stop and start the instance of SQL Server or take the AdventureWorks2012 database OFFLINE and then ONLINE to implement the change.
G. Moving tempdb to a new location
The following example moves
tempdb
from its current location on the disk to another disk location. Because tempdb
is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb
continues to function in its existing location.- Determine the logical file names of the
tempdb
database and their current location on disk. - Change the location of each file by using
ALTER DATABASE
. - Stop and restart the instance of SQL Server.
- Verify the file change.
- Delete the tempdb.mdf and templog.ldf files from their original location.
H. Making a filegroup the default
The following example makes the
Test1FG1
filegroup created in example B the default filegroup. Then, the default filegroup is reset to the PRIMARY
filegroup. Note that PRIMARY
must be delimited by brackets or quotation marks.I. Adding a Filegroup Using ALTER DATABASE
The following example adds a
FILEGROUP
that contains the FILESTREAM
clause to the FileStreamPhotoDB
database.The following example adds a
FILEGROUP
that contains the MEMORY_OPTIMIZED_DATA
clause to the xtp_db
database. The filegroup stores memory optimized data.J. Change filegroup so that when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow
The following example generates the required
ALTER DATABASE
statements to modify read-write filegroups with the AUTOGROW_ALL_FILES
setting.See Also
Query To Find Database Size
SQL Server | * SQL Database managed instance * |
Azure SQL Database managed instance
Use this statement with a database in Azure SQL Database managed instance.
Syntax for databases in a managed instance
Arguments
<add_or_modify_files>::=
Specifies the file to be added, removed, or modified.
database_nameIs the name of the database to be modified.
ADD FILEAdds a file to the database.
TO FILEGROUP { filegroup_name }Specifies the filegroup to which to add the specified file. To display the current filegroups and which filegroup is the current default, use the sys.filegroups catalog view.
REMOVE FILE logical_file_nameRemoves the logical file description from an instance of SQL Server and deletes the physical file. The file cannot be removed unless it is empty.
logical_file_nameIs the logical name used in SQL Server when referencing the file.
MODIFY FILESpecifies the file that should be modified. Only one <filespec> property can be changed at a time. NAME must always be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.
<filespec>::=
Controls the file properties.
NAME logical_file_nameSpecifies the logical name of the file.
logical_file_nameIs the logical name used in an instance of SQL Server when referencing the file.
NEWNAME new_logical_file_nameSpecifies a new logical name for the file.
new_logical_file_nameIs the name to replace the existing logical file name. The name must be unique within the database and comply with the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier.
SIZE sizeSpecifies the file size.
sizeIs the size of the file.
When specified with ADD FILE, size is the initial size for the file. When specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.
When size is not supplied for the primary file, the SQL Server uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB.
The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. To specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number by 1024. For example, specify 1536 KB instead of 1.5 MB (1.5 x 1024 = 1536).
MAXSIZE { max_size| UNLIMITED }Specifies the maximum file size to which the file can grow.
max_sizeIs the maximum file size. The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. If max_size is not specified, the file size will increase until the disk is full.
UNLIMITEDSpecifies that the file grows until the disk is full. In SQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.
FILEGROWTH growth_incrementSpecifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.
growth_incrementIs the amount of space added to the file every time new space is required.
The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.
A value of 0 indicates that automatic growth is set to off and no additional space is allowed.
If FILEGROWTH is not specified, the default values are:
- Data 64 MB
- Log files 64 MB
<add_or_modify_filegroups>::=
Add, modify, or remove a filegroup from the database.
ADD FILEGROUP filegroup_nameAdds a filegroup to the database.
The following example creates a filegroup that is added to a database named sql_db_mi, and adds a file to the filegroup.
REMOVE FILEGROUP filegroup_nameRemoves a filegroup from the database. The filegroup cannot be removed unless it is empty. Remove all files from the filegroup first. For more information, see 'REMOVE FILE logical_file_name,' earlier in this topic.
MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME =new_filegroup_name }Modifies the filegroup by setting the status to READ_ONLY or READ_WRITE, making the filegroup the default filegroup for the database, or changing the filegroup name.
<filegroup_updatability_option>Sets the read-only or read/write property to the filegroup.
DEFAULTChanges the default database filegroup to filegroup_name. Only one filegroup in the database can be the default filegroup. For more information, see Database Files and Filegroups.
NAME = new_filegroup_nameChanges the filegroup name to the new_filegroup_name.
AUTOGROW_SINGLE_FILE
When a file in the filegroup meets the autogrow threshold, only that file grows. This is the default.
AUTOGROW_ALL_FILES
When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.
<filegroup_updatability_option>::=
Sets the read-only or read/write property to the filegroup.
READ_ONLY | READONLYSpecifies the filegroup is read-only. Updates to objects in it are not allowed. The primary filegroup cannot be made read-only. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.
Because a read-only database does not allow data modifications:
- Automatic recovery is skipped at system startup.
- Shrinking the database is not possible.
- No locking occurs in read-only databases. This can cause faster query performance.
Note
The keyword READONLY will be removed in a future version of MicrosoftSQL Server. Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. Use READ_ONLY instead.
READ_WRITE | READWRITESpecifies the group is READ_WRITE. Updates are enabled for the objects in the filegroup. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.
Note
The keyword
READWRITE
will be removed in a future version of MicrosoftSQL Server. Avoid using READWRITE
in new development work, and plan to modify applications that currently use READWRITE
to use READ_WRITE
instead.The status of these options can be determined by examining the is_read_only column in the sys.databases catalog view or the Updateability property of the
DATABASEPROPERTYEX
function.Remarks
To decrease the size of a database, use DBCC SHRINKDATABASE.
You cannot add or remove a file while a
BACKUP
statement is running.A maximum of 32,767 files and 32,767 filegroups can be specified for each database.
Examples
A. Adding a file to a database
The following example adds a 5-MB data file to the AdventureWorks2012 database.
B. Adding a filegroup with two files to a database
The following example creates the filegroup
Test1FG1
in the AdventureWorks2012 database and adds two 5-MB files to the filegroup.C. Removing a file from a database
The following example removes one of the files added in example B.
D. Modifying a file
The following example increases the size of one of the files added in example B.The ALTER DATABASE with MODIFY FILE command can only make a file size bigger, so if you need to make the file size smaller you need to use DBCC SHRINKFILE.
This example shrinks the size of a data file to 100 MB, and then specifies the size at that amount.
E. Making a filegroup the default
The following example makes the
Test1FG1
filegroup created in example B the default filegroup. Then, the default filegroup is reset to the PRIMARY
filegroup. Note that PRIMARY
must be delimited by brackets or quotation marks.F. Adding a Filegroup Using ALTER DATABASE
The following example adds a
FILEGROUP
to the MyDB
database.G. Change filegroup so that when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow
The following example generates the required
ALTER DATABASE
statements to modify read-write filegroups with the AUTOGROW_ALL_FILES
setting.See Also
how can i query my sql server to only get the size of database?
I used this :
I got this :
It returns me several column that i don't need, maybe there is a trick to select database_size column from this stored procedure ?
I also tried this code :
It gives me this result:
So i wrote this:
I got: 1183
So it works but maybe there is a proper way to get this?
Devart101k1919 gold badges143143 silver badges162162 bronze badges
Adeel ASIFAdeel ASIF1,24988 gold badges2222 silver badges3737 bronze badges
6 Answers
Try this one -
Query:
Output:
Function:
UPDATE 2016/01/22:
Show information about size, free space, last database backups
Output:
DevartDevart101k1919 gold badges143143 silver badges162162 bronze badges
Also compare the results with the following query's result
It produces result similar to the following
There is a good article - Different ways to determine free space for SQL Server databases and database files
LijoLijo11.3k5555 gold badges201201 silver badges346346 bronze badges
Worked perfectly for me to calculate SQL database size in SQL Server 2012
Anjan KantAnjan Kant
YOU check how this query works in the below link.
Pruthvi RajPruthvi Raj
AturAtur96666 gold badges2121 silver badges3434 bronze badges
EXEC sp_spaceused @oneresultset = 1
show in 1 row all of the resultif you execute just 'EXEC sp_spaceused' you will see two rowsWork in SQL Server Management Studio v17.9
koly86koly86
Not the answer you're looking for? Browse other questions tagged sqlsql-serversql-server-2008sql-server-2008-r2 or ask your own question.
-->This article provides an overview of the SQL Database resource limits for a SQL Database server that manages single databases and elastic pools. It also provides information regarding what happens when those resource limits are hit or exceeded.
Note
For managed instances limits, see SQL Database resource limits for managed instances.
Maximum resource limits
Resource | Limit |
---|---|
Databases per server | 5000 |
Default number of servers per subscription in any region | 20 |
Max number of servers per subscription in any region | 200 |
DTU / eDTU quota per server | 54,000 |
vCore quota per server/instance | 540 |
Max pools per server | Limited by number of DTUs or vCores. For example, if each pool is 1000 DTUs, then a server can support 54 pools. |
Note
To obtain more DTU /eDTU quota, vCore quota, or more servers than the default amount, a new support request can be submitted in the Azure portal for the subscription with issue type “Quota”. The DTU / eDTU quota and database limit per server constrains the number of elastic pools per server.
Important
As the number of databases approaches the limit per SQL Database server, the following can occur:
- Increasing latency in running queries against the master database. This includes views of resource utilization statistics such as sys.resource_stats.
- Increasing latency in management operations and rendering portal viewpoints that involve enumerating databases in the server.
Storage size
- For single databases rources please refer to either DTU-based resource limits or vCore-based resource limits for the storage size limits per pricing tier.
What happens when database resource limits are reached
Compute (DTUs and eDTUs / vCores)
When database compute utilization (measured by DTUs and eDTUs, or vCores) becomes high, query latency increases and can even time out. Under these conditions, queries may be queued by the service and are provided resources for execution as resource become free.When encountering high compute utilization, mitigation options include:
- Increasing the compute size of the database or elastic pool to provide the database with more compute resources. See Scale single database resources and Scale elastic pool resources.
- Optimizing queries to reduce the resource utilization of each query. For more information, see Query Tuning/Hinting.
Storage
When database space used reaches the max size limit, database inserts and updates that increase the data size fail and clients receive an error message. Database SELECTS and DELETES continue to succeed.
When encountering high space utilization, mitigation options include:
- Increasing the max size of the database or elastic pool, or add more storage. See Scale single database resources and Scale elastic pool resources.
- If the database is in an elastic pool, then alternatively the database can be moved outside of the pool so that its storage space is not shared with other databases.
- Shrink a database to reclaim unused space. For more information, see Manage file space in Azure SQL Database
Sessions and workers (requests)
The maximum number of sessions and workers are determined by the service tier and compute size (DTUs and eDTUs). New requests are rejected when session or worker limits are reached, and clients receive an error message. While the number of connections available can be controlled by the application, the number of concurrent workers is often harder to estimate and control. This is especially true during peak load periods when database resource limits are reached and workers pile up due to longer running queries.
When encountering high session or worker utilization, mitigation options include:
- Increasing the service tier or compute size of the database or elastic pool. See Scale single database resources and Scale elastic pool resources.
- Optimizing queries to reduce the resource utilization of each query if the cause of increased worker utilization is due to contention for compute resources. For more information, see Query Tuning/Hinting.
Transaction Log Rate Governance
Transaction log rate governance is a process in Azure SQL Database used to limit high ingestion rates for workloads such as bulk insert, SELECT INTO, and index builds. These limits are tracked and enforced at the sub-second level to the rate of log record generation, limiting throughput regardless of how many IOs may be issued against data files. Transaction log generation rates currently scale linearly up to a point that is hardware dependent, with the maximum log rate allowed being 96 MB/s with the vCore purchasing model.
Note
The actual physical IOs to transaction log files are not governed or limited.
Log rates are set such that they can be achieved and sustained in a variety of scenarios, while the overall system can maintain its functionality with minimized impact to the user load. Log rate governance ensures that transaction log backups stay within published recoverability SLAs. This governance also prevents an excessive backlog on secondary replicas.
As log records are generated, each operation is evaluated and assessed for whether it should be delayed in order to maintain a maximum desired log rate (MB/s per second). The delays are not added when the log records are flushed to storage, rather log rate governance is applied during log rate generation itself.
The actual log generation rates imposed at run time may also be influenced by feedback mechanisms, temporarily reducing the allowable log rates so the system can stabilize. Log file space management, avoiding running into out of log space conditions and Availability Group replication mechanisms can temporarily decrease the overall system limits.
Log rate governor traffic shaping is surfaced via the following wait types (exposed in the sys.dm_db_wait_stats DMV):
Wait Type | Notes |
---|---|
LOG_RATE_GOVERNOR | Database limiting |
POOL_LOG_RATE_GOVERNOR | Pool limiting |
INSTANCE_LOG_RATE_GOVERNOR | Instance level limiting |
HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE | Feedback control, availability group physical replication in Premium/Business Critical not keeping up |
HADR_THROTTLE_LOG_RATE_LOG_SIZE | Feedback control, limiting rates to avoid an out of log space condition |
When encountering a log rate limit that is hampering desired scalability, consider the following options:
- Scale up to a larger tier in order to get the maximum 96 MB/s log rate.
- If data being loaded is transient, i.e. staging data in an ETL process, it can be loaded into tempdb (which is minimally logged).
- For analytic scenarios, load into a clustered columnstore covered table. This reduces the required log rate due to compression. This technique does increase CPU utilization and is only applicable to data sets that benefit from clustered columnstore indexes.
Next steps
- For information about general Azure limits, see Azure subscription and service limits, quotas, and constraints.
- For information about DTUs and eDTUs, see DTUs and eDTUs.
- For information about tempdb size limits, see TempDB in Azure SQL Database.
A question that is frequently asked by customers using Azure SQL Database is “How can I determine the size of my database programmatically?” Interestingly, different people may be talking about different things when asking this question. Is it the size of all database files on disk? Is it the size of just the data files? Is it the size of used space in the database? Is it the total size of allocated and empty space in the database? Depending on the context, all these things may be the right answer to the question.
Today, if you do a web search on this topic, the most frequent answer to this question will point you to querying the sys.dm_db_partition_stats DMV, and looking at the reserved_page_count column. Other solutions involve querying sys.allocation_units and sys.resource_stats DMVs, or using sp_spaceused stored procedure.
In the context of Azure SQL Database, the measurement that most customers would be interested in is the size used by the Azure SQL Database service to govern the size of the database, i.e. the 161.29 GB that is shown in Azure Portal in this example:
This value is the total size of allocated extents in data files.
However, none of the methods mentioned earlier will accurately provide that measurement for V12 databases. sys.dm_db_partition_stats and sys.allocation_units report at partition and allocation unit level, rather than data file level. sys.resource_stats averages database size over five minute intervals, and therefore does not consider the most recent changes in space usage. sp_spaceused returns several size values, however the total size of allocated extents in data files, which is used by the service, is not one of them.
For V12 databases, the measurement we are interested in is determined using the sys.database_files DMV and the FILEPROPERTY function with the ‘SpaceUsed’ argument. Only ROWS files are considered. Log and XTP files are excluded for the purposes of determining database size.
The following statement is an example of the correct way to determine the size of an Azure SQL Database V12 database programmatically:
-->This topic describes how to display the data and log space information for a database in SQL Server 2017 by using SQL Server Management Studio or Transact-SQL.
Before You Begin
Security
Permissions
Permission to execute sp_spaceused is granted to the public role. Only members of the db_owner fixed database role can specify the **@updateusage** parameter.
Using SQL Server Management Studio
To display data and log space information for a database
- In Object Explorer, connect to an instance of SQL Server and then expand that instance.
- Expand Databases.
- Right-click a database, point to Reports, point to Standard Reports,, and then click Disk Usage.
Using Transact-SQL
To display data and log space information for a database by using sp_spaceused
- Connect to the Database Engine.
- From the Standard bar, click New Query.
- Copy and paste the following example into the query window and click Execute. This example uses the sp_spaceused system stored procedure to report disk space information for the
Vendor
table and its indexes.
To display data and log space information for a database by querying sys.database_files
- Connect to the Database Engine.
- From the Standard bar, click New Query.
- Copy and paste the following example into the query window and click Execute. This example queries the sys.database_files catalog view to return specific information about the data and log files in the AdventureWorks2012 database.
See Also
SELECT (Transact-SQL)
sys.database_files (Transact-SQL)
sp_spaceused (Transact-SQL)
Add Data or Log Files to a Database
Delete Data or Log Files from a Database
sys.database_files (Transact-SQL)
sp_spaceused (Transact-SQL)
Add Data or Log Files to a Database
Delete Data or Log Files from a Database