All Questions
Tagged with transaction-log sql-server-2008-r2
60 questions
0
votes
0
answers
44
views
Review of scheduled bulk deletion on a production database
I have a EU privacy requirement to delete production data regularly. The script runs weekly when the traffic is quiet. I wondered if there is any way to improve this script. I can't delete directly ...
- 299
6
votes
1
answer
3k
views
SQL Server - Files timestamps unchanged so what is it that's being backed up?
Please see the below snapshot, showing the timestamps of all the data files as well as transaction log files for one of the SQL Server instances under my management. This is a production instance.
...
- 423
5
votes
2
answers
908
views
Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?
If I size my log files to evenly split the entire drive they reside on, leaving no extra space available, will log backups still be able to occur successfully? I have several databases, with one log ...
- 39.5k
2
votes
1
answer
815
views
Why Won't a Database Backup if the Log File is Full?
I've got a simple backup script for a database in the full recovery model on SQL Server 2008 R2:
backup database livendb to disk = '\\ehsjmaydb01\Data\livendb.bak' with init, format
backup log ...
- 2,650
4
votes
1
answer
1k
views
Can't shrink Log file - "An invalid Floating point operation occurred. (Microsoft SQL Server, Error: 3623)"
When selecting the Transaction Log file from the dropdown on the Shrink Files screen in SSMS, I get the following error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management....
- 41
3
votes
3
answers
4k
views
Database with replication and mirroring stuck in LOG_BACKUP after log file shrink
Need an advice from SQL Server Experts. I'm just a developer, not a DBA, so pardon my ignorance.
My environment consists of 2 equal instances (Node1 and Node2) of SQL Server 2008 R2 x64 (10.50.4339.0)...
- 175
2
votes
1
answer
365
views
Database transaction log growth who is the cuplrit? [duplicate]
There are abundant information on how to shrink a growing Transaction log, from frequent log backups, to changing the RECOVERY from FULL to SIMPLE and truncate. A daily background job takes care of ...
- 121
2
votes
1
answer
253
views
Cannot get LOB data from SQL Server transaction logs
I am trying to extract all information (Insert, Update and Delete Operations) from the transaction log file of SQL Server manually. For this work, I am using the undocumented functions fn_dblog and ...
- 31
4
votes
3
answers
1k
views
Bloated transaction log and .bak backup size confusion [duplicate]
I recently inherited a poorly-managed SQL server.
One of the databases is in rough shape. Here are the details:
The .mdf file is a modest 369MB.
The .ldf file is a bloated 29.7GB.
The recovery model ...
2
votes
2
answers
2k
views
Log shipping monitor primary not seeing recent backups, warnings triggered in error
I am receiving the Log shipping Primary Server Alert warning in error
In msdb.dbo.log_shipping_monitor_primary the last backup time is 1215 however on that machine there are .trn files that have been ...
- 61
1
vote
1
answer
442
views
Is a manual log file grow procedure working different that autogrowth?
I wonder if the internal methods of growing a file using
ALTER DATABASE testdb
MODIFY FILE
(
NAME = testdb_Log
, SIZE = 40960MB --40 GB
)
work different then letting the file ...
- 4,807
-2
votes
1
answer
3k
views
SQL Server 2008 R2 - Rebuild Log
I'm trying to rebuild a log file for my database. I want to know if what is happening is normal. This is what I'm doing:
use master
ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER ...
- 1
0
votes
1
answer
2k
views
Sql Server log: is 'Backup|Log was backed up' the same as 'BackupDatabase: Log files done'?
One transaction log file is growing and growing for a couple of days and I can't find out why. Now I figured out that either the way the activities are stored in the sql logfile OR the behaviour of ...
- 4,807
4
votes
1
answer
284
views
Why isn't transaction log free space always reused?
After monitoring the Transaction log files for a while I noticed that some of them sometimes behave in a way I did not expect.
Here are two samples for comparison. All data is based on EXEC('DBCC ...
- 4,807
3
votes
1
answer
3k
views
Redefine Transaction log size restricion fails
I am trying to set the transaction log's max size restriction to UNRESTRICTED using SSMS using the following dialogue:
The actual setting is set to 2.097.152 MB - a nonsense value that exceeds the ...
- 4,807
2
votes
0
answers
161
views
LOG BACKUP is TRUNCATING in Log Shipping
I had configured Log shipping from INSTANCE1 to INSTANCE2. For the past few days, am able to find that the log backup is truncating only at the midnight. Due to this, LSRestore Job in INSTANCE2 got ...
5
votes
1
answer
2k
views
Influence of index rebuild tasks in tempdb on the transaction log?
I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before...
The goal is to ...
- 4,807
1
vote
1
answer
239
views
Really big LOG backups on a DB with Image field
Normally, our log backups has < 10MB size In a big database with a lot of transactions per hour ).
But now we have a database, that is storing Images on a Image field.
the problem is, the ...
- 7,484
0
votes
1
answer
59
views
Index Rebuilding Log File Growth
I would like to start running regular index maintenance on a server that has been running for a while without it. The database is running in FULL recover with log shipping. The problem I am having is ...
- 181
3
votes
2
answers
1k
views
Shrinking log files on development
I've been looking at getting some disk space back on some of our development machines.
These are machines that are not being backed up, not being developed on directly, and not being maintained ...
- 2,631
2
votes
1
answer
216
views
First log backup after FULL taking lots of space
In the backups I'm doing in my database, the first LOG backup I do after the FULL backup is taking too much space, even more than the full backup.
For example:
4:50 AM: FULL BACKUP - .bak 7.2GB
6:...
- 451
2
votes
1
answer
112
views
Change recovery mode and enforce a transaction log backup?
I am going to do a special data deletion operation - once and during a maintenance with no user access (Sql 2008 r2).
The result is a large deletion of millions of blob data from a table by re-...
- 4,807
1
vote
0
answers
203
views
How can I avoid writing to the Transaction Log file when I drop, create and reload data to a table?
I need to create a couple of snapshot tables that will export a large amount of data. While running a few tests, I need to create the table, import data, inspect the results, update the export query ...
- 937
6
votes
1
answer
188
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 ...
- 115
13
votes
2
answers
576
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 ...
- 707
7
votes
3
answers
41k
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.
I'...
- 73
6
votes
3
answers
12k
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 > ...
- 111
2
votes
0
answers
2k
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), on ...
- 21
4
votes
1
answer
2k
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 ...
- 143
2
votes
2
answers
1k
views
Log Sequence Numbers in Full BackUp and Log Backup
My full back-up starts daily at 02:00 AM. It takes about 3 hours.
I also take T-Log back-ups every 15 minutes (at 15,30,45 and 00 hours).
I can verify the LSN sequences of T-log backups when I "...
- 1,550
3
votes
1
answer
328
views
Checkpoints and Logging
I'm working on getting full grasp of logging architecture.
Please correct me if I'm wrong here:
I understand that a checkpoint occurs (also lazywriter) to write the dirty pages in the memory to the ...
- 1,550
2
votes
3
answers
3k
views
How do you create a job in SQL server 2008 R2 that will clear transaction logs?
I have created a maintenance plan in SQL Server 2008 to delete transaction logs backups older than 3 days. Every day I look in the folder designated and the logs are still there. The job states it has ...
- 21
30
votes
4
answers
57k
views
Why does transaction log continue to grow in Simple recovery mode with nightly backups
Before immediately marking as duplicate, I have read Mike Walsh's Why Does the Transaction Log Keep Growing or Run Out of Space?, but I don't think it gave an answer to my situation. I looked through ...
- 438
2
votes
2
answers
8k
views
Fixing an error 9004 without taking the database offline
My database (SQL Server 2008 r2) is running fine but I’m getting the following error in my event log.
Error: 9004, Severity: 23, State: 6.
An error occurred while processing the log for database '...
5
votes
2
answers
10k
views
Switching from SIMPLE to FULL recovery mode implications
I have several databases that I'd like to move over to FULL recovery mode so we can have point-in-time restore capability. We don't need it on all of our databases, just the ones that are transaction ...
- 3,844
3
votes
3
answers
301
views
Log File Management - Space on Disk
At our organisation we have several non-Production environments where Developers run free and wild with TSQL code and databases. The DBA team do not normally monitor or maintain them. Recently several ...
- 1,463
6
votes
4
answers
3k
views
Virtual Log Files (VLFs) Fragmentation
I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation?
Another thing that I want to confirm: Are the following ...
- 4,073
1
vote
1
answer
1k
views
Transaction Log Shipping doesn't use the latest transaction log
I have setup transaction log shipping between two servers every 15 minutes, this is on SQL Server 2008 R2. The problem is that when the logs are being restored on the secondary server, it always uses ...
- 13
2
votes
1
answer
168
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 ...
- 21
5
votes
3
answers
3k
views
Encountered unexpected growth Transaction log file size
We are using SQL Server 2008 R2 Standard version, we created Maintenance plan Rebuild Index, Reorganized Index and update Statistic , Scheduled Every day at 1 AM. After Executing this Maintenance Plan,...
- 51
2
votes
1
answer
80
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 ...
user3284286
6
votes
1
answer
4k
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 ...
- 322
1
vote
1
answer
2k
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 ...
- 13
0
votes
2
answers
128
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
- 11
1
vote
1
answer
714
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 ...
- 631
34
votes
5
answers
91k
views
Difference between Full backup and Copy-only full backup
I saw in the SQL Server Central thread Does a full backup truncate the log? that full backup does not truncate the log:
No. Neither Full or Differential backups truncate the transaction log. - Lynn ...
- 3,131
4
votes
1
answer
943
views
Can I decrypt TDE transaction log files for use with 3rd party software?
Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
- 43
9
votes
1
answer
2k
views
When performing a cross-database transaction, in which transaction log(s) is the information stored?
Given the following snippet:
-- error checking omitted for brevity
begin tran
exec database1..my_stored_procedure
exec database2..my_other_stored_procedure
if (@@error <> 0)
rollback
...
- 368
3
votes
2
answers
3k
views
Reducing high VLF count
I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model.
I ran a full ...
- 273
0
votes
2
answers
418
views
Database not naturally shrinking log file [duplicate]
I have a database that is set to Simple recovery mode. There is 99% free space in the log file and I can manually truncate the log file right down to 500 MB.
The next day, after a daily import ...
- 466