Can I have SQL server clear a value after a certain amount of time?

I have an application that allows users to "reserve" a certain time and location for practice sessions. I want them to look at a web form that shows which times are available and then allows them to select an open slot and then submit this form with some personal information. My problem is that as soon as they select an open slot, other users should see that this slot is no longer available. On the other hand, if someone selects an open slot and then takes too long to type in their personal information, I want it somehow to kick them out and re-open this slot back up.

I know that I can create a field called 'status' in my database that gets turn to something like 'pending' as soon as the first user clicks on an open slot, but how would I turn this field back to the 'open' state if the user took too long to enter their data?

Any help would be surely appreciated.

Cheers,
AzFyou can store the time the page was requsted in some variable and check against some preset time like 5 min or 10 min...also when do you set the slot as reserved...as soon as the user clicks on the spot or at the end of the page after the user finished entering all the info...? i would suggest doing the latter...

hth

Can I GROUP BY aggregate Function (Like SUM)

Hello,
I column that calculated at run time in insert , can i gruop by this column,the new one that not exist yetyes, with a derived table, but why would you want to?

please show an example, using sample data to illustrate|||this is my case:

i have a table with a column datetime (I use to record time of calls) , i want to create table that hold data for each hour, so i round the column and want to group by this column in the same time|||you can round a datetime? please show your query|||This is the Query of round datetime

DATEADD(Hour, DATEDIFF(Hour, 0, cdrCallDate), 0)

You can change [hour] to day , minutes , second , etc...|||yes, you can GROUP BY that expression :)

and you cannot use second, it causes an overflow

:)|||thanks man for this information

Can I force printing?

