sql 2000 working slowly

[baran121]

hello everybody,
i have two server, one of them use sql server 2000, and sending data from one of them (which i use as client) to server which uses sql server 2000. there is a problem, the server is working very slowly, and sometimes log file is growing.
please help me.

 

[Crowley16]

can you state your configuration and problem more clearly?

try looking at the activity monitor to identify wait types and proceed from there...

--------------------
Procrastinate Now!

 

[SQLSister]

It could be working slowly because the log isn't being allowed to grow enoughfor the import.

Or perhaps your transaction log is never backed up (not your datbase, yourlog) and thus is growing to take up all the hard drive and there isn't enough left over to do the task you want to do.

Or perhaps you are doing thing row-by-row which is alawys the worst way to process large amounts of data.

Or maybe you are updating everything when you only need to updata a subset.

Or...
Without more information it's hard to say. But there are hundreds of things which can casue slow performance. Suggest you get a good book on performance tuning.

"NOTHING is more important in a database than integrity." ESquared

 

[baran121]

when i see data and log files are big , so i take database to temp database with export data. after export i drop my database and create again, after import data to my database from temp database. after i create primary keys,indexs,triggers,procedures,views and functions.
i am not sure am i right, before getting data must i create primary keys,indexs.... ?

 

[Crowley16]

eh, have you not come across the dbcc shrinkfile command?

I suggest you restore your original database and use the dbcc shrinkfile command on that. I really hope you got a backup.

Also, I'd suggest you read up a lot more on SQL Server administration, this is not something that should be done to reduce database size...

--------------------
Procrastinate Now!

 

[baran121]

before doing that data base .mdf file size was 2.300 GB
now its size is 1.100 GB.
maybe you are right "this is not something that should be done to reduce database size... " but result is that it reduces database size about 1.200 GB

 

[PRPhx]

Are you using any kind of maint. plan? If so, are you rebuilding indexes, backing up your database, etc.? This is something that needs to be done on a regular basis.

 

[gmmastros]

Also.... backing up your log files. This is especially true if you have your database in full recovery mode.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[baran121]

yes i am using sql server 2000 and the maint. plan of backup is every day, after 3 days it deletes old ones. i dont understand that you mean about indexes if it is about after exporting data yes i built all indexes,primary keys again.

 

[baran121]

no gmmastros , it is simple mode

 

[baran121]

i used dbcc shrinkdatabase. it is very useful,helpful.
thank you very much. please check it am i right ?

DECLARE Crs CURSOR
FOR
SELECT NAME FROM sysobjects WHERE xtype = 'u'
OPEN Crs
DECLARE @tname VARCHAR(25)
FETCH NEXT FROM Crs INTO @tname
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@tname)
FETCH NEXT FROM Crs INTO @tname
END
CLOSE Crs
DEALLOCATE Crs


dbcc shrinkdatabase(mydb)