How cursors work internally and what's the most efficient way?

Hello-
The system in question is on SQL Server 7.0/sp4.
I use a cursor to rebuild index (DBCC DBREINDEX). The database has size of around 57+ GB while the fragmentation causes the size to hover around 100 GB Mark (drive where data device exists is around 140 GB while the log device drive has 35 GB empty space as well... so space is not a problem).
What's happening is that previously when there were around 1600+ tables, the scheduled task run without a problem. However, for last two weeks the scheduled task ends in failure. I checked and found 2 tables with errors that I fixed with DBCC CHECKTABLE.
What puzzles me is that there were some (I think 2 tables) that got stuck as I try to run the same script through Query Analyzer that I run scheduled. However, when rebuilt indexes manually for those tables individually, it was done in no time. What kind of problem does this show?
For using the STATIC option, how much space should I have on tempdb? currently we have 10 GB.
Thanks a lot!
MZeeshanHi MZeeshan,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
According on your description, I am not quite clear what the problem is on
your side. I would like you to provide more information so that I can help
you narrow down this issue.
1. What is your accurate concern? As I understand, the job failed and table
occurred on your side, however, after you fix using DBCC CHECKTABLE, the
error always occurs next time on the same table. If I have misunderstood,
please feel free to let me know and describe it in detail.
2. If you DBCC DBREINDEX on the 2 tables in another job, did the same
problem occur again?
Also, I would like you to provide the following detailed information so
that I can perform further research on my side.
1. When the job failed, what is the error message? Please provide the
detailed error message.
2 Please provide the Sqldiag.txt using Sqldiag utility. You can send it to
me at v-yshao@.microsoft.com.
For more in formation regarding sqldiag utility, please refer to the
following article on SQL Server Books Online.
Topic: "Sqldiag Utility"
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Muhammad,
According to your description, I understand that when you performed DBCC
DBREINDEX individually for each table, there is not any problem. However,
when you run the scripts in a job, which performed the DBCC DBREINDEX
command for very small tables in a loop with cursor, the job didn't
complete in expected time. If I have misunderstood, please feel free to let
me know.
It is a hard work reviewing the scripts and Sqldiag.txt. According to the
error log, it seems that there are not any abnormal things occurring.
I would like you to provide the following information so that I can narrow
down this issue.
1. Can you please tell me how long it takes to finish running the SQL
scripts? I also would like to know your expected time.
2. Please try to perform the same script on another database, does the same
problem occur? Does the problem only occur on a specific database having
many small tables?
Please try to use the following script to see if it will decrease the
execute time.
//////////////////
CREATE PROCEDURE REINDEX_ALL
WITH RECOMPILE AS
SET NOCOUNT ON
DECLARE @.TableName varchar(300)
DECLARE @.DisplayString varchar(255)
/*----
--
--
Select all table names for REINDEX
----
--
--*/
DECLARE MainTableNamesCursor CURSOR FOR
SELECT o.name
FROM sysobjects o
WHERE o.type = 'U'
ORDER BY o.name
OPEN MainTableNamesCursor
FETCH NEXT FROM MainTableNamesCursor INTO @.TableName
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
SELECT @.DisplayString = 'REINDEX ' + @.TableName
SELECT @.DisplayString
EXEC ('DBCC DBREINDEX (' + @.TableName + ')')
FETCH NEXT FROM MainTableNamesCursor INTO @.TableName
END
END
DEALLOCATE MainTableNamesCursor
SET NOCOUNT OFF
//////////////////////
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

How can I truncate the log file

Dear All,
The database XXX has size 1GB and the log has size 1.5GB.
The recovery model is FULL. (SQL-2000)
How can I truncate the log file (reduce the size) through
a plan?
Harris
Do you have any log shipping or any other log backup jobs scheduled? If yes,
have the job run more often, so that it truncates the log after the log
backup is taken (may be every hour or so, depending on the transaction
volume) . If no, set the recovery model to SIMPLE and do a truncate log.
Everything should be OK after you do either of these.
Rathna Raj
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan?
>
> Harris
|||Hi,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn'
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If your data is not that critical (Development Server) and if you do not
require a time based
recovery go for SIMPLE recovery Model for your database. This require less
monitoring of tranasction log usage
Thanks
Hari
MCDBA
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan?
>
> Harris

