SQL Database Size 0 and Unallocated space in negative ..how to Free up?

Question

Post reply

SQL Database Size 0 and Unallocated space in negative ..how to Free up?

SQlLogic

SSCrazy Eights

Points: 9686
More actions
May 30, 2014 at 12:39 pm

#287069

Hi everyone ,
One of database size on sql 2000 showing available space 0.00MB and unallocated space -6030.04 MB.
Any idea how to free up space.
Is could be reason database holding locks when user trying to insert data?
when user trying to access database from application they are not able to make change in DB through app. is this could be reason?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

1. What recovery model is the database in?

2. What type(s) of backups are you running?

3. What are you doing for index maintenance?

4. How big is TempDB?

5. How big is the log file on your database?

To fix the negative space available thing, try running the following in the database you're having problems with...

DBCC UPDATEUSAGE (0);

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sanjivvinaik SSC-Addicted Points: 434 More actions · Answer 2

This issue is caused if log back is not taken before tlog file get full. There are 2 ways to correct this issue. I am putting xxx please replace it with your db name.

First way if you don't care about point of recovery. Please follow below steps. Execute below tsql:

ALTER DATABASE xxx SET RECOVERY SIMPLE;

Then find logical name of your log file by using below tsql:

select * from sys.master_files

(Take the name of your DB log file. I think it is 7th coloum in the result returned by above tsql). Then execute below tsql

USE xxx;

GO

CHECKPOINT;

GO

CHECKPOINT; -- run twice to ensure file wrap-around

GO

DBCC SHRINKFILE(yourdb_log, 19000 MB);

GO

(Check space allocated for your log file and change above figure (19000 MB) accordingly)

And you are done...

Now Second way if you care about point of recovery. Please follow below steps.

ALTER DATABASE xxx SET RECOVERY FULL;

After that take a log backup

BACKUP LOG xxx

TO DISK = 'C:\xxx.TRN'

GO

Then

Find logical name of log file of your database. Use below statement to get logical name. (7th column)

select * from sys.master_files

Then increase the log size

USE [master];

GO

ALTER DATABASE xxx

MODIFY FILE (NAME = yourdb_log, SIZE = 20000 MB, FILEGROWTH = 500MB);

GO

(Please set size according to disk space available and change above figure (20000 MB) accordingly)

USE xxx;

GO

DBCC SHRINKFILE(yourdb_log, 19000 MB);

GO

(Check space allocated for your log file and change above figure (19000 MB) accordingly)

And you are done...

Thanks...

Sanjiv Vinaik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sanjivvinaik (5/31/2014)
This issue is caused if log back is not taken before tlog file get full.

Lack of log backups don't cause negative used space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sanjivvinaik SSC-Addicted Points: 434 More actions · Answer 4

Hmm... I was under impression that if log backup are taken in due interval (maybe 2 mins or so depending on application) before tlog gets full in that case database should will not go into negative size. Please correct me what else can cause this please.

Thanks..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The only way a DB can record 'negative' size or free space is when the page allocation data is wrong. Hence Jeff's recommendation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sanjivvinaik SSC-Addicted Points: 434 More actions · Answer 6

Thanks Gail... logicinside22 please use Jeff's recommendation. If it fixes great else use other steps.

Source: I fixed similar issue in SQL 2012 for DB participating in AG.

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 7

Thanks Jeff and Everyone for kind suggestions.

here some more details.

Full Backup - Every night

Index Rebuild Every night

T_Log - Every 3 hr between 5am to 8Pm

Recovery -Full

File Intial Size 10092 MB , Autogrowth 20MB , Unlimited

It keep going negative again and again like end of every week.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jeff Moden (5/30/2014)
To fix the negative space available thing, try running the following in the database you're having problems with...
DBCC UPDATEUSAGE (0);

Because there are bugs in SQL 2000's free space tracking, resulting in the 'negative' space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass