Solved

reduce the size of  transaction log using dbcc (shrinkfile)

Posted on 2011-02-18
7
396 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 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Encryption question 2 66
Move SQL 2005 Express to Server 2012R2 19 169
SQL query with cast 38 61
SQL- GROUP BY 4 26
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

730 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