Try to set your log file to autogrow or temporarily add some extra log files which can handle such an operation
BUT
it may be simplier and faster to do something like this:
use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20)
GO
UPDATE npidata SET
npiNew = SUBSTRING(npi, 1, 20)
GO
ALTER TABLE npidata DROP COLUMN npi
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)
In this case log file usage can be controlled by UPDATE statement, you may perform this by small chunks like:
use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20), ready bit
GO
WHILE 1 = 1
BEGIN
UPDATE TOP (100) npidata SET
npiNew = SUBSTRING(npi, 1, 20),
ready=1
WHERE ready is null
IF @@ROWCOUNT = 0 BREAK
END
GO
ALTER TABLE npidata DROP COLUMN npi
GO
ALTER TABLE npidata DROP COLUMN ready
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)
For better performance, in case when the table is big - you may apply temporary index on column ready
OH, YES!!!
Like @RemusRusanu said - TURN OFF THE AUTOSHRINK
Summarizing the information linked to in the post above....
The command you have used is for a live backup but what you want is an incremental backup. The approach you want to use (per this blog) uses somewhat different switches. You want to use the -n -t -x switches, and the -o to specify a log file. So the command ends up something like:
dbbackup -n -t -x -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" -o "c:\backup\backup_log.txt"
Best Answer
Keep in mind that the backup does not necessarily reflect the size of the database (including the log) on disk - even if compression is not used.
You are likely trying to expand a log file that matches the size on the original database, and there is not enough space on the disk. You should check the size that the restore operation is trying to restore to looking at the size column for the files in the following query:
This may or may not match the current size of the original source database, depending on when the backup was taken and what type of operations have been performed against that database in the meantime.
If you don't have that much space to spare on the disk you're trying to restore to, you have multiple options:
WITH MOVEto place the data and/or log files on a different drive with sufficient space; or,COPY_ONLYbackup and restore that.