Zero space available in DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Zero space available in DB

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2005-10-31 : 07:59:12

Hope this question is not too dumb for this forum.. but here goes.

I have a DB on SQL Server 2K which is the backend for our Great Plains System.

When I look at the properties of the Database from Enterprise Manager, It shows Size: 1370.57mb and space available: 0.00 The ZERO space available is what I'm concerned about. The DB is in FULL recovery mode with a nighly Full backup and transaction log backups throught the day. The DB is set to autogrow and the file growth is set at 20 percent.

My question is.. should I be worried about the ZERO space available and if so, how do I correct this??

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-31 : 08:26:19

this is too dumb for this forum... just kidding

well since you have it set to autogrow there's no worries if you have enough disk space.

Go with the flow & have fun! Else fight the flow

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2005-10-31 : 08:36:44

Tanks! :)

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-31 : 08:41:48

Hi,

Check the disk space you have in your DB server. To monitor the database space you can also create the job below and append the data in a database table or a file. This will help you to monitor the growth.

set nocount on
go
select left(sd.name,15) as 'DatabaseName',sum(sa.size *8/1024) as 'TotalSize (MB)' from master..sysaltfiles sa
join master..sysdatabases sd on sd.dbid = sa.dbid
where sa.dbid>4 and sd.name='<DB_NAME>' group by sd.name order by sd.name
Go
declare @SQLstring as nvarchar(50)
set @SQLstring = N'DBCC SHOWFILESTATS'
create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, Used_Space int, name1 varchar(25),NameofFile Varchar(200) )
use <DB_NAME>
insert into #DBSpace execute sp_executesql @SQLstring
select Name1,Used_Space/16 as 'Total Used Space (MB)' from #DBSpace
Drop table #DBSpace

Regards
Sachin Samuel

Kristen
Test

22859 Posts

Posted - 2005-10-31 : 13:42:56

I would change your File Growth from Percentage to a Fixed amount.

In my experience allocating a 100MB or more at a time takes SQL server quite a while - and if it happens at a busy time things tend to time-out.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources