Maintenance Plan Fails but I can't figure out why? No log informa

I have a maintenance plan that is failing but I'm not sure why. Plan
consists of:
1 Backup Database
2 Cleanup Task
3 Reorganize Index Task
4 Rebuild Index Task
5 Shrink Database Task
6 Update Statistics Task
I have a notification message configured for errors. For some reason I
receive two emails. One states success while the other states failure. On
the success email I do not see any mention of steps 3,4 or 6 (from above). I
cannot find any additional information in the logs. Below are the two
emails. These emails are sent out back to back. Thanks in advance for your
help.
Terry
###################################################
Success Email
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047 Report
was generated on "MSSQL".
Maintenance Plan: Database Backups
Duration: 08:26:44
Status: Succeeded.
Details:
Back Up Database (Full) (MSSQL)
Task start: 9/28/2007 9:00 PM.
Task end: 9/28/2007 9:07 PM.
Success
BAK Cleanup Task (MSSQL)
Task start: 9/28/2007 9:07 PM.
Task end: 9/28/2007 9:07 PM.
Success
Shrink Database Task (MSSQL)
Task start: 9/29/2007 3:33 AM.
Task end: 9/29/2007 3:35 AM.
Success
###################################################
Failed Email
OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
DURATION: 8 hours, 26 minutes, 51 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 1
(BackupDatabaseJob). The last step to run was step 1 (Subplan).Hello Terry!
Check out Jobs' histories from the Jobs node under SQL Server Agent. There,
you will find the one which doesn't work. It stops your maintenance plan.
Besides, you better setup your maintenance plan one by one, not an "all in
one" package.
Ekrem Ã?nsoy
"Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
>I have a maintenance plan that is failing but I'm not sure why. Plan
> consists of:
> 1 Backup Database
> 2 Cleanup Task
> 3 Reorganize Index Task
> 4 Rebuild Index Task
> 5 Shrink Database Task
> 6 Update Statistics Task
> I have a notification message configured for errors. For some reason I
> receive two emails. One states success while the other states failure.
> On
> the success email I do not see any mention of steps 3,4 or 6 (from above).
> I
> cannot find any additional information in the logs. Below are the two
> emails. These emails are sent out back to back. Thanks in advance for
> your
> help.
> Terry
> ###################################################
> Success Email
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047 Report
> was generated on "MSSQL".
> Maintenance Plan: Database Backups
> Duration: 08:26:44
> Status: Succeeded.
> Details:
> Back Up Database (Full) (MSSQL)
> Task start: 9/28/2007 9:00 PM.
> Task end: 9/28/2007 9:07 PM.
> Success
>
> BAK Cleanup Task (MSSQL)
> Task start: 9/28/2007 9:07 PM.
> Task end: 9/28/2007 9:07 PM.
> Success
>
> Shrink Database Task (MSSQL)
> Task start: 9/29/2007 3:33 AM.
> Task end: 9/29/2007 3:35 AM.
> Success
>
> ###################################################
> Failed Email
> OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
> DURATION: 8 hours, 26 minutes, 51 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 1
> (BackupDatabaseJob). The last step to run was step 1 (Subplan).
>|||Hi Ekrem,
Thank you for the reply. I checked out the history logs for the job and
what is strange is that there are no entries? I right clicked on the job and
selected 'View Histories' but no entries. Can you think of a reason why I'm
not seeing any data?
Thanks,
Terry
"Ekrem Ã?nsoy" wrote:
> Hello Terry!
>
> Check out Jobs' histories from the Jobs node under SQL Server Agent. There,
> you will find the one which doesn't work. It stops your maintenance plan.
> Besides, you better setup your maintenance plan one by one, not an "all in
> one" package.
>
> --
> Ekrem Ã?nsoy
>
> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
> >I have a maintenance plan that is failing but I'm not sure why. Plan
> > consists of:
> > 1 Backup Database
> > 2 Cleanup Task
> > 3 Reorganize Index Task
> > 4 Rebuild Index Task
> > 5 Shrink Database Task
> > 6 Update Statistics Task
> >
> > I have a notification message configured for errors. For some reason I
> > receive two emails. One states success while the other states failure.
> > On
> > the success email I do not see any mention of steps 3,4 or 6 (from above).
> > I
> > cannot find any additional information in the logs. Below are the two
> > emails. These emails are sent out back to back. Thanks in advance for
> > your
> > help.
> >
> > Terry
> >
> > ###################################################
> > Success Email
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047 Report
> > was generated on "MSSQL".
> > Maintenance Plan: Database Backups
> > Duration: 08:26:44
> > Status: Succeeded.
> > Details:
> > Back Up Database (Full) (MSSQL)
> > Task start: 9/28/2007 9:00 PM.
> > Task end: 9/28/2007 9:07 PM.
> > Success
> >
> >
> > BAK Cleanup Task (MSSQL)
> > Task start: 9/28/2007 9:07 PM.
> > Task end: 9/28/2007 9:07 PM.
> > Success
> >
> >
> > Shrink Database Task (MSSQL)
> > Task start: 9/29/2007 3:33 AM.
> > Task end: 9/29/2007 3:35 AM.
> > Success
> >
> >
> > ###################################################
> > Failed Email
> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
> > DURATION: 8 hours, 26 minutes, 51 seconds
> > STATUS: Failed
> > MESSAGES: The job failed. The Job was invoked by Schedule 1
> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
> >
>|||Hi Terry,
Could you tell me exactly what version and edition of SQL Server do you use?
First two reasons I can think of:
1- You did not select the checkbox just at the left side of the job name
from the Log File Viewer window.
2- The Jobs have never worked (in your case, some of them worked, so this
must not be valid for you)
P.S.
You use SSMS, not SSMSE right?
Ekrem Ã?nsoy
"Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
news:BA438105-C888-45E8-8E7E-623852F82345@.microsoft.com...
> Hi Ekrem,
> Thank you for the reply. I checked out the history logs for the job and
> what is strange is that there are no entries? I right clicked on the job
> and
> selected 'View Histories' but no entries. Can you think of a reason why
> I'm
> not seeing any data?
> Thanks,
> Terry
> "Ekrem Ã?nsoy" wrote:
>> Hello Terry!
>>
>> Check out Jobs' histories from the Jobs node under SQL Server Agent.
>> There,
>> you will find the one which doesn't work. It stops your maintenance plan.
>> Besides, you better setup your maintenance plan one by one, not an "all
>> in
>> one" package.
>>
>> --
>> Ekrem Ã?nsoy
>>
>> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
>> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
>> >I have a maintenance plan that is failing but I'm not sure why. Plan
>> > consists of:
>> > 1 Backup Database
>> > 2 Cleanup Task
>> > 3 Reorganize Index Task
>> > 4 Rebuild Index Task
>> > 5 Shrink Database Task
>> > 6 Update Statistics Task
>> >
>> > I have a notification message configured for errors. For some reason I
>> > receive two emails. One states success while the other states failure.
>> > On
>> > the success email I do not see any mention of steps 3,4 or 6 (from
>> > above).
>> > I
>> > cannot find any additional information in the logs. Below are the two
>> > emails. These emails are sent out back to back. Thanks in advance for
>> > your
>> > help.
>> >
>> > Terry
>> >
>> > ###################################################
>> > Success Email
>> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
>> > Report
>> > was generated on "MSSQL".
>> > Maintenance Plan: Database Backups
>> > Duration: 08:26:44
>> > Status: Succeeded.
>> > Details:
>> > Back Up Database (Full) (MSSQL)
>> > Task start: 9/28/2007 9:00 PM.
>> > Task end: 9/28/2007 9:07 PM.
>> > Success
>> >
>> >
>> > BAK Cleanup Task (MSSQL)
>> > Task start: 9/28/2007 9:07 PM.
>> > Task end: 9/28/2007 9:07 PM.
>> > Success
>> >
>> >
>> > Shrink Database Task (MSSQL)
>> > Task start: 9/29/2007 3:33 AM.
>> > Task end: 9/29/2007 3:35 AM.
>> > Success
>> >
>> >
>> > ###################################################
>> > Failed Email
>> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
>> > DURATION: 8 hours, 26 minutes, 51 seconds
>> > STATUS: Failed
>> > MESSAGES: The job failed. The Job was invoked by Schedule 1
>> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
>> >|||Hi Ekrem,
Using SQL Server 2005 (9.00.2047) and SQL Server Management Studio.
This is what I've did:
Expanded 'SQL Server Agent', Expanded Jobs, Right clicked on job (Database
Backups.Subplan) and selected 'View History'. When the dialog displays the
job is selected.
Thanks,
Terry
"Ekrem Ã?nsoy" wrote:
> Hi Terry,
>
> Could you tell me exactly what version and edition of SQL Server do you use?
> First two reasons I can think of:
> 1- You did not select the checkbox just at the left side of the job name
> from the Log File Viewer window.
> 2- The Jobs have never worked (in your case, some of them worked, so this
> must not be valid for you)
> P.S.
> You use SSMS, not SSMSE right?
>
> --
> Ekrem Ã?nsoy
>
> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
> news:BA438105-C888-45E8-8E7E-623852F82345@.microsoft.com...
> > Hi Ekrem,
> > Thank you for the reply. I checked out the history logs for the job and
> > what is strange is that there are no entries? I right clicked on the job
> > and
> > selected 'View Histories' but no entries. Can you think of a reason why
> > I'm
> > not seeing any data?
> > Thanks,
> > Terry
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> >> Hello Terry!
> >>
> >>
> >> Check out Jobs' histories from the Jobs node under SQL Server Agent.
> >> There,
> >> you will find the one which doesn't work. It stops your maintenance plan.
> >>
> >> Besides, you better setup your maintenance plan one by one, not an "all
> >> in
> >> one" package.
> >>
> >>
> >> --
> >> Ekrem Ã?nsoy
> >>
> >>
> >>
> >> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
> >> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
> >> >I have a maintenance plan that is failing but I'm not sure why. Plan
> >> > consists of:
> >> > 1 Backup Database
> >> > 2 Cleanup Task
> >> > 3 Reorganize Index Task
> >> > 4 Rebuild Index Task
> >> > 5 Shrink Database Task
> >> > 6 Update Statistics Task
> >> >
> >> > I have a notification message configured for errors. For some reason I
> >> > receive two emails. One states success while the other states failure.
> >> > On
> >> > the success email I do not see any mention of steps 3,4 or 6 (from
> >> > above).
> >> > I
> >> > cannot find any additional information in the logs. Below are the two
> >> > emails. These emails are sent out back to back. Thanks in advance for
> >> > your
> >> > help.
> >> >
> >> > Terry
> >> >
> >> > ###################################################
> >> > Success Email
> >> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> >> > Report
> >> > was generated on "MSSQL".
> >> > Maintenance Plan: Database Backups
> >> > Duration: 08:26:44
> >> > Status: Succeeded.
> >> > Details:
> >> > Back Up Database (Full) (MSSQL)
> >> > Task start: 9/28/2007 9:00 PM.
> >> > Task end: 9/28/2007 9:07 PM.
> >> > Success
> >> >
> >> >
> >> > BAK Cleanup Task (MSSQL)
> >> > Task start: 9/28/2007 9:07 PM.
> >> > Task end: 9/28/2007 9:07 PM.
> >> > Success
> >> >
> >> >
> >> > Shrink Database Task (MSSQL)
> >> > Task start: 9/29/2007 3:33 AM.
> >> > Task end: 9/29/2007 3:35 AM.
> >> > Success
> >> >
> >> >
> >> > ###################################################
> >> > Failed Email
> >> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
> >> > DURATION: 8 hours, 26 minutes, 51 seconds
> >> > STATUS: Failed
> >> > MESSAGES: The job failed. The Job was invoked by Schedule 1
> >> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
> >> >
> >>
>|||Hi Terry!
Can you check the Filter setting in the Log Viewer window? If it's set to
some values then click Clear button in the Filter Settings window and click
OK.
Check if other logs are clear or do they have records from the Log File
Viewer? (SQL Agent logs for example)
If it is not your situation, then can you take a look at your Instance path?
Check if log files are stored or not.
For Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
Note:
I once faced this problem but in that situation the person who met this
problem was using SSMSE and when he started using SSMS he started seeing
logs.
--
Ekrem Ã?nsoy
"Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
news:0725AFDB-ED8A-4F3E-8712-EDAA3F097B15@.microsoft.com...
> Hi Ekrem,
> Using SQL Server 2005 (9.00.2047) and SQL Server Management Studio.
> This is what I've did:
> Expanded 'SQL Server Agent', Expanded Jobs, Right clicked on job (Database
> Backups.Subplan) and selected 'View History'. When the dialog displays
> the
> job is selected.
> Thanks,
> Terry
>
> "Ekrem Ã?nsoy" wrote:
>> Hi Terry,
>>
>> Could you tell me exactly what version and edition of SQL Server do you
>> use?
>> First two reasons I can think of:
>> 1- You did not select the checkbox just at the left side of the job name
>> from the Log File Viewer window.
>> 2- The Jobs have never worked (in your case, some of them worked, so this
>> must not be valid for you)
>> P.S.
>> You use SSMS, not SSMSE right?
>>
>> --
>> Ekrem Ã?nsoy
>>
>> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
>> news:BA438105-C888-45E8-8E7E-623852F82345@.microsoft.com...
>> > Hi Ekrem,
>> > Thank you for the reply. I checked out the history logs for the job
>> > and
>> > what is strange is that there are no entries? I right clicked on the
>> > job
>> > and
>> > selected 'View Histories' but no entries. Can you think of a reason
>> > why
>> > I'm
>> > not seeing any data?
>> > Thanks,
>> > Terry
>> >
>> > "Ekrem Ã?nsoy" wrote:
>> >
>> >> Hello Terry!
>> >>
>> >>
>> >> Check out Jobs' histories from the Jobs node under SQL Server Agent.
>> >> There,
>> >> you will find the one which doesn't work. It stops your maintenance
>> >> plan.
>> >>
>> >> Besides, you better setup your maintenance plan one by one, not an
>> >> "all
>> >> in
>> >> one" package.
>> >>
>> >>
>> >> --
>> >> Ekrem Ã?nsoy
>> >>
>> >>
>> >>
>> >> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
>> >> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
>> >> >I have a maintenance plan that is failing but I'm not sure why. Plan
>> >> > consists of:
>> >> > 1 Backup Database
>> >> > 2 Cleanup Task
>> >> > 3 Reorganize Index Task
>> >> > 4 Rebuild Index Task
>> >> > 5 Shrink Database Task
>> >> > 6 Update Statistics Task
>> >> >
>> >> > I have a notification message configured for errors. For some
>> >> > reason I
>> >> > receive two emails. One states success while the other states
>> >> > failure.
>> >> > On
>> >> > the success email I do not see any mention of steps 3,4 or 6 (from
>> >> > above).
>> >> > I
>> >> > cannot find any additional information in the logs. Below are the
>> >> > two
>> >> > emails. These emails are sent out back to back. Thanks in advance
>> >> > for
>> >> > your
>> >> > help.
>> >> >
>> >> > Terry
>> >> >
>> >> > ###################################################
>> >> > Success Email
>> >> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
>> >> > Report
>> >> > was generated on "MSSQL".
>> >> > Maintenance Plan: Database Backups
>> >> > Duration: 08:26:44
>> >> > Status: Succeeded.
>> >> > Details:
>> >> > Back Up Database (Full) (MSSQL)
>> >> > Task start: 9/28/2007 9:00 PM.
>> >> > Task end: 9/28/2007 9:07 PM.
>> >> > Success
>> >> >
>> >> >
>> >> > BAK Cleanup Task (MSSQL)
>> >> > Task start: 9/28/2007 9:07 PM.
>> >> > Task end: 9/28/2007 9:07 PM.
>> >> > Success
>> >> >
>> >> >
>> >> > Shrink Database Task (MSSQL)
>> >> > Task start: 9/29/2007 3:33 AM.
>> >> > Task end: 9/29/2007 3:35 AM.
>> >> > Success
>> >> >
>> >> >
>> >> > ###################################################
>> >> > Failed Email
>> >> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01
>> >> > PM
>> >> > DURATION: 8 hours, 26 minutes, 51 seconds
>> >> > STATUS: Failed
>> >> > MESSAGES: The job failed. The Job was invoked by Schedule 1
>> >> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
>> >> >
>> >>|||Ekrem Ã?nsoy wrote:
> Hi Terry!
>
> Can you check the Filter setting in the Log Viewer window? If it's set
> to some values then click Clear button in the Filter Settings window
> and click OK.
> Check if other logs are clear or do they have records from the Log
> File Viewer? (SQL Agent logs for example)
> If it is not your situation, then can you take a look at your Instance
> path? Check if log files are stored or not.
> For Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> Note:
> I once faced this problem but in that situation the person who met
> this problem was using SSMSE and when he started using SSMS he started
> seeing logs.
>
Instead of viewing history on the SQL Agent job - view the history on
the maintenance plan. The history here will show each step in the
maintenance plan and should identify why the other steps are failing.
My guess as to why you are getting two messages is that the steps in the
maintenance plan are not sequenced. In the maintenance plan editor, you
need to drag the green arrow (go to next step on success) or the blue
arrow (go to next step on completion) and perform each step in the
maintenance plan in sequence. If your jobs are not sequenced - those
other steps are probably failing because they cannot obtain a lock or
are blocked by the other steps.
And finally, you do not need to perform all of those steps - and not in
the order you listed them. I generally run the following:
Integrity Checks (DBCC CHECKDB)
On Success: Rebuild/Reorganize Indexes
On Success: Backup Database
On Success: Maintenance Plan Cleanup Task
Depending upon the system, I may include a step to Update Statistics and
would include that step right before the backup. You should not shrink
the database as it will only grow again, especially when you are
rebuilding indexes. Make sure your database has been sized large enough
so that the rebuild index operation does not grow the database
(generally 20% free).
Jeff|||Hi Jeffrey,
Thanks for the reply. All the tasks are sequenced (blue connections). I
took a look at the history logs as you mentioned (thanks) but what is strange
is not all the task are contained in the log but yet the ones that are there
show success. I have turned on extended logging and I initially removed the
'Reorganize Index Task' and 'Rebuild Index Task' steps. When I did this
everything worked and all 4 entries are listed in the log. I later added
back the 'Reorganize Index Task' and the job failed. As mentioned above when
I view the log I do not see all the steps and the ones that are listed show
success. The 'Reorganize Index Task' is not listed. No failed steps are
listed. Strange deal.
Thanks,
Terry
"Jeffrey Williams" wrote:
> Ekrem Ã?nsoy wrote:
> > Hi Terry!
> >
> >
> > Can you check the Filter setting in the Log Viewer window? If it's set
> > to some values then click Clear button in the Filter Settings window
> > and click OK.
> >
> > Check if other logs are clear or do they have records from the Log
> > File Viewer? (SQL Agent logs for example)
> >
> > If it is not your situation, then can you take a look at your Instance
> > path? Check if log files are stored or not.
> > For Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> >
> > Note:
> > I once faced this problem but in that situation the person who met
> > this problem was using SSMSE and when he started using SSMS he started
> > seeing logs.
> >
> Instead of viewing history on the SQL Agent job - view the history on
> the maintenance plan. The history here will show each step in the
> maintenance plan and should identify why the other steps are failing.
> My guess as to why you are getting two messages is that the steps in the
> maintenance plan are not sequenced. In the maintenance plan editor, you
> need to drag the green arrow (go to next step on success) or the blue
> arrow (go to next step on completion) and perform each step in the
> maintenance plan in sequence. If your jobs are not sequenced - those
> other steps are probably failing because they cannot obtain a lock or
> are blocked by the other steps.
> And finally, you do not need to perform all of those steps - and not in
> the order you listed them. I generally run the following:
> Integrity Checks (DBCC CHECKDB)
> On Success: Rebuild/Reorganize Indexes
> On Success: Backup Database
> On Success: Maintenance Plan Cleanup Task
> Depending upon the system, I may include a step to Update Statistics and
> would include that step right before the backup. You should not shrink
> the database as it will only grow again, especially when you are
> rebuilding indexes. Make sure your database has been sized large enough
> so that the rebuild index operation does not grow the database
> (generally 20% free).
> Jeff
>

Maintenance Plan DBCC Functionality

When setting up a maintenance plan ,with the maintenance plan wizard,
when SQL re-organizes data and index pages, will data be able to be
inserted and read during these operations?
Thanks,
ben mMaint wiz uses DBCC DBREINDEX.
From memory, DBREINDEX will acquire shared table lock when rebuilding
non-clustered index. This will allow reads, but writes will be blocked.
Again, from memory, DBREINDEX will acquire exclusive table lock when
rebuilding clustered index- This will block both readers (unless code does
dirty reads) and writers.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <ben@.brainspout.com> wrote in message
news:O9Tb4hR5DHA.2732@.TK2MSFTNGP09.phx.gbl...
> When setting up a maintenance plan ,with the maintenance plan wizard,
> when SQL re-organizes data and index pages, will data be able to be
> inserted and read during these operations?
> Thanks,
> ben m
>|||Would it be possible to manually execute the DBCC REINDEX command with
an option not to lock writes?
This is a transaction processing box.
thanks again,
-Ben
Tibor Karaszi wrote:
> Maint wiz uses DBCC DBREINDEX.
> From memory, DBREINDEX will acquire shared table lock when rebuilding
> non-clustered index. This will allow reads, but writes will be blocked.
> Again, from memory, DBREINDEX will acquire exclusive table lock when
> rebuilding clustered index- This will block both readers (unless code does
> dirty reads) and writers.
>|||Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <ben@.brainspout.com> wrote in message news:ON8FIKa5DHA.1664@.TK2MSFTNGP11.phx.gbl...
> Would it be possible to manually execute the DBCC REINDEX command with
> an option not to lock writes?
> This is a transaction processing box.
> thanks again,
> -Ben
> Tibor Karaszi wrote:
> > Maint wiz uses DBCC DBREINDEX.
> >
> > From memory, DBREINDEX will acquire shared table lock when rebuilding
> > non-clustered index. This will allow reads, but writes will be blocked.
> > Again, from memory, DBREINDEX will acquire exclusive table lock when
> > rebuilding clustered index- This will block both readers (unless code does
> > dirty reads) and writers.
> >
>|||Do you know of any other maintenance tools that can be used, that will
allow writes to the table being modified?
Tibor Karaszi wrote:
> Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
>|||It is not the client's decision whether to write at the same time as a rebuild is happening. It is
the server's decision. In SQK Server, all the methods to rebuilds an index, will in the end call the
same code (the code which is called when an index is created by CREATE INDEX), and this code uses
locks to protect against other modifications.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <ben@.brainspout.com> wrote in message news:%235k9sja5DHA.2764@.TK2MSFTNGP09.phx.gbl...
> Do you know of any other maintenance tools that can be used, that will
> allow writes to the table being modified?
> Tibor Karaszi wrote:
> > Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
> >
>|||See my reply to your previous post... checkout the whitepaper below that
gives you full details on this problem.
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben@.brainspout.com> wrote in message
news:#5k9sja5DHA.2764@.TK2MSFTNGP09.phx.gbl...
> Do you know of any other maintenance tools that can be used, that will
> allow writes to the table being modified?
> Tibor Karaszi wrote:
> > Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
> >
>|||Also, you might want to check out DBCC INDEXDEFRAG in case you haven't already. I do recommend
reading the document that Paul posted. Good stuff.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u$Sut4a5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> It is not the client's decision whether to write at the same time as a rebuild is happening. It is
> the server's decision. In SQK Server, all the methods to rebuilds an index, will in the end call
the
> same code (the code which is called when an index is created by CREATE INDEX), and this code uses
> locks to protect against other modifications.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Ben" <ben@.brainspout.com> wrote in message news:%235k9sja5DHA.2764@.TK2MSFTNGP09.phx.gbl...
> > Do you know of any other maintenance tools that can be used, that will
> > allow writes to the table being modified?
> > Tibor Karaszi wrote:
> > > Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
> > >
> >
>

Maintenance Plan DBCC Functionality

When setting up a maintenance plan ,with the maintenance plan wizard,
when SQL re-organizes data and index pages, will data be able to be
inserted and read during these operations?
Thanks,
ben mMaint wiz uses DBCC DBREINDEX.
From memory, DBREINDEX will acquire shared table lock when rebuilding
non-clustered index. This will allow reads, but writes will be blocked.
Again, from memory, DBREINDEX will acquire exclusive table lock when
rebuilding clustered index- This will block both readers (unless code does
dirty reads) and writers.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Ben" <ben@.brainspout.com> wrote in message
news:O9Tb4hR5DHA.2732@.TK2MSFTNGP09.phx.gbl...

quote:

---

> When setting up a maintenance plan ,with the maintenance plan wizard,
> when SQL re-organizes data and index pages, will data be able to be
> inserted and read during these operations?
> Thanks,
> ben m
>

---

|||Would it be possible to manually execute the DBCC REINDEX command with
an option not to lock writes?
This is a transaction processing box.
thanks again,
-Ben
Tibor Karaszi wrote:

quote:

---

> Maint wiz uses DBCC DBREINDEX.
> From memory, DBREINDEX will acquire shared table lock when rebuilding
> non-clustered index. This will allow reads, but writes will be blocked.
> Again, from memory, DBREINDEX will acquire exclusive table lock when
> rebuilding clustered index- This will block both readers (unless code does
> dirty reads) and writers.
>

---

|||Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Ben" <ben@.brainspout.com> wrote in message news:ON8FIKa5DHA.1664@.TK2MSFTNGP11.phx.gbl...

quote:

---

[c
olor=darkred]
> Would it be possible to manually execute the DBCC REINDEX command with
> an option not to lock writes?
> This is a transaction processing box.
> thanks again,
> -Ben
> Tibor Karaszi wrote:
>
>[/color]

---

|||Do you know of any other maintenance tools that can be used, that will
allow writes to the table being modified?
Tibor Karaszi wrote:

quote:

---

> Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
>

---

|||It is not the client's decision whether to write at the same time as a rebui
ld is happening. It is
the server's decision. In SQK Server, all the methods to rebuilds an index,
will in the end call the
same code (the code which is called when an index is created by CREATE INDEX
), and this code uses
locks to protect against other modifications.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Ben" <ben@.brainspout.com> wrote in message news:%235k9sja5DHA.2764@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Do you know of any other maintenance tools that can be used, that will
> allow writes to the table being modified?
> Tibor Karaszi wrote:
>

---

|||See my reply to your previous post... checkout the whitepaper below that
gives you full details on this problem.
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben@.brainspout.com> wrote in message
news:#5k9sja5DHA.2764@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Do you know of any other maintenance tools that can be used, that will
> allow writes to the table being modified?
> Tibor Karaszi wrote:
>

---

|||Also, you might want to check out DBCC INDEXDEFRAG in case you haven't alrea
dy. I do recommend
reading the document that Paul posted. Good stuff.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u$Sut4a5DHA.2064@.TK2MSFTNGP11.phx.gbl...

quote:

---

> It is not the client's decision whether to write at the same time as a reb
uild is happening. It is
> the server's decision. In SQK Server, all the methods to rebuilds an index, will in the en
d call

---

the

quote:

---

> same code (the code which is called when an index is created by CREATE IND
EX), and this code uses
> locks to protect against other modifications.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...
lserver
>
> "Ben" <ben@.brainspout.com> wrote in message news:%235k9sja5DHA.2764@.TK2MSF
TNGP09.phx.gbl...
>

---

Maintenance Plan - Optimizations

I ran a Optimization plan on Sunday morning and my database grew from 14GB
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully. Seriously, you might consider doing full backups right after
a reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."

>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backup
s
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/Real...fraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Maintenance Plan - Optimizations

I ran a Optimization plan on Sunday morning and my database grew from 14GB
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?
Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully. Seriously, you might consider doing full backups right after
a reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."

>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backups
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/RealSQLGuy/DefraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Maintenance Plan - Optimizations

I ran a Optimization plan on Sunday morning and my database grew from 14GB
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully. :) Seriously, you might consider doing full backups right after
a reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backups
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/RealSQLGuy/DefraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Maintenance Plan

After I run the maintainence plan, check db index...
the log will grow up from zero size to 20 GB size.
i know how to truncate
but the point is why it grow so large.
is it any problems?I assume that the maint plan includes DBCC DBREINDEX. This will rebuild all
your indexes and those
rebuilds are logged to the transaction log. I suggest you read this articles
regarding reindexing:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"inamori" <test@.test.com> wrote in message news:cqt9t7$s003@.imsp212.netvigator.com...seagreen">
> After I run the maintainence plan, check db index...
> the log will grow up from zero size to 20 GB size.
> i know how to truncate
> but the point is why it grow so large.
> is it any problems?
>|||how about the data file
i shrink the data file from 30Gb to 15GB
after 3 hours, it grow up suddenly
can u tell me why?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl g...
> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
all your indexes and those
> rebuilds are logged to the transaction log. I suggest you read this
articles regarding reindexing:
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "inamori" <test@.test.com> wrote in message
news:cqt9t7$s003@.imsp212.netvigator.com...
>|||What actions did you perform during those three hours?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"inamori" <test@.test.com> wrote in message news:cqucqh$pvv1@.imsp212.netvigator.com...seagreen">
> how about the data file
> i shrink the data file from 30Gb to 15GB
> after 3 hours, it grow up suddenly
> can u tell me why?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
> news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl g...
> all your indexes and those
> articles regarding reindexing:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> news:cqt9t7$s003@.imsp212.netvigator.com...
>|||just do truncate and compress the data file
lets say
15GB used + 15 Free space
after i trucncate, 15 free space deleted
that mean 15 GB currently
but dont know why
3 hours later, 15 GB free sapce come out again
that mean grow back 30GB(15GB free + 15 used)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dSKeWb7EHA.3644@.TK2MSFTNGP09.phx.gbl...
> What actions did you perform during those three hours?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "inamori" <test@.test.com> wrote in message
news:cqucqh$pvv1@.imsp212.netvigator.com...
[vbcol=seagreen]
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx[vbcol=seagreen]
>|||SQL Server doesn't grow the database files by itself. You need to find out w
hat commands were
executed during these three hours that causes the database file size to grow
. My guess is that you
are doing a DBREINDEX during this time period.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Test" <TEst@.test.com> wrote in message news:es1Jo4g7EHA.2572@.tk2msftngp13.phx.gbl...seagreen">
> just do truncate and compress the data file
> lets say
> 15GB used + 15 Free space
> after i trucncate, 15 free space deleted
> that mean 15 GB currently
> but dont know why
> 3 hours later, 15 GB free sapce come out again
> that mean grow back 30GB(15GB free + 15 used)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23dSKeWb7EHA.3644@.TK2MSFTNGP09.phx.gbl...
> news:cqucqh$pvv1@.imsp212.netvigator.com...
>
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
>

Maintenance Plan

After I run the maintainence plan, check db index...
the log will grow up from zero size to 20 GB size.
i know how to truncate
but the point is why it grow so large.
is it any problems?
I assume that the maint plan includes DBCC DBREINDEX. This will rebuild all your indexes and those
rebuilds are logged to the transaction log. I suggest you read this articles regarding reindexing:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"inamori" <test@.test.com> wrote in message news:cqt9t7$s003@.imsp212.netvigator.com...
> After I run the maintainence plan, check db index...
> the log will grow up from zero size to 20 GB size.
> i know how to truncate
> but the point is why it grow so large.
> is it any problems?
>
|||how about the data file
i shrink the data file from 30Gb to 15GB
after 3 hours, it grow up suddenly
can u tell me why?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl g...
> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
all your indexes and those
> rebuilds are logged to the transaction log. I suggest you read this
articles regarding reindexing:
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "inamori" <test@.test.com> wrote in message
news:cqt9t7$s003@.imsp212.netvigator.com...
>
|||What actions did you perform during those three hours?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"inamori" <test@.test.com> wrote in message news:cqucqh$pvv1@.imsp212.netvigator.com...
> how about the data file
> i shrink the data file from 30Gb to 15GB
> after 3 hours, it grow up suddenly
> can u tell me why?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
> news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl g...
> all your indexes and those
> articles regarding reindexing:
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> news:cqt9t7$s003@.imsp212.netvigator.com...
>
|||just do truncate and compress the data file
lets say
15GB used + 15 Free space
after i trucncate, 15 free space deleted
that mean 15 GB currently
but dont know why
3 hours later, 15 GB free sapce come out again
that mean grow back 30GB(15GB free + 15 used)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dSKeWb7EHA.3644@.TK2MSFTNGP09.phx.gbl...
> What actions did you perform during those three hours?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "inamori" <test@.test.com> wrote in message
news:cqucqh$pvv1@.imsp212.netvigator.com...[vbcol=seagreen]
[vbcol=seagreen]
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
>
|||SQL Server doesn't grow the database files by itself. You need to find out what commands were
executed during these three hours that causes the database file size to grow. My guess is that you
are doing a DBREINDEX during this time period.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Test" <TEst@.test.com> wrote in message news:es1Jo4g7EHA.2572@.tk2msftngp13.phx.gbl...
> just do truncate and compress the data file
> lets say
> 15GB used + 15 Free space
> after i trucncate, 15 free space deleted
> that mean 15 GB currently
> but dont know why
> 3 hours later, 15 GB free sapce come out again
> that mean grow back 30GB(15GB free + 15 used)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23dSKeWb7EHA.3644@.TK2MSFTNGP09.phx.gbl...
> news:cqucqh$pvv1@.imsp212.netvigator.com...
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
>

Maintenance Plan

After I run the maintainence plan, check db index...
the log will grow up from zero size to 20 GB size.
i know how to truncate
but the point is why it grow so large.
is it any problems?I assume that the maint plan includes DBCC DBREINDEX. This will rebuild all your indexes and those
rebuilds are logged to the transaction log. I suggest you read this articles regarding reindexing:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"inamori" <test@.test.com> wrote in message news:cqt9t7$s003@.imsp212.netvigator.com...
> After I run the maintainence plan, check db index...
> the log will grow up from zero size to 20 GB size.
> i know how to truncate
> but the point is why it grow so large.
> is it any problems?
>|||how about the data file
i shrink the data file from 30Gb to 15GB
after 3 hours, it grow up suddenly
can u tell me why?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl ¤¤¼¶¼g...
> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
all your indexes and those
> rebuilds are logged to the transaction log. I suggest you read this
articles regarding reindexing:
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "inamori" <test@.test.com> wrote in message
news:cqt9t7$s003@.imsp212.netvigator.com...
> > After I run the maintainence plan, check db index...
> >
> > the log will grow up from zero size to 20 GB size.
> >
> > i know how to truncate
> >
> > but the point is why it grow so large.
> >
> > is it any problems?
> >
> >
>|||What actions did you perform during those three hours?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"inamori" <test@.test.com> wrote in message news:cqucqh$pvv1@.imsp212.netvigator.com...
> how about the data file
> i shrink the data file from 30Gb to 15GB
> after 3 hours, it grow up suddenly
> can u tell me why?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
> news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl ¤¤¼¶¼g...
>> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
> all your indexes and those
>> rebuilds are logged to the transaction log. I suggest you read this
> articles regarding reindexing:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "inamori" <test@.test.com> wrote in message
> news:cqt9t7$s003@.imsp212.netvigator.com...
>> > After I run the maintainence plan, check db index...
>> >
>> > the log will grow up from zero size to 20 GB size.
>> >
>> > i know how to truncate
>> >
>> > but the point is why it grow so large.
>> >
>> > is it any problems?
>> >
>> >
>>
>|||just do truncate and compress the data file
lets say
15GB used + 15 Free space
after i trucncate, 15 free space deleted
that mean 15 GB currently
but dont know why
3 hours later, 15 GB free sapce come out again
that mean grow back 30GB(15GB free + 15 used)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dSKeWb7EHA.3644@.TK2MSFTNGP09.phx.gbl...
> What actions did you perform during those three hours?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "inamori" <test@.test.com> wrote in message
news:cqucqh$pvv1@.imsp212.netvigator.com...
> > how about the data file
> >
> > i shrink the data file from 30Gb to 15GB
> >
> > after 3 hours, it grow up suddenly
> >
> > can u tell me why?
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l
¥ó
> > news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl ¤¤¼¶¼g...
> >> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
> > all your indexes and those
> >> rebuilds are logged to the transaction log. I suggest you read this
> > articles regarding reindexing:
> >>
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "inamori" <test@.test.com> wrote in message
> > news:cqt9t7$s003@.imsp212.netvigator.com...
> >> > After I run the maintainence plan, check db index...
> >> >
> >> > the log will grow up from zero size to 20 GB size.
> >> >
> >> > i know how to truncate
> >> >
> >> > but the point is why it grow so large.
> >> >
> >> > is it any problems?
> >> >
> >> >
> >>
> >>
> >
> >
>|||SQL Server doesn't grow the database files by itself. You need to find out what commands were
executed during these three hours that causes the database file size to grow. My guess is that you
are doing a DBREINDEX during this time period.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Test" <TEst@.test.com> wrote in message news:es1Jo4g7EHA.2572@.tk2msftngp13.phx.gbl...
> just do truncate and compress the data file
> lets say
> 15GB used + 15 Free space
> after i trucncate, 15 free space deleted
> that mean 15 GB currently
> but dont know why
> 3 hours later, 15 GB free sapce come out again
> that mean grow back 30GB(15GB free + 15 used)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23dSKeWb7EHA.3644@.TK2MSFTNGP09.phx.gbl...
>> What actions did you perform during those three hours?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "inamori" <test@.test.com> wrote in message
> news:cqucqh$pvv1@.imsp212.netvigator.com...
>> > how about the data file
>> >
>> > i shrink the data file from 30Gb to 15GB
>> >
>> > after 3 hours, it grow up suddenly
>> >
>> > can u tell me why?
>> >
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l
> ¥ó
>> > news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl ¤¤¼¶¼g...
>> >> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
>> > all your indexes and those
>> >> rebuilds are logged to the transaction log. I suggest you read this
>> > articles regarding reindexing:
>> >>
>> >
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "inamori" <test@.test.com> wrote in message
>> > news:cqt9t7$s003@.imsp212.netvigator.com...
>> >> > After I run the maintainence plan, check db index...
>> >> >
>> >> > the log will grow up from zero size to 20 GB size.
>> >> >
>> >> > i know how to truncate
>> >> >
>> >> > but the point is why it grow so large.
>> >> >
>> >> > is it any problems?
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Maintaining the database

