August 22, 2023 at 7:47 pm
Hi All,
I have a question about a disk space issue related to SQL Server. We have a 100 GB drive, with 90 GB being used space and 10 GB as free space. Within a span of two weeks, a log table has grown from 80 GB to 90 GB, but when we check the drive's free space, it still shows 10 GB available. I'm puzzled as I expected it to have filled the entire 100 GB drive.
Can anyone explain why we still see 90 GB used and only 10 GB free? Interested in any insights.
Regards,
Sam
August 22, 2023 at 7:54 pm
The db that table was in already had space allocated but that had not been used yet. Therefore, SQL can allocate that space to a table without having to get more space from the drive.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 23, 2023 at 11:12 am
Thank you Scott.
August 23, 2023 at 11:28 am
The db that table was in already had space allocated but that had not been used yet. Therefore, SQL can allocate that space to a table without having to get more space from the drive.
Can I assume , due to autogrowth the space has already been allocated to that object and not been used yet? if so, how to check it ? any DMV ?
August 23, 2023 at 12:13 pm
Here you go. You can see the allocated & unallocated space.
Frequently, log growth is because you either don't have log backups enabled while in full recovery, or you don't have frequent enough log backups. Check that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 24, 2023 at 6:11 am
Instant File Initialization (IFI) is a feature in Microsoft SQL Server that improves the performance of database file creation and growth operations. When SQL Server needs to create or grow a data file, it requires initializing the space by filling it with zeros. This process can be time-consuming and resource-intensive, especially for large files.
Instant File Initialization addresses this issue by allowing SQL Server to skip the zero-filling step for data files. Instead of writing zeros to the new or expanded space, SQL Server simply marks the space as allocated. This significantly reduces the time it takes to create or grow a data file, as there's no need to write a large amount of zeros.
However, it's important to note that Instant File Initialization only applies to data files, not to log files. Log files still require zero initialization due to their critical role in maintaining data consistency and recovery.
=======================================================================
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply