The transaction log for database 'DB' is full.

arul · 2013-05-21

Hi,
I am using nav 2009sp1 classic
I created new Database for Test.
And i restored the Live Db Backup.
While doing that i m getting the following error.

The following SQL Server error or errors occurred when accessing the Change Log Entry table:

9002,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

SQL:
INSERT INTO "TEST_DB_210513"."dbo"."NAV 9 16-12-10$Change Log Entry" WITH (REPEATABLEREAD) ("Entry No_","Date and Time","Time","User ID","Table No_","Field No_","Type of Change","Old Value","New Value","Primary Key","Primary Key Field 1 No_","Primary Key Field 1 Value","Primary Key Field 2 No_","Primary Key Field 2 Value","Primary Key Field 3 No_","Primary Key Field 3 Value") VALUES (6966575,{ts '2012-04-05 07:40:53.907'},{ts '1754-01-01 13:10:53.907'},'MURTHY',27,7384,0,'','Yes','No.=92016-27-04/13-05',1,'92016-27-04/13-05',0,'',0,'')

Marije_Brummel · 2013-05-21

Have you used google?

This is a common thing to fix. Expand the SQL Transaction Log

bbrown · 2013-05-21

If you used SQL to backup live and restore as a test DB, you wouldn't have this problem in the first place. It would also likely be much faster.

arul · 2013-08-07

Hi Brown
The transaction log issue was resloved
Through SQL Query is shrinked it
Query is :

GO
ALTER DATABASE [DB NAME] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE("LOG FILE NAME",1)
ALTER DATABASE [DB NAME] SET RECOVERY FULL WITH NO_WAIT
GO \:D/

One of my friend suggested this ....thanks Saurav =D>

bbrown · 2013-08-08

I still say it would have been easier to use SQL backup/restore. Also a Shrink is not the proper solution to a "log full" message.

arul · 2013-08-08

Hi brown
But i am new to technical ,not aware much about that .
that's why i gone through with this idea.
If possible guide me in brief so that i could avoid this in future

Thanks in advance

bbrown · 2013-08-09

You should size your transaction log so it does not grow between transaction log backups. If it is growing, you should adjust by either increasign the size of the log or the frequency of the backups.

arul · 2013-08-09

hi,
Yes i will do the same .
But brown apart that i have major issue ,in detail of the issue
kindly click the below link,
viewtopic.php?f=23&t=58590

The transaction log for database 'DB' is full.

Comments

Categories