June 22, 2009 at 3:46 pm
Hello,
I am trying to insert 489 records in Access from one linked SQL 2005 table to another and get the following message:
[ODBC--insert on a linked table 'dbo_WKORDER' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'GBAWork' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (#9002)
I tried a tlog backup, which has worked in the past to truncate the log, but it does no good this time and I can't find the table that includes the log_reuse_wait_desc column.
Can someone guide me in the right direction?
Thanks...
June 22, 2009 at 3:53 pm
nscott (6/22/2009)
Hello,I am trying to insert 489 records in Access from one linked SQL 2005 table to another and get the following message:
[ODBC--insert on a linked table 'dbo_WKORDER' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'GBAWork' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (#9002)
I tried a tlog backup, which has worked in the past to truncate the log, but it does no good this time and I can't find the table that includes the log_reuse_wait_desc column.
Can someone guide me in the right direction?
Thanks...
Read the error message, it tells you what table to look in for the information. You want to query sys.databases.
June 22, 2009 at 4:02 pm
Thanks, I did read the error message and subsequently can't find the table it is referring to - I looked under "System Databases" and under the System Tables for the specific database. Should I be able to see and open the table like standard tables or do I have to query it sight unseen? Forgive me, I am a SQL novice - specifics are appreciated!
June 22, 2009 at 4:16 pm
Run this: select name, log_reuse_wait_desc from sys.databases
June 22, 2009 at 4:18 pm
You won't find it under tables, you will find it under System Views.
June 23, 2009 at 7:17 am
Thanks for your help! The field in question contains a value of "LOG_BACKUP". As mentioned above, I have performed full backups of the transaction log, with no effect. Do I need to manually truncate the log or something?
As a sidenote, many of our databases contain this same value - is this in itself a problem or a symptom of backups being run incompletely?
June 23, 2009 at 7:22 am
nscott (6/23/2009)
Thanks for your help! The field in question contains a value of "LOG_BACKUP". As mentioned above, I have performed full backups of the transaction log, with no effect. Do I need to manually truncate the log or something?As a sidenote, many of our databases contain this same value - is this in itself a problem or a symptom of backups being run incompletely?
Can you again check that column's value in sys.databases for your database. What else is preventing the log size to be truncated? If it's backup log, backing up the log resolves this error..
June 23, 2009 at 7:32 am
I double-checked it and "LOG_BACKUP" is all it says. I expanded the column as much as possible. Are there any other fields to that table that would help? Thanks...
June 23, 2009 at 7:38 am
i just hope u're checking for your database
select log_wait_reuse_desc from sys.databases where name='MyDBName'
If it's not a production server, try putting the recovery mode of your database to SIMPLE and then back to FULL..
In my opinion, Backup Log should resolve the error....:Whistling:
June 23, 2009 at 7:46 am
SQL Server tables linked to MS Access get name "SchemaName_TableName". You get ODBC error for table name "dbo_WKORDER" what usually points to table "WKORDER" in schema "dbo"
Please execute this statement which searches for both names in all databases:
DECLARE @table_name NVARCHAR(128)
DECLARE @sql NVARCHAR(4000)
SELECT @table_name = 'dbo_WKORDER'
SELECT @sql = 'IF EXISTS (SELECT TOP(1) 1 FROM ?.sys.objects WHERE name = ' + QUOTENAME(@table_Name, '''') + ')
SELECT ''?'' DB, * FROM ?.sys.objects WHERE name = ' + QUOTENAME(@table_Name, '''')
EXECUTE sp_MSforeachdb @sql
SELECT @table_name = 'WKORDER'
SELECT @sql = 'IF EXISTS (SELECT TOP(1) 1 FROM ?.sys.objects WHERE name = ' + QUOTENAME(@table_Name, '''') + ')
SELECT ''?'' DB, * FROM ?.sys.objects WHERE name = ' + QUOTENAME(@table_Name, '''')
EXECUTE sp_MSforeachdb @sql
June 23, 2009 at 7:56 am
nscott (6/23/2009)
Thanks for your help! The field in question contains a value of "LOG_BACKUP". As mentioned above, I have performed full backups of the transaction log, with no effect.
Full backups or transaction log backups? What command, precisely, are you running to take that backup?
Are you sure that the backup is succeeding?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2009 at 3:36 pm
I have run both recently, and have repeated the tlog backup today with the option to truncate the log checked on. I run them from the context menu. The ldf is just under 200MB, while the mdf itself is only 160MB. Do I need to run a shrinkfile command or something to get it down to a specific size? I'm only trying to add 489 records! Thanks...
June 23, 2009 at 7:09 pm
nscott (6/23/2009)
I have run both recently, and have repeated the tlog backup today with the option to truncate the log checked on. I run them from the context menu. The ldf is just under 200MB, while the mdf itself is only 160MB. Do I need to run a shrinkfile command or something to get it down to a specific size? I'm only trying to add 489 records! Thanks...
200MB log file is just fine. you dont need to shrink it. Dont you have free space on the disk?
Also can u check if auto growth for the log is enabled or not?
run this command and see how much space is available in the log file for your database.
DBCC SQLPERF(logspace)
June 24, 2009 at 8:13 am
200MB log file is just fine. you dont need to shrink it. Dont you have free space on the disk?
Also can u check if auto growth for the log is enabled or not?
run this command and see how much space is available in the log file for your database.
DBCC SQLPERF(logspace)
I have 400 GB of free disk space on the SQL server. For log properties, initial size = 195 MB; autogrowth = By 10 MB, restricted growth to 200 MB.
The logspace command returns: Log Size (MB) = 194.9297; Log Space Used (%) = 77.13519 (BUT 195/200 = 97.5%) ???
Some of our other databases have 1+ GB log files with unrestricted growth - others have varying degrees of restricted growth.
Should I set all my log files to unrestricted and rely on regular tlog backups to truncate them?
Thanks!
June 24, 2009 at 9:02 am
nscott (6/24/2009)
The logspace command returns: Log Size (MB) = 194.9297; Log Space Used (%) = 77.13519 (BUT 195/200 = 97.5%) ???
194 is the size of the file on disk. 77% used means that the portion of the log file that's in use is around 150MB
Why 77% is in use, I don't know. It may be that you need to make your log backups more frequent if you don't want that much space to be used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply