Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
43
questions
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 ...
- 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 ...
- 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 ...
- 957
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 ...
- 3,131
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 ...
- 173
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'...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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, ...
- 761
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 ...
- 239
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 ...
- 165
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 ...
- 3,131
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 ...
- 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).
...
- 6,178
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 ...
- 53
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-...
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, ...
- 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?
Jango
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 ...
- 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 ...
- 1,417
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 ...
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.....
- 217
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 ...
- 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 ...
- 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 ...
- 761
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!
- 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\...
- 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 ...
- 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 ...
- 974
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 ...
- 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 ...
- 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 ...
- 3,131
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 ...
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 ...
- 125
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 ...
- 3,131
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 1,504