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
3
votes
1
answer
4k
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 ...
- 1,003
6
votes
2
answers
1k
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 ...
- 115
2
votes
3
answers
2k
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 ...
- 123
3
votes
3
answers
19k
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 ...
- 153
0
votes
1
answer
106
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
-1
votes
2
answers
774
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 ...
- 111
3
votes
1
answer
106
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 ...
- 167
2
votes
1
answer
335
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 ...
- 21
3
votes
1
answer
3k
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 ...
- 31
0
votes
1
answer
2k
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 ...
- 197
5
votes
3
answers
1k
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 ...
- 165
2
votes
1
answer
357
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 ...
- 121
4
votes
3
answers
6k
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 ...
- 1,369
0
votes
1
answer
127
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 ...
- 321
0
votes
1
answer
270
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
0
answers
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 ...
- 625
7
votes
2
answers
9k
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 ...
- 71
1
vote
1
answer
2k
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 ...
- 321
0
votes
1
answer
91
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 ?
- 1
4
votes
2
answers
2k
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 ...
- 211
0
votes
2
answers
5k
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
1
answer
1k
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 ...
- 79
3
votes
2
answers
1k
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 ...
- 213
3
votes
1
answer
6k
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).
...
- 6,178
1
vote
2
answers
3k
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 ...
- 21
0
votes
2
answers
178
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
1
answer
587
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 (...
- 629
3
votes
1
answer
77
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 ...
- 313
4
votes
3
answers
592
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 ...
- 1,774
3
votes
2
answers
4k
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 ...
- 61
0
votes
2
answers
111
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 ...
- 1,774
0
votes
1
answer
1k
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 ...
- 2,169
3
votes
2
answers
2k
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 ...
- 217
4
votes
4
answers
1k
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 ...
- 191
5
votes
3
answers
286
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/...
- 177
0
votes
2
answers
32k
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
1
answer
574
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. ...
- 535
1
vote
1
answer
2k
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 ...
- 111
0
votes
1
answer
130
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 ...
- 13
7
votes
2
answers
37k
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 ...
- 974
1
vote
1
answer
134
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 ....
- 265
0
votes
0
answers
138
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 ...
- 6,397
2
votes
2
answers
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 ...
- 21
1
vote
1
answer
826
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 ...
- 15
0
votes
1
answer
82
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 ...
- 175
7
votes
2
answers
17k
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.....
- 217
17
votes
4
answers
11k
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 ...
- 173
1
vote
1
answer
1k
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 ...
- 1,381
0
votes
2
answers
65
views
Restoring Database to a smaller test envrionment [duplicate]
I don't believe this is possible but wanted to check.
I've got a bi-weekly job running to restore the full backup to a test environment so the devs have something fresh to destroy without damaging ...
- 2,061
3
votes
1
answer
715
views
Shrinking Databases in a maintenance plan [duplicate]
Everything I've heard (including from Microsoft) says you should never shrink a database. Yet a software vendor (Symantec) is telling us to do DBCC SHRINKFILE on the SQL 2012 databases and logs ...
