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)

Shanky
  • 19,148
  • 4
  • 37
  • 58

1 Answers1

-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.

Vishal Patel
  • 34
  • 4