I have a script or SP that takes a very long time to perform
multiple tasks, and after each one there is a PRINT statement
that shows the time and what was just accomplished, to help me
monitor what's happening and estimate how long it will take.
In a script, I can put a GO after each PRINT to cause the output
to appear immediately, but that's not possible inside an SP.
Instead, it seems the output goes to a buffer, and the buffer
is only output when a PRINT causes the buffer to become full.
Sometimes there is a long delay before the buffer fills. Is
there a way other than GO to cause immediate printing? (I
guess I could just use longer messages, and fill up the
buffer with every one, but that's not very elegant.)

Thanks,
Jim G
Countrywide Home LoansYou can use RAISERROR...WITH NOWAIT to cause messages to avoid buffering.
Messages will be displayed immediately when using a tool like Query
Analyzer:

RAISERROR ('my message', 0, 1) WITH NOWAIT

--
Hope this helps.

Dan Guzman
SQL Server MVP

<jim_geissman@.countrywide.com> wrote in message
news:1125531618.828313.53510@.z14g2000cwz.googlegro ups.com...
>I have a script or SP that takes a very long time to perform
> multiple tasks, and after each one there is a PRINT statement
> that shows the time and what was just accomplished, to help me
> monitor what's happening and estimate how long it will take.
> In a script, I can put a GO after each PRINT to cause the output
> to appear immediately, but that's not possible inside an SP.
> Instead, it seems the output goes to a buffer, and the buffer
> is only output when a PRINT causes the buffer to become full.
> Sometimes there is a long delay before the buffer fills. Is
> there a way other than GO to cause immediate printing? (I
> guess I could just use longer messages, and fill up the
> buffer with every one, but that's not very elegant.)
> Thanks,
> Jim G
> Countrywide Home Loans|||Thanks, Dan.

Jim Geissman

Can I find change log information for a DTS

Hi,
Is it possible to find the date n time when a particular DTS was changed. All that I can see is thecreation date of a DTS :(
Will appreciate your help.Hi, try right-clicking the package and select Versions. it could give you more info on package history. mojza|||Thanks mojza :) That helps and though it says 'create date' I can use this information as the last modification date.

Many thanks once again.

Can I delete the gather logs?

The gather logs are huge, full of mostly "success" messages, and are
massively slowing down our FT population time. Can I safely delete
them?
Absolutely, but something is wrong here. I think you are in debug mode.
Have a look at the following key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\G ather\SQLServer\SQL0000800005
- replacing SQL0000800005 with your actual catalog name. Make sure the
value of the logsuccesses key is 0.
If it isn't set it to 0 and restart MSSearch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Optiontrader1138" <seoconsultant12@.gmail.com> wrote in message
news:1174065973.703142.140190@.n76g2000hsh.googlegr oups.com...
> The gather logs are huge, full of mostly "success" messages, and are
> massively slowing down our FT population time. Can I safely delete
> them?
>
|||It was already zero ...
On Mar 16, 12:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> Absolutely, but something is wrong here. I think you are in debug mode.
> Have a look at the following key
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\G ather\SQLServer\SQL0000800005
> - replacing SQL0000800005 with your actual catalog name. Make sure the
> value of the logsuccesses key is 0.
> If it isn't set it to 0 and restart MSSearch.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Optiontrader1138" <seoconsultan...@.gmail.com> wrote in message
> news:1174065973.703142.140190@.n76g2000hsh.googlegr oups.com...
|||I stopped the FT service, deleted the main gather log and created an
empty file with the same filename. I then restarted the FT service. It
should've started where it left off, but it doesn't appear that's
happening.
The catalog was initially "idle", which I don't understand. It is set
to "change tracking in background", so I would have assumed it would
start automatically. But it didn't. I kicked off an incremental
population, and it appears that it's started over from the beginning.
In Perf Monitor, the "documents filtered" monitor is currently 0,
while "Waiting documents" is steadily climbing. The new gather file is
already at 627Mb and climbing.
This doesn't seem right. Any ideas?
On Mar 16, 12:25 pm, "Optiontrader1138" <seoconsultan...@.gmail.com>
wrote:[vbcol=seagreen]
> It was already zero ...
> On Mar 16, 12:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
>
>
|||You should have left it with change tracking. When it is idle it means it
has finished populating/indexing.
You did not need to re-create the gatherer log. I am still a little puzzled
as to why it is giving you all these status messages.
Could you attach the gatherer log here or email it to me offline. Don't post
it as it is hard for me to interpret all the hex.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Optiontrader1138" <seoconsultant12@.gmail.com> wrote in message
news:1174070015.812532.3280@.y66g2000hsf.googlegrou ps.com...
>I stopped the FT service, deleted the main gather log and created an
> empty file with the same filename. I then restarted the FT service. It
> should've started where it left off, but it doesn't appear that's
> happening.
> The catalog was initially "idle", which I don't understand. It is set
> to "change tracking in background", so I would have assumed it would
> start automatically. But it didn't. I kicked off an incremental
> population, and it appears that it's started over from the beginning.
> In Perf Monitor, the "documents filtered" monitor is currently 0,
> while "Waiting documents" is steadily climbing. The new gather file is
> already at 627Mb and climbing.
> This doesn't seem right. Any ideas?
>
> On Mar 16, 12:25 pm, "Optiontrader1138" <seoconsultan...@.gmail.com>
> wrote:
>
|||As much as I would love to send you the gather log, it is currently
1.17 Gb, so I don't think you'd appreciate me sending it!
"Change tracking in background" is still on. I checked the table and
it definitely was not finished populating when it was idle ... 90% of
the DB was still not indexed and was not showing up in my test
queries.
I think part of the confusion may be that the registry key that you
mentioned may only affect the notifier gather log (in my case, named
SQL0001200005.Ntfy1.gthr). This logfile is only 1k. The 1.17Gb file is
named SQL0001200005.Crwl2.gthr. This is just based on what I've been
able to google so far - let me know if I'm way off-base.
Thanks!!
On Mar 16, 12:51 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> You should have left it with change tracking. When it is idle it means it
> has finished populating/indexing.
> You did not need to re-create the gatherer log. I am still a little puzzled
> as to why it is giving you all these status messages.
> Could you attach the gatherer log here or email it to me offline. Don't post
> it as it is hard for me to interpret all the hex.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Optiontrader1138" <seoconsultan...@.gmail.com> wrote in message
> news:1174070015.812532.3280@.y66g2000hsf.googlegrou ps.com...
>
>
>
>
>
>
|||Can you export the registry key for this catalog and post it here?
Do other catalog exhibit the same behavior?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Optiontrader1138" <seoconsultant12@.gmail.com> wrote in message
news:1174071790.211949.76010@.l75g2000hse.googlegro ups.com...
> As much as I would love to send you the gather log, it is currently
> 1.17 Gb, so I don't think you'd appreciate me sending it!
> "Change tracking in background" is still on. I checked the table and
> it definitely was not finished populating when it was idle ... 90% of
> the DB was still not indexed and was not showing up in my test
> queries.
> I think part of the confusion may be that the registry key that you
> mentioned may only affect the notifier gather log (in my case, named
> SQL0001200005.Ntfy1.gthr). This logfile is only 1k. The 1.17Gb file is
> named SQL0001200005.Crwl2.gthr. This is just based on what I've been
> able to google so far - let me know if I'm way off-base.
> Thanks!!
> On Mar 16, 12:51 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
|||Sure - here it is:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005]
"LogDirectory"="E:\\SQLData\\SQL0001200005"
"StreamLogsDirectory"="D:\\Program Files\\Microsoft SQL Server\\MSSQL\
\FTData\\SQLServer\\GatherLogs"
"LogName"="E:\\SQLData\\SQL0001200005\\SQL00012000 05.Ntfy1.gthr"
"LogNumber"=dword:00000001
"NotificationLogCheckPoint"=hex:80,00,00,00,00,00, 00,00
"HistoryHashMapFile"="E:\\SQLData\\SQL00012000 05\
\SQL0001200005.Hash.gthr"
"DocIdMapFile"="E:\\SQLData\\SQL0001200005\\SQL000 1200005.Idm.gthr"
"NewCrawlNumber"=dword:00000003
"LogSuccess"=dword:00000000
"FollowComplexUrls"=dword:00000000
"DisableRecovery"=dword:00000000
"DisableRobotsExclusion"=dword:00000000
"NormalizeUrls"=dword:00000001
"CaseSensitiveUrls"=dword:00000000
"UseIncrementalCrawlDirIter"=dword:00000000
"ACContentSync"=dword:0000005a
"ACRejects"=dword:00000000
"ACTotal"=dword:00000000
"ACErrorEst"=dword:ffffffff
"ACErrors"=dword:00000000
"ACErrorSamples"=dword:00000000
"ACMaxNoAccess"=dword:00127500
"FilterAlways"=dword:00000000
"LazyCheckPointUpdateInterval"=dword:00015180
"EnableCheckPoint"=dword:00000000
"ACActiveProfile"=hex:ed,58,81,3c,ed,58,81,3c,ed,5 8,81,3c,ed,
58,81,3c,ed,58,81,\
3c,ed,58,81,3c,ed,58,81,3c,ed,58,81,3c,ed,58,81,3c ,ed,58,81,3c,ed,
58,81,3c,\
ed,58,81,3c,ed,58,81,3c,ed,58,81,3c,ed,58,81,3c,ed ,58,81,3c,ed,
58,81,3c,ed,\
58,81,3c,ed,58,81,3c,33,33,33,3f,
00,00,00,00,00,00,00,00,00,00,00,00,00,00,\
00,00,00,00,00,00
"ACTrainingProfile"=hex:c6,89,29,34,c6,89,29,34,c6 ,89,29,34,c6,89,29,34,c6,89,\
29,34,c6,89,29,34,c6,89,29,34,c6,89,29,34,c6,89,29 ,34,c6,89,29,34,c6,89,29,\
34,c6,89,29,34,c6,89,29,34,c6,89,29,34,c6,89,29,34 ,c6,89,29,34,c6,89,29,34,\
c6,89,29,34,c6,89,29,34,8a,e1,ea,
36,00,00,00,00,00,00,00,00,00,00,00,00,00,\
00,00,00,00,00,00,00
"LogExcluded"=dword:00000000
"LogDisabled"=dword:00000000
"ForceFullCrawl"=dword:00000000
"PausedExternal"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Crawls]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Crawls\2]
"CrawlType"=dword:00000002
"InProgress"=dword:00000001
"DoneAddingCrawlSeeds"=dword:00000001
"LogName"="E:\\SQLData\\SQL0001200005\\SQL00012000 05.Crwl2.gthr"
"CheckPoint"=hex:80,00,00,00,00,00,00,00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Extensions]
"IncludedExtensions"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Extensions\ExtensionList]
"0"="htm"
"1"="html"
"2"="txt"
"3"="asp"
"4"="xls"
"5"="doc"
"6"="ppt"
"7"="exch"
"8"="xml"
"9"="eml"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Mappings]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Protocols]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Protocols\File]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Protocols\File\0]
"Included"=dword:00000001
"ExtExclusionsUsed"=dword:00000001
"PrefixName"=""
"ProgIdHandler"="MSSearch.FileHandler.1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Protocols\Http]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Protocols\Http\0]
"Included"=dword:00000001
"ExtExclusionsUsed"=dword:00000001
"PrefixName"=""
"ProgIdHandler"="MSSearch.HttpHandler.1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Protocols\MSSQL75]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Protocols\MSSQL75\0]
"Included"=dword:00000001
"ExtExclusionsUsed"=dword:00000001
"PrefixName"=""
"ProgIdHandler"="sqlfth75.sqlfth75.1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Protocols\OleDb]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Protocols\OleDb\0]
"Included"=dword:00000001
"ExtExclusionsUsed"=dword:00000001
"PrefixName"=""
"ProgIdHandler"="MSSearch.OleDbHandler.1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\Sites]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\StartPages]
"NewStartPageIdentifier"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\StartPages\0]
"URL"="MSSQL75://SQLServer/77bfcb91"
"HostDepth"=dword:00000000
"EnumerationDepth"=dword:ffffffff
"FollowDirectories"=dword:00000001
"StartPageIdentifier"=dword:00000000
"CrawlNumberInProgress"=dword:00000002
"CrawlNumberScheduled"=dword:ffffffff
"ForceFullCrawl"=dword:00000000
"LastCrawlStopped"=dword:00000000
"Type"=dword:00000002
"CrawlControl"=dword:00000000
"LastCrawlType"=dword:00000002
"IncludeInProjectCrawls"=dword:00000001
"LastCrawlTime"=hex:86,05,c1,a7,f8,67,c7,01
"NotificationHRes"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
\SQL0001200005\StreamLog]
"CurrentStreamLog"=dword:00000004
"MaxLogs"=dword:00000005
"StreamLogCount"=dword:00000003
|||It looks good. I am not sure what is going on, other than to say this looks
abnormal.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Optiontrader1138" <seoconsultant12@.gmail.com> wrote in message
news:1174073637.792740.132220@.o5g2000hsb.googlegro ups.com...
> Sure - here it is:
> Windows Registry Editor Version 5.00
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005]
> "LogDirectory"="E:\\SQLData\\SQL0001200005"
> "StreamLogsDirectory"="D:\\Program Files\\Microsoft SQL Server\\MSSQL\
> \FTData\\SQLServer\\GatherLogs"
> "LogName"="E:\\SQLData\\SQL0001200005\\SQL00012000 05.Ntfy1.gthr"
> "LogNumber"=dword:00000001
> "NotificationLogCheckPoint"=hex:80,00,00,00,00,00, 00,00
> "HistoryHashMapFile"="E:\\SQLData\\SQL00012000 05\
> \SQL0001200005.Hash.gthr"
> "DocIdMapFile"="E:\\SQLData\\SQL0001200005\\SQL000 1200005.Idm.gthr"
> "NewCrawlNumber"=dword:00000003
> "LogSuccess"=dword:00000000
> "FollowComplexUrls"=dword:00000000
> "DisableRecovery"=dword:00000000
> "DisableRobotsExclusion"=dword:00000000
> "NormalizeUrls"=dword:00000001
> "CaseSensitiveUrls"=dword:00000000
> "UseIncrementalCrawlDirIter"=dword:00000000
> "ACContentSync"=dword:0000005a
> "ACRejects"=dword:00000000
> "ACTotal"=dword:00000000
> "ACErrorEst"=dword:ffffffff
> "ACErrors"=dword:00000000
> "ACErrorSamples"=dword:00000000
> "ACMaxNoAccess"=dword:00127500
> "FilterAlways"=dword:00000000
> "LazyCheckPointUpdateInterval"=dword:00015180
> "EnableCheckPoint"=dword:00000000
> "ACActiveProfile"=hex:ed,58,81,3c,ed,58,81,3c,ed,5 8,81,3c,ed,
> 58,81,3c,ed,58,81,\
> 3c,ed,58,81,3c,ed,58,81,3c,ed,58,81,3c,ed,58,81,3c ,ed,58,81,3c,ed,
> 58,81,3c,\
> ed,58,81,3c,ed,58,81,3c,ed,58,81,3c,ed,58,81,3c,ed ,58,81,3c,ed,
> 58,81,3c,ed,\
> 58,81,3c,ed,58,81,3c,33,33,33,3f,
> 00,00,00,00,00,00,00,00,00,00,00,00,00,00,\
> 00,00,00,00,00,00
> "ACTrainingProfile"=hex:c6,89,29,34,c6,89,29,34,c6 ,89,29,34,c6,89,29,34,c6,89,\
> 29,34,c6,89,29,34,c6,89,29,34,c6,89,29,34,c6,89,29 ,34,c6,89,29,34,c6,89,29,\
> 34,c6,89,29,34,c6,89,29,34,c6,89,29,34,c6,89,29,34 ,c6,89,29,34,c6,89,29,34,\
> c6,89,29,34,c6,89,29,34,8a,e1,ea,
> 36,00,00,00,00,00,00,00,00,00,00,00,00,00,\
> 00,00,00,00,00,00,00
> "LogExcluded"=dword:00000000
> "LogDisabled"=dword:00000000
> "ForceFullCrawl"=dword:00000000
> "PausedExternal"=dword:00000000
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Crawls]
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Crawls\2]
> "CrawlType"=dword:00000002
> "InProgress"=dword:00000001
> "DoneAddingCrawlSeeds"=dword:00000001
> "LogName"="E:\\SQLData\\SQL0001200005\\SQL00012000 05.Crwl2.gthr"
> "CheckPoint"=hex:80,00,00,00,00,00,00,00
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Extensions]
> "IncludedExtensions"=dword:00000001
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Extensions\ExtensionList]
> "0"="htm"
> "1"="html"
> "2"="txt"
> "3"="asp"
> "4"="xls"
> "5"="doc"
> "6"="ppt"
> "7"="exch"
> "8"="xml"
> "9"="eml"
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Mappings]
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Protocols]
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Protocols\File]
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Protocols\File\0]
> "Included"=dword:00000001
> "ExtExclusionsUsed"=dword:00000001
> "PrefixName"=""
> "ProgIdHandler"="MSSearch.FileHandler.1"
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Protocols\Http]
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Protocols\Http\0]
> "Included"=dword:00000001
> "ExtExclusionsUsed"=dword:00000001
> "PrefixName"=""
> "ProgIdHandler"="MSSearch.HttpHandler.1"
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Protocols\MSSQL75]
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Protocols\MSSQL75\0]
> "Included"=dword:00000001
> "ExtExclusionsUsed"=dword:00000001
> "PrefixName"=""
> "ProgIdHandler"="sqlfth75.sqlfth75.1"
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Protocols\OleDb]
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Protocols\OleDb\0]
> "Included"=dword:00000001
> "ExtExclusionsUsed"=dword:00000001
> "PrefixName"=""
> "ProgIdHandler"="MSSearch.OleDbHandler.1"
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\Sites]
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\StartPages]
> "NewStartPageIdentifier"=dword:00000001
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\StartPages\0]
> "URL"="MSSQL75://SQLServer/77bfcb91"
> "HostDepth"=dword:00000000
> "EnumerationDepth"=dword:ffffffff
> "FollowDirectories"=dword:00000001
> "StartPageIdentifier"=dword:00000000
> "CrawlNumberInProgress"=dword:00000002
> "CrawlNumberScheduled"=dword:ffffffff
> "ForceFullCrawl"=dword:00000000
> "LastCrawlStopped"=dword:00000000
> "Type"=dword:00000002
> "CrawlControl"=dword:00000000
> "LastCrawlType"=dword:00000002
> "IncludeInProjectCrawls"=dword:00000001
> "LastCrawlTime"=hex:86,05,c1,a7,f8,67,c7,01
> "NotificationHRes"=dword:00000000
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gather\SQLServer
> \SQL0001200005\StreamLog]
> "CurrentStreamLog"=dword:00000004
> "MaxLogs"=dword:00000005
> "StreamLogCount"=dword:00000003
>

can I combine several partitions into one in a cube?

Hi,
I got 4 partitions corresponding to four fact tables.
Does it affect cube query performance? most of time, i need to access all
partitions.
If so, how can I combine them into one partition?
Thanks,
GuangmingIf you are using MOLAP storage (which should be mostly the case) then the
underlying relational fact tables are never touched during a query.
Partitioning is very important for smooth even response times. See the AS
Performance Guide here:
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
when it discusses partitioning. It is also discussed extensively in the SQL
Server 2000 Resource Kit which has an entire chapter on partitioning.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
> Hi,
> I got 4 partitions corresponding to four fact tables.
> Does it affect cube query performance? most of time, i need to access all
> partitions.
> If so, how can I combine them into one partition?
> Thanks,
>
> Guangming|||In management studio, go to cube - partition, right click you'll get 'merge
partition ...'.
it seems that if there are multiple partitions in one measure group, you can
merge them. If partitions are for different measure groups, you can not.
Most time, merging is not necessary, I guess.
Guangming
"Dave Wickert [MSFT]" wrote:

> If you are using MOLAP storage (which should be mostly the case) then the
> underlying relational fact tables are never touched during a query.
> Partitioning is very important for smooth even response times. See the AS
> Performance Guide here:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx[/ur
l]
> when it discusses partitioning. It is also discussed extensively in the SQ
L
> Server 2000 Resource Kit which has an entire chapter on partitioning.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.co
m>
> wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com..
.
>
>|||
> In management studio, go to cube - partition, right click you'll get 'merg
e
> partition ...'.
> it seems that if there are multiple partitions in one measure group, you c
an
> merge them. If partitions are for different measure groups, you can not.
Usually you have different measure groups, because the tables have
different columns. So by definition you have a different structure in
the partition therefore it does not make sense to merge them
If you do have multiple measure groups of identically structured fact
tables then they probably should be setup as multiple partitions under
the on measure group, rather then multiple measure groups each with a
single partition.

> Most time, merging is not necessary, I guess.
In AS2k, the server would read and resolve the data from each partition
using a separate thread so having multiple partitions can give you a
performance boost, especially on a multi processor machine with a fast
disk sub system.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbellsql

can I combine several partitions into one in a cube?

Hi,
I got 4 partitions corresponding to four fact tables.
Does it affect cube query performance? most of time, i need to access all
partitions.
If so, how can I combine them into one partition?
Thanks,
Guangming
If you are using MOLAP storage (which should be mostly the case) then the
underlying relational fact tables are never touched during a query.
Partitioning is very important for smooth even response times. See the AS
Performance Guide here:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
when it discusses partitioning. It is also discussed extensively in the SQL
Server 2000 Resource Kit which has an entire chapter on partitioning.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
> Hi,
> I got 4 partitions corresponding to four fact tables.
> Does it affect cube query performance? most of time, i need to access all
> partitions.
> If so, how can I combine them into one partition?
> Thanks,
>
> Guangming
|||In management studio, go to cube - partition, right click you'll get 'merge
partition ...'.
it seems that if there are multiple partitions in one measure group, you can
merge them. If partitions are for different measure groups, you can not.
Most time, merging is not necessary, I guess.
Guangming
"Dave Wickert [MSFT]" wrote:

> If you are using MOLAP storage (which should be mostly the case) then the
> underlying relational fact tables are never touched during a query.
> Partitioning is very important for smooth even response times. See the AS
> Performance Guide here:
> http://www.microsoft.com/technet/pro.../ansvcspg.mspx
> when it discusses partitioning. It is also discussed extensively in the SQL
> Server 2000 Resource Kit which has an entire chapter on partitioning.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
> wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
>
>
|||
> In management studio, go to cube - partition, right click you'll get 'merge
> partition ...'.
> it seems that if there are multiple partitions in one measure group, you can
> merge them. If partitions are for different measure groups, you can not.
Usually you have different measure groups, because the tables have
different columns. So by definition you have a different structure in
the partition therefore it does not make sense to merge them
If you do have multiple measure groups of identically structured fact
tables then they probably should be setup as multiple partitions under
the on measure group, rather then multiple measure groups each with a
single partition.

> Most time, merging is not necessary, I guess.
In AS2k, the server would read and resolve the data from each partition
using a separate thread so having multiple partitions can give you a
performance boost, especially on a multi processor machine with a fast
disk sub system.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Can I change from Integrated Security?

When creating my database, I initially set it up to use Integrated Security (Windows Login), however I am having a hard time logging into the database on a consistent basis using this method. Can I now change to a specified user id and password? How would I do that? Would I lose any data in my database? Does the user id and password need to be the same user id and password used when logging into SQL Server?

Thank you.

The Authetication method is global to the server and not specific to the database.

When you change your authentication to mixed mode

The SA account becomes the most powerful account in the Sql server box

and can login in to all the databases in the server by default.

there are no data loss in the change.

reset the password of your SA account and makeuse of it.

|||

You can change the SQL Server security model to allow a SQL Server login

In the Object Explorer window, right-click on the Server, and then click on the [Security] page. Change to SQL Server and Windows Authentication mode. Then click [OK].

However, it is not conisdered a good practice to use the [sa] account. It represents a significant security risk. It is preferred to create a login for your purposes, and provide just enough permissions for that login to do its work.

Lesson 7: Understanding Security and Network Connectivity -from this series may be of help in understanding how to use SQL Server security.

SQL Server 2005 Express Video Learning
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

Can I change a datasource's properties at report execution time?

