Solved

Srink Log File in SQL 2005

Posted on 2015-01-08
6
91 Views
Last Modified: 2015-01-14
One of our log file grew to 200 gigs due to a failed backup, I backed up this file and reduced the file size using the task menu in SQL Server Manager using the options in the attached image,  the action successfully completed, but when I check the size, the log file has not been reduced in size
SQL2005SrinkFile.jpg
0
Comment
6 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Did you perform a transaction log backup or a full backup?
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
0
 

Author Comment

by:Stephen Weber - MCSE, CCNA
Comment Utility
I performed a log backup and then full backup the night before, and a log backup after shrinking the file, but have not done a full backup since attempting the shrink.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
The log backup should be BEFORE the shrink.
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
Back up database
Backup log
Shrink log if you need to
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
Database backups and log backups are independent of each other.  Thus, a missed database backup should not directly affect the log backup size, assuming log backups are running normally.

You need to see if anything is preventing SQL from marking existing log space for reuse, aka "truncating the log":

SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = '<your_db_name>'

If the wait_desc is not "NOTHING", then we need to resolve that first.  If SQL can't "truncate" the log, you won't be able to shrink it.

Btw, I'd strongly urge you to use the command:
DBCC SHRINKFILE
to shrink files rather than the GUI.  The GUI is still on occasion flaky, and that is not an area where you want any flakiness at all.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now