1
vote
3answers
112 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 ...
0
votes
1answer
74 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 ...
2
votes
2answers
105 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 ...
1
vote
2answers
589 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 ...
2
votes
2answers
911 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 ...
-2
votes
1answer
290 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 ...
0
votes
1answer
90 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 ...
0
votes
0answers
21 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 ...
3
votes
2answers
325 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 ...
4
votes
1answer
75 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 ...
0
votes
2answers
726 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, ...
6
votes
1answer
840 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 ...
2
votes
4answers
4k 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 ...
2
votes
1answer
353 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 ...
4
votes
1answer
446 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 ...
3
votes
2answers
184 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 ...
0
votes
2answers
4k 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 ...
2
votes
3answers
1k 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 ...
2
votes
1answer
161 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
votes
3answers
205 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 ...
3
votes
2answers
791 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 ...
1
vote
3answers
292 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 ...
3
votes
2answers
3k 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 ...
2
votes
2answers
2k 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. ...
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. ...
2
votes
5answers
3k 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 ...
1
vote
2answers
1k 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 ...
3
votes
1answer
243 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 ...