Maintenance Plan and Log size

Hi,

How do I delete a Maintenance Plan that I have prepared?

Also I have my data log growing beyond 8 GB of data. I have around 12 million rows of data in one table and more than 10 million waiting to come in. What do I need to do here? Have another data file for the same database? How does that work? Should I keep the same data file and allow it to grow to say 15-18 GB?

Also my transaction log keeps growing. It can now hold 8 GB of logs. All I am doing is a simple insert into the big table. I changed the recovery model to Simple ( from Full), and did a shrink log and then it shrank to 50 MB. But the space allocated still remains 8 GB. So now I am thinking that I can change it to say 250 MB and bring back the recovery model to Full. Is that permissible? Any backup log commands associated with a particular mode would be helpful. Please let me know.

ThanksHowdy

Easy - just right click the maint plan & delete it.

If you are importing data during a quiet time ( i.e. after hours ) and you dont need to keep the tran log, you could set the database recovery to SIMPLE while you are importing the data, then set it back to FULL after.
This stops the tran log becoming huge. You can flick the database into SIMPLE recovery mode, then run a manual checkpoint ( i.e. use QA & type CHECKPOINT then run it ) then run a DBCC SHRINKFILE command for both the database & log files.

The space allocated remains at 8 GB? Well, if you flick the database into SIMPLE recovery mode, run the shrinkfile command for the log file, you should remove all the emplty space from the tran log. The checkpoint command will write any dirty pages to the database, allowing a clean run at shrinking the tran log. Sometimes however, if you do the same thing in FULL recovery mode, you may not shrink the tran log if the database has uncompleted transactions, which can then stop the tran log shrinking as the LSNs in the tran log may be at odd spots that will stop the tran log from shrinking.

I'd keep the base size of the tran log as small as possible.

Cheers

SG.

maintaining unique keys when offline

If you have a "Orders" table that is being sync'd to subscribers that are ocassionaly offline, and the subscribers add rows to their local Orders table. When they go online to sync with the published "Orders" table, how do you handle keeping the "OrderId" field unique?

Example:

Both salespeople sync the following data down:

OrderId Desc

1 Order 1

2 Test Order

Both salespeople go offline and add orders

Salesperson 1 adds:

OrderId Desc

3 Joes Order

Salesperson 2 adds:

OrderId Desc

3 Kathys Order

Now, when they go back online, they both will sync their orders up to the main database and they both have the OrderId of 3.

The main problem with using an int identity as a primary key is that it gets assigned by the database on insert; as you're discovering, assigning it outside of the database creates key collisions.

There are several different approaches you can use. All of them have problems:

1) If the row contains some combination of values that are always unique, use this combination as the primary key.

Problems with this approach: Not always possible. If the table's going to be referenced as part of a foreign-key relationship, you have to replicate all of the parts of the key in the referencing rows.

2) Use a composite primary key with two columns, or a primary key that's a concatenation of two values. One is a token that's unique to each process that's creating records; the second is a sequentially-assigned value that each process is responsible for assigning. In your example, you'd use the salesperson as the token. So you could either make salesperson, order id the primary key, or you could create nvarchar keys like "joe:1".

Problems with this approach: The token has to be invariant, i.e. changing the salesperson on the order can create key collisions. The database can no longer assign primary keys, since the PKs are being assigned offline, outside of the database.

3) Use a primary key of type uniqueidentifier (i.e.a GUID). All GUIDs are unique, so you'll never have a key collision.

Problems with this approach: your identifier won't be usable by humans. Sorting by GUIDs is useless. GUIDs use 32 bytes of storage, as opposed to 4 for int.

4) Use a temporary local key (using either of the above 2 approaches) when creating records on the client, and assign the real key when the row is inserted.

Problems with this approach: The client won't automatically know what the keys are of the rows it just inserted into the database, and will have to re-query the database to get their values.

|||

SQL replication also has the ability to assign identity ranges to subscribers. You might want to look into it as a solution. The only problem is in a high subscriber, high volume scenario is assigning an appropriate range to each subscriber.

I looked into this solution for the company I currently work for.We decided using a GUID was a much better solution.It eliminates the need to monitor the identity ranges.

Maintain separate table via trigger vs. indexed view

Hello,
I have a table LargeTable in many columns and many rows. However, I also
need to have a small subset of the rows in LargeTable and only data from
small set of columns; call it SmallTable.
SmallTable gets read very often. So basically I've been using trigger on
LargeTable to watch for any change there to populate the SmallTable.
However, I realized that an indexed view can replace SmallTable.
In general, which one would be a better design in term of performance? I
know that indexed view can be more elegant but I'm interested in
performance. thanks!"Zester" <zeze@.nottospam.com> wrote in message
news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table LargeTable in many columns and many rows. However, I also
> need to have a small subset of the rows in LargeTable and only data from
> small set of columns; call it SmallTable.
> SmallTable gets read very often. So basically I've been using trigger on
> LargeTable to watch for any change there to populate the SmallTable.
> However, I realized that an indexed view can replace SmallTable.
> In general, which one would be a better design in term of performance? I
> know that indexed view can be more elegant but I'm interested in
> performance. thanks!
>
>
What makes you think this is a candidate for an indexed view? You could
create an ordinary view and put a relevant nonclustered index on the base
table. That way you won't incur the same write overhead that an indexed view
has. In SQL Server 2005 you can also include non-key columns in a
nonclustered index.
--
David Portas|||the LargeTable gets read even more often than the SmallTable and LargeTable
serves the main feature of our product. Writing to it is not often but does
occur - most likely only to the columns that have nothing to do with the
dataset needed for SmallTable. The feature using SmallTable is minor, so we
don't want it to interfere with the main feature that needs LargeTable.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uKBFF$3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> I have a table LargeTable in many columns and many rows. However, I also
>> need to have a small subset of the rows in LargeTable and only data from
>> small set of columns; call it SmallTable.
>> SmallTable gets read very often. So basically I've been using trigger on
>> LargeTable to watch for any change there to populate the SmallTable.
>> However, I realized that an indexed view can replace SmallTable.
>> In general, which one would be a better design in term of performance? I
>> know that indexed view can be more elegant but I'm interested in
>> performance. thanks!
>>
> What makes you think this is a candidate for an indexed view? You could
> create an ordinary view and put a relevant nonclustered index on the base
> table. That way you won't incur the same write overhead that an indexed
> view has. In SQL Server 2005 you can also include non-key columns in a
> nonclustered index.
> --
> David Portas
>

Mailing Result Set Rows

In the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1071904&SiteID=1 mike.groh stated that you would define the result set as a data set variable through an execute sql task. He then pushed the variable out as a dataset type. (ds = CType(Dts.Variables("Email_CurrentDataset").Value, DataSet)

How would this type of object be declared as a user variable? Is it of type object? What is the logic behind converting a result set to a data set?

Specifically I am having problems with converting / casting the result set to a data set in that I am getting an error stating that it cannot convert the com object to a data.dataset class...

Imports ADODB

Imports System

Imports System.Xml

Imports System.Collections

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts

Imports Microsoft.SqlServer.Dts.DtsClient

PublicClass ScriptMain

PublicSub Main()

Dim ds As DataSet

Dim dr As DataRow

Dim str AsString

Dim dtm AsString

Dim s AsString

Try

ds = CType(Dts.Variables("resultSet").Value, DataSet) ' <-- Error on converting

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

EndTry

Here's the code to convert an ADO resultset to an ADO.NET dataset.

Code Snippet

Dim oledbAdapter As New OleDb.OleDbDataAdapter
Dim dataTable As New System.Data.DataTable

oledbAdapter.Fill(dataTable, Me.Variables.resultset)