Advice on automating a job.

Hi I'm just looking for a little bit of advice to point me in the right direction before I start to put this together.

The task I have is too automate the clear down of the transaction logs. Presently I am manually going in and using this method:

BACKUP LOG {database name} WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( {log file name},2 )

However now we have decided this would be best to be automated and I'm faily new to SQL server so I'm not sure the best way to go about this. So I was hoping someone could point me in the right direction that I go about doing it so I don't go off on a complete tangent. Ow and Can anyone think of any problem automating this job could cause.

My eternal graitude, EdWhy are you backing up with TRUNCATE ONLY? This does not give you any recoverability for transactions occuring after the last backup. If you really don't need point-in-time recoverability then you could just put the database in simple recovery mode.

blindman|||Ahh I see

I was trying to come up with a way of reducing the size transaction Log without, stopping the database and restarting and I came across this advice, I used the first statment because otherwise the the transactionlog won't shrink much. I didn't really consider the point that (I said I was new too this) this basically mean that the it is no longer actuly producing a functional backup.

Ouch

Well in that case I need to come up with a solution, Do you have any Idea what the best time to clear down the transaction logs would be? erm could I run this immediately before the back up. but then if the backup is faulty we would lose a whole days work and if I run it just after the backup then the next days would be useless, unless I run it immediately and make sure their is no transaction inbetween the backup and the transaction shrinking!

Do you know how to restore the transaction log to it orginal functioning glory!

Thanks Again Ed

Adventure Works DW.sln Starting to write data of the 'Internet ~1 ~MG' partition. Hangs Foreve

Hello,

I am trying to process the sample solution "Adventure Works DW.sln",

whenever I get to the point

...

Starting to write data of the 'Internet ~1 ~MG' partition.

...

This processing step NEVER finishes.

Does anyone have a 'way' to make this 'finish' processing?

If I Exit out of Visual Studio and try again, sometimes I will get the error.

Error 5 Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database. 0 0

Does anyone have any ideas?

Thanks,

Andre Mikulec

AIMDBA

Andre_Mikulec@.Hotmail.com

Also,

When I do individual object processing ...

Processing Dimension 'Clustered Customers.'

Processing Dimension 'Attribute Name'

Started reading data for the 'Attribute Name' attribute.

This <Started reading data for the 'Attribute Name' attribute.> hangs forever and never finishes.

Does anyone have any ideas?

Thanks.

AIMDBA

Andre_Mikulec@.Hotmail.com

|||

Moving to DM forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I was just going through my solution explorer.

I have noticed thate the cube or mining structure 'Customer Clusters' (the one that hangs and does not finish) does IS NOT SEEN IN MY Solution Explorer.

How can this not be?

Does anyone have any idea on how I could fix.

This problem.

I have installed the SQL Server Developer's Edition using my company's MSDN subscription at work.

I installed and deployed the Analysis Services sample solution "Adventure Works DW" just fine without any problems.

This is weird.

Does anyone have any idea on how to fix this.

I have installed and re-installed the AdventureworksBI.msi on my non-company problem host.

The same problem occurs. My old settings in OLAP seem that they are being kept.

I know that the security in the OLAP directories is strange.

Does anyone have any ideas on how to proceed?

Note, I applied Visual Studio Service Pack 1.

Now, I get the same error, but at a different spot.

Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
SQL queries 1 ( *** BEFORE VISUAL STUDIO SERVICE PACK 1 IT HUNG HERE ***)
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Processing Dimension 'Subcategory Basket Analysis'.
Start time 5/26/2007 2:15:02 AM
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/26/2007 2:15:02 AM; End time: 5/26/2007 2:15:02 AM; Duration: 0:00:00
Processing Dimension Attribute 'Attribute Name'.
Start time 5/26/2007 2:15:02 AM ( *** AFTER VISUAL STUDIO SERVICE PACK 1 IT HANGS HERE ***)

Does anyone have any ideas on how to proceed?

Thank you very much.

AIM

Andre_Mikulec@.Hotmail.com

ADO.NET 2.0 Slows SP Execution By Large Factor

I'm hoping someone will be able to point me in the right direction for solving this problem as i've come a bit stuck.

The Sql Server 2005 Stored Procedure runs in about 3 secs for a small table when run from SQL Management Studio (starting withdbcc freeproccache before execution) but times out when run through ADO.NET on .NET (45 sec timeout).

I've made sure the connection was closed prior to opening and executing the adapter. I'm a bit stuck as where to check next though.

Any ideas greatfully received, Thanks

Can you give us something more to go on? If the stored procedure text is relatively short, can you post it with related tables/indexes and such?

|||

Could you please make sure that you are connected to the Sql Server before making call to that stored procedure... Just try to execute a small query with a top clause.

The problem seems with Connection

|||

Unfortunately the SP is fairly complicated at about 200 lines in all. In summary it comprises of two joins. One is a highly indexed Account table and the other is a YearToDate table.

The output is basically a list of regions and all the Revenue that they've generated over period X to Y.

Here's my data tier code. The bottleneck seems to be the last line as that is the part that takes ages when i'm stepping through in debug mode

ssCommand.CommandType =CommandType.StoredProcedure;
ssCommand.CommandText = "my_stored_proc";
ssCommand.Connection =newSqlConnection("my_con_str");
ssCommand.CommandTimeout = 45;
foreach (SqlParameter ssParamin mySqlParmCollection){
ssCommand.Parameters.Add(ssParam);
}
ssCommand.Connection.Open();
ssDataAdapter =newSqlDataAdapter(ssCommand);
ssDataAdapter.Fill(ssDataSet, sDataTableName);

Many thanks,

|||

The nice thing would be to check the Sql Profiler... By this way you will be able to know whether its hitting the Sql Server or not.. and it will narrow down the problem. Then you can see why its taking so long

|||

I've just done a trace and the execution took almost 45 secs to complete using ADO.NET

I then ran it through query analyser and it took 3 secs.

I'm very puzzled by all of this, I could understand the execution plans not being used though .NET but they were cleared whilst executing them using sql management..

Your help is much appreciated,

Matt

|||

This is a excerpt from one of the msdn forum ..

I had the same problem. Seems like the SQL execution plan was corrupted for the stored procedure. I created a new SP and and copied the code to the new SP from the old and ran the application and the data was returned in no time. This proves that this was a SQL server issue and probably the execution plan for the SP was not efficient when it is run from ADO.NET. The reason that the SP worked from the query analyser and not the application using ADO.NET is QA directly executes the SP on the server but ADO.NET internally executes the sp by calling sp_executesql. The dropping and recreating the SP from the database should be a quick fix for the future. It may be worth while to find out WHY this condition happens after a while on the database.

Here are some information from the research I did on this for your reference:
1) "SqlCommand.ExecuteReader executes all commands in the context of the sp_executesql stored procedure." (http://msdn2.microsoft.com/en-us/library/aa720629(VS.71).aspx)
2) "Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table. You will notice this problem quite often when you are working with temporary tables in SQL Server 7.0 as SQL Server will determine that after 6 modifications to a temporary table any stored procedure referencing that table will need to be recompiled."
(http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
3) "You may have heard about a system stored procedure called sp_executesql. It lets you evaluate dynamic SQL, but it happens to also cache its execution plan."
(http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/controlling-stored-procedure-caching-with-dyanmic-sql.aspx)
4) "The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used. "
(http://www.databasejournal.com/features/mssql/article.php/1565961)

Check the link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1102019&SiteID=1

and try the solutions mentioned

ADO in C ?

Hi. Can someone please point me in the direction of using ADO from C?

Thanks!

All sorts of tutorials here. http://www.functionx.com/

Administrative Tasks

Hi
Can somebody please point me to good URL or may be some scripts that I can
use to execute as part of DB health check.
Also, I would like to see if the DB is well designed or thought of while it
was created; I know its a wide subject, but I will be happy to get started
with some script that you guys have been using in your environment.
TIA
PPHi
You may want to read up on maintenance plans in Books online along with
other procedures such as the DBCC commands. Also check out the article
http://www.sqlservercentral.com/col...enanceplans.asp s
well as the other scripts/articles on this site. If you want to do things
yourself you may want to look at what the expressmaint procedure at
http://www.sqldbatips.com/code.asp does.
John
"PP" wrote:

> Hi
> Can somebody please point me to good URL or may be some scripts that I can
> use to execute as part of DB health check.
> Also, I would like to see if the DB is well designed or thought of while i
t
> was created; I know its a wide subject, but I will be happy to get started
> with some script that you guys have been using in your environment.
> TIA
> PP|||SQL Server 2000 Administrator's Pocket Consultant
http://www.microsoft.com/technet/pr...in/sqlops0.mspx
SQL Server 2000 Resource Kit
http://www.microsoft.com/technet/pr...it/default.mspx
Microsoft SQL Server Developer Center - Architecture and Design
http://msdn.microsoft.com/sql/learn/arch/
SQL-Server-Performance.Com
http://www.sql-server-performance.com/
"PP" <PP@.discussions.microsoft.com> wrote in message
news:AA0FF976-9140-42B6-BDF4-8A741D03DAF5@.microsoft.com...
> Hi
> Can somebody please point me to good URL or may be some scripts that I can
> use to execute as part of DB health check.
> Also, I would like to see if the DB is well designed or thought of while
> it
> was created; I know its a wide subject, but I will be happy to get started
> with some script that you guys have been using in your environment.
> TIA
> PP