Hi guys.
I have a huge database file. When I run sp_spaceused this are the
results:
db_size 1337,31 Mb
unallocated 14,62 Mb
reserved 1088456 Kb
data 258992 Kb
index_size 6224 Kb
unused 823240 Kb
Now, when I run dbcc shrinkfile(data base_name, 50) or whatever value of
percent, it says there is not enough free space in db.
Please advice how to shrink the size of that file and why is it so big?
Thanks in advance
Zvonko 3 2514
what about initially shrinking the log , and then the data file?
--
----
Jack Vamvas
_______________ _______________ _____
Receive free SQL tips - www.ciquery.com/sqlserver.htm
_______________ _______________ _____
"Zvonko" <zv************ @velkat.net> wrote in message
news:44******@n s1.novi-net.net... Hi guys.
I have a huge database file. When I run sp_spaceused this are the results:
db_size 1337,31 Mb unallocated 14,62 Mb reserved 1088456 Kb data 258992 Kb index_size 6224 Kb unused 823240 Kb
Now, when I run dbcc shrinkfile(data base_name, 50) or whatever value
of percent, it says there is not enough free space in db.
Please advice how to shrink the size of that file and why is it so
big? Thanks in advance
Zvonko
Zvonko (zv************ @velkat.net) writes: I have a huge database file. When I run sp_spaceused this are the results:
db_size 1337,31 Mb unallocated 14,62 Mb reserved 1088456 Kb data 258992 Kb index_size 6224 Kb unused 823240 Kb
Now, when I run dbcc shrinkfile(data base_name, 50) or whatever value of percent, it says there is not enough free space in db.
Please advice how to shrink the size of that file and why is it so big?
Big? 1.3 GB is a small database by today's stanadards, at least for a
production system.
The fact that the amount of "unused" is so much larger than data, indicates
that you have one or more tables that suffers from high level of
fragmentation.
This SELECT:
SELECT object_name(id) , reserved FROM sysindexes WHERE indid IN (0,1)
ORDER BY reserved DESC
gives you the tables by size. You can then use DBCC SHOWCONTIG to determine
the level of fragmentation, and DBCC DBREINDEX to defragment the tables.
However, you cannot reindex tables that does not have a clustered index
with DBCC DBREINDEX. You can however, create a clustered index on these
tables, and then drop it if you absolutely do not want it.
"reserved" = space actually allocated to tables.
"unused" = space within the reserved space that is not actually used.
DBCC SHRINKFILE operates only with unallocated space. Once you have
defragmented the tables, you may be able to shrink the file.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Do not forget to do some backup B4 the operation...
Jack Vamvas wrote: what about initially shrinking the log , and then the data file?
-- ---- Jack Vamvas _______________ _______________ _____ Receive free SQL tips - www.ciquery.com/sqlserver.htm _______________ _______________ _____
"Zvonko" <zv************ @velkat.net> wrote in message news:44******@n s1.novi-net.net... Hi guys.
I have a huge database file. When I run sp_spaceused this are the results:
db_size 1337,31 Mb unallocated 14,62 Mb reserved 1088456 Kb data 258992 Kb index_size 6224 Kb unused 823240 Kb
Now, when I run dbcc shrinkfile(data base_name, 50) or whatever value of percent, it says there is not enough free space in db.
Please advice how to shrink the size of that file and why is it so big? Thanks in advance
Zvonko
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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)
|
by: Jens |
last post by:
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('LEAR_Index',40000)
But to no apparent avail; the file did not shrink.
|
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...
|
by: paolo.arimado |
last post by:
Dear everyone,
Can someone please help me on my HW. I'm trying to write a program
that will display the following:
*
***
*****
*******
*********
| |
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:
|
by: sifrah |
last post by:
Hi All,
My SQL server transaction log is getting bigger every day and my HDD if
running out of space.
So i follow the MS KB about how to Shrinking the Transaction Log.
After doing so the log is much much smaller as i can see the size of it
under enterprise manager.
The problem is that the HDD still shows the same size.
If i shrink the DB why the and reduce its size why the HDD does not
|
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...
|
by: Michael Kintner |
last post by:
Is there any way to clear out the LDF file or reduce in size?
thank you in advance fro any help,
Michael Kintner
|
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...
|
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,...
| |
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,...
|
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...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |