April 14, 2003 at 9:09 am
Hi,
I have a SQL table with approx 18,000 records in it. I would like to add two addition bit fields (NO NULLS)and default zero. However, if I try to save my changes it gives me an error !!!
/*
14 April 2003 16:04:10
User:
Server: DEV1
Database: Omega_UK
Application: MS SQLEM - Data Tools
*/
'tblSector' table saved successfully
'tblSource' table saved successfully
'tblCountry' table saved successfully
'tblAddress' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Tmp_tblAddress' in database 'Omega_UK' because the 'PRIMARY' filegroup is full.
What can I do ?
Many thanks
Mike
April 14, 2003 at 9:12 am
HI,
in Enterprise Manager right click on the database, choose properties, select the data files tab and see if the automatically grow file option is checked. You might then specify how SQL Server will grow the file in percent or in megabyte
Cheers,
Frank
Edited by - a5xo3z1 on 04/14/2003 09:15:39 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 14, 2003 at 9:17 am
Review your SQL Server errorlog for any errors. How large you TEMPDB is? You may have to allocate more spaces for it. Check you database transaction log file too.
Edited by - Allen_Cui on 04/14/2003 09:17:44 AM
April 14, 2003 at 9:43 am
Hi Allen & Frank,
Many thanks for your prompt reply greatly appreciated.
My database is 25MB and tran log 1 MB.
My tempdb is 14MB.
I allocated 30MB to the database, now it says my transaction log is not big enough! Should I increase this too ?
The settings of the database are to grow by 10% with no restrictions on size !
Why is it complaining even with these settings ?
I plan to import another 18,000 contacts into the system, am I best to increase these two sizes to a more realistic size ?
Thanks again.
Mike
quote:
Review your SQL Server errorlog for any errors. How large you TEMPDB is? You may have to allocate more spaces for it. Check you database transaction log file too.Edited by - Allen_Cui on 04/14/2003 09:17:44 AM
April 15, 2003 at 12:06 am
hmmmm...
tempdb size seems ok (mine is right now about 8MB with no problems at all). temp db and its log can grow by 10 percent.
can your transaction log also grow to unlimited size? I think 1MB might not be enough to import 18.000 records. I don't restrict size on log files. All I have done is to set database option "Auto shrink" (right click database, properties, Options tab). With this configuration I have not experienced any trouble although I frequently move many records from here to there.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 15, 2003 at 6:07 am
The tran log can also grow by the same portion 10%.
This is probably a stupid question but I will ask it anyway ! Is their a starting size of database when designing the structure i.e. db 30MB & log 30 MB ? You will probaly respond with it depends on what you are going to do with it !
My structure is basically a CRM type layout with 40,000 companies and contacts.
Thanks from a newish ! SQL user
Mike
quote:
hmmmm...tempdb size seems ok (mine is right now about 8MB with no problems at all). temp db and its log can grow by 10 percent.
can your transaction log also grow to unlimited size? I think 1MB might not be enough to import 18.000 records. I don't restrict size on log files. All I have done is to set database option "Auto shrink" (right click database, properties, Options tab). With this configuration I have not experienced any trouble although I frequently move many records from here to there.
Cheers,
Frank
April 15, 2003 at 6:28 am
You can easily check this, if you create a new db. By default there is automatically grow checked and unrestricted. If you don't change default initial size, it is set to 1 MB. So, your db and tempdb can grow and are not restricted. Hmmm..have you set up a maintainance plan, are you backing up log files? Maybe you take a look at "Truncating the Transaction Log" in BOL and perhaps get familiar with the DBCC tools
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 15, 2003 at 7:35 am
Hi Frank,
I use the following statements when I import a large number of contacts:
DBCC SHRINKDATABASE('Omega_UK')
GO
BACKUP TRAN Omega_UK WITH TRUNCATE_ONLY
GO
Is this what you mean ?
Thanks
Mike
April 15, 2003 at 8:37 am
Yes, that's what I mean.
I do a full backup twice a day with log backup every hour between 8 am and 8pm
Do you mean BACKUP LOG instead of BACKUP TRAN?
Are you using this statements right after import?
...If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files. At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records. The process of deleting these log records to reduce the size of the logical log is called truncating the log.
The active portion of the transaction log can never be truncated. The active portion of the log is the part of the log needed to recover the database at any time, so must have the log images needed to roll back all incomplete transactions. It must always be present in the database in case the server fails because it will be required to recover the database when the server is restarted. The record at the start of the active portion of the log is identified by the minimum recovery log sequence number (MinLSN).... from BOL
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 15, 2003 at 8:50 am
I mean BACKUP TRAN ?
SHould I be using BACKUP LOG ?
Do you have an automated Job defined to do yours ?
When you say backup I presume you do not mean to DAT tape !
Does it backup the database to a temporary database ?
Now my database is running and is actively used by 20+ users what sort of maintenance plan should I be looking at ?
Thanks once again.
Mike
April 16, 2003 at 1:38 am
hi,
sorry to answer your question so late. I was busy.
I have set up a maintenance plan using the SQL wizard The following happens
- backup up all user db's
- no optimizations
- integrity check, including indexes
- backup up db and trans logs as part of maintenance plan, verify integrity upon completion
- save to disk, using a subdirectory for each db. (this happens right before our company backup solution gets to work every night. It backs up the disk
- remove files older than 2 days
- write report to file and email operator (me)
This might be not the best maintenance plan, but I haven't had any problems ever since I've set it up.
I do db backups twice a days and trans log backup every hour. If your db is frequently used and data is changed frequently, you SHOULD backup translogs more often. There should be no problem doing this with logged in users.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 17, 2003 at 8:39 am
The reason you might be getting the filegroup is full even though it set to autogrow is: whenever the filegroup has reached its max at that given point of time and when it aquires new extents it throws this message to warn.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply