Controlling the size of the Tempdb

Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.
If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.
|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...

>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
every[vbcol=seagreen]
space,[vbcol=seagreen]
automated[vbcol=seagreen]
the[vbcol=seagreen]
size?
>
>.
>

Controlling the size of the Tempdb

Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...
>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
>> Description:
>> Error: 9002, Severity: 17, State: 6
>> The log file for database 'tempdb' is full. Back up the
>> transaction log for the database to free up some log
>> space.
>> Rather than have to check the size of the tempdb.mdf
every
>> day to make sure it's not consuming too much drive
space,
>> we would like to find out if anyone has found an
automated
>> way of backing up the tempdb to reduce the log size, or
>> maybe even schedule a restart of the SQL services in
the
>> middle of the night to reset the log to its default
size?
>> Any help would be greatly appreciated.
>
>.
>

Controlling the size of the Tempdb

Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...

>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
every[vbcol=seagreen]
space,[vbcol=seagreen]
automated[vbcol=seagreen]
the[vbcol=seagreen]
size?[vbcol=seagreen]
>
>.
>

Control Flow Diverging then Converging Back

I am somewhat new to SSIS and have a question on branching/control flow. We have 3 manufacturing facilities on AS400. Requirement were to create independent SSIS packages in case certain facilities were down, to not interrupt or fail other facilities. to be honest, I didn't want to maintain 3 * (each plant package) packages and decided to create one that takes in a command line parameter and will create seperate batch files (requirements for our archaic scheduling system) that pass in the plant to be processed.

Everything works fine and the packages analyze the passed parameter with no problem and evaluate it when calling stored procedures int a SQL Task. I am required (I think - let me know if I'm wrong here) to have 3 seperate Data Flow tasks depending on which plant is being processed because they are different connections (same server, different DBs - AS400). Which Data Flow Taks run is based on a precedence constraint that analyzes the passed in plant number.

After the Data Flow Tasks are complete, I'd like to merge back into one path again (even though1 out of N will only run at a time) because they are all simply stored proc calls with different parameters. It seemed silly to me to have the same tasks repeated 3 different times. I looked into started up tasks after events, but I don't know a lot about it and it seems as though I would still be maintaining 3 different paths anyway.

Can anyone point me in the right direction?

P.S. (Even if the sollution is to have 1 Data Flow Task I'm still interested in knowing how to converge paths back together)

You can take an upstream control flow item and hook it to three data flows. Then coming out of each data flow you can hook them up to one downstream control flow item. Then you'll just have to work with your precedence constraints to ensure that if one doesn't run the whole package doesn't fail.|||

If I'm undertanding you correctly, that's exactly what I tried. I should have mentioned that. The problem is that the package stops (with success) after the data flow is complete. I assumed it was because all but one of the paths coming to the post-data-flow task did not even execute (which is what I want) so they did not pass a complete or success flag tp the first post-data-flow task. So in short, everything runs great, it just stops as soon as the data flow task is complete.

Is there some other condition that I can put on the post-data-flow constraints? Or another toolbox item that I can use to connect them?

BTW. I appreciate the quick response!

|||You can change the precedence of those connectors. Double click on the connector line and you'll see the options available to you. Look at using "OR" in the multiple conditions.|||

Yahtzee!!

Totally my bad. I had THOUGHT I set them all to "OR", but your suggestion made me check again. One was set to "AND".

Much appreciated!

Consume DataReaderDest from asp.net page?

I have seen the other posts about how to use Microsoft.SqlServer.Dts.DtsClient to run a package and get back the DataReader results. But this fails when run from a client mahcine that does not have SSIS installed. I want to have this page on a web server run the package on a remote Sql Server machine and get back the results but have so far failed. Any one got this working?

protected void Page_Load(object sender, EventArgs e)
{
string path = @."C:\Documents and Settings\Brandon\My Documents\Visual Studio 2005\Projects\Integration Services Project5\Integration Services Project5\FuzzyLookup.dtsx";

DtsConnection connection = new DtsConnection();
connection.ConnectionString = string.Format(@."-f ""{0}""", path);
connection.Open();

DtsCommand command = new DtsCommand(connection);
command.CommandText = "DataReaderDest";

IDataReader reader = command.ExecuteReader(CommandBehavior.Default);

DataSet set = new DataSet();
set.Load(reader, LoadOption.OverwriteChanges, reader.GetSchemaTable().TableName);

_grid.DataSource = set;
_grid.DataBind();

connection.Close();

}

You have to install the SSIS components on the machine where you are executing the SSIS package (which does require a license of SQL Server).

Consume DataReaderDest from asp.net page?

I have seen the other posts about how to use Microsoft.SqlServer.Dts.DtsClient to run a package and get back the DataReader results. But this fails when run from a client mahcine that does not have SSIS installed. I want to have this page on a web server run the package on a remote Sql Server machine and get back the results but have so far failed. Any one got this working?

protected void Page_Load(object sender, EventArgs e)
{
string path = @."C:\Documents and Settings\Brandon\My Documents\Visual Studio 2005\Projects\Integration Services Project5\Integration Services Project5\FuzzyLookup.dtsx";

DtsConnection connection = new DtsConnection();
connection.ConnectionString = string.Format(@."-f ""{0}""", path);
connection.Open();

DtsCommand command = new DtsCommand(connection);
command.CommandText = "DataReaderDest";

IDataReader reader = command.ExecuteReader(CommandBehavior.Default);

DataSet set = new DataSet();
set.Load(reader, LoadOption.OverwriteChanges, reader.GetSchemaTable().TableName);

_grid.DataSource = set;
_grid.DataBind();

connection.Close();

}

You have to install the SSIS components on the machine where you are executing the SSIS package (which does require a license of SQL Server).