Delete Database

A database disappeared from one of our qa servers last night yet when we looked at the logs no record of the drop datbase command was to be found like wise no record of create database.
Can any one tell me where we can look in sqlserver or on the windows 2000 server fro a record of these eventsYikes! I'd never really looked at my SQL Error log files when I dropped a database, but it appears that there is no entry for this event in there. I dunno where to tell you to go from here...

I would suggest that you audit your security settings, remove anyone from the sysadmin group that doesn't absolutely positively have to be there and for sure set up to audit failed logins.

Regards,

hmscott|||Might try the event logs on the server. At least you'll have an idea who was doing what and when.|||Thanks for the advice fortunately it was a dev server so no real damange and we could restore it.
Lokks like there may be a case to go to microsoft and see if it could be added .

Delegation stops working after a while

I have a linked server set up between two servers (A & B).
Server A is running 2005 and Server B is running 2000.
Both servers SQL services are running using a domain user account and have
their SPN's registered in the AD.
The client connects to Server A using integrated security (TCP/IP and
Kerberos not NTLM) and runs disributed queries using the linked server to
server B.
Delegation is set up in the AD and is working, at least for some time.
After a while (sometimes a couple of minutes, sometimes a couple of hours)
the delegation seems to stop working and the client recieves the error
"Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection." The client is still connected and authenticated using TCP/IP
and
Kerberos.
After a restart of SQL Server on server A the delegation starts working
again.
I cannt find anything in the eventlogs on either one of the servers or the
client, and nothing in the sql server logs.
Does anybody have any idea of what could be wrong, or give me a though on
where to start looking.
Thanks
/MattiasWe are encountering this problem also. I have contacted MS but they are
still gathering information.
There is at least one other unanswered post on this here as well, see
subject = "SQL2005 Linked server authentication drops".
Are you running sql server under under a domain account that is not in the
local admins group by any chance?
--
-b
"Mattias" wrote:
> I have a linked server set up between two servers (A & B).
> Server A is running 2005 and Server B is running 2000.
> Both servers SQL services are running using a domain user account and have
> their SPN's registered in the AD.
> The client connects to Server A using integrated security (TCP/IP and
> Kerberos not NTLM) and runs disributed queries using the linked server to
> server B.
> Delegation is set up in the AD and is working, at least for some time.
> After a while (sometimes a couple of minutes, sometimes a couple of hours)
> the delegation seems to stop working and the client recieves the error
> "Login
> failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection." The client is still connected and authenticated using TCP/IP
> and
> Kerberos.
> After a restart of SQL Server on server A the delegation starts working
> again.
> I cannt find anything in the eventlogs on either one of the servers or the
> client, and nothing in the sql server logs.
> Does anybody have any idea of what could be wrong, or give me a though on
> where to start looking.
> Thanks
> /Mattias
>
>|||I am the other unanswered posting!
It fails intermittently when we run under 'sa' or a domain account that is
in the local admin group.
I have raised this through the Microsoft concierge service and they said
there are others with the same problem but no resolutions as yet!
Wendy
"BBogart" wrote:
> We are encountering this problem also. I have contacted MS but they are
> still gathering information.
> There is at least one other unanswered post on this here as well, see
> subject = "SQL2005 Linked server authentication drops".
> Are you running sql server under under a domain account that is not in the
> local admins group by any chance?
> --
> -b
>
> "Mattias" wrote:
> > I have a linked server set up between two servers (A & B).
> > Server A is running 2005 and Server B is running 2000.
> > Both servers SQL services are running using a domain user account and have
> > their SPN's registered in the AD.
> >
> > The client connects to Server A using integrated security (TCP/IP and
> > Kerberos not NTLM) and runs disributed queries using the linked server to
> > server B.
> > Delegation is set up in the AD and is working, at least for some time.
> >
> > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > the delegation seems to stop working and the client recieves the error
> > "Login
> > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > connection." The client is still connected and authenticated using TCP/IP
> > and
> > Kerberos.
> >
> > After a restart of SQL Server on server A the delegation starts working
> > again.
> >
> > I cannt find anything in the eventlogs on either one of the servers or the
> > client, and nothing in the sql server logs.
> >
> > Does anybody have any idea of what could be wrong, or give me a though on
> > where to start looking.
> >
> > Thanks
> > /Mattias
> >
> >
> >|||We have seen this in our environment too. One moment a linked server query
will work just fine with delegated Windows credentials, the next moment you
receive errors like the following:
OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I am still working on a reproducable way of generating the message, but I
seem to have problems a lot when I initially generate a linked server within
SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
I suspect that something in the Kerberos token is expiring. After I logout
and login, I can usually start a new session that works (just not today).
"Woo" wrote:
> I am the other unanswered posting!
> It fails intermittently when we run under 'sa' or a domain account that is
> in the local admin group.
> I have raised this through the Microsoft concierge service and they said
> there are others with the same problem but no resolutions as yet!
> Wendy
>
>
> "BBogart" wrote:
> > We are encountering this problem also. I have contacted MS but they are
> > still gathering information.
> >
> > There is at least one other unanswered post on this here as well, see
> > subject = "SQL2005 Linked server authentication drops".
> >
> > Are you running sql server under under a domain account that is not in the
> > local admins group by any chance?
> > --
> > -b
> >
> >
> > "Mattias" wrote:
> >
> > > I have a linked server set up between two servers (A & B).
> > > Server A is running 2005 and Server B is running 2000.
> > > Both servers SQL services are running using a domain user account and have
> > > their SPN's registered in the AD.
> > >
> > > The client connects to Server A using integrated security (TCP/IP and
> > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > server B.
> > > Delegation is set up in the AD and is working, at least for some time.
> > >
> > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > the delegation seems to stop working and the client recieves the error
> > > "Login
> > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > connection." The client is still connected and authenticated using TCP/IP
> > > and
> > > Kerberos.
> > >
> > > After a restart of SQL Server on server A the delegation starts working
> > > again.
> > >
> > > I cannt find anything in the eventlogs on either one of the servers or the
> > > client, and nothing in the sql server logs.
> > >
> > > Does anybody have any idea of what could be wrong, or give me a though on
> > > where to start looking.
> > >
> > > Thanks
> > > /Mattias
> > >
> > >
> > >|||I also suspect a ticket is expiring.
I am still working with MS on this with no resolution yet.
Once we see a failure, failures continue regardless of logging off and back
on until sql server is restarted. A reboot is not necessary as I previously
thought.
We have seen it take as little as a few hours or up to a week or more for
the failures to start again.
--
-b
"JD Qixcle" wrote:
> We have seen this in our environment too. One moment a linked server query
> will work just fine with delegated Windows credentials, the next moment you
> receive errors like the following:
> OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I am still working on a reproducable way of generating the message, but I
> seem to have problems a lot when I initially generate a linked server within
> SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
> I suspect that something in the Kerberos token is expiring. After I logout
> and login, I can usually start a new session that works (just not today).
>
>
> "Woo" wrote:
> > I am the other unanswered posting!
> >
> > It fails intermittently when we run under 'sa' or a domain account that is
> > in the local admin group.
> >
> > I have raised this through the Microsoft concierge service and they said
> > there are others with the same problem but no resolutions as yet!
> >
> > Wendy
> >
> >
> >
> >
> > "BBogart" wrote:
> >
> > > We are encountering this problem also. I have contacted MS but they are
> > > still gathering information.
> > >
> > > There is at least one other unanswered post on this here as well, see
> > > subject = "SQL2005 Linked server authentication drops".
> > >
> > > Are you running sql server under under a domain account that is not in the
> > > local admins group by any chance?
> > > --
> > > -b
> > >
> > >
> > > "Mattias" wrote:
> > >
> > > > I have a linked server set up between two servers (A & B).
> > > > Server A is running 2005 and Server B is running 2000.
> > > > Both servers SQL services are running using a domain user account and have
> > > > their SPN's registered in the AD.
> > > >
> > > > The client connects to Server A using integrated security (TCP/IP and
> > > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > > server B.
> > > > Delegation is set up in the AD and is working, at least for some time.
> > > >
> > > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > > the delegation seems to stop working and the client recieves the error
> > > > "Login
> > > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > > connection." The client is still connected and authenticated using TCP/IP
> > > > and
> > > > Kerberos.
> > > >
> > > > After a restart of SQL Server on server A the delegation starts working
> > > > again.
> > > >
> > > > I cannt find anything in the eventlogs on either one of the servers or the
> > > > client, and nothing in the sql server logs.
> > > >
> > > > Does anybody have any idea of what could be wrong, or give me a though on
> > > > where to start looking.
> > > >
> > > > Thanks
> > > > /Mattias
> > > >
> > > >
> > > >|||Did you have this issue resolved ?
Any hints / links /pointers/ thots will be appreciated
Thanks,
GA
"BBogart" wrote:
> I also suspect a ticket is expiring.
> I am still working with MS on this with no resolution yet.
> Once we see a failure, failures continue regardless of logging off and back
> on until sql server is restarted. A reboot is not necessary as I previously
> thought.
> We have seen it take as little as a few hours or up to a week or more for
> the failures to start again.
> --
> -b
> "JD Qixcle" wrote:
> > We have seen this in our environment too. One moment a linked server query
> > will work just fine with delegated Windows credentials, the next moment you
> > receive errors like the following:
> >
> > OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> > "Communication link failure".
> > Msg 10054, Level 16, State 1, Line 0
> > TCP Provider: An existing connection was forcibly closed by the remote host.
> > Msg 18452, Level 14, State 1, Line 0
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I am still working on a reproducable way of generating the message, but I
> > seem to have problems a lot when I initially generate a linked server within
> > SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
> > I suspect that something in the Kerberos token is expiring. After I logout
> > and login, I can usually start a new session that works (just not today).
> >
> >
> >
> >
> > "Woo" wrote:
> >
> > > I am the other unanswered posting!
> > >
> > > It fails intermittently when we run under 'sa' or a domain account that is
> > > in the local admin group.
> > >
> > > I have raised this through the Microsoft concierge service and they said
> > > there are others with the same problem but no resolutions as yet!
> > >
> > > Wendy
> > >
> > >
> > >
> > >
> > > "BBogart" wrote:
> > >
> > > > We are encountering this problem also. I have contacted MS but they are
> > > > still gathering information.
> > > >
> > > > There is at least one other unanswered post on this here as well, see
> > > > subject = "SQL2005 Linked server authentication drops".
> > > >
> > > > Are you running sql server under under a domain account that is not in the
> > > > local admins group by any chance?
> > > > --
> > > > -b
> > > >
> > > >
> > > > "Mattias" wrote:
> > > >
> > > > > I have a linked server set up between two servers (A & B).
> > > > > Server A is running 2005 and Server B is running 2000.
> > > > > Both servers SQL services are running using a domain user account and have
> > > > > their SPN's registered in the AD.
> > > > >
> > > > > The client connects to Server A using integrated security (TCP/IP and
> > > > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > > > server B.
> > > > > Delegation is set up in the AD and is working, at least for some time.
> > > > >
> > > > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > > > the delegation seems to stop working and the client recieves the error
> > > > > "Login
> > > > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > > > connection." The client is still connected and authenticated using TCP/IP
> > > > > and
> > > > > Kerberos.
> > > > >
> > > > > After a restart of SQL Server on server A the delegation starts working
> > > > > again.
> > > > >
> > > > > I cannt find anything in the eventlogs on either one of the servers or the
> > > > > client, and nothing in the sql server logs.
> > > > >
> > > > > Does anybody have any idea of what could be wrong, or give me a though on
> > > > > where to start looking.
> > > > >
> > > > > Thanks
> > > > > /Mattias
> > > > >
> > > > >
> > > > >|||--
-b
"DallasBlue" wrote:
> Did you have this issue resolved ?
> Any hints / links /pointers/ thots will be appreciated
> Thanks,
> GA
> "BBogart" wrote:
> > I also suspect a ticket is expiring.
> >
> > I am still working with MS on this with no resolution yet.
> >
> > Once we see a failure, failures continue regardless of logging off and back
> > on until sql server is restarted. A reboot is not necessary as I previously
> > thought.
> >
> > We have seen it take as little as a few hours or up to a week or more for
> > the failures to start again.
> > --
> > -b
> >
> > "JD Qixcle" wrote:
> >
> > > We have seen this in our environment too. One moment a linked server query
> > > will work just fine with delegated Windows credentials, the next moment you
> > > receive errors like the following:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> > > "Communication link failure".
> > > Msg 10054, Level 16, State 1, Line 0
> > > TCP Provider: An existing connection was forcibly closed by the remote host.
> > > Msg 18452, Level 14, State 1, Line 0
> > > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > > Server connection.
> > >
> > > I am still working on a reproducable way of generating the message, but I
> > > seem to have problems a lot when I initially generate a linked server within
> > > SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
> > > I suspect that something in the Kerberos token is expiring. After I logout
> > > and login, I can usually start a new session that works (just not today).
> > >
> > >
> > >
> > >
> > > "Woo" wrote:
> > >
> > > > I am the other unanswered posting!
> > > >
> > > > It fails intermittently when we run under 'sa' or a domain account that is
> > > > in the local admin group.
> > > >
> > > > I have raised this through the Microsoft concierge service and they said
> > > > there are others with the same problem but no resolutions as yet!
> > > >
> > > > Wendy
> > > >
> > > >
> > > >
> > > >
> > > > "BBogart" wrote:
> > > >
> > > > > We are encountering this problem also. I have contacted MS but they are
> > > > > still gathering information.
> > > > >
> > > > > There is at least one other unanswered post on this here as well, see
> > > > > subject = "SQL2005 Linked server authentication drops".
> > > > >
> > > > > Are you running sql server under under a domain account that is not in the
> > > > > local admins group by any chance?
> > > > > --
> > > > > -b
> > > > >
> > > > >
> > > > > "Mattias" wrote:
> > > > >
> > > > > > I have a linked server set up between two servers (A & B).
> > > > > > Server A is running 2005 and Server B is running 2000.
> > > > > > Both servers SQL services are running using a domain user account and have
> > > > > > their SPN's registered in the AD.
> > > > > >
> > > > > > The client connects to Server A using integrated security (TCP/IP and
> > > > > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > > > > server B.
> > > > > > Delegation is set up in the AD and is working, at least for some time.
> > > > > >
> > > > > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > > > > the delegation seems to stop working and the client recieves the error
> > > > > > "Login
> > > > > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > > > > connection." The client is still connected and authenticated using TCP/IP
> > > > > > and
> > > > > > Kerberos.
> > > > > >
> > > > > > After a restart of SQL Server on server A the delegation starts working
> > > > > > again.
> > > > > >
> > > > > > I cannt find anything in the eventlogs on either one of the servers or the
> > > > > > client, and nothing in the sql server logs.
> > > > > >
> > > > > > Does anybody have any idea of what could be wrong, or give me a though on
> > > > > > where to start looking.
> > > > > >
> > > > > > Thanks
> > > > > > /Mattias
> > > > > >
> > > > > >
> > > > > >

Delegation stops working after a while

I have a linked server set up between two servers (A & B).
Server A is running 2005 and Server B is running 2000.
Both servers SQL services are running using a domain user account and have
their SPN's registered in the AD.
The client connects to Server A using integrated security (TCP/IP and
Kerberos not NTLM) and runs disributed queries using the linked server to
server B.
Delegation is set up in the AD and is working, at least for some time.
After a while (sometimes a couple of minutes, sometimes a couple of hours)
the delegation seems to stop working and the client recieves the error
"Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection." The client is still connected and authenticated using TCP/IP
and
Kerberos.
After a restart of SQL Server on server A the delegation starts working
again.
I cannt find anything in the eventlogs on either one of the servers or the
client, and nothing in the sql server logs.
Does anybody have any idea of what could be wrong, or give me a though on
where to start looking.
Thanks
/MattiasWe are encountering this problem also. I have contacted MS but they are
still gathering information.
There is at least one other unanswered post on this here as well, see
subject = "SQL2005 Linked server authentication drops".
Are you running sql server under under a domain account that is not in the
local admins group by any chance?
--
-b
"Mattias" wrote:

> I have a linked server set up between two servers (A & B).
> Server A is running 2005 and Server B is running 2000.
> Both servers SQL services are running using a domain user account and have
> their SPN's registered in the AD.
> The client connects to Server A using integrated security (TCP/IP and
> Kerberos not NTLM) and runs disributed queries using the linked server to
> server B.
> Delegation is set up in the AD and is working, at least for some time.
> After a while (sometimes a couple of minutes, sometimes a couple of hours)
> the delegation seems to stop working and the client recieves the error
> "Login
> failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection." The client is still connected and authenticated using TCP/IP
> and
> Kerberos.
> After a restart of SQL Server on server A the delegation starts working
> again.
> I cannt find anything in the eventlogs on either one of the servers or the
> client, and nothing in the sql server logs.
> Does anybody have any idea of what could be wrong, or give me a though on
> where to start looking.
> Thanks
> /Mattias
>
>|||I am the other unanswered posting!
It fails intermittently when we run under 'sa' or a domain account that is
in the local admin group.
I have raised this through the Microsoft concierge service and they said
there are others with the same problem but no resolutions as yet!
Wendy
"BBogart" wrote:
[vbcol=seagreen]
> We are encountering this problem also. I have contacted MS but they are
> still gathering information.
> There is at least one other unanswered post on this here as well, see
> subject = "SQL2005 Linked server authentication drops".
> Are you running sql server under under a domain account that is not in the
> local admins group by any chance?
> --
> -b
>
> "Mattias" wrote:
>|||We have seen this in our environment too. One moment a linked server query
will work just fine with delegated Windows credentials, the next moment you
receive errors like the following:
OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I am still working on a reproducable way of generating the message, but I
seem to have problems a lot when I initially generate a linked server within
SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
I suspect that something in the Kerberos token is expiring. After I logout
and login, I can usually start a new session that works (just not today).
"Woo" wrote:
[vbcol=seagreen]
> I am the other unanswered posting!
> It fails intermittently when we run under 'sa' or a domain account that is
> in the local admin group.
> I have raised this through the Microsoft concierge service and they said
> there are others with the same problem but no resolutions as yet!
> Wendy
>
>
> "BBogart" wrote:
>|||I also suspect a ticket is expiring.
I am still working with MS on this with no resolution yet.
Once we see a failure, failures continue regardless of logging off and back
on until sql server is restarted. A reboot is not necessary as I previously
thought.
We have seen it take as little as a few hours or up to a week or more for
the failures to start again.
--
-b
"JD Qixcle" wrote:
[vbcol=seagreen]
> We have seen this in our environment too. One moment a linked server quer
y
> will work just fine with delegated Windows credentials, the next moment yo
u
> receive errors like the following:
> OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote hos
t.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I am still working on a reproducable way of generating the message, but I
> seem to have problems a lot when I initially generate a linked server with
in
> SQL Management Studio 2005. My connections rarely last beyond 24 hours, a
nd
> I suspect that something in the Kerberos token is expiring. After I logou
t
> and login, I can usually start a new session that works (just not today).
>
>
> "Woo" wrote:
>|||Did you have this issue resolved ?
Any hints / links /pointers/ thots will be appreciated
Thanks,
GA
"BBogart" wrote:
[vbcol=seagreen]
> I also suspect a ticket is expiring.
> I am still working with MS on this with no resolution yet.
> Once we see a failure, failures continue regardless of logging off and bac
k
> on until sql server is restarted. A reboot is not necessary as I previous
ly
> thought.
> We have seen it take as little as a few hours or up to a week or more for
> the failures to start again.
> --
> -b
> "JD Qixcle" wrote:
>|||-b
"DallasBlue" wrote:
[vbcol=seagreen]
> Did you have this issue resolved ?
> Any hints / links /pointers/ thots will be appreciated
> Thanks,
> GA
> "BBogart" wrote:
>

Delay in SQLAgent Service startup

Hi,
On a number of servers we are facing issues regarding the SQL Server Agent
service. The service is experiencing a delay during startup, more than the
wait hint period. THe service should ideally be starting in 30 seconds (Or it
seems to say so in the Wait Hint). But it is taking around 1min. Our software
thinks it is a timeout and raises an error.
Following is the SQLAgent log file of when the service is starting:
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1956
2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
8.00.760 (0 connection limit)
2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is [MACHINE_NAME]\[INSTANCE]
2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
Windows NT 5.0 (2195) Service Pack 3
2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded (maximum
concurrency: 20)
2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
(maximum concurrency: 10)
2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded (maximum
concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
loaded (maximum concurrency: 10)
2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
(maximum concurrency: 25)
2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows NT
service control
2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No mail
profile defined)
2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
threads: 0)
2006-11-08 03:31:10 - ? [146] Request servicer engine started
2006-11-08 03:31:10 - ? [167] Populating job cache...
2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2006-11-08 03:31:10 - ? [133] Support engine started
2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job cache
2006-11-08 03:31:10 - ? [170] Populating alert cache...
2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
The machine is a very high spec machine and there's a lot of cpu/memory
available.
Thanks
It looks like it is hanging on ActiveScripting which is part of DTS. Does
this help you to resolve it?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kunal" <Kunal@.discussions.microsoft.com> wrote in message
news:C42254BF-88A3-49E5-A461-27B98EF8687D@.microsoft.com...
> Hi,
> On a number of servers we are facing issues regarding the SQL Server Agent
> service. The service is experiencing a delay during startup, more than the
> wait hint period. THe service should ideally be starting in 30 seconds (Or
> it
> seems to say so in the Wait Hint). But it is taking around 1min. Our
> software
> thinks it is a timeout and raises an error.
> Following is the SQLAgent log file of when the service is starting:
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID 1956
> 2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
> 8.00.760 (0 connection limit)
> 2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
> 2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
> Local host server is [MACHINE_NAME]\[INSTANCE]
> 2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
> 2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
> Windows NT 5.0 (2195) Service Pack 3
> 2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded
> (maximum
> concurrency: 20)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
> (maximum concurrency: 10)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded
> (maximum
> concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
> loaded (maximum concurrency: 10)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
> (maximum concurrency: 25)
> 2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows
> NT
> service control
> 2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No
> mail
> profile defined)
> 2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
> threads: 0)
> 2006-11-08 03:31:10 - ? [146] Request servicer engine started
> 2006-11-08 03:31:10 - ? [167] Populating job cache...
> 2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
> OnIdle job schedules will have no effect
> 2006-11-08 03:31:10 - ? [133] Support engine started
> 2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
> 2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job
> cache
> 2006-11-08 03:31:10 - ? [170] Populating alert cache...
> 2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
>
> The machine is a very high spec machine and there's a lot of cpu/memory
> available.
> Thanks
sql

Delay in SQLAgent Service startup

Hi,
On a number of servers we are facing issues regarding the SQL Server Agent
service. The service is experiencing a delay during startup, more than the
wait hint period. THe service should ideally be starting in 30 seconds (Or i
t
seems to say so in the Wait Hint). But it is taking around 1min. Our softwar
e
thinks it is a timeout and raises an error.
Following is the SQLAgent log file of when the service is starting:
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
(x86
unicode retail build) : Process ID
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
(x86
unicode retail build) : Process ID 1956
2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANC
E] version
8.00.760 (0 connection limit)
2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DL
L;
Local host server is [MACHINE_NAME]\[INSTANCE]
2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] runni
ng
Windows NT 5.0 (2195) Service Pack 3
2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded (maxi
mum
concurrency: 20)
2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
(maximum concurrency: 10)
2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully load
ed
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded (max
imum
concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
loaded (maximum concurrency: 10)
2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loade
d
(maximum concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
(maximum concurrency: 25)
2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Win
dows NT
service control
2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No m
ail
profile defined)
2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum work
er
threads: 0)
2006-11-08 03:31:10 - ? [146] Request servicer engine started
2006-11-08 03:31:10 - ? [167] Populating job cache...
2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined
-
OnIdle job schedules will have no effect
2006-11-08 03:31:10 - ? [133] Support engine started
2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Event
s)
2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the
job cache
2006-11-08 03:31:10 - ? [170] Populating alert cache...
2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
---
The machine is a very high spec machine and there's a lot of cpu/memory
available.
ThanksIt looks like it is hanging on ActiveScripting which is part of DTS. Does
this help you to resolve it?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kunal" <Kunal@.discussions.microsoft.com> wrote in message
news:C42254BF-88A3-49E5-A461-27B98EF8687D@.microsoft.com...
> Hi,
> On a number of servers we are facing issues regarding the SQL Server Agent
> service. The service is experiencing a delay during startup, more than the
> wait hint period. THe service should ideally be starting in 30 seconds (Or
> it
> seems to say so in the Wait Hint). But it is taking around 1min. Our
> software
> thinks it is a timeout and raises an error.
> Following is the SQLAgent log file of when the service is starting:
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.76
0
> (x86
> unicode retail build) : Process ID
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.76
0
> (x86
> unicode retail build) : Process ID 1956
> 2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTA
NCE] version
> 8.00.760 (0 connection limit)
> 2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
> 2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.
DLL;
> Local host server is [MACHINE_NAME]\[INSTANCE]
> 2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
> 2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] run
ning
> Windows NT 5.0 (2195) Service Pack 3
> 2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded
> (maximum
> concurrency: 20)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
> (maximum concurrency: 10)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully lo
aded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded
> (maximum
> concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
> loaded (maximum concurrency: 10)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loa
ded
> (maximum concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loade
d
> (maximum concurrency: 25)
> 2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under W
indows
> NT
> service control
> 2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No
> mail
> profile defined)
> 2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum wo
rker
> threads: 0)
> 2006-11-08 03:31:10 - ? [146] Request servicer engine started
> 2006-11-08 03:31:10 - ? [167] Populating job cache...
> 2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defin
ed -
> OnIdle job schedules will have no effect
> 2006-11-08 03:31:10 - ? [133] Support engine started
> 2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Eve
nts)
> 2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in t
he job
> cache
> 2006-11-08 03:31:10 - ? [170] Populating alert cache...
> 2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
> ---
> The machine is a very high spec machine and there's a lot of cpu/memory
> available.
> Thanks

Delay in SQLAgent Service startup

Hi,
On a number of servers we are facing issues regarding the SQL Server Agent
service. The service is experiencing a delay during startup, more than the
wait hint period. THe service should ideally be starting in 30 seconds (Or it
seems to say so in the Wait Hint). But it is taking around 1min. Our software
thinks it is a timeout and raises an error.
Following is the SQLAgent log file of when the service is starting:
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1956
2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
8.00.760 (0 connection limit)
2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is [MACHINE_NAME]\[INSTANCE]
2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
Windows NT 5.0 (2195) Service Pack 3
2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded (maximum
concurrency: 20)
2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
(maximum concurrency: 10)
2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded (maximum
concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
loaded (maximum concurrency: 10)
2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
(maximum concurrency: 25)
2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows NT
service control
2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No mail
profile defined)
2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
threads: 0)
2006-11-08 03:31:10 - ? [146] Request servicer engine started
2006-11-08 03:31:10 - ? [167] Populating job cache...
2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2006-11-08 03:31:10 - ? [133] Support engine started
2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job cache
2006-11-08 03:31:10 - ? [170] Populating alert cache...
2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
---
The machine is a very high spec machine and there's a lot of cpu/memory
available.
ThanksIt looks like it is hanging on ActiveScripting which is part of DTS. Does
this help you to resolve it?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kunal" <Kunal@.discussions.microsoft.com> wrote in message
news:C42254BF-88A3-49E5-A461-27B98EF8687D@.microsoft.com...
> Hi,
> On a number of servers we are facing issues regarding the SQL Server Agent
> service. The service is experiencing a delay during startup, more than the
> wait hint period. THe service should ideally be starting in 30 seconds (Or
> it
> seems to say so in the Wait Hint). But it is taking around 1min. Our
> software
> thinks it is a timeout and raises an error.
> Following is the SQLAgent log file of when the service is starting:
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID 1956
> 2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
> 8.00.760 (0 connection limit)
> 2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
> 2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
> Local host server is [MACHINE_NAME]\[INSTANCE]
> 2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
> 2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
> Windows NT 5.0 (2195) Service Pack 3
> 2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded
> (maximum
> concurrency: 20)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
> (maximum concurrency: 10)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded
> (maximum
> concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
> loaded (maximum concurrency: 10)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
> (maximum concurrency: 25)
> 2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows
> NT
> service control
> 2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No
> mail
> profile defined)
> 2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
> threads: 0)
> 2006-11-08 03:31:10 - ? [146] Request servicer engine started
> 2006-11-08 03:31:10 - ? [167] Populating job cache...
> 2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
> OnIdle job schedules will have no effect
> 2006-11-08 03:31:10 - ? [133] Support engine started
> 2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
> 2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job
> cache
> 2006-11-08 03:31:10 - ? [170] Populating alert cache...
> 2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
> ---
> The machine is a very high spec machine and there's a lot of cpu/memory
> available.
> Thanks

