I have a database whose tlog has grown to 4.5 GB. The db is in full recovery mode and I have tried several transaction log backups coupled with DBCC shrinkfile. And it will not shrink. Does anyone have any ideas? There are several transactions which have a status =2, but there are no active transactions in the database. I wonder why the...
I am investigating an issue relating to a large log expansion during an ETL process, even though the database is set in bulk logged mode (and it is not running in psuedo simple but truely bulk logged) Using the ::fn_dblog(null,null) function to examine the transaction log operations and the context of the operation, the log expansion is...
How do I get the current size of the transaction log? How do I get the size limit? I'd like to monitor this so I can determine how often I need to backup the transaction log. I usually have a problem with the transaction log when I perform large operations. ...
I am running SQL Server 2005. My db backup scheme is: Recovery model: FULL Backup Type: Full Backup component: Database Backup set will expire: after 0 days Overwrite media: Back up to the existing media set, Append to the existing backup set The db is writing to 250GB drive (232GB actual). My _Data.mdf file is over 55GB and my _Log.l...
Possible Duplicate: SQL Server 2005 Transaction Log too big I am running SQL Server 2005. My db backup scheme is: Recovery model: FULL Backup Type: Full Backup component: Database Backup set will expire: after 0 days Overwrite media: Back up to the existing media set, Append to the existing backup set The db is writing to 250G...
When you right-click on database, Reports...Disk Usage, I get a report. Because I have limited permissions, I only get the top portion, which shows "Transaction Log Space Usage" - that number alone could be useful to me. However, later I get error messages about not having permissions to run DBCC showfilestats, and I also definitely don...
Hi, I've read other posts and have done hours of research but am still none the wiser. I have a database that is 65 gig in the data file, and currently 230 gig in the log file. I'm trying to redesign the database so it is more efficient but when making schema changes, the log file tries to grow too large and the windows whinges that it's...
I am trying to shrink my database log file. I have tried to run: USE databasename BACKUP log databasename WITH truncate_only DBCC shrinkfile (databasename_log, 1) I get the error message: Msg 155, Level 15, State 1, Line 3 'truncate_only' is not a recognized BACKUP option. Am I missing something? ...
I am trying to shrink my log file using DBCC SHRINKFILE(db_2.ldf), which is the name for log file It gives me error every time: 8985, Level 16, State 1, Line 1 Could not locate file 'FIelD' for database db in sys.database_files. The file either does not exist, or was dropped. Can you please suggest what can I do to fix it. ...
We have a database that is backed up weekly in simple mode. Yesterday, we had a crc error corrupt the mdf file and we were unable to save it. I restored the backup from last week, but now we have a gap from the time of the backup to the time of the restore. Since I have the ldf file from that database, is there any way to "replay" tha...
How can you find out what transaction log backup files have been restored using SQL in SQL Server 2005? ...
Hi, I’m working on a experiment regarding to a course I’m taking about tuning DB2. I’m using the EC2 from Amazon (aws) to conduct the experiment. My problem is, however, that I have to test a non-compression against row-compression in DB2 and to do that I’ve created a bsh file that run those experiments. But when I reach to my compressi...
Here is my scenario: we have a database, let's call it Logging, with a table that holds records from Log4Net (via MSMQ). The db's recovery mode is set to Simple: we don't care about the transaction logs -- they can roll over. We have a job that uses data from sp_spaceused to determine if we've met a certain size threshold. If the thre...
Hey chaps, I have following situation: SQL Server 2008 database, in full recovery model I created a full backup, and an hour later a transaction log backup (both to separate files, so I have a .bak file, and a .trn file) Now for testing purposes, I want to restore the database using these two files. I am doing this on the same serve...
So I noticed that one of my log backups is about 1000x larger than normal. I'd like to see what is in there. Is there something I can use to read it? Thanks! ...
I'm having a dilemma on whether or not to log SQL queries in production as well. I don't know how slow writing files is in PHP. Probably some benchmarks could give some answers, but I wanted to see what you guys think before. What does or does not make the process slow? Or what things could it depend on? ...
How do you find the modification history for a certain table / row in SQL server 2000? Thanks. ...
Hi, I need to update millions of rows as part of my next release, but doing so fills the transaction log and fails. I have a few ideas but I'm not a SQL expert so I'm sure there will be gotchas that I'm not aware of. Pertinent points: I need to hand a script over to the operations team so need a T-SQL method with no manual interventi...
Most of the time users will hit the database to read news. There are very few number of queries executed under transactions. 95% of the database hits would be for read-only purposes. My database log files size is growing 1 GB per day. Even if I shrink the database, the log file size is not decreasing. What could be the reason for growi...
I have an SQL Server 2005 instance whose full backup (.BAK) failed due to low disk space. However half hourly transaction log backups continue (.TRN). Assuming I have an older full backup, could these continuing transaction logs be used to restore the database? i.e. do the transaction log backups only run from the last successful back...