@Alan Please check once by disabling the audit.
That server has an AUDIT on a database. If the audit is enabled, you
will get that error. If you disable the audit,query will run fine.
I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes
Remote data modifications through a linked server use the sp_cursor
model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk loading method such as bcp
or SSIS
will generally be more efficient (unless the number of rows to be inserted is tiny).
Another alternative for ad-hoc needs is to build a string containing a single INSERT
statement with multiple rows in the VALUES
clause. The statement is then used with EXECUTE AT
, for example:
-- Note local-to-azure table name used, not four-part syntax
DECLARE @sql varchar(max) = 'INSERT dbo.Test VALUES ';
-- Build a list of 1000 numbers to insert
SELECT @sql += '(' + CONVERT(varchar(11), n) + '),'
FROM dbo.Numbers AS N
WHERE n BETWEEN 1 AND 1000;
-- Remove trailing comma
SET @sql = LEFT(@sql, LEN(@sql) - 1);
-- For debugging
PRINT @sql;
-- Execute the finished statement at the remote server;
EXECUTE (@sql) AT AZURE;
The constructed insert statement executes at the Azure database, so a local name is used for the table (note: building a dynamic insert statement using a four-part target name buys you nothing).
Note that EXECUTE ... AT
requires RPC
and RPC OUT
enabled in the linked server options.
The VALUES
clause for a plan INSERT
statement is limited to 1000 elements. There are ways around that (a VALUES
clause in a CTE does not have that restriction). You could also choose to build 1000-row batches or build single-row insert statements, but if the there is that much data on a regular basis, you would probably be better off using one of the bulk loading methods instead.
Best Answer
Below is a sample query to return information from the default trace using T-SQL.
By filtering on
WHERE TEXTDATA LIKE 'DBCC SHRINK%'
you will find all Shrink events.