Hello All
Just curious if there are any tutorials/help files/forums on using this and other Konesans control Flow and data flow task? most import the flow watcher task.
thanks
Karen
No is the honest answer. We write the documentation pages on the SQLIS site for each component, but as free components we have not gone that far on documentation. Questions pop up here about them or you can contact us direct (http://www.konesans.com/contact.aspx), we are happy to help.Hi...
I'm inserting and deleting about 30 000 records into 2 tables each day - import them from a text file using DTS.
The users add about 1000 records a day using Access and Windows .NET frontends...
Which TSQL commands should I run frequently to keep the database up to speed ?
I'm doing the following ... do you know of anything else?
Backup LOG MyDataBase WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (MyDataBase , 40)
GO
Backup LOG tempdb WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (tempdb, 70)
GO
THANKS!!!!!!!!!!!!!!!
Dave
There are a lot of different things you can do to keep the speed of your database up, such as:
Set the database to Simple Recovery and enable Auto Shrink, this will perform the same function you are doing with your DBCC Shrink commands. There are quite a few debates as to whether or not Simple Recovery and Auto Shrink impact performance but I have not seen any negative impacts myself.
Partition your database across multiple physical drives, this can dramatically improve performance
If you are using SAN disk, properly align the sector boundaries of your disk, here is a rather large document on the subject but it's a good one
http://www.hertenberger.co.za/resources/diskpar.pdf#search='how%20to%20use%20diskpar'
Delete any data older than "x" days from your tables, which it sounds like you may already be doing
Dedicate "x" amount of RAM to your instance
Dedicate "x" number of CPU's to your instance
The list goes on but that is a few examples.
|||Simple recovery model will not impact performance. However, the autoshrink certainly will. If the autoshrink kicks off when you are trying to process other queries, it will definitely slow everything down.|||Hi Dave,
What comes to mind immediatly is that there will be a potentially large amount of fragmentation of tables/indexes in your db. This is because: a) you are performing a large amount of deletes and inserts often and b) shrinkdatabase introduces logical fragmentation.
So, I would recommend you run dbcc showcontig on your main tables, then perform rebuilds as needed with either DBCC DBREINDEX or DBCC INDEXDEFRAG. As an aside, if there has been a large amount of fragmentation on several tables, make sure your statistics are up to date, and run sp_recompile on the tables in question so that any stored procs you have can make use of the new stats immediatly.
Cheers
Rob
Hi, Lesego.
If you're going to be doing queries agianst these tables that you're adding and removing data from, it's probably a good idea to update the statistics on the table. UPDATE STATISTICS is the command to use, and you'll want to run it against any statistics the table has -- you can find those most easily by exploring in the object browser in managemnet studio, but you'll have one for each index on the table, plus any that you've created yourself, plus any that the server has created automatically.
UPDATE STATISTICS might not be too important if you're selecting data directly from the table. But it will be very important if you are using the table that's the target of your insert/delete batch job in any JOINs with other tables. The query optimizer makes many decisions about how to best execute a statement based on information it can gather from the statistics on the table.
Hope that helps, and do let us know if you have more follow-up questions.
.B ekiM
I hope this is posted in the correct forum.
I have Linked Servers that are used to query Access databases that reside on a file server.This has been working for a long time, until I applied KB913580 which references the Distributed Transaction Controller. I am running SQL Server 2000 on the same desktop (development environment) from which I am accessing the Linked Server.The file server is on the same LAN segment – there is no firewall involved.
I uninstalled the update and rebooted but still getting the error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\tacir2k3\Infrastructure\Databases\July 2003 Databases\General\GenDb2004_TR_Db.mdb'.It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:].
The SQL Server log contains the following (see bolded item):
2006-05-12 07:58:29.79 serverMicrosoft SQL Server2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2006-05-12 07:58:29.79 serverCopyright (C) 1988-2002 Microsoft Corporation.
2006-05-12 07:58:29.79 serverAll rights reserved.
2006-05-12 07:58:29.79 serverServer Process ID is 1720.
2006-05-12 07:58:29.79 serverLogging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2006-05-12 07:58:29.98 serverSQL Server is starting at priority class 'normal'(1 CPU detected).
2006-05-12 07:58:31.85 serverSQL Server configured for thread mode processing.
2006-05-12 07:58:31.95 serverUsing dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2006-05-12 07:58:32.18 serverAttempting to initialize Distributed Transaction Coordinator.
2006-05-12 07:58:32.37 serverFailed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
2006-05-12 07:58:32.46 spid3Starting up database 'master'.
The Distributed Transaction Coordinator service is started. The settings are all set to OFF (0).
A query to a table in the linked server returns:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\tacir2k3\Infrastructure\Databases\July 2003 Databases\General\GenDb2004_TR_Db.mdb'.It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:].
(It is not in use by any other user).
exec xp_cmdshell 'type \\TACIR2K3\infrastructure\Databases\July 2004 Databases\General\GenDb2005_TR_Db.mdb'
This returns” “access is denied”
Is there any way to resolve this problem?
Hi,
I am not sure if the issue is related to the patch or not.
Most probably uninstalling the patch won't help. I have experienced few issues after installing this patch on Windows 2000 server.
Even uninstalling didn't help.
Solution which worked:
Uninstall the patch, reboot.
Install the patch manually from Microsoft website and not from windows update site, reboot and the issue should be fixed if it's related to the patch.
The patch if installed from windows update is not updating few core files in Windows 2000 server. Manual install is able to update the files.
|||I installed KB913580 on a Windows 2000 server hosting our Exchange Server (5.5). This sits on an internal IP and receives traffic from a TrimMail (mail filtering device). The TrimMail gets an internal IP NATted from a Cisco Pix firewall. The firewall is connected directly to a cable modem (the router is a Cisco "router on a stick" handled through a trunked vlan on a Cisco Catalyst 6600 switch).
The way it is designed, the TrimMail device receives mail, filters, forwards to the Exchange server. If the Exchange server is unavailable, TrimMail holds the mail for up to five days.
After the installation of KB913580, incoming mail from outside our network stopped. Some of the incoming mail bounced, some was acquired by TrimMail and held but not delivered to Exchange. Every "ping" from inside the network to any outside address failed. Browser traffic worked fine without any delays, so the connection was all right.
When KB913580 was uninstalled, "ping" worked, mail came through fine.
I hate to think we are exposed to the security vulnerability this patch is designed to fix, but until I figure out what happened and why, I cannot re-install it.
Any ideas?
|||Yes, there's no doubt in my mind that this patch caused these problems and the problems I have encountered.
I applied the Microsoft patch MS06-018 (KB913580) Vulnerability in Microsoft Distributed Transaction Coordinator Could Allow Denial of Service on the 13th (4 days after it was issued) to my dedicated server (at a hosting site) running SQL2K SP4. Since then, on two occasions, I needed to reboot my server (Win 2K Server SP4) and the server took exceptionally long to boot and then when I finally gained access to the server SQL Server EM could not find my server (my databases were gone), I couldn't access Internet sites with IE (although I could ping remote sites), and I couldn't even open the Control Panel to look at network services. In the Services menu, it just said mssql.exe "Starting...". When that service was stopped, the network stuff all happened so I could access sites, and I could restart SQL Server.
Excerpts from the SQL Server Log:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Attempting to initialize Distributed Transaction Coordinator.
(12 -30 minutes later)
Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
Could not set up Net-Library 'SSNETLIB'..
Unable to load any netlibs.
SQL Server could not spawn FRunCM thread.
Does anyone know SPECIFICALLY the issue here? This is all on a production server where I cannot experiment with uninstalling/rebooting/installing/rebooting etc.
Thanks for any help.
|||I did as you suggested and rebooted.
The SQL Server log errors related to the Distributed Transaction Coordinator are gone and I can reference the linked database.
Thank you many times!!! I would never have come to this solution.
|||Well I spoke too soon about this solving my problem. Got one good SQL Server start and now I am back to the same problem.
I also noticed a couple of other problems that started occurring after KB900485 was applied on 4/28/2006. I tried the suggestion regarding reinstalling this patch from the MS site and keep getting the automatic updates notice for it when I reboot. It displays on the Add/Remove programs list.
I get about 21 messages " Error 15457. Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install." These started on 5/1/2006 after KB900485 was applied. On the last test these occurred at the time I executed SQL to access the linked database.
I looked closer at the Events log and notice that I also started getting the following error related to MSSQLServer after KB900485 was applied. "SuperSocket into: (SpnRegister): Error 1355.
GRRRRR. Anyone with any other suggestions.
|||
I had issues with KB913580 and developed a bunch of strange problems on my server and it turned out to be a tape driver conflict.
Everything else that happened was a symptom of the problem and not the actual problem. I could not log out or shutdown the server, I could not browse the internet and some of the core services were not started or timed out etc which caused a series of problems overall.
So, if you have backup exec v10D installed on this server and have the veritas drivers installed try removing the veritas tape drivers and go to your native tape drivers from MS or the Manufacture and see if your other problems go away. It may take two reboots for the tape drives and veritas to work properly. Once to initially identify the tape drive and install the drivers and then a second reboot to actually load the drivers.
Let me know if you have this situation and if it corrects it.
|||After a MONTH of PSS from MS, dozens of hours of emails and phone calls, and several PSS techs, it turns out that this was a SQL Server bug, that occasionally appears on some Windows 2000 SP4 machines when the KB913580 patch changes something about the order of startup services.
The fix can be found here:
http://support.microsoft.com/?kbid=917405
Note: Much of this time was wasted because the KB913580 patch could not be completely uninstalled (as expected) once it was manually installed (not from the update process).
