Link to home
Start Free TrialLog in
Avatar of printmedia
printmedia

asked on

Space available appears as 0.00 MB in Sql Server 2000 database

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.
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

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.
Avatar of printmedia
printmedia

ASKER

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?
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.
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?
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
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?
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.
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.
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.
Either you executed "DBCC SHRINKDATABASE (N'myDB', TRUNCATEONLY) "

or

Autoshrink is enabled.
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?
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.
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for all your help and patience Brandon.
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