Solved

Transaction Log Not Shrinking

Posted on 2013-12-20
5
623 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
[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
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

734 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