long delete

Hi
I have person table that is taking about 90 seconds to delete a person. The
person table is related to about 20 other tables on the personid with the
option exforce relationship for replication and enforce relationship for
inserts and updates. I checked the indexes and they're all indexed. Does
anyone know what could cause such a long delete
thanks
p
Do you have indexes on all the foreign key columns (the referencing tables)? Any blocking going on?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alfred" <alfred@.discussions.microsoft.com> wrote in message
news:00C44F73-7BFA-4CBA-9720-A7BAA5188998@.microsoft.com...
> Hi
> I have person table that is taking about 90 seconds to delete a person. The
> person table is related to about 20 other tables on the personid with the
> option exforce relationship for replication and enforce relationship for
> inserts and updates. I checked the indexes and they're all indexed. Does
> anyone know what could cause such a long delete
> thanks
> p
|||Hi
Whne it is running, check for any blocking by another process.
Affecting 20 tables does take some time, especially if there are multiple
rows on child tables for each parent row.
Have a look at SQL Profile whilst it is running and check the IO statistics.
Regards
Mike
"alfred" wrote:

> Hi
> I have person table that is taking about 90 seconds to delete a person. The
> person table is related to about 20 other tables on the personid with the
> option exforce relationship for replication and enforce relationship for
> inserts and updates. I checked the indexes and they're all indexed. Does
> anyone know what could cause such a long delete
> thanks
> p
|||Hi
there are not alot of child in the relationship from the number of rows
affected, usually around 2 or 3 rows affected. I checked the sql profiler
trace file. What is taking up the time are these cacheremove events that
execute sql statements that are not really being called by the delperson
procedure. How could they be called?
thanks
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Whne it is running, check for any blocking by another process.
> Affecting 20 tables does take some time, especially if there are multiple
> rows on child tables for each parent row.
> Have a look at SQL Profile whilst it is running and check the IO statistics.
> Regards
> Mike
>
> "alfred" wrote:
|||hi,
I also run the delete person where personid = 123 on index tuning wizard,
but I keep getting this error
"there is insufficent memory to perform index analysis."
what could cause that
thanks
P
"Tibor Karaszi" wrote:

> Do you have indexes on all the foreign key columns (the referencing tables)? Any blocking going on?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alfred" <alfred@.discussions.microsoft.com> wrote in message
> news:00C44F73-7BFA-4CBA-9720-A7BAA5188998@.microsoft.com...
>
>
|||I don't know, only reference for that error I found is:
http://groups.google.com/groups?hl=s...eta%3Dgroup%25
3Dmicrosoft.public.sqlserver.*
Did you verify that the referencing table has indexes on the foreign key columns?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alfred" <alfred@.discussions.microsoft.com> wrote in message
news:286C0462-19C4-4C5B-95D7-F16F3112890B@.microsoft.com...[vbcol=seagreen]
> hi,
> I also run the delete person where personid = 123 on index tuning wizard,
> but I keep getting this error
> "there is insufficent memory to perform index analysis."
> what could cause that
> thanks
> P
> "Tibor Karaszi" wrote:

Logshipping

Hi
I have a strange problem started happening in my log shipping. The database
went to suspect/loading/read-only mode in the middle of the night.
Can't see any particular reason in the sql srever errorlog.
One thing i noticed in th msdb..backupset. This table did not have an entry
for anyother transaction log that happened after the last transaction log
that got applid to the database. But the logshipping copy job is fine and it
copies all the logs. Something broken internally in the logshpping. I looked
online for anymore info. Apparently there was a hotfix before SP3. We already
have sp3 on this server.
Did anyone experience this problem?
Any idea?
Any kind of help is appreciated.
Thanks
Uday
Hi
Can you see how did the last LOG restore to the database? What happened?
"Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
news:27D5BE75-8F73-44CE-8614-EEE00372D2C6@.microsoft.com...
> Hi
> I have a strange problem started happening in my log shipping. The
> database
> went to suspect/loading/read-only mode in the middle of the night.
> Can't see any particular reason in the sql srever errorlog.
> One thing i noticed in th msdb..backupset. This table did not have an
> entry
> for anyother transaction log that happened after the last transaction log
> that got applid to the database. But the logshipping copy job is fine and
> it
> copies all the logs. Something broken internally in the logshpping. I
> looked
> online for anymore info. Apparently there was a hotfix before SP3. We
> already
> have sp3 on this server.
> Did anyone experience this problem?
> Any idea?
> Any kind of help is appreciated.
> Thanks
> Uday
|||The logshipping_plan_databases table says that the last file loaded was the
midbight file. The database didn't get recovered after that.
It doesn't tell me any reason why this happened.
It went to suspect. I reset the suspect bit and restarted the services.
That didn't help.
--Uday
"Uri Dimant" wrote:

> Hi
> Can you see how did the last LOG restore to the database? What happened?
> "Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
> news:27D5BE75-8F73-44CE-8614-EEE00372D2C6@.microsoft.com...
>
>
|||Hi Uri
I also notice something strange.
The database that was doing the full backup job failed.
It actually did do the backup. But the job failed saying that it can't find
the network path name. The full backup job says that it ran for 1:26minutes
before it failed. But i see transaction log for every 15minutes in that 1:26
minutes. Usually when the ful backup starts, the transaction log back starts
but never does the transaction log backup until its completed. Right.
This is strange.
--Uday
"Uri Dimant" wrote:

> Hi
> Can you see how did the last LOG restore to the database? What happened?
> "Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
> news:27D5BE75-8F73-44CE-8614-EEE00372D2C6@.microsoft.com...
>
>
|||Ignore this reply.
It did say that it waited for the hour that it took for the full backup to
fail.
I had the backup file. Bayut the job reported failure.
Back to square onw. I am not sure what happened.
Help please
Ud
"Udayasree" wrote:
[vbcol=seagreen]
> Hi Uri
> I also notice something strange.
> The database that was doing the full backup job failed.
> It actually did do the backup. But the job failed saying that it can't find
> the network path name. The full backup job says that it ran for 1:26minutes
> before it failed. But i see transaction log for every 15minutes in that 1:26
> minutes. Usually when the ful backup starts, the transaction log back starts
> but never does the transaction log backup until its completed. Right.
> This is strange.
> --Uday
> "Uri Dimant" wrote:
|||Well, try restore a last good full backup.
"Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
news:B119E88C-4856-4C0F-B481-1CA7656C0266@.microsoft.com...[vbcol=seagreen]
> Ignore this reply.
> It did say that it waited for the hour that it took for the full backup to
> fail.
> I had the backup file. Bayut the job reported failure.
> Back to square onw. I am not sure what happened.
> Help please
> Ud
> "Udayasree" wrote:
|||I did.
This has been happening lately. Thats how i reestablish the logshipping.
But if this happens on a big database, it will take me a day or 2 to bring
the standby database.
You think i should call Microsoft.
You guys never encountered this issue.
Uday
"Uri Dimant" wrote:

> Well, try restore a last good full backup.
>
>
> "Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
> news:B119E88C-4856-4C0F-B481-1CA7656C0266@.microsoft.com...
>
>

Logshipping

Hi

I have a problem in logshipping any one help would be appreciated.

during logshipping when i am adding data (log.data,transactlog)to the
destination database when i select the destination server name(server2)it
didn't showing any drives present .instead showing local drives.if i select
3rd server then it shows perfect all the available drives .If any one knows
the error please let me know u help would be appreciated.

Thanks,
pardhi

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1I don't really know, but I would first look at permissions - the MSSQL
service account may not be able to access some drives. If this isn't
helpful, you should give some more details - exactly which dialogue
you're looking at (I guess you're using the maintenance plan wizard?),
which drives you can see, which drives you can't see (are they local
drives, mapped network drives, UNC paths...), what happens if you use
sp_add_log_shipping_plan directly etc.

Simon|||Thanks simon for your response.

Its weired working from home and not from office.

Thanks

Simon Hayes wrote:
>I don't really know, but I would first look at permissions - the MSSQL
>service account may not be able to access some drives. If this isn't
>helpful, you should give some more details - exactly which dialogue
>you're looking at (I guess you're using the maintenance plan wizard?),
>which drives you can see, which drives you can't see (are they local
>drives, mapped network drives, UNC paths...), what happens if you use
>sp_add_log_shipping_plan directly etc.
>Simon

--
Message posted via http://www.sqlmonster.com|||Hello, Iam also having similer problem.. would be interested to see more
coments.

Thanks,

--
admin

Admin

http://www.exforsys.com
http://www.geekinterview.com
http://www.itquestionbank.com
http://www.myitblog.com
----------------------
admin's Profile: http://www.exforsys.com/forum/member.php?userid=1
View this thread: http://www.exforsys.com/forum/showthread.php?t=5864|||Hello, I am having similer problem.. would be interested to see more
comments in detail..

Thanks,

--
techguru
----------------------
techguru's Profile: http://www.exforsys.com/forum/member.php?userid=9401
View this thread: http://www.exforsys.com/forum/showthread.php?t=5864

Logshipping

Hi
I have a strange problem started happening in my log shipping. The database
went to suspect/loading/read-only mode in the middle of the night.
Can't see any particular reason in the sql srever errorlog.
One thing i noticed in th msdb..backupset. This table did not have an entry
for anyother transaction log that happened after the last transaction log
that got applid to the database. But the logshipping copy job is fine and it
copies all the logs. Something broken internally in the logshpping. I looked
online for anymore info. Apparently there was a hotfix before SP3. We alread
y
have sp3 on this server.
Did anyone experience this problem?
Any idea?
Any kind of help is appreciated.
Thanks
UdayHi
Can you see how did the last LOG restore to the database? What happened?
"Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
news:27D5BE75-8F73-44CE-8614-EEE00372D2C6@.microsoft.com...
> Hi
> I have a strange problem started happening in my log shipping. The
> database
> went to suspect/loading/read-only mode in the middle of the night.
> Can't see any particular reason in the sql srever errorlog.
> One thing i noticed in th msdb..backupset. This table did not have an
> entry
> for anyother transaction log that happened after the last transaction log
> that got applid to the database. But the logshipping copy job is fine and
> it
> copies all the logs. Something broken internally in the logshpping. I
> looked
> online for anymore info. Apparently there was a hotfix before SP3. We
> already
> have sp3 on this server.
> Did anyone experience this problem?
> Any idea?
> Any kind of help is appreciated.
> Thanks
> Uday|||The logshipping_plan_databases table says that the last file loaded was the
midbight file. The database didn't get recovered after that.
It doesn't tell me any reason why this happened.
It went to suspect. I reset the suspect bit and restarted the services.
That didn't help.
--Uday
"Uri Dimant" wrote:

> Hi
> Can you see how did the last LOG restore to the database? What happened?
> "Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
> news:27D5BE75-8F73-44CE-8614-EEE00372D2C6@.microsoft.com...
>
>|||Hi Uri
I also notice something strange.
The database that was doing the full backup job failed.
It actually did do the backup. But the job failed saying that it can't find
the network path name. The full backup job says that it ran for 1:26minutes
before it failed. But i see transaction log for every 15minutes in that 1:26
minutes. Usually when the ful backup starts, the transaction log back starts
but never does the transaction log backup until its completed. Right.
This is strange.
--Uday
"Uri Dimant" wrote:

> Hi
> Can you see how did the last LOG restore to the database? What happened?
> "Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
> news:27D5BE75-8F73-44CE-8614-EEE00372D2C6@.microsoft.com...
>
>|||Ignore this reply.
It did say that it waited for the hour that it took for the full backup to
fail.
I had the backup file. Bayut the job reported failure.
Back to square onw. I am not sure what happened.
Help please
Ud
"Udayasree" wrote:
[vbcol=seagreen]
> Hi Uri
> I also notice something strange.
> The database that was doing the full backup job failed.
> It actually did do the backup. But the job failed saying that it can't fin
d
> the network path name. The full backup job says that it ran for 1:26minute
s
> before it failed. But i see transaction log for every 15minutes in that 1:
26
> minutes. Usually when the ful backup starts, the transaction log back star
ts
> but never does the transaction log backup until its completed. Right.
> This is strange.
> --Uday
> "Uri Dimant" wrote:
>|||Well, try restore a last good full backup.
"Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
news:B119E88C-4856-4C0F-B481-1CA7656C0266@.microsoft.com...[vbcol=seagreen]
> Ignore this reply.
> It did say that it waited for the hour that it took for the full backup to
> fail.
> I had the backup file. Bayut the job reported failure.
> Back to square onw. I am not sure what happened.
> Help please
> Ud
> "Udayasree" wrote:
>|||I did.
This has been happening lately. Thats how i reestablish the logshipping.
But if this happens on a big database, it will take me a day or 2 to bring
the standby database.
You think i should call Microsoft.
You guys never encountered this issue.
Uday
"Uri Dimant" wrote:

> Well, try restore a last good full backup.
>
>
> "Udayasree" <Udayasree@.discussions.microsoft.com> wrote in message
> news:B119E88C-4856-4C0F-B481-1CA7656C0266@.microsoft.com...
>
>sql

Logs

Hi
I have a database In SQL server and I want to keep a log of all the
activity’s that that the end user did I want to know who did and what he
did and when he did what is the bet way to keep track on that. In my previews
application I made a logs table with fields tableid, tablename, userid
,action, datetime and then always when insert update or delete in table we
save it also to logs, is this idea good for SQL server or there is a better
way to do it with SQL server archiving
Read about blackbox in Books Online and see if thats what you were looking
for.
Or use profiler.
Thanks,
Sree
"hershel" wrote:

> Hi
> I have a database In SQL server and I want to keep a log of all the
> activity’s that that the end user did I want to know who did and what he
> did and when he did what is the bet way to keep track on that. In my previews
> application I made a logs table with fields tableid, tablename, userid
> ,action, datetime and then always when insert update or delete in table we
> save it also to logs, is this idea good for SQL server or there is a better
> way to do it with SQL server archiving
>

Logs

Hi
I have a database In SQL server and I want to keep a log of all the
activity’s that that the end user did I want to know who did and what he
did and when he did what is the bet way to keep track on that. In my preview
s
application I made a logs table with fields tableid, tablename, userid
,action, datetime and then always when insert update or delete in table we
save it also to logs, is this idea good for SQL server or there is a better
way to do it with SQL server archivingRead about blackbox in Books Online and see if thats what you were looking
for.
Or use profiler.
Thanks,
Sree
"hershel" wrote:

> Hi
> I have a database In SQL server and I want to keep a log of all the
> activity’s that that the end user did I want to know who did and what
he
> did and when he did what is the bet way to keep track on that. In my previ
ews
> application I made a logs table with fields tableid, tablename, userid
> ,action, datetime and then always when insert update or delete in table we
> save it also to logs, is this idea good for SQL server or there is a bette
r
> way to do it with SQL server archiving
>sql

Login to db

Hi
I have two programs that connect to the same db using exact same conncetion
string but only one manages to connect.
Any explanations
Thanks
Sam
Can you provide a little more details like maybe the error message? Have
you checked to see if they are both using TCP or named piepes etc?
Andrew J. Kelly SQL MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23p5GRLUfEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have two programs that connect to the same db using exact same
conncetion
> string but only one manages to connect.
> Any explanations
> Thanks
> Sam
>
|||Hi,
Could you enable the SQL Profiler and run the applications seperately and
check what happends exactly.
Does both your application uses same login, database ?
Thanks
Hari
MCDBA
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:#p5GRLUfEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have two programs that connect to the same db using exact same
conncetion
> string but only one manages to connect.
> Any explanations
> Thanks
> Sam
>

Login to db

Hi
I have two programs that connect to the same db using exact same conncetion
string but only one manages to connect.
Any explanations
Thanks
SamCan you provide a little more details like maybe the error message? Have
you checked to see if they are both using TCP or named piepes etc?
Andrew J. Kelly SQL MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23p5GRLUfEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have two programs that connect to the same db using exact same
conncetion
> string but only one manages to connect.
> Any explanations
> Thanks
> Sam
>|||Hi,
Could you enable the SQL Profiler and run the applications seperately and
check what happends exactly.
Does both your application uses same login, database ?
Thanks
Hari
MCDBA
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:#p5GRLUfEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have two programs that connect to the same db using exact same
conncetion
> string but only one manages to connect.
> Any explanations
> Thanks
> Sam
>

