Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
1
vote
1answer
36 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 ...
1
vote
0answers
14 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
1answer
76 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 ...
0
votes
1answer
62 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,
@...
0
votes
1answer
54 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 ...
0
votes
1answer
57 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 ...
2
votes
1answer
480 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) ...
2
votes
0answers
37 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 ...
0
votes
2answers
37 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 ...
2
votes
1answer
43 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 ...
0
votes
1answer
23 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 - ...
-1
votes
2answers
167 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!
0
votes
1answer
150 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 ...
0
votes
1answer
29 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 ...
0
votes
1answer
85 views
How to 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 unallocated space .
Does unallocated space means unused space ? If ...
0
votes
1answer
43 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
vote
2answers
198 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, ...
3
votes
1answer
563 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 ...
5
votes
4answers
4k 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
vote
1answer
466 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 ...
2
votes
0answers
369 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 ...
0
votes
0answers
655 views
unable to shrink tempdb
My production server environment is Microsoft SQL Server 2014 (SP2-CU10-GDR).
I split my tempdb files into 8 files (I have 8 CPU )
One day, my tempdb size 720 GB (each of file 90GB *8 )
I need to ...
0
votes
1answer
319 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
1answer
60 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 ...
4
votes
1answer
189 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....
2
votes
3answers
6k 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\...
2
votes
1answer
650 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 ...
1
vote
2answers
56 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 ...
1
vote
1answer
40 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 ...
1
vote
2answers
472 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
vote
1answer
283 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 ...
3
votes
2answers
374 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 ...
6
votes
2answers
2k 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 ...
2
votes
3answers
426 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 ...
2
votes
2answers
412 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 ...
-4
votes
2answers
61 views
Scared of ShrinkDB so will this alternative be OK?
I've read too many horror stories to even touch any T-SQL command with SHRINK in it, but I need to sort out a number of my SQL Server 2008 databases that have bloat.
My databases consist of a simple ...
3
votes
1answer
511 views
DBCC SHRINKFILE for Log and Data when using Availability Groups
We had an issue where one of our database tables grew by 40GB by mistake. The database is part of an Availability Group with one primary and two replicas. While trying to delete the extra records we ...
5
votes
2answers
268 views
Determining what objects SHRINKDATABASE will free space from
First off, yes, shrink is bad. Don't use it, ever. I understand. Unfortunately, it's kind of SOP and I'm not in a position to argue since it's usually a stopgap until we can get a maintenance window ...
2
votes
3answers
825 views
SQL Server : Log File Shrink
We are running SCCM 2012 r2 Reporting services and the SQL Server log file has got out of hand due to the backup not being set up properly and I'd like to confirm something before I continue.
I have ...
1
vote
2answers
3k views
Large transactional log won't shrink after transactional backup
I'll start by saying our databases use full recovery mode and we run SQL Server 2014.
Over the last few days our backups appeared to spiral out of control to the point that our full/differential ...
0
votes
1answer
57 views
Shrinking bigger databases [duplicate]
Hello masters of MS SQL
I have a kind of a problem on my hands. We have quite big DB (5TB) and we've just "enabled" some software archiving which means that most of the data will migrate to other DB'...
-1
votes
2answers
83 views
How to schedule and perform shrinking of database-files
am using the below code to peform shrink the db to release unused space as my db's log files are growing exponentially.
But i wanted to use this as a scheduled event,running every two days at night ...
3
votes
1answer
73 views
Is it a good idea to shrink prior to a SQL Server upgrade to a new instance
We currently have a 100GB database which is in simple recovery mode, and it is running on 2008 R2. We have a new server with SQL 2016 Enterprise installed, ready to go - and we're building a migration ...
2
votes
1answer
114 views
Database free space not being reused
At the company I work for, we have encountered a problem where it seems that SQL Server 2008 R2 is not correctly calculating the free space in databases. As such databases on that server are growing ...
4
votes
1answer
834 views
Shrinking during backup
All,
I'm familiar with the good reasons not to shrink a database and I am in the process of shrinking a 2TB one. We have a large database that gets backed up each day (by policy) and because of the ...
0
votes
1answer
301 views
Risks associated with automatically shrinking TempDB as a SQL agent job?
I have a situation where I have a constraint on TempDB to not exceed 350 GB in size. I'm using autogrowth 10 (percent) on TempDB, as I've read that it is supposed to be best practice on databases ...
5
votes
3answers
360 views
How do shrink *right* when required?
I've been reading all the articles (and comments) linked from Stop Shrinking Your Database Files. Seriously. Now. I understand that shrinking is bad.
What I have a hard time figuring out is how to do ...
2
votes
1answer
203 views
Database transaction log growth who is the cuplrit? [duplicate]
There are abundant information on how to shrink a growing Transaction log, from frequent log backups, to changing the RECOVERY from FULL to SIMPLE and truncate. A daily background job takes care of ...
4
votes
3answers
2k views
Releasing free space in mdf file to OS [duplicate]
We have a 120GB database. There was table with 60GB of data which is useless and we have truncated it.
Now database size is 120GB with free space 60GB. The database will not grow up to 60GB in at ...
0
votes
1answer
69 views
Fix ShrinkFile / ShrinkDb database
my DBA obviously thinks that it is best practice to use shrinkdb / shrinkfile all the time some bigger table is truncated / deleted just a little bit.
I tried to talk sense to him and explain him how ...
