Sql-server – Deploying to an existing database from VS 2010 on TFS

The quickest fix would be to restart the SQL Server and Tempdb will be recreated with default size and empty files.

But if it's a production server you can't really restart it when you want. A real fix would be to add a new file on a different drive and run your queries.

An example would be (new file of starting size 1 MB, increase 100 MB, limit 500 MB):

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'newtempfile', FILENAME = N'e:\newtempfile.ndf' , SIZE = 100MB, MAXSIZE = 500MB , FILEGROWTH = 100MB )
GO

And then, when you have time, check what's using Tempdb so much. But likely you'll still need space for TempDB, so you'd better plan space for this and assign enough space for this db, as it's very important for a healthy system (you can simply look at it as a RAM part).

PS1: check if you don't have a Cartesian product anywhere in your select statements, because that number seems a bit high.

PS2: If you have enough free space on the TempDB drive, check if the files didn't get to their limit and autogrowth is disabled. If yes, enable autogrowth (not by percent, but by some specific size you feel comfortable with).

PS3: a good solution would be to break that ETL process into smaller transactions. Instead of removing 1 Bil records at once, do it by 1000 mill .. or play with batch sizes until you feel safe with the length of the process and the size of the files. You'll likely obtain the result faster and not have huge increases in space at once.

You can do some limited adjustments to the schema with ALTER XML SCHEMA COLLECTION. Here's a simple example:

USE tempdb
GO

IF OBJECT_ID('dbo.users') IS NOT NULL DROP TABLE dbo.users
GO
IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test')
DROP XML SCHEMA COLLECTION xsd_test
GO

-- Create a simple schema
CREATE XML SCHEMA COLLECTION xsd_test AS '<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="users">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" name="user">
                    <xs:complexType>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

-- Create a table with XML bound to that schema
CREATE TABLE dbo.users ( userId INT IDENTITY PRIMARY KEY, userXML XML(xsd_test) )
GO

-- Populate the table
INSERT INTO dbo.users ( userXML )
VALUES
    ('<users><user name="Sinaesthetic"/></users>' ),
    ('<users><user name="wBob"/></users>' )
GO

-- Alter the schema
IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test')
ALTER XML SCHEMA COLLECTION xsd_test
ADD '<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="skills">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" name="skill">
                    <xs:complexType>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

UPDATE dbo.users
SET userXML.modify('insert <skills><skill name="someSkill"></skill></skills> after (users)[1]')
WHERE userId = 1