question

pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 asked pdsqsql-8017 answered

DBCC SHRINKFILE - Doesn't shrinking

Hello,
I am running DBCC SHRINKFILE but seems that it's not working.
I have ran same command few months back and it was working fine.
I have Sql Server 2014 and one of the database is almost 1.7 TB size and we are almost Disk space FULL, I know that it's not a good option but we are at the limit to increase the disk space and currently we don't have any other option to free up some space.
Our DB size is 1750 GB and Space Available is 700 MB when I look DB Properties.
I am running following command:
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

 set @DBFileName = 'SqlDBData'
    
 set @TargetFreeMB = 10000
    
 set @ShrinkIncrementMB = 5000
    
 -- Show Size, Space Used, Unused Space, and Name of all database files
 select
         [FileSizeMB]    =
                 convert(numeric(10,2),round(a.size/128.,2)),
         [UsedSpaceMB]   =
                 convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
         [UnusedSpaceMB] =
                 convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
         [DBFileName]    = a.name
 from
         sysfiles a
    
 declare @sql varchar(8000)
 declare @SizeMB int
 declare @UsedMB int
    
 -- Get current file size in MB
 select @SizeMB = size/128. from sysfiles where name = @DBFileName
    
 -- Get current space used in MB
 select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
    
 select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
    
 -- Loop until file at desired size
 while  @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
         begin
    
         set @sql =
         'dbcc shrinkfile ( '+@DBFileName+', '+
         convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '
    
         print 'Start ' + @sql
         print 'at '+convert(varchar(30),getdate(),121)
    
         exec ( @sql )
    
         print 'Done ' + @sql
         print 'at '+convert(varchar(30),getdate(),121)
    
         -- Get current file size in MB
         select @SizeMB = size/128. from sysfiles where name = @DBFileName
            
         -- Get current space used in MB
         select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
    
         select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName
    
         end
    
 select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
    
    
 -- Show Size, Space Used, Unused Space, and Name of all database files
 select
         [FileSizeMB]    =
                 convert(numeric(10,2),round(a.size/128.,2)),
         [UsedSpaceMB]   =
                 convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
         [UnusedSpaceMB] =
                 convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
         [DBFileName]    = a.name
 from
         sysfiles a

Is it I am reaching limit or "Space Available: is only 700 MB then it's not showing?

Previously few months back when I ran then it was showing me the progress and see as below sample result kind message:

 (2 rows affected)
    
 (1 row affected)
 Start dbcc shrinkfile ( SqlDBData, 1768564 ) 
 at 2021-07-19 15:52:32.250
sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @pdsqsql-8017,

Welcome to Microsoft Q&A!
Please see this document about DBCC SHRINKFILE: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15
Disk space is full, and shrinking is no solution to the disk exhaustion situation. You need to increase the disk space.
If you do not want to do log backup, you can change your recovery model into SIMPLE. This is not recommended if your database is a production database.

Best regards,
Seeya


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered SeeyaXi-msft commented

Thanks Seeya.
My Database is already in "SIMPLE" Recovery mode. So no Log backup and this is a Datafile I am shrinking not a log file as log file space is not an issue, most of all the space taken by Data file.
I am fully aware about "shrinking is no solution" but right now adding Disk space is not an option so we need to do the one time SHRINK operation.
Did you see my description why it's not shrinking?
Your insight in the issue really appreciated.

Thank you for your help!

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Good day.
What DBCC SHRINKFILE does, is all zone level actions. It will move used zones forward and remove zones that are not in use from the file. However, it will not remove and merge empty pages inside a zone, nor will it remove and merge pages from space in a page. So, there are many zones in a database with only one or two pages used, the effect of DBCC SHRINKFILE will not be obvious.
DBCC SHOWCONTIG can help you see a more detailed picture of the page. If there are fragments, consider rebuilding the index.

Best regards,
Seeya

0 Votes 0 ·
pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered

Thanks SeeyaXi.
But any specific reason it's not showing any impact?
How it will reclaim the Disk space as we are almost at full?

Do I have to restart Sql Server Service to see any impact as my disk space showing same size?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog edited

So you have a database that is 1750 GB full of which 700 MB is not allocated if I understand correctly. Then you can shrink the database at maximum 700 MB to 1749 GB (rounded). That is not particular meaningful.

You will have to add more disk. And if that is not an option, you will need to create some free space in the database. Here are a couple of options do this:
1. Purge data you can live without.
2. Drop indexes that you really need. (It is not uncommon to find databases with redundant indexes).
3. Compress tables or indexes.
4. If you are on Enterprise Edition, replace the clustered index with a clustered columnstore, this can be a big compression gain.

Although, with only 700 MB free, the latter two options may not really be available to you. If you have a 200 GB table you want to compress down to 150 GB, you will need 150 GB of disk space available, since SQL Server builds a new index structure. It does not compress in place.

Once you have freed up space inside the database, you could start shrinking. Although the only reason you would do that is that you desperately need the disk space for something else. Because I suspect that you will continue to pour data into that database.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered ErlandSommarskog commented

Thanks Erland.
Unfortunately this is sql server 2014 SP1 Standard edition so Can't use that features.
I might see any UnUsed indexex If I can drop it.
Compression I might look that option but not sure.
Disk space currently they can't increase.
Purging we try but nothin currently more we can do it.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQL 2014 SP1????

Compression also requires Enterprise Edition in SQL 2014, so that option is not available to you.

Can't purge, can't extend disk. Seems like you are caught up between a rock and a hard place then.

0 Votes 0 ·
pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered ErlandSommarskog commented

Hello,
I have question regarding DBCC SHRINKFILE to run as a job like weekly or bi-weekly (I know it's not advisable and recommended practice) but need to run it make some room for a while, what's the best way I can use the size?

This DB is in SIMPLE RECOVERY Model and need to Shrink only Data file not log file nothing thinking to Shrink Database unless someone suggest it.

Database size is almost 1.8 TB but after some shrinking manually for few days, Space Available shows in DB Property is 2600 MB.
It will change if no Delete activities happens but currently trying to do some cleaning activities.
Following size will be ok or I should use more size?
I wanted to make sure that job will close after running for desired size so need to know if running weekly what will be the idea size to use it?


 **USE UserDB;  
 GO  
 DBCC SHRINKFILE (DataFile1, 10);  
 GO**


Thank you for all your help!

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

10 is the target size, in MB as I recall. Good luck!

(If you have 2.6 GB free in a 1.8TB database, shrinking is completely pointless anyway.)

0 Votes 0 ·
pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered ErlandSommarskog commented

Thanks Erland.
I am completely Agreed with your point and not in a favor to do the Shrink but unfortunately I have to.
So you are suggesting to use 10 MB in or for for safe I can go with 1 GB?
I am just trying to be safe as job not much run longer.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Again, you have a 1.7 TB database. You say that there is 2.8 GB of free space. How do you expect to be able to shrink that to 1 GB?

You can shrink that database to 1.7 - 0.0026 which is, rounded to one decimal 1.7 TB.

You can run DBCC SHRINKFILE without any size argument and see what happens, but I would not be surprised if you don't reclaim a single byte.

And if you are really luck and regain 2.6 GB, what are you going to use those 2.6 GB of disk space for? Watch the database grow again.

You have two options:
1. Get more disk space.
2. Purge/archive data agreessively, at least a couple of hundred gigabytes, so that shrinking becomes a meaningful operation.

But in your current situation, shrinking is not an option available to you.

0 Votes 0 ·
pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered ErlandSommarskog commented

Thanks once again Eralnd.
I understood and fortunately application people started to purge the unwanted data so so we are right now fine but for future I wanted to set up the job so I can run periodically as a job schedule to avoid anything disk space issue as currently purging and shrink helped and of 'course your previous suggestions and help.
2.6 GB size is current space but it will change so trying to keep it standard size in my shrink parameters so I don't have to much change in once set up the job.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Again, what else are you going to use that reclaimed disk space for?

If you need to store something else on that disk, you may have to shrink. But if you want to have as much as space available for that database, do you really want to be in the situation where you shrink it to 1.2 TB, and then dies with disk full, because someone else downloaded 500 GB of p*rn movies or some other junk on that free disk space?

0 Votes 0 ·
pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered ErlandSommarskog commented

Erland,
I think Application has some kind of activities which stores logs and need to keep some data for certain period also but currently we are having hurdle to increase the disk space or moving another disk so only option to shrink the DB/File as not sure how much clean up ongoing we can do it.
Before running Shrink operation, We had lots of arguments as I tried to avoid to run it and that's why whole QA started here.
When data growth happens again then we may gonna have more space available to shrink it.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I think Application has some kind of activities which stores logs and need to keep some data for certain period also but currently we are having hurdle to increase the disk space or moving another disk so only option to shrink the DB/File as not sure how much clean up ongoing we can do it.

What are these logs? Log files stored outside SQL Server, but as on the same disk as the database?

Or log tables inside the database?

0 Votes 0 ·
pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered ErlandSommarskog commented

Erland,
Logs means all activity audit which storing into some of the tables so in same disk nothing logs what we think.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So they are logging into the database. Well, there is no point in shrinking it, since nothing else being stored on the disk.

It seems that you need more free space inside the database. Purging can achieve that. Shrinking cannot.

0 Votes 0 ·