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
1
vote
1answer
44 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
1answer
7k views

DBCC SHRINKFILE EMPTYFILE not emptying

Trying to remove an extra database file from a database using EMPTYFILE on SQL Server 2008 R2. use [MYDATABASE] DBCC SHRINKFILE (MY_DATABASE_7, EMPTYFILE); use master ALTER ...
0
votes
2answers
76 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)
1
vote
1answer
731 views

Fix transaction log file after database update

I have a databases that are regularly (monthly) updated. An update includes a lot of new or changed T-SQP procedures, functions, indexes creation, etc. A database X has transaction log file with the ...
0
votes
1answer
39 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'...
0
votes
1answer
469 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
vote
0answers
56 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
3answers
57 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
2answers
90 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 ...
0
votes
1answer
21 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 ...
7
votes
4answers
33k 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\...
34
votes
3answers
54k views

What is the difference between Shrink Database and File?

DBCC ShrinkDatabase() DBCC ShrinkFile() Do I need to run both DBCC commands in order to shrink the database? What is the difference between these two above?
2
votes
1answer
188 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
70 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
116 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
51 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 ...
274
votes
4answers
295k views

Why Does the Transaction Log Keep Growing or Run Out of Space?

This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this: In SQL Server - What are some reasons the transaction log ...
2
votes
3answers
10k views

Large transactional log won't shrink after transactional backup [duplicate]

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
2answers
447 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%. ...
1
vote
1answer
70 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
94 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 ...
2
votes
2answers
90 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
283 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. ...
0
votes
0answers
26 views

Major DB operations like table drop, Shrink etc on AWS SQL Server 2014 SE with Multi-AZ switched on

Are there any issues if Major DB operations like table truncation, big amount of data delete or Shrink are performed on AWS SQL Server 2014 SE with Multi-AZ switched on? Should the Multi-AZ switched ...
-1
votes
1answer
395 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 ...
5
votes
2answers
490 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 ...
0
votes
0answers
51 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 ...
5
votes
3answers
1k views

Reducing the size of a database

SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff ...
45
votes
5answers
64k views

When is it OK to shrink a Database?

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
10
votes
10answers
16k views

Shrink a database below its initial size

I've got a SQL server 2005 dev database that is a 30GB copy of live. We have deleted some data that is not needed in dev, which brings the data file space used down to 20GB. So we have about 33% ...
5
votes
1answer
763 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 ...
3
votes
3answers
746 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 ...
1
vote
1answer
4k views

Why does FREEPROCCACHE let you shrink tempdb?

Somebody did something stupid and tempdb exploded in size. Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked). ...
49
votes
8answers
116k views

How do I shrink all files quickly for all databases?

In SQL Server (2008 in this case) how can I quickly shrink all the files, both log and data, for all databases on an instance? I could go through SSMS and right click each and choose Tasks -> Shrink, ...
1
vote
0answers
263 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
2answers
150 views

What's the most efficient way to reclaim unused DB hard drive space?

Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having. We have a database that unintentionally ballooned to 300+ GB. We have turned off the option ...
10
votes
1answer
232 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' , ...
0
votes
1answer
686 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
446 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 ...
2
votes
1answer
405 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
vote
1answer
2k 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
178 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 ...
1
vote
1answer
137 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
1answer
943 views

Shrink a mirrored and replicated DB

I have dropped one huge table from the production DB Current DB size on disk = 264 GB The real size of the Tables = 32.164 GB I am going to shrink this DB. Although it is not recommended, but ...
3
votes
2answers
650 views

Shrinking log file

Some of my SQL Server's log files (ldf) are huge, like 5GB. I Found an article about shrinking, but am not sure why the author repeated DBCCSHRINKFILE 6 times. Can someone please confirm and suggest ...
10
votes
3answers
15k views

Database size - MDF too large?

I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
1
vote
0answers
21 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
166 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 ...
7
votes
4answers
21k views

Shrink database after drop table?

I had a table with 70+ million records in a SQL Server database, I dropped that table (a one-time thing) to free up space on the disk, but it looks like the size didn't change much. I do see that I ...
0
votes
1answer
210 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, @...