.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Asad Ali
Post New Web Links

Shrink Log file for a replicated databases

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server
 

Hi All,

I have a replicated database (TR) and my log file is growing and i need to apply a maintenace plan to avoid run out of space, so i would like to know what you suggest to have the best solution

 

thanks in advance




View Complete Post


More Related Resource Links

how to shrink log file of database which is merge replicated

  

ALL,

  i have merge replication configured on sql 2008. HOw i should shrink log file of replicated database.

Thanks


Shrink database log file sql server 2005

  
Hi, I am trying to shrink log file for a testing database.This database will be restored with production database periodically.When i tried to create backup of the log file Processed 0 pages for database 'hcbeta', file 'HC_log' on file 6. The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. BACKUP LOG successfully processed 0 pages in 0.423 seconds (0.000 MB/sec). I tried EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 Resulted with error:Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. So tried to publish the database using EXEC sp_dboption 'hcbeta', 'Publish', 'true' resulted error mesage:Msg 15242, Level 16, State 1, Procedure sp_dboption, Line 138 Database option 'Publish' is not unique. and retrived duplicate_options as "merge Publish" ,"Publish" Plesae help me in shrinking my database log file. Thanks, Adi.

Why doesn't SQL Server 2005 Enterprise always shrink a log file after a backup?

  
I have several databases that, despite being backed up regularly, do not release space to the operating system.  In one extreme case, I have a log file that is less than 1% utilized (and has been for months.) I know how to manually force a shrink, but I should not need to do that on any sort of regular basis.  In fact, it is against best practices to manually shrink a log file (or any database file for that matter) because of the very high risk of an unrecoverable error. It is my understanding that SQL Server will eventually handle these things on its own.  However, it does not appear to be doing so.  Any thoughts as to why? Thanks, Dave

Multiple databases backup/restore in a transaction, if possible, into only one file

  

Hi dear,

I have three databases named XDW, XOLTP, XOLTPSchema. All of these are for only one application. In the application, user should do backup/restore using GUI.

So...

1. How I should do backup/restore in one transaction? i.e. all of three actions should be success elsewhere all of them should fail.

2. Is it possible to backup them into only one file e.g. X.bak?

3. Or, What is the standard solution in this condition?

Thanks in advance.


LEARN "LINQ TO DATASET" DURING A QUICK SIMPLE HOW TO:
How To: Joining multiple DataTable using “LINQ to DataSet”

How to shrink the sharepoint database log file

  

Hello,

I have a SharePoint content database with the size 70GB but he log file is 450GB. i wanted to shrink the log file. Can anyone tell me  how to do that ? It would be great if anyone can provide me the complete flow.

  1. Do I have to shink the log file when the database is offline?
  2. what if we just take the backup and create a new log file ?
  3. How do we restrict the size of the log file by not growing big but just auto shrink by it self.

This is all on the production server, i need to fix this asap. Any help would be appreciated.

Thanks in Advance.


How can I shrink the size of LDF file after BACKUP LOG?

  

Ive just done BACKUP LOG against particular database and found no changes in size for the appropriate LDF file. It is shrunk only after I issue:

ALTER

 

DATABASE dssip

SET

 

RECOVERY SIMPLE

But this approach breaks all logs chain. Is there any way to shrink the LDF file for FULL backup model without destroying the whole sequece of log backups?


How to shrink the sharepoint database log file

  

Hello,

I have a SharePoint content database with the size 70GB but he log file is 450GB. i wanted to shrink the log file. Can anyone tell me  how to do that ? It would be great if anyone can provide me the complete flow.

  1. Do I have to shink the log file when the database is offline?
  2. what if we just take the backup and create a new log file ?
  3. How do we restrict the size of the log file by not growing big but just auto shrink by it self.

This is all on the production server, i need to fix this asap. Any help would be appreciated.

Thanks in Advance.

 


Issue with non-replicated transaction in Log file

  

Hi All,

I'm trying to shrink my Transaction log. Its taking 8 GB of space and I'm getting out of space on this drive. The database is in SIMPLE recovery model.

The problem is that when I see the log info using DBCC LogInfo() it shows 2723 rows with Status of 2. I issued the Checkpoint command many times as well. There are very few rows with Status 0 which are removed when I shrink the file. But these 2723 rows with Status of active-tran are taking up the most space and are not getting cleared.

I had restared the sql server service manytimes (just for experimenting). Eventually, I exectued the DBCC OPENTRANS command; which tells me that there is a non-distributed transaction in T.Log. This transaction is the very first transaction in T.Log file with Status 2 and Parity 128.

This server used to work as Publisher few months back. All the Subscriptions have been deleted and the relavant Distributor and Subscriber have been re-formatted and re-installed since then. Now when I try to drop the Local Publications from this local server it gives me error for relevant Distributor or Subscribor machines which do not exist any more.

How should I truncate this Log file and unmark this pending non-distributed transaction from T.Log.

