Sql How to Get Size and Read Write Times

In this commodity, we will discuss the actual database file size and gratuitous space in the database file size with the help of monitoring script and compress solutions. While creating a database initial size of the database files (Data File and Log File) tin can be defined by us, with the Autogrowth and MAXSIZE parameter. The initial size volition be the same as the model database if by risk user forgot to ascertain the initial size parameter while creating a database or practice the same intentionally.

At the get-go, size of the database tin can be gear up up using the SIZE parameter and how much maximum space tin can be occupied by a database can exist defined using the MAXSIZE parameter. How fast a database file can reach its maximum space tin can exist resolved using the FILEGROWTH parameter. Nosotros are facilitated in a way we define the FILEGROWTH parameter. Information technology leaves united states of america with two options either by specific absolute value or past per centum, while in both cases we must define the value in MB format. There will be no purlieus on the growth of a database file if we practise non take care of defining the MAXSIZE parameter, equally the default value is UNLIMITED.

SQL Server tin non commit whatsoever transaction to disk storage in the situation where disk is full so it's always good to define the MAXSIZE parameter beforehand to avoid such situation. UNLIMITED would fill up the disk storage but earlier that, information technology makes several spaces tinier than earlier. Even this has a very hazardous impact on the operating system equally well if it doesn't have plenty infinite to execute its arrangement programs. We also need to take into consideration the size of multiple databases earlier defining the MAXSIZE attribute.

Gigantic sized tabular array data will be dispensed into multiple files in a larger platform in gild to decrease the amount of the disk contention. To increase the I/O performance, SQL Server supports multiple file-group with secondary files, client data and index can be stored in the secondary file-group. The same tactics can be applied with the database log file in which the user tin create multiple files with a single database and information technology is recommended to store log files on a different bulldoze than the principal information file (mdf).

The database file can increment in size without any boundary if the MAXSIZE parameter is not been configured by the user. It is highly suggested to construct an alert with a view to get rid of the chances of a full disk. When certain weather condition exist we tin can report an issue or display information technology on health monitor using alert. There tin can exist any issues and solutions with each issue to tackle it quickly. For example,

• Complimentary space in Data file or Log file
• Heavy Transaction Log information file
• Increasing Log file Size and non shrinking due to heavy transaction stuck

Database log file size volition exist populating continuously till the side by side transaction backup happens if the database recovery model is not uncomplicated and Transaction backup or Log shipping is not set up. However, upon creating a Log backup, there is definitely going to exist a free space. For instance, consider taking the log backup of the file with size 1024MB. What happens after this is that all logs will be removed inside the file and as a event of this at that place will be a plethora of space.

To shrink a file in SQL Server, we always use DBCC SHRINKFILE() command. This DBCC SHRINKFILE() control will release the complimentary infinite for the input parameter.

DBCC SHRINKFILE ( [ FileName / FileID ] , [ EMPTYFILE / [ nMB ( Amount for Shrink ) , NOTRUNCATE / TRUNCATEONLY ] ] )

The file will be shrunk by either file name or file id using the command above. The shrinking amount size will be considered as specified with the command in a unit of MB. Now what could be the Amount for Shrink in the DBCC SHRINKFILE()?

Permit's talk over with an instance, In the above image, the AdventureWorks database has a one Information file AdventureWorks2016CTP3_Data with space in the file is 500 MB larger and information technology also possesses free infinite of 208.8MB. Where Logfile AdventureWorks2016CTP3_Log is in existence with 600 MB with complimentary space in the file is 362.9 MB. The below script can be put on to utilise by a user to get free infinite for the database files.

Become a list of database files with size for all databases in SQL Server:

• sys.master_files DMV returns the database files in detail with the current size of each file
• master_files system object volition return details for each database of the SQL Server instance

SELECT DB_NAME ( database_id ) As database_name , type_desc , proper name As FileName , size / 128.0 Equally CurrentSizeMB FROM sys . master_files WHERE database_id > vi AND type IN ( 0 , i )

Get a list of databases file with size and free space for a database in SQL Server:

• sys.database_files DMV returns the database file with the details
• sys.database_files is a system object which returns data for the selected database only

SELECT DB_NAME ( ) Every bit DbName , name Every bit FileName , type_desc , size / 128.0 Every bit CurrentSizeMB , size / 128.0 - CAST ( FILEPROPERTY ( proper noun , 'SpaceUsed' ) AS INT ) / 128.0 Every bit FreeSpaceMB FROM sys . database_files WHERE type IN ( 0 , i ) ;



