Datafile full while db is in unlimited file growth mode

[Kalin]

Hi all,

I have a SQL-Server database (size +/-5 GB) which filled up completely, to my suprise because this database had unlimited filegrowth enabled and the disk wasn't full. After creating a second datafile the database continued to operate normally.
Any thoughs on what may have caused this eradic behavior are welcome.

Grtz,

Kalin

 

[SQLBill]

You really have to look at what you've been doing with the database.

Have you had large transactions?

Have you done any database maintenance, such as reindexing/defragging indexes?

-SQLBill

Posting advice: FAQ481-4875

 

[DrSql]

You might want do a shirnk on the DB.
From EM right click on the db and see what is the total size of the db and sapce available.

Try the follwoing steps to your db.
[tt]
use mydb
DBCC SHRINKDATABASE (mydb, 10)
GO
use mydb
select name from sysfiles
go
GO
DBCC SHRINKFILE (NameOFDataFile, 0)
GO
DBCC SHRINKFILE (NameOfLogFile, 0)
GO

[/tt]

Perform this tsaks at an off peak time and with single user mode for better results. Back up your db prior to this.



Dr.Sql
Good Luck.

 

[Kalin]

SQLBill:
There were no dba tasks performed. Thus no reindexing no maintenance or defragging. There were no large transactions performed.

Furthermore nothing different from the normal operations where carried out on the db.

Dr.Sql:
datafile 4896Mb
Logfile ~1200Mb
Space available 0 Mb



Grtz,

Kalin

 

[Catadmin]

Kalin,

First, it might be the TEMP DB that was running out to space instead of your user DB. Check that to see if it's got a limit on its file growth.

Second: Just out of curiosity, did you check the hard drive's recycle bin for a large volume of objects that had been "deleted" but not emptied?

Windows supposedly frees up space when you delete things, but in reality, the OS marks those files as free to be deleted (or tombstoned) and sticks them in the recycle bin. The problem is, if you don't clear out the recycle bin, those files will never get overwritten and continue to take up HD space even though the space they're on is marked as free.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[k108]

Ok, this may seem too obvious, but would the autogrowth % have been too big for the existing free disk space?

That is, if it autogrew, would it have taken up more than the available disk space, given the autogrowth %?