Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
226
questions
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
1
vote
3
answers
4k
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 ...
- 447
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
2
votes
1
answer
8k
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 ...
- 21
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
7
votes
3
answers
383
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
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
3k
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 ...
user1892330
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
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
1
vote
1
answer
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
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
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
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
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,944
1
vote
2
answers
7k
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 ...
- 31
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
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
2
votes
2
answers
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 ...
- 121
4
votes
1
answer
2k
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 ...
- 32k
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
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
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
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
4
votes
3
answers
548
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 ...
- 166