Solved

reduce the size of  transaction log using dbcc (shrinkfile)

Posted on 2011-02-18
7
390 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
ID: 34927381
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
ID: 34927400
Oh, you should also check initial log file size as I believe it can't shrink below that.
0
 

Author Comment

by:GVRDIT
ID: 35019242
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 35019403
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
ID: 35402856
Please close
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35403312
>>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
ID: 38059356
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

815 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

13 Experts available now in Live!

Get 1:1 Help Now