(SQL2000) Management - SQL Server Logs - Current, question 1

[LucieLastic]

hi All

On our development server, one of the programmers got the error returned in his code (from the db):

"Could not allocate space for <...> in database .. because PRIMARY file group is full."

I believe this was caused by a full transaction log, is that right? I tried to look at the current log in SQL Server Logs but impossible as it's size was in excess of 7,000,000 Bytes. Is there any way of looking at this log in a raw form, eg as a text file, rather than thru Enterprise Manager?

I stopped and restarted SQL Server to force it to create a new 'Current' log but I would like to see what's in the other log, though.

Many thanks for help,
lou

 

[SQLBill]

Instead of stopping and restarting services, run the command: sp_cycle_errorlog. That will create a new log.

To read the text file of the error log, first go to Enterprise Manager, open the current error log. You will see a line that says: Logging SQL Server messages in file....

That will tell you the location of the error log text file. Go there and open the file in NotePad.

-SQLBill

 

[SQLBill]

By the way, that sounds like your .mdf is full. Is your database files set to autogrow? If so, are you allowing them to grow large enough?

-SQLBill

 

[LucieLastic]

hi SQLBill

Thanks for the replies. I did increase the trans log size and have just increased the database size. We've set the sizes to be fixed, that is, no auto increase options, as we want to monitor how big it's going to get whilst it's being developed. We have the recovery model set to 'Simple', too, at the moment whilst it's in it's infancy.

Thanks for the useful log tip, too, have made a note of that.

lou

 

[SQLBill]

Just remember, since you aren't allowing autogrowth, if you have a large transaction or your database grows quickly you could have a problem. If either one 'bumps' against the max size that you've allowed then any jobs/transactions will fail. You will then need to manually grow the db and then redo the transactions.

-SQLBill

 

[JennyPeters]

Bill,

Will the jobs/transactions fail, or simply overwrite the oldest ones?

Thanks,

Jenny

 

[SQLBill]

Fail.

In simple mode checkpoints are used. These are NEVER issued until a transaction is complete. Then eventually, SQL Server can reclaim the transaction log space up to checkpoints. But until a checkpoint is issued, the log needs room to grow.

Check the BOL for more information on Transaction Logs.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875