[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Physical Log size of SQL 2008 DB not reducing after each transaction log backup

Posted on 2011-10-12
5
Medium Priority
?
242 Views
Last Modified: 2012-05-12
Hello there,

I have a application DB hosted in SQL 2008 (Full recovery), with Transaction log backup scheduled every 2 hours from 7 AM till 10 PM, what we are finding is that the Physical log size continues to grow and is currently at 48 GB. When I check the amount of free physical space by right clicking tasks in SMSS and it says 97% space taken and only 3% free.
But when I check the size of the transaction log backup, it is less than 1 GB.
Since it only 3% free, running DBCC shrinkfile will not work, to regain the remaining 97% of space.

Please advise.

Thanks and Regards.
0
Comment
Question by:goprasad
  • 3
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36959777
There are two reasons for that:
1. Active Transactions.
2. Transactions that have not been replicated.  
0
 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 36961040
after  10 PM create a job like this

USE [master]
GO
ALTER DATABASE dbaname SET RECOVERY SIMPLE WITH NO_WAIT
GO

USE [Test]
GO
DBCC SHRINKFILE (N'dbnamet_log' , 0)
GO

USE [master]
GO
ALTER DATABASE dbaname SET RECOVERY Full WITH NO_WAIT
GO

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36961591
If you value your data (and your job), please don't attempt to use that last suggestion.
0
 

Author Comment

by:goprasad
ID: 36965252
Hello acperkins,
based on your reasons stated below:
There are two reasons for that:
1. Active Transactions.
2. Transactions that have not been replicated.  

Therefore the way out is to put DB in a single user mode afterhours and try DBCC shrinkfile?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36965887
>>Therefore the way out is to put DB in a single user mode afterhours and try DBCC shrinkfile? <<
No.  I would be more inclined to see if I am right using something like this:
DBCC OPENTRAN
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Bare Metal Image backup allows for the restore of an entire system to a similar or dissimilar hardware. They are highly useful for migrations and disaster recovery. Bare Metal Image backups support Full and Incremental backups. Differential backup…
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question