Questions tagged [shrink]

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

18 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
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
0 answers
491 views

Why does shrinking the transaction log generate lots of log (LOP_SHRINK_NOOP) and not shrink the log?

I just tried shrinking an overly big log file (FULL recovery model). This operation is not expected to always succeed and indeed the log was not shrunk. But I observed that a lot of new log was ...
usr's user avatar
  • 7,330
1 vote
0 answers
25 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
Rutuja Bidwai's user avatar
1 vote
1 answer
1k views

Stop never ending backup in SQL Server 2012 Express

Our production database log file has grown to consume all disk space on our server. I know it's bad practice but I want to be able to shrink it in order to restore the service as soon as possible. ...
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
0 answers
188 views

How does shrinking of tempdb log file work?

On the following documentation page: https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-tempdb-database?view=sql-server-ver16 It states that: Assume that tempdb has two files:...
Fajela Tajkiya's user avatar
0 votes
0 answers
678 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
289 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
1 answer
630 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
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,005
0 votes
3 answers
6k 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
1k 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
210 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 ...
kalpna's user avatar
  • 1
0 votes
1 answer
87 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 ...
Sriram R's user avatar
0 votes
1 answer
95 views

Unable to shrink files

I am kind of new to SQL Server. I am trying to shrink my MDF files, however I am unable to do it. There is no error message. Any idea why this is the case ?
Ijaz's user avatar
  • 1
0 votes
1 answer
84 views

Shrink configuration & content database in Sharepoint DBs

There was some argument between me and a college about for how we should shrink DBs. We knew that shrinking should be manual and only if required. We run in a case that configuration DBs reached to ...
SomeOneNew2014's user avatar
0 votes
0 answers
467 views

Relationship between file shrink and used memory in SQL Server

Some background info: this is a test SQL Server 2012, with restored databases from production servers, all of them with over 20% space available in the data file, up to even 50%. I'd rather restore as ...
Razvan Zoitanu's user avatar
-1 votes
1 answer
73 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