If a database is keeping larger and larger in size, and having more than 1M
records.
How to improve the search speed in that database, even I did index the key..
Thanks
Indexing is the key (no pun intended) to search performance. However, you
also need to make sure query expressions are sargable and the query is well
written.
Perhaps someone can offer specific suggestions if you post the CREATE TABLE,
indexes, query and sample data.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Michael Lam" <[nospam]lamsoft@.lamsoft.net> wrote in message
news:uLjaiGfeIHA.4312@.TK2MSFTNGP03.phx.gbl...
> If a database is keeping larger and larger in size, and having more than
> 1M
> records.
> How to improve the search speed in that database, even I did index the
> key..
> Thanks
>

maint plan with syntax error from wizard

Hello,
I have created a fairly full-blown maint plan for a number of databases on
sql server standard 2005. The plan includes backup, index maintenance,
shrink, and a few other things I can't recall. This plan was created with
the maintenance wizard. When I try to run it I get a syntax error and in
viewing the generated t-sql I really don't see any problems with it. Has
anyone encountered this and what is the likely issue here? I guess I'm
implying that I don't really believe the error message since I don't believe
the wizard could really create a syntax error. Any help with this would be
greatly appreciated.
Thanks, NWOP.What is the T-SQL statement and the error messages?
By the way running shrink statements in your jobs and maintenance plan is
not a good idea and could impact the performance of your server.
Hope this helps,
Ben Nevarez
"new_world_order_pigs" wrote:
> Hello,
> I have created a fairly full-blown maint plan for a number of databases on
> sql server standard 2005. The plan includes backup, index maintenance,
> shrink, and a few other things I can't recall. This plan was created with
> the maintenance wizard. When I try to run it I get a syntax error and in
> viewing the generated t-sql I really don't see any problems with it. Has
> anyone encountered this and what is the likely issue here? I guess I'm
> implying that I don't really believe the error message since I don't believe
> the wizard could really create a syntax error. Any help with this would be
> greatly appreciated.
> Thanks, NWOP.|||Wow, you responded before the original showed up in the page. Thanks. The
only error message at all is "syntax error" and something along the lines of
job couldn't start. The only way I know to see the syntax is to "edit" the
step and click "show t-sql"-- if there is another way I'd like to know it and
also, if there are further error messages to look at I'd like to know that as
well.
Thanks again Ben,
NWOP.
"Ben Nevarez" wrote:
> What is the T-SQL statement and the error messages?
> By the way running shrink statements in your jobs and maintenance plan is
> not a good idea and could impact the performance of your server.
> Hope this helps,
> Ben Nevarez
>
>
> "new_world_order_pigs" wrote:
> > Hello,
> >
> > I have created a fairly full-blown maint plan for a number of databases on
> > sql server standard 2005. The plan includes backup, index maintenance,
> > shrink, and a few other things I can't recall. This plan was created with
> > the maintenance wizard. When I try to run it I get a syntax error and in
> > viewing the generated t-sql I really don't see any problems with it. Has
> > anyone encountered this and what is the likely issue here? I guess I'm
> > implying that I don't really believe the error message since I don't believe
> > the wizard could really create a syntax error. Any help with this would be
> > greatly appreciated.
> > Thanks, NWOP.|||If you have a problem with a job, select View History, expand the steps of
your job and look for error messages there. Also, some T-SQL statements write
entries to the SQL Server error log, so look for errors there as well.
Hope this helps,
Ben Nevarez
"new_world_order_pigs" wrote:
> Wow, you responded before the original showed up in the page. Thanks. The
> only error message at all is "syntax error" and something along the lines of
> job couldn't start. The only way I know to see the syntax is to "edit" the
> step and click "show t-sql"-- if there is another way I'd like to know it and
> also, if there are further error messages to look at I'd like to know that as
> well.
> Thanks again Ben,
> NWOP.
> "Ben Nevarez" wrote:
> >
> > What is the T-SQL statement and the error messages?
> >
> > By the way running shrink statements in your jobs and maintenance plan is
> > not a good idea and could impact the performance of your server.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "new_world_order_pigs" wrote:
> >
> > > Hello,
> > >
> > > I have created a fairly full-blown maint plan for a number of databases on
> > > sql server standard 2005. The plan includes backup, index maintenance,
> > > shrink, and a few other things I can't recall. This plan was created with
> > > the maintenance wizard. When I try to run it I get a syntax error and in
> > > viewing the generated t-sql I really don't see any problems with it. Has
> > > anyone encountered this and what is the likely issue here? I guess I'm
> > > implying that I don't really believe the error message since I don't believe
> > > the wizard could really create a syntax error. Any help with this would be
> > > greatly appreciated.
> > > Thanks, NWOP.|||Ben Nevarez wrote:
> If you have a problem with a job, select View History, expand the steps of
> your job and look for error messages there. Also, some T-SQL statements write
> entries to the SQL Server error log, so look for errors there as well.
> Hope this helps,
> Ben Nevarez
>
>
> "new_world_order_pigs" wrote:
>
>> Wow, you responded before the original showed up in the page. Thanks. The
>> only error message at all is "syntax error" and something along the lines of
>> job couldn't start. The only way I know to see the syntax is to "edit" the
>> step and click "show t-sql"-- if there is another way I'd like to know it and
>> also, if there are further error messages to look at I'd like to know that as
>> well.
>> Thanks again Ben,
>> NWOP.
>> "Ben Nevarez" wrote:
>>
>> What is the T-SQL statement and the error messages?
>> By the way running shrink statements in your jobs and maintenance plan is
>> not a good idea and could impact the performance of your server.
>> Hope this helps,
>> Ben Nevarez
>>
>>
>> "new_world_order_pigs" wrote:
>>
>> Hello,
>> I have created a fairly full-blown maint plan for a number of databases on
>> sql server standard 2005. The plan includes backup, index maintenance,
>> shrink, and a few other things I can't recall. This plan was created with
>> the maintenance wizard. When I try to run it I get a syntax error and in
>> viewing the generated t-sql I really don't see any problems with it. Has
>> anyone encountered this and what is the likely issue here? I guess I'm
>> implying that I don't really believe the error message since I don't believe
>> the wizard could really create a syntax error. Any help with this would be
>> greatly appreciated.
>> Thanks, NWOP.
Since this is a maintenance plan - right click on the maintenance plan
and view history instead of the agent job. This history will show you
each step in your maintenance plan and give you more information about
what exactly is failing.|||Hello again and thanks for all the assistance. I have now done all of these
things and actually taken each and every step separately, viewed the t-sql,
executed it step by step and get a success on each one. So, I'm thinking it
must be something having to do with the manner in which it is evoked. I
guess I'm still surprised this isn't a common and recognizeable problem since
I've never believed that I'm "so special." In any case, where would I look
to see how the job itself is being invoked? Perhaps that is it? Perhaps I'm
missing something?
Thanks again,
NWOP.
"Jeffrey Williams" wrote:
> Ben Nevarez wrote:
> > If you have a problem with a job, select View History, expand the steps of
> > your job and look for error messages there. Also, some T-SQL statements write
> > entries to the SQL Server error log, so look for errors there as well.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "new_world_order_pigs" wrote:
> >
> >
> >> Wow, you responded before the original showed up in the page. Thanks. The
> >> only error message at all is "syntax error" and something along the lines of
> >> job couldn't start. The only way I know to see the syntax is to "edit" the
> >> step and click "show t-sql"-- if there is another way I'd like to know it and
> >> also, if there are further error messages to look at I'd like to know that as
> >> well.
> >>
> >> Thanks again Ben,
> >> NWOP.
> >>
> >> "Ben Nevarez" wrote:
> >>
> >>
> >> What is the T-SQL statement and the error messages?
> >>
> >> By the way running shrink statements in your jobs and maintenance plan is
> >> not a good idea and could impact the performance of your server.
> >>
> >> Hope this helps,
> >>
> >> Ben Nevarez
> >>
> >>
> >>
> >>
> >> "new_world_order_pigs" wrote:
> >>
> >>
> >> Hello,
> >>
> >> I have created a fairly full-blown maint plan for a number of databases on
> >> sql server standard 2005. The plan includes backup, index maintenance,
> >> shrink, and a few other things I can't recall. This plan was created with
> >> the maintenance wizard. When I try to run it I get a syntax error and in
> >> viewing the generated t-sql I really don't see any problems with it. Has
> >> anyone encountered this and what is the likely issue here? I guess I'm
> >> implying that I don't really believe the error message since I don't believe
> >> the wizard could really create a syntax error. Any help with this would be
> >> greatly appreciated.
> >> Thanks, NWOP.
> >>
> Since this is a maintenance plan - right click on the maintenance plan
> and view history instead of the agent job. This history will show you
> each step in your maintenance plan and give you more information about
> what exactly is failing.
>

