Installer stalled for hours?

[SirArion]

Ran the setup for 4.0 on our premium Lansweeper server, the SQL database is hosted on another server.

Setup seems to have stalled at this point and I am unsure if I should kill this or just let it go?? Been running for 4hours against a db of 2.5gb.

"Setup is updating your database to the latest version"
Currently running sql code:
-- end check all constraints
-- PRINT 'Changing computername keyfield'
DECLARE @computername nvarchar(300)
DECLARE @TEL int
DECLARE @TEMPSQL nvarchar(4000)
SET @TEL = 1
Declare Allcomputers cursor for
select computername from tblcomputers

open Allcomputers
fetch next from Allcomputers into @computername
WHILE @@fetch_status = 0
BEGIN
SET @TEMPSQL = N'update tblcomputers set computername = ' + '''' + cast(@TEL as varchar) + '''' + ' where computername = ' + '''' + @computername + ''''
EXEC sp_executesql @TEMPSQL

SET @TEL = @TEL + 1
FETCH NEXT FROM Allcomputers INTO @computername
END

CLOSE Allcomputers
DEALLOCATE Allcomputers

[efosshaug]

Hi.

We like very much an int id instead of computername, so this is a good change, but this will do the same as the cursor proc

CREATE TABLE dbo.Tmp_Computers
(
[Computername] [int] IDENTITY(1,1) NOT NULL,
[ComputerUnique] [nvarchar](150) NULL,
[Domain] [nvarchar](300) NULL,
[Userdomain] [nvarchar](150) NULL,
[Username] [nvarchar](150) NULL,
[Lastseen] [datetime] NULL DEFAULT (getdate()),
[Firstseen] [datetime] NULL DEFAULT (getdate()),
[Lasttriggered] [datetime] NULL,
[LastScheduled] [datetime] NULL,
[LastActiveScan] [datetime] NULL,
[FQDN] [nvarchar](512) NULL,
[LastknownIP] [varchar](100) NULL,
[Computer] [nvarchar](150) NULL,
[SiteID] [numeric](18, 0) NULL,
[Scanserver] [nvarchar](150) NULL,
[ReplID] [numeric](18, 0) NULL,
[ReplSite] [numeric](18, 0) NULL,
[OScode] [varchar](20) NULL,
[SP] [numeric](18, 0) NULL,
[Description] [nvarchar](300) NULL
) ON [PRIMARY]

go
-- SET IDENTITY_INSERT Tmp_Computers ON

go
IF EXISTS(SELECT * FROM tblcomputers)
INSERT INTO Tmp_Computers ( ComputerUnique,
[Domain],Userdomain,Username,Lastseen,Firstseen,Lasttriggered,
LastScheduled,LastActiveScan,FQDN,LastknownIP,Computer,SiteID)
SELECT ComputerUnique,
[Domain],Userdomain,Username,Lastseen,Firstseen,Lasttriggered,
LastScheduled,LastActiveScan,FQDN,LastknownIP,Computer,SiteID FROM dbo.tblcomputers TABLOCKX
go
SET IDENTITY_INSERT Tmp_Computers OFF


DROP TABLE tblcomputers
go
Exec sp_rename 'Tmp_Computers', 'tblcomputers'
GO

This is finished in 6 seconds, and later you update the id in the other tables so as far as I can see from your script this will work fine.
All I do is to let SQL create the id column
I am going to try this 🙂

[Hemoco]

efosshaug wrote:
This is finished in 6 seconds, and later you update the id in the other tables so as far as I can see from your script this will work fine.
All I do is to let SQL create the id column
I am going to try this 🙂

Don't, it will fail.

[Hemoco]

The step that takes the most time:
Each computername field is converted into an integer field.
Ex:
mycomputer -> 1
myothercomputer -> 2
...

this change is propagated to all other tables that use the field computername.

Plus point: the database/querying will be much faster in v4 because the key to link all tables is much smaller.

[d1eter]

well.., my upgrade to ver.4 took 2hr40mins.
looking at the lansweeper service stopped and started time, in the system log.

lansweeper db size is about 1GB in file size.
before upgrade is ~800MB only.

~840 devices inside the database.
not much complicated/customized reports.

just to share some facts in my site.

good luck to you guys running the updates.
30 hours.. wow..

[poweld1]

Kicked off installation at 2pm yesterday and still running like everyone above, have about 6000 PCs in total. DB size is about 2.2GB.

[efosshaug]

Hi.

Mine ran for 11 hours before I killed it. Reran setup. It did some quick sql-jobs and finished. When I see in the tblComputers it has changed from name to int on 259 of 6100 computers! 10 hours for that! Something wrong with the t-sql?

Anyway, lansweeper is now off course not working, but I have a lot of systems using the sql-data to present information. These are still working so I can live trough the day.

Is it possible to get the sql-scripts so i can run them directly on the sql-server?

We have a premium installation, but my avatar says free user!

Edit:
Found the scripts and looked at them. I would have used a temp-table with identity instead of cursor to get new id, rename and drop.
And - when I look at the script you are doing this afterward?? With identity_insert on. Why not let the id column populate itself? This way you could filled the table with new computernames in one query!
But I might be missing something here.

Anyway, Since my database is halfconverted, I need to do a restore and run the scripts again. I would like to run them on the sql-server. which of the sql-scripts should I run for upgrade and in what order?

[Hemoco]

efosshaug wrote:

Is it possible to get the sql-scripts so i can run them directly on the sql-server?

You need the script upgradescript1.sql in the lansweeper\sql scripts folder

[sticky]

About 31 hours so far 😞

[poweld1]

sticky wrote:
About 31 hours so far 😞

Has yours finished yet?

[Hemoco]

sticky wrote:
About 31 hours so far 😞

How big is your database.
How much computers.
Do you see any sql server activity?