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
299 votes
4 answers
347k 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 ...
Mike Walsh's user avatar
  • 18.1k
37 votes
4 answers
19k views

I Need to Shrink My Database - I just freed a lot of space

This question is asked in various forms here but the question boils down to: I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again. How can I ...
Mike Walsh's user avatar
  • 18.1k
48 votes
5 answers
71k 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 ...
bumble_bee_tuna's user avatar
7 votes
2 answers
3k views

Database defragmentation and autogrowth settings

We do have some maintenance plan for our SQL Server 2008 r2 express. Every month we do de-fragmentation of the database if any table has page count more than 50 for any table and average fragmentation ...
IT researcher's user avatar
17 votes
4 answers
11k views

Claiming disk space after removing table field

I am running sql 2008 r2 and the db was working fine and fast for last 3 years untill about 3 months ago we added ntext field on very active and used table. Now we are starting to get out of server ...
user1021182's user avatar
8 votes
4 answers
29k 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'...
SE1986's user avatar
  • 1,928
4 votes
4 answers
1k views

Need assistance shrinking MDF File

Verbose logging was accidentally left on for one of our database ballooning our database MDF file from roughly 90GB to 192GB. We've disabled verbose logging, truncated the logging table but now we ...
Brad's user avatar
  • 191
2 votes
3 answers
2k views

Should I shrink my database?

I have a 1.2Tb SQL Server 2008 R2 database which is more than 50% empty, because I migrated a lot of functionality to PostgreSql. The database is still in use, and still somewhat growing, but I do not ...
A-K's user avatar
  • 7,234
18 votes
3 answers
87k views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
gotqn's user avatar
  • 3,780
8 votes
3 answers
18k views

Get MS SQL Server to release disk space

A poorly managed database table has grown to be enormous. 48+gigs of orphan records. I am trying to clean it up and put my dangerously full hard drive back to a normal state. I will be deleting approx ...
jackncoke's user avatar
  • 183
7 votes
2 answers
32k views

How to minimize log operations in SQL Server to avoid "log full" error

I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting Msg 9002, Level 17, ...
Zerotoinfinity's user avatar
7 votes
1 answer
7k views

SHRINKFILE best practices and experience [duplicate]

Preamble: In general it's a big no-no, but believe me that are rare cases when space is really needed. For example Express Edition is limited to 10GB. Imagine that you discovered that with a data type ...
Csaba Toth's user avatar
5 votes
3 answers
1k 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 ...
Alias_Knagg's user avatar
5 votes
3 answers
13k views

Shrink the database only up to its initial size which is set after creating database

In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file. So now if create a new ...
IT researcher's user avatar
5 votes
1 answer
2k views

Mitigate negative effects of shrinking

Closely related to this question from a few weeks ago, but the answers didn't discuss how to do the dirty deed. Here's the situation: We now 15 GB data in a database, but a logging bug in an ...
Jamie F's user avatar
  • 313
3 votes
1 answer
6k 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). ...
James Jenkins's user avatar
3 votes
2 answers
686 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 ...
Monica Todd's user avatar
0 votes
2 answers
32k views

Reduce MDF file size [duplicate]

I am working with a 500 GB SQL Server database. I need to reduce the size of the database data file after removing a large amount (50%) of the data. I tried re indexing, it's good the database has de-...
kannadhasan G's user avatar
53 votes
8 answers
155k 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, ...
jcolebrand's user avatar
  • 6,345
41 votes
3 answers
74k 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?
user avatar
12 votes
2 answers
28k views

TempDB will not shrink. No Open Transactions

I have a TempDB on SQL 2008 that has gotten very large (>40gb) and I want to shrink it down. I have used the dbcc shrinkdatabase, dbcc shrinkfile and the shrink command through Management Studio. I ...
user45117's user avatar
  • 123
10 votes
2 answers
18k 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 ...
Saeed Neamati's user avatar
8 votes
1 answer
7k 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 ...
nobodyofnaught's user avatar
7 votes
2 answers
17k views

Switching to Simple Recovery - shrinking transaction logs

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned.....
freefaller's user avatar
3 votes
2 answers
2k views

Shrinking sql databases - bad idea? [duplicate]

I have a database, that due to some silly code issues has grown hugely. It is now sitting at 24GB, most of it is very unnecessary logging information (system generated debug information). My server ...
Crudler's user avatar
  • 217
2 votes
3 answers
8k views

How to do a one-time log shrink on a database with transactional replication

I have inherited a 10 GB database with an 80 GB log [only 3% in use according to DBCC SQLPERF(logspace)]. It seems safe to assume the extreme log growth was due to problems from long before I was ...
Mark Freeman's user avatar
  • 2,151
2 votes
1 answer
360 views

Dealing with non active transaction in log file and shrinking

I have posted the first part of my question here & I don't want to flood my question with a loads of information and would force people to alter their answers for my each question. I think I am ...
Zerotoinfinity's user avatar
0 votes
2 answers
2k 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!
Shroog's user avatar
  • 13
13 votes
6 answers
105k 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\...
r0tt's user avatar
  • 998
11 votes
3 answers
9k views

Will the transaction log shrink automagically in SQL Server?

When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...
jrara's user avatar
  • 5,333
7 votes
2 answers
37k views

SQL Server history of growth and shrink events

Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014 ? (New instances to support, noticed from the ticketing system that they have ...
Razvan Zoitanu's user avatar
6 votes
2 answers
1k 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 ...
Drew's user avatar
  • 115
5 votes
3 answers
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 ...
Brett's user avatar
  • 95
4 votes
2 answers
2k views

Autogrowth, shrink and database performance

I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it. I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 ...
IT researcher's user avatar
3 votes
1 answer
2k views

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 ...
sachin-SQLServernewbiee's user avatar
2 votes
2 answers
2k 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
2 votes
2 answers
680 views

Large number of VLF and shrinking the log

If there are lot number of VLF then it will effect the performance of the queries. I saw that solution is to shrink the log file. But shrinking the file is also bad. So how to take control of whole ...
IT researcher's user avatar
2 votes
0 answers
482 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,310
1 vote
1 answer
826 views

Delete query does not appear to open space on the SQL Server

Our SQL Server (version 2012) is housed on a cloud server, for which we pay whenever we need additional space. We determined that we could delete some of the data to prevent the need for continually ...
CGrand's user avatar
  • 15
1 vote
2 answers
963 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 ...
J.D.'s user avatar
  • 35.2k
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
  • 245
0 votes
1 answer
2k 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 ...
Cenderze's user avatar
  • 197
-1 votes
1 answer
226 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