473,847 Members | 1,561 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Shrinking database files

I have a database file LEAR_Index(yes, it hold index data) from a have
have recently removed a bunch of data.
It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink the
file to lean 30-40Gb.

I´ve been trying this:

dbcc shrinkfile('LEA R_Index',40000)

But to no apparent avail; the file did not shrink.
I´ve tried using enterprise manager for this but it consistenly
crashes when performing this operation.#

Any thoughs, idear as to what i might be doing wrong?

Jul 23 '05 #1
6 3098
Is LEAR_Index also the logical name of the database's data file? If
not, then you may have mixed up the syntax for DBCC SHRINKFILE and DBCC
SHRINKDATABASE:

DBCC SHRINKDATABASE( LEAR_Index, 30)
DBCC SHRINKFILE(LEAR _Index_Data, 40000)

If that doesn't help, perhaps you can post the result set from the DBCC
execution, or whatever output you get. This KB article might also be
relevant:

http://support.microsoft.com/default...b;en-us;324432

Simon

Jul 23 '05 #2
Jens (je************ @lycos-europe.com) writes:
I have a database file LEAR_Index(yes, it hold index data) from a have
have recently removed a bunch of data.
It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink the
file to lean 30-40Gb.

I´ve been trying this:

dbcc shrinkfile('LEA R_Index',40000)

But to no apparent avail; the file did not shrink.
I´ve tried using enterprise manager for this but it consistenly
crashes when performing this operation.#

Any thoughs, idear as to what i might be doing wrong?


You may first have to run DBCC DBREINDEX to defragment fragmented
tables.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
I recall shrinking a database using enterprise manager and thinking it had
locked up (i.e. crashed) but it hadn't - it came back when it had finished
shrinking.

If Enterprise Manager appears to lock up then try shrinking using it before
you go home and see if it has finished by the following morning.

Brian.

www.cryer.co.uk/brian
Jul 23 '05 #4

Jens wrote:
I have a database file LEAR_Index(yes, it hold index data) from a have have recently removed a bunch of data.
It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink the file to lean 30-40Gb.

I´ve been trying this:

dbcc shrinkfile('LEA R_Index',40000)

But to no apparent avail; the file did not shrink.
I´ve tried using enterprise manager for this but it consistenly
crashes when performing this operation.#

Any thoughs, idear as to what i might be doing wrong?


It takes many hours on even good hardware to perform this operation.
you did not mention your disk setup.
In any event, if the actual usage is only 20gb, it may be cheaper to
create another file of 30gb, move the objects to it and drop the old
file.
alternatively you should consider partitioning, which will allow you do
make this move gradually.

Jul 23 '05 #5
Erland - why do you think this will help? Fragmented tables don't affect
shrink in the least, and also, shrink will most likely remove all the work
done by DBREINDEX as it tends to introduce fragmentation.

Thanks

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.0.0.1...
Jens (je************ @lycos-europe.com) writes:
I have a database file LEAR_Index(yes, it hold index data) from a have
have recently removed a bunch of data.
It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink the
file to lean 30-40Gb.

I´ve been trying this:

dbcc shrinkfile('LEA R_Index',40000)

But to no apparent avail; the file did not shrink.
I´ve tried using enterprise manager for this but it consistenly
crashes when performing this operation.#

Any thoughs, idear as to what i might be doing wrong?


You may first have to run DBCC DBREINDEX to defragment fragmented
tables.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #6
Paul S Randal [MS] (pr*****@online .microsoft.com) writes:
Erland - why do you think this will help? Fragmented tables don't affect
shrink in the least, and also, shrink will most likely remove all the work
done by DBREINDEX as it tends to introduce fragmentation.


Ok, thanks for the correction, Paul!
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2021
by: John Mosey | last post by:
A huge (and never used) database log was taking up about 4 GB of HD space. We want the data for historical capacity, however, don't care about the transactions log. After a bit of research I ran the script on: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q256/6/50.asp&NoWebContent=1 (which works just fine on Sql Server 2k) And then
2
2228
by: Jaeger | last post by:
Hi all. I'm having trouble shrinking a database. In short, using the SQL Query Analyzer, I enter the following commands: DBCC SHRINKDATABASE (database, 80) GO The command returns the expected 'DBCC execution completed.' response, but
1
2242
by: willie | last post by:
Hi all: I restored one backup database (7.9 GB mdf) on two diffrent servers. I shrunk them by clicking "Move pages to beginning of file before shrinking". After shrinking, one mdf file is 6.7 GB, and the other is 4.2GB. I shunk again and again: 1. the 6.7GB become 5.9GB, 5.2GB, 4.7GB and 4.2Gb (four times) 2. the 4.2Gb become 4.0GB (just one more time)
1
1636
by: jeffreyv | last post by:
Hi! I'm studying to have my MCSE 70-228 certification and I'm trying some things with backing up transaction logs and shrinking it. Here's what I do: There is no activity in the database by the way. I have a transaction log of 1792 kb...
2
1759
by: Marc C | last post by:
Hello, I am trying to clean up a database I inherited. I have an 80GB SQL 2000 database with 20 datafiles each 4096 in size. I have been able to remove unneeded data and am now trying to clean up. If I do a Shrink on each datafile would able to recover on average 2gb out of 4g, however I would prefer to have 10 full datafiles and 10 empty. (or better yet 5 full 8GB datafiles and 15 empty)
3
5763
by: Bopolissimus Platypus Jr | last post by:
hello all, i've got a database that takes up 4G of space. when i run a script that deletes all rows and then vacuum, the data directory gets down to around 3-3.5G. what i'd like is to get a blank database structure that really contains no data at all, or any unused space at all. apparently that's not what i'm getting now. is there a way to get this apart from dump, initdb, restore? i figure that if i could dump/initdb/restore i'd...
2
1571
by: William | last post by:
I've been tasked with taking over the support for a client's SQL Server database. I'm not a DBA by profession, I'm a software developer who uses SQL Server as a database designer. The clients have reported that the server is running out of disk space and examination shows that the log files for several of the databases are at 5Gb or more. After reading around the subject I suggested the following sequence of operations:
7
3985
khickyphutz
by: khickyphutz | last post by:
Good day! I would like to ask for a technical advice regarding transaction logs. Right now, our server only have 2.8GB due to the transaction log of our database which is 65GB now. Its database size is around 1.8GB. There is an approximate growth of 1GB+ per day in the transaction log. I have tried backing up the transaction log with the option of remove inactive transaction logs but it wont work, as well as backing up completely the...
0
1309
by: kazper | last post by:
Good day, I have some problems regarding my Hard disk space for my logs in SQL Server 2005. When I try shrinking the database it returns an error not letting me to do anything about the size of the file. Is there any way that I can delete the used transaction logs in my LOG File to free some disk space that I need. I cannot delete or add any new files on my database, because the transaction log file is full. My database is set AUTO GROW...
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9735
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10347
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4541
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4133
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.