Solved

DATABASE SIZE SHRINKING AND LOG FILE QUESTION

Posted on 2002-03-05
3
299 Views
Last Modified: 2012-06-27
Hello all.  I have a database that is approx 625 Meg large currently.  I am not sure why it is getting so large it was only approx 200Meg about 2 weeks ago.  What I did was I dumped the log file and then did a shrink it went from 701 Meg to 625 Meg.  One of the tables is very large it has 1.3 million records in it.  I know that might be why it is so big.  But I was wondering it says in the properties of the database and on the data files tab it says space allocated 624 Meg.  Is there any checks I can do to see actually how big the database should be like by the records etc?  Any other way to shrink database more?
0
Comment
Question by:InvisibleMan
  • 2
3 Comments
 

Accepted Solution

by:
baulrich earned 15 total points
ID: 7752598
Invisible,

each SQL database is made up of a .mdf file and a .ldf file.  These are by default in C:\Program Files\Microsoft SQL Server\MSSQL\Data\  You can go here and view the size of your 2 database files:  dbname.mdf and dbname.ldf  usually.  The .mdf file cannot be reduced in size, as it actually contains the info in your db.  The log file, under the right conditions should be able to be reduced to about only 1 MB.  So if your dbname.ldf file is considerably bigger than that, you can shrink the size occupied by the entire DB.  To do that you can use Enterprise Manager (Start->Program Files->Microsoft SQL Sever->Enterprise Mgr).  Browse to the server and find your database name under the databases folder.  Right click on the name of your DB and go to properties.  Now...go thru the tabs(i think it the 3rd or 4th over) on this dialog box and find the drop-down box for the logging mode.  This can be set to Full, Simple, and maybe one or 2 other options.  Be sure to choose Simple here.  Now you can go back to your DB->All Tasks->Shrink Database.  It should get your .ldf file down to about 1 MB.  Now, you may want to go back and change the logging mode back to Full if you are scared of not recovering if something goes wrong.  Hope all that helps.

- baulrich
0
 

Expert Comment

by:SpideyMod
ID: 8004169
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

I am in the process of cleaning up the questions for this member as this member has failed to take care of their opened questions
Experts, please leave your recommendations within the next 3 days.
Without recommendations, I intend to come back here on 2/26/2003 and:
Force Accept answer by baulrich


InvisibleMan,
You have 16 opened questions and have been asked to take care of them previously.  If you wish to keep your account in good standing, please take care of these questions prior to 02/26/2003.  
Failing this, your account will be reviewed by a site administrator at that point.  I am posting this in ALL 16 opened questions and have submitted a cleanup request in community support.

http://www.experts-exchange.com/Applications/MS_Office/Q_20439569.html
http://www.experts-exchange.com/Databases/MS_Access/Q_20453889.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20388128.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20343921.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20273575.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20480517.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20475331.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20409250.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20336887.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20327829.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20324132.html
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20390261.html
http://www.experts-exchange.com/Web/Graphics/Adobe_Acrobat/Q_20403544.html
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_20519637.html
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_20457825.html
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_20428768.html


SpideyMod
Community Support Moderator @Experts Exchange

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
0
 

Expert Comment

by:SpideyMod
ID: 8059011
All C grades issued are being changed to A grades.  InvisibleMan, if you have issue with this, please post a comment in Community Support and I will have another moderator or an admin look at my actions.  If the answer didn't work, you should have opted to request a refund instead of accepting an answer which any one of us would have gladly assisted with.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now