Solved

nsure that there is enough disk space available, and that the account under which the Snapshot Agent runs has permissions to write to the snapshot folder and its subdirectories.

Posted on 2011-02-20
2
843 Views
Last Modified: 2012-08-14
Getting the following error when i execute the script :

IF ( SELECT OBJECT_ID('tableABC') ) IS NOT NULL
BEGIN
      ALTER TABLE tableABC
      ADD flag_column nchar NOT NULL DEFAULT 'n'
END


Its giving Msg 21331, Level 16, State 1, Procedure sp_MSreplcopyscriptfile, Line 49
Cannot copy user script file to the snapshot folder at the Distributor (copy "IF ( SELECT OBJECT_ID('tableABC') ) IS NOT NULL
BEGIN
       ALTER TABLE tableABC
       ADD flag_column nchar NOT NULL DEFAULT 'n'
END" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\2011022133011641897"). Ensure that there is enough disk space available, and that the account under which the Snapshot Agent runs has permissions to write to the snapshot folder and its subdirectories.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
error for me.

0
Comment
Question by:PeteEngineer
2 Comments
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 250 total points
ID: 34940918
Hi,

It looks like, you have Snapshot enabled on the given database. If you create a snapshot, then all the modifications to the base Database will also be redirected to the "Snapshot" created.
And as the message is saying. Please check if you have enough space on C: drive and also, whether the account from which the SQL Server Service is running is having permissions of read-write on the given directory.

RNM
0
 
LVL 10

Assisted Solution

by:John Claes
John Claes earned 250 total points
ID: 34940952
When adding a defaulted Column to an existing table, the table is fully loaded into the temp database (copy).

I don't know how big the tableABC is and how much place you have upon your C-Disk, but there might be a small issue here. ;-)


how big is my tabel ?
run following Stored procedure (is a close calculation)
CREATE PROCEDURE GetAllTableSizes
AS
/*
    Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)      --For storing values in the cursor
DECLARE @TableSchema VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 
select sysobjects.[name],sys.schemas.[name]
from sysobjects
INNER JOIN sys.schemas ON sysobjects.uid  = sys.schemas.schema_id 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName, @TableSchema

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
	declare @TableFullName varchar(201)
	set @TableFullName= @TableSchema + '.' + @TableName
print @TableFullName
    INSERT  #TempTable
        EXEC sp_spaceused @TableFullName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName, @TableSchema
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT * 
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

GO

Open in new window


Then Check if the user has rights to that folder upon the hard drive....
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
C# Application Local DB Connection String 23 62
Updating ms sql with special characters 8 25
Sql query 34 22
Caste datetime 2 25
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now