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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2003 query lost it's only join 7 27
Run SQL Server Proc from Access 11 31
SQL Count issue 24 16
Help to build a Proc... 6 20
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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