Login problems on Vista before and after SP2

Hi:

I have recently updated my Sony Vaio and installed Windows Vista. After installing I could not access my SQL databases because of the NT Authorisation / sysadmin problem.

I have since installed SQLServer SP2 (Nov CTP) but the problem persists. If I run SQL Management Studio under the administrative account I can login just fine. I have also made sure the 'NT AUTHORITY\SYSTEM' is marked for sysadmin access (which it is).

The eror I get is Error 18456.

What else can I do to gain access via Windows Authorisation?

TIA,

MartinH.

Hello Martin,

Are you are logging into the Vista under any domain credentials? If so please add the domain user as the local system administrator and try.

Thanks,

Prakash P [MSFT]

|||

Prakash:

Sorry to be so dense, but I'm not quite sure what you are asking.

I am using Vista on a portable that does not connect to any Domain Server. The locally created user belongs to the Administrators group.

Is this what you meant by 'please add the domain user as the local system administrator'?

Thanks for your reply.

Martin.

|||

Here is a workaround for resolving this issue:

1. Run SSMS “As an Administrator”, by right clicking the SSMS icon and clicking “Run As Administrator” option and select Windows Authentication and login to SQL Server.

2. In the Object Explorer node select the Security node and then select Logins node. Add a new login for your windows account and select Windows Authentication. Also go the Server Roles pane in the same window and select sysadmin role for it.

3. Close SSMS and start it normally and give your domain account and select Windows Authentication and you will not have any issues thereafter.

|||

Rajesh:

That did the trick!!

Thanks very much for your help.

Regards,

Martin.

Login problem, mystery

Hi
I have installed MSDE ver 8.00.760.03 from the package sql2desksp03 located
at go.microsoft.com/fwlink/?linkid=13962
My intention is to develop an asp.net application with Microsoft Visual
Basic.Net Standard. Already installed. Because I can't create a database
with the
standard edition I want to use MS Access 2000 project to create the tables,
but when I try to create the project using the Microsoft SQL Server database
wizard and enter my SQL Server details as follows
name=svrname
user=sa
password=correctpassword
I get the following message.
Connection failed:
SQLState:'28000'
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'sa'.
Reason: Not associated with a trusted SQL Server connection.
If I leave the Microsoft SQL Server database wizard user and password boxes
empty I am able to create my project but unable to create tables.
I'm on WinXP SP2 with Administrative rights. MSDE is running and my password
is definately correct - Please help :-)
hi,
SimonW wrote:
> Hi
> I have installed MSDE ver 8.00.760.03 from the package sql2desksp03
> located at go.microsoft.com/fwlink/?linkid=13962
> My intention is to develop an asp.net application with Microsoft
> Visual Basic.Net Standard. Already installed. Because I can't create
> a database with the
> standard edition I want to use MS Access 2000 project to create the
> tables, but when I try to create the project using the Microsoft SQL
> Server database
> wizard and enter my SQL Server details as follows
> name=svrname
> user=sa
> password=correctpassword
> I get the following message.
> Connection failed:
> SQLState:'28000'
> SQL Server Error: 18452
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
> 'sa'. Reason: Not associated with a trusted SQL Server connection.
> If I leave the Microsoft SQL Server database wizard user and password
> boxes empty I am able to create my project but unable to create
> tables.
> I'm on WinXP SP2 with Administrative rights. MSDE is running and my
> password is definately correct - Please help :-)
MSDE installs by default only allowing trusted conections... you can modify
this behavior bot at install time, providing the
SECURITYMODE=SQL
parameter to the setup.exe boostrap installer or later, at runtime, hacking
the Windows registry as described in
http://support.microsoft.com/default...n-us;285097... stop and
restart the server..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very much Andrea :-)
"Andrea Montanari" wrote:

> hi,
> SimonW wrote:
> MSDE installs by default only allowing trusted conections... you can modify
> this behavior bot at install time, providing the
> SECURITYMODE=SQL
> parameter to the setup.exe boostrap installer or later, at runtime, hacking
> the Windows registry as described in
> http://support.microsoft.com/default...n-us;285097... stop and
> restart the server..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>