I am developing this site http://www.onlineacademicadvisor.com and having DB problems for the 3rd time in a row.
Whenever the traffic on the site is getting bigger, the transaction log becomes full and no user can login. This problem is described at http://support.microsoft.com/kb/317375 From there, I got the feeling that the problem occurrs if transactions are not committed and last for too long.
However, I do not have any explicit transactions, just the usual select, insert, update statements in stored procedures. I do not call COMMIT (or RETURN) explicitly at the end of my stored procedures though. My stored procedures are short.
Have you got any ideas about what can cause the problem? I really have not idea what that could be.
Over the past few days we noticed severe performance issues on some of our more complicated queries. I ran a DBCC ShowContig on the problematic tables, and noted that the Logical Scan Fragmentation was very high, like over 90%. I ran a DBCC DBREINDEX on the tables, the Logical Scan Fragmentation reduced down to between 0% and 10%, and the queries ran instantly.
However...the next day, the queries were causing problems again. Running ShowContig showed the fragmentation was up to over 90% again. Now, these are very static tables I'm dealing with...absolutely no UPDATE, INSERT or DELETE commands have been run against them (we import the data once a month). I set up a job to monitor the state of the index fragmentation overnight. All is well until 0100, when the LSF hits 90% again. I can't figure out what could be causing this, we have no jobs that run on, or affect, this database overnight, except the backup, which runs at 2100. Has anyone experienced anything like this before, or does SQL Server do something on the fly that could cause it to happen?
I have a transaction log which is 1 Gb and only has about 40 Mb free. When I run DBCC loginfo I find the first active log dates back to the middle of August. Does any one have any suggestions on the best way to approach this situation. How can I query the transaction log to find out what the old transactions are? I was going to use the detach database, rename log, attach database to shrink the log but don;t want to do this incase there is active data in the log. We are doing hourly log dunps. Thanks Grant
Hi all I found that my transaction log is beig filled up and I tried to truncate it from DB properties window and I failed. I want to know how to truncate it and how to prevent it from growing up untill it's filled Thanks Mohamed
Our transaction log filled up the hard drive on the server and we are looking for a quick and easy way to delete it. Any ideas? We can't easily back it up because it is over 10500 meg in size.
I need help fast. I have a SQL Server 6.5 SP2 database that is 5000 MB and the transaction log is 1000 MB the log is 98 % full but will not truncate. I have tried all the truncate commands (Truncate only, no_log, DBCC) but it will only trucate the log like 10 MB. Dose anyone know how to truncate this log??
I have a problem with the transaction log of a database. when i truncate it and check the properties if says there are 0 MB available even though it is allocated 300 MB. this seems to be causing alot of problems. Any idea why the log will not truncate??
I am getting an error when I try to DTS data to a database that the transaction log is full. I do: backup transaction db_name with truncate_only, but I still get the error. Any ideas on how to resolve this are appreciated. Kellie
I'm still fairly new to SQL server and I've just ran into a problem when dropping some tables from my DB as my transaction log has become full. I looked at books online and it basically said to backup the log so it will also truncate the log. However I've ran out of disk space on my data drive so can't take a backup.
Is there a way to just truncate the active log to shrink the size?
Today, one of our backups failed due to the transaction log being full. I expanded the transaction log and then the backup completed normally. For a short term resolution, I increased the maximum allowed size on our transaction log files.
We are doing a database backup only as follows: backup database xxxxx to xxxx_data with init
I did find a note stating that the transaction log will eventually become full if doing a transaction log backup only. We do not generate a lot of transactions so I had not seen a need to backup the log during the day. The full database backup is done daily.
My question is, in order to permanently resolve this error do I have to: - take a transaction log backup as well. Should I take it take right after the full database backup or at some later point. - do I have to schedule/run the shrink database command.
iHi everyone I have a big transaction log took over all the disk space, I know I can backup the log to free the space but my backup drive is on the same drive. Is there any other way to clean the Transaction log without backing up?
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.
At the start, transaction log size is 1MB. at the middle of the dts it reached to nearly 2 GB. I encountered this error in DTS:
OnError,,,CallerType, CallerChannel, Dealer, DODealer, HotlineType, Model, Reg'l Signal Code, Account, Contact,,,10/22/2007 10:55:43 AM,10/22/2007 10:55:43 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The transaction log for database 'DB1' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".
I visited the sys.database's log_reuse_wait_desc and it is set to checkpoint.
The transaction log keeps getting full in the middle of the dts, not enough to do the 1 round execution of the dts. What to do with this one? Anything that i need to add in my dts to truncate the logs?
Well, when I start thinking: I got it all, I run into a new problem. I'm trying to execute a package that contains several (7) sequence containers each of them deleting all the records from 7 tables and inserting new rows.
I get the following error:
The transaction log for database 'MyDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hi All, any advice or help greatly appreciated, I need to Process Dimensions and Cubes Overnight, what is the best and most reliable way of achieving this.
System.Data.SqlClient.SqlException: The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases I have my website on a web hotel and I only have limited access to my database. How can I solve this for now, and the future??
Has anyone ever run across you Tempdb Transaction Log being full and getting an error 9002 severity 17 state 2? One issue is the tempdb was created with all the defaults 1mb in size. As well as my transaction log is now at 4 GIG in size.
when i try to delete the records it gives the errror suggest me how to deal with this problem ===== Msg 1105, Level 17, State 2 Can't allocate space for object 'Syslogs' in database 'armaster' because the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
I have a process that failed with the following error message. The SQL server error messages also said the Transaction log was full and there was not enough disc space.
Strangely, when I checked all the drives on the server, there was plenty of free space, the smallest amount free on one drive, where SQL server is located was 20GB. I am confused.
I backed up the DB to another server and deleted a lot of the transaction logs and now the drive has 30GB and is okay.
Does anyone know how the transaction log fills up? Can I change a setting somewhere to increase the maximum size of the transaction log? Or maybe change the location where it is saved to another drive? Or have it automatically cleared out every once in a while?
Any of your input is greatly appreciated.
Thanks.
Rodney
-------------------------------------------------------------------------- DS-DBMS-E400: UDA driver reported the following on connection 'Data Target (ODBC)': DMS-E-DBPARSER, The underlying database detected an error during processing of the SQL request. [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'ancosalesdm' is full. Back up the transaction log for the database to free up some log space. (for details, see Build_SAL_FA_ShipSKU_0584.log) [PROGRESS - 00:32:42] Build Node 68 'SAL_FA_ShipSKU'; failed DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74': DMS-E-GENERAL, A general exception has occurred during operation 'execute immediate'. The log file for database 'ancosalesdm' is full. Back up the transaction log for the database to free up some log space. DMS-E-GENERAL, A general exception has occurred during operation 'execute immediate'. General SQL Server error: Check messages from the SQL Server. DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74': DMS-E-GENERAL, A general exception has occurred during operation 'rollback transaction'. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. DMS-E-GENERAL, A general exception has occurred during operation 'rollback transaction'. General SQL Server error: Check messages from the SQL Server. --------------------------------------------------------------------------
I am running a website with a SQL Server database attached.My transaction logs are full and my hosting co. won't allocate moredisk space for me.I need to delete my database transaction logs and asume I will need torun an SQL script to do this.Problem: I do not have MS Enterprise Manager of any database utilityon my website apart from MS Access. Where can I download a free SQLtool that I can use to delete the transaction logs from my database.Any help appreciated.ThanksFrancois Terblancheverismall.com
Hi All,My SQL server transaction log is getting bigger every day and my HDD ifrunning out of space.So i follow the MS KB about how to Shrinking the Transaction Log.After doing so the log is much much smaller as i can see the size of itunder enterprise manager.The problem is that the HDD still shows the same size.If i shrink the DB why the and reduce its size why the HDD does notshows it?Is there a way to clear the size from the HDD?Thanks All
I have set the initial size of the log file for a database to 1M, themaximum size is unrestricted, and the increase rate is 10%.However, when I attempt to delete thousands of rows, the error is stillreported that the transaction log file is full. Why can't the log fileincrease automatically?*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
During the day I frequently use watch the current activity window under Enterprise Manager to see who is doing what and when.
However, overnight there are various users running various jobs that I am not always informed about. Wactcing the current activity isn't an option here.
Does anyone have a job that I could periodically run overnight to perfrom the same function as the current activty box? Which system tables does the current activity functionality use?
Thanks. Any information that can be provided here will be appreciated.
I am using SQL Server 2000, when I do a backup I use the database maintenance plan at enterprise manager. I select my database and then I schedule the complete backup and transaction log backup to "everyday at 22:00:00" My question is, do I really need to do the transaction log backup? If I am not wrong with the complete backup I can already recover all my data, the transaction log backup is useless if done at the same time that I do the complete backup, right?
I always get this message, when i want to run a stonger query or a transaction that takes a longer time:
"The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
I checked the log_reuse_wait_desc column: LOG_BACKUP I ran tr log backup...nothing... I tried to set to simple reco mode the db...this helped... temporaly...i got again below message. ( i wouldn't like to set to simple mode the db because the size of db is 160GB now....so i don't want to eun a fullbackup)
TempDB size is 50MB now and it can grow until 7GB. The trqansaction log size is 14GB and there are 50GB free space, so it can grow.
This symptom occurs since i installed SP2 and the CP Rev.4.0
Our testing server experienced a timeout exception when execute System.Data.SqlClient.SqlTransaction.Commit() in SQL Server 2000 thru .Net Framework 1.1. And this happened at 2007-02-13 18:07:05,954. It was strange to us that all the insert statement can be executed without error within the transaction but the commit operation fails. Moreover, after about 40 minutes, we found that the transaction log of this database is full.
Here is the exception and SQL Server 2000 Error Log: Exception Stack Trace: DateTime: 2007-02-13 18:07:05,954 Req Id: bccdae08-cc47-4f85-8f48-5f0b9dbbbf88 Exception: MyDatabaseException Detail: MyDatabaseException: Index #0 Server: MySQLServer Source: .Net SqlClient Data Provider Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. LineNumber: 0 Procedure: ConnectionRead (recv()). State: 0 Error Number: -2 ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected) at System.Data.SqlClient.TdsParser.ReadBuffer() at System.Data.SqlClient.TdsParser.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream) at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String sqlBatch, String method) at System.Data.SqlClient.SqlConnection.ExecuteTransaction(String sqlBatch, String method) at System.Data.SqlClient.SqlTransaction.Commit()
SQL Server Error Log:
2007-02-13 13:02:23.72 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944769, first LSN: 9434:22326:1, last LSN: 9434:22360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:MSSQLBackupMyDatabaseName.bak'}). 2007-02-13 15:50:52.40 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944970, first LSN: 9442:38096:1, last LSN: 9443:748:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'Legato#4f96edfd-7fdb-4cd1-a740-3fe9a54d66c6'}). 2007-02-13 18:48:51.42 spid66 Error: 9002, Severity: 17, State: 62007-02-13 18:48:51.42 spid66 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space.. 2007-02-13 18:52:40.61 spid58 Error: 9002, Severity: 17, State: 6 2007-02-13 18:52:40.61 spid58 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space.. 2007-02-13 18:53:22.69 spid61 Error: 9002, Severity: 17, State: 6 2007-02-13 18:53:22.69 spid61 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space.. 2007-02-13 18:54:01.11 spid57 Error: 9002, Severity: 17, State: 6 2007-02-13 18:54:01.11 spid57 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
Are these 2 things related? Would a full transaction log cause a commit operation fail?