I will be running reports on a shared database server. The database server
supports one main application with many environments for different user
groups. Each groupsâ' data is kept in a different database instance.
I want to deploy my reports in a single location on the shared database
server to simplify installing reporting services and deploying reports.
When a user requests a on-demand report I want the report to pull the data
from the correct SQL Server instance.
Can I to change the reportâ's shared datasource â'Connection Stringâ' to the
userâ's database instance. I will be using SOAP to invoke the reports.
Are there other ways to accomplish my intentions?
I am using SQL Server 2000 Reporting Services Developer Edition with SP1 and
SQL Server 2000 Standard Edition with service packs 1, 2, 3, and 3a applied.Hi,
You may want to use 'SetReportDataSources' to switch between datasources.
More information about this method can be found from the MSDN site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
rsp_prog_soapapi_intro_0dnq.asp
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Can I change a datasource's properties at report execution
time?
>thread-index: AcUz8ztCgxbpG2rJQdCyH/tlSmdIXw==>X-WBNR-Posting-Host: 192.85.47.1
>From: =?Utf-8?B?dGV4YXMzNDYw?= <texas3460@.noemail.nospam>
>Subject: Can I change a datasource's properties at report execution time?
>Date: Mon, 28 Mar 2005 16:07:01 -0800
>Lines: 17
>Message-ID: <26693252-7562-4848-9FD1-9D529F3D40F9@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 8bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:46323
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I will be running reports on a shared database server. The database
server
>supports one main application with many environments for different user
>groups. Each groupsâ' data is kept in a different database instance.
>I want to deploy my reports in a single location on the shared database
>server to simplify installing reporting services and deploying reports.
>When a user requests a on-demand report I want the report to pull the data
>from the correct SQL Server instance.
>Can I to change the reportâ's shared datasource â'Connection Stringâ'
to the
>userâ's database instance. I will be using SOAP to invoke the reports.
>Are there other ways to accomplish my intentions?
>I am using SQL Server 2000 Reporting Services Developer Edition with SP1
and
>SQL Server 2000 Standard Edition with service packs 1, 2, 3, and 3a
applied.
>

Can I backup SQl server 2005 databases with Shadow Copies (VSS)

Good Day,
I would like to create shadow copies of our SQL databases as a way of
providing temporary point in time backups. I was thinking of doing the
following:-
1) Hidden share of the DB folders (mdf/ldf).
2) Apply NTFS as precaution.
3) Store shadow copies on external drive.
4) Create copies every 30/60 minutes.
Would this cause a problem with the SQL server 2005? Would a restoration be
as simple as restoring the mdf & ldf files? It is a live server and I would
not want to cause any probs so thought I would look/check around. I have
googled but can only find pages with people using VSS programatically.
Kind Regards,
Paul Johnson.
--
<a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
target="_blank"><img border = "0"
src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help from
Paul Johnson!" /></a>"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:e7f$JL8nIHA.2292@.TK2MSFTNGP03.phx.gbl...
> Good Day,
> I would like to create shadow copies of our SQL databases as a way of
> providing temporary point in time backups. I was thinking of doing the
> following:-
> 1) Hidden share of the DB folders (mdf/ldf).
> 2) Apply NTFS as precaution.
> 3) Store shadow copies on external drive.
> 4) Create copies every 30/60 minutes.
> Would this cause a problem with the SQL server 2005? Would a restoration
> be as simple as restoring the mdf & ldf files? It is a live server and I
> would not want to cause any probs so thought I would look/check around. I
> have googled but can only find pages with people using VSS
> programatically.
> Kind Regards,
> Paul Johnson.
>
> --
> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
> target="_blank"><img border = "0"
> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help from
> Paul Johnson!" /></a>
Hi Paul
As I understand it, you would need to implement your backup solution using
the VSS API or an application that uses it. NTBackup seems to only be able
to cope with databases that are using SIMPLE recovery mode.
Without backing up the logs, you will not have any ability to rollforward
your transactions, therefore your solution is going to incorporate some data
loss.
John|||I have not looked into shadow copy with SQL Server closely to be able to
comment on your proposed approach intelligently. But one thing I do know is
that your proposed approach is not a common one. So unless you are doing a
POC or just exploring, I'd suggest you don't go there since there are other
much more 'mature' approach to meeting your requirements.
Linchi
"Paul Johnson" wrote:
> Good Day,
> I would like to create shadow copies of our SQL databases as a way of
> providing temporary point in time backups. I was thinking of doing the
> following:-
> 1) Hidden share of the DB folders (mdf/ldf).
> 2) Apply NTFS as precaution.
> 3) Store shadow copies on external drive.
> 4) Create copies every 30/60 minutes.
> Would this cause a problem with the SQL server 2005? Would a restoration be
> as simple as restoring the mdf & ldf files? It is a live server and I would
> not want to cause any probs so thought I would look/check around. I have
> googled but can only find pages with people using VSS programatically.
> Kind Regards,
> Paul Johnson.
>
> --
> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
> target="_blank"><img border = "0"
> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help from
> Paul Johnson!" /></a>
>
>|||Good Day Linchi,
Thank you for your reply.
I was hoping to avoid using a backup program to do this Shadow Copies offers
exactly what I want and it works in the background. I guess I will have to
use our regular backup software.
Thanks once again.
Kind Regards,
Paul Johnson.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:99C5AB60-74B5-4166-A943-C86288A92F1C@.microsoft.com...
>I have not looked into shadow copy with SQL Server closely to be able to
> comment on your proposed approach intelligently. But one thing I do know
> is
> that your proposed approach is not a common one. So unless you are doing a
> POC or just exploring, I'd suggest you don't go there since there are
> other
> much more 'mature' approach to meeting your requirements.
> Linchi
> "Paul Johnson" wrote:
>> Good Day,
>> I would like to create shadow copies of our SQL databases as a way of
>> providing temporary point in time backups. I was thinking of doing the
>> following:-
>> 1) Hidden share of the DB folders (mdf/ldf).
>> 2) Apply NTFS as precaution.
>> 3) Store shadow copies on external drive.
>> 4) Create copies every 30/60 minutes.
>> Would this cause a problem with the SQL server 2005? Would a restoration
>> be
>> as simple as restoring the mdf & ldf files? It is a live server and I
>> would
>> not want to cause any probs so thought I would look/check around. I have
>> googled but can only find pages with people using VSS programatically.
>> Kind Regards,
>> Paul Johnson.
>>
>> --
>> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
>> target="_blank"><img border = "0"
>> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help
>> from
>> Paul Johnson!" /></a>
>>|||Thank you for your reply.
I was hoping to avoid using a backup program to do this. Shadow Copies
offers exactly what I want and it works in the background. I guess I will
have to use our regular backup software or just get SQL server to create
backups to the drive on a schedule.
Thanks once again.
Kind Regards,
Paul Johnson.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u3Ktnr8nIHA.1768@.TK2MSFTNGP03.phx.gbl...
> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
> news:e7f$JL8nIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Good Day,
>> I would like to create shadow copies of our SQL databases as a way of
>> providing temporary point in time backups. I was thinking of doing the
>> following:-
>> 1) Hidden share of the DB folders (mdf/ldf).
>> 2) Apply NTFS as precaution.
>> 3) Store shadow copies on external drive.
>> 4) Create copies every 30/60 minutes.
>> Would this cause a problem with the SQL server 2005? Would a restoration
>> be as simple as restoring the mdf & ldf files? It is a live server and I
>> would not want to cause any probs so thought I would look/check around. I
>> have googled but can only find pages with people using VSS
>> programatically.
>> Kind Regards,
>> Paul Johnson.
>>
>> --
>> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
>> target="_blank"><img border = "0"
>> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help
>> from Paul Johnson!" /></a>
> Hi Paul
> As I understand it, you would need to implement your backup solution using
> the VSS API or an application that uses it. NTBackup seems to only be able
> to cope with databases that are using SIMPLE recovery mode.
> Without backing up the logs, you will not have any ability to rollforward
> your transactions, therefore your solution is going to incorporate some
> data loss.
> John
>|||"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:e7f$JL8nIHA.2292@.TK2MSFTNGP03.phx.gbl...
> Good Day,
> I would like to create shadow copies of our SQL databases as a way of
> providing temporary point in time backups. I was thinking of doing the
> following:-
Why not use normal SQL Server backups?
Or if you have Enterprise you can also take snapshots.
> 1) Hidden share of the DB folders (mdf/ldf).
> 2) Apply NTFS as precaution.
> 3) Store shadow copies on external drive.
> 4) Create copies every 30/60 minutes.
> Would this cause a problem with the SQL server 2005? Would a restoration
> be as simple as restoring the mdf & ldf files? It is a live server and I
> would not want to cause any probs so thought I would look/check around. I
> have googled but can only find pages with people using VSS
> programatically.
> Kind Regards,
> Paul Johnson.
>
> --
> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
> target="_blank"><img border = "0"
> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help from
> Paul Johnson!" /></a>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:%23z0$3F%23nIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Good Day Linchi,
> Thank you for your reply.
> I was hoping to avoid using a backup program to do this Shadow Copies
> offers exactly what I want and it works in the background. I guess I will
> have to use our regular backup software.
Unless your regular backup software is SQL Server aware, I would not go down
this route.
> Thanks once again.
> Kind Regards,
> Paul Johnson.
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:99C5AB60-74B5-4166-A943-C86288A92F1C@.microsoft.com...
>>I have not looked into shadow copy with SQL Server closely to be able to
>> comment on your proposed approach intelligently. But one thing I do know
>> is
>> that your proposed approach is not a common one. So unless you are doing
>> a
>> POC or just exploring, I'd suggest you don't go there since there are
>> other
>> much more 'mature' approach to meeting your requirements.
>> Linchi
>> "Paul Johnson" wrote:
>> Good Day,
>> I would like to create shadow copies of our SQL databases as a way of
>> providing temporary point in time backups. I was thinking of doing the
>> following:-
>> 1) Hidden share of the DB folders (mdf/ldf).
>> 2) Apply NTFS as precaution.
>> 3) Store shadow copies on external drive.
>> 4) Create copies every 30/60 minutes.
>> Would this cause a problem with the SQL server 2005? Would a restoration
>> be
>> as simple as restoring the mdf & ldf files? It is a live server and I
>> would
>> not want to cause any probs so thought I would look/check around. I have
>> googled but can only find pages with people using VSS programatically.
>> Kind Regards,
>> Paul Johnson.
>>
>> --
>> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
>> target="_blank"><img border = "0"
>> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help
>> from
>> Paul Johnson!" /></a>
>>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

