All Questions
Tagged with transaction-log sql-server-2008
50 questions
0
votes
1
answer
77
views
What happens if the Subscriber goes down - will the t-logs keep accumulating on the publisher in Transactional Replication?
I am trying to consider all the options before setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL ...
- 37
1
vote
1
answer
735
views
Log File Size and dbcc sqlperf (logspace)
Consider the following log file setting for a database named XYZ:
Initial size = 250MB, Autogrowth by 500MB, Limited to 1GB
I have created a SQL Job to monitor the size of XYZ database ldf using the ...
- 113
7
votes
2
answers
4k
views
Why is my log file so massive? 22gb. I am running log backups
I can't seem to figure out the answer. I've seen multiple answers like this:
Why Does the Transaction Log Keep Growing or Run Out of Space?
and everyone talks about running back ups on your log file ...
- 181
0
votes
2
answers
712
views
Can sp_Blitz, sp_BlitzCache, or sp_BlitzFirst identify what queries caused a major growth operation to occur on a particular log file?
I'm currently logging the outputs of sp_Blitz, sp_BlitzCache, and sp_BlitzFirst regularly (via SQL jobs).
A particular database's log file grew somewhat exponentially and I believe I can see the ...
- 39.5k
1
vote
1
answer
923
views
How do I shrink the MASTER log file in SQL Server 2008?
This is for a non-system database:
USE AdventureWorks
GO
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AdventureWorks_Log)
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL
...
- 11
0
votes
1
answer
286
views
Maintenance plan is backing up database transaction log but it is not selected
I have a strange problem, the transaction log for my database is being backed up however it is not selected.
I change the names of the server and databases in the screenshots to protect my ...
3
votes
1
answer
142
views
SQL Server 2008 - SIMPLE database log file is HUGE relative to the actual DB?
So... I'm not a SQL Server DBA but was asked to look at a customer's situation where they have a 2GB database that has a 60GB transaction log. I've read up on a whole lot of articles on the web (Aaron ...
- 179
2
votes
1
answer
48
views
Detail of all transactions on a specific row
I've a User table and It's data can be added/ updated from a User Screen. I want detail of all transactions on any row.
For example, I have data in table like this
Name - Age - Salary
John - 25 -...
- 175
6
votes
2
answers
6k
views
MSDB log file size
We have a SQL Server 2008 cluster with 60 databases.
Full backup runs every night, log backup runs every 15 minutes.
The MSDB database recovery model is set to simple (default).
To be able to see ...
- 61
3
votes
3
answers
7k
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 ...
- 41
2
votes
0
answers
39
views
SQL Server transaction log reported full when it isn't [duplicate]
The transaction log for database 'WebDB' is full.
No, it isn't. Its 90% free. The transaction log backups run normally. My hardware guy tells me the hardware is running perfectly, with plenty of ...
user212421
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
1
vote
2
answers
4k
views
What is causing my Transaction log to grow
The TLOG in my SQL Server 2008 is growing faster than usual.
The DB has a Full recovery model and we have a TLOG backup every hour.
Can you please write me or refer me to link that describes a way to ...
- 575
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
6
votes
3
answers
12k
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 ...
- 177
1
vote
1
answer
425
views
SQL Server log file continues to grow? [duplicate]
I'm having an issue with a SQL Server log file which will not maintain a steady amount of space held on a HDD which it has been issued. The drive is ~50GB and the log file is growing to occupy this ...
- 53
1
vote
1
answer
1k
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 ...
- 605
2
votes
2
answers
704
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 ...
- 3,131
5
votes
2
answers
4k
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 ...
- 9,682
4
votes
3
answers
7k
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 ...
- 443
-2
votes
1
answer
976
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 ...
- 161
0
votes
1
answer
554
views
SQL Server automatic transaction log truncation
Have looked through msdn but cant find answers to these:
Any help much appreciated:
In simple recovery mode:
Is there any TSQL or alternative functionality that will provide a notification either ...
penguinAttack
0
votes
0
answers
22
views
SQL Server 2008 log file growing and wont shrink. Hard Drive Space running out [duplicate]
I have a SQL Server 2008 database which has a log file (.ldf) with a size of 360GB and growing every day.
The .mdf file is only 25GB. The hard drive is 950 GB and has 150GB free space. I want to ...
Ameya Raje
5
votes
2
answers
5k
views
How large will the Transaction Log backup be?
I encountered a production database of 12 GB with a log of 90 GB. Full and differential database backups are made with a third-party backup tool, but no one is backing up the transaction log so it got ...
user5147
4
votes
1
answer
136
views
Log file growth issues
We're consolidating data from a bunch of databases into four reporting databases each night.
Because the entire dataset is imported each night we do not need to be able to restore the data to a point ...
- 2,119
12
votes
2
answers
7k
views
SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs
After doing some research I cannot seem to find an answer to this question.
Background
I am attempting to setup a backup plan that fits the following three requirements:
Reliability of backups, ...
- 151
10
votes
2
answers
12k
views
Transaction Log maintenance on Mirror database
SQL Server Version: 2008 R2 Enterprise SP2
I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
- 175
10
votes
4
answers
41k
views
Manually set log file size after shrink SQL Server 2008 R2
Am becoming a somewhat involuntary DBA at work at themoment 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 84GB....
user20442
5
votes
1
answer
2k
views
SQL Server Troubleshooting Replication and Long-Running fn_dblog query
I am trying to get (pull) Transactional Replication working again for a subscriber.
Currently, in Replication Monitor I am seeing messages in the Publisher to Distributor tab and the Distributor to ...
- 373
6
votes
2
answers
3k
views
Why can't I read my transaction log backup file using fn_dump_dblog?
I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery.
After backing up the transaction log, I attempt to read it ...
- 313
3
votes
2
answers
255
views
SQL Server transaction log - shrinking and reattaching
I have the next scenario in SQL Server 2008: one big database (production DB). I want to backup this db then restore it on another server.
Then I do some delete operation from some big tables. After ...
Razvan
7
votes
2
answers
40k
views
How to disable ldf file or limit its size [duplicate]
Possible Duplicate:
Why Does the Transaction Log Keep Growing or Run Out of Space?
My .ldf file size keeps increasing. I already tried to shrink it but after that it just grows again.
How can I ...
- 245
3
votes
3
answers
7k
views
ASPState Log file growing tremendously - SQL Server 2008
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Web Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
I have an ASPState database online, and its log file ASPState_log is growing ...
- 191
3
votes
1
answer
225
views
Shrink a large log file for a "Throw Away" database
I have a copy of my prod database on a development server. I am using it to test a deployment script.
But when I run it, it says that the log file is full.
I checked and it is at 10 GB (Almost the ...
- 2,540
-2
votes
2
answers
971
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
2
votes
3
answers
788
views
Transaction Log file .ldf is too small
Current database is almost 200GB however the transaction log file is only 4.1GB.
I am worried if this influences the performance of databse and how could it be solved.
When I do this query
DECLARE ...
R.J.
3
votes
1
answer
3k
views
Identify cause of unusual transaction log growth
I have an application running on SQL Server 2008 (it's a cluster but it hasn't failed over.)
Previously, the transaction log used to grow to 10-20GB every 2-3 days. We would shrink it back (after ...
- 178
20
votes
2
answers
46k
views
Sql Server - Best Practices for Growing Database Files
I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
- 629
3
votes
2
answers
2k
views
SQL Server 2008 Query Saved in Logs?
Every time I create a query and run it to create a report I save the query in my Projects folder in case I have to rerun the query in the future or if I have to modify it for any reason but it seems ...
- 3,283
81
votes
4
answers
217k
views
How to identify which query is filling up the tempdb transaction log?
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
- 1,033
1
vote
3
answers
798
views
Big size of database logs SQL Server 2008
I have a database which is running under Microsoft SQL Server 2008. Now, I have seen that the log of the database (the .ldf file) is growing to big size. The database file (.mdf) has a size of 630MB ...
- 153
4
votes
2
answers
8k
views
How to monitor SQL Server data and log file growth?
I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I ...
- 41
2
votes
2
answers
3k
views
SQL Server 2008, switching to simple recovery mode
Currently our transaction log is 50 GB; impressive huh?
I want to switch to simple recovery mode and remove the transaction log entirely.
Do I need to stop any user activity and detach the database. ...
- 394
7
votes
5
answers
7k
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. ...
- 677
21
votes
6
answers
73k
views
How to prevent transaction log getting full during index reorganize?
We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. ...
Sriram Subramanian
3
votes
3
answers
3k
views
Changing a column's data type fills the transaction log
Running this code:
ALTER TABLE npidata
ALTER COLUMN npi varchar(20)
Gives this error:
Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'SalesDWH' is full. To find out why ...
- 255
3
votes
5
answers
9k
views
Keep transaction log size under control during batch processing
tl;dr
I think my verbosity has obscured the real question I'm asking here, so I apologise for that. My main issue is that the checkpoint command appeared to be working for at least 800 iterations of ...
- 306
2
votes
2
answers
2k
views
SQL Server 2008 - Log file out of control & cant shrink it [duplicate]
Possible Duplicate:
Why Does the Transaction Log Keep Growing or Run Out of Space?
I am having trouble with my database, the log files is currently 250GB in size, I need to shrink this down ...
- 121
3
votes
1
answer
362
views
Does blocking always mean open transaction?
This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation (...
- 5,373