Database size

Question

Database size

Sjey

SSC-Addicted

Points: 468
More actions
January 27, 2005 at 2:16 pm

#87380

I have SQL Server 2000 loaded on my local drive. I have a database ABC 4 GB in size – the space allocated for this database is 3786MB and 1000 MB for the transaction log. I do not have any other databases other than those that originally came with MS SQL Server. When I try to import data into SQL using DTS or when I try to create an index, the task fails with a message saying that there is insufficient space on PRIMARY filegroup. I have 43GB of free space on my local hard drive. I have the file properties Automatically grow file and Unrestricted file growth checked on the Data Files tab of the properties of the database ABC. Any ideas on what I need to do to expand the database?

Thank you

Viewing 15 posts - 1 through 15 (of 19 total)

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

sa24 SSCrazy Eights Points: 8027 More actions · Answer 1

What is the growth increment set ? Also, can you just try to increase the size of the data file to 5 GB and try importing data ?

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 2

Also check the file growth properties on your tempdb.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Sjey SSC-Addicted Points: 468 More actions · Answer 3

I tried changing the size of the data file - unable to change. The growth increment is set to 15%

Sjey SSC-Addicted Points: 468 More actions · Answer 4

The file size of tempdb is only 253 MB and is set to grow by 10% with unrestricted file growth.

Jason Hall Hall of Fame Points: 3906 More actions · Answer 5

You said that you were unable to change the size of the data file. This is not good and most likely the same problem that is causing the DTS failure. What error message is generated when you try to change the size of the data file.

jarretg SSCommitted Points: 1706 More actions · Answer 6

Sheba,

What OS are you running and what file system is your local drive, NTFS or FAT32?

Jarret

Sjey SSC-Addicted Points: 468 More actions · Answer 7

Sjey

SSC-Addicted

Points: 468

January 28, 2005 at 9:14 am

#539552

Windows XP & FAT32

Sjey SSC-Addicted Points: 468 More actions · Answer 8

Microsoft SQL - DMO (ODBC SQL State: 42000)

Error 5149: MODIFY FILE encountered operating system error 112 (There is not enough space on the disk) while attempting to expand the physical file.

jarretg SSCommitted Points: 1706 More actions · Answer 9

The problem is with your file system, the maximum file size on a FAT32 formatted partition is 4GB.

Check this out: http://www.experts-exchange.com/Operating_Systems/MSDOS/Q_20343072.html

My suggestion is to add additional files (less than 4GB max size) to your PRIMARY filegroup in your database and then try your DTS import again. Either that, or you could always format your hard drive with NTFS.

See ALTER DATABASE in BOL for how to add files to a database.

Hope this helps.

Jarret

Jason Hall Hall of Fame Points: 3906 More actions · Answer 10

Is there a reason that you are using a FAT32 filesystem. I would recommend backing up your files and reformatting the drive if possible. Even if you break your database up into multiple filegroups, the maximum partition size of 4GB will still come back to bite you as your data grows.

Sjey SSC-Addicted Points: 468 More actions · Answer 11

Thanks You all for your valuable advice.

Thank you

Dejan Zivanovic SSC Rookie Points: 38 More actions · Answer 12

Do not FORMAT partitition!!!

Run Windows in Safe mode (press fast F8 in first moments of boot)

Start - Run - 'type' cmd

use command: convert c: /FS:NTFS

'(if your *mdf file is on partition c: )'

Before, you could perform check disk (chkdsk c: /r /p)

Good luck, but I think that will 99,99% work

Sql DBA SSC Enthusiast Points: 199 More actions · Answer 13

> Either that, or you could always format your hard drive with NTFS.

An alternative one might consider might be convert. For example:

CONVERT C: /FS:NTFS

HTH

Sjey SSC-Addicted Points: 468 More actions · Answer 14

I am trying to alter my database with the following command:

ALTER DATABASE Claims

ADD FILE

( NAME = Claims_dat2,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER2000\Data\Claims2.mdf',

SIZE = 5GB,

MAXSIZE = 10GB,

FILEGROWTH = 1 GB)

TO FILEGROUP PRIMARY

This is the error message I get:

Server: Msg 156, Level 15, State 1, Line 8

Incorrect syntax near the keyword 'PRIMARY'.

What is wrong with the syntax? Please help.

Thanks