can dbcc showcontig slow things down/ lock tables cont'd

Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that
they will be locked for a shorter period of time?
TIA, ChrisR
Yes. Shared locks are put up when you run it. Try using WITH FAST and do
only one table at a time. You get less info, but it's the stuff you need.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"ChrisR" <noemail@.bla.com> wrote in message
news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
sql2k sp3a
Not really any more to ask.
TIA, ChrisR" A fast scan does not read the leaf or data level pages of the index. "
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_46cn.asp
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ChrisR" <noemail@.bla.com> wrote in message
news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that
> they will be locked for a shorter period of time?
> TIA, ChrisR
>
>
> Yes. Shared locks are put up when you run it. Try using WITH FAST and do
> only one table at a time. You get less info, but it's the stuff you need.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
> sql2k sp3a
> Not really any more to ask.
> TIA, ChrisR
>
>|||Thanks Adam. I saw that, and the way I read it is that it won't lock my
tables up. But before I fire this off in production during the middle of the
day, I wanted to confirm my thoughts?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCIS9nMqFHA.208@.TK2MSFTNGP10.phx.gbl...
>" A fast scan does not read the leaf or data level pages of the index. "
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_46cn.asp
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
>> Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that
>> they will be locked for a shorter period of time?
>> TIA, ChrisR
>>
>>
>> Yes. Shared locks are put up when you run it. Try using WITH FAST and
>> do
>> only one table at a time. You get less info, but it's the stuff you
>> need.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
>> sql2k sp3a
>> Not really any more to ask.
>> TIA, ChrisR
>>
>|||FAST makes the operation faster as the leaf level doesn't have to be read. As for locking FAST
doesn't make a difference (table S lock). For non-heaps, you will have table IS lock instead when
using FAST.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <noemail@.bla.com> wrote in message news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that they will be locked for a
> shorter period of time?
> TIA, ChrisR
>
>
> Yes. Shared locks are put up when you run it. Try using WITH FAST and do
> only one table at a time. You get less info, but it's the stuff you need.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
> sql2k sp3a
> Not really any more to ask.
> TIA, ChrisR
>
>|||It will still acquire shared locks.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ChrisR" <noemail@.bla.com> wrote in message
news:umO5mqMqFHA.1028@.TK2MSFTNGP09.phx.gbl...
> Thanks Adam. I saw that, and the way I read it is that it won't lock my
> tables up. But before I fire this off in production during the middle of
the
> day, I wanted to confirm my thoughts?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCIS9nMqFHA.208@.TK2MSFTNGP10.phx.gbl...
> >" A fast scan does not read the leaf or data level pages of the index. "
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_46cn.asp
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "ChrisR" <noemail@.bla.com> wrote in message
> > news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
> >> Thanks Tom. Does WITH FAST mean that the tables won't be locked, or
that
> >> they will be locked for a shorter period of time?
> >>
> >> TIA, ChrisR
> >>
> >>
> >>
> >>
> >>
> >> Yes. Shared locks are put up when you run it. Try using WITH FAST and
> >> do
> >> only one table at a time. You get less info, but it's the stuff you
> >> need.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >> .
> >> "ChrisR" <noemail@.bla.com> wrote in message
> >> news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
> >> sql2k sp3a
> >>
> >> Not really any more to ask.
> >>
> >> TIA, ChrisR
> >>
> >>
> >>
> >
> >
>|||Oops, a mistake in my post:
For heaps, FAST doesn't make difference (S table lock).
For non-heaps, FAST takes table IS instead of table S.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:OJs0rsMqFHA.3576@.TK2MSFTNGP09.phx.gbl...
> FAST makes the operation faster as the leaf level doesn't have to be read. As for locking FAST
> doesn't make a difference (table S lock). For non-heaps, you will have table IS lock instead when
> using FAST.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ChrisR" <noemail@.bla.com> wrote in message news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
>> Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that they will be locked for
>> a shorter period of time?
>> TIA, ChrisR
>>
>>
>> Yes. Shared locks are put up when you run it. Try using WITH FAST and do
>> only one table at a time. You get less info, but it's the stuff you need.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
>> sql2k sp3a
>> Not really any more to ask.
>> TIA, ChrisR
>>
>

can dbcc indexdefrag reindex the whole database in one time?

