Insert into increased database size to maximum

Question

Insert into increased database size to maximum

ktflash

Default port

Points: 1400
More actions
June 27, 2018 at 10:19 am

#413976

Hi,
sorry first of all for the nervous writing and multiple edits, i think i srewed up big time and try to provide as much information as possible
for testing purpose i used a select into command and created a new table.
My issue is the amount of rows was so big that the database size is now capped at the disk file it is stored.
How do i deal with this?
My problem is that the insert failed and that means the table is empty.
So where is the data stored and how do i remove it?
I already dropped the table to be sure
Its the .mdf file on a seperate disk that has grown out of control
Edit: i used shrink database files, but it "only" cleared 157gb
theres still 67% unallocated disk use
and after using the shrink data base theres a file named same as the mdf with of data type dbcc-7, is that a snapshot of the dtb? and can i delete that without any issues?
somehow it says both files are the same size, but that size is > the disk size and it says theres still space (150gb) left after shrinking
99,9% unalloacted transaction logs, but they are separate from the .mdf in the. ldf file which is on a seperate disk and not too big of an issue
for my comprehesion: has the server reserved size in the mdf file for the expceted growth of the database and now that the data was not inserted, theres alot empty space reserved on the disk?
Edit: is there an issue with using the shrink option to a certain size?
the report shows me the size it has growed during the insert, so is there a any possible issue with reducing the size so something like the size after some of the growths?
if i didnt missunderstand the last post in this topic, the server reduces the size of the file itself over time? if so, how long does it take?
https://www.sqlservercentral.com/Forums/Topic1319271-146-1.aspx
I want to be the very best
Like no one ever was
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

To verify what files are used by a specific database and how much of them are used, you can run this script in the database:
SELECT DB_NAME() AS database_name, f.file_id, f.name AS file_name, f.type_desc, f.physical_name, f.size/128 AS size_MB, FILEPROPERTY(f.name, 'SpaceUsed')/128 AS used_MB, f.size/128 - FILEPROPERTY(f.name, 'SpaceUsed')/128 AS available_MB, CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END AS growth, f.is_percent_growth FROM sys.database_files f

Note that once you shrink a database or database file, there is a good chance that the data in the tables is now fragmented:
https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

After doing that initial shrink, is there still inadequate free disk space? You mention it cleared 157 GB, how many other database files are on the same disk and what is their rate of growth per month? I'd be concerned about trying to shrink a database to the point where it didn't have practically any unused space within it. I'm not sure what the dbcc-7 file is offhand, to see if it is part of some other database you can look at:
SELECT d.database_id, d.name AS database_name, f.file_id, f.name AS file_name, f.physical_name, f.type_desc, f.state_desc, f.size / 128 AS size_MB, CAST(f.max_size AS bigint) / CAST(128 AS bigint) AS max_size_MB, CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END AS growth, f.is_percent_growth FROM sys.master_files f INNER JOIN sys.databases d ON f.database_id = d.database_id ORDER BY d.name, f.data_space_id DESC, f.file_id

ktflash Default port Points: 1400 More actions · Answer 2

As i expected and saw in the report already, its alot empty space and the unused space is immense:

So my questions are still existing:

- if i remember correctly, growth of mdf files slows down the performance, if so:
--> how do i revert this issue of fragmentation of the mdf file?
--> is there any issue with decreasing the mdf file back to a certain size, lets say, 1,5 TB (screenshot above says 0,82 TB

SELECT d.database_id, d.name AS database_name, f.file_id, f.name AS file_name, f.physical_name, f.type_desc, f.state_desc, f.size / 128 AS size_MB, CAST(f.max_size AS bigint) / CAST(128 AS bigint) AS max_size_MB,
  CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END AS growth, f.is_percent_growth
  FROM sys.master_files f
  INNER JOIN sys.databases d ON f.database_id = d.database_id
  ORDER BY d.name, f.data_space_id DESC, f.file_id

--> the file is not listed here
--> looking into the file directory the next day, the file was gone, i guess it was a temporary file?

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

Again, I suppose the real issue is how much free space do you need on the disk, not how much unused space do you have in the database. Have you done analysis to determine the rate of growth of this database and any other databases that share the F: drive? If no other databases use the F: drive then it may not matter. I know it looks odd to have so much unused space in the file GUE_BI.mdf, but unless there's a need for something else to consume the free space, it doesn't make sense to force the database to give it up.

The only way to resolve the issue of the fragmentation would be to perform some index maintenance on ones that are fragmented such as a rebuild. Do you use some kind of script to maintain indexes such as one of these:
https://ola.hallengren.com/
http://www.minionware.net/products/reindex/

The only safe way to shrink a file would be to try to use TRUCNATEONLY parameter in the shrink command, but depending on where in the file the used space is this may not free up space since it only frees up unused space at the end of the file.
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql?view=sql-server-2017

ktflash Default port Points: 1400 More actions · Answer 4

Update on the situation:
- there was barely any growth before the incident
- Its the only database on the disk
- in the meantime the the .mdf size has shrunk more so i have 650gb left on the disk (without me doing anything) --> theres been a ola hallengren skript running over the weekend, as its standard on server, maybe that reduced the size
--> Data Usage report of the database shows no automatic decrease in size, last row is the automatic growth that was caused

In the very beginning i was very nervous because the disk was full and i expected to have blown up the disk space with dumb data.
Since in the meantime i know its only reserved empty space for future growths it doesnt sound too bad.
I just remember an external expert talking about multiple growths slow down the performance.
If true, is it because the mdf file has been fragmented or something?

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

ktflash - Tuesday, July 3, 2018 8:10 AM
...In the very beginning i was very nervous because the disk was full and i expected to have blown up the disk space with dumb data.
Since in the meantime i know its only reserved empty space for future growths it doesnt sound too bad.
I just remember an external expert talking about multiple growths slow down the performance.
If true, is it because the mdf file has been fragmented or something?

The last point you mention about having many small growths could lead to physical fragmentation of the data file on the disk itself. Since you say there is no other database on the same disk then this is not likely to be a problem here.

Another factor is when a database file grows, there is some time involved for SQL Server to claim the free disk space as unused space in the database file, especially if you're not using what's called "instant file initialization". If this is a production database, to avoid transactions having to wait for the database files to grow, many people like to schedule the file to grow when there is less activity on the server where they know the unused space is low.
Here's an article that talks about instant file initialization and what is required in the Windows OS to configure it:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-2016 

ktflash Default port Points: 1400 More actions · Answer 6

hmmmm,

i need to backup and restore the DTB from the system to another server for developing
but the dev sys has a slightly (2 compared to 2,8) smaller disk, so i cant restore the dtb there, since the first shrinking wasnt enough
although most of the mdf size is unallocated space

i dont know any other way, besides upgrading the disk which is currently no option, sadly

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help