Solved

Transaction Log Not Shrinking

Posted on 2013-12-20
5
601 Views
Last Modified: 2014-03-21
I have a database maintenance plan in place to shrink my transaction log and database nightly. For some reason it is not executing properly and my transaction log is remaining very large. Here is the error:

GO


Shrink Database (HJOSQL05)
Shrink Database on Local server connection
Databases: Commerce
Limit: 50 MB
Free space: 10 %
Task start: 2013-12-20T03:16:54.
Task end: 2013-12-20T03:16:57.
Failed:(-1073548784) Executing the query "DBCC SHRINKDATABASE(N'Commerce', 10, TRUNCATEONLY)..." failed with the following error: "Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:USE [Commerce]
GO
DBCC SHRINKDATABASE(N''Commerce'', 10, TRUNCATEONLY)

GO


I backup the transaction log hourly during the day, and the database at the end of the day around 6:00 pm. I run this maintenance routine later in the evening after the backup.

Any ideas?
0
Comment
Question by:JJENSEN3
5 Comments
 
LVL 6

Accepted Solution

by:
RaithZ earned 500 total points
ID: 39731898
Using TRUNCATEONLY just releases the free space at the end of the log and does not re-organize the data in the files, so unless your log files have excessive free space you won't see much of a size change.

source:  http://technet.microsoft.com/en-us/library/ms190488.aspx


Additionally the error indicates that there is some other operation that is moving data around and it can't do the shrink while that activity is going on.

Are you doing any other steps in this maint. plan? like a re-index?
0
 
LVL 3

Expert Comment

by:13Shadow
ID: 39732029
Why are you trying to shrink the db daily? I can see shrinking your transaction log daily but not the db.
Try changing your command to: DBCC SHRINKFILE (N'DEFAULTDB_log' , 0, TRUNCATEONLY) or whatever the log name is and see if it works.

If you try running the command manually, does it work?
0
 

Author Comment

by:JJENSEN3
ID: 39732400
I am running a reorganize and reindex in the same maintenance plan.
RaithZ do you think they are overlapping? I would think they would run sequentially.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39732984
You should not be shrinking the dbs or logs on any regular basis, especially not daily.  The shrinks are very bad for overall SQL performance.  There, the best thing would be to remove those tasks completely.

The most likely overlap is with a BACKUP DATABASE.  If you want to keep the performance-killing shrinks, make sure they don't overlap with any backups.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39733185
Aside from the severe performance hit, chances are that if you are successful you will lose any chance of doing a point-in-time restore.  And if you cannot do that, then there is no point in setting your database to Full Recovery Model.

The bottom line as pointed out previously is that what you are attempting to do is a very bad idea.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

914 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

12 Experts available now in Live!

Get 1:1 Help Now