Transaction log is a list/history of modification executed by a database management system to guarantee ACID properties over crashes or hardware failures.

learn more… | top users | synonyms

2
votes
1answer
87 views

How is my database being backed up?

I'm using SQL Server 2008 R2. Someone else set everything up. I encountered an error today: Autogrow of file 'ASPState_log' in database 'ASPState' was cancelled by user or timed out after 1748 ...
2
votes
0answers
69 views

Log file Zeroing

During the creation of the database, I am getting below mentioned information about log file zeroing and I have highlighted my understanding in bold fonts. 2015-08-23 12:42:36.850 spid51 ...
3
votes
1answer
58 views

Availability Group log_reuse_wait = Replication

We have a SQL 2014 Availability Group with 3 replicas of our primary database - 2 in our main data center and 1 in our DR center. We did a large index rebuild on the primary which obviously needs to ...
0
votes
2answers
225 views

shrink database in always on availability

hi i was using this query for shrinking the database log file USE Tfs_CoE; GO ALTER DATABASE Tfs_CoE SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (Tfs_CoE_Log, 1); GO ALTER DATABASE Tfs_CoE SET RECOVERY ...
0
votes
0answers
50 views

How to free up disk space on a drive which has BiztalkMsgBoxDB_log file in it

I have been asked to analyze a issue regarding one of the biztalk servers. I was asked to free up space on a particular drive, where I found the only file BiztalkMsgBoxDB_log.bak is taking up close ...
5
votes
1answer
327 views

Can't restore SQL Server db from full backup, log processing fails, database in 'restoring' state

I am trying to set up a database for development purposes on my PC's local SQL Server Developer Edition 12.0.2000.8. I've got a full database backup and separate transaction-log-only backup files ...
2
votes
2answers
32 views

Random and constant “Log Growths” Alerting, how to make it stop?

I've set up alerting on all my database servers and every once in awhile I'll get the following error, sometimes it goes on and on and on, other times it'll be a few times here and there. The SQL ...
5
votes
2answers
278 views

SQL Server log file used pecentage does not drop after multiple backups

SQL Server 2005 I ran "dbcc SQLPerf(logspace)", then took two log backups, and ran "dbcc SQLPerf(logspace)" again. No change from 48.55% used, even though "dbcc opentran (foo)" says that there are no ...
6
votes
1answer
78 views

Size of individual transactions in log file

is there a way to find out the size of transactions from the transaction log by looking at the log file in SQL Server 2008? I do have an unexpected bloat of my logfile from some maintenance jobs which ...
13
votes
2answers
278 views

How are these two SQL Server rollbacks different?

In SQL Server 2008 R2, how are these two rollbacks different: Run an ALTER statement, for a few minutes, and then hit 'Cancel Executing'. It takes a few minutes to rollback completely. Run the same ...
-1
votes
1answer
36 views

Mysql: How does a rollback operation be performed inside InnoDB

I would like to hear in simple words Please answer me in simple words to the question on the below scenario. ============== Engine: Innodb Trx1 : started and a DML is executed at 10:00 AM and rolled ...
1
vote
1answer
18 views

Optimal time interval for scheduling of Transaction Log Backup

I have a database which is currently of size 50 MB. The database is bound to grow by 10 to 15 MB per hour and the data is important. So, I wanted to ask what should be the optimal time interval in ...
2
votes
2answers
98 views

Disk size and log file growth during upgrades

I have four SQL Server databases. Per the maximum load requirements, each database file can be between 1 and 2 TB in size. The HDD is a multi-disk DAS with about 9TB of space. During a database ...
2
votes
3answers
1k views

How can I reduce the Initial Size of a LOG file?

I've inherited a SQL Server 2008r2 database; the Data file was created with an initial size of 1,000 mb, but the Transaction Log has an initial size of 28,016 mb. No backups were being performed. ...
3
votes
1answer
221 views

How to get the daily amount of committed transactions on a given MSSQL database?

The number of transactions for the current connection can be obtained with @@TRANCOUNT. How can you get a daily counter of the globally committed transactions on a given database?
3
votes
3answers
1k views

Restore a large number of transaction logs

I'm using SQL Server 2008 R2, and I need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right-click database > tasks > ...
1
vote
1answer
57 views

Safe delete Transaction Log in SQL Mirroring scenario

We are using SQL Server Mirroring with a witness (High Safety mode) and we see the transaction log on the Principal Server has grown around 20 GB in few weeks. I looked around on SO and google for ...
1
vote
0answers
205 views

Could not redo log record on mirror

One of my database mirrors is in the suspended state today and the error log on the mirror shows the following. Could not redo log record (338934:102862:477), for transaction ID (0:848150408), ...
0
votes
0answers
17 views

Options for reducing transaction log size when disk space is restricted [duplicate]

I have a very unfortunate situation in which I have some SQL Server (standard edition) databases living on a small (~120gb) disk, and a transaction log which has grown to 50gb+. Unfortunately, the ...
3
votes
1answer
423 views

Unable to shrink log file due to snapshot replication

Our database log (SQL Server 2008 R2) is growing too big, and truncating it via DBCC SHRINKFILE doesn't work (the database is set to simple recovery mode). A bit of background. I released some ...
1
vote
1answer
48 views

What transaction logs are for

I'm confused about tranasaction logs for SQL Server. I was under the impression they can be used to roll the database forward. For example say I made a software release on Sunday, I added three new ...
2
votes
0answers
93 views

Transaction log bloat when writing to table on replicated database with parameterized filter

We are observing abnormally large log writes whenever we update a table that is used for parameterized filter in a merge replication. There are a number of join filters as well. When executing a ...
4
votes
1answer
162 views

SQL Server 2012 mirror setup - how to reduce transaction log files [duplicate]

We just upgraded our Windows Server 2003\SQL Server 2005 setup to Windows Server 2012/SQL Server 2012. Both configurations have a mirror with NO witness server. On the old setup the transaction logs ...
0
votes
1answer
56 views

Query trans log or tempdb?

New DBA, all of our databases are SQL 2005. All of our servers run in Simple recovery mode. I work for a school district, where we report student attendance to the state. I run a query to fix ...
0
votes
1answer
248 views

SQL Server Data File/Log File poor performance

I've been asked to look at a database/application server that's "performing slow". No one could really tell me what was performing slow (except for the database backups) so this has been a discovery ...
1
vote
0answers
1k views

INSERT INTO with TABLOCK taking longer than without TABLOCK [closed]

I'm doing something like below to ensure the insert is minimally logged: insert into AnotherDatabase.dbo.HeapTable with (Tablock) ( <column fields ) select <column fields> from ...
0
votes
0answers
38 views

Does the transaction log grow during a full backup? [duplicate]

I've been tagged as the occasional DBA in the office. I have minimal experience and it would be great if someone could answer my simple question. I know the trans log grows during transactions, but ...
2
votes
2answers
2k views

The log in this backup set is too recent, but it's from before the last full backup!

I'm trying to restore a full database backup plus transaction logs to the most recent transaction log. I've set up transaction logs to ship out every 15 minutes. This setup was performed via the SMSS ...
5
votes
2answers
2k views

Transaction log is full due to 'CHECKPOINT' for DB in simple recovery model

For one of our DB's on SQL server 2012 RTM, We could not perform almost any troubleshooting step as the error says: " Transaction log is full due to 'CHECKPOINT" This being a dev database, its ...
4
votes
1answer
188 views

Tempdb change settings and recommendation

In my production database I have below configuration in TempDb and needless to say I am facing a lot of IO issues on the drive . So, as per recommendation I am planning to change it Change #1 ...
4
votes
1answer
219 views

Large Transaction Backup

On my SQL database I run a full backup at 6am and transactional backups every 15 minutes on week days starting at 8am. Every Monday the first transactional backup is 7 Gb. I want to know why, my ...
0
votes
1answer
90 views

Why temdb and log file size is not reduced by SQL Server

