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
2
votes
2answers
3k views

Shrink a SQL Anywhere 12 database

Is there any way to shrink SQL Anywhere databases? With SQL Server, it was an easy task. Just do it, have a faster database again that has a size which can be transferred over a slow internet ...
2
votes
4answers
177 views

When should I use the shrink option

I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
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 ...
1
vote
3answers
3k views

Reclaiming space from a large partitioned database

We have a database where one of the business group decided to remove several millions of rows as part of their upgrade process. (we removed for them..).. This database is partitioned based on a date ...
4
votes
2answers
11k 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 ...
1
vote
1answer
5k views

Is Reorganizing Indexes required after ShrinkDatabase or can I just reogranize index with fragmentation more than 10%?

I am a new DBA to a SQL 2005 production Report server. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on ...
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?
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 ...
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 ...
4
votes
2answers
2k views

Shrinking SQL Server 2000 database

I apologize for the long question. I am trying to learn a lot on the fly as I have become the defacto SQL Admin. I have been tasked with moving a production database to a new server. My goal is to ...
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 ...
5
votes
5answers
30k 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 ...
1
vote
1answer
2k views

Use of Shrink after rebuilding Indexes in sql server 2005

I need to create a weekly database plan for my company's databases (5.8Gb), now I want to rebuild the Indexes, and then want to trigger a Shrink operation. Now I know Shrink creates Index ...
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
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 ...
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 ...
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 ...
0
votes
2answers
4k views

SHRINK FILE works with SA but not as a normal user

My problem is that when I try to shrink the Log it works when I am logged in as SA but when I log in as a "normal" USER it does not shrink the log. (SQL Express 2008r2) I have tried to find a answer ...
3
votes
2answers
626 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 ...
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 ...
2
votes
2answers
2k views

SQL Server 2008 - Log file out of control & cant shrink it [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? I am having trouble with my database, the log files is currently 250GB in size, I need to shrink this down ...
3
votes
1answer
1k views

Optimising a Database Shrink (in terms of speed and reducing fragmentation)

We have a large database that has had a lot of older data stripped out, leaving primary datafile (all objects are currently in the primary filegroup) of about 80Gb containing a little less than 20Gb ...
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 ...
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 ...
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, ...
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?
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% ...
4
votes
3answers
508 views

Shrink database after removing extra data [duplicate]

We have a need to fit a database in 4GB in order to use SQL Server Express Edition. I started from a 7GB database, and found a lot of unneeded records, and deleted them. After shrinking, the database ...