1

Our production database log file has grown to consume all disk space on our server.

I know it's bad practice but I want to be able to shrink it in order to restore the service as soon as possible.

Still, when I go and launch my SHRINKFILE command,I get this error :

Msg 3023, Level 16, State 2, Line 2
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.

It seems to me that the backup will never end. Is there a way to stop it manually ?

(I tried the SQL Server Agent thing, as told in the MSDN documentation, but it is not present in my SQL Server Express)

  • You cannot run shrink operation while backup is going on. What do u mean when u say backup is never ending what is wait type , is it blocked – Shanky Dec 29 '14 at 13:46
  • @Floum: Is this still a issue. Can you update about current situation of problem – Shanky Dec 29 '14 at 18:21
  • After this problem is resolved, read this: dba.stackexchange.com/q/29829/47920 – Aaroninus Dec 29 '14 at 18:56
-1

Restarting SQL Service will kill backup process and then you can shrink. Below query will show you spid of backup process, if any running on server.

SELECT *
       FROM master.dbo.sysprocesses
       WHERE --dbid = db_id('YourDatabase') AND --if you want to see status for database
       cmd LIKE 'BACKUP DATABASE%'

Below query will kill backup process if you don't want to restart SQL service.

Declare @SPIDU Varchar(10), @Query Varchar(50);
SELECT top 1 @SPIDU = spid
       FROM master.dbo.sysprocesses
       WHERE --dbid = db_id('YourDatabase') AND --if you want to see status for database
       cmd LIKE 'BACKUP DATABASE%'
Select @Query = 'KILL ' + @SPIDU + ';'
Exec( @Query)

Make sure you do clear trans log by taking backup or else you wont be able to reclaim space from trans log file.

| improve this answer | |
  • Whats point in asking OP to restart SQL Server services that would be really a bad idea, he might kill the backup operation. Did you looked at error message. – Shanky Dec 29 '14 at 18:20
  • If you read question, server is out of space and he is not worried about backup because he wants to shrink file as soon as possible by killing backup. I agree with you about looking at error message but he is not DBA and programmer. I hope you will provide some queries for wait and steps for error message to OP. – Vishal Patel Dec 30 '14 at 9:29
  • Vishal did u anywhere said in ur answer that why he is getting shrink error. Plus there is no point to restart sql server like u suggested. Your answer is incorrect and does not addresse complete point – Shanky Dec 30 '14 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy