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
100 views

SQL Server - Data and Log Separation

We have a SQL Server 2008 R2 which has, among others, a 12K RPM HDD and a 7.2K RPM HDD. The original intention was to store the data files on the 12K drive and the log files on the 7.2K drive, as is ...
2
votes
1answer
198 views

Why write ahead logs in PostgreSQL are generated every second

PostgreSQL version 9.2 generates write ahead log (WAL) every second i.e. 60 WALs are generated in one minute. Is it possible to make them occur less frequently? Is it possible to increase the WAL ...
0
votes
1answer
213 views

Audit SQL Server database transactions

I found some column values have been changed on a table, I need to view when and who made these changes. I tried to read the transaction log backup file, but I didn't understand it and cannot find ...
9
votes
2answers
1k views

SQL Server 'Create Database' statement. How to inherit autogrowth settings?

I am using SQL Server 2008 R2 and creating databases through continuous deployment. In our system, the default 1Mb/10% autogrowth settings in SQL Server work badly with our data. Particularly as we ...
5
votes
4answers
256 views

Only do log backup if necessary in SQL Server

We have transaction log backups taken every few minutes from many databases. Is there a way to determine if there were any committed transactions since the last backup and only then do a log backup? ...
3
votes
3answers
2k views

Multiple Transaction Log files and performance impact

So I've gotten into an argument with several people regarding the number of transaction log files a give databases should have. I've seen several post saying you should only have 1 transaction log ...
1
vote
1answer
74 views

Database never maintained

Just moved from wrong forum i paste here my question: I just got a new client recently migrated from SQL Server 2000 to SQL Server 2005 due to vendor application limitation to new releases. Vendor ...
1
vote
1answer
207 views

Delete log to increase DB space?

I'm running SQL Server 2008 Express and hitting the 4GB size wall. I'd like to make some space to give me time before i migrate to a larger DB. I don't need my transaction log. So, can i delete ...
3
votes
1answer
94 views

Storing Tlogs and data files on the same drive

I'm looking for some clarification of various things I've read. I understand it to be best practice to store your transaction logs on a separate drive (spindle) than where you MDFs and NDFs are ...
2
votes
3answers
2k views

What are the most effective techniques to reduce SQL Server disk space use when updating all rows in a very large table?

Given a SQL Server table with a large number of rows no columns with large-value data types multiple indexes more allocated space than available for the largest possible transaction log size a ...
2
votes
1answer
45 views

Transaction Log Backups on SQL Server

Running SQL2008 R2 SP2, database is in FULL Mode Is it Possible to shrink the transaction log without putting it into SIMPLE mode? Temporarily cannot take log backus and don't want it to grow until I ...
3
votes
1answer
479 views

How to pinpoint root cause of excessive log file growth

The log file stays pretty small (~500MB) until about 11PM, and at that point it begins growing until no disk space is available. The log file reached it's maximum size at 12:39AM, so the growth took ...
4
votes
5answers
3k views

Managing Transaction Log Size using a Maintenance Plan [duplicate]

Are there any best practices for configuring a Maintenance Plan in SQL Server 2008? Currently, I'm removing database backups and transaction logs greater than 40 hours old, then backing them up. ...
0
votes
1answer
349 views

DB2 - Archive mode - large number of logs in logarchmeth1 folder

maybe for someone this is begginner's questions. I read lot of documentation and set: `LOGPRIMARY 6 LOGSECOND 4 LOGFILSIZ 4096 logarchmeth1 Disk - C:\archive logpath C:\active` Number of logs ...
1
vote
1answer
298 views

SQL SERVER Virtual Log Files

I am new to SQL SERVER, could anyone please explain that why do we have VLFs' in T-LOG and why not have 1 T-log itself. I am aware that VLFs' are logical but my question is why VLFs'? what is the ...
7
votes
1answer
109 views

TEMPDB Log Files [duplicate]

Do I want to store the tempdb log files on the same drive as my user database log files, the same drive as the tempdb data file, or on a separate drive?
2
votes
1answer
74 views

Log File over 400gigs…need to bring this database back to life

I'm not a dba so I don't understand the admin side of SQL Server too much. One of our sharepoint instances blew up and our IT staff asked if I could assist bring it back to life. I found backups for ...
3
votes
1answer
56 views

Implement Log Shipping in prod env

We have a 3.5 TB DB which is already involved in log shipping to two different servers, as one will be in restore sate only and one will be in stand by mode which helps for reporting needs. All three ...
-2
votes
1answer
181 views

Why do I need Transaction logs at all [closed]

We use Microsoft SQL Server as a repository of images and data about those images. We record in real time and never care about recovery. If the database slows down and can't handle the number of ...
0
votes
2answers
96 views

Copying a restoring database in SQL Server 2012

I've got two SQL Servers, one of these servers (Server A) is backing up transaction logs on some database and uploading them to the other (Server B). Unfortunately I have no access to Server A, I ...
10
votes
1answer
1k views

DBCC CHECKDB firing every 20 - 60 seconds

I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message: Starting up database 'dbname' CHECKDB for ...
3
votes
2answers
716 views

Missing Transaction log in chain, Possible to skip

We have had one of our clients SQL Servers crash and need to restore it to the most recent possible time. We have a full backup from Monday and transaction logs that run every hour up until this ...
1
vote
0answers
377 views

Log shipping on secondary server

Log shipping fails with the below error. Sql agent on secondary server has access to the folder and files in security. It is not a firewall issue. I created the jobs using the LS scripts as it fails ...
3
votes
0answers
186 views

Why are we suddenly getting time-outs on multiple transactions? [closed]

Two days ago the transaction log filled up and my user's couldn't update or insert. One of my techs truncated the log, shrank the db, then did a full backup. The next day (Monday) we noticed that ...
2
votes
2answers
95 views