With a series of question Part1, Part2 and Part3. With the answers and research on internet I came to know that TemDb is use for holding different values (ex.: Temp variable, Temp table, CTE, Hash ...
4
votes
1answer
186 views

IO request time and less write latency

I am facing issues related to constantly growing log file due to which I am getting error. When I checked SQL Log I found below messages (error log is filled of these messages almost 90%) SQL ...
1
vote
1answer
88 views

Dealing with non active transaction in log file and shrinking

I have posted the first part of my question here & I don't want to flood my question with a loads of information and would force people to alter their answers for my each question. I think I am ...
3
votes
2answers
1k views

How to minimize log operations in SQL Server to avoid “log full” error

I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting Msg 9002, Level 17, ...
0
votes
1answer
418 views

Will restarting SQL Server clear my transaction log? [closed]

I have a client that restarts his SQL Server instance to clear an application problem he is encountering. I find this is very odd, I don't think you should ever restart the database in normal ...
0
votes
0answers
26 views

Database Log Files Getting out of control [duplicate]

We have a database in which the transaction log file is blowing up to 100GB with the actual database being 1GB in size. It blows up pretty quick as this database is pulling data from another database ...
1
vote
0answers
53 views

Unsafe statement written to the binary log using statement format

In MySQL error log I am getting this warning message: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it ...
1
vote
3answers
1k views

MS SQL Server 2008 Transaction log file back up failure

I've done some reading around this subject here and on StackOverflow but nothing really answers my question enough for me to go forward. I'll explain we have a MS SQL 2008 server running, the ...
2
votes
1answer
114 views

Use transaction logs multiple times

Every night a full backup is made of all the user databases. Every 15 minutes, transaction logs are created. Today i succesfully restored a database with the use of transaction logs. I set the time ...
0
votes
0answers
25 views

Why is the transaction log file not truncated though its simple recovery model? [duplicate]

My database is simple recovery model and when I view the free space in log file it shows 99%. Why doesn't my log file truncate the committed data automatically to free space in ldf file? When I shrink ...
1
vote
1answer
399 views

What is stored in a transaction log file?

What does the transaction log file store? Is it the blocks of transactions to be executed, is it the snapshot of records before beginning the execution of a transaction or is it just the statements ...
0
votes
0answers
66 views

Why are transactions long running?

My stored procedure has a transaction blog. I pick up a record from a database and process it in that transaction block. I noticed that my transaction log has grown in gigabytes. But my transaction ...
1
vote
1answer
69 views

Why is my transaction file growing when db is in simple mode and there are no waiting transactions

I noticed that my database log file has reached 160GBs. My database is in simple recovery mode and I used the following query to see if there are any waiting transactions. SELECT ...
1
vote
1answer
441 views

Mysql: binary log vs transaction log

In mysql with innodb, there are two types of logs: binary log and transaction log. I know transaction log is specific to innodb. But when comes to replication, backup and crash recover, what roles ...
1
vote
1answer
47 views

Why do you have to execute a full backup after a new database for trans backup jobs to run [closed]

Playing around on my local sql server at home after an issue occurred at work. We have full backup jobs scheduled on Mondays only. I created a DB on Tuesday (when doffs and trans are scheduled). ...
2
votes
1answer
164 views

How does the transaction log record a transaction?

We all know what SQL Server's transaction log records information about the transaction. But what, in a full recovery model, is actually recorded in the transaction log? You'll say: THE TRANSACTIONS! ...
0
votes
0answers
21 views

SQL Server Transaction Log Huge [duplicate]

I came to work today and I saw the transaction log with 441GB, using almost all disk space. I use SQL Server 2012 and have Full backup, but somehow I lost control of the t-log. So, I need to do 2 ...
0
votes
1answer
90 views

logging in Bulk logged recovery VS Full recovery model

I am trying to understand the logging aspects in Full and Bulk recovery models.As per MSDN ,select * into test from abcsd is logged minimally in Bulk logged ,but in full recovery model,it is logged ...
1
vote
1answer
245 views

Read transaction log records

I am reading Remus Rusanu article on tlog.In that article he demoed on how to read log payload by using dbcc page option .My question is ,is there a way to read logrecord output of fn_dblog(null,null) ...