We’re rewarding the question askers & reputations are being recalculated! Read more.

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
265
votes
4answers
281k 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 ...
47
votes
8answers
107k 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, ...
43
votes
5answers
62k 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 ...
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 ...
33
votes
3answers
49k 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?
24
votes
1answer
139k 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 ...
23
votes
6answers
109k 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 ...
17
votes
2answers
53k 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 ...
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 ...
16
votes
5answers
77k 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 ...
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 ...
10
votes
3answers
14k 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 ...
10
votes
10answers
15k 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% ...
10
votes
1answer
126 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' , ...
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 ...
8
votes
2answers
18k 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 ...
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
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 ...
7
votes
3answers
4k 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 ...
7
votes
2answers
26k 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 ...
7
votes
2answers
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 ...
7
votes
4answers
19k 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 ...
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 ...
7
votes
3answers
367 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 ...
7
votes
5answers
21k 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 ...
7
votes
4answers
21k 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\...
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 ...
6
votes
4answers
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 ...
6
votes
4answers
11k 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'...
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.....
6
votes
2answers
16k 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
2answers
6k 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 ...
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 ...
5
votes
3answers
262 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/...
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 ...
5
votes
1answer
313 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 ...
5
votes
5answers
29k 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 ...
5
votes
2answers
178 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 ...
5
votes
2answers
378 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
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
2answers
1k 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 ...
5
votes
3answers
585 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 ...
5
votes
2answers
339 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 ...
5
votes
4answers
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 ...
5
votes
2answers
430 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. ...
5
votes
2answers
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 ...
4
votes
3answers
3k 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 ...
4
votes
3answers
489 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 ...
4
votes
1answer
2k 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?
4
votes
1answer
542 views

Is it wise to drop fixed-length columns to free up space?

I have a huge table in SQL Server 2005. (The table is about 40gb) I am looking for the safest and most pain-free way to either free up space, or delay its growth. The table has three fixed length (...