100% CPU usage on server

[dprothero]

Fortress 1.1.3.18336

Our Fortress server recently started showing 100% CPU usage when someone tries to login (the 100% CPU is on the server side, not the client). It appears SQL Server is using most of the CPU, with it occasionally shifting to w3wp.

I increased the SQLCommandTimeout in Vault.config and now we can finally get connected, but it takes a very long time to do so.

We're not sure why this just started happening. Normally everything performs quite well.

[Beth]

How large are the following database files?
sgvault.mdf
sgvault_log.ldf
sgmaster.mdf
sgmaster_log.ldf
sgdragnet.mdf
sgdranet_log.ldf

Are any errors being logged in your Vault Server Log?

[harolds]

David (dprothero) ask me to get these numbers for you:
sgvault.mdf - 7.63 gb
sgvault_log.ldf - 68.1 mb
sgmaster.mdf - 2.18 mb
sgmaster_log.ldf - 120 mb
sgdragnet.mdf - 489 mb
sgdranet_log.ldf - 984 mb

researching errors now.

[harolds]

Each of the following happened several times:
----10/28/2009 8:30:19 AM mmieglitz--WXPPRO-MIKEM(192.168.1.76)--SSL Disabled System.Data.SqlClient.SqlException: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

----10/28/2009 8:30:19 AM mmieglitz--WXPPRO-MIKEM(192.168.1.76)--SSL Disabled GetLabelStructure returned: FailDBReader

----10/28/2009 9:24:36 AM backend2--timeforce.tresearch.net(192.168.1.36)--SSL Disabled GetLabelStructure returned: FailInvalidLabel

----10/28/2009 12:50:32 PM dprothero--w7david.tresearch.net(192.168.1.42)--SSL Disabled System.Web.HttpException: Redirect failed because authentication ticket could not be stored in a cookie or URL due to configuration restrictions. Set EnableCrossAppRedirect to true in the <forms> configuration section in order to enable the ticket to be transferred to external locations via the URL.

----10/29/2009 8:56:51 AM backend2--timeforce.tresearch.net(192.168.1.36)--SSL Disabled Vault Failed to establish a connection to the database.
----10/29/2009 8:56:51 AM backend2--timeforce.tresearch.net(192.168.1.36)--SSL Disabled System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

----10/29/2009 8:58:32 AM --()-- Vault Failed to establish a connection to the database.
----10/29/2009 8:58:32 AM --()-- System.Data.SqlClient.SqlException: The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

----10/29/2009 8:58:32 AM --()-- Vault Failed to establish a connection to the database.

----10/29/2009 9:05:44 AM hsohrweide--W7SOHRWEIDE(192.168.1.120)--SSL Disabled System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

----10/29/2009 9:05:44 AM hsohrweide--W7SOHRWEIDE(192.168.1.120)--SSL Disabled GetRepositoryStructure returned: FailInvalidRevision


----10/29/2009 12:43:44 PM hsohrweide--W7SOHRWEIDE(192.168.1.120)--SSL Disabled (46bebeb7-8ffb-4539-b2c6-b7eae8f7fb60) BeginTx returned: FailObjectCheckedOut
----10/29/2009 12:43:44 PM hsohrweide--W7SOHRWEIDE(192.168.1.120)--SSL Disabled (46bebeb7-8ffb-4539-b2c6-b7eae8f7fb60) Rollback: $/TherapeuticResearch.Net/TRC.UI.Webroot/HelperCE.vb returned: FailObjectCheckedOut

[Beth]

What version of SQL do you have? What is the OS of the server?

[harolds]

server 2003 enterprise SP2
9.00.4053.00 SP3 Enterprise Edition

[Beth]

Let's start by having you shrink the log files on your sgdragnet and sgmaster databases. Sometimes having log files that are larger than the main database files can cause problems.

1) Make a complete BACKUP of the database.
2) Execute the following query:

Code: Select all

BACKUP LOG sgvault WITH TRUNCATE_ONLY

3) Execute the following query:

Code: Select all