MAC used as search index for Encrypted data: how secure?

I have read recommendations about searching encrypted data. Typically, they involve creating a MAC (message authentication code) table. One of the elements of that table is a HASH of the encrypted data (plus a Mac key) that is used as an index for searching. Is that HASH as secure as the encrypted data itself, or is this approach less secure? If it is less secure, then may I assume that approach is the only feasible way to search data encrypted by nondeterministic algorithms?

TIA,

Barkingdog

Is the hash as secure as the encrypted data? This is a difficult question, because the answer depends on what you are trying to secure against, and what algorithms you are using, and what vulnerabilities they develop over time. One thing that a hash would disclose, and that encryption would normally not disclose, is data identity. That is, you can encrypt the same piece of data and you won't be able to determine from the resulting blobs whether they are corresponding to identical data, but if you hash the data, the result will be identical.

Strictly speaking, the only way to search encrypted data (and I mean non-deterministic, although I should not need to specify this because encryption is always intended to be a non-deterministic operation) is to decrypt all data and search through the decrypted text. Using hashes is a workaround that allows you to search hashes instead of the encrypted data, to answer an equality search. You can probably devise other alternative search schemes, depending on what searches you want to allow and how much information you are willing to give away.

Thanks
Laurentiu

|||

>>>Using hashes is a workaround

Yes, I agree with that. I'm just concerned that the creation of hashes (out of practical necessity) results in a workable solution because the hash codes are easier to "crack" than the encrypted data itself. Maybe "cracking" the hash can lead to cracking the encrypted data?

Barkingdog.

|||

Strictly speaking, yes it is possible to user a rainbow attack against a pure hash on the data we are trying to protect, especially if the domain of the plaintext is finite and well defined (i.e. SSN, CCN, etc.). For example, I can easily create an offline rainbow table with all possible hashes (SHA1, MD5, etc.) for every possible SSN, once I find your hashed value I just need to find the corresponding value on the rainbow table.

This is the reason why I was suggesting using an HMAC, that way the domain of the plaintext is different and creating a dictionary is far more expensive that the example above. You are still giving away some information to the potential attacker, but the difference is that now there is a random bag of bits of arbitrary length (the HMAC key) combined with the data we want to protect, that way the hash input does not belong to the same domain as the original plaintext.

Assuming that the key is truly random, large enough and well protected, the previous attack is rendered useless, and to the best of our knowledge the attacker will need to brute force all possibilities (i.e. hash( ‘111-111-111’ + 0x00…001 ), hash( ‘111-111-111’ + 0x00…002 ) … hash( ‘999-999-999’ + 0xFF…FFF ), as you can see this is far more expensive, and the larger the key, the better.

Is this better or worse than trying to brute force a symmetric key? That is a really difficult question as Laurentiu mentioned. Hashes are less expensive (computational wise) than decryptions, but there are too many factors to consider (domain of your plaintext, domain of the key, reusing keys, new attack methods being discovered, etc.).

The best option if you want to create an index over the encrypted data may be to create a completely new identifier for it, i.e. a customer ID that is completely unrelated to the data being protected, but we understand this option is not always possible due to business reasons.

I would recommend defining what assets you are trying to protect and against what kind of threats. From there you need to evaluate what options you have to protect against such threats, as well as what mechanisms (preferably use defense in depth) you have to prevent, detect and halt a possible attack, as well as what are the steps to follow after the situation is back under control.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine