Transaction Log is Full - 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)

Transaction Log is Full

Author

Topic

dboiler1
Starting Member

39 Posts

Posted - 2007-08-15 : 09:20:56

I was trying to create a table on a Database and it gave me this message:

'tblSuggestion' table - Unable to create table. The transaction log for 'Database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

And I don't see the log_reuse_wait_desc column anywhere.

Any ideas?

dave

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 09:38:25

ALTER DATABASE DB_NAME SET RECOVERY SIMPLE

CHECKPOINT

BACKUP LOG DB_NAME WITH NO_LOG

DBCC SHRINKFILE (LOG_FILE_NAME, 1024)

/*1024 is file size you want to shrink the log file to you can select any*/

ALTER DATABASE DB_NAME SET RECOVERY FULL ---(OPTIONAL)

Ashley Rhodes

dboiler1
Starting Member

39 Posts

Posted - 2007-08-15 : 14:49:40

Am able to add tables now. Thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-15 : 22:19:51

You should backup log periodically if the db is not in simple recovery model.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources