Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
227
questions
0
votes
3
answers
242
views
SQL Server Space Reclaim
We have a sizeable SQL Server 2016 database (running on Windows 2016 Datacenter). A table was growing exponentially due to a bad SQL process. The bad SQL process has been resolved, but the specific ...
jf95
1
vote
2
answers
952
views
What realistic issues can occur from shrinking the log files on my database (either through the SSMS UI or issuing a DBCC ShrinkFile command)?
I've heard talk of potential corruption or data loss occurring from shrinking the database data or log files. I'm curious in particular about only shrinking just the log files, what are some real ...
- 35k
1
vote
1
answer
165
views
ETL process causes database to rapidly grow - Shrinkfile reduces size from 140GB to 2GB - Keep shrinking?
I have a database (SQL Server 2012) that is used mainly for ETL of data. Every hour SSIS packages run that truncates/deletes and reloads full datasets. The database keeps growing even though the data ...
- 11
1
vote
1
answer
3k
views
Is it safe to stop the shrinking of MSDB?
In a production server (Microsoft SQL Server 2014), the file MSDBData.mdf ballooned up to more than 450 GB, eating almost all the space in the server (only 38 GB remaining).
First thing, I created ...
- 13
0
votes
2
answers
4k
views
SQL Server - Log file not shrinking in AlwaysOn group
We have three-node AO setup in SQL Server. Primary node, one secondary node in Sync mode and another secondary node in Async mode.
Main Database on Primary node, log file-size is 434GB, Used 99.91%. ...
- 11
2
votes
2
answers
585
views
Log file won't shrink - suggestions?
I have a 225GB DB with a 390GB log file, which has only recently exploded in size. I am trying to shrink it - per these instructions: https://www.brentozar.com/blitz/transaction-log-larger-than-data-...
- 23
1
vote
2
answers
2k
views
Need to move TempDB to another drive. But its files cannot be dropped or shrunk
Today I had a task to move all TempDB files to a new, smaller drive (RAMdisk).
I analysed TempDB
files space usage - not all the space inside of its files was used so I decided that new drive is ok.
...
- 2,319
5
votes
2
answers
2k
views
DBCC SHRINKFILE on the distribution database
I have an .mdf file from distribution database which is growing.
I know the advantages and disadvantages of shrinking database/files.
Is it okay to shrink the .mdf file to some a smaller size.
Is ...
- 1,295
-1
votes
1
answer
3k
views
Reducing .mdf file size
I have a drive, :d, in my server where I have my .mdf file stored and now all of a sudden I notice that the drive :d has no space left.
When I checked the .mdf file of the database demo1, it is 1.6TB ...
- 1,295
0
votes
0
answers
53
views
Is a SQL Server DB worth shrinking? [duplicate]
I purged about 100 GB on 200 GB SQL Server v11 DB. The MDF file still show about 200 GB in the file explorer.
Everything looks fine, first tests shows 50% time treatments increase and the backup's ...
- 1
8
votes
1
answer
7k
views
What is DBCC SHRINKFILE actually doing?
I have a database which was 11TB in size. I recently truncated over 5TB of data from this database.
(I am fully familiar with all the reasons why you would not normally shrink a database please)
I'm ...
1
vote
0
answers
4k
views
How we can clear tempdb without restart MSSQL service [duplicate]
While checking my tempdb details, its showing that almost 90 GB is consumed. So I tried clearing all caches and perform shrinking, but still it's consuming 90 GB of space.
My drive became full where ...
- 21
0
votes
1
answer
3k
views
How safe is to use BACKUP LOG WITH TRUNCATE_ONLY
As per the title, how much is it safe to use BACKUP LOG db_name WITH TRUNCATE_ONLY in last STEP of any SQL-job execution? Randomly found on google that this Code line can be very harmful in use. But ...
- 189
2
votes
3
answers
2k
views
Initial Database Size Too Big
I have a clustered SQL 2016 standard database. It is set up in a virtual environment with a total of 250 GB of storage. However, my developer set the initial size of the databases to be 100 GB. There ...
- 21
10
votes
1
answer
999
views
SHRINKFILE Failure - Why does increasing file size resolve it?
I am running some SHRINKFILE operations to clean up a bunch of tiny, unnecessary files in a filegroup. For one of the shrinks, the command below results in an error:
DBCC SHRINKFILE (N'myfile' , ...
- 10.9k
1
vote
2
answers
184
views
Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express
I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to ...
4
votes
1
answer
2k
views
DBCC SHRINKFILE performance
I have a production SQL Server 2008 with 8 TB spread over several databases. I've deleted about 85% of the data in each database and MUST recover the disk space. The final SUM of all USED_SPACE will ...
- 193
1
vote
0
answers
24
views
How I can truncate and shrink the Logs file in mirroring (Full Recovery model)?
I am trying to truncate and shrink the database. I have taken the backup of transaction logs and now trying to shrink but it's executing with No effect.
I have used dbcc shrinkfile(). Please help
2
votes
1
answer
376
views
How to search Query Store for a specific word or command in a query?
I have an Enterprise Edition of SQL 2016 SP1, that has a database with Query Store active.
I want to search for a specific word or command in the saved queries. I have googled around and not seen ...
- 6,178
0
votes
1
answer
877
views
Index Maintenance - Reorganize After BIG SQL Server Shrink
I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database.
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@...
user141153
0
votes
1
answer
385
views
how to shrink(and also reduce size on disk) a very big log file in SQL server 2016 [duplicate]
I have a datawarehouse with database log of 800 GB.now I want to shrink it or reduce the disk space occupied by Log.ldf file. I tried to shrink file in several ways - took full backup , performned ...
1
vote
1
answer
906
views
Reduce pre-allocated disk space
We have a reporting SQL (2014 R2 Enterprise) system with a Reporting and Staging DB on it. The reporting DB is pre-provisioned at 4 TB across 8 data files. Currently it contains just shy of 1 TB of ...
- 1,126
2
votes
1
answer
598
views
I just deleted 19k hi-res images from a table column and have a few questions about shrinking
We just moved to a new image-storage method for our application, which means we don't need to store image byte arrays in the database any longer.
To that end, I have deleted (via an UPDATE statement) ...
- 365
2
votes
0
answers
1k
views
DBCC ShrinkFile not working [duplicate]
I have searched the site and found numerous posts on this issue, but none really answer what I am experiencing.
Background
( I know shrinkfile is bad, but in this case, it makes sense)
Our client ...
- 257
0
votes
2
answers
196
views
DBCC Shrinkfile after full backup restoration but before differential restoration
My database full backup file size is 760 GB and differential would be around 20 GB but the database size is around 2TB. As it is a production database, so to apply only a minimum downtime, i want to ...
- 1
2
votes
1
answer
85
views
How are pages reallocated in a a data file after shrinkfile sql server
I understand that shrinking the data files is a bad idea in SQL Server due to index fragmentation.
I want to ensure that I completely understand before arguing my point.
I know that when dbcc ...
- 123
0
votes
1
answer
325
views
After page compressing a huge table, the disk space didn't change. Do I need to shrink in any kind?
MSSQL: There is a daily partitioned table which was 2 GB and we needed to compress this table by page compression. After compression, the table size got smaller if I check from the table properties - ...
- 9
0
votes
2
answers
2k
views
Shrink Tempdb log file when reaching a specific size [closed]
how I can shrink tempdb log file when reaching a specific size?? I'm scheduling job to shrink tempdb but still I face a problem of disk space due to tempdb is full!
- 13
0
votes
1
answer
1k
views
MS SQL Server: DBCC ShrinkDatabase - Does it really not shrink the database below the minimum size?
Per Microsoft's documentation on DBCC ShrinkDatabase command, it shouldn't shrink the database below it's "minimum size".
But when I ran the command, my database data files' initial file sizes shrunk ...
- 35k
0
votes
1
answer
51
views
Shrinking the database partition if autogrow is turned off?
I want to know:
Are there chances of shrinking the database partition if autogrow is turned off?
Also, are there chances of data fragmentation and increase in seek times if we fully extend size ...
- 103
3
votes
1
answer
1k
views
How do I recover 350 Gigabytes of unallocated space
I was left with a scenario where my Database Initial size is 370GB.
When I checked the EXEC sp_spaceused, I can see almost 360GB of unallocated space .
Does unallocated space means unused space? If ...
- 1,580
0
votes
1
answer
77
views
Missing data in specific tables post log backup and shrink
We have two DB servers which are part of Always On. Recently we noticed that log backups haven't been running on them for sometime and their .ldf files were growing. We took a log backup and tried to ...
- 1
2
votes
2
answers
2k
views
Is it bad to shrink + reindex after removing a significant part of data?
I have a test environment running on SQL Server 2017 Express that uses only recent data (a job removes data older than three months once per month).
The job does the following:
Using a while loop, ...
- 1,261
3
votes
1
answer
7k
views
SQL Server shrink command 'estimated_completion_time' keeps going up
Running SQL 2012 R2...
Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64) Standard Edition (64-bit)
Ran the shrink command
DBCC SHRINKDATABASE(databaseNameHere)
(Yes, I know ...
- 133
8
votes
4
answers
29k
views
Why Can't I shrink log file in full recovery mode [duplicate]
I have a log file that is 302MB.
I have done a log backup which has left the log file mostly free (I can see this through the Disk Usage standard report)
If i try to run
DBCC SHRINKFILE (N'...
- 1,908
1
vote
1
answer
7k
views
Why did my Always On AG lock up?
I am in over my head here. tl;dr; did a shrinkfile (I know, I know) (shrinking a 100gb file with 10gb of empty space by 2gb), it ran for ~15 minutes, then everything broke. What I don't understand, is ...
- 113
2
votes
0
answers
2k
views
DBCC Refuses to Shrink a Data File - drive was full
Yes, fully aware of Paul's article, and "DON'T", et al...
This is SQL 2014 Enterprise - Build 12.0.5538 - SP2 CU3 - iron - 2x8 1TB RAM - all SSD.
There is an unused FILESTREAM data file:
file_id ...
- 311
0
votes
1
answer
2k
views
should I shrink the filegroup? how? SQL Server
I just have a question. We recently purged 1,5 millions of record from a table. The Current allocated space for the related Filegroup is 700GB, but the space Used is 300GB. Should I perform a shrink ...
2
votes
1
answer
420
views
defragment SQL server database
I have deleted some tables on a SQL server. Suddenly the size of the database jumped by 100Gb (whereas the deleted tables were taking 10% of that..).
I have read good and bad things about SHRINK ...
- 398
4
votes
1
answer
1k
views
Can't shrink Log file - "An invalid Floating point operation occurred. (Microsoft SQL Server, Error: 3623)"
When selecting the Transaction Log file from the dropdown on the Shrink Files screen in SSMS, I get the following error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management....
- 41
13
votes
6
answers
105k
views
Oracle shrinking / reclaiming free tablespace space
I have a Oracle 12c tablespace with the size of 90GB. Almost 40GB are free.
TB1 54 % 90880 41383 49497 46 98304 50 % ONLINE PERMANENT
The tablespace has 3 datafiles.
E:\ORACLE\...
- 998
1
vote
1
answer
4k
views
Killed DbccFilesCompact found running for days
There were 2 running, actually, the first one I killed went away quickly. The second is in suspended status, from sys.dm_exec_requests the percent_complete doesn't change (88.87) and the ...
- 29
1
vote
2
answers
61
views
Recovery and Shrink [closed]
I am trying to change the Recovery Mode from Full to Simple if the database doesn't have any Full or T.Log backups setup from past week days.
The problem I am facing with the below script is it's ...
- 41
1
vote
1
answer
73
views
Better Way of Releasing Massive Unused storage from Data Files to Server
After we did massive truncate of data, there is a huge unused space in multiple databases data files, and I wonder what would be the best way of releasing it back. I know most of the experts are ...
- 41
1
vote
2
answers
2k
views
Finding extents of Oracle BIN$ segments
I am looking for information on recyclebin extents.
When dropping a table its segments are renamed to 'BIN$...' system generated names and the extents are hidden.
Will those extents still stop me ...
- 1,456
1
vote
1
answer
727
views
Shrinking mdf files - SQL Server 2012
I am in need to shrink a data file in SQL Server 2012. I am using shrink files option in tasks of the database and am using "Reorganize pages before releasing unused space" option. SQL Server says ...
- 59
3
votes
3
answers
1k
views
Should I run multiple DBCC Shrinkfile if files are in different disk arrays?
We just deleted about 45% of the data in a database, and we want to reduce the size of the mdf files (now we have a "delete old data job", so it won't grow that much again in the near future).
My ...
- 141
10
votes
2
answers
18k
views
How to free the unused space for a table
This question is asked like tens of times, and to my surprise such a simple requirement comes so difficult. Yet I can't solve this problem.
I use SQL Server 2014 Express edition with 10GB limit of ...
- 1,417
2
votes
3
answers
2k
views
Does shrinking log file affects the indexing in SQL Server?
In database log file grows from 32 GB to 70 GB and % of log space used become > 97%. After I took the log backup then %of log space used comes < 1% but size remains 70 GB .
Now I want to shrink ...
- 185
2
votes
2
answers
2k
views
Reclaim disk space from large database's primary filegroup after truncating tables (other than shrink)
We have an 8 TB SQL Server 2014 SP1 database, with one primary filegroup, spread across multiple files on different drives. This is part of an AlwaysOn AG with multiple secondaries (some async).
One ...
- 1,144