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

learn more… | top users | synonyms (1)

5
votes
3answers
229 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 ...
0
votes
2answers
73 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 ...
1
vote
1answer
47 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. ...
0
votes
1answer
22 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
56 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 ...
2
votes
2answers
117 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
41 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
28 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
292 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
40 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
33 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 ...
1
vote
2answers
371 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 ...
12
votes
4answers
804 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
84 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 ...
0
votes
1answer
31 views

Using SHRINKDATABASE to shrink a Database by a certain amount

I have a database which the users are going to do some data cleansing on. They should do this every 6 months but have left it for 18 months. After they have cleansed the data I want to shrink the ...
0
votes
2answers
38 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 ...
3
votes
1answer
90 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
2answers
133 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. ...
-1
votes
2answers
97 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 ...
0
votes
1answer
207 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, ...
4
votes
1answer
1k 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. ...
0
votes
1answer
8 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
1answer
77 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
0answers
72 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 ...
6
votes
3answers
1k 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 ...
1
vote
1answer
104 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 ...
3
votes
2answers
2k 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, ...
1
vote
1answer
216 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. ...
0
votes
0answers
27 views

Database Log Files Getting out of control [duplicate]

We have a database in which the transaction log file is blowing up to 100GB with the actual database being 1GB in size. It blows up pretty quick as this database is pulling data from another database ...
0
votes
2answers
146 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 ...
1
vote
0answers
1k 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 ...
-1
votes
1answer
74 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 ...
6
votes
4answers
840 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 ...
5
votes
2answers
178 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 ...
1
vote
0answers
125 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 ...
1
vote
1answer
495 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. ...
2
votes
1answer
303 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 ...
1
vote
1answer
71 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 ...
1
vote
1answer
67 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 ...
8
votes
1answer
39k 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 ...
0
votes
2answers
117 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 ...
1
vote
1answer
490 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 ...
6
votes
1answer
13k 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 ...
4
votes
2answers
5k 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 ...
2
votes
3answers
699 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 ...
0
votes
2answers
114 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 ...
4
votes
2answers
8k 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 ...
4
votes
1answer
2k 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 ...
2
votes
3answers
5k 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 ...
-1
votes
1answer
2k 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 ...