Skip to main content

Questions tagged [shrink]

Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.

Filter by
Sorted by
Tagged with
0 votes
0 answers
23 views

Reduce primary DATAFILE size for an SQL Server Database 2016

In an AG always on configuration (primary) I have a database with 10TB datafile, 50% of the datafile is a free space. Then, I want to reduce this DATAFILE from 10TB to 8TB. How to proceed for that ...
user avatar
0 votes
1 answer
89 views

SQL Server, Maintenance Plan, Shrinking DATA File

After 5 years of work on our Sql Server database, I plan to carry out a maintenance on, and we plan to eliminate more than 170 GB of useless data by truncating some tables, and after that a Shrink ...
Mohammed Amine BENAMARA's user avatar
0 votes
2 answers
181 views

Does rebuilding/reorganize an index in SQL Server free up space in the DB file due to compacting data pages?

If I have an index with a default fill factor of 100%, and modifications result in a large amount of internal fragmentation (page splits, free space, etc.) so that many pages end up around 60% full ...
Marcus's user avatar
  • 69
-1 votes
1 answer
92 views

Alternatives to reclaim free space after Shrink activity

For couple of our SQL server databases on version SQL2017 APP team did purge to change retention on these partitioned tables which has released quite good amount of space. Some background- Before ...
Newbie-DBA's user avatar
0 votes
0 answers
64 views

SQLServer Physical Log shrinking automatically with AutoShrink off

Running in SQLServer 2016 I have a weird situation where the Physical log is shrinking without any interaction and with AutoShrink=false set. Also, on occasion the size is shrinking below the initial ...
DbaJag's user avatar
  • 1
0 votes
1 answer
36 views

What is the correct value (GB) to shrink a DB?

My database has an MDF file of 56 GB and an LDF file of 263 GB, recovery model is setting to Full. So, I would like to shrink the LDF file because we are out of space. What specific size should I ...
luis.huallpa's user avatar
0 votes
1 answer
455 views

DBCC Srhinkfile is stuck running for 2+ days

Forgive me, but I am very new to being a DBA as I ended up in this role out of necessity :) I am currently working on reducing the size of several databases. I previously asked a question here about ...
P5_'s user avatar
  • 27
0 votes
1 answer
341 views

DBCC ShrinkDatabase is increasing my file size

Yesterday, I asked a question about how to reduce the size of a database. The community suggested using the command dbcc shrinkdatabase. I followed their advice and ran the command for over 24 hours. ...
P5_'s user avatar
  • 27
-1 votes
1 answer
70 views

Azure SQL Database :: SHRINKDATABASE increase the unallocated space

I'm trying to squeeze as much Azure SQL Databases as possible in order to make them fir inside a SQL Elastic Pool. I'm using this query that allows me to compare the unallocated space before and after ...
Francesco Mantovani's user avatar
-1 votes
1 answer
366 views

SQL Elastic Pools: "The semaphore timeout period has expired" while REBUILD index

I'm shrinking databases that are already on a SQL Elastic Pool. The goal is to fit as much as possible databases in a unique Elastic Pool. In order to do so I've stubble upon a blog that suggest to ...
Francesco Mantovani's user avatar
4 votes
1 answer
1k views

How to reclaim space on Sql Server Azure

I have an Azure Sql Server database (in the cloud, not on a VM)(Not a managed instance) which currently has 270 gigs of space allocated to it. However, the actual space used is 27% (81 gigs). I would ...
Greg Gum's user avatar
  • 143
-1 votes
1 answer
339 views

how to shrink database with a big file in SQL server

I have deleted some of the tables on the SQL server2019 database this table is around 1.5TB. And already shrink the database but there is no effect.
bro.visal's user avatar
0 votes
0 answers
792 views

PostgreSQL Database Data and Log size

I am looking for a way to list PostgreSQL Database Data and Log sizes. Using the following command we can check the total database size based on the documentation https://pgpedia.info/p/...
Stavros Koureas's user avatar
0 votes
1 answer
333 views

How to remove unallocated space after partitioning

I've recently done partitioning on a table (postcode column) and created new filegroups for each postcode. Now that it is no longer using the PRIMARY filegroup, the file size is still as it is. How do ...
aYasser97's user avatar
0 votes
2 answers
348 views

Database not shrinking and cant insert data even thought file has free space

I have SQL Server Express 2019 on my local system on a large SSD with >50Gb free disk space. I had a 14Gb database on production, which I copied, deleted a bunch of old data, and reduced the size ...
Vaibhav Garg's user avatar
1 vote
1 answer
445 views

Reclaiming allocated free space from partitioned tables

We have a data warehouse with about 4TB worth of datafiles. A couple of these databases consist of partitioned tables. Since we're running out of disk space (on premise environment), we are looking to ...
Foxyproxy's user avatar
-3 votes
1 answer
228 views

After mass deletion of data, what should be done first: Index rebuilding or shrinking filegroups?

When we delete huge amounts data from a table, what should be the best thing to follow: Shrink the file group first and rebuild indexes, or Rebuild indexes then shrink the file group?
Krishna508's user avatar
-2 votes
2 answers
1k views

Why not reduce the database log file size with shrink?

I use SQL server database. To reduce the database log file I set the recovery model of the database to simple and then Shrink database but the log file does not get the effect. What is problem?
hmahdavi's user avatar
  • 107
-5 votes
1 answer
1k views

Does the shrink data and log file command need to be done during maintenance hours or less busy hours?

Our data and log files have increased drastically due to a recent large INSERT query and DELETE query. The database in simple recovery mode. Does the shrink data and log file command take the database ...
variable's user avatar
  • 3,468
0 votes
2 answers
57 views

Recovering space on a restored and pruned SQL Server database for testing purposes

I have a requirement to regularly take a backup of a set of tenant databases (all same schema), and restore them in our UAT environment, then clear down most of the client data, leaving only a few ...
Rebecca's user avatar
  • 299
1 vote
1 answer
464 views

Unable to shrink data file with LOB data sql server 2017

I have a huge table that stores pdf documents using datatype "image".  Recently we started to offload files to blob storage, that is why we want to shrink main data file. All my tries to ...
Serdia's user avatar
  • 697
2 votes
2 answers
4k views

Why dbcc's shrinkdatabase and shrinkfile aren't working?

Ok, I got it. Shrinking your database is wrong. You hate it. But let me explain. I have a 1TB P1 Azure SQL Database in production with ~50 tables, where ~5 of them are JSON containers. This was the ...
Davide De Pretto's user avatar
-1 votes
1 answer
728 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 ...
SakZepelin's user avatar
0 votes
1 answer
788 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: ...
DBALUKE HUANG's user avatar
0 votes
0 answers
103 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 ...
Greg Bala's user avatar
  • 1,015
0 votes
1 answer
4k 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, ...
Gábor Major's user avatar
0 votes
3 answers
7k 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 ...
dba_maroc's user avatar
0 votes
1 answer
2k 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 ...
jrdba123's user avatar
0 votes
2 answers
111 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 ...
Vladimir Baranov's user avatar
1 vote
1 answer
3k 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 ...
asmgx's user avatar
  • 219
0 votes
2 answers
1k 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 ...
askolotl's user avatar
  • 255
0 votes
1 answer
353 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 ...
askolotl's user avatar
  • 255
1 vote
4 answers
4k 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 ...
Florian's user avatar
  • 31
1 vote
1 answer
2k 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 ...
Aruna's user avatar
  • 111
0 votes
2 answers
128 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 &...
Plain_Dude_Sleeping_Alone's user avatar
1 vote
1 answer
288 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 ...
sachin-SQLServernewbiee's user avatar
-2 votes
2 answers
94 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 ...
Ludo's user avatar
  • 11
0 votes
2 answers
218 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 ...
sachin-SQLServernewbiee's user avatar
0 votes
2 answers
270 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 (...
Rauf Asadov's user avatar
  • 1,283
0 votes
1 answer
37 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 ...
MadOp's user avatar
  • 15
-2 votes
1 answer
110 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 ...
SakZepelin's user avatar
1 vote
1 answer
13k 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 ...
veena ranganath's user avatar
1 vote
2 answers
475 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 ...
Silver Horse's user avatar
1 vote
2 answers
6k 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)
Martin Matuska's user avatar
0 votes
1 answer
80 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 ...
Peter's user avatar
  • 21
0 votes
1 answer
904 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'...
StiffBoard's user avatar
2 votes
1 answer
1k 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 ...
NateJ's user avatar
  • 804
2 votes
2 answers
3k 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 ...
Scriptonaut's user avatar
1 vote
3 answers
386 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 ...
rofz's user avatar
  • 91
0 votes
1 answer
38 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 ...
hmahdavi's user avatar
  • 107

1
2 3 4 5