hi,
I need to shring a SQL 2000 db by 50GB. Is it better to start sql in single
user mode when doing such action?I believe you will find that the database will be put into single user mode
for the life of your shrink session automatically. But it might simplify
matters to do it manually.
Joseph R.P. Maloney, CSP,CCP,CDP
"stoney" wrote:
> hi,
> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in single
> user mode when doing such action?|||Thanks very much for your prompt response. Does the system have a better
performance if I shrink the database more often? Say if I shrink the
database every 3 months.
Thanks.
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>I believe you will find that the database will be put into single user mode
> for the life of your shrink session automatically. But it might simplify
> matters to do it manually.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?|||In my humble opinion database server perform better with their logs shrunk
and stored in medias.
The data are more important than the logs.
and logs are overhead to database services
thats why a sound backup and restore strategy must implemented
--
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Diane Walker" wrote:
> Thanks very much for your prompt response. Does the system have a better
> performance if I shrink the database more often? Say if I shrink the
> database every 3 months.
> Thanks.
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
> >I believe you will find that the database will be put into single user mode
> > for the life of your shrink session automatically. But it might simplify
> > matters to do it manually.
> >
> >
> > --
> > Joseph R.P. Maloney, CSP,CCP,CDP
> >
> >
> > "stoney" wrote:
> >
> >> hi,
> >>
> >> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
> >> single
> >> user mode when doing such action?
>
>|||Shrinking does not require the db to be in single user mode and there is
nothing that does that automatically.
--
Andrew J. Kelly SQL MVP
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>I believe you will find that the database will be put into single user mode
> for the life of your shrink session automatically. But it might simplify
> matters to do it manually.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?|||Actually you get better performance if you don't shrink it at all in most
cases. If you need to keep shrinking it that is a pretty good indication it
wants to be larger than you think it should be. So leave it alone. See
this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Thanks very much for your prompt response. Does the system have a better
> performance if I shrink the database more often? Say if I shrink the
> database every 3 months.
> Thanks.
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?
>|||Just the opposite. Transaction logs are crucial to performance on SQL
Server. Since all data manipulation is sent to the transaction log first it
will slow everything else down if it can not write fast enough. By
shrinking the log files too much you will force them to grow just when it
needs to write entries to the log file. They then must wait on that
operation to complete before they can continue.
--
Andrew J. Kelly SQL MVP
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:986EDB0C-E8C8-47F5-8646-175E0CE40A22@.microsoft.com...
> In my humble opinion database server perform better with their logs shrunk
> and stored in medias.
> The data are more important than the logs.
> and logs are overhead to database services
> thats why a sound backup and restore strategy must implemented
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Diane Walker" wrote:
>> Thanks very much for your prompt response. Does the system have a better
>> performance if I shrink the database more often? Say if I shrink the
>> database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>> >I believe you will find that the database will be put into single user
>> >mode
>> > for the life of your shrink session automatically. But it might
>> > simplify
>> > matters to do it manually.
>> >
>> >
>> > --
>> > Joseph R.P. Maloney, CSP,CCP,CDP
>> >
>> >
>> > "stoney" wrote:
>> >
>> >> hi,
>> >>
>> >> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> >> single
>> >> user mode when doing such action?
>>|||Thanks very much for the information.
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:986EDB0C-E8C8-47F5-8646-175E0CE40A22@.microsoft.com...
> In my humble opinion database server perform better with their logs shrunk
> and stored in medias.
> The data are more important than the logs.
> and logs are overhead to database services
> thats why a sound backup and restore strategy must implemented
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Diane Walker" wrote:
>> Thanks very much for your prompt response. Does the system have a better
>> performance if I shrink the database more often? Say if I shrink the
>> database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>> >I believe you will find that the database will be put into single user
>> >mode
>> > for the life of your shrink session automatically. But it might
>> > simplify
>> > matters to do it manually.
>> >
>> >
>> > --
>> > Joseph R.P. Maloney, CSP,CCP,CDP
>> >
>> >
>> > "stoney" wrote:
>> >
>> >> hi,
>> >>
>> >> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> >> single
>> >> user mode when doing such action?
>>|||My database file is 55 MB. The transaction log is 14 GB. Do you recommend
that I leave the transaction log alone? Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uKa26MFwFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Actually you get better performance if you don't shrink it at all in most
> cases. If you need to keep shrinking it that is a pretty good indication
> it wants to be larger than you think it should be. So leave it alone.
> See this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> Thanks very much for your prompt response. Does the system have a better
>> performance if I shrink the database more often? Say if I shrink the
>> database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might
>> simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?
>>
>|||No that is excessive and warrants a shrinking<g>. But why did it get there
in the first place? Chances are you are in FULL recovery mode and only issue
Full backups. You need to issue regular (at least several times a day) LOG
backups in order for the log to truncate the committed transactions and
reuse the space. If you don't want to do Log backups then change the
recovery mode to SIMPLE and it will keep it in check for you.
--
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:u3917jGwFHA.3720@.TK2MSFTNGP14.phx.gbl...
> My database file is 55 MB. The transaction log is 14 GB. Do you
> recommend that I leave the transaction log alone? Thanks.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uKa26MFwFHA.2516@.TK2MSFTNGP12.phx.gbl...
>> Actually you get better performance if you don't shrink it at all in most
>> cases. If you need to keep shrinking it that is a pretty good indication
>> it wants to be larger than you think it should be. So leave it alone.
>> See this:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> Thanks very much for your prompt response. Does the system have a
>> better performance if I shrink the database more often? Say if I shrink
>> the database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might
>> simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?
>>
>>
>|||Thanks very much for your prompt response. I have no idea why the log file
gets that big. What should I be doing so that the log file would not get
this big? We have had this database for 1 year and 3 months. I am doing
the full backup everyday, running Backexec from Veritas.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltiB%23HwFHA.3688@.tk2msftngp13.phx.gbl...
> No that is excessive and warrants a shrinking<g>. But why did it get
> there in the first place? Chances are you are in FULL recovery mode and
> only issue Full backups. You need to issue regular (at least several
> times a day) LOG backups in order for the log to truncate the committed
> transactions and reuse the space. If you don't want to do Log backups
> then change the recovery mode to SIMPLE and it will keep it in check for
> you.
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:u3917jGwFHA.3720@.TK2MSFTNGP14.phx.gbl...
>> My database file is 55 MB. The transaction log is 14 GB. Do you
>> recommend that I leave the transaction log alone? Thanks.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uKa26MFwFHA.2516@.TK2MSFTNGP12.phx.gbl...
>> Actually you get better performance if you don't shrink it at all in
>> most cases. If you need to keep shrinking it that is a pretty good
>> indication it wants to be larger than you think it should be. So leave
>> it alone. See this:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> Thanks very much for your prompt response. Does the system have a
>> better performance if I shrink the database more often? Say if I
>> shrink the database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might
>> simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>> hi,
>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql in
>> single
>> user mode when doing such action?
>>
>>
>>
>|||I listed what I feel the issue was in the last post<g>. Change your
recovery mode to Simple if you don't do log backups.
--
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:u65jHzSwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Thanks very much for your prompt response. I have no idea why the log
> file gets that big. What should I be doing so that the log file would not
> get this big? We have had this database for 1 year and 3 months. I am
> doing the full backup everyday, running Backexec from Veritas.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltiB%23HwFHA.3688@.tk2msftngp13.phx.gbl...
>> No that is excessive and warrants a shrinking<g>. But why did it get
>> there in the first place? Chances are you are in FULL recovery mode and
>> only issue Full backups. You need to issue regular (at least several
>> times a day) LOG backups in order for the log to truncate the committed
>> transactions and reuse the space. If you don't want to do Log backups
>> then change the recovery mode to SIMPLE and it will keep it in check for
>> you.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:u3917jGwFHA.3720@.TK2MSFTNGP14.phx.gbl...
>> My database file is 55 MB. The transaction log is 14 GB. Do you
>> recommend that I leave the transaction log alone? Thanks.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uKa26MFwFHA.2516@.TK2MSFTNGP12.phx.gbl...
>> Actually you get better performance if you don't shrink it at all in
>> most cases. If you need to keep shrinking it that is a pretty good
>> indication it wants to be larger than you think it should be. So leave
>> it alone. See this:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:OI6jb4EwFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> Thanks very much for your prompt response. Does the system have a
>> better performance if I shrink the database more often? Say if I
>> shrink the database every 3 months.
>> Thanks.
>> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
>> news:E4CA50BF-C163-4968-82CD-E64AAAD20AD7@.microsoft.com...
>>I believe you will find that the database will be put into single user
>>mode
>> for the life of your shrink session automatically. But it might
>> simplify
>> matters to do it manually.
>>
>> --
>> Joseph R.P. Maloney, CSP,CCP,CDP
>>
>> "stoney" wrote:
>>> hi,
>>>
>>> I need to shring a SQL 2000 db by 50GB. Is it better to start sql
>>> in single
>>> user mode when doing such action?
>>
>>
>>
>>
>
Showing posts with label single. Show all posts
Showing posts with label single. Show all posts
Thursday, March 29, 2012
Sunday, March 11, 2012
DBCC INDEXDEFRAG
We are currently running SQL Server 2000 on single box for the last 2 years.
There were some performance issues on the database and after analysing I saw
that the database required defragmentation. So I executed DBCC INDEXDEFRAG
on all the tables and things went back to normal in terms of the
performance. After two days our application users complained about some un
expected results. After the analysis following is the information.
The table that has 3 columns
1. TarifTypeKey, LanguageKey and Description
2. There is a composite clustered index on TariffTypeKey and LanguageKey
3. The table returns only 5 rows in the following format
1. Download
2. Upgrade
3. Deletion
4. Force
5. Print
4. These values are returned back from the database from a Stored Procedure
and following is the code
SELECT TariffTypeKey, Description
FROM TariffTypesDescription
WHERE LanguageKey = 'EN'
The same query worked fine since the first day. But since I have executed
the script to defragment the indexes we get back results in the following
order
5. Print
3. Deletion
1. Download
2. Upgrade
4. Force
Nothing has changed in the stored procedure. I did some more research and
tried to break down the query into two parts
1. SELECT TariffTypeKey, Description
FROM TariffTypesDescription (until here we get the desired results)
2. WHERE LanguageKey = 'EN' (as soon as we use this the results are changed)
Kindly provide some of your knowledge on this as this is on the live
database. Please do not hesitate to contact me if you need further
information.
-Saj> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
The engine is free to produce the resultset in ANY ORDER if the query does
not have an order-by clause. Your assumption based on past experience is
the problem.|||Thanks a ton for everyone who replied to this question as I already fixed
the problem using ORDER BY clause but I was wondering how come the orders of
the rows changed.
I appreciate it.
"Sajid S. Malik" <sajid_malick@.yahoo.com> wrote in message
news:ucoSH0D$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We are currently running SQL Server 2000 on single box for the last 2
> years. There were some performance issues on the database and after
> analysing I saw that the database required defragmentation. So I executed
> DBCC INDEXDEFRAG on all the tables and things went back to normal in terms
> of the performance. After two days our application users complained about
> some un expected results. After the analysis following is the information.
> The table that has 3 columns
> 1. TarifTypeKey, LanguageKey and Description
> 2. There is a composite clustered index on TariffTypeKey and LanguageKey
> 3. The table returns only 5 rows in the following format
> 1. Download
> 2. Upgrade
> 3. Deletion
> 4. Force
> 5. Print
> 4. These values are returned back from the database from a Stored
> Procedure and following is the code
> SELECT TariffTypeKey, Description
> FROM TariffTypesDescription
> WHERE LanguageKey = 'EN'
> The same query worked fine since the first day. But since I have executed
> the script to defragment the indexes we get back results in the following
> order
> 5. Print
> 3. Deletion
> 1. Download
> 2. Upgrade
> 4. Force
> Nothing has changed in the stored procedure. I did some more research and
> tried to break down the query into two parts
> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
> Kindly provide some of your knowledge on this as this is on the live
> database. Please do not hesitate to contact me if you need further
> information.
> -Saj
>
There were some performance issues on the database and after analysing I saw
that the database required defragmentation. So I executed DBCC INDEXDEFRAG
on all the tables and things went back to normal in terms of the
performance. After two days our application users complained about some un
expected results. After the analysis following is the information.
The table that has 3 columns
1. TarifTypeKey, LanguageKey and Description
2. There is a composite clustered index on TariffTypeKey and LanguageKey
3. The table returns only 5 rows in the following format
1. Download
2. Upgrade
3. Deletion
4. Force
5. Print
4. These values are returned back from the database from a Stored Procedure
and following is the code
SELECT TariffTypeKey, Description
FROM TariffTypesDescription
WHERE LanguageKey = 'EN'
The same query worked fine since the first day. But since I have executed
the script to defragment the indexes we get back results in the following
order
5. Print
3. Deletion
1. Download
2. Upgrade
4. Force
Nothing has changed in the stored procedure. I did some more research and
tried to break down the query into two parts
1. SELECT TariffTypeKey, Description
FROM TariffTypesDescription (until here we get the desired results)
2. WHERE LanguageKey = 'EN' (as soon as we use this the results are changed)
Kindly provide some of your knowledge on this as this is on the live
database. Please do not hesitate to contact me if you need further
information.
-Saj> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
The engine is free to produce the resultset in ANY ORDER if the query does
not have an order-by clause. Your assumption based on past experience is
the problem.|||Thanks a ton for everyone who replied to this question as I already fixed
the problem using ORDER BY clause but I was wondering how come the orders of
the rows changed.
I appreciate it.
"Sajid S. Malik" <sajid_malick@.yahoo.com> wrote in message
news:ucoSH0D$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We are currently running SQL Server 2000 on single box for the last 2
> years. There were some performance issues on the database and after
> analysing I saw that the database required defragmentation. So I executed
> DBCC INDEXDEFRAG on all the tables and things went back to normal in terms
> of the performance. After two days our application users complained about
> some un expected results. After the analysis following is the information.
> The table that has 3 columns
> 1. TarifTypeKey, LanguageKey and Description
> 2. There is a composite clustered index on TariffTypeKey and LanguageKey
> 3. The table returns only 5 rows in the following format
> 1. Download
> 2. Upgrade
> 3. Deletion
> 4. Force
> 5. Print
> 4. These values are returned back from the database from a Stored
> Procedure and following is the code
> SELECT TariffTypeKey, Description
> FROM TariffTypesDescription
> WHERE LanguageKey = 'EN'
> The same query worked fine since the first day. But since I have executed
> the script to defragment the indexes we get back results in the following
> order
> 5. Print
> 3. Deletion
> 1. Download
> 2. Upgrade
> 4. Force
> Nothing has changed in the stored procedure. I did some more research and
> tried to break down the query into two parts
> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
> Kindly provide some of your knowledge on this as this is on the live
> database. Please do not hesitate to contact me if you need further
> information.
> -Saj
>
Thursday, March 8, 2012
DBCC INDEXDEFRAG
We are currently running SQL Server 2000 on single box for the last 2 years.
There were some performance issues on the database and after analysing I saw
that the database required defragmentation. So I executed DBCC INDEXDEFRAG
on all the tables and things went back to normal in terms of the
performance. After two days our application users complained about some un
expected results. After the analysis following is the information.
The table that has 3 columns
1. TarifTypeKey, LanguageKey and Description
2. There is a composite clustered index on TariffTypeKey and LanguageKey
3. The table returns only 5 rows in the following format
1. Download
2. Upgrade
3. Deletion
4. Force
5. Print
4. These values are returned back from the database from a Stored Procedure
and following is the code
SELECT TariffTypeKey, Description
FROM TariffTypesDescription
WHERE LanguageKey = 'EN'
The same query worked fine since the first day. But since I have executed
the script to defragment the indexes we get back results in the following
order
5. Print
3. Deletion
1. Download
2. Upgrade
4. Force
Nothing has changed in the stored procedure. I did some more research and
tried to break down the query into two parts
1. SELECT TariffTypeKey, Description
FROM TariffTypesDescription (until here we get the desired results)
2. WHERE LanguageKey = 'EN' (as soon as we use this the results are changed)
Kindly provide some of your knowledge on this as this is on the live
database. Please do not hesitate to contact me if you need further
information.
-Saj> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
The engine is free to produce the resultset in ANY ORDER if the query does
not have an order-by clause. Your assumption based on past experience is
the problem.|||Thanks a ton for everyone who replied to this question as I already fixed
the problem using ORDER BY clause but I was wondering how come the orders of
the rows changed.
I appreciate it.
"Sajid S. Malik" <sajid_malick@.yahoo.com> wrote in message
news:ucoSH0D$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We are currently running SQL Server 2000 on single box for the last 2
> years. There were some performance issues on the database and after
> analysing I saw that the database required defragmentation. So I executed
> DBCC INDEXDEFRAG on all the tables and things went back to normal in terms
> of the performance. After two days our application users complained about
> some un expected results. After the analysis following is the information.
> The table that has 3 columns
> 1. TarifTypeKey, LanguageKey and Description
> 2. There is a composite clustered index on TariffTypeKey and LanguageKey
> 3. The table returns only 5 rows in the following format
> 1. Download
> 2. Upgrade
> 3. Deletion
> 4. Force
> 5. Print
> 4. These values are returned back from the database from a Stored
> Procedure and following is the code
> SELECT TariffTypeKey, Description
> FROM TariffTypesDescription
> WHERE LanguageKey = 'EN'
> The same query worked fine since the first day. But since I have executed
> the script to defragment the indexes we get back results in the following
> order
> 5. Print
> 3. Deletion
> 1. Download
> 2. Upgrade
> 4. Force
> Nothing has changed in the stored procedure. I did some more research and
> tried to break down the query into two parts
> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
> Kindly provide some of your knowledge on this as this is on the live
> database. Please do not hesitate to contact me if you need further
> information.
> -Saj
>
There were some performance issues on the database and after analysing I saw
that the database required defragmentation. So I executed DBCC INDEXDEFRAG
on all the tables and things went back to normal in terms of the
performance. After two days our application users complained about some un
expected results. After the analysis following is the information.
The table that has 3 columns
1. TarifTypeKey, LanguageKey and Description
2. There is a composite clustered index on TariffTypeKey and LanguageKey
3. The table returns only 5 rows in the following format
1. Download
2. Upgrade
3. Deletion
4. Force
5. Print
4. These values are returned back from the database from a Stored Procedure
and following is the code
SELECT TariffTypeKey, Description
FROM TariffTypesDescription
WHERE LanguageKey = 'EN'
The same query worked fine since the first day. But since I have executed
the script to defragment the indexes we get back results in the following
order
5. Print
3. Deletion
1. Download
2. Upgrade
4. Force
Nothing has changed in the stored procedure. I did some more research and
tried to break down the query into two parts
1. SELECT TariffTypeKey, Description
FROM TariffTypesDescription (until here we get the desired results)
2. WHERE LanguageKey = 'EN' (as soon as we use this the results are changed)
Kindly provide some of your knowledge on this as this is on the live
database. Please do not hesitate to contact me if you need further
information.
-Saj> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
The engine is free to produce the resultset in ANY ORDER if the query does
not have an order-by clause. Your assumption based on past experience is
the problem.|||Thanks a ton for everyone who replied to this question as I already fixed
the problem using ORDER BY clause but I was wondering how come the orders of
the rows changed.
I appreciate it.
"Sajid S. Malik" <sajid_malick@.yahoo.com> wrote in message
news:ucoSH0D$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We are currently running SQL Server 2000 on single box for the last 2
> years. There were some performance issues on the database and after
> analysing I saw that the database required defragmentation. So I executed
> DBCC INDEXDEFRAG on all the tables and things went back to normal in terms
> of the performance. After two days our application users complained about
> some un expected results. After the analysis following is the information.
> The table that has 3 columns
> 1. TarifTypeKey, LanguageKey and Description
> 2. There is a composite clustered index on TariffTypeKey and LanguageKey
> 3. The table returns only 5 rows in the following format
> 1. Download
> 2. Upgrade
> 3. Deletion
> 4. Force
> 5. Print
> 4. These values are returned back from the database from a Stored
> Procedure and following is the code
> SELECT TariffTypeKey, Description
> FROM TariffTypesDescription
> WHERE LanguageKey = 'EN'
> The same query worked fine since the first day. But since I have executed
> the script to defragment the indexes we get back results in the following
> order
> 5. Print
> 3. Deletion
> 1. Download
> 2. Upgrade
> 4. Force
> Nothing has changed in the stored procedure. I did some more research and
> tried to break down the query into two parts
> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
> Kindly provide some of your knowledge on this as this is on the live
> database. Please do not hesitate to contact me if you need further
> information.
> -Saj
>
Sunday, February 19, 2012
DBCC CHECKDB Single User Mode
Does the database have to be in Single User Mode to execute the
statement listed below?
DBCC CHECKDB ('Database',Repair_Rebuild) WITH ALL_ERRORMSGS
Thanks,> Does the database have to be in Single User Mode to execute the
> statement listed below?
Based on the error message, I'd say yes'?
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.|||Yes it does. Before fixing any errors that checkdb has reported, you should
work out whay the errors occured (check the errorlog and event logs for
hardware messages for instance)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQXkCNUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> Based on the error message, I'd say yes'?
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
>
statement listed below?
DBCC CHECKDB ('Database',Repair_Rebuild) WITH ALL_ERRORMSGS
Thanks,> Does the database have to be in Single User Mode to execute the
> statement listed below?
Based on the error message, I'd say yes'?
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.|||Yes it does. Before fixing any errors that checkdb has reported, you should
work out whay the errors occured (check the errorlog and event logs for
hardware messages for instance)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQXkCNUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> Based on the error message, I'd say yes'?
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
>
DBCC CHECKDB Single User Mode
Does the database have to be in Single User Mode to execute the
statement listed below?
DBCC CHECKDB ('Database',Repair_Rebuild) WITH ALL_ERRORMSGS
Thanks,
> Does the database have to be in Single User Mode to execute the
> statement listed below?
Based on the error message, I'd say yes??
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
|||Yes it does. Before fixing any errors that checkdb has reported, you should
work out whay the errors occured (check the errorlog and event logs for
hardware messages for instance)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQXkCNUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> Based on the error message, I'd say yes??
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
>
statement listed below?
DBCC CHECKDB ('Database',Repair_Rebuild) WITH ALL_ERRORMSGS
Thanks,
> Does the database have to be in Single User Mode to execute the
> statement listed below?
Based on the error message, I'd say yes??
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
|||Yes it does. Before fixing any errors that checkdb has reported, you should
work out whay the errors occured (check the errorlog and event logs for
hardware messages for instance)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQXkCNUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> Based on the error message, I'd say yes??
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
>
DBCC CHECKDB Single User Mode
Does the database have to be in Single User Mode to execute the
statement listed below?
DBCC CHECKDB ('Database',Repair_Rebuild) WITH ALL_ERRORMSGS
Thanks,> Does the database have to be in Single User Mode to execute the
> statement listed below?
Based on the error message, I'd say yes'?
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.|||Yes it does. Before fixing any errors that checkdb has reported, you should
work out whay the errors occured (check the errorlog and event logs for
hardware messages for instance)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQXkCNUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> > Does the database have to be in Single User Mode to execute the
> > statement listed below?
> Based on the error message, I'd say yes'?
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
>
statement listed below?
DBCC CHECKDB ('Database',Repair_Rebuild) WITH ALL_ERRORMSGS
Thanks,> Does the database have to be in Single User Mode to execute the
> statement listed below?
Based on the error message, I'd say yes'?
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.|||Yes it does. Before fixing any errors that checkdb has reported, you should
work out whay the errors occured (check the errorlog and event logs for
hardware messages for instance)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQXkCNUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> > Does the database have to be in Single User Mode to execute the
> > statement listed below?
> Based on the error message, I'd say yes'?
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
>
dbcc checkdb on a large table
We have a 200 GB database with a single table that spans 36 partitions.
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
PrakashHi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBC
C
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
PrakashHi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBC
C
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
dbcc checkdb on a large table
We have a 200 GB database with a single table that spans 36 partitions.
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
Prakash
Hi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBCC
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
Prakash
Hi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBCC
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
dbcc checkdb on a large table
We have a 200 GB database with a single table that spans 36 partitions.
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
PrakashHi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBCC
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
PrakashHi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBCC
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
Subscribe to:
Posts (Atom)