Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
227
questions
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 ...
5
votes
2
answers
559
views
Catch event of auto_shrink in SQL Server Log? [duplicate]
I am aware of the disadvantages of AUTO_SHRINK being configured on SQL Server databases. Recently, in a new environment, I discovered several production servers with the Auto Shrink option turned on.
...
- 4,757
-1
votes
2
answers
1k
views
how can I delete records when my disk drive is full?
My data folder of SQL as filled up the space of my local drive. I have 150KB free space left in the drive. So I have to delete some records to regain space. But when I run the delete query it says ...
- 471
0
votes
1
answer
3k
views
Why can't I get the available free space after shrinking?
My disk drive was full. So I deleted some records. Then I tried to shrink and it indicated available free space of around 5GB as indicated in the below image.
Once I ran the shrink for data file, ...
- 471
4
votes
1
answer
4k
views
Truncate log file mirrored database
In SQL Server 2008, I have a mirrored database with a log file that has increased to 80GB, which is not normal and I have serious problem. I would like to decrease the log file size by truncating it.
...
- 51
0
votes
1
answer
81
views
Shrink configuration & content database in Sharepoint DBs
There was some argument between me and a college about for how we should shrink DBs. We knew that shrinking should be manual and only if required.
We run in a case that configuration DBs reached to ...
1
vote
1
answer
192
views
When Should You Shrink Your Database
I have read conflicting articles about shrinking a databases. Basically, our database grew 100% overnight from 150 gigs to 300 gigs b/c I believe it was with staging tables I put in. However, all the ...
0
votes
0
answers
445
views
Relationship between file shrink and used memory in SQL Server
Some background info: this is a test SQL Server 2012, with restored databases from production servers, all of them with over 20% space available in the data file, up to even 50%. I'd rather restore as ...
- 974
8
votes
3
answers
18k
views
Get MS SQL Server to release disk space
A poorly managed database table has grown to be enormous. 48+gigs of orphan records. I am trying to clean it up and put my dangerously full hard drive back to a normal state. I will be deleting approx ...
- 183
2
votes
1
answer
360
views
Dealing with non active transaction in log file and shrinking
I have posted the first part of my question here & I don't want to flood my question with a loads of information and would force people to alter their answers for my each question.
I think I am ...
- 761
7
votes
2
answers
32k
views
How to minimize log operations in SQL Server to avoid "log full" error
I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting
Msg 9002, Level 17, ...
- 761
1
vote
1
answer
1k
views
How do I reduce SharePoint 2010 SQL server log files? [duplicate]
I have a work place portal built on SharePoint 2010 and the SQL Server drive space is full. I am using SQL Server 2008 R2. Of the 800 GB disk space, the log files have taken approximately 730 GB. ...
- 13
0
votes
2
answers
695
views
What causes disk space growth from Informatica load onto SQL Server?
We've been dealing with huge disk growth on SQL Server from Informatica. After the load, the database grows to 2.4TB. After the database shrink, it goes to 1.05TB. What could likely cause this to ...
- 2,072
1
vote
1
answer
8k
views
DBCC SHRINKFILE EMPTYFILE not emptying
Trying to remove an extra database file from a database using EMPTYFILE on SQL Server 2008 R2.
use [MYDATABASE]
DBCC SHRINKFILE (MY_DATABASE_7, EMPTYFILE);
use master
ALTER ...
- 9,630
-1
votes
1
answer
154
views
shrink transaction log [duplicate]
Can I shrink transaction log when database in online using below command ?
DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
FYI my database is in production server & highly critical
...
- 1
6
votes
4
answers
1k
views
shrink after archiving, is this that bad too?
I have a database which grow to 20 GB, after archiving some data, the real size of tables is just 5GB.
Backup script does save a copy of the backup to a different location.
Moving 20GB or 5Gb does ...
- 203
5
votes
2
answers
372
views
Shrink or no Shrink dilemma
I have databases that I am tasked to move two databases DBName1 and DBName2 to SSD and I only have limited space on the SSD to fit these databases.I do not want to shrink log or data files for the ...
- 177
2
votes
0
answers
482
views
Why does shrinking the transaction log generate lots of log (LOP_SHRINK_NOOP) and not shrink the log?
I just tried shrinking an overly big log file (FULL recovery model). This operation is not expected to always succeed and indeed the log was not shrunk.
But I observed that a lot of new log was ...
- 7,300
1
vote
1
answer
1k
views
Stop never ending backup in SQL Server 2012 Express
Our production database log file has grown to consume all disk space on our server.
I know it's bad practice but I want to be able to shrink it in order to restore the service as soon as possible.
...
floum
4
votes
1
answer
3k
views
Shrinking old partitions
I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have ...
- 529
2
votes
1
answer
365
views
Shrinking VARCHAR with MODIFY: data alignment and potential for data loss
I'm using MODIFY to change (shrink) a number of VARCHAR fields of various lengths in various tables. In each case, the field is called ip and is a textual representation of an IPv4 or IPv6 address. To ...
- 121
1
vote
1
answer
86
views
Still i am not satisfying with Logfile Shrinking in SQL Server? [duplicate]
Already i asked this question but still i'm not getting the very clear answer.
In my server holds more than 20 live databases in that my logfile(.ldf) is use to grow more than 5 GB per day.
Using the ...
- 19
30
votes
2
answers
172k
views
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
...
- 449
0
votes
2
answers
183
views
Shrink SQL Server DB By xgb
I'm trying to free up some space in a database, firstly by swapping out and archiving partitions. The DB is massive though, and i want to start freeing up some space on the drive, but I don't want to ...
- 253
1
vote
1
answer
754
views
Fix transaction log file after database update
I have a databases that are regularly (monthly) updated. An update includes a lot of new or changed T-SQP procedures, functions, indexes creation, etc.
A database X has transaction log file with the ...
- 3,770
18
votes
3
answers
87k
views
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 ...
- 3,770
12
votes
2
answers
28k
views
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 ...
- 123
2
votes
3
answers
2k
views
Should I shrink my database?
I have a 1.2Tb SQL Server 2008 R2 database which is more than 50% empty, because I migrated a lot of functionality to PostgreSql. The database is still in use, and still somewhat growing, but I do not ...
- 7,224
0
votes
2
answers
175
views
How did my databases shrink?
I have a nightly job that monitors database and log file sizes. Over time the databases grow. Last night some databases actually shrank in size. The logs show no SHRINKFILE OR SHRINKDATABASE events....
- 109
9
votes
2
answers
23k
views
Set simple recovery mode and shrink log files for all user created databases
I hope you can point me in the right direction.
I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit.
I want the script to:
To select ...
- 363
4
votes
1
answer
8k
views
Shrinking large DB file not working
I have a 5 TB database file (don't ask why) that I am cleaning up. I was already able to free over a TB and I will probably end up at 2.5 TB.
All best-practice advice aside, I need to now shrink this ...
- 9,095
2
votes
3
answers
8k
views
How to do a one-time log shrink on a database with transactional replication
I have inherited a 10 GB database with an 80 GB log [only 3% in use according to DBCC SQLPERF(logspace)]. It seems safe to assume the extreme log growth was due to problems from long before I was ...
- 2,151
-1
votes
1
answer
5k
views
How to reduce the size of index file in SQL Server 2008? [closed]
I am accessing two servers
Application server (WC)
Database server
The application server is using the database server
Here index file in the database server is growing automatically without any ...
4
votes
2
answers
2k
views
Autogrowth, shrink and database performance
I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it.
I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 ...
- 3,131
5
votes
3
answers
13k
views
Shrink the database only up to its initial size which is set after creating database
In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file.
So now if create a new ...
- 3,131
37
votes
4
answers
19k
views
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 ...
- 18.1k
5
votes
2
answers
2k
views
if database is not shrinked does it affect backup size?
Ok my question is simple.
Lets say that when i click database and hover over shrink it says it can be reduced 1 GB space.
Now when i do a backup of this database, does this space also getting added ...
- 553
21
votes
5
answers
113k
views
SQL Server Database Not Shrinking [duplicate]
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 ...
- 471
0
votes
2
answers
797
views
How Can I Determine Object Distribution in a Multiple-File Filegroup?
I am looking to eliminate an extraneous data file in a filegroup in my DB but don't want to use the dreaded DBCC SHRINKFILE method, I am preferring to rebuild indexes into an alternate FG, perform the ...
- 1,933
1
vote
2
answers
5k
views
Freeing space after deleting rows [duplicate]
I have a SQL Server 2008 instance and one table is quite large (log data) so I delete old logs. Now I start to read articles on this problem, here is one.
As I see it there is no solution! If I ...
2
votes
2
answers
680
views
Large number of VLF and shrinking the log
If there are lot number of VLF then it will effect the performance of the queries. I saw that solution is to shrink the log file. But shrinking the file is also bad. So how to take control of whole ...
- 3,131
10
votes
3
answers
16k
views
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 ...
- 103
7
votes
1
answer
7k
views
SHRINKFILE best practices and experience [duplicate]
Preamble: In general it's a big no-no, but believe me that are rare cases when space is really needed. For example Express Edition is limited to 10GB. Imagine that you discovered that with a data type ...
- 239
7
votes
2
answers
3k
views
Database defragmentation and autogrowth settings
We do have some maintenance plan for our SQL Server 2008 r2 express.
Every month we do de-fragmentation of the database if any table has page count more than 50 for any table and average fragmentation ...
- 3,131
0
votes
2
answers
2k
views
Should I shrink the log file? [duplicate]
My log file was too big, and I made it unrestricted in growth. Should I shrink it with SQL Server Management Studio? What are the downsides, if any, of doing this?
Mike Flynn
-3
votes
3
answers
10k
views
Shrink Database using query [closed]
I have deleted tons of records from my database and now i want to shrink the database.
So please give me script which shrinks the database
- 15
2
votes
2
answers
6k
views
Shrink transaction log while transaction in progress
I have a several-GB import into my SQL Server 2005 (Standard Edition) database.
The import is separated into 4 parts, each part in its own transaction.
As you can guess, the transaction log files ...
- 183
9
votes
5
answers
29k
views
DBCC SHRINKFILE works with file_id but not with logical name
I am trying to shrink a database file but am running into errors.
Using the file_id from sys.database_files works but using the logical file name yields an error.
The logical file name is the same in ...
- 371
7
votes
3
answers
6k
views
How should I best handle a rapidly growing database?
I have a database that I need to maintain.
Sadly, the setup and use of that database I can't change, much (thanks to some internal politics).
It's running on SQL Server 2008r2.
Its only been live for ...
- 173
2
votes
2
answers
4k
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 ...
- 155