Kindly help me out with this :(.


How often a db file will shrink when you set the AUTO_SHRINK option on?

  

First of all, I know AUTO_SHRINK option should be off, but I need to know this, as an IT trainer.

How often a db file will shrink when you set the AUTO_SHRINK option on?

I found out that in case of SQL Server 2000, it'll be 30 minutes.

How about SQL Server 2008?

This page http://msdn.microsoft.com/en-us/library/ms189080.aspx says, "the database can be set to shrink automatically at specified intervals."  but it does not show us HOW.

Thanks in advance,

CARBO


how to shrink transaction log file in log shipping

  

I have 200GB 5 databases on one server i change there recovery model to full on monday and took full backup on monday.took differential backup on friday no log backup during this period and configure logshipping but because of 5 day database did not ran log backup transaction log size went up to 70gb each but now with transaction log backup job transaction log size came down to 20 gb used but it is not able to free up unused space it has 98% unused space.

Dbcc shrink file does not help


Log file does not shrink.

  

I have SQL 2008 on which i have Log Shipping configured. My primary instance has a .ldf of 70GB.

Since it is a log shipping, the logs are backed up periodically every 15min. But when i try to shink the log file nothing happens.

There are no open transaction.

DBCC LOGINFO has 280 rows with status bit of 2 except for some of LSN which are currently in use.

LOG_RESUSE_WAIT_DESC has "NOTHING" which tells me no log backup is required.

My secondary database log file is small. I know if i change it to simple recovery and then shrink, it will shrink it.

But the question is even the log backups are taken periodically why is the log file not shrinking.

Can anyone suggest why this would happen.


Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

Cannot shrink log file, some remains of replication

  

Hi,

It is about SQL Server 2008. The log file is huge - 30 GB, while the backup of log is 200 KB. There were 2 pull subscriptions which I removed, but still cannot shrink the log file.

1. When I use select log_reuse_wait_desc -> I receive "REPLICATION" in spite of there is no subscriptions or publications

2.  When I use select * from sys.tables 

Was forced to shrink a log file. How do I prevent future fragmentation

  
I read that one of the side effects of truncating and shrinking a large log file is that it will incurr fragmentation as it grows.  What steps can I take to prevent this?  How do I monitor the log files internal fragmentation as it grows?

Cannot shrink log file because all logical log files are in use

  

SQL 2005 9.0.2153 EE English
Database has recovery model = Full

I have job which is executed at night time. It executes these main tasks (in order of execution)
1. Renames transaction log backup of previous day.
2. Executes maintenance plan (check database -> rebuild indexes -> update statistics)
3. Makes fake backup of transaction log (to remove it later).
4. Shrinks log (dbcc shrinkfile(...,0, TRUNCATEONLY).
5. Deletes fake backup from step 3.
6. Makes full backup of database.

There is another job that executes every 15 minute at work time which backups transaction log.

Step 4 finishes with message "Cannot shrink log file ... because all logical log files are in use". And log size remains unchanged.

If I try to shrink log at work time using same dbcc shrinkfile I don't get message and log becomes small.

I added 2 steps with checkpointing. Now job executes these steps:

1. Renames transaction log backup of previous day.
2. Executes maintenance plan (check database -> rebuild indexes -> update statistics)
3. Checkpoints database
4. Makes fake backup of transaction log (to remove it later).
5. Checkpoints database
6. Shrinks log (dbcc shrinkfile(...,0, TRUNCATEONLY).
7. Deletes fake backup from step 3.
8. Makes full backup of database.

But message occures yet and l

Unable to shrink log file

  

I am working with a small database. Data file is size 2 MB. But, the log file size is 11 MB. The database is in simple recovery mode. The version is SQL Server 2008 R2 64-bit standard. I am unable to shrink the log file to something lower than 11 MB. I am using the statement, DBCC SHRINKFILE (dblog1);

I have also used the statement, DBCC SHRINKFILE (dblog1, 10);

I would like any guidance on why the log refuses to shrink.
 

The following t-sql is used to view the space used:

 SELECT  
ds.name as filegroupname
, df.name AS 'FileName' 
, physical_name AS 'PhysicalName'
, size/128 AS 'TotalSizeinMB'
, CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB'
, size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB' 
, (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds  
        ON df.data_space_id = ds.data_space_id;

Thank you.

+ive



Large transaction log file won't shrink

  

In my SQL 2005 database, the log file is 12 GB and won't shrink.  The initial size also is 12 GB.  Does that mean that's the size of the active log, and is that why it cannot shrink smaller than that?  The initial size used to be around a MB or so.  I understand why the file grows, but not why the initial size increases.

The transaction log grew even though the recovery mode was "Simple".  I switched it to "Full" and backed up the transaction log (with the "truncate" option), but doing that didn't allow the log file to shrink.

Running dbcc shrinkfile generated the message "Cannot shrink log file ... because all logical log files are in use".

This database has been in use many years without having this problem.  This problem might have started around the time I set up replication for this database.  This server is the publisher and the distributor.  Could this cause this problem?  Do I need to remove the replication before shrinking the log file?

I've read numerous other postings on similar problems, but haven't found the answer.

Thanks.


File Splitter in .Net

  
I love to do utility programs in .Net. And this is one of them. As I was playing with JSplit (a free file splitter program), I wondered if I could do it in .Net. The framework supports file operations like reading files in bytes and creating them. It is indeed easy to do file operations in .Net Basics on File operations
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend