[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Transaction Log Not Shrinking

Posted on 2013-12-20
5
Medium Priority
?
664 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 7

Accepted Solution

by:
RaithZ earned 1500 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 70

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 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