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.
Showing posts with label cursor. Show all posts
Showing posts with label cursor. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
How could I return the next row(Or 8th row for example)
Hi,
I want to return the next row in a select ... order by ... cursor.
I don't want to loop,just SQL,Do you know any solution?
For example: in Oracle we use rownum,is there any equivalent in SQL Server?
-ThanksWhy don't you explain why you think you need a cursor? In Oracle cursors are required, but with SQL Server they are considered inefficent and are rarely used.|||Thanks blindman ,
Typical scenario may be looks like this: I return the result of a heavy select(think about many joins and ...) to the client application: I want just one row at this stage. The application processes that row and then it needs the next row in that select to process.How should I say: "The next row in that select"?
One solution: Use a table (maybe temporary table) to hold the result of that select with an identity column as the rownum of each row.
Another solution: Hold the result of select in application layer and go through a loop in application to fetch the next row.
I am just curious about how one could do this in an "on the fly" manner? You are right maybe it is not efficient in SQL Server but is there any way at all?
-Regards|||Well, I think you should hold the data in the application layer if you want to ensure a static dataset for processing. Problem is, if you go back to SQL Server for your "next" record, the underlying data may have changed. Is your application modifying the data and sending it back to SQL server to be updated?|||Is your application modifying the data and sending it back to SQL server to be updated?
No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks|||Well you may think that, but it doesn't, not in SQL 2K.
2k5 will have rownumber if I'm not mistaken...
But this is the best server side paging articles I've seen
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
And
http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx|||No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks
No, I'm not in agreement. How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility. If the application demands paging, then the database needs to be DESIGNED to facilitate paging. Even ROWNUMBER is meaningless for repeated calls on dynamic datasets.|||How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility.
OK,I am in agreement.Maybe this is about "Presentation of Data" but we know there is not a clear line between those layers: Many things can be done in Database layer but is implemented in "Application server or middle layer" and vice versa.
Thanks to your posts but I still think it is inefficient to pull whole data between layers and I became so happy when I heard that Yukon will have rownum from Brett.Thanks!|||I'd say use the system the way it was designed. Most of your time is not going to be pulling back an entire dataset, but cycling through it row by row. Databases are designed to work on results sets (i.e., not row by row) and thus aren't very efficient when you want to do that. However, procedural languages are designed to do that very thing. By way of example, I had a cursor running on the database when I first began posting here that took approximately 15 minutes to run. Through the help of those more knowledgable here, I was able to remove the cursor and get the same results in under 30 seconds. I guess the best way to test it is do it both ways, but I'm willing to bet you'll find that pulling the entire result set back and cycling in the app will be faster than coding a cursor in the database. Just my thoughts.
I want to return the next row in a select ... order by ... cursor.
I don't want to loop,just SQL,Do you know any solution?
For example: in Oracle we use rownum,is there any equivalent in SQL Server?
-ThanksWhy don't you explain why you think you need a cursor? In Oracle cursors are required, but with SQL Server they are considered inefficent and are rarely used.|||Thanks blindman ,
Typical scenario may be looks like this: I return the result of a heavy select(think about many joins and ...) to the client application: I want just one row at this stage. The application processes that row and then it needs the next row in that select to process.How should I say: "The next row in that select"?
One solution: Use a table (maybe temporary table) to hold the result of that select with an identity column as the rownum of each row.
Another solution: Hold the result of select in application layer and go through a loop in application to fetch the next row.
I am just curious about how one could do this in an "on the fly" manner? You are right maybe it is not efficient in SQL Server but is there any way at all?
-Regards|||Well, I think you should hold the data in the application layer if you want to ensure a static dataset for processing. Problem is, if you go back to SQL Server for your "next" record, the underlying data may have changed. Is your application modifying the data and sending it back to SQL server to be updated?|||Is your application modifying the data and sending it back to SQL server to be updated?
No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks|||Well you may think that, but it doesn't, not in SQL 2K.
2k5 will have rownumber if I'm not mistaken...
But this is the best server side paging articles I've seen
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
And
http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx|||No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks
No, I'm not in agreement. How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility. If the application demands paging, then the database needs to be DESIGNED to facilitate paging. Even ROWNUMBER is meaningless for repeated calls on dynamic datasets.|||How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility.
OK,I am in agreement.Maybe this is about "Presentation of Data" but we know there is not a clear line between those layers: Many things can be done in Database layer but is implemented in "Application server or middle layer" and vice versa.
Thanks to your posts but I still think it is inefficient to pull whole data between layers and I became so happy when I heard that Yukon will have rownum from Brett.Thanks!|||I'd say use the system the way it was designed. Most of your time is not going to be pulling back an entire dataset, but cycling through it row by row. Databases are designed to work on results sets (i.e., not row by row) and thus aren't very efficient when you want to do that. However, procedural languages are designed to do that very thing. By way of example, I had a cursor running on the database when I first began posting here that took approximately 15 minutes to run. Through the help of those more knowledgable here, I was able to remove the cursor and get the same results in under 30 seconds. I guess the best way to test it is do it both ways, but I'm willing to bet you'll find that pulling the entire result set back and cycling in the app will be faster than coding a cursor in the database. Just my thoughts.
Subscribe to:
Posts (Atom)