Solved

reduce the size of  transaction log using dbcc (shrinkfile)

Posted on 2011-02-18
7
385 Views
Last Modified: 2012-06-21
I am baffled by the unsuccessful attempts to shrink a transaction log from 100G to 10G on a SQL Server 2005 instance.  I have shrunken numerous other log/data files successfully in the past.   I can't see what is different about this log.  Here's is the command I've used,

DBCC SHRINKFILE (<LogName>, 10240) WITH NO_INFOMSGS

Also, I have done a backup of the log prior to this.  The shrinkfile operation did not error out.  In fact, it came back almost instanteously but nothing had happened, the log is still ~100G in size.

Any idea?!

Thanks
Cordelia
0
Comment
Question by:GVRDIT
  • 3
  • 3
7 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
You should perform the steps like below:

USE dbname
GO
--check file size
SELECT name, size, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO

CHECKPOINT -- commit all uncommited transactions
GO
BACKUP LOG dbname WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(dbname_Log)
GO

--check file size after shrink
SELECT name, size, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Oh, you should also check initial log file size as I believe it can't shrink below that.
0
 

Author Comment

by:GVRDIT
Comment Utility
Sorry for the delayed response...I thought I was supposed to get an email when my question is replied to.  I did check after an hour or so after I posted my question and it showed no reply at that time.  Is there a lag between the time when a question is responded to and when the reply is posted?

Back to the question, I was able to shrink the log using exactly the same steps a few days later.  This is what I did both times.

backup log envadmin to disk='backupPathName' with name='Unscheduled Log Backup'
go
dbcc sqlperf(logspace)
go
use <dbname>
DBCC SHRINKFILE (<LogicalLogName>, 10240) WITH NO_INFOMSGS      -- instantenous
go
dbcc sqlperf(logspace)
go

Still don't know why I couldn't shrink the log the first time!!

Thanks for your reply, Iconhan!
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
If first time you tried that the log was just doing a autogrow due to activity I believe the shrink may not work but not error as well. you should be able to see info about db files growth in SSMS if you did not restarted the server.
0
 

Author Comment

by:GVRDIT
Comment Utility
Please close
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>Please close <<
Here is how you do it:
How do I close a question?
http://www.experts-exchange.com/help.jsp#hs=29&hi=407
0
 

Author Closing Comment

by:GVRDIT
Comment Utility
Although I didn't think there was any activity on the database (but I am not certain), the theory was worth looking into if it ever happens again.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

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

18 Experts available now in Live!

Get 1:1 Help Now