Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
227
questions
299
votes
4
answers
346k
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
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
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
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
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
32
votes
6
answers
165k
views
Shrinking the log file does not reduce size
I have a database which has a 350 MB data file (.mdf) and a 4.9 GB log file (.ldf). The recovery model is set to FULL.
When I try to shrink the log file, it's not shrinking.
I know shrinking a ...
- 893
30
votes
2
answers
172k
views
Best practice to shrink Tempdb in a production environment
What is best practice to use when shrinking Temporary db in SQL Server 2008?
Is it risky to use the following?
use tempdb
GO
DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
...
- 449
21
votes
5
answers
113k
views
SQL Server Database Not Shrinking [duplicate]
I have an SQL Database with a lot of unused space.
However when I run
Tasks | Shrink | Database
And
Tasks | Shrink | Files
from Management studio it isn't freeing up any space.
Unfortunately the ...
- 471
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,770
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
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
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
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
10
votes
3
answers
16k
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 ...
- 103
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
10
votes
10
answers
21k
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% ...
Amjid Qureshi
10
votes
1
answer
1k
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' , ...
- 10.9k
9
votes
2
answers
23k
views
Set simple recovery mode and shrink log files for all user created databases
I hope you can point me in the right direction.
I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit.
I want the script to:
To select ...
- 363
9
votes
5
answers
29k
views
DBCC SHRINKFILE works with file_id but not with logical name
I am trying to shrink a database file but am running into errors.
Using the file_id from sys.database_files works but using the logical file name yields an error.
The logical file name is the same in ...
- 371
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
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,908
8
votes
4
answers
27k
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 ...
- 652
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
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
7
votes
2
answers
9k
views
Transaction Log Files Larger Than Data Files [duplicate]
I'm an accidental DBA and need help determining a course of action regarding transaction log files on several databases. I ran Brent Ozar's sp_blitz to see what areas of concern there might be on an ...
- 71
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
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
7
votes
3
answers
6k
views
How should I best handle a rapidly growing database?
I have a database that I need to maintain.
Sadly, the setup and use of that database I can't change, much (thanks to some internal politics).
It's running on SQL Server 2008r2.
Its only been live for ...
- 173
7
votes
2
answers
8k
views
SQL Server 2008 log files have minimum sizes, what gives and how do I make them smaller?
Ok, to start with, I screwed up when I created the databases, using a create script roughly like so: (artificial linebreaks and names/paths for wrapping purposes)
CREATE DATABASE [EXAMPLE] ON ...
- 6,345
7
votes
3
answers
382
views
Everyone warns against shrinking a database, but will it work in my case? 1 GB db with tables removed now 100 MB
Everyone is warning against shrinking a database, e.g.
SQLAuthority
But will it be a valid option in my case? The database was originally more than 1 GB,
but with a table removed from it (moved to ...
- 1,365
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
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
6
votes
4
answers
1k
views
shrink after archiving, is this that bad too?
I have a database which grow to 20 GB, after archiving some data, the real size of tables is just 5GB.
Backup script does save a copy of the backup to a different location.
Moving 20GB or 5Gb does ...
- 203
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
5
votes
3
answers
286
views
Shrinking database which is not having any insert/update
We have a process of archiving wherein we take the backup of current database and restore it as xxx archive database. Now this database contains previous data and will not have any insert/update/...
- 177
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
5
answers
38k
views
Is it possible to shrink an .MDF file on a drive with low free space?
Is there a way to shrink mdf file of database in SQL Server 2008 without using additional space?
Total space in hard disk is 136 GB, the .MDF file size is 124 GB, and the log file size is 2 GB. I ...
puneet
5
votes
2
answers
2k
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 ...
- 1,305
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
5
votes
2
answers
2k
views
if database is not shrinked does it affect backup size?
Ok my question is simple.
Lets say that when i click database and hover over shrink it says it can be reduced 1 GB space.
Now when i do a backup of this database, does this space also getting added ...
- 553
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
2
answers
372
views
Shrink or no Shrink dilemma
I have databases that I am tasked to move two databases DBName1 and DBName2 to SSD and I only have limited space on the SSD to fit these databases.I do not want to shrink log or data files for the ...
- 177
5
votes
4
answers
2k
views
Shrink database is not shrinking as much as expected on a 1.5 TB database.
I'm working in an Enterprise system with a 1.5 terabyte database that is on a single filegroup, with the log in Simple Recovery Mode. Since we have many teams working on different projects in ...
- 51
5
votes
2
answers
559
views
Catch event of auto_shrink in SQL Server Log? [duplicate]
I am aware of the disadvantages of AUTO_SHRINK being configured on SQL Server databases. Recently, in a new environment, I discovered several production servers with the Auto Shrink option turned on.
...
- 4,757
5
votes
2
answers
13k
views
TempDB data files don't shrink well on SQL 2008
So ideally you want to pre-size your TempDB data and log files appropriately so that this isn't a concern, but sometimes a rogue developer runs a crazy huge query on a production server during work ...
- 9,854
5
votes
2
answers
14k
views
Shrinking the SYSTEM tablespace in Oracle
Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table.
We have truncated SYS.AUD$ but the datafile is still very big (~30G).
Resize doesn't work because the file contains used data ...
- 61
4
votes
3
answers
6k
views
Releasing free space in mdf file to OS [duplicate]
We have a 120GB database. There was table with 60GB of data which is useless and we have truncated it.
Now database size is 120GB with free space 60GB. The database will not grow up to 60GB in at ...
- 1,369
4
votes
3
answers
591
views
Wasted space in MDF file: Should I just use SHRINKFILE or some other alternative?
I've got a database which has an 850GB MDF file. Over the course of 20 months a logging mechanism in the application created a variety of huge tables. The maintenance script which was only supposed to ...
- 1,774
4
votes
1
answer
3k
views
Compressions vs shrinking a database
Is compression and shrinking a database in SQL Server same? If I compress a table, will it decrease query performance and DML?
- 71