Why Is SQL DB So Big? Can I Shrink?
Jun 16, 2004
Hello,
We just migrated our DB from one SQL Server to Another. Both DB's used to be the same size at 11GB. The new SQL Server is a whopping 33GB, and I can't seem to find out why. The new DM has fewer tables (the data is identical).
Does anyone know how I can compact or shrink my DB, know why or how it grew so much and didn't go back down to normal size, and maybe where I can search properties or change settings to find out what is going on and prevent it from happening again.
I know MS Access has a shrink utility, but don't know where it is in SQL Server.
Thanks, in advance for your help.
Rodney
View 12 Replies
ADVERTISEMENT
Jan 5, 2007
If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:
1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.
View 4 Replies
View Related
Jun 6, 2002
I have a SQL 7.0 sp3 server and I am trying to shrink a production database for use on other servers. The database/log sizes are 9601mb / 138mb with 772mb / 128mb free. I truncate a table that contains binary data. After this the database/log sizes are 9601mb / 138mb with 6634mb / 111mb free. The next step is to use EM to issue a shrink db. After this the database/log sizes are 6807mb / 378mb with 9.56mb / 365mb free.
After the use of truncate the the data in the datafile is about 2967mb.
After the shrink db the data in the datafile is about 6797mb.
The size of the data grew after the shrink db was issued. I looked at the tables and the space is largely associated with two tables that have a binary field, but the number or rows are still the same. I ran a checkdb just to make sure that there was not a problem and no problems were reported. I have used this process many times and this is the first time I have come up with these results. Any suggestions?
Thanks,
Glen
View 2 Replies
View Related
Aug 17, 2001
if I shrink the db will it enable me to create another db with that space released by the first db?
View 1 Replies
View Related
Jul 19, 2000
How can i shrink my log file? It is like 900 megs and i
was it to be more like 400 megs.
Please Help
Melissa Sener
Project Manager
Square Yard
View 5 Replies
View Related
Sep 27, 2002
Hi, My name is Alexander and came´s to ask about Sql Server log.
Do you know why sometimes the log not truncate? All time
comes increasing!
I setup "Automatic Shrink" and it swims happens. If you can help me!
Thanks in advanced
View 3 Replies
View Related
Jun 30, 1999
Hi everybody:
Actually I have a 8.5 Gb database but the correct size that I need
to due disk space is 5 Gb. When I make a backup, the size of the dump is
4Gb, so I wonder if I create a 4- 4.5Gb my dump db will fit in it, but
not. Other way is create antoher db with 4 - 4.5 Gb and transfer via
sql-transfer or sql-bcp but I got a message that my device is too
short.
So I ask : What are the correct ways to reduce the size of a
database ?
Thanks in advance.
View 1 Replies
View Related
Jul 26, 2004
Hello,
I have a 24go sqlserver 2000 database, but only 5go used, when i use the shrink command in em there is no change.
can i modify the property of the db (auto-shrink => on ) actually it's on OFF.
View 6 Replies
View Related
Nov 27, 2006
Newbie question about reclaiming some disk space on our SQL server.
We currently have a db that has the following stats:
space allocated: 34734 MB
space free: 843.66
of this the current size of .mdf is 33877 MB and the space used is 33873 MB
the drive that holds this database is running out of space, what is the best method for me to use to reclaim some disk space?
I have read books online and am a little bit confused as to whether or not using shrinkfile on the .mdf will free up space to the OS? Do I need to do anything else before or after such as reindex? Also what is the overhead as we only have about 10% free on the drive.
View 4 Replies
View Related
May 22, 2006
I am trying to shrink a log file (.LDF), but it will not work. I am typing:
BACKUP LOG "Database1" WITH TRUNCATE_ONLY
dbcc shrinkfile ("Database1_log", 50)
The microsoft site says this should work:
http://support.microsoft.com/kb/272318/
...but I can't get the file to shrink, at all? I'm sure the SQL services need to be running. I am logged in as my Administrator / sa. What could I be missing? Trying to shrink it too much? (THANKS).
View 3 Replies
View Related
Jul 26, 2000
A few of the databases I have are take up a lot of space, but when I look at the actual space that's being used, it's very small. I've tried shrinking them but they still remain at this bloated size. Is there any other way to reduce their size?
View 3 Replies
View Related
Oct 24, 2001
what's this : "auto shrink " db option in properties ???
in which case may i use this option ?
please help
View 3 Replies
View Related
Sep 18, 2000
Txn log is nearly 10GB with only 60MB actually used. Disk has 150MB remaining. SQL 7.0 SP2.
I've truncated the log several times. I'm aware that the active portion of the log can be at the end and have added many records to cycle the active portion round to the start of the log file. How big are these log segments?
I've run dbcc shrinkfile with and without a target size.
I've checked DBCC OPENTRAN. No open txns exist.
What else can I try? I've looked through similar postings and haven't spotted anything.
Yours hopefully,
Simon
View 2 Replies
View Related
Mar 27, 2000
I am looking for the correct procedure for shrinking a database file ,
please reply back if anybody knows the solution
Thanks
Ajay
View 1 Replies
View Related
Jan 18, 2001
Is it possible to shrink Transaction Log file? If yes, how? Urgent matter please.
Regards,
View 1 Replies
View Related
Oct 20, 1999
On a database with a log file that has an unrestricted file growth, the file size exceeds 1 GB. Since this excessive was caused by a badly written update statement, I want to reduce the size to about 200MB.
After reading the BOL I was convinced that I only need to take two actions: truncate the log file (to create some free space in the log file)and shrink it.
These are the statements I executed:
backup log ODS with truncate_only
dbcc shrinkfile (ODS_Log, truncateonly)
After I executed these statements - BTW, there were no errors - the file size was still the same. Can somebody tell me why?
Thanks,
Stef
View 1 Replies
View Related
Feb 17, 2000
Is there a way to shrink the error log in SQL 6.5 without stoping the server?
View 1 Replies
View Related
Aug 27, 1999
Hi,
I have created a new database in SQL Server 7 with the auto grow options set to on. I then added a whole load of new data to the table which made the transaction log file grow to 20Mb.
I then truncated the transaction log to remove all the completed transactions. The Enterprise Manager now shows the Log to only have 3MB of data in it but the file is still 20MB.
I have tried setting the truncate log on checkpoint option, and tried running DBCC SHRINK DATABASE and DBCC SHRINK FILE commands but these seem to have no affect on the file size.
Does anyone have any idea what I might have missed/done wrong?
Yours well and truly stuck,
Martin
View 2 Replies
View Related
Jun 9, 2000
How do I shrink my transaction log file ? The physical file are 10Gb in size but only contains 100Mb data so I want to shrink it to save diskspace. I tried DBCC SHRINKFILE command but the file dont shrink.
Any other methods to shrink a transaction log file ? Like backup/restore ?
Help !
Thanks
View 2 Replies
View Related
Mar 7, 2001
Is it possible to shrink the size of a transaction log file in SQL 6.5. Normally in SQL 7.0 I use detach and reattach but this is not supported in 6.5. The log file for a 200Mb database is 749Mb with no unused space and no open transaction. Thanks
View 2 Replies
View Related
Sep 25, 2002
Hi,
I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays:
The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.
The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7.
Does anyone have a solution to this dilemma?
Thank You
View 4 Replies
View Related
Nov 9, 1999
I am in the process of copying database from large size to smaller size one. The actual data in the database is very less.
When I try to shrink the database, I am getting message I can't shrink the database.
Can any one suggest on 'How to copy this database?'
Thank you,
View 4 Replies
View Related
Jan 21, 2004
Hi
I have a SQL2k db ABC with only 2mb data but around 500 log. I did the followings
----------------------------------
backup log ABC with truncate_only
dbcc shrinkdatabase(ABC, 10)
Cannot shrink log file 2 (ABC_Log) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 2 58768 128 58768 128
use ABC
sp_spaceused
database_name database_size unallocated space
------------ ------------------ ------------------
ABC 506.75 MB -0.51 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
2248 KB 1288 KB 544 KB 416 KB
sp_helpdb ABC
name size maxsize growth usage
-------------- ------------------ ------------------ ------ ---------
ABC_Data 1728 KB Unlimited 10% data only
ABC_Log 517184 KB Unlimited 10% log only
question:
1. what is the negative -0.51 MB for unallocated space?
2. I can't kill all the process logins, is there other way to shrink the database log side?
thanks
-D
View 6 Replies
View Related
Jul 31, 2006
Hi All,
I have a question that keeps my mind buys and I wasn't able to find answer on that.
I have a full backup recovery plan and I am backing up my Log files in every 15 minutes. At the same time My log files are shrunk in every 3 hours so Are shrinking the log files correct approach?
If somebody enlighten me on this issue I appreciate it.
Thanks
LS
:confused:
View 4 Replies
View Related
Jan 15, 2002
Id like to know how I force the transaction log to shrink in v2000?
I have sp_force_shrink_log but this only works in v7.
... Also, how do I set the log to truncate on checkpoint?
Many thanks,
Lauryn
View 1 Replies
View Related
Feb 27, 2002
Hi,
Issuing 'dbcc shrinkfile (log ,truncateonly)'
I get the error message below. Books online doesn't say any more.
Can anyone explain? I am the only user connected to the db at the time, no jobs are executing.
Cannot shrink log file 2 (log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View 1 Replies
View Related
Jun 25, 2004
Hi,
Roughly how long would it take to shrink a 300GB db, its been running over 8 hours already (throught enterprise manager)?
Thanks in advance
aido.
Dual P4 xeon 2gb ram 350gb scsi raid array
View 3 Replies
View Related
Jun 17, 2004
Ok. Thanks for the help today with helping me isolate why I have 2 almost identical databases, one is 11GB and one is 33GB.
I was able to find out that a few of the tables are way off in size proportions. In one database, the file is 354 MB, and the new DB, the same file is 8.9 GB!! The row count is identical and the number of columns is the same.
I tried dropping the table and an doing a full refresh, so my database is smaller, but there is still a lot of free space that won't go away when I shrink the DB.
1. Does anyone know the code to shrink each table one at a time?
2. Does anyone know how to remove the free space in my DB, the shrinkDB function is just not working.
Thanks.
View 6 Replies
View Related
Oct 25, 2004
I have Disk Xtender 2000 which was made by OTG Software , Legato and now EMC. I have an NT 4.0 PC with Microsoft SQL 2000. I have a drive space problem and need to shrink a 38 gig .ldf file called OTG03.ldf I also have a 2 gig .mdf file called OTG03.mdf How can I shrink this .ldf file. I'm not a DBA so being specific is greatly appreciated.
View 7 Replies
View Related
Jan 12, 2005
file?
I know how to shrink the log file but I am not sure if I can shrink the MDF file
and how. I am using SQL 2000.
I don;t think my database is that big but the complete backup file is up
to 6G.
this is what is displayed in Taskpad
MDF-------------- 5941M Used 589M Free
Log -------------- 3.58 M Used 12.5M Free
Any suggestion is greatly appreciated.
Thank you
View 4 Replies
View Related
Oct 9, 2007
Hello, everyone:
I have a production database in which data file takes 17GB and used 10GB. I want to shrink it. Once I take about one hour to shrink 1GB. So I am worry long shrinking time. Is there down time during the shrinking that doesn't allow user access?
Thanks
ZYT
View 4 Replies
View Related
May 15, 2008
My database is current 135GB. I have been running the following:
DBCC SHRINKFILE([database_data],2)
DBCC SHRINKDATABASE ( [Database],20,TRUNCATEONLY)
It's been running for 28 hours. Should it be taking this long? If I stop the shrink, will it need to rollback?
Thanks!
View 2 Replies
View Related
Dec 13, 2012
My DB's recover model is SIMPLE. Is it OK to schedule a SHRINK FILE only on the log files regularly? Any GOOD vs BAD about my plan? I want to do this because the log files keeps on increasing. Right now, the log file s on ENABLE AUTOGROWTH, FILE GROWTH = 10%, RESTICTED FILE GROWTH = 2,097,152.
View 8 Replies
View Related
