Hello.
We are using a stand alone Windows 2000 server with only SQL server 2000
running on it.
Now, these server is alone with his proper domain name.
This windows 2000 server will join a new domain as a member.
So we will change his domain name.
We are only using TCPIP connection beetween clients apps and SQL server.
Is there any problem for the client apps, or SQL server himself.
Thanks in advance for your answers.
Jean Jacques SerpoulI am currently in the same process. If anyone has done this or knows of docs please let us know
Thankyou
Shawn
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Friday, March 30, 2012
modifing a running trace
Is there a way to modify a running trace without stopping and re-createing it?
I have a trace running for SOX Audting and I need to add a new user to be
traced.
I created the trace in a script not the GUI in EM.
When I created it I used the following code to ignore this user and now I
need to add him to the trace:
exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
I have the trace writing out to files, and I am up to approx #1000. If I
stop the trace I will need to move the file out of the directory, and
re-start at #1. I am hoping to avoid renumbering.
TIA,
Joe
jaylou wrote:
> Is there a way to modify a running trace without stopping and
> re-createing it?
> I have a trace running for SOX Audting and I need to add a new user
> to be traced.
> I created the trace in a script not the GUI in EM.
> When I created it I used the following code to ignore this user and
> now I need to add him to the trace:
> exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
> I have the trace writing out to files, and I am up to approx #1000.
> If I stop the trace I will need to move the file out of the
> directory, and re-start at #1. I am hoping to avoid renumbering.
> TIA,
> Joe
Why not use an auto-numbering scheme for the trace file names or just
add the current date/time to the suffix of the trace file name. You need
to stop the trace in order to change its filter criteria.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks,
That was what I was afraid of
Joe
"David Gugick" wrote:
> jaylou wrote:
> Why not use an auto-numbering scheme for the trace file names or just
> add the current date/time to the suffix of the trace file name. You need
> to stop the trace in order to change its filter criteria.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Hi Jaylou
I have a system that takes care of this & various other SQL Profiler
management issues. It's soon to be released to market but is available for
testing now if you're interested at www.gajsoftware.com (to get the eval,
you simply sig up, activate your account & visit the "editions" page).
Basically it provides for multi-server, schedulable, template based profiler
management. If you change the profiler template (via an easy to use gui),
the background trace management service will automatically re-configure the
trace for you & take care of the file rollover issues.
Regards,
Greg Linwood
SQL Server MVP
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:724A9BBE-FCFC-4401-B42A-91CFF5EA5CD5@.microsoft.com...
> Is there a way to modify a running trace without stopping and re-createing
> it?
> I have a trace running for SOX Audting and I need to add a new user to be
> traced.
> I created the trace in a script not the GUI in EM.
> When I created it I used the following code to ignore this user and now I
> need to add him to the trace:
> exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
> I have the trace writing out to files, and I am up to approx #1000. If I
> stop the trace I will need to move the file out of the directory, and
> re-start at #1. I am hoping to avoid renumbering.
> TIA,
> Joe
I have a trace running for SOX Audting and I need to add a new user to be
traced.
I created the trace in a script not the GUI in EM.
When I created it I used the following code to ignore this user and now I
need to add him to the trace:
exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
I have the trace writing out to files, and I am up to approx #1000. If I
stop the trace I will need to move the file out of the directory, and
re-start at #1. I am hoping to avoid renumbering.
TIA,
Joe
jaylou wrote:
> Is there a way to modify a running trace without stopping and
> re-createing it?
> I have a trace running for SOX Audting and I need to add a new user
> to be traced.
> I created the trace in a script not the GUI in EM.
> When I created it I used the following code to ignore this user and
> now I need to add him to the trace:
> exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
> I have the trace writing out to files, and I am up to approx #1000.
> If I stop the trace I will need to move the file out of the
> directory, and re-start at #1. I am hoping to avoid renumbering.
> TIA,
> Joe
Why not use an auto-numbering scheme for the trace file names or just
add the current date/time to the suffix of the trace file name. You need
to stop the trace in order to change its filter criteria.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks,
That was what I was afraid of
Joe
"David Gugick" wrote:
> jaylou wrote:
> Why not use an auto-numbering scheme for the trace file names or just
> add the current date/time to the suffix of the trace file name. You need
> to stop the trace in order to change its filter criteria.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Hi Jaylou
I have a system that takes care of this & various other SQL Profiler
management issues. It's soon to be released to market but is available for
testing now if you're interested at www.gajsoftware.com (to get the eval,
you simply sig up, activate your account & visit the "editions" page).
Basically it provides for multi-server, schedulable, template based profiler
management. If you change the profiler template (via an easy to use gui),
the background trace management service will automatically re-configure the
trace for you & take care of the file rollover issues.
Regards,
Greg Linwood
SQL Server MVP
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:724A9BBE-FCFC-4401-B42A-91CFF5EA5CD5@.microsoft.com...
> Is there a way to modify a running trace without stopping and re-createing
> it?
> I have a trace running for SOX Audting and I need to add a new user to be
> traced.
> I created the trace in a script not the GUI in EM.
> When I created it I used the following code to ignore this user and now I
> need to add him to the trace:
> exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
> I have the trace writing out to files, and I am up to approx #1000. If I
> stop the trace I will need to move the file out of the directory, and
> re-start at #1. I am hoping to avoid renumbering.
> TIA,
> Joe
Monday, March 26, 2012
model database "lost"
When moving the sysdatabases to a different drive using specific Q224071 ()
instructions i found that the model db does not show up and when running:
use model
go
sp helpfile
go
i get:
Could not locate entry in sysdatabases for database model. No entry found
with that name. Make sure that the name is entered correctly.
any ideas?Did you enable trace flag 3608 and reattach model database before msdb?
The proper steps are:
1. moving master
2. moving model
3. moving msdb
4. moving tempdb
-oj
"Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
message news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
> When moving the sysdatabases to a different drive using specific Q224071
> ()
> instructions i found that the model db does not show up and when running:
> use model
> go
> sp helpfile
> go
> i get:
> Could not locate entry in sysdatabases for database model. No entry found
> with that name. Make sure that the name is entered correctly.
> any ideas?
>|||Hi,
Was sp_attach_db statement successfull? And did you try to remove -T3608
flag from the startup parameter?
If you have done the above, try to re-start the server and see what the
error log has to say.
--
Thanks
Yogish|||In addition tot he other posts, check the dbid for model (in sysdatabases).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in message
news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
> When moving the sysdatabases to a different drive using specific Q224071 ()
> instructions i found that the model db does not show up and when running:
> use model
> go
> sp helpfile
> go
> i get:
> Could not locate entry in sysdatabases for database model. No entry found
> with that name. Make sure that the name is entered correctly.
> any ideas?
>|||flag 3608 yes, it was enabled.
About the steps you mentioned, i followed Q224071 and it has the order like
these:
1. moving msdb
2. moving master
3. moving model
4. moving tempdb
Could you confirm where you got your order?
"oj" wrote:
> Did you enable trace flag 3608 and reattach model database before msdb?
> The proper steps are:
> 1. moving master
> 2. moving model
> 3. moving msdb
> 4. moving tempdb
>
> --
> -oj
>
> "Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
> message news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
> > When moving the sysdatabases to a different drive using specific Q224071
> > ()
> > instructions i found that the model db does not show up and when running:
> > use model
> > go
> > sp helpfile
> > go
> >
> > i get:
> > Could not locate entry in sysdatabases for database model. No entry found
> > with that name. Make sure that the name is entered correctly.
> >
> > any ideas?
> >
>
>|||No attach wasnt successful. End up with error:
Could not locate entry in sysdatabases for database model. No entry found
with that name. Make sure that the name is entered correctly.
"Yogish" wrote:
> Hi,
> Was sp_attach_db statement successfull? And did you try to remove -T3608
> flag from the startup parameter?
> If you have done the above, try to re-start the server and see what the
> error log has to say.
> --
> Thanks
> Yogish|||Make sure you attached them in the right order, I had a similar issue where
msdb became model (as well as msdb) because of a typo when attaching the
model database, led to some odd behaviour :-)
Life without model
http://www.sqlteam.com/item.asp?ItemID=10060
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
message news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
> When moving the sysdatabases to a different drive using specific Q224071
> ()
> instructions i found that the model db does not show up and when running:
> use model
> go
> sp helpfile
> go
> i get:
> Could not locate entry in sysdatabases for database model. No entry found
> with that name. Make sure that the name is entered correctly.
> any ideas?
>|||It's clearly stated that model must be attached before msdb in kb 224071.
The actually order for master/tempdb is not that important when you're
moving system databases.
Moving the MSDB database (SQL Server 2000)
Note If you are using this procedure in conjunction with moving the msdb and
model databases, the order of reattachment must be model first and then
msdb. If msdb is reattached first, it must be detached and not reattached
until after model has been attached.
--
-oj
"Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
message news:C3F4F8D7-135E-4113-9423-92566B6BE454@.microsoft.com...
> flag 3608 yes, it was enabled.
> About the steps you mentioned, i followed Q224071 and it has the order
> like
> these:
> 1. moving msdb
> 2. moving master
> 3. moving model
> 4. moving tempdb
> Could you confirm where you got your order?
> "oj" wrote:
>> Did you enable trace flag 3608 and reattach model database before msdb?
>> The proper steps are:
>> 1. moving master
>> 2. moving model
>> 3. moving msdb
>> 4. moving tempdb
>>
>> --
>> -oj
>>
>> "Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
>> message news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
>> > When moving the sysdatabases to a different drive using specific
>> > Q224071
>> > ()
>> > instructions i found that the model db does not show up and when
>> > running:
>> > use model
>> > go
>> > sp helpfile
>> > go
>> >
>> > i get:
>> > Could not locate entry in sysdatabases for database model. No entry
>> > found
>> > with that name. Make sure that the name is entered correctly.
>> >
>> > any ideas?
>> >
>>
instructions i found that the model db does not show up and when running:
use model
go
sp helpfile
go
i get:
Could not locate entry in sysdatabases for database model. No entry found
with that name. Make sure that the name is entered correctly.
any ideas?Did you enable trace flag 3608 and reattach model database before msdb?
The proper steps are:
1. moving master
2. moving model
3. moving msdb
4. moving tempdb
-oj
"Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
message news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
> When moving the sysdatabases to a different drive using specific Q224071
> ()
> instructions i found that the model db does not show up and when running:
> use model
> go
> sp helpfile
> go
> i get:
> Could not locate entry in sysdatabases for database model. No entry found
> with that name. Make sure that the name is entered correctly.
> any ideas?
>|||Hi,
Was sp_attach_db statement successfull? And did you try to remove -T3608
flag from the startup parameter?
If you have done the above, try to re-start the server and see what the
error log has to say.
--
Thanks
Yogish|||In addition tot he other posts, check the dbid for model (in sysdatabases).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in message
news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
> When moving the sysdatabases to a different drive using specific Q224071 ()
> instructions i found that the model db does not show up and when running:
> use model
> go
> sp helpfile
> go
> i get:
> Could not locate entry in sysdatabases for database model. No entry found
> with that name. Make sure that the name is entered correctly.
> any ideas?
>|||flag 3608 yes, it was enabled.
About the steps you mentioned, i followed Q224071 and it has the order like
these:
1. moving msdb
2. moving master
3. moving model
4. moving tempdb
Could you confirm where you got your order?
"oj" wrote:
> Did you enable trace flag 3608 and reattach model database before msdb?
> The proper steps are:
> 1. moving master
> 2. moving model
> 3. moving msdb
> 4. moving tempdb
>
> --
> -oj
>
> "Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
> message news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
> > When moving the sysdatabases to a different drive using specific Q224071
> > ()
> > instructions i found that the model db does not show up and when running:
> > use model
> > go
> > sp helpfile
> > go
> >
> > i get:
> > Could not locate entry in sysdatabases for database model. No entry found
> > with that name. Make sure that the name is entered correctly.
> >
> > any ideas?
> >
>
>|||No attach wasnt successful. End up with error:
Could not locate entry in sysdatabases for database model. No entry found
with that name. Make sure that the name is entered correctly.
"Yogish" wrote:
> Hi,
> Was sp_attach_db statement successfull? And did you try to remove -T3608
> flag from the startup parameter?
> If you have done the above, try to re-start the server and see what the
> error log has to say.
> --
> Thanks
> Yogish|||Make sure you attached them in the right order, I had a similar issue where
msdb became model (as well as msdb) because of a typo when attaching the
model database, led to some odd behaviour :-)
Life without model
http://www.sqlteam.com/item.asp?ItemID=10060
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
message news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
> When moving the sysdatabases to a different drive using specific Q224071
> ()
> instructions i found that the model db does not show up and when running:
> use model
> go
> sp helpfile
> go
> i get:
> Could not locate entry in sysdatabases for database model. No entry found
> with that name. Make sure that the name is entered correctly.
> any ideas?
>|||It's clearly stated that model must be attached before msdb in kb 224071.
The actually order for master/tempdb is not that important when you're
moving system databases.
Moving the MSDB database (SQL Server 2000)
Note If you are using this procedure in conjunction with moving the msdb and
model databases, the order of reattachment must be model first and then
msdb. If msdb is reattached first, it must be detached and not reattached
until after model has been attached.
--
-oj
"Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
message news:C3F4F8D7-135E-4113-9423-92566B6BE454@.microsoft.com...
> flag 3608 yes, it was enabled.
> About the steps you mentioned, i followed Q224071 and it has the order
> like
> these:
> 1. moving msdb
> 2. moving master
> 3. moving model
> 4. moving tempdb
> Could you confirm where you got your order?
> "oj" wrote:
>> Did you enable trace flag 3608 and reattach model database before msdb?
>> The proper steps are:
>> 1. moving master
>> 2. moving model
>> 3. moving msdb
>> 4. moving tempdb
>>
>> --
>> -oj
>>
>> "Christian Castro" <ChristianCastro@.discussions.microsoft.com> wrote in
>> message news:59F5AFE2-A996-46BF-8E53-B4D19480836B@.microsoft.com...
>> > When moving the sysdatabases to a different drive using specific
>> > Q224071
>> > ()
>> > instructions i found that the model db does not show up and when
>> > running:
>> > use model
>> > go
>> > sp helpfile
>> > go
>> >
>> > i get:
>> > Could not locate entry in sysdatabases for database model. No entry
>> > found
>> > with that name. Make sure that the name is entered correctly.
>> >
>> > any ideas?
>> >
>>
Friday, March 23, 2012
mmc.exe-Entry point not found in Enterprise Manager
I recently installed SQL 2000 on my machine and it was running fine till I i
nstalled SQL7 on the same machine. Now the SQL7 is running okay but I can't
use SQL2000 anymore. I received the following while try to open Enterprise
Manager:
mmc-entry point not found
The procedure entry point ? loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PB
G@.Z could
not be located in the dynamic link library SEMCOMN.dll
Can anyone provide me with some advise? Thanks in advance.Ling,
I agree with Tom.Though not officially stated(or is it?), its a good
practice to install SQL7 first and then proceed with the SQL2000 one.In your
case, instead of trying to play around with (un)registering the dlls, go
ahead and uninstall SQL7.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Ling" <anonymous@.discussions.microsoft.com> wrote in message
news:CDFD0161-D3A2-4717-88BD-52ADF1C56322@.microsoft.com...
> I recently installed SQL 2000 on my machine and it was running fine till I
installed SQL7 on the same machine. Now the SQL7 is running okay but I
can't use SQL2000 anymore. I received the following while try to open
Enterprise Manager:
> mmc-entry point not found
> The procedure entry point ? loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PB
G@.Z
could not be located in the dynamic link library SEMCOMN.dll
> Can anyone provide me with some advise? Thanks in advance.|||Thank you guys. I will give it a try.
nstalled SQL7 on the same machine. Now the SQL7 is running okay but I can't
use SQL2000 anymore. I received the following while try to open Enterprise
Manager:
mmc-entry point not found
The procedure entry point ? loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PB
G@.Z could
not be located in the dynamic link library SEMCOMN.dll
Can anyone provide me with some advise? Thanks in advance.Ling,
I agree with Tom.Though not officially stated(or is it?), its a good
practice to install SQL7 first and then proceed with the SQL2000 one.In your
case, instead of trying to play around with (un)registering the dlls, go
ahead and uninstall SQL7.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Ling" <anonymous@.discussions.microsoft.com> wrote in message
news:CDFD0161-D3A2-4717-88BD-52ADF1C56322@.microsoft.com...
> I recently installed SQL 2000 on my machine and it was running fine till I
installed SQL7 on the same machine. Now the SQL7 is running okay but I
can't use SQL2000 anymore. I received the following while try to open
Enterprise Manager:
> mmc-entry point not found
> The procedure entry point ? loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PB
G@.Z
could not be located in the dynamic link library SEMCOMN.dll
> Can anyone provide me with some advise? Thanks in advance.|||Thank you guys. I will give it a try.
mmc.exe-Entry point not found in Enterprise Manager
I recently installed SQL 2000 on my machine and it was running fine till I installed SQL7 on the same machine. Now the SQL7 is running okay but I can't use SQL2000 anymore. I received the following while try to open Enterprise Manager:
mmc-entry point not found
The procedure entry point ?loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z could not be located in the dynamic link library SEMCOMN.dll
Can anyone provide me with some advise? Thanks in advance.
Ling,
I agree with Tom.Though not officially stated(or is it?), its a good
practice to install SQL7 first and then proceed with the SQL2000 one.In your
case, instead of trying to play around with (un)registering the dlls, go
ahead and uninstall SQL7.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Ling" <anonymous@.discussions.microsoft.com> wrote in message
news:CDFD0161-D3A2-4717-88BD-52ADF1C56322@.microsoft.com...
> I recently installed SQL 2000 on my machine and it was running fine till I
installed SQL7 on the same machine. Now the SQL7 is running okay but I
can't use SQL2000 anymore. I received the following while try to open
Enterprise Manager:
> mmc-entry point not found
> The procedure entry point ?loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z
could not be located in the dynamic link library SEMCOMN.dll
> Can anyone provide me with some advise? Thanks in advance.
|||Thank you guys. I will give it a try.
mmc-entry point not found
The procedure entry point ?loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z could not be located in the dynamic link library SEMCOMN.dll
Can anyone provide me with some advise? Thanks in advance.
Ling,
I agree with Tom.Though not officially stated(or is it?), its a good
practice to install SQL7 first and then proceed with the SQL2000 one.In your
case, instead of trying to play around with (un)registering the dlls, go
ahead and uninstall SQL7.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Ling" <anonymous@.discussions.microsoft.com> wrote in message
news:CDFD0161-D3A2-4717-88BD-52ADF1C56322@.microsoft.com...
> I recently installed SQL 2000 on my machine and it was running fine till I
installed SQL7 on the same machine. Now the SQL7 is running okay but I
can't use SQL2000 anymore. I received the following while try to open
Enterprise Manager:
> mmc-entry point not found
> The procedure entry point ?loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z
could not be located in the dynamic link library SEMCOMN.dll
> Can anyone provide me with some advise? Thanks in advance.
|||Thank you guys. I will give it a try.
mmc.exe-Entry point not found in Enterprise Manager
I recently installed SQL 2000 on my machine and it was running fine till I installed SQL7 on the same machine. Now the SQL7 is running okay but I can't use SQL2000 anymore. I received the following while try to open Enterprise Manager
mmc-entry point not foun
The procedure entry point ?loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z could not be located in the dynamic link library SEMCOMN.dll
Can anyone provide me with some advise? Thanks in advance.Uninstall SQL Server 7.0 and 2000. If you still have to
use SQL Server 7.0 First, Install SQL Server 7.0 as the
default then, Install SQL Server 2000 as the named
instance.
>--Original Message--
>I recently installed SQL 2000 on my machine and it was
running fine till I installed SQL7 on the same machine.
Now the SQL7 is running okay but I can't use SQL2000
anymore. I received the following while try to open
Enterprise Manager:
>mmc-entry point not found
>The procedure entry point ?
loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z could not be
located in the dynamic link library SEMCOMN.dll
>Can anyone provide me with some advise? Thanks in
advance.
>.
>|||Ling,
I agree with Tom.Though not officially stated(or is it?), its a good
practice to install SQL7 first and then proceed with the SQL2000 one.In your
case, instead of trying to play around with (un)registering the dlls, go
ahead and uninstall SQL7.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Ling" <anonymous@.discussions.microsoft.com> wrote in message
news:CDFD0161-D3A2-4717-88BD-52ADF1C56322@.microsoft.com...
> I recently installed SQL 2000 on my machine and it was running fine till I
installed SQL7 on the same machine. Now the SQL7 is running okay but I
can't use SQL2000 anymore. I received the following while try to open
Enterprise Manager:
> mmc-entry point not found
> The procedure entry point ?loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z
could not be located in the dynamic link library SEMCOMN.dll
> Can anyone provide me with some advise? Thanks in advance.|||Thank you guys. I will give it a try.sql
mmc-entry point not foun
The procedure entry point ?loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z could not be located in the dynamic link library SEMCOMN.dll
Can anyone provide me with some advise? Thanks in advance.Uninstall SQL Server 7.0 and 2000. If you still have to
use SQL Server 7.0 First, Install SQL Server 7.0 as the
default then, Install SQL Server 2000 as the named
instance.
>--Original Message--
>I recently installed SQL 2000 on my machine and it was
running fine till I installed SQL7 on the same machine.
Now the SQL7 is running okay but I can't use SQL2000
anymore. I received the following while try to open
Enterprise Manager:
>mmc-entry point not found
>The procedure entry point ?
loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z could not be
located in the dynamic link library SEMCOMN.dll
>Can anyone provide me with some advise? Thanks in
advance.
>.
>|||Ling,
I agree with Tom.Though not officially stated(or is it?), its a good
practice to install SQL7 first and then proceed with the SQL2000 one.In your
case, instead of trying to play around with (un)registering the dlls, go
ahead and uninstall SQL7.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Ling" <anonymous@.discussions.microsoft.com> wrote in message
news:CDFD0161-D3A2-4717-88BD-52ADF1C56322@.microsoft.com...
> I recently installed SQL 2000 on my machine and it was running fine till I
installed SQL7 on the same machine. Now the SQL7 is running okay but I
can't use SQL2000 anymore. I received the following while try to open
Enterprise Manager:
> mmc-entry point not found
> The procedure entry point ?loadSQLToolsLibrary@.@.YAPAUHINSTANCE_@.@.PBG@.Z
could not be located in the dynamic link library SEMCOMN.dll
> Can anyone provide me with some advise? Thanks in advance.|||Thank you guys. I will give it a try.sql
Wednesday, March 21, 2012
Mixed mode to Windows mode without trace....
Hi folks,
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
Thank you in advance...
SHJ
If you were not explicitly auditing server logins during that period, you cannot obtain this information later. Also, an authentication mode change will not take effect until the server is restarted. Was your server restarted over the week-end? You should be able to see this from the errorlogs because a new errorlog would be started.
Thanks
Laurentiu
Mixed mode to Windows mode without trace....
Hi folks,
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
Thank you in advance...
SHJ
If you were not explicitly auditing server logins during that period, you cannot obtain this information later. Also, an authentication mode change will not take effect until the server is restarted. Was your server restarted over the week-end? You should be able to see this from the errorlogs because a new errorlog would be started.
Thanks
Laurentiu
mixed mode does not work
I have recently set the security mode to Mixed mode for SQL2005 running on
Win XP Pro SP-2. I want to be able to access the database from other
machines using the sa password. This I can do but I am no longer able to
use the WinNT authen. for local access. What gives?
ThanksLarry - I have only seen this when the user's windows password has
expiredbut they are still logged on in windows and try to access sql
server - any chance this is the case?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I don't think that is it. But you may be on the right track. I often use a
cached profile, I.e. I am not connected to my domain but still use a Domain
login. If that is the case, SQL server would not be able to validate my
authentication while I am not connected to the server. Of course, I can
test this when I am in the office or possible while I have a VPN open to the
Domain server.
Thanks,
Larry
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ODg6C67OHHA.4172@.TK2MSFTNGP03.phx.gbl...
> Larry - I have only seen this when the user's windows password has
> expiredbut they are still logged on in windows and try to access sql
> server - any chance this is the case?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
Win XP Pro SP-2. I want to be able to access the database from other
machines using the sa password. This I can do but I am no longer able to
use the WinNT authen. for local access. What gives?
ThanksLarry - I have only seen this when the user's windows password has
expiredbut they are still logged on in windows and try to access sql
server - any chance this is the case?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I don't think that is it. But you may be on the right track. I often use a
cached profile, I.e. I am not connected to my domain but still use a Domain
login. If that is the case, SQL server would not be able to validate my
authentication while I am not connected to the server. Of course, I can
test this when I am in the office or possible while I have a VPN open to the
Domain server.
Thanks,
Larry
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ODg6C67OHHA.4172@.TK2MSFTNGP03.phx.gbl...
> Larry - I have only seen this when the user's windows password has
> expiredbut they are still logged on in windows and try to access sql
> server - any chance this is the case?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
mixed mode does not work
I have recently set the security mode to Mixed mode for SQL2005 running on
Win XP Pro SP-2. I want to be able to access the database from other
machines using the sa password. This I can do but I am no longer able to
use the WinNT authen. for local access. What gives?
ThanksLarry - I have only seen this when the user's windows password has
expiredbut they are still logged on in windows and try to access sql
server - any chance this is the case?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I don't think that is it. But you may be on the right track. I often use a
cached profile, I.e. I am not connected to my domain but still use a Domain
login. If that is the case, SQL server would not be able to validate my
authentication while I am not connected to the server. Of course, I can
test this when I am in the office or possible while I have a VPN open to the
Domain server.
Thanks,
Larry
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ODg6C67OHHA.4172@.TK2MSFTNGP03.phx.gbl...
> Larry - I have only seen this when the user's windows password has
> expiredbut they are still logged on in windows and try to access sql
> server - any chance this is the case?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
Win XP Pro SP-2. I want to be able to access the database from other
machines using the sa password. This I can do but I am no longer able to
use the WinNT authen. for local access. What gives?
ThanksLarry - I have only seen this when the user's windows password has
expiredbut they are still logged on in windows and try to access sql
server - any chance this is the case?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I don't think that is it. But you may be on the right track. I often use a
cached profile, I.e. I am not connected to my domain but still use a Domain
login. If that is the case, SQL server would not be able to validate my
authentication while I am not connected to the server. Of course, I can
test this when I am in the office or possible while I have a VPN open to the
Domain server.
Thanks,
Larry
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ODg6C67OHHA.4172@.TK2MSFTNGP03.phx.gbl...
> Larry - I have only seen this when the user's windows password has
> expiredbut they are still logged on in windows and try to access sql
> server - any chance this is the case?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
mixed mode does not work
I have recently set the security mode to Mixed mode for SQL2005 running on
Win XP Pro SP-2. I want to be able to access the database from other
machines using the sa password. This I can do but I am no longer able to
use the WinNT authen. for local access. What gives?
Thanks
Larry - I have only seen this when the user's windows password has
expiredbut they are still logged on in windows and try to access sql
server - any chance this is the case?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I don't think that is it. But you may be on the right track. I often use a
cached profile, I.e. I am not connected to my domain but still use a Domain
login. If that is the case, SQL server would not be able to validate my
authentication while I am not connected to the server. Of course, I can
test this when I am in the office or possible while I have a VPN open to the
Domain server.
Thanks,
Larry
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ODg6C67OHHA.4172@.TK2MSFTNGP03.phx.gbl...
> Larry - I have only seen this when the user's windows password has
> expiredbut they are still logged on in windows and try to access sql
> server - any chance this is the case?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
Win XP Pro SP-2. I want to be able to access the database from other
machines using the sa password. This I can do but I am no longer able to
use the WinNT authen. for local access. What gives?
Thanks
Larry - I have only seen this when the user's windows password has
expiredbut they are still logged on in windows and try to access sql
server - any chance this is the case?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I don't think that is it. But you may be on the right track. I often use a
cached profile, I.e. I am not connected to my domain but still use a Domain
login. If that is the case, SQL server would not be able to validate my
authentication while I am not connected to the server. Of course, I can
test this when I am in the office or possible while I have a VPN open to the
Domain server.
Thanks,
Larry
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ODg6C67OHHA.4172@.TK2MSFTNGP03.phx.gbl...
> Larry - I have only seen this when the user's windows password has
> expiredbut they are still logged on in windows and try to access sql
> server - any chance this is the case?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
Monday, March 19, 2012
Mixed environments - SQL 2000 Standard & Enterprise?
We are upgrading a production database server to new hardware. The server is currently running SQL Server 2000 Standard Edition. We are thinking about installing SQL Server 2000 Enterprise Edition, however that would mean the test server (2000 Standard) and production server (2000 Enterprise) have different edtions of SQL Server. How much of a risk does this present? Later in the year we would upgrade test to SQL Server 2000 EE, but for a couple of months the environments would be different.
Thanks, DaveThere would be no side-effect for going from std to ent at all.|||The only problem I can forsee would be if you use a feature that only exists in Enterprise Edition. Most of the ones I can think of off the top of my head do not affect code, though (clustering, log shipping).|||I would use the SQL Server Dev Edition for the test environment since it has the full functionality as SQL Server Ent like log shipping.
Thanks, DaveThere would be no side-effect for going from std to ent at all.|||The only problem I can forsee would be if you use a feature that only exists in Enterprise Edition. Most of the ones I can think of off the top of my head do not affect code, though (clustering, log shipping).|||I would use the SQL Server Dev Edition for the test environment since it has the full functionality as SQL Server Ent like log shipping.
Mixed environment x64 and x86 (compatibility)
I have all sql servers running 32bit. We need another SQL cluster and I would
like to install SQL2005 x64. My concerns are the compatibility between
SQL2000 32bit, SQL2005 32bit and the new one:
questions:
can I restore a x64 database to a 32bit SQL Server?
Anyone has had compatibility issues using a mixed environment?
If there is no difference, why there are different AdventureWorks versions
(x64 and x86) to download?
http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
Microsoft says there is no problem, but I would like to hear some real
experience ..
thanks
Julio M
Hi
"Julio Mattos" wrote:
> I have all sql servers running 32bit. We need another SQL cluster and I would
> like to install SQL2005 x64. My concerns are the compatibility between
> SQL2000 32bit, SQL2005 32bit and the new one:
> questions:
> can I restore a x64 database to a 32bit SQL Server?
The backup file format is the same on both machines. The database file
format has not changed either, therefore you can move these from one platform
to the next without issues.
> Anyone has had compatibility issues using a mixed environment?
> If there is no difference, why there are different AdventureWorks versions
> (x64 and x86) to download?
> http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
This is probably because of the samples being deployed on different platforms.
> Microsoft says there is no problem, but I would like to hear some real
> experience ..
You should deploy this because you need a platform that is better suited for
the 64 bit environment i.e. better memory usage, better processors etc.
> thanks
> Julio M
>
John
like to install SQL2005 x64. My concerns are the compatibility between
SQL2000 32bit, SQL2005 32bit and the new one:
questions:
can I restore a x64 database to a 32bit SQL Server?
Anyone has had compatibility issues using a mixed environment?
If there is no difference, why there are different AdventureWorks versions
(x64 and x86) to download?
http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
Microsoft says there is no problem, but I would like to hear some real
experience ..
thanks
Julio M
Hi
"Julio Mattos" wrote:
> I have all sql servers running 32bit. We need another SQL cluster and I would
> like to install SQL2005 x64. My concerns are the compatibility between
> SQL2000 32bit, SQL2005 32bit and the new one:
> questions:
> can I restore a x64 database to a 32bit SQL Server?
The backup file format is the same on both machines. The database file
format has not changed either, therefore you can move these from one platform
to the next without issues.
> Anyone has had compatibility issues using a mixed environment?
> If there is no difference, why there are different AdventureWorks versions
> (x64 and x86) to download?
> http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
This is probably because of the samples being deployed on different platforms.
> Microsoft says there is no problem, but I would like to hear some real
> experience ..
You should deploy this because you need a platform that is better suited for
the 64 bit environment i.e. better memory usage, better processors etc.
> thanks
> Julio M
>
John
Mixed environment x64 and x86 (compatibility)
I have all sql servers running 32bit. We need another SQL cluster and I would
like to install SQL2005 x64. My concerns are the compatibility between
SQL2000 32bit, SQL2005 32bit and the new one:
questions:
can I restore a x64 database to a 32bit SQL Server?
Anyone has had compatibility issues using a mixed environment?
If there is no difference, why there are different AdventureWorks versions
(x64 and x86) to download?
http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
Microsoft says there is no problem, but I would like to hear some real
experience ..
thanks
Julio MHi
"Julio Mattos" wrote:
> I have all sql servers running 32bit. We need another SQL cluster and I would
> like to install SQL2005 x64. My concerns are the compatibility between
> SQL2000 32bit, SQL2005 32bit and the new one:
> questions:
> can I restore a x64 database to a 32bit SQL Server?
The backup file format is the same on both machines. The database file
format has not changed either, therefore you can move these from one platform
to the next without issues.
> Anyone has had compatibility issues using a mixed environment?
> If there is no difference, why there are different AdventureWorks versions
> (x64 and x86) to download?
> http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
This is probably because of the samples being deployed on different platforms.
> Microsoft says there is no problem, but I would like to hear some real
> experience ..
You should deploy this because you need a platform that is better suited for
the 64 bit environment i.e. better memory usage, better processors etc.
> thanks
> Julio M
>
John
like to install SQL2005 x64. My concerns are the compatibility between
SQL2000 32bit, SQL2005 32bit and the new one:
questions:
can I restore a x64 database to a 32bit SQL Server?
Anyone has had compatibility issues using a mixed environment?
If there is no difference, why there are different AdventureWorks versions
(x64 and x86) to download?
http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
Microsoft says there is no problem, but I would like to hear some real
experience ..
thanks
Julio MHi
"Julio Mattos" wrote:
> I have all sql servers running 32bit. We need another SQL cluster and I would
> like to install SQL2005 x64. My concerns are the compatibility between
> SQL2000 32bit, SQL2005 32bit and the new one:
> questions:
> can I restore a x64 database to a 32bit SQL Server?
The backup file format is the same on both machines. The database file
format has not changed either, therefore you can move these from one platform
to the next without issues.
> Anyone has had compatibility issues using a mixed environment?
> If there is no difference, why there are different AdventureWorks versions
> (x64 and x86) to download?
> http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
This is probably because of the samples being deployed on different platforms.
> Microsoft says there is no problem, but I would like to hear some real
> experience ..
You should deploy this because you need a platform that is better suited for
the 64 bit environment i.e. better memory usage, better processors etc.
> thanks
> Julio M
>
John
Mixed environment x64 and x86 (compatibility)
I have all sql servers running 32bit. We need another SQL cluster and I woul
d
like to install SQL2005 x64. My concerns are the compatibility between
SQL2000 32bit, SQL2005 32bit and the new one:
questions:
can I restore a x64 database to a 32bit SQL Server?
Anyone has had compatibility issues using a mixed environment?
If there is no difference, why there are different AdventureWorks versions
(x64 and x86) to download?
http://www.microsoft.com/downloads/...&displaylang=en
Microsoft says there is no problem, but I would like to hear some real
experience ..
thanks
Julio MHi
"Julio Mattos" wrote:
> I have all sql servers running 32bit. We need another SQL cluster and I wo
uld
> like to install SQL2005 x64. My concerns are the compatibility between
> SQL2000 32bit, SQL2005 32bit and the new one:
> questions:
> can I restore a x64 database to a 32bit SQL Server?
The backup file format is the same on both machines. The database file
format has not changed either, therefore you can move these from one platfor
m
to the next without issues.
> Anyone has had compatibility issues using a mixed environment?
> If there is no difference, why there are different AdventureWorks versions
> (x64 and x86) to download?
> http://www.microsoft.com/downloads/...&displaylang=en
This is probably because of the samples being deployed on different platform
s.
> Microsoft says there is no problem, but I would like to hear some real
> experience ..
You should deploy this because you need a platform that is better suited for
the 64 bit environment i.e. better memory usage, better processors etc.
> thanks
> Julio M
>
John
d
like to install SQL2005 x64. My concerns are the compatibility between
SQL2000 32bit, SQL2005 32bit and the new one:
questions:
can I restore a x64 database to a 32bit SQL Server?
Anyone has had compatibility issues using a mixed environment?
If there is no difference, why there are different AdventureWorks versions
(x64 and x86) to download?
http://www.microsoft.com/downloads/...&displaylang=en
Microsoft says there is no problem, but I would like to hear some real
experience ..
thanks
Julio MHi
"Julio Mattos" wrote:
> I have all sql servers running 32bit. We need another SQL cluster and I wo
uld
> like to install SQL2005 x64. My concerns are the compatibility between
> SQL2000 32bit, SQL2005 32bit and the new one:
> questions:
> can I restore a x64 database to a 32bit SQL Server?
The backup file format is the same on both machines. The database file
format has not changed either, therefore you can move these from one platfor
m
to the next without issues.
> Anyone has had compatibility issues using a mixed environment?
> If there is no difference, why there are different AdventureWorks versions
> (x64 and x86) to download?
> http://www.microsoft.com/downloads/...&displaylang=en
This is probably because of the samples being deployed on different platform
s.
> Microsoft says there is no problem, but I would like to hear some real
> experience ..
You should deploy this because you need a platform that is better suited for
the 64 bit environment i.e. better memory usage, better processors etc.
> thanks
> Julio M
>
John
Mitigate slow I/O during Checkpoint?
Hi,
We have a moderately sized (100GB) database which is running on a
dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
and we've recently begun an effort to speed up the performance by
cleaning up some poorly written queries, reconfiguring indexes, etc.
One of the things we've noticed while running Performance Monitor is
that when SQL Server issues a checkpoint (every 80 seconds or so during
peak production times), the write queue on the disk array containing our
data files goes from an average length of about 0.2 to about 100 and
stays there for the duration of the checkpoint, typically about 15
seconds or so. During this time queries which normally execute very
quickly (50ms or so), will sometimes require a second or more to
complete.
We opened a case with Microsoft, and the first thing they had us do was
to move our log files to a different disk array, but that didn't seem to
have any effect. We still haven't moved the log files for tempdb,
master, etc., but I wouldn't think those would matter nearly as much as
the one for our datafile. Subsequent suggestions from them don't seem
to have helped either.
Is this normal behavior? Are we I/O bound and simply require a faster
disk subsystem? Is there something in our hardware configuration which
can/should be changed to help out? Are there any SQL configuration
settings we could change to either reduce or eliminate the problem? Any
suggestions would be greatly appreciated.
Our server configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
(3) 73GB 10K drives RAID 5 (OS & Data File Logs)
Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
Logs)
Windows Server 2003
SQL 2000 Enterprise w/SP3
Thanks,
Chris
If responding via e-mail, place a period between my first and last name.
The only way I have ever been able to solve the checkpoint IO bottleneck was
to install SQL to a SAN with a decent sized write cache. Fibre channel is
full duplex so the writes do not eat all the bus bandwidth. A large cache
can absorb a checkpoint and write it to the disks in the background.
Typical SCSI controllers with 64MB or 128 MB of cache will always be
overwhelmed in a production environment by a sufficiently large checkpoint.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.207.46.248. 16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.
|||It appears you application is very write-intensive. Consider placing data
files on RAID 10.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.207.46.248. 16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.
We have a moderately sized (100GB) database which is running on a
dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
and we've recently begun an effort to speed up the performance by
cleaning up some poorly written queries, reconfiguring indexes, etc.
One of the things we've noticed while running Performance Monitor is
that when SQL Server issues a checkpoint (every 80 seconds or so during
peak production times), the write queue on the disk array containing our
data files goes from an average length of about 0.2 to about 100 and
stays there for the duration of the checkpoint, typically about 15
seconds or so. During this time queries which normally execute very
quickly (50ms or so), will sometimes require a second or more to
complete.
We opened a case with Microsoft, and the first thing they had us do was
to move our log files to a different disk array, but that didn't seem to
have any effect. We still haven't moved the log files for tempdb,
master, etc., but I wouldn't think those would matter nearly as much as
the one for our datafile. Subsequent suggestions from them don't seem
to have helped either.
Is this normal behavior? Are we I/O bound and simply require a faster
disk subsystem? Is there something in our hardware configuration which
can/should be changed to help out? Are there any SQL configuration
settings we could change to either reduce or eliminate the problem? Any
suggestions would be greatly appreciated.
Our server configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
(3) 73GB 10K drives RAID 5 (OS & Data File Logs)
Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
Logs)
Windows Server 2003
SQL 2000 Enterprise w/SP3
Thanks,
Chris
If responding via e-mail, place a period between my first and last name.
The only way I have ever been able to solve the checkpoint IO bottleneck was
to install SQL to a SAN with a decent sized write cache. Fibre channel is
full duplex so the writes do not eat all the bus bandwidth. A large cache
can absorb a checkpoint and write it to the disks in the background.
Typical SCSI controllers with 64MB or 128 MB of cache will always be
overwhelmed in a production environment by a sufficiently large checkpoint.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.207.46.248. 16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.
|||It appears you application is very write-intensive. Consider placing data
files on RAID 10.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.207.46.248. 16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.
Mitigate slow I/O during Checkpoint?
Hi,
We have a moderately sized (100GB) database which is running on a
dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
and we've recently begun an effort to speed up the performance by
cleaning up some poorly written queries, reconfiguring indexes, etc.
One of the things we've noticed while running Performance Monitor is
that when SQL Server issues a checkpoint (every 80 seconds or so during
peak production times), the write queue on the disk array containing our
data files goes from an average length of about 0.2 to about 100 and
stays there for the duration of the checkpoint, typically about 15
seconds or so. During this time queries which normally execute very
quickly (50ms or so), will sometimes require a second or more to
complete.
We opened a case with Microsoft, and the first thing they had us do was
to move our log files to a different disk array, but that didn't seem to
have any effect. We still haven't moved the log files for tempdb,
master, etc., but I wouldn't think those would matter nearly as much as
the one for our datafile. Subsequent suggestions from them don't seem
to have helped either.
Is this normal behavior? Are we I/O bound and simply require a faster
disk subsystem? Is there something in our hardware configuration which
can/should be changed to help out? Are there any SQL configuration
settings we could change to either reduce or eliminate the problem? Any
suggestions would be greatly appreciated.
Our server configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
(3) 73GB 10K drives RAID 5 (OS & Data File Logs)
Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
Logs)
Windows Server 2003
SQL 2000 Enterprise w/SP3
Thanks,
Chris
--
If responding via e-mail, place a period between my first and last name.The only way I have ever been able to solve the checkpoint IO bottleneck was
to install SQL to a SAN with a decent sized write cache. Fibre channel is
full duplex so the writes do not eat all the bus bandwidth. A large cache
can absorb a checkpoint and write it to the disks in the background.
Typical SCSI controllers with 64MB or 128 MB of cache will always be
overwhelmed in a production environment by a sufficiently large checkpoint.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.207.46.248.16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.|||It appears you application is very write-intensive. Consider placing data
files on RAID 10.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.207.46.248.16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.
We have a moderately sized (100GB) database which is running on a
dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
and we've recently begun an effort to speed up the performance by
cleaning up some poorly written queries, reconfiguring indexes, etc.
One of the things we've noticed while running Performance Monitor is
that when SQL Server issues a checkpoint (every 80 seconds or so during
peak production times), the write queue on the disk array containing our
data files goes from an average length of about 0.2 to about 100 and
stays there for the duration of the checkpoint, typically about 15
seconds or so. During this time queries which normally execute very
quickly (50ms or so), will sometimes require a second or more to
complete.
We opened a case with Microsoft, and the first thing they had us do was
to move our log files to a different disk array, but that didn't seem to
have any effect. We still haven't moved the log files for tempdb,
master, etc., but I wouldn't think those would matter nearly as much as
the one for our datafile. Subsequent suggestions from them don't seem
to have helped either.
Is this normal behavior? Are we I/O bound and simply require a faster
disk subsystem? Is there something in our hardware configuration which
can/should be changed to help out? Are there any SQL configuration
settings we could change to either reduce or eliminate the problem? Any
suggestions would be greatly appreciated.
Our server configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
(3) 73GB 10K drives RAID 5 (OS & Data File Logs)
Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
Logs)
Windows Server 2003
SQL 2000 Enterprise w/SP3
Thanks,
Chris
--
If responding via e-mail, place a period between my first and last name.The only way I have ever been able to solve the checkpoint IO bottleneck was
to install SQL to a SAN with a decent sized write cache. Fibre channel is
full duplex so the writes do not eat all the bus bandwidth. A large cache
can absorb a checkpoint and write it to the disks in the background.
Typical SCSI controllers with 64MB or 128 MB of cache will always be
overwhelmed in a production environment by a sufficiently large checkpoint.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.207.46.248.16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.|||It appears you application is very write-intensive. Consider placing data
files on RAID 10.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.207.46.248.16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.
Mitigate slow I/O during Checkpoint?
Hi,
We have a moderately sized (100GB) database which is running on a
dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
and we've recently begun an effort to speed up the performance by
cleaning up some poorly written queries, reconfiguring indexes, etc.
One of the things we've noticed while running Performance Monitor is
that when SQL Server issues a checkpoint (every 80 seconds or so during
peak production times), the write queue on the disk array containing our
data files goes from an average length of about 0.2 to about 100 and
stays there for the duration of the checkpoint, typically about 15
seconds or so. During this time queries which normally execute very
quickly (50ms or so), will sometimes require a second or more to
complete.
We opened a case with Microsoft, and the first thing they had us do was
to move our log files to a different disk array, but that didn't seem to
have any effect. We still haven't moved the log files for tempdb,
master, etc., but I wouldn't think those would matter nearly as much as
the one for our datafile. Subsequent suggestions from them don't seem
to have helped either.
Is this normal behavior? Are we I/O bound and simply require a faster
disk subsystem? Is there something in our hardware configuration which
can/should be changed to help out? Are there any SQL configuration
settings we could change to either reduce or eliminate the problem? Any
suggestions would be greatly appreciated.
Our server configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
(3) 73GB 10K drives RAID 5 (OS & Data File Logs)
Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
Logs)
Windows Server 2003
SQL 2000 Enterprise w/SP3
Thanks,
Chris
If responding via e-mail, place a period between my first and last name.The only way I have ever been able to solve the checkpoint IO bottleneck was
to install SQL to a SAN with a decent sized write cache. Fibre channel is
full duplex so the writes do not eat all the bus bandwidth. A large cache
can absorb a checkpoint and write it to the disks in the background.
Typical SCSI controllers with 64MB or 128 MB of cache will always be
overwhelmed in a production environment by a sufficiently large checkpoint.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.2
07.46.248.16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.|||It appears you application is very write-intensive. Consider placing data
files on RAID 10.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.2
07.46.248.16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.
We have a moderately sized (100GB) database which is running on a
dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
and we've recently begun an effort to speed up the performance by
cleaning up some poorly written queries, reconfiguring indexes, etc.
One of the things we've noticed while running Performance Monitor is
that when SQL Server issues a checkpoint (every 80 seconds or so during
peak production times), the write queue on the disk array containing our
data files goes from an average length of about 0.2 to about 100 and
stays there for the duration of the checkpoint, typically about 15
seconds or so. During this time queries which normally execute very
quickly (50ms or so), will sometimes require a second or more to
complete.
We opened a case with Microsoft, and the first thing they had us do was
to move our log files to a different disk array, but that didn't seem to
have any effect. We still haven't moved the log files for tempdb,
master, etc., but I wouldn't think those would matter nearly as much as
the one for our datafile. Subsequent suggestions from them don't seem
to have helped either.
Is this normal behavior? Are we I/O bound and simply require a faster
disk subsystem? Is there something in our hardware configuration which
can/should be changed to help out? Are there any SQL configuration
settings we could change to either reduce or eliminate the problem? Any
suggestions would be greatly appreciated.
Our server configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
(3) 73GB 10K drives RAID 5 (OS & Data File Logs)
Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
Logs)
Windows Server 2003
SQL 2000 Enterprise w/SP3
Thanks,
Chris
If responding via e-mail, place a period between my first and last name.The only way I have ever been able to solve the checkpoint IO bottleneck was
to install SQL to a SAN with a decent sized write cache. Fibre channel is
full duplex so the writes do not eat all the bus bandwidth. A large cache
can absorb a checkpoint and write it to the disks in the background.
Typical SCSI controllers with 64MB or 128 MB of cache will always be
overwhelmed in a production environment by a sufficiently large checkpoint.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.2
07.46.248.16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.|||It appears you application is very write-intensive. Consider placing data
files on RAID 10.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Birk" <chrisbirk@.gmail.com> wrote in message
news:Xns961A7E02A1B16chrisbirkgmailcom@.2
07.46.248.16...
> Hi,
> We have a moderately sized (100GB) database which is running on a
> dedicated SQL Server. Our application is a mixture of OLTP and OLAP,
> and we've recently begun an effort to speed up the performance by
> cleaning up some poorly written queries, reconfiguring indexes, etc.
> One of the things we've noticed while running Performance Monitor is
> that when SQL Server issues a checkpoint (every 80 seconds or so during
> peak production times), the write queue on the disk array containing our
> data files goes from an average length of about 0.2 to about 100 and
> stays there for the duration of the checkpoint, typically about 15
> seconds or so. During this time queries which normally execute very
> quickly (50ms or so), will sometimes require a second or more to
> complete.
> We opened a case with Microsoft, and the first thing they had us do was
> to move our log files to a different disk array, but that didn't seem to
> have any effect. We still haven't moved the log files for tempdb,
> master, etc., but I wouldn't think those would matter nearly as much as
> the one for our datafile. Subsequent suggestions from them don't seem
> to have helped either.
> Is this normal behavior? Are we I/O bound and simply require a faster
> disk subsystem? Is there something in our hardware configuration which
> can/should be changed to help out? Are there any SQL configuration
> settings we could change to either reduce or eliminate the problem? Any
> suggestions would be greatly appreciated.
> Our server configuration is as follows:
> Dell 2650
> Dual 2.6GHz Xeon
> 2GB RAM
> (3) 73GB 10K drives RAID 5 (OS & Data File Logs)
> Powervault 220 w/14 36GB 15K drives RAID 5 (Data Files & SQL System
> Logs)
> Windows Server 2003
> SQL 2000 Enterprise w/SP3
> Thanks,
> Chris
> --
> If responding via e-mail, place a period between my first and last name.
Monday, March 12, 2012
missing tables and stored procedures after restoring database
I have been running a desktop PC with MSDE 1.0 installed. I had made a
backup of the data using a t-sql command backup database ... Since this PC
had crashed and has subsequently been rebuilt, I have restored the database
but found that vital tables and stored procedures are missing. The missing
tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
into the database as user sa.
Any help in recovering those missing tables and stored procedures would be
greatly appreciated.
use restore headeronly command on the backup file To check when the
backup was taken looks like you haven't backed up the database when
you created the tables whcih you said you were missing or you have
restored
an earlier backup not the latest once since you are the Sysadmin u can
view all the tables created with any login and if you want to have
information
about the latest backup taken check out the one with
Use Msdb
select *from backupset
order by backup_finish_date desc
|||If they're missing after a restore then they were not there at the time
of the back and are now gone forever (presumedly they were created after
the backup you used to restore the DB). Do you have any later backups?
BTW, every table & proc in a DB has an owner. To be sure you're just
not missing it in whatever client-side tool you're using execute this T-SQL:
select o.[name] as tablename, u.[name] as ownername, type
from dbo.sysobjects as o
inner join dbo.sysusers as u on o.uid = o.uid
order by type, u.[name], o.[name]
Have a look to see if you can see the proc (type P) or the table (type
U) somewhere in the resultset. Also are you sure these "vital" tables &
procs were in the same database and weren't in another database that was
being referenced from your database? For example,
use A
go
select * from B.dbo.MyVitalTable
exec B.dbo.MyVitalProc
If that was true then they wouldn't be included in the backup (and
therefore subsequent restore) of your database.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Old Paulie wrote:
>I have been running a desktop PC with MSDE 1.0 installed. I had made a
>backup of the data using a t-sql command backup database ... Since this PC
>had crashed and has subsequently been rebuilt, I have restored the database
>but found that vital tables and stored procedures are missing. The missing
>tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
>into the database as user sa.
>Any help in recovering those missing tables and stored procedures would be
>greatly appreciated.
>
|||saradhi,
Thanks for the advice, I have checked the backupset table in the Msdb
database and that only describes one backup taken last year. The backup at
that time probably would not have included these missing tables. I had
however made several backups and their is no proof of these in this table. I
need to obviously review the way I back up since it appears I am doing
something fundamentally wrong. Any advice on backing up for future reference
would be greatly appreciated. Should I be backing up the other databases
that are created upon install of msde, ie Master, Msdb etc ...?
"saradhi" wrote:
> use restore headeronly command on the backup file To check when the
> backup was taken looks like you haven't backed up the database when
> you created the tables whcih you said you were missing or you have
> restored
> an earlier backup not the latest once since you are the Sysadmin u can
> view all the tables created with any login and if you want to have
> information
> about the latest backup taken check out the one with
> Use Msdb
> select *from backupset
> order by backup_finish_date desc
>
|||Hi Mike,
I am quite sure that the vital tables belonged to my database as I had
created no others! I did run your query and did not find and records
relating to the missing tables and stored procedures. From your advice and
'saradhi's', it is obvious now that my backup was flawed and I am doing
something wrong
Thank you for your assistance.
"Mike Hodgson" wrote:
[vbcol=seagreen]
> If they're missing after a restore then they were not there at the time
> of the back and are now gone forever (presumedly they were created after
> the backup you used to restore the DB). Do you have any later backups?
> BTW, every table & proc in a DB has an owner. To be sure you're just
> not missing it in whatever client-side tool you're using execute this T-SQL:
> select o.[name] as tablename, u.[name] as ownername, type
> from dbo.sysobjects as o
> inner join dbo.sysusers as u on o.uid = o.uid
> order by type, u.[name], o.[name]
> Have a look to see if you can see the proc (type P) or the table (type
> U) somewhere in the resultset. Also are you sure these "vital" tables &
> procs were in the same database and weren't in another database that was
> being referenced from your database? For example,
> use A
> go
> select * from B.dbo.MyVitalTable
> exec B.dbo.MyVitalProc
> If that was true then they wouldn't be included in the backup (and
> therefore subsequent restore) of your database.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Old Paulie wrote:
|||One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
Check using RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I am quite sure that the vital tables belonged to my database as I had
> created no others! I did run your query and did not find and records
> relating to the missing tables and stored procedures. From your advice and
> 'saradhi's', it is obvious now that my backup was flawed and I am doing
> something wrong Thank you for your assistance.
> "Mike Hodgson" wrote:
|||Tibor,
It turns out that my backup DID include several backup sets and I was
therefore able to restore the most recent backup set to get back all missing
tables and procedures.
THANK YOU VERY MUCH!
"Tibor Karaszi" wrote:
> One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
> Check using RESTORE HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
> news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...
>
backup of the data using a t-sql command backup database ... Since this PC
had crashed and has subsequently been rebuilt, I have restored the database
but found that vital tables and stored procedures are missing. The missing
tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
into the database as user sa.
Any help in recovering those missing tables and stored procedures would be
greatly appreciated.
use restore headeronly command on the backup file To check when the
backup was taken looks like you haven't backed up the database when
you created the tables whcih you said you were missing or you have
restored
an earlier backup not the latest once since you are the Sysadmin u can
view all the tables created with any login and if you want to have
information
about the latest backup taken check out the one with
Use Msdb
select *from backupset
order by backup_finish_date desc
|||If they're missing after a restore then they were not there at the time
of the back and are now gone forever (presumedly they were created after
the backup you used to restore the DB). Do you have any later backups?
BTW, every table & proc in a DB has an owner. To be sure you're just
not missing it in whatever client-side tool you're using execute this T-SQL:
select o.[name] as tablename, u.[name] as ownername, type
from dbo.sysobjects as o
inner join dbo.sysusers as u on o.uid = o.uid
order by type, u.[name], o.[name]
Have a look to see if you can see the proc (type P) or the table (type
U) somewhere in the resultset. Also are you sure these "vital" tables &
procs were in the same database and weren't in another database that was
being referenced from your database? For example,
use A
go
select * from B.dbo.MyVitalTable
exec B.dbo.MyVitalProc
If that was true then they wouldn't be included in the backup (and
therefore subsequent restore) of your database.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Old Paulie wrote:
>I have been running a desktop PC with MSDE 1.0 installed. I had made a
>backup of the data using a t-sql command backup database ... Since this PC
>had crashed and has subsequently been rebuilt, I have restored the database
>but found that vital tables and stored procedures are missing. The missing
>tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
>into the database as user sa.
>Any help in recovering those missing tables and stored procedures would be
>greatly appreciated.
>
|||saradhi,
Thanks for the advice, I have checked the backupset table in the Msdb
database and that only describes one backup taken last year. The backup at
that time probably would not have included these missing tables. I had
however made several backups and their is no proof of these in this table. I
need to obviously review the way I back up since it appears I am doing
something fundamentally wrong. Any advice on backing up for future reference
would be greatly appreciated. Should I be backing up the other databases
that are created upon install of msde, ie Master, Msdb etc ...?
"saradhi" wrote:
> use restore headeronly command on the backup file To check when the
> backup was taken looks like you haven't backed up the database when
> you created the tables whcih you said you were missing or you have
> restored
> an earlier backup not the latest once since you are the Sysadmin u can
> view all the tables created with any login and if you want to have
> information
> about the latest backup taken check out the one with
> Use Msdb
> select *from backupset
> order by backup_finish_date desc
>
|||Hi Mike,
I am quite sure that the vital tables belonged to my database as I had
created no others! I did run your query and did not find and records
relating to the missing tables and stored procedures. From your advice and
'saradhi's', it is obvious now that my backup was flawed and I am doing
something wrong
Thank you for your assistance."Mike Hodgson" wrote:
[vbcol=seagreen]
> If they're missing after a restore then they were not there at the time
> of the back and are now gone forever (presumedly they were created after
> the backup you used to restore the DB). Do you have any later backups?
> BTW, every table & proc in a DB has an owner. To be sure you're just
> not missing it in whatever client-side tool you're using execute this T-SQL:
> select o.[name] as tablename, u.[name] as ownername, type
> from dbo.sysobjects as o
> inner join dbo.sysusers as u on o.uid = o.uid
> order by type, u.[name], o.[name]
> Have a look to see if you can see the proc (type P) or the table (type
> U) somewhere in the resultset. Also are you sure these "vital" tables &
> procs were in the same database and weren't in another database that was
> being referenced from your database? For example,
> use A
> go
> select * from B.dbo.MyVitalTable
> exec B.dbo.MyVitalProc
> If that was true then they wouldn't be included in the backup (and
> therefore subsequent restore) of your database.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Old Paulie wrote:
|||One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
Check using RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I am quite sure that the vital tables belonged to my database as I had
> created no others! I did run your query and did not find and records
> relating to the missing tables and stored procedures. From your advice and
> 'saradhi's', it is obvious now that my backup was flawed and I am doing
> something wrong
Thank you for your assistance.> "Mike Hodgson" wrote:
|||Tibor,
It turns out that my backup DID include several backup sets and I was
therefore able to restore the most recent backup set to get back all missing
tables and procedures.
THANK YOU VERY MUCH!
"Tibor Karaszi" wrote:
> One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
> Check using RESTORE HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
> news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...
>
missing tables and stored procedures after restoring database
I have been running a desktop PC with MSDE 1.0 installed. I had made a
backup of the data using a t-sql command backup database ... Since this PC
had crashed and has subsequently been rebuilt, I have restored the database
but found that vital tables and stored procedures are missing. The missing
tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
into the database as user sa.
Any help in recovering those missing tables and stored procedures would be
greatly appreciated.use restore headeronly command on the backup file To check when the
backup was taken looks like you haven't backed up the database when
you created the tables whcih you said you were missing or you have
restored
an earlier backup not the latest once since you are the Sysadmin u can
view all the tables created with any login and if you want to have
information
about the latest backup taken check out the one with
Use Msdb
select *from backupset
order by backup_finish_date desc|||If they're missing after a restore then they were not there at the time
of the back and are now gone forever (presumedly they were created after
the backup you used to restore the DB). Do you have any later backups?
BTW, every table & proc in a DB has an owner. To be sure you're just
not missing it in whatever client-side tool you're using execute this T-SQL:
select o.[name] as tablename, u.[name] as ownername, type
from dbo.sysobjects as o
inner join dbo.sysusers as u on o.uid = o.uid
order by type, u.[name], o.[name]
Have a look to see if you can see the proc (type P) or the table (type
U) somewhere in the resultset. Also are you sure these "vital" tables &
procs were in the same database and weren't in another database that was
being referenced from your database? For example,
use A
go
select * from B.dbo.MyVitalTable
exec B.dbo.MyVitalProc
If that was true then they wouldn't be included in the backup (and
therefore subsequent restore) of your database.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Old Paulie wrote:
>I have been running a desktop PC with MSDE 1.0 installed. I had made a
>backup of the data using a t-sql command backup database ... Since this PC
>had crashed and has subsequently been rebuilt, I have restored the database
>but found that vital tables and stored procedures are missing. The missing
>tables were not allegedly owned by dbo but perhaps had no owner(?) PS I lo
g
>into the database as user sa.
>Any help in recovering those missing tables and stored procedures would be
>greatly appreciated.
>|||saradhi,
Thanks for the advice, I have checked the backupset table in the Msdb
database and that only describes one backup taken last year. The backup at
that time probably would not have included these missing tables. I had
however made several backups and their is no proof of these in this table.
I
need to obviously review the way I back up since it appears I am doing
something fundamentally wrong. Any advice on backing up for future referenc
e
would be greatly appreciated. Should I be backing up the other databases
that are created upon install of msde, ie Master, Msdb etc ...?
"saradhi" wrote:
> use restore headeronly command on the backup file To check when the
> backup was taken looks like you haven't backed up the database when
> you created the tables whcih you said you were missing or you have
> restored
> an earlier backup not the latest once since you are the Sysadmin u can
> view all the tables created with any login and if you want to have
> information
> about the latest backup taken check out the one with
> Use Msdb
> select *from backupset
> order by backup_finish_date desc
>|||Hi Mike,
I am quite sure that the vital tables belonged to my database as I had
created no others! I did run your query and did not find and records
relating to the missing tables and stored procedures. From your advice and
'saradhi's', it is obvious now that my backup was flawed and I am doing
something wrong Thank you for your assistance.
"Mike Hodgson" wrote:
[vbcol=seagreen]
> If they're missing after a restore then they were not there at the time
> of the back and are now gone forever (presumedly they were created after
> the backup you used to restore the DB). Do you have any later backups?
> BTW, every table & proc in a DB has an owner. To be sure you're just
> not missing it in whatever client-side tool you're using execute this T-SQ
L:
> select o.[name] as tablename, u.[name] as ownername, type
> from dbo.sysobjects as o
> inner join dbo.sysusers as u on o.uid = o.uid
> order by type, u.[name], o.[name]
> Have a look to see if you can see the proc (type P) or the table (type
> U) somewhere in the resultset. Also are you sure these "vital" tables &
> procs were in the same database and weren't in another database that was
> being referenced from your database? For example,
> use A
> go
> select * from B.dbo.MyVitalTable
> exec B.dbo.MyVitalProc
> If that was true then they wouldn't be included in the backup (and
> therefore subsequent restore) of your database.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Old Paulie wrote:
>|||One last chance. Perhaps your backup file has several backups on it, and you
restore the first one?
Check using RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I am quite sure that the vital tables belonged to my database as I had
> created no others! I did run your query and did not find and records
> relating to the missing tables and stored procedures. From your advice an
d
> 'saradhi's', it is obvious now that my backup was flawed and I am doing
> something wrong Thank you for your assistance.
> "Mike Hodgson" wrote:
>|||Tibor,
It turns out that my backup DID include several backup sets and I was
therefore able to restore the most recent backup set to get back all missing
tables and procedures.
THANK YOU VERY MUCH!
"Tibor Karaszi" wrote:
> One last chance. Perhaps your backup file has several backups on it, and y
ou restore the first one?
> Check using RESTORE HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
> news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...
>
backup of the data using a t-sql command backup database ... Since this PC
had crashed and has subsequently been rebuilt, I have restored the database
but found that vital tables and stored procedures are missing. The missing
tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
into the database as user sa.
Any help in recovering those missing tables and stored procedures would be
greatly appreciated.use restore headeronly command on the backup file To check when the
backup was taken looks like you haven't backed up the database when
you created the tables whcih you said you were missing or you have
restored
an earlier backup not the latest once since you are the Sysadmin u can
view all the tables created with any login and if you want to have
information
about the latest backup taken check out the one with
Use Msdb
select *from backupset
order by backup_finish_date desc|||If they're missing after a restore then they were not there at the time
of the back and are now gone forever (presumedly they were created after
the backup you used to restore the DB). Do you have any later backups?
BTW, every table & proc in a DB has an owner. To be sure you're just
not missing it in whatever client-side tool you're using execute this T-SQL:
select o.[name] as tablename, u.[name] as ownername, type
from dbo.sysobjects as o
inner join dbo.sysusers as u on o.uid = o.uid
order by type, u.[name], o.[name]
Have a look to see if you can see the proc (type P) or the table (type
U) somewhere in the resultset. Also are you sure these "vital" tables &
procs were in the same database and weren't in another database that was
being referenced from your database? For example,
use A
go
select * from B.dbo.MyVitalTable
exec B.dbo.MyVitalProc
If that was true then they wouldn't be included in the backup (and
therefore subsequent restore) of your database.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Old Paulie wrote:
>I have been running a desktop PC with MSDE 1.0 installed. I had made a
>backup of the data using a t-sql command backup database ... Since this PC
>had crashed and has subsequently been rebuilt, I have restored the database
>but found that vital tables and stored procedures are missing. The missing
>tables were not allegedly owned by dbo but perhaps had no owner(?) PS I lo
g
>into the database as user sa.
>Any help in recovering those missing tables and stored procedures would be
>greatly appreciated.
>|||saradhi,
Thanks for the advice, I have checked the backupset table in the Msdb
database and that only describes one backup taken last year. The backup at
that time probably would not have included these missing tables. I had
however made several backups and their is no proof of these in this table.
I
need to obviously review the way I back up since it appears I am doing
something fundamentally wrong. Any advice on backing up for future referenc
e
would be greatly appreciated. Should I be backing up the other databases
that are created upon install of msde, ie Master, Msdb etc ...?
"saradhi" wrote:
> use restore headeronly command on the backup file To check when the
> backup was taken looks like you haven't backed up the database when
> you created the tables whcih you said you were missing or you have
> restored
> an earlier backup not the latest once since you are the Sysadmin u can
> view all the tables created with any login and if you want to have
> information
> about the latest backup taken check out the one with
> Use Msdb
> select *from backupset
> order by backup_finish_date desc
>|||Hi Mike,
I am quite sure that the vital tables belonged to my database as I had
created no others! I did run your query and did not find and records
relating to the missing tables and stored procedures. From your advice and
'saradhi's', it is obvious now that my backup was flawed and I am doing
something wrong
Thank you for your assistance."Mike Hodgson" wrote:
[vbcol=seagreen]
> If they're missing after a restore then they were not there at the time
> of the back and are now gone forever (presumedly they were created after
> the backup you used to restore the DB). Do you have any later backups?
> BTW, every table & proc in a DB has an owner. To be sure you're just
> not missing it in whatever client-side tool you're using execute this T-SQ
L:
> select o.[name] as tablename, u.[name] as ownername, type
> from dbo.sysobjects as o
> inner join dbo.sysusers as u on o.uid = o.uid
> order by type, u.[name], o.[name]
> Have a look to see if you can see the proc (type P) or the table (type
> U) somewhere in the resultset. Also are you sure these "vital" tables &
> procs were in the same database and weren't in another database that was
> being referenced from your database? For example,
> use A
> go
> select * from B.dbo.MyVitalTable
> exec B.dbo.MyVitalProc
> If that was true then they wouldn't be included in the backup (and
> therefore subsequent restore) of your database.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Old Paulie wrote:
>|||One last chance. Perhaps your backup file has several backups on it, and you
restore the first one?
Check using RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I am quite sure that the vital tables belonged to my database as I had
> created no others! I did run your query and did not find and records
> relating to the missing tables and stored procedures. From your advice an
d
> 'saradhi's', it is obvious now that my backup was flawed and I am doing
> something wrong
Thank you for your assistance.> "Mike Hodgson" wrote:
>|||Tibor,
It turns out that my backup DID include several backup sets and I was
therefore able to restore the most recent backup set to get back all missing
tables and procedures.
THANK YOU VERY MUCH!
"Tibor Karaszi" wrote:
> One last chance. Perhaps your backup file has several backups on it, and y
ou restore the first one?
> Check using RESTORE HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
> news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...
>
Subscribe to:
Posts (Atom)