Solved

Space available appears as 0.00 MB in Sql Server 2000 database

Posted on 2009-04-08
15
1,709 Views
Last Modified: 2012-05-06
Hi all.

I have a database 'myDB' when I right click it and select properties. In the first tab 'General' it says the following:

Size: 2196.38MB
Space available: 0.00 MB

In the second tab 'Data Files': 1151 Space allocated (MB)
In the third tab 'Transaction Log': 1046 Space allocated (MB)

When I go to where the files are on my server, the .mdf file shows 1,178,368KB, and the .LDF file shows 1,070,720KB.

How can I have space available for this database?

Thank you in advance.
0
Comment
Question by:printmedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097397
Do you have your database setup to Auto Grow?  All you have to do is, on the second and third table respectively, increase the size to whatever you feel you need.  If the data segment fills up, it will cause the backup to occur in the log segment.  So it is likely that increasing your data segment to "an appropriate value" (which you must determine) will alleviate your log segment issues.
0
 

Author Comment

by:printmedia
ID: 24097535
On SQL Server 2000, where would I find the option for Auto Grow?, in the Options tab I see a setting for Auto Shrink.

Would it be the Automatically grow file options found on the Data Files and Transaction Log tabs?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097730
I would advise against auto-grow.  Because it can mask problems in your database.  I am always in favor of monitor and manually grow.

But if you must.

In enterprise manager:
right click the db > properties
Data Files (Transaction Log) table.
Check the box at the bottom that says "automatically grow file" with the options you want.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:printmedia
ID: 24097983
Ok. I gave my Data Files tab: 4000 Space allocated (MB) and Transaction Log tab: 2050 Space Allocated (MB)

Now the General tab shows:

Size: 6050.00 MB
Space Available: 1113.60 MB

And the file .mdf file shows: 4,096,000 KB
and the .LDF file shows: 2,099,200 KB

That .LDF file is pretty big, this database is used by 15 people non-stop. But how can I keep it controlled? If I have a scheduled job that does the following on a daily basis:

DBCC SHRINKDATABASE (N'myDB', TRUNCATEONLY) will this help?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24098073
you should NOT be shrinking your database.  That's why your free space is 0MB.  You need to be managing your backups of your database.

Here's an article that chapmandew has written about managing database backups and log file usage.

http://blogs.techrepublic.com.com/datacenter/?p=448
0
 

Author Comment

by:printmedia
ID: 24099089
Thanks for the article Brandon, it cleared up a lot.

As stated in the article, our database is in FULL recovery mode, so we have to start doing various transaction log backups throughtout the day.

We are in SQL Server 2000 and the article assumed SQL 2005, so my question is how would I do this in SQL 2000? Is it a scheduled job that runs let's say 4 times a day? What would the command in the job be?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24099356
The same basic principles for how and when to backup a database still apply.  Also for what recovery mode you should be in.  Since it would seem that you aren't doing transaction log backups now, do you even need to?  

There are several reasons to be in FULL recovery mode.  Replication and point in time restore being two of them.  If you aren't doing either, you may want to CONSIDER (I'm not saying you should or shouldn't) changing our recovery mode to simple.
0
 

Author Comment

by:printmedia
ID: 24099927
I just checked the database properties again, and it went back to
Size 3399.00 MB
Space available:  0.00 MB

And in the Data Files tab it changed the Space Allocated (MB) back to 1349 MB. Even after I changed it to 6050 MB earlier.

Why is it cutting it down? I checked my scheduled jobs, and there hasn't been a job run in the past 4 hours.
0
 

Author Comment

by:printmedia
ID: 24099977
Actually, I looked at the maint\log folder, that log file is being backed up every 3 hours. So we did have a maintenance plan set, so that file grew...but that still doesn't explain why it keeps lowering the Space Allotted for the Data Files.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24099985
Either you executed "DBCC SHRINKDATABASE (N'myDB', TRUNCATEONLY) "

or

Autoshrink is enabled.
0
 

Author Comment

by:printmedia
ID: 24100006
Yes you are right, Autoshrink is enabled, so the trouble is not my Data Files but rather the transaction logs, and those are being being backed up every 3 hours. So maybe I should wait until the next time the Maintenance Plan runs which is at 3:00pm EASTERN TIME to see if the size of the log file goes down?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24100037
If auto-shrink is enabled, and you are in full recovery mode, the log will shrink after there is free space in it (when it is backed up).  The data segment will shrink when there is free space as well.

You can't manually expand a database that is set to auto-shrink.  Because it will just see that it can be shrunk and shrink it for you.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24100041
Translation: Don't enable auto-shrink.
0
 

Author Comment

by:printmedia
ID: 24100863
Thanks for all your help and patience Brandon.
0
 

Expert Comment

by:lwilliams1809
ID: 33693492
After turning off the auto-shrink AND NEXT doing   DBCC updateusage (@DBname) and refreshed my  view, everything worked fine. I'm  now able to see the Space Available 599.65 MB  vs. 0.00
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

729 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