Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [shrink]

Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.

Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.

It should be avoided at all costs http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

221 questions
297
votes
4 answers

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 grows so large? Why is my log file so big? What…
Mike Walsh
  • 18,013
  • 6
  • 46
  • 72
52
votes
8 answers

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, but I'm looking for something faster. I scripted…
jcolebrand
  • 6,254
  • 4
  • 41
  • 67
47
votes
5 answers

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 delete 50 GB of data -- not on one table, but a…
bumble_bee_tuna
  • 947
  • 2
  • 11
  • 19
39
votes
3 answers

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
36
votes
4 answers

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 shrink my database? What files do I shrink? What…
Mike Walsh
  • 18,013
  • 6
  • 46
  • 72
32
votes
6 answers

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 database is not good and it should not be done. But…
Navaneet
  • 883
  • 5
  • 12
  • 23
30
votes
2 answers

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 DBCC FREESYSTEMCACHE ('ALL') -- clean system…
Mcol
  • 449
  • 1
  • 4
  • 4
19
votes
5 answers

SQL Server Database Not Shrinking

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 machine that it is running on is running out of hard…
DermFrench
  • 441
  • 3
  • 6
  • 15
18
votes
3 answers

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 transaction log backup on either the primary…
gotqn
  • 3,709
  • 10
  • 42
  • 77
17
votes
4 answers

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 space because of the huge expanding size of this…
user1021182
  • 173
  • 1
  • 1
  • 5
11
votes
2 answers

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 get the following error: Page 1:4573184 could not…
user45117
  • 113
  • 1
  • 1
  • 4
11
votes
3 answers

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 automagically at some time point? Or do I have to…
jrara
  • 5,253
  • 19
  • 53
  • 65
11
votes
6 answers

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\ORADATA\xx\TB1.DBF 33554416 True 100…
r0tt
  • 918
  • 6
  • 25
  • 48
10
votes
3 answers

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 model is SIMPLE and the .ldf is at 6Gb. For…
Andrija_Bgd
  • 103
  • 1
  • 1
  • 4
10
votes
1 answer

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' , EMPTYFILE)' File ID x of database ID x cannot be…
LowlyDBA - John M
  • 10,941
  • 11
  • 40
  • 60
1
2 3
…
14 15