Solved

reduce the size of  transaction log using dbcc (shrinkfile)

Posted on 2011-02-18
7
399 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 40

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 40

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 40

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

728 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