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
i have merge replication configured on sql 2008. HOw i should shrink log file of replicated database.
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.
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.
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.
This is all on the production server, i need to fix this asap. Any help would be appreciated.
Thanks in Advance.
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:
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?
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 :(.
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,
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
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.
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
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 *
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?
SQL 2005 9.0.2153 EE EnglishDatabase 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 database4. Makes fake backup of transaction log (to remove it later).5. Checkpoints database6. 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
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:
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;
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.