• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

Transaction Log Not Shrinking

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
JJENSEN3
Asked:
JJENSEN3
1 Solution
 
RaithZCommented:
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
 
13ShadowCommented:
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
 
JJENSEN3Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Anthony PerkinsCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now