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
3
votes
1answer
1k views

DBCC SHRINKFILE for Log and Data when using Availability Groups

We had an issue where one of our database tables grew by 40GB by mistake. The database is part of an Availability Group with one primary and two replicas. While trying to delete the extra records we ...
5
votes
2answers
379 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 ...
2
votes
3answers
1k views

SQL Server : Log File Shrink

We are running SCCM 2012 r2 Reporting services and the SQL Server log file has got out of hand due to the backup not being set up properly and I'd like to confirm something before I continue. I have ...
1
vote
3answers
7k views

Large transactional log won't shrink after transactional backup [duplicate]

I'll start by saying our databases use full recovery mode and we run SQL Server 2014. Over the last few days our backups appeared to spiral out of control to the point that our full/differential ...
0
votes
1answer
75 views

Shrinking bigger databases [duplicate]

Hello masters of MS SQL I have a kind of a problem on my hands. We have quite big DB (5TB) and we've just "enabled" some software archiving which means that most of the data will migrate to other DB'...
-1
votes
2answers
242 views

How to schedule and perform shrinking of database-files

am using the below code to peform shrink the db to release unused space as my db's log files are growing exponentially. But i wanted to use this as a scheduled event,running every two days at night ...
3
votes
1answer
75 views

Is it a good idea to shrink prior to a SQL Server upgrade to a new instance

We currently have a 100GB database which is in simple recovery mode, and it is running on 2008 R2. We have a new server with SQL 2016 Enterprise installed, ready to go - and we're building a migration ...
2
votes
1answer
137 views

Database free space not being reused

At the company I work for, we have encountered a problem where it seems that SQL Server 2008 R2 is not correctly calculating the free space in databases. As such databases on that server are growing ...
3
votes
1answer
2k views

Shrinking during backup

All, I'm familiar with the good reasons not to shrink a database and I am in the process of shrinking a 2TB one. We have a large database that gets backed up each day (by policy) and because of the ...
0
votes
1answer
488 views

Risks associated with automatically shrinking TempDB as a SQL agent job?

I have a situation where I have a constraint on TempDB to not exceed 350 GB in size. I'm using autogrowth 10 (percent) on TempDB, as I've read that it is supposed to be best practice on databases ...
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 ...
2
votes
1answer
308 views

Database transaction log growth who is the cuplrit? [duplicate]

There are abundant information on how to shrink a growing Transaction log, from frequent log backups, to changing the RECOVERY from FULL to SIMPLE and truncate. A daily background job takes care of ...
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 ...
0
votes
1answer
85 views

Fix ShrinkFile / ShrinkDb database

my DBA obviously thinks that it is best practice to use shrinkdb / shrinkfile all the time some bigger table is truncated / deleted just a little bit. I tried to talk sense to him and explain him how ...
0
votes
1answer
112 views

Should not shrink database to release space in production?

My understanding is that we should never shrink the database in production to free up space? Rather, we should shrink the Transaction log. Could someone explain why? Thanks!
0
votes
0answers
15 views

Vcenter database log file keeps growing - shrinking the log doesn't help [duplicate]

We have a vcenter database on an AG setup. The log file keeps growing sometime due to metrics which are enabled on the application end. They will be moving this soon to another db vendor to probably ...
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 ...
0
votes
1answer
1k views

Shrinking TEMPDB

Is it necessary to restart SQL Server after DBCC SHRINKFILE on tempdb.mdf and tempdb.ldf or is there any other method to reduce the size of tempdb.mdf and tempdb.ldf without restarting SQL Server? Is ...
0
votes
1answer
64 views

Unable to shrink files

I am kind of new to SQL Server. I am trying to shrink my MDF files, however I am unable to do it. There is no error message. Any idea why this is the case ?
0
votes
0answers
376 views

Why did DBCC SHRINKFILE never complete but shrink successfully after cancellation?

I have to deal with databases that have experienced a lot of data deletion and I am preparing to move the DBs to new Sql-Server installations and additionally to a new SAN. The size of the data files ...
4
votes
2answers
1k views

Best Way to Reduce the Size of 8 TB DB?

We have a legacy SQL Server 2005 DB that is 8 TB in size. We will be purging data older than 5 years from this DB. After the purge process is complete, I want to be able to shrink the DB or reduce the ...
0
votes
2answers
2k views

Are there any disadvantages to shrinking a database? [duplicate]

My production server's database grows unexpectedly and almost I am going to create a job for shrinking the database which runs once in a month. Is it a good idea? The backups was failed and the time ...
1
vote
1answer
354 views

Shrinking and consolidating to only one log file

I've restored a database from production to QA, on SQL Server 2012. One of the databases has two log files: logfile.ldf - 6 GB logfile2.ldf - 200 GB Due to space issues and data not being truncated ...
3
votes
2answers
323 views

Performance degraded after shrinking

