SQL Server 2005: Error 9002 - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Server 2005: Error 9002

Author

Topic

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2008-01-21 : 17:31:55

I am getting this error in my SQL Server 2005 server.
It is saying that one of my databases transaction log file is full, but I am a little confused.

I googled this the other day and found a threat here, but I was having problems with the search function. I wasn't getting anything back. Hate to repost.

Anyway, the DB is in simple recovery mode. The disk that it is on has plenty of space; 80+gigs.

I cannot recall the T-SQL command to be able to query of sys.databases to find infor on 'log_reuse_wait_desc' (I am really new to SQL).

Does anyone have some suggestions and help? Can someone help me with the t-sql command to use against sys.databases?

Thanks.

JW

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-21 : 17:36:56

If the database is on Simple recovery mode,just shrink the tran log files.

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2008-01-21 : 17:41:19

quote:
Originally posted by sodeep

If the database is on Simple recovery mode,just shrink the tran log files.

Ok. I'll google that.

But, can anyone tell me how to query sys.databases? YOu get information such as:

http://msdn2.microsoft.com/en-us/library/ms345414.aspx

Was trying to recall how to do that?

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-21 : 17:47:29

Check this one :http://msdn2.microsoft.com/en-us/library/ms178534.aspx

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2008-01-21 : 17:52:43

quote:
Originally posted by sodeep

Check this one :http://msdn2.microsoft.com/en-us/library/ms178534.aspx

Ya...can you help with how to correctly enter the t-sql query, so I can see this information? I remember seeing a article on it, but I can not remember how to do it.

Appreciate it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-01-21 : 17:55:11

SELECT log_reuse_wait_desc, [name]
FROM sys.databases

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2008-01-21 : 18:02:22

quote:
Originally posted by sodeep

SELECT log_reuse_wait_desc, [name]
FROM sys.databases

Thanks.

Interesting results:

log_reuse_wait_desc
------------------------------------------------------------
NOTHING
ACTIVE_TRANSACTION
NOTHING
ACTIVE_TRANSACTION
NOTHING
NOTHING
NOTHING
ACTIVE_TRANSACTION

(8 row(s) affected)

That is from the DB having the error.
Pretty new to this, so not sure what im looking for. Any suggestions?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-21 : 18:10:19

Following will clean up log space:

backup log db_name with truncate_only

You need to find out db recovery model and set log backup job properly.

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2008-01-21 : 18:12:47

quote:
Originally posted by rmiao

Following will clean up log space:

backup log db_name with truncate_only

You need to find out db recovery model and set log backup job properly.

Thanks. I'll give that a shot.
Recovery mode for this DB is simple mode.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-21 : 18:23:57

Then did you let log file auto grow?

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2008-01-21 : 18:27:57

quote:
Originally posted by rmiao

Then did you let log file auto grow?

Hmm. Just noticed something after reading your comment.
Looking at the properties for this DB, for autogrowth, it is set to:

"By 10 percent, restricted growth to 500mb."

Looking at the file, it is almost at 500mb.

Should it be like that? Should I change it?

For the t-sql command, just execute a simple:

backup log <dbname> with truncate_only

That it?

Thx

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-21 : 18:49:05

i'll not restrict to max size....ever. and 500 MB is pretty small...

if disk space is an issue, time to buy more disks, but as u said, it isnt

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-21 : 19:49:52

You can schedule a sql job to run 'backup log <dbname> with truncate_only', but doesn't help if has big transaction in db.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources