Basics of SQL Server Database Instant File Initialization

SQLServerF1

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the operations like Create a database, Add files, log or data, to an existing database, Increase the size of an existing file (including autogrow operations), Restore a database or filegroup. File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros.

In SQL Server, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.

How to Enable Database Instant File Initialization in SQL Server?
Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy. Important point to note regarding instant file initialization is that Instant file initialization is not available when TDE is enabled.

To grant an account the Perform volume maintenance tasks permission:
On the computer where the backup file will be created, open the Local Security Policy application (secpol.msc).
In the left pane, expand Local Policies, and then click User Rights Assignment.
In the right pane, double-click Perform volume maintenance tasks.
Click Add User or Group and add any user accounts that are used for backups.
Click Apply, and then close all Local Security Policy dialog boxes.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Script to Gather Disk Space Details and Database File Free Space Information

It is a common requirement to find the details about the total free disk space, database files total size, free space available in database files, name of the database files, type of database file and the physical location they are located at.

Below is the code which returns Free disk space on a drive, name of database, logical and physical file names of all databases, total size, free space available inside the database files and the physical location where they are stored.

USE tempdb
    GO
      CREATE TABLE #TMPFIXEDDRIVES (
    DRIVE CHAR(1),
    MBFREE INT)

    INSERT INTO #TMPFIXEDDRIVES
    EXEC xp_FIXEDDRIVES

    CREATE TABLE #TMPSPACEUSED (
    DBNAME VARCHAR(1500),
    FILENME VARCHAR(500),
    SPACEUSED FLOAT,
    growth int)

INSERT INTO #TMPSPACEUSED
    EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed,growth from sysfiles''')

SELECT C.DRIVE,
    CASE 
    WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
    ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
    END AS DISKSPACEFREE,
    A.NAME AS DATABASENAME,
    B.NAME AS FILENAME,
    CASE B.TYPE 
    WHEN 0 THEN 'DATA'
    ELSE TYPE_DESC
    END AS FILETYPE,
    CASE 
    WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
    ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
    END AS FILESIZE,
    CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE_MB,
    (d.growth*8)/1024 as AutoGrowth_MB,
    B.PHYSICAL_NAME
    FROM SYS.DATABASES A
    JOIN SYS.MASTER_FILES B
    ON A.DATABASE_ID = B.DATABASE_ID
    JOIN #TMPFIXEDDRIVES C
    ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
    JOIN #TMPSPACEUSED D
    ON A.NAME = D.DBNAME
    AND B.NAME = D.FILENME
    ORDER BY databasename

/*<b>*/DROP/*</b>*/ TABLE #TMPFIXEDDRIVES
/*<b>*/DROP/*</b>*/ TABLE #TMPSPACEUSED

Output returned will be in below format

DRIVE

DISKSPACEFREE

DATABASENAME

FILENAME

FILETYPE

FILESIZE

SPACEFREE_MB

AutoGrowth_MB

PHYSICAL_NAME

S

923.48 GB

DBName

LogicalFileName

DATA

218.16 GB

8500.25

200

S:\DataFile.mdf

DRIVE – Drive where the database file resides.
DISKSPACEFREE – Free disk space available in the drive.
DATABASENAME – Name of the database.
FILENAME – Logical database File Name.
FILETYPE – What type of file it is, Data File, Log File.
FILESIZE – Total size of the database file.
SPACEFREE_MB – Free space available inside the database file.
AutoGrowth_MB – Auto_Growth size of the database file.
PHYSICAL_NAME – Physical location and physical database file name.

Below script will provide with space details of a specific database and its files. This information will be useful to find which database files are large, how much free space is still available in the database files, so that we can shrink the file to release the available free space.

select
       name
     , filename
     , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
     , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
     , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
     from dbo.sysfiles a 

name

filename

FileSizeMB

SpaceUsedMB

FreeSpaceMB

tempdev

T:\Data\tempdb.mdf

40960.00

4.75

40955.25

templog

T:\Log\templog.mdf

2048.00

365.86

1682.14

tempdata

T:\Data\tempdata.mdf

40960.00

4.56

40955.44

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Low Disk Drive Space Issue Caused by SQL Server Database Files

Low disk space is often raised as alerts from monitoring tools or at times we notice when we RDP to the server or where the SQL Server or databases appear down and we notice insufficient space errors.

– Need to determine what type of drive is filling up. Is it something that will threaten the integrity of the SQL process? For example a transaction log drive filling up is more urgent than a backup drive filling up.

– If drive only contains data files then check the file which is using lot of space. We can find the file size, path and free and used file sizes of SQL database files from SSMS using below query

Select name , filename 
     , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
     , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
     , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
     from dbo.sysfiles a

– It is not good to shrink data files as it will cause lot of fragmentation and dramatically slowdown the performance, so we need to check on adding addition disk space to the drive or plan to move some data files to another drive. Capacity planning needs to be done to estimate the required disk space for database files for next one year.

– If the drive contains transaction log files, run the below query to find the log files which have high percent of space used and large log files. you can try to easily shrink the log files sizes. However, you might run into issues when the engine does not let you shrink the file, for example: replication, log shipping or mirroring holding the log, a long running transaction, database set to Full recovery and no backup has run, etc. You will need to clear these conditions before space can be recovered from the log files.

DBCC SQLPERF('logspace')

Above query should give an idea on which log file is large and if it is FULL.
Below query will provide the reason on why the log file is now allowed to shrink

Select log_reuse_wait_desc, name from sys.databases

– If backup files causing low disk space. This depends on each environment on how the backup are configured, need to review the back maintenance plan/jobs and check for the retention period of the backups. Verify the backup path to see if more backups are stored than required, if yes, then check and remove backups which are not required after confirming with all stakeholders

– If the files taking up the space are NOT SQL Server related. For example, temp files, application files, hibernate files, general user files, etc. In these cases consult server team and other stakeholders for the server.

Hope this was helpful.

Happy Reading,
SQLServerF1 Team

 

Unable to Release Free Space from Database Data File Even After Shrink

I have worked on a issues where there was a requirement to release free space from the data file. There was a database whose data file grew very large. Development team worked and deleted/archived lot of data and now there was lot of free space available inside the database file. The next step was to run Shrink command to release the free space inside the file.

Although, we recommended against Shrinking, but the Development team mentioned that the database will not grow much as we will be archiving the data regularly, so we need to get the free space from the data file which can be used by other databases on the server.

So, we started working on releasing the free space available in the data file, but we ran into issues where we tried standard options, but they were not working. Below are some steps which we tried to release the free space.

– Ran DBCC SHRINKFILE against the data file. This command completed successfully, but did not release any free space.

– Tried running ShrinkFile command in batches to release small chunk of space, but still was not working.

– Rebuilded all the indexes in the database and then issued SHRINKFILE command, but still the free space was not released.

– Ran DBCC UPDATEUSAGE to correct any page and row count inaccuracies in the catalog views. Then ran the ShrnikFile command, but still we could not release the free space.

USE DatabaseName;
GO
DBCC UPDATEUSAGE (DatabaseName) WITH NO_INFOMSGS;
GO

– We are SQL Server 2008 R2 SP2, so no known issues related to Shrink issues.

– Checked for any GhostRecords/Cleanup, but no such issues found.

– Finally, we were able to fix the issue by below steps.

1. Ran DBCC CLEANTABLE which reclaims space from dropped variable-length columns in tables or indexed views. Below code is used to reclaim space from all tables in current database.

Use DatabaseName
GO
EXEC sp_msforeachtable ‘DBCC CLEANTABLE(0, ”?”) ‘;

2. Ran SHRINKFILE command which now released the free space.

Caution using DBCC CLEANTABLE command, DBCC CLEANTABLE runs as one or more transactions. If a batch size is not specified, the command processes the whole table in one transaction and the table is exclusively locked during the operation. For some large tables, the length of the single transaction and the log space required may be too much. If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows. DBCC CLEANTABLE cannot be run as a transaction inside another transaction. This operation is fully logged.DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after you make significant changes to variable-length columns in a table or indexed view and you need to immediately reclaim the unused space.

Happy Reading,
SQLServerF1 Team