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

2
votes
1answer
54 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 ...
-1
votes
2answers
63 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
56 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
198 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
7 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
65 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
50 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 ...
5
votes
3answers
531 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
62 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
315 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
60 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
24 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
56 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
votes
1answer
61 views

shrink transaction log

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
vote
0answers
69 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
259 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
161 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
50 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
63 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 ...
5
votes
1answer
17k 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
97 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
353 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 ...
1
vote
1answer
7k 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
votes
2answers
2k 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 ...
0
votes
2answers
108 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
1answer
852 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
3k 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 ...
2
votes
2answers
420 views

Autogrowth, shrink and database performance

I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it. 1)I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 ...
3
votes
2answers
4k 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 ...
17
votes
3answers
2k 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 ...
3
votes
2answers
231 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 ...
4
votes
4answers
13k views

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 ...
0
votes
2answers
328 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
vote
2answers
2k 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
2answers
216 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 ...
7
votes
2answers
4k 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 ...
5
votes
1answer
2k 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 ...
3
votes
2answers
1k 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 ...
0
votes
2answers
616 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?
-3
votes
3answers
3k 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
0
votes
2answers
447 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 ...
4
votes
4answers
6k 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 ...
7
votes
3answers
2k 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 ...
1
vote
1answer
1k views

Howto shrink an sql-anywhere 12 db?

is there any tool to shrink SQL Anywhere Databases? With MSSQL/MSSQL Express it was an easy task. Just do it, have a faster DB again which has a size which can be transferred over a slow internet ...
2
votes
4answers
164 views

When should I use the shrink option

I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
5
votes
5answers
27k views

Shrinking the log file does not reduce size

I have a database which had mdf size of 350 MB and ldf size 4.9 GB 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 still i ...
0
votes
2answers
1k views

Reclaiming space from a large partitioned database

We have a database where one of the business group decided to remove several millions of rows as part of their upgrade process. (we removed for them..).. This database is partitioned based on a date ...
4
votes
2answers
5k views

Shrinking the SYSTEM tablespace in Oracle

Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table. We have truncated SYS.AUD$ but the datafile is still very big (~30G). Resize doesn't work because the file contains used data ...
0
votes
1answer
1k views

Is Reorganizing Indexes required after ShrinkDatabase or can I just reogranize index with fragmentation more than 10%?

I am a new DBA to a SQL 2005 production Report server. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on ...