Searching transaction logs for changes

I currently have a database with an attached CMS. The CMS has about 50+ people with various permission levels that can log in etc... I would like to know if there is a way I can look at the ...
1
vote
2answers
7k views

How to Fix log_reuse_wait_desc=LOG_BACKUP? LOG Backup not possible

I checked sys.databases to find out which databases need LOG backups. It surprised me that I had quite a few where log_reuse_wait_desc=LOG_BACKUP meaning, the JOB I have set up to run LOG backups on ...
1
vote
1answer
544 views

Securely deleting data from SQL Server logs

Is there a good way to purge data from a particular DB's *.LDF? A little background... I'm a SysAd, not a DBA. I'm running SQL Server 2008 R2 for one of my applications and I'm not particularly ...
1
vote
1answer
164 views

Is it possible to change the drive that Microsoft DPM backs up to?

A while ago our sysops guys created a 15 minute transaction log backup schedule using Microsoft DPM. We often have an issue where there won't be enough space on the Log drive to perform a backup ...
35
votes
5answers
15k views

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with ...
5
votes
4answers
12k views

Manually set log file size after shrink SQL Server 2008 R2

Am becoming a somewhat involuntary DBA at work at teh moment and really need some help on something. We have a 40GB database in Full Recovery Mode, no log backup configured and a huge log file of ...
0
votes
2answers
89 views

Rebuilding index

I have a transaction database and one of nightly job is to rebuild indexes that followed by a log backup and then shrink the log. Is this safe to do so ? Thanks Antony
-2
votes
2answers
452 views

Can the transaction log in SQL Server 2008 Express edition be viewed in text?

Is it possible to read the transaction log in text/table format, and if so, how can I do that?
1
vote
1answer
135 views

Transaction(s) still alive: How do I find the cause?

I can tell that something is hogging the transaction log on the DB2 for one of our Websphere Commerce installations. Basically I have a burst of transaction log files that are timestamped 5 days ago. ...
2
votes
1answer
2k views

DBCC loginfo contains thousands of VLF entries with status 2

I've inherited this huge SQL Server 2000 prod database and suddenly the transaction log ran out of space. The transaction log gets backed up every hour but I think over the weekend some existing jobs ...
1
vote
1answer
997 views

How to flush SQL Server database from RAM to hard disk?

As part of the nightly maintenance plan, my working MS SQL Server 2012 databases are backup-ed with options of simple recovery and full backup. Transaction log is not backup-ed. Also, at nights the ...
0
votes
2answers
415 views

Is an AWS SQL Server RDS “log capture” the same as a transaction log backup?

When an SQL Server RDS instance provided by Amazon is configured for a recovery model using Full transaction logging, will the transaction log be trimmed by the "transaction log capture" that Amazon ...
0
votes
1answer
241 views

CHECKPOINT prevents database drop

I'm working in the following scenario: I'm creating an script that recreates an entire database. The script involves destruction an recreation of the database and all its objects, as well as a great ...
6
votes
2answers
479 views

Two Different Sources Taking Transaction Log Backups

I have inherited a database server with SQL Server 2005. The databases have full recovery mode with hourly transaction log backups. There are two sources taking full backups overnight and hourly ...
2
votes
1answer
148 views

Transaction log autogrowth duration

I am using event notifications to capture data and log file autogrowth events for all databases on my servers. I'm using the data to for analysis of database storage configuration. In looking at the ...
10
votes
2answers
2k views

SQL Server transaction log backups: test whether tail log follows last known log backup

We are using SQL Server with full recovery mode. Given a full backup and a series of log backups, we would like to be able to check whether the log chain is complete from the last full backup to the ...
2
votes
2answers
177 views

Large number of VLF and shrinking the log

If there are lot number of VLF then it will effect the performance of the queries. I saw that solution is to shrink the log file. But shrinking the file is also bad. So how to take control of whole ...
2
votes
2answers
1k views

Shrinking a SQL Server Log File Multiple Times

Recently ran into a situation where after a log backup and shrinkfile, the LDF file remained the same size. DBCC LOGINFO clearly showed only one active VLF and SSMS said that 99% of the space in the ...
3
votes
1answer
915 views

How to troubleshoot db/app latency during transaction log backup

I have an application that uses a SQL server (2008 R2) database and we are periodically having performance problems with the application and it coincides with our 15-minute transaction log backups. ...
5
votes
1answer
220 views

My log file is large, what should I do? [duplicate]

My transaction log is larger than I'd like it to be, and I want to know how I can fix that.
9
votes
1answer
1k views

How important are transaction log VLFs for performance?

How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?
2
votes
2answers
3k views

Transaction log has grown huge after maintenance plan to rebuild idex

A couple of days ago I was testing some stuff using the Maintenance Plans in SQL Server 2008. I created one to rebuild indexes and take a full backup every week. (I know, bad thing I've found out ...
1
vote
2answers
1k views

Running out of Transaction Log space during Alter Table

I'm running an alter table, alter column on a table with nearly 30 million rows and SQL Azure fails after approximately 18 minutes saying that The session has been terminated because of excessive ...
3
votes
3answers
5k views

Transaction Log maintenance when switching to Simple Recovery

Background: I recently inherited 50+ SQL Servers with 450+ databases. The nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. All of the databases are ...
-2
votes
1answer
481 views

Restore to last transaction log restore

We have a log shipping setup where we replicate from our primary database to a secondary. Now that database is, by the way of log shipping, in standby and thus read-only. We set it up so we could ...
1
vote
1answer
467 views

Sneaky rollback in SQL Server 2008 R2

Is it possible for an entire set of committed transactions to be rolled back in SQL Server 2008 R2 without any notification or logging anywhere? We had a set of data apparently get rolled back this ...