Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

reduce the size of  transaction log using dbcc (shrinkfile)

Posted on 2011-02-18
7
Medium Priority
?
417 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 40

Accepted Solution

by:
lcohan earned 1000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

618 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