Solved

Transaction Log Not Shrinking

Posted on 2013-12-20
5
610 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:Scott Pletcher
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

861 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