Defragment tables that have no clustered index

Hi!
We have several SQL Servers in a system that replicates
information in non-realtime between them using MSMQ and
Biztalk and to guarantee uniqueness it uses GUID:s as
primary and foreign keys. When planning this solution we
were recommended by Microsoft to use only non-clustered
indexes on these tables.
Since there are a lot of inserts and updates to the data
in this system we have now got a lot of really
fragmentated tables but since we have no clustered indexes
DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
solve this problem?
I have searched the newsgroups (and of course "Inside sQL
Server 2000", Hi Kalen! Any suggestions..?) but found no
answers that works. After reading a post here I tried both
DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
hardly effects the terrible scan density (DBCC SHOWCONTIG)
for these tables. The only other solution I have read
about is to use BCP to export and import the tables but
this quite complicated solution can't really be included
in our weekly maintenance job which is what we want.
Please, anyone, suggestions? I can't be the only one with
this problem..?
- AllanRead in "BOL - DBCC DBREINDEX" after reindexing, update
your statistics manually after the reindex.
Greg
>--Original Message--
>Hi!
>We have several SQL Servers in a system that replicates
>information in non-realtime between them using MSMQ and
>Biztalk and to guarantee uniqueness it uses GUID:s as
>primary and foreign keys. When planning this solution we
>were recommended by Microsoft to use only non-clustered
>indexes on these tables.
>Since there are a lot of inserts and updates to the data
>in this system we have now got a lot of really
>fragmentated tables but since we have no clustered
indexes
>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
>solve this problem?
>I have searched the newsgroups (and of course "Inside sQL
>Server 2000", Hi Kalen! Any suggestions..?) but found no
>answers that works. After reading a post here I tried
both
>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
>hardly effects the terrible scan density (DBCC
SHOWCONTIG)
>for these tables. The only other solution I have read
>about is to use BCP to export and import the tables but
>this quite complicated solution can't really be included
>in our weekly maintenance job which is what we want.
>Please, anyone, suggestions? I can't be the only one with
>this problem..?
>- Allan
>.
>|||There's no easy way to reorg (or perhaps "compact" is a better word as the data isn't sorted in any
way) for a heap. Two ways I can think of:
Create a clustered index and drop it.
Export/import of the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
news:03d101c38386$33166d70$a301280a@.phx.gbl...
> As far as I have understood (and tested) neither DBCC
> DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> have clustered indexes on these tables the data is not
> stored on the leaf level of the index but in a heap. What
> I want to know is how to defragment this heap..
> >--Original Message--
> >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> >your statistics manually after the reindex.
> >
> >Greg
> >
> >>--Original Message--
> >>Hi!
> >>
> >>We have several SQL Servers in a system that replicates
> >>information in non-realtime between them using MSMQ and
> >>Biztalk and to guarantee uniqueness it uses GUID:s as
> >>primary and foreign keys. When planning this solution we
> >>were recommended by Microsoft to use only non-clustered
> >>indexes on these tables.
> >>
> >>Since there are a lot of inserts and updates to the data
> >>in this system we have now got a lot of really
> >>fragmentated tables but since we have no clustered
> >indexes
> >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> to
> >>solve this problem?
> >>
> >>I have searched the newsgroups (and of course "Inside
> sQL
> >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> >>answers that works. After reading a post here I tried
> >both
> >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> it
> >>hardly effects the terrible scan density (DBCC
> >SHOWCONTIG)
> >>for these tables. The only other solution I have read
> >>about is to use BCP to export and import the tables but
> >>this quite complicated solution can't really be included
> >>in our weekly maintenance job which is what we want.
> >>
> >>Please, anyone, suggestions? I can't be the only one
> with
> >>this problem..?
> >>
> >>- Allan
> >>
> >>.
> >>
> >.
> >|||Yes Tibor has it correct. That's one of the reasons I suggest that most
tables have a clustered index.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23W979hBhDHA.616@.TK2MSFTNGP11.phx.gbl...
> There's no easy way to reorg (or perhaps "compact" is a better word as the
data isn't sorted in any
> way) for a heap. Two ways I can think of:
> Create a clustered index and drop it.
> Export/import of the data.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
> news:03d101c38386$33166d70$a301280a@.phx.gbl...
> > As far as I have understood (and tested) neither DBCC
> > DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> > have clustered indexes on these tables the data is not
> > stored on the leaf level of the index but in a heap. What
> > I want to know is how to defragment this heap..
> >
> > >--Original Message--
> > >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> > >your statistics manually after the reindex.
> > >
> > >Greg
> > >
> > >>--Original Message--
> > >>Hi!
> > >>
> > >>We have several SQL Servers in a system that replicates
> > >>information in non-realtime between them using MSMQ and
> > >>Biztalk and to guarantee uniqueness it uses GUID:s as
> > >>primary and foreign keys. When planning this solution we
> > >>were recommended by Microsoft to use only non-clustered
> > >>indexes on these tables.
> > >>
> > >>Since there are a lot of inserts and updates to the data
> > >>in this system we have now got a lot of really
> > >>fragmentated tables but since we have no clustered
> > >indexes
> > >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> > to
> > >>solve this problem?
> > >>
> > >>I have searched the newsgroups (and of course "Inside
> > sQL
> > >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> > >>answers that works. After reading a post here I tried
> > >both
> > >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> > it
> > >>hardly effects the terrible scan density (DBCC
> > >SHOWCONTIG)
> > >>for these tables. The only other solution I have read
> > >>about is to use BCP to export and import the tables but
> > >>this quite complicated solution can't really be included
> > >>in our weekly maintenance job which is what we want.
> > >>
> > >>Please, anyone, suggestions? I can't be the only one
> > with
> > >>this problem..?
> > >>
> > >>- Allan
> > >>
> > >>.
> > >>
> > >.
> > >
>

Defrag on SAN disk ?

I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.
Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>
|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:[vbcol=seagreen]
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
a
>
|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:[vbcol=seagreen]
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
>
|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.

> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric

Defrag on SAN disk ?

I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds like a
>> good
>> idea.|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
--
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
> > Jim,
> >
> > Interesting question. Who is the SAN vendor? SANs store data
> > differently to "normal" file systems. Blocks do not get overwritten
> > (usually), but a new block gets written when data changes. So, I'm
> > not entirely sure what would happen if you ran a disk defrag tool on
> > a SAN volume.
> > I would first of all make sure that you don't have any SQL Server
> > fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> > talking to your infrastructure team about this, failing that speak to
> > the SAN vendor.
> >
> > I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >
> >
> > Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds like
a
> >> good
> >> idea.
>|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
>> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
>> necessary on our CX series.
>>
>> Mark Allison wrote:
>> Jim,
>> Interesting question. Who is the SAN vendor? SANs store data
>> differently to "normal" file systems. Blocks do not get overwritten
>> (usually), but a new block gets written when data changes. So, I'm
>> not entirely sure what would happen if you ran a disk defrag tool on
>> a SAN volume.
>> I would first of all make sure that you don't have any SQL Server
>> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
>> consider talking to your infrastructure team about this, failing
>> that speak to the SAN vendor.
>> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>>
>> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds
>> like a good
>> idea.|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
> > Tell your engineer he is full of sxxx<g>. While a large amount of
> > cache may abstract some aspects of data being read and written to
> > disk there is always the fact fragmentation can lead to pages that
> > are not as full as you would like. If the page is half empty on disk
> > it will be half empty when read into the sql server data cache as
> > well. This means you can only have half the amount of data or
> > indexes in cache at any one time. It also means lots more I/O's
> > (even if they are logical) and that means lots more cpu ect.
> >
> >
> > "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> > news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> >> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
> >> necessary on our CX series.
> >>
> >>
> >> Mark Allison wrote:
> >> Jim,
> >>
> >> Interesting question. Who is the SAN vendor? SANs store data
> >> differently to "normal" file systems. Blocks do not get overwritten
> >> (usually), but a new block gets written when data changes. So, I'm
> >> not entirely sure what would happen if you ran a disk defrag tool on
> >> a SAN volume.
> >> I would first of all make sure that you don't have any SQL Server
> >> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
> >> consider talking to your infrastructure team about this, failing
> >> that speak to the SAN vendor.
> >>
> >> I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >>
> >>
> >> Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds
> >> like a good
> >> idea.
>|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric

Defrag for SQL Servers inside a Virtual Machine?

sorry for asking here, since there's no forum for Virtual Server

My question is

We have 2 SQL Server instances on 2 Virtual Machine/Servers (say VM1 and VM2), which resides on a real physical machine (say A)

Is there point in defragging VM1 or VM2's hard drive at all?

or should I just defrag on the physical machine A?

You want to make sure the VMs are down (stopped) then defrag the physical machine. That would be all you need.|||

that's what I thought anyway, first time I defragged without shutting VMs down, I got a blue screen on 1 VM

second time I defragged still without shutting VMs down, everything went smoothly

I guess it's safer to shut down the VMs anyway

Thanks for the answer

Default Stored Procedures

After a recent audit on our SQL servers, it was
recommended that we remove the following default
procedures where possible. I have not seen any security
checklists on removing these procedures. Should I remove
these if they are not needed? How are they removed? Thanks
for your help.
Xp_CmdShell
Sp_OACreate
Sp_OADestroy
Sp_OAGetErrorInfo
Sp_OAGetProperty
Sp_OAMethod
Sp_OASetProperty
Sp_OAStop
Xp_regaddmultistring
Xp_regdeletekey
Xp_regdeletevalue
Xp_regenumvalues
Xp_regread
Xp_regremovemultistring
Xp_regwriteMost of the extended stored procedures you listed above are considered dange
rous and could leave your installation and server prone to attack. You can
drop” extended stored procedures with the sp_DropExtendedProc system sto
red procedure and then actu
ally deleting the dll from the server.
Try reading SQL Server Security by Chip Andrews, David Litchfield, and bill
Grindlay (Osborne Press) Chapter 9 and Appendix A
Randy Dyess
www.Database-Security.Info|||There is no official Microsoft guidance on the removal of these stored
procedures. If you decide to remove these stored procedures, then you
should verify in a test environment prior to attempting this on a
production machine.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

default replication mechanism

Hi,
I did'n want to use default replication mechanism when I initially set up
transactional replication between 2 servers (sql server 2000 sp3). However,
now I need to change replication to use default mechanism for some operations
(inserts/updates/deletes) of existing articles.
1. Is there any way to do that without dropping whole publication (for the
articles which didn't replicate updates to the other side)?
2. Also, is there any way to generate sp_MSins... stored procedures without
synchronizing data between publisher and subsciber (option "created stored
procedures during initial synchronization of subscriptions")? I have 2 large
databases, and initializing subscriptions (synchronizing data) just to
generate these stored procedures wouldn't make lot of sense.
Thanks
Pedja,
here's the answer to the second question:
http://msdn2.microsoft.com/en-us/library/ms187946.aspx
For the first question, I'm not quite sure what you're asking. Do you want
to add an article to an existing publication? If so, you can do this from
the GUI, run the snapshot agent and synchronize. This will just add the new
article and not reinitialize the others.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the answer.
For the first question, since I replicated inserts, but not delets and
updates in the old setup, these two fields are grayed (unavailable). What I
am asking is is there a way to enable them (check them to use generated
stored procedures), without droping and recreating publication (unchecking
and checking just that article doesn't work)?
"Paul Ibison" wrote:

> Pedja,
> here's the answer to the second question:
> http://msdn2.microsoft.com/en-us/library/ms187946.aspx
> For the first question, I'm not quite sure what you're asking. Do you want
> to add an article to an existing publication? If so, you can do this from
> the GUI, run the snapshot agent and synchronize. This will just add the new
> article and not reinitialize the others.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Pedja,
not as far as I know. I think your best bet would be to do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)