I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log? ...
Our Test DB is suddenly missing rows. We want them back. Is there a way to sift through everything that has happened to the database today? Each SQL statement? I presume this kind of stuff is in the transaction log, but am not sure how to view it. Is there a way to undo delete operations? BTW: Yes, we do have a backup, but would prefe...
What does it mean that a Transaction Log is Full? I have it the file set to grow 20% when needed. I have 4GBs left on the drive. How do I solve this issue permanently? Running these commands solves the issue temporarily: DBCC SHRINKFILE('MyDatabase_log', 1) BACKUP LOG MyDatabase WITH TRUNCATE_ONLY DBCC SHRINKFILE('MyDatabase_log', 1) ...
How to truncate and shrink large log files in SQL Server 2005? How to apply truncation at regular intervals? Is there any difference between truncation and shrinking? Thanks in advance ...
Here's my scenario: I have a simple stored procedure that removes a specific set of rows from a table (we'll say about 30k rows), and then inserts about the same amount of rows. This generally should only take a few seconds; however, the table has a trigger on it that watches for inserts/deletes, and tries to mimic what happened to a l...
I'm trying to manage the size of a SQL Server 2008 log file. I have a reporting database that is loaded once a day. The Simple recovery model is the best fit as there are no transactions other than the morning load, and I can re-create those records. My goals are to have the transaction log at a fixed size, large enough that it doesn'...
Instead of placing triggers on tables everywhere in an Oracle database, is there a Java API that I can use to read transactions off the Oracle transaction log? My purpose is to be able to detect transactions going into a proprietary(vendor) database and react accordingly. We can't modify the database so that we do not void our maintenan...
I have a SQL Server 2005 database that is backed up nightly. There backup consists of: 1 FULL backup of the database. 1 backup of the transaction log. These are currently two separate jobs. The log is huge and I'd like to set things up so that: 1) the database is backed up in full nightly 2) the log is set such that I can recover the...
We have SQL Server 2005 database with full backup and transaction logs. We have a problem with the database - and need the SQL CSI Forensic team to help. Is there a way to look at the transaction logs and identify whether a stored procedure was executed? We know the time that it happened (if it happened) but there is a dispute wheth...
How do I increase the size of the transaction log? Is is also possible to temporarily increase the transaction log? Let's say I have the following scenario. I have a Delete operation that's too big for the current transaction log. I wan't to: Increase the transaction log (can I detect the current size?, can I tell how large I need th...
I've been looking all over the internet and I can't find an acceptable solution to my problem, I'm wondering if there even is a solution without a compromise... I'm not a DBA, but I'm a one man team working on a huge web site with no extra funding for extra bodies, so I'm doing the best I can. Our backup plan sucks, and I'm having a re...
I have a SQL job that simply executes a stored procedure. Each morning when the job attempts to run, I get the following error: The current transaction cannot be committed and cannot support operations that write to the log file. When I keep trying to restart the job, it keeps giving me that same error. However, if I just execute the...
Hello, I have multiple databases using merge replication with , @publication_compatibility_level = N'90RTM', @replicate_ddl = 1. I have a backup job on the publisher database that does a full backup nightly. I have another job that backs up the transaction log every 3 hours and runs DBCC SHRINKFILE (N'TheDatabaseName_Log' ) on the publ...
The problem is this: t0: Insert is made into my database t1: Delete is executed t2: Full backup is made t3: Transaction log backup is made How can i recover the deleted record after t3 (which is now)? I want the database in a state between t0 and t1. The log level was Full. Edit: I have already run DBCC LOG(DB-Name, 3), but the log s...
I have SQL Server 2005 Database where the transaction log has not updated in a week. The transaction log has recently been truncated due to a situation that used up all available disk space. I have a test database on the same server which is growing daily as expected. What might be the cause of this? ...
On a SQL Server 2000 system, I have a templog.ldf file that seems to grow without bound! But when I check, there are never any open transaction in the tempdb (using DBCC OPENTRAN), nor do I ever use explicit transactions within tempdb. I do, however, use temp tables within my stored procedures. And who knows what ASP.NET data access obj...
I am trying to write a T-SQL routine that shrink the transaction log file using DBCC SHRINKFILE based on the logical name of the database. The DB_NAME() function gives you the logical name of the database. Is there an equivalent one for the transaction log? If not, is there some other way to get this information? The default name for...
OK, here is goes. I’m not a database guru or admin. In fact, besides some occasional index / query tuning, I don’t poke around too often in databases. One of the things which often eludes me is the SQL Server transaction log. I know what it’s for, what it contains and how it works (at least conceptually), but I think I don’t get why SQL ...
In SQL Server 2005, if you have a database with no backup - but it is running in full recovery mode - is it possible to "roll back" some deletes? If yes - how? ...
I am working with SQL Server 2000, and trying to change the data type of a field from varchar to nvarchar, so that it can handle international characters. However, there is already a lot of data in that table, and when I try to save the change, I get the following error: Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server ...