USE sgvault 
GO 
DBCC SHRINKFILE ( sgvault_log ) 
GO

4) Make a complete BACKUP of the database with the truncated log.

Once this is complete, how is your SQL database reacting?

[bwestover]

I am also working on this same team (dprothero and harolds)

I ran these steps to backup the dbs, and then shrink the logs.

Code: Select all

backup database sgdragnet
to disk=N'd:\backup\sgdragnet.bak'
with stats = 10

backup database sgmaster
to disk=N'd:\backup\sgmaster.bak'
with stats = 10

BACKUP LOG sgdragnet WITH TRUNCATE_ONLY

BACKUP LOG sgmaster WITH TRUNCATE_ONLY


USE sgdragnet
GO
DBCC SHRINKFILE ( sgdragnet_log )
GO

USE sgmaster
GO
DBCC SHRINKFILE ( sgmaster_log )
GO

WE have noticed that the problem seems to occur during the GetRepositoryTreeDelta function.
The stored procedure on SQL is sgvault.dbo.spgetrepositorydelta

When this operation is running, the CPU goes up to 100% and Vault slows way down for all users. This has occurred even after running the operation above.

[dprothero]

I ran these steps to backup the dbs, and then shrink the logs.

Code: Select all

backup database sgdragnet
to disk=N'd:\backup\sgdragnet.bak'
with stats = 10

backup database sgmaster
to disk=N'd:\backup\sgmaster.bak'
with stats = 10

BACKUP LOG sgdragnet WITH TRUNCATE_ONLY

BACKUP LOG sgmaster WITH TRUNCATE_ONLY


USE sgdragnet
GO
DBCC SHRINKFILE ( sgdragnet_log )
GO

USE sgmaster
GO
DBCC SHRINKFILE ( sgmaster_log )
GO

WE have noticed that the problem seems to occur during the GetRepositoryTreeDelta function.
The stored procedure on SQL is sgvault.dbo.spgetrepositorydelta

When this operation is running, the CPU goes up to 100% and Vault slows way down for all users. This has occurred even after running the operation above.

[Beth]

Do you perform regular Vault database maintenance?

Do you have folder security set on the repositories?

How many repositories do you have?

Could you go to your Vault admin web page and view the statistics on each repository? How do your numbers look there?

[dprothero]

Reviving this thread as we still have this problem. It occurs for a while, usually for a day, then once everyone has gotten logged into Fortress either via the client or in Visual Studio 2008 (after waiting 15-20 minutes), it seems to go away.

Yes, we do perform regular db maintenance.

No, we don't use folder security.

We have 4 repositories, but we really only use one of them.

This is the one we actively use:

TherapeuticResearchNet2
Repository Information: Revisions: 32279
Folders: 1406 (+2099 deleted)
Files: 19924 (+21792 deleted)
Tree Size: 2.42 GB
Disk Space Needed: 4.84 GB
Database Size: 4.66 GB


These we are keeping around for historical purposes:
OLD - TherapeuticResearchNet
Repository Information: Revisions: 13297
Folders: 446 (+101 deleted)
Files: 4732 (+7321 deleted)
Tree Size: 393.56 MB
Disk Space Needed: 787.13 MB
Database Size: 637.02 MB

OLD - TRSites - ASP sites and Backend
Repository Information: Revisions: 9820
Folders: 230 (+2 deleted)
Files: 3908 (+86 deleted)
Tree Size: 132.9 MB
Disk Space Needed: 265.8 MB
Database Size: 107.17 MB

Utilities
Repository Information: Revisions: 1
Folders: 1
Files: 0
Tree Size: 0 bytes
Disk Space Needed: 0 bytes
Database Size: 0 bytes

[Beth]

I will need to see a full server log. Could you send an email to support at sourcegear.com (attn: Beth) with a link to this forum thread and the server log attached? If the server log is large, could you zip it up before sending?

[dprothero]

Thanks Beth. Could you let me know where this log file is?

[Beth]

The log I need is at C:\Windows\temp\sgvault\sgvault.log.

[dprothero]

Logs sent.