Hi,
Normally i run the DBCC INDEXDEFRAG one table in one time, can run
all the tables in one time?
--
Thank you very much!
Best regards,
FlorenceHi,
This gives DBCC DBREINDEX. You can use this during off-peak hrs, as this is
not an online re-indexing operation. If you still want to use DBCC
INDEXDEFRAG, look at the usage of the same in BOL, and replace it with DBCC
DBREINDEX in given script.
/* Re-indexes the specified database */
CREATE PROCEDURE scr_defrag_database
@.dbname nvarchar(256)
AS
BEGIN
-- Quote the database name with brackets
DECLARE @.quoteddbname nvarchar(256)
SET @.quoteddbname = quotename( @.dbname )
-- The outer EXEC is so we can do USE, not allowed in stored procs
-- The inner EXEC does the actual reindex on each table in the
-- specified database
EXEC('
USE '+ @.quoteddbname +'
DECLARE @.stablename sysname
DECLARE PKMS_Tables CURSOR LOCAL FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' ORDER BY 1
OPEN PKMS_Tables
FETCH NEXT FROM PKMS_Tables INTO @.stablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.stablename = quotename(@.stablename, ''[]'')
EXEC('' DBCC DBREINDEX ( ''+@.stablename+'') WITH NO_INFOMSGS'')
FETCH NEXT FROM PKMS_Tables INTO @.stablename
END
CLOSE PKMS_Tables')
END
--
Thanks
Yogish|||CREATE PROC ukug2_op_defrag
@.is_reindex char(1) = 'Y'
AS
BEGIN
SET NOCOUNT ON
-- Basis of script taken from BOL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE tables_cur CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tables_cur
DECLARE @.ObjectName sysname
FETCH NEXT FROM tables_cur INTO @.ObjectName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH COUNT_ROWS,
NO_INFOMSGS' )
INSERT #fraglist
EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
FETCH NEXT FROM tables_cur INTO @.ObjectName
END
CLOSE tables_cur
DEALLOCATE tables_cur
SELECT *
FROM #fraglist fo
WHERE LogicalFrag > 30 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then don't do
anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
IF @.is_reindex = 'Y'
BEGIN
DECLARE defrag_cur CURSOR FOR
SELECT ObjectName,
IndexName
FROM #fraglist fo
WHERE LogicalFrag > 30 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then
don't do anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
DECLARE @.IndexName sysname
DECLARE @.sql varchar(1000)
OPEN defrag_cur
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
indexname=' + @.IndexName
SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', '
+ @.IndexName + ')'
PRINT @.sql
EXEC( @.sql )
PRINT 'Complete.'
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
END
DEALLOCATE defrag_cur
END
DROP TABLE #fraglist
END
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||This is a very timely thread for me as I'm just getting ready to implement a
SP that is based on the BOL sp.
I guess I'm confused by this a bit. From what I read in BOL in SQL 2000
DBCC INDEXDEFRAG will defragment clustered indexes. The SP you created from
the BOL base is a bit different and I guess I just want to know if the one
if BOL is incorrect or just different.
It determines the indexes to defrag based on the results of:
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
if you wanted to exclude clustered indexes I suppose you would be able to
modify to:
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM jdit_fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
AND INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') = 0
Thanks
Ivan
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:%23xN31fA9EHA.2112@.TK2MSFTNGP14.phx.gbl...
> CREATE PROC ukug2_op_defrag
> @.is_reindex char(1) = 'Y'
> AS
> BEGIN
> SET NOCOUNT ON
> -- Basis of script taken from BOL
> CREATE TABLE #fraglist (
> ObjectName CHAR (255),
> ObjectId INT,
> IndexName CHAR (255),
> IndexId INT,
> Lvl INT,
> CountPages INT,
> CountRows INT,
> MinRecSize INT,
> MaxRecSize INT,
> AvgRecSize INT,
> ForRecCount INT,
> Extents INT,
> ExtentSwitches INT,
> AvgFreeBytes INT,
> AvgPageDensity INT,
> ScanDensity DECIMAL,
> BestCount INT,
> ActualCount INT,
> LogicalFrag DECIMAL,
> ExtentFrag DECIMAL)
> DECLARE tables_cur CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> ORDER BY TABLE_NAME
> OPEN tables_cur
> DECLARE @.ObjectName sysname
> FETCH NEXT FROM tables_cur INTO @.ObjectName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH
> COUNT_ROWS, NO_INFOMSGS' )
> INSERT #fraglist
> EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
> FETCH NEXT FROM tables_cur INTO @.ObjectName
> END
> CLOSE tables_cur
> DEALLOCATE tables_cur
> SELECT *
> FROM #fraglist fo
> WHERE LogicalFrag > 30 -- % fragmentation
> AND IndexID BETWEEN 1 AND 254
> AND ( IndexID = 1
> OR NOT EXISTS (
> SELECT * -- If clustered index to defrag then don't do
> anything else (no point).
> FROM #fraglist fi
> WHERE fi.ObjectId = fo.ObjectId
> AND fi.IndexID = 1
> )
> )
> IF @.is_reindex = 'Y'
> BEGIN
> DECLARE defrag_cur CURSOR FOR
> SELECT ObjectName,
> IndexName
> FROM #fraglist fo
> WHERE LogicalFrag > 30 -- % fragmentation
> AND IndexID BETWEEN 1 AND 254
> AND ( IndexID = 1
> OR NOT EXISTS (
> SELECT * -- If clustered index to defrag then
> don't do anything else (no point).
> FROM #fraglist fi
> WHERE fi.ObjectId = fo.ObjectId
> AND fi.IndexID = 1
> )
> )
> DECLARE @.IndexName sysname
> DECLARE @.sql varchar(1000)
> OPEN defrag_cur
> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
> indexname=' + @.IndexName
> SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', '
> + @.IndexName + ')'
> PRINT @.sql
> EXEC( @.sql )
> PRINT 'Complete.'
> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> END
> DEALLOCATE defrag_cur
> END
> DROP TABLE #fraglist
> END
>
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>|||Boy, I wish I could recall messages...I think I answered my own
question--this is for DBREINDEX, not INDEXDEFRAG.
"Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
news:uU4xUfB9EHA.2900@.TK2MSFTNGP09.phx.gbl...
> This is a very timely thread for me as I'm just getting ready to implement
> a SP that is based on the BOL sp.
> I guess I'm confused by this a bit. From what I read in BOL in SQL 2000
> DBCC INDEXDEFRAG will defragment clustered indexes. The SP you created
> from the BOL base is a bit different and I guess I just want to know if
> the one if BOL is incorrect or just different.
> It determines the indexes to defrag based on the results of:
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> if you wanted to exclude clustered indexes I suppose you would be able to
> modify to:
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
> FROM jdit_fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> AND INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') = 0
> Thanks
> Ivan
> "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> news:%23xN31fA9EHA.2112@.TK2MSFTNGP14.phx.gbl...
>> CREATE PROC ukug2_op_defrag
>> @.is_reindex char(1) = 'Y'
>> AS
>> BEGIN
>> SET NOCOUNT ON
>> -- Basis of script taken from BOL
>> CREATE TABLE #fraglist (
>> ObjectName CHAR (255),
>> ObjectId INT,
>> IndexName CHAR (255),
>> IndexId INT,
>> Lvl INT,
>> CountPages INT,
>> CountRows INT,
>> MinRecSize INT,
>> MaxRecSize INT,
>> AvgRecSize INT,
>> ForRecCount INT,
>> Extents INT,
>> ExtentSwitches INT,
>> AvgFreeBytes INT,
>> AvgPageDensity INT,
>> ScanDensity DECIMAL,
>> BestCount INT,
>> ActualCount INT,
>> LogicalFrag DECIMAL,
>> ExtentFrag DECIMAL)
>> DECLARE tables_cur CURSOR FOR
>> SELECT TABLE_NAME
>> FROM INFORMATION_SCHEMA.TABLES
>> WHERE TABLE_TYPE = 'BASE TABLE'
>> ORDER BY TABLE_NAME
>> OPEN tables_cur
>> DECLARE @.ObjectName sysname
>> FETCH NEXT FROM tables_cur INTO @.ObjectName
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH
>> COUNT_ROWS, NO_INFOMSGS' )
>> INSERT #fraglist
>> EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
>> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
>> FETCH NEXT FROM tables_cur INTO @.ObjectName
>> END
>> CLOSE tables_cur
>> DEALLOCATE tables_cur
>> SELECT *
>> FROM #fraglist fo
>> WHERE LogicalFrag > 30 -- % fragmentation
>> AND IndexID BETWEEN 1 AND 254
>> AND ( IndexID = 1
>> OR NOT EXISTS (
>> SELECT * -- If clustered index to defrag then don't do
>> anything else (no point).
>> FROM #fraglist fi
>> WHERE fi.ObjectId = fo.ObjectId
>> AND fi.IndexID = 1
>> )
>> )
>> IF @.is_reindex = 'Y'
>> BEGIN
>> DECLARE defrag_cur CURSOR FOR
>> SELECT ObjectName,
>> IndexName
>> FROM #fraglist fo
>> WHERE LogicalFrag > 30 -- % fragmentation
>> AND IndexID BETWEEN 1 AND 254
>> AND ( IndexID = 1
>> OR NOT EXISTS (
>> SELECT * -- If clustered index to defrag then
>> don't do anything else (no point).
>> FROM #fraglist fi
>> WHERE fi.ObjectId = fo.ObjectId
>> AND fi.IndexID = 1
>> )
>> )
>> DECLARE @.IndexName sysname
>> DECLARE @.sql varchar(1000)
>> OPEN defrag_cur
>> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
>> indexname=' + @.IndexName
>> SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''',
>> ' + @.IndexName + ')'
>> PRINT @.sql
>> EXEC( @.sql )
>> PRINT 'Complete.'
>> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
>> END
>> DEALLOCATE defrag_cur
>> END
>> DROP TABLE #fraglist
>> END
>>
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://www.sqlserverfaq.com?mbr=21
>> (Create your own groups, Forum, FAQ's and a ton more)
>>
>|||Seems like a bit of confusion here. DBCC INDEXDEFRAG can defragment
clustered and non-clustered indexes. DBCC DBREINDEX can rebuild clustered
and non-clustered indexes. The example scripts in this thread are taken from
Example E in BOL for DBCC SHOWCONTIG - look there for more details.
You should read the whitepaper below on fragmentation and when you actually
need to do anything about it (don't just blindly defrag/rebuild everything
in your database - you're wasting resources).
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
news:O3CQZhB9EHA.1228@.tk2msftngp13.phx.gbl...
> Boy, I wish I could recall messages...I think I answered my own
> question--this is for DBREINDEX, not INDEXDEFRAG.
> "Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
> news:uU4xUfB9EHA.2900@.TK2MSFTNGP09.phx.gbl...
> > This is a very timely thread for me as I'm just getting ready to
implement
> > a SP that is based on the BOL sp.
> >
> > I guess I'm confused by this a bit. From what I read in BOL in SQL 2000
> > DBCC INDEXDEFRAG will defragment clustered indexes. The SP you created
> > from the BOL base is a bit different and I guess I just want to know if
> > the one if BOL is incorrect or just different.
> >
> > It determines the indexes to defrag based on the results of:
> >
> > SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> > FROM #fraglist
> > WHERE LogicalFrag >= @.maxfrag
> > AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> >
> > if you wanted to exclude clustered indexes I suppose you would be able
to
> > modify to:
> >
> > SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
> > FROM jdit_fraglist
> > WHERE LogicalFrag >= @.maxfrag
> > AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> > AND INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') = 0
> >
> > Thanks
> >
> > Ivan
> >
> > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > news:%23xN31fA9EHA.2112@.TK2MSFTNGP14.phx.gbl...
> >> CREATE PROC ukug2_op_defrag
> >> @.is_reindex char(1) = 'Y'
> >> AS
> >> BEGIN
> >> SET NOCOUNT ON
> >>
> >> -- Basis of script taken from BOL
> >>
> >> CREATE TABLE #fraglist (
> >> ObjectName CHAR (255),
> >> ObjectId INT,
> >> IndexName CHAR (255),
> >> IndexId INT,
> >> Lvl INT,
> >> CountPages INT,
> >> CountRows INT,
> >> MinRecSize INT,
> >> MaxRecSize INT,
> >> AvgRecSize INT,
> >> ForRecCount INT,
> >> Extents INT,
> >> ExtentSwitches INT,
> >> AvgFreeBytes INT,
> >> AvgPageDensity INT,
> >> ScanDensity DECIMAL,
> >> BestCount INT,
> >> ActualCount INT,
> >> LogicalFrag DECIMAL,
> >> ExtentFrag DECIMAL)
> >>
> >> DECLARE tables_cur CURSOR FOR
> >> SELECT TABLE_NAME
> >> FROM INFORMATION_SCHEMA.TABLES
> >> WHERE TABLE_TYPE = 'BASE TABLE'
> >> ORDER BY TABLE_NAME
> >>
> >> OPEN tables_cur
> >>
> >> DECLARE @.ObjectName sysname
> >>
> >> FETCH NEXT FROM tables_cur INTO @.ObjectName
> >>
> >> WHILE @.@.FETCH_STATUS = 0
> >> BEGIN
> >> EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH
> >> COUNT_ROWS, NO_INFOMSGS' )
> >>
> >> INSERT #fraglist
> >> EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
> >> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
> >>
> >> FETCH NEXT FROM tables_cur INTO @.ObjectName
> >>
> >> END
> >>
> >> CLOSE tables_cur
> >> DEALLOCATE tables_cur
> >>
> >> SELECT *
> >> FROM #fraglist fo
> >> WHERE LogicalFrag > 30 -- % fragmentation
> >> AND IndexID BETWEEN 1 AND 254
> >> AND ( IndexID = 1
> >> OR NOT EXISTS (
> >> SELECT * -- If clustered index to defrag then don't
do
> >> anything else (no point).
> >> FROM #fraglist fi
> >> WHERE fi.ObjectId = fo.ObjectId
> >> AND fi.IndexID = 1
> >> )
> >> )
> >>
> >> IF @.is_reindex = 'Y'
> >> BEGIN
> >> DECLARE defrag_cur CURSOR FOR
> >> SELECT ObjectName,
> >> IndexName
> >> FROM #fraglist fo
> >> WHERE LogicalFrag > 30 -- % fragmentation
> >> AND IndexID BETWEEN 1 AND 254
> >> AND ( IndexID = 1
> >> OR NOT EXISTS (
> >> SELECT * -- If clustered index to defrag then
> >> don't do anything else (no point).
> >> FROM #fraglist fi
> >> WHERE fi.ObjectId = fo.ObjectId
> >> AND fi.IndexID = 1
> >> )
> >> )
> >>
> >> DECLARE @.IndexName sysname
> >> DECLARE @.sql varchar(1000)
> >>
> >> OPEN defrag_cur
> >>
> >> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> >>
> >> WHILE @.@.FETCH_STATUS = 0
> >> BEGIN
> >> PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
> >> indexname=' + @.IndexName
> >> SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) +
''',
> >> ' + @.IndexName + ')'
> >> PRINT @.sql
> >> EXEC( @.sql )
> >> PRINT 'Complete.'
> >>
> >> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> >>
> >> END
> >> DEALLOCATE defrag_cur
> >> END
> >>
> >> DROP TABLE #fraglist
> >>
> >> END
> >>
> >>
> >> --
> >> Tony Rogerson
> >> SQL Server MVP
> >> http://www.sqlserverfaq.com?mbr=21
> >> (Create your own groups, Forum, FAQ's and a ton more)
> >>
> >>
> >
> >
>

can dbcc indexdefrag reindex the whole database in one time?

Hi,
Normally i run the DBCC INDEXDEFRAG one table in one time, can run
all the tables in one time?
Thank you very much!
Best regards,
Florence
Hi,
This gives DBCC DBREINDEX. You can use this during off-peak hrs, as this is
not an online re-indexing operation. If you still want to use DBCC
INDEXDEFRAG, look at the usage of the same in BOL, and replace it with DBCC
DBREINDEX in given script.
/* Re-indexes the specified database */
CREATE PROCEDURE scr_defrag_database
@.dbname nvarchar(256)
AS
BEGIN
-- Quote the database name with brackets
DECLARE @.quoteddbname nvarchar(256)
SET @.quoteddbname = quotename( @.dbname )
-- The outer EXEC is so we can do USE, not allowed in stored procs
-- The inner EXEC does the actual reindex on each table in the
-- specified database
EXEC('
USE '+ @.quoteddbname +'
DECLARE @.stablename sysname
DECLARE PKMS_Tables CURSOR LOCAL FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' ORDER BY 1
OPEN PKMS_Tables
FETCH NEXT FROM PKMS_Tables INTO @.stablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.stablename = quotename(@.stablename, ''[]'')
EXEC('' DBCC DBREINDEX ( ''+@.stablename+'') WITH NO_INFOMSGS'')
FETCH NEXT FROM PKMS_Tables INTO @.stablename
END
CLOSE PKMS_Tables')
END
Thanks
Yogish
|||CREATE PROC ukug2_op_defrag
@.is_reindex char(1) = 'Y'
AS
BEGIN
SET NOCOUNT ON
-- Basis of script taken from BOL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE tables_cur CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tables_cur
DECLARE @.ObjectName sysname
FETCH NEXT FROM tables_cur INTO @.ObjectName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH COUNT_ROWS,
NO_INFOMSGS' )
INSERT #fraglist
EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
FETCH NEXT FROM tables_cur INTO @.ObjectName
END
CLOSE tables_cur
DEALLOCATE tables_cur
SELECT *
FROM #fraglist fo
WHERE LogicalFrag > 30 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then don't do
anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
IF @.is_reindex = 'Y'
BEGIN
DECLARE defrag_cur CURSOR FOR
SELECT ObjectName,
IndexName
FROM #fraglist fo
WHERE LogicalFrag > 30 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then
don't do anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
DECLARE @.IndexName sysname
DECLARE @.sql varchar(1000)
OPEN defrag_cur
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
indexname=' + @.IndexName
SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', '
+ @.IndexName + ')'
PRINT @.sql
EXEC( @.sql )
PRINT 'Complete.'
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
END
DEALLOCATE defrag_cur
END
DROP TABLE #fraglist
END
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||This is a very timely thread for me as I'm just getting ready to implement a
SP that is based on the BOL sp.
I guess I'm confused by this a bit. From what I read in BOL in SQL 2000
DBCC INDEXDEFRAG will defragment clustered indexes. The SP you created from
the BOL base is a bit different and I guess I just want to know if the one
if BOL is incorrect or just different.
It determines the indexes to defrag based on the results of:
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
if you wanted to exclude clustered indexes I suppose you would be able to
modify to:
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM jdit_fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
AND INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') = 0
Thanks
Ivan
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:%23xN31fA9EHA.2112@.TK2MSFTNGP14.phx.gbl...
> CREATE PROC ukug2_op_defrag
> @.is_reindex char(1) = 'Y'
> AS
> BEGIN
> SET NOCOUNT ON
> -- Basis of script taken from BOL
> CREATE TABLE #fraglist (
> ObjectName CHAR (255),
> ObjectId INT,
> IndexName CHAR (255),
> IndexId INT,
> Lvl INT,
> CountPages INT,
> CountRows INT,
> MinRecSize INT,
> MaxRecSize INT,
> AvgRecSize INT,
> ForRecCount INT,
> Extents INT,
> ExtentSwitches INT,
> AvgFreeBytes INT,
> AvgPageDensity INT,
> ScanDensity DECIMAL,
> BestCount INT,
> ActualCount INT,
> LogicalFrag DECIMAL,
> ExtentFrag DECIMAL)
> DECLARE tables_cur CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> ORDER BY TABLE_NAME
> OPEN tables_cur
> DECLARE @.ObjectName sysname
> FETCH NEXT FROM tables_cur INTO @.ObjectName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH
> COUNT_ROWS, NO_INFOMSGS' )
> INSERT #fraglist
> EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
> FETCH NEXT FROM tables_cur INTO @.ObjectName
> END
> CLOSE tables_cur
> DEALLOCATE tables_cur
> SELECT *
> FROM #fraglist fo
> WHERE LogicalFrag > 30 -- % fragmentation
> AND IndexID BETWEEN 1 AND 254
> AND ( IndexID = 1
> OR NOT EXISTS (
> SELECT * -- If clustered index to defrag then don't do
> anything else (no point).
> FROM #fraglist fi
> WHERE fi.ObjectId = fo.ObjectId
> AND fi.IndexID = 1
> )
> )
> IF @.is_reindex = 'Y'
> BEGIN
> DECLARE defrag_cur CURSOR FOR
> SELECT ObjectName,
> IndexName
> FROM #fraglist fo
> WHERE LogicalFrag > 30 -- % fragmentation
> AND IndexID BETWEEN 1 AND 254
> AND ( IndexID = 1
> OR NOT EXISTS (
> SELECT * -- If clustered index to defrag then
> don't do anything else (no point).
> FROM #fraglist fi
> WHERE fi.ObjectId = fo.ObjectId
> AND fi.IndexID = 1
> )
> )
> DECLARE @.IndexName sysname
> DECLARE @.sql varchar(1000)
> OPEN defrag_cur
> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
> indexname=' + @.IndexName
> SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', '
> + @.IndexName + ')'
> PRINT @.sql
> EXEC( @.sql )
> PRINT 'Complete.'
> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> END
> DEALLOCATE defrag_cur
> END
> DROP TABLE #fraglist
> END
>
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
|||Boy, I wish I could recall messages...I think I answered my own
question--this is for DBREINDEX, not INDEXDEFRAG.
"Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
news:uU4xUfB9EHA.2900@.TK2MSFTNGP09.phx.gbl...
> This is a very timely thread for me as I'm just getting ready to implement
> a SP that is based on the BOL sp.
> I guess I'm confused by this a bit. From what I read in BOL in SQL 2000
> DBCC INDEXDEFRAG will defragment clustered indexes. The SP you created
> from the BOL base is a bit different and I guess I just want to know if
> the one if BOL is incorrect or just different.
> It determines the indexes to defrag based on the results of:
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> if you wanted to exclude clustered indexes I suppose you would be able to
> modify to:
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
> FROM jdit_fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> AND INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') = 0
> Thanks
> Ivan
> "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> news:%23xN31fA9EHA.2112@.TK2MSFTNGP14.phx.gbl...
>
|||Seems like a bit of confusion here. DBCC INDEXDEFRAG can defragment
clustered and non-clustered indexes. DBCC DBREINDEX can rebuild clustered
and non-clustered indexes. The example scripts in this thread are taken from
Example E in BOL for DBCC SHOWCONTIG - look there for more details.
You should read the whitepaper below on fragmentation and when you actually
need to do anything about it (don't just blindly defrag/rebuild everything
in your database - you're wasting resources).
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
news:O3CQZhB9EHA.1228@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Boy, I wish I could recall messages...I think I answered my own
> question--this is for DBREINDEX, not INDEXDEFRAG.
> "Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
> news:uU4xUfB9EHA.2900@.TK2MSFTNGP09.phx.gbl...
implement[vbcol=seagreen]
to[vbcol=seagreen]
do[vbcol=seagreen]
''',
>

can dbcc indexdefrag reindex the whole database in one time?

Hi,
Normally i run the DBCC INDEXDEFRAG one table in one time, can run
all the tables in one time?
--
Thank you very much!
Best regards,
FlorenceHi,
This gives DBCC DBREINDEX. You can use this during off-peak hrs, as this is
not an online re-indexing operation. If you still want to use DBCC
INDEXDEFRAG, look at the usage of the same in BOL, and replace it with DBCC
DBREINDEX in given script.
/* Re-indexes the specified database */
CREATE PROCEDURE scr_defrag_database
@.dbname nvarchar(256)
AS
BEGIN
-- Quote the database name with brackets
DECLARE @.quoteddbname nvarchar(256)
SET @.quoteddbname = quotename( @.dbname )
-- The outer EXEC is so we can do USE, not allowed in stored procs
-- The inner EXEC does the actual reindex on each table in the
-- specified database
EXEC('
USE '+ @.quoteddbname +'
DECLARE @.stablename sysname
DECLARE PKMS_Tables CURSOR LOCAL FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' ORDER BY 1
OPEN PKMS_Tables
FETCH NEXT FROM PKMS_Tables INTO @.stablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.stablename = quotename(@.stablename, ''[]'')
EXEC('' DBCC DBREINDEX ( ''+@.stablename+'') WITH NO_INFOMSGS'')
FETCH NEXT FROM PKMS_Tables INTO @.stablename
END
CLOSE PKMS_Tables')
END
Thanks
Yogish|||CREATE PROC ukug2_op_defrag
@.is_reindex char(1) = 'Y'
AS
BEGIN
SET NOCOUNT ON
-- Basis of script taken from BOL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE tables_cur CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tables_cur
DECLARE @.ObjectName sysname
FETCH NEXT FROM tables_cur INTO @.ObjectName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH COUNT_ROWS,
NO_INFOMSGS' )
INSERT #fraglist
EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
FETCH NEXT FROM tables_cur INTO @.ObjectName
END
CLOSE tables_cur
DEALLOCATE tables_cur
SELECT *
FROM #fraglist fo
WHERE LogicalFrag > 30 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then don't do
anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
IF @.is_reindex = 'Y'
BEGIN
DECLARE defrag_cur CURSOR FOR
SELECT ObjectName,
IndexName
FROM #fraglist fo
WHERE LogicalFrag > 30 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then
don't do anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
DECLARE @.IndexName sysname
DECLARE @.sql varchar(1000)
OPEN defrag_cur
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
indexname=' + @.IndexName
SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', '
+ @.IndexName + ')'
PRINT @.sql
EXEC( @.sql )
PRINT 'Complete.'
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
END
DEALLOCATE defrag_cur
END
DROP TABLE #fraglist
END
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||This is a very timely thread for me as I'm just getting ready to implement a
SP that is based on the BOL sp.
I guess I'm confused by this a bit. From what I read in BOL in SQL 2000
DBCC INDEXDEFRAG will defragment clustered indexes. The SP you created from
the BOL base is a bit different and I guess I just want to know if the one
if BOL is incorrect or just different.
It determines the indexes to defrag based on the results of:
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
if you wanted to exclude clustered indexes I suppose you would be able to
modify to:
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM jdit_fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
AND INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') = 0
Thanks
Ivan
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:%23xN31fA9EHA.2112@.TK2MSFTNGP14.phx.gbl...
> CREATE PROC ukug2_op_defrag
> @.is_reindex char(1) = 'Y'
> AS
> BEGIN
> SET NOCOUNT ON
> -- Basis of script taken from BOL
> CREATE TABLE #fraglist (
> ObjectName CHAR (255),
> ObjectId INT,
> IndexName CHAR (255),
> IndexId INT,
> Lvl INT,
> CountPages INT,
> CountRows INT,
> MinRecSize INT,
> MaxRecSize INT,
> AvgRecSize INT,
> ForRecCount INT,
> Extents INT,
> ExtentSwitches INT,
> AvgFreeBytes INT,
> AvgPageDensity INT,
> ScanDensity DECIMAL,
> BestCount INT,
> ActualCount INT,
> LogicalFrag DECIMAL,
> ExtentFrag DECIMAL)
> DECLARE tables_cur CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> ORDER BY TABLE_NAME
> OPEN tables_cur
> DECLARE @.ObjectName sysname
> FETCH NEXT FROM tables_cur INTO @.ObjectName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH
> COUNT_ROWS, NO_INFOMSGS' )
> INSERT #fraglist
> EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
> FETCH NEXT FROM tables_cur INTO @.ObjectName
> END
> CLOSE tables_cur
> DEALLOCATE tables_cur
> SELECT *
> FROM #fraglist fo
> WHERE LogicalFrag > 30 -- % fragmentation
> AND IndexID BETWEEN 1 AND 254
> AND ( IndexID = 1
> OR NOT EXISTS (
> SELECT * -- If clustered index to defrag then don't do
> anything else (no point).
> FROM #fraglist fi
> WHERE fi.ObjectId = fo.ObjectId
> AND fi.IndexID = 1
> )
> )
> IF @.is_reindex = 'Y'
> BEGIN
> DECLARE defrag_cur CURSOR FOR
> SELECT ObjectName,
> IndexName
> FROM #fraglist fo
> WHERE LogicalFrag > 30 -- % fragmentation
> AND IndexID BETWEEN 1 AND 254
> AND ( IndexID = 1
> OR NOT EXISTS (
> SELECT * -- If clustered index to defrag then
> don't do anything else (no point).
> FROM #fraglist fi
> WHERE fi.ObjectId = fo.ObjectId
> AND fi.IndexID = 1
> )
> )
> DECLARE @.IndexName sysname
> DECLARE @.sql varchar(1000)
> OPEN defrag_cur
> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
> indexname=' + @.IndexName
> SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', '
> + @.IndexName + ')'
> PRINT @.sql
> EXEC( @.sql )
> PRINT 'Complete.'
> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> END
> DEALLOCATE defrag_cur
> END
> DROP TABLE #fraglist
> END
>
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>|||Boy, I wish I could recall messages...I think I answered my own
question--this is for DBREINDEX, not INDEXDEFRAG.
"Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
news:uU4xUfB9EHA.2900@.TK2MSFTNGP09.phx.gbl...
> This is a very timely thread for me as I'm just getting ready to implement
> a SP that is based on the BOL sp.
> I guess I'm confused by this a bit. From what I read in BOL in SQL 2000
> DBCC INDEXDEFRAG will defragment clustered indexes. The SP you created
> from the BOL base is a bit different and I guess I just want to know if
> the one if BOL is incorrect or just different.
> It determines the indexes to defrag based on the results of:
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> if you wanted to exclude clustered indexes I suppose you would be able to
> modify to:
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
> FROM jdit_fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> AND INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') = 0
> Thanks
> Ivan
> "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> news:%23xN31fA9EHA.2112@.TK2MSFTNGP14.phx.gbl...
>|||Seems like a bit of confusion here. DBCC INDEXDEFRAG can defragment
clustered and non-clustered indexes. DBCC DBREINDEX can rebuild clustered
and non-clustered indexes. The example scripts in this thread are taken from
Example E in BOL for DBCC SHOWCONTIG - look there for more details.
You should read the whitepaper below on fragmentation and when you actually
need to do anything about it (don't just blindly defrag/rebuild everything
in your database - you're wasting resources).
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
news:O3CQZhB9EHA.1228@.tk2msftngp13.phx.gbl...
> Boy, I wish I could recall messages...I think I answered my own
> question--this is for DBREINDEX, not INDEXDEFRAG.
> "Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
> news:uU4xUfB9EHA.2900@.TK2MSFTNGP09.phx.gbl...
implement[vbcol=seagreen]
to[vbcol=seagreen]
do[vbcol=seagreen]
''',[vbcol=seagreen]
>