At present, free space for the file in the above query upshot ready will be returned past the FreeSpaceMB column. 600 MB of space will be preoccupied with organisation deejay into the file organization for the AdventureWorks2016CTP3_Log file, Nonetheless, 362 MB is complimentary to shrink it up to 238 MB (600 – 362 = 238).

The example taken higher up refers to a small database with a very lesser original database file size and gratuitous infinite in a file for the AdventureWorks. Simply, in real scenarios (i.e. on Production Servers), database size can be humongous with a heavy flow of database transactions. The log file volition store the transaction log if your database is in full recovery model. Used space will exist neither released nor flushed by log file until the side by side Log backup and database file size will grow up with the log.

When any transaction blocks or stuck whatever database transactions, database log file size can be quickly increased and cross the expected output file size to handle this issue. The transaction lock problem can ascend with Query performance and the same can happen with the client-side application also. When transactions are truncated and log backup is generated free infinite will be available. When whatsoever bulky Delete, Driblet or Truncate operation is executed on the tabular array, there can be a large amount of free space made available for data files. Free space can also be reclaimed past dropping a huge index also.

Query as above should be performed in each database to monitor the complimentary space for all databases with the criteria and on the upshot set, space monitor criteria must exist applied. Users tin can apply sp_msforeachdb() procedure to execute monitor T-SQL queries in each database of the SQL Server.

T-SQL Query to get total space and free space for database files:

one ii 3 iv 5 vi 7 8 9 10 11 12 xiii 14 15 16 17 18 nineteen 20 21 22 23 24 25

CREATE Table #FileSize ( dbName NVARCHAR ( 128 ) , FileName NVARCHAR ( 128 ) , type_desc NVARCHAR ( 128 ) , CurrentSizeMB DECIMAL ( 10 , 2 ) , FreeSpaceMB DECIMAL ( 10 , 2 ) ) ; INSERT INTO #FileSize ( dbName , FileName , type_desc , CurrentSizeMB , FreeSpaceMB ) exec sp_msforeachdb 'use [?]; SELECT DB_NAME() Equally DbName,         name Equally FileName,         type_desc,         size/128.0 AS CurrentSizeMB,         size/128.0 - CAST(FILEPROPERTY(name, ' 'SpaceUsed' ') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type IN (0,1);' ; SELECT * FROM #FileSize WHERE dbName Non IN ( 'distribution' , 'main' , 'model' , 'msdb' ) AND FreeSpaceMB > ? ; DROP TABLE #FileSize ;

In the in a higher place query, for every individual database file size will be stored by a temporary table called #FileSize. Thereafter, Necessary filter or calculative logic will be applied by Database Administrator Alert with adding logic can exist set as below:

• If Free Space in the file is exceeding the limit (nMB)
• If the percentage of Costless space is exceeding n% compare to total space
• If the electric current Size of a database file is exceeding the limit (nMB)

Activity must be taken relative to the priority and level of the trouble by DBA on the alarm mail database.

Space tin can exist reclaimed by making a move either:

• When the disk is in close proximity of getting full and then we might accept to consider moving a file to a different disk
• If a database is in full recovery model and log shipping isn't configured, make a database to the simple recovery model
• If a database is in full recovery model and log shipping isn't configured take a Full backup and Transaction Log backup
• Change the Max Size parameter value for the database file, if it needs to be expanded
• Disable auto-growth parameter for the database file and limit with the (northward)size equally the priority of the database

Infinite alarm can be configured on a health monitor with a mail. Nowadays we have many 3rd-party monitors in the market to monitor database file size, where you can ready the output of this query with the priority alert type. Users can attach this logic in Procedure likewise with returning mail to the database monitoring team if whatever alert criteria succeed for the database file size.

Conclusion

We discussed how we can monitor the actual database file size and its free space with the assistance of monitoring script and shrink solutions in this article. If you have any questions, experience free to ask in the comments department beneath.

• Author
• Contempo Posts



Jignesh has adept experience in Database Solutions and Compages, working with multiple customers on Database Pattern & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.

View all posts by Jignesh Raiyani

garcialoat1946.blogspot.com

Source: https://www.sqlshack.com/how-to-determine-free-space-and-file-size-for-sql-server-databases/