I was cleaning up database (SQL Server 2012 SE) by deleting old data and truncating some unnecessary tables. After deleting and truncating data performance improved and had 67% free spaces out of 16 ...
1
vote
1answer
3k views

Why does FREEPROCCACHE let you shrink tempdb?

Somebody did something stupid and tempdb exploded in size. Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked). ...
1
vote
2answers
1k views

Is it better to shrink a database or defragment to improve performance?

I'm no DBA hence me visiting here. I do however manage an application which writes to a SQL database. We are experiencing poor performance with the application now. Pages taking forever to load up and ...
0
votes
2answers
144 views

What's the most efficient way to reclaim unused DB hard drive space?

Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having. We have a database that unintentionally ballooned to 300+ GB. We have turned off the option ...
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 (...
3
votes
1answer
74 views

Why can't I shrink my logfile below to the requested value?

Experimenting VLFs behaviors with my test lab. Firsty, I have created two databases (BASE2 & BASE3) with different log files size (1024 & 2048 MO) : Secondly, I'am running the following ...
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 ...
2
votes
2answers
2k views

Can we shrink datafile of a database which is configured with log shipping

I am using MS SQL Server 2012 version RTM, below is our environment, I have a primary database Server A and Secondary Database Server B Log shipping configured on A to B of a database, 1) Database ...
0
votes
2answers
99 views

Safe to Shrink Database in this Scenario? [duplicate]

For years I've had it drilled in to me: Never ever shrink a database file. I understand why of course, and I never do, mainly because I never need to. Now though, I have a database with an .mdf ...
0
votes
1answer
629 views

Shrink data MDF file on server running on low disk space

Before proceeding, I know the cons of doing this and have read many questions on same. But since we are here on urgent need and adding disk would take over a couple of days and the DB in question ...
3
votes
2answers
1k views

Shrinking sql databases - bad idea? [duplicate]

I have a database, that due to some silly code issues has grown hugely. It is now sitting at 24GB, most of it is very unnecessary logging information (system generated debug information). My server ...
4
votes
4answers
746 views

Need assistance shrinking MDF File

Verbose logging was accidentally left on for one of our database ballooning our database MDF file from roughly 90GB to 192GB. We've disabled verbose logging, truncated the logging table but now we ...
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/...
0
votes
2answers
24k views

Reduce MDF file size [duplicate]

I am working with a 500 GB SQL Server database. I need to reduce the size of the database data file after removing a large amount (50%) of the data. I tried re indexing, it's good the database has de-...
1
vote
1answer
369 views

Optimising file shrink and index reorganization operations

I'm archiving my SQL tables. So I am shrinking my files (because I need the free space) and re-organising my indexes(to defragment them). I know shrinking a file causes the indexes to be fragmented. ...
1
vote
1answer
1k views

Shrinking Database Task [duplicate]

I am new to the DBA Part. I had an 110 GB Database from which I deleted records from a 95GB sized table. Since the SQL Server Stores memory for the future, it still had 110 GB on the disc. I ran the ...
0
votes
1answer
105 views

SQL Server Shrink

When running the Maintenance Plan Wizard to shrink a DB ... I am selecting all databases, however I have it set to shrink only when it grows beyond a specific size. If a database grows beyond a ...
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 ...
1
vote
1answer
90 views

Copy database to a remote server on daily basis

I need to replace the copy of my PROD db in a DEV system on a daily basis. The problem is that I haven't enough space in my DEV system to restore the PROD db. Is there any way to create a shrinked ....
0
votes
0answers
71 views

How to shrink the .ldf(log) file of the particular live production database? [duplicate]

I would like to say that as one of my production database (.ldf) log file is growing larger than databse size. The size of database file (.mdf) and their log file (.ldf) is as follows:- 1)database ...
2
votes
2answers
4k views

Should I shrink this SQL Server data file?

I have read many articles describing how shrinking data files causes terrible things to happen floods, famine, fragmentation, and general mayhem. Here is the cliff notes of my situation: SSAS ...
1
vote
1answer
138 views

Delete query does not appear to open space on the SQL Server

Our SQL Server (version 2012) is housed on a cloud server, for which we pay whenever we need additional space. We determined that we could delete some of the data to prevent the need for continually ...
0
votes
1answer
71 views

One time database shrink catch-22 solution?

I am developing a plan for SQL Server 2012 EE to properly downsize and type (nchar to char types) some needlessly Unicode nvarchar(max) fields and would like to optimize the database size as part of ...
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.....
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 ...
0
votes
0answers
568 views

Transactional Replication issue due to big Transaction Log in sql server 2008 r2

I want an answer of question and that would be highly appreciated Scenario: First of all i am an IT guy from Sports Field and using SQL Server 2008R2. I have 2 production servers server 1 and Server ...
1
vote
1answer
820 views

Shrink a mirrored and replicated DB

I have dropped one huge table from the production DB Current DB size on disk = 264 GB The real size of the Tables = 32.164 GB I am going to shrink this DB. Although it is not recommended, but ...