How can I truncate the log file

Dear All,
The database XXX has size 1GB and the log has size 1.5GB.
The recovery model is FULL. (SQL-2000)
How can I truncate the log file (reduce the size) through
a plan'
HarrisDo you have any log shipping or any other log backup jobs scheduled? If yes,
have the job run more often, so that it truncates the log after the log
backup is taken (may be every hour or so, depending on the transaction
volume) . If no, set the recovery model to SIMPLE and do a truncate log.
Everything should be OK after you do either of these.
Rathna Raj
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan'
>
> Harris|||Hi,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn'
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If your data is not that critical (Development Server) and if you do not
require a time based
recovery go for SIMPLE recovery Model for your database. This require less
monitoring of tranasction log usage
Thanks
Hari
MCDBA
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan'
>
> Harris

How can I truncate the log file

Dear All,
The database XXX has size 1GB and the log has size 1.5GB.
The recovery model is FULL. (SQL-2000)
How can I truncate the log file (reduce the size) through
a plan'
HarrisDo you have any log shipping or any other log backup jobs scheduled? If yes,
have the job run more often, so that it truncates the log after the log
backup is taken (may be every hour or so, depending on the transaction
volume) . If no, set the recovery model to SIMPLE and do a truncate log.
Everything should be OK after you do either of these.
Rathna Raj
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan'
>
> Harris|||Hi,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn'
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If your data is not that critical (Development Server) and if you do not
require a time based
recovery go for SIMPLE recovery Model for your database. This require less
monitoring of tranasction log usage
Thanks
Hari
MCDBA
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan'
>
> Harris

How can i treate Huge DB size in sql server 2000

Hi all,

I have DB in operation its MDF size reached 8.38 GB and the system that work on the queries of insert timeout and the operation failed and many problems happens ...

actually the reason of the huge size of the DB is just one table that contain image field which we store word files in it in each row in the table .........

so how can i solve this problem without affecting the structure of the DB ..... coz we don't wanna to make code changes in the application that use this DB

thanks

if it 8.38 GB.. this can not be considered as a "Huge" DB from SQL Server point of view ... SQL Server handles much much larger databases... this is basically... architecture flaw... when u store word file in database it is bound to have some performance issue... its again if you have sufficient Hardware resources u can do that... you could have store the path of word file instead of file itself... anyhow, if u don't want to change the architecture... you can move the table which stores word file to a different Filegroup/disk ... or archive the unwanted data… add more hardware… etc.. etc…

Madhu

|||

I hear about the posibility of partioning the huge size table into physical partitions with out affect the logical structure so the performance of accessing the table will be faster ......

so if this process can be done on sql server 2000 on already exist DB how can it be done ...

i need an article about doing that .........

|||

If you are refering to partition of tables its supported in SQL 2005. This article might help you.

http://msdn2.microsoft.com/en-us/library/ms190787.aspx

Thanks,

|||

What about making partionning in sql server 2000?

|||

there are many article available... google it...

i think this will give some hints

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3861.mspx?mfr=true

regards

Madhu

|||You can create filegroups in SQL Server 2000. 8+GB is not a huge database as I have been handling databases more than 100GB and SQL Server is still functioning well. If your application is the one timing out, you have to revisit your application design and your codes as well as your database structure.|||When was the last time the database has been checked for consistency and redinexed for stats update?|||

Let me honest with that never happen ......

so .... is there any suggestions?!

|||

use DBCC DBREINDEX to reindex all the tables and use sp_updatestats to update all the table statistics and see the performance

also use sp_spaceused @.updateusage = 'TRUE' to get the space used by the data...

Madhu