Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
209
questions
0
votes
1answer
66 views
How to reduce big size database files (Data File ) SQL Server without SHRINK?
I found a couple of 'question' regarding shrinking big datafiles but I think that is not very accurate to my situation. So here is my question
I have a big database with almost 8TB of Data File Size ...
0
votes
1answer
30 views
Shrink the data file on Azure SQL MI
I have a 600GB DB with 400GB free space in the data file on Azure SQL MI.
I am planning to shrink it and save the storage, but it's a critical DB with the user sessions.
There are two plans:
Plan A: ...
0
votes
0answers
58 views
transaction log grows despite recovery=simple. SHRINKFILE does not release space
I have this script that restores a DB, sets recovery=simple, trims some tables & shrinks files:
RESTORE DATABASE [ABC] FROM DISK (....)
ALTER DATABASE [ABC] SET RECOVERY simple
go
exec [ABC]..trim
...
0
votes
1answer
450 views
Oracle 19c cannot shrink blob column
I deleted a bunch of old data from a schema, including BLOB data, and afterwards I tried to shrink and compact everything. I managed to shrink all tables (enable row movement, shrink space compact, ...
0
votes
2answers
322 views
What is Fastest way to shrink a datafile?
I have a 4 Tb SQL Server 2008 R2 database. I'm going to move this DB to a new server with a new release of SQL Server 2012. (the application does not support SQL Server > 2012, but that is not the ...
0
votes
1answer
140 views
Oracle 11g - Decreasing Big Table Size
I want to reduce the size of a table.
I thought if I could do this with export / import, but when I check it
with the ESTIMATE_ONLY option, it doesn't seem to work.
I have no chance of using ...
0
votes
2answers
55 views
What could be the reason for shrinking the transaction log file before and after the database backup?
I'm an accidental DBA. I'm the only person in a company who has some experience with SQL Server and I have to deal with a system inherited from another company after it was acquired. People who ...
1
vote
1answer
179 views
Cannot release unused space in SQL Server mdf file [duplicate]
I have a huge mdf file (61 GB) and it is 95% empty
I want to release unused space.
I tried to Shrink the file
but when I click OK the Shrink screen closes and nothing happens
the file still 61 GB
How ...
0
votes
2answers
134 views
Is DBCC SHRINKFILE better than DBCC SHRINKDATABASE? [duplicate]
In the documentation to DBCC SHRINKDATABASE, Microsoft is warning to run the command because it's i/o expensive.
However, in the documentation to DBCC SHRINKFILE, there is no such warning.
Now when we ...
0
votes
1answer
34 views
Does DBCC SHRINKDATABASE free physical disk space during its run?
I want to run exactly this command:
DBCC SHRINKDATABASE mydb
When I run this just for a while and then kill it, will the physical database file be a little bit smaller?
In other words: Does the ...
0
votes
4answers
875 views
How to shrink large LDF [duplicate]
I have a client with a SQL2016 DB with a 100Gb MDF and a 4Gb LDF. The DB is set to Simple.
I can't get it done to shrink the LDF to a minimum. How can I shrink the LDF to a normal level?
Does this ...
1
vote
1answer
818 views
Release unused space in SQL Server database
I have a SQL Server database with roughly 1.5 TB test data (size of core tables are calculated using sp_spaceused 'tablename'). At the time of data creation process, I have allocated 350 GB to each ...
0
votes
2answers
61 views
What's the difference between "shrink" file vs simply reducing the initial size?
I'm totally new in DB Administration. So i got confused about the "file shrink" functionality offered on SQL Server Management Studio (SMSS) or via T-SQL. Why I would I need to use that &...
1
vote
1answer
65 views
Almost Empty Data File is still taking time to Shrink in SQL Server
I had one file in a filegroup FG1 which was orginally of size 130 GB and almost full. It was having partitioned tables in them. I have moved all the big tables to other filegroup FG2 and now FG1 is ...
-2
votes
2answers
65 views
How to prevent database shrinking
We have a 3° party application, with an application user who needs to be db_owner on the database (not my choice) on SQL Server system. All stored procedures are encrypted in this database. At some ...
0
votes
2answers
93 views
Reclaiming space from half filled huge FGs having Partitioned and LOB Data in it
We have a database with multiple filegroups. Out of which, there are 4 filegroups(Non-Primary) which has most of the data. These filegroups have partitioned tables into it. It also contains LOB Data ...
0
votes
2answers
76 views
Shoudn't DBCC SHIRNKFILE(Data_File, 0) command shrink data file to the last allocated extent?
I have a datafile with the following parameters:
When I execute DBCC SHRINKFILE (DBAtools_data,1), it shows the following resuls:
I am OK with that. My complaint is about DBCC SHRINKFILE (...
0
votes
1answer
31 views
SQL Backup routine regarding DB Logs
I recently had an incident that got me rethinking my backup plan: One of our MSSQL driven aplications stalled because the log fle had grown too big on the filesystem (it was 50 GBs IIRC).
It turned ...
-2
votes
1answer
62 views
Shrink of a big databases ? SQL Server [duplicate]
In this moment I have a very big database (almost 4 TB) and wanna shrink data files for a future migration
Unused space : almost 3000 GB
I have in mind shrink data files day per day with low amount of ...
0
votes
1answer
3k views
tempdb not releasing space
I have 1 tempdb mdf file, and 7 ndf files. They all grew to 63 GB, and filled up the 500 GBdrive (the .mdf and .ndf files say 99% free space.).
I ran sp_who2 'Active' and there is no active ...
1
vote
2answers
154 views
How to compress a non-shrinkable table with nvarchar(max) fields?
We have a serious problem which was on our minds for pretty much two weeks already. We have a big database (~250 GB, MS SQL Server 2014) and we needed to shrink it somehow, since our SSD is almost ...
1
vote
2answers
2k views
SQL Server : database free space vs data file free space
I don't understand well why the available space in database is 0% while in file is 98% available. Can you explain or advise? (SQL Server 2019, SSMS the newest, one datafile)
0
votes
1answer
31 views
Is it possible to shrink mdf file in replication server
Is it possible to shrink the data file of a SQL Server replication target server and continue the replication operation?
Ideally I'm trying to avoid shrinking the data file to reduce down time, then ...
0
votes
1answer
150 views
Transaction log still full after backup
A database in an availibility group on MS SQL 2014 with a decent log file which at some occasions has been shrinked because of some major jobs filling it up. Usually don't shrink logfiles but as there'...
1
vote
0answers
337 views
DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1
In following a recommendation from our new storage vendor, I'm trying to split up some large & busy databases into multiple data files, each to live on a separate VDisk that has a separate ...
1
vote
2answers
725 views
Sql Server 2017 - Cannot shrink transaction log file, blocked by LOG_BACKUP status
Last week my sql server 2017 instance (running on windows server 2016) ran out of space, filling the disk completely up with a 190GB transaction log file.
Luckily this was on a cloud instance, so I ...
1
vote
3answers
214 views
Static archive database: SQL Server can't shrink it and leaves ~400G of unallocated space
We are having an issue with our databases. Here's what happens:
All databases have around 50% of unallocated space and DBCC SHRINKDATABASE/SHRINKFILE won't reclaim the space back. These are archive ...
0
votes
1answer
23 views
Why database file logs are huge? [duplicate]
We have an industrial program. For a device that is running 24 hours a day. The database is constantly used.
What I noticed today after 4 months: The database log file size was 80 Gig !
Query ...
2
votes
1answer
807 views
Is there any fastest way to shrink LOB data in SQL Server
We have a database of total size of 3886 GB. Out of which 778 GB is free. So total 3108 GB worth of data is present.
Out of which 2458 GB is "LOB Data" and 400 GB is worth "Regular".
Since the ...
2
votes
1answer
110 views
Shrink log with TRUNCATEONLY but there's nothing to truncate?
An external company has a job set up which shrinks the log with truncate only - I checked the default trace and for example last night DBCC SHRINKLOG WITH TRUNCATEONLY happened 300 times. If I also ...
0
votes
3answers
131 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 ...
1
vote
2answers
127 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 ...
1
vote
1answer
105 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 ...
1
vote
1answer
793 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 ...
0
votes
2answers
2k 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%. ...
2
votes
2answers
230 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-...
1
vote
2answers
876 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.
...
5
votes
2answers
1k 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
votes
1answer
1k 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 ...
0
votes
0answers
52 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 ...
6
votes
1answer
3k 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
0answers
3k 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 ...
0
votes
1answer
2k 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 ...
2
votes
3answers
914 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 ...
10
votes
1answer
393 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' , ...
1
vote
2answers
181 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 ...
2
votes
1answer
506 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
22 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
226 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
436 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,
@...
