Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
32
questions
265
votes
4answers
283k 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 ...
35
votes
4answers
11k 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 ...
44
votes
5answers
63k 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 ...
7
votes
2answers
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 ...
6
votes
4answers
12k 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'...
16
votes
4answers
7k 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 ...
2
votes
3answers
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 ...
17
votes
2answers
54k 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 ...
6
votes
2answers
17k 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, ...
5
votes
3answers
11k 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 ...
1
vote
1answer
3k 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
votes
1answer
5k 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 ...
5
votes
1answer
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 ...
5
votes
3answers
599 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 ...
3
votes
2answers
630 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 ...
47
votes
8answers
108k 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, ...
4
votes
4answers
767 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 ...
8
votes
2answers
16k 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 ...
7
votes
2answers
5k 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 ...
3
votes
2answers
1k 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 ...
2
votes
1answer
257 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 ...
10
votes
3answers
8k 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 ...
9
votes
3answers
12k 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 ...
6
votes
2answers
13k 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.....
4
votes
2answers
1k 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 ...
-1
votes
2answers
1k 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!
5
votes
2answers
397 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 ...
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 ...
2
votes
0answers
382 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 ...
2
votes
2answers
596 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 ...
1
vote
1answer
141 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 ...
0
votes
1answer
514 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 ...
