maintenance plan wizard: enterprise

Showing posts with label enterprise. Show all posts

Friday, March 30, 2012

Manage server messages shows % instead of name of item in error

When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table. It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.What you are seeing in Enterprise Manager is a collection of all the error
messages, that are stored in the master..sysmessages table. They are not the
errors raised by your application. When an error occurs, SQL Server will
raise those errors by substituting the parameters with appropriate values.
If you think you are experiencing an error in your application, then you
should capture the error number and the error description via the mechanism
provided by your programming language. For example, the Err object in VB.
Alternatively you can use SQL Server Profiler to see what statements are
being executed by SQL Server. You can trace the Exception event to capture
errors.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table.
It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because
there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.|||Narayana:
Thanks for your suggestions. Can you point me to a resource on tracing the
message with profiler. The vb app does not seem to be throwing an exception
so i am trying to figure out what is going on. I am not sure it is even
trying to write to the db at this point.
"Narayana Vyas Kondreddi" wrote:
> What you are seeing in Enterprise Manager is a collection of all the error
> messages, that are stored in the master..sysmessages table. They are not the
> errors raised by your application. When an error occurs, SQL Server will
> raise those errors by substituting the parameters with appropriate values.
> If you think you are experiencing an error in your application, then you
> should capture the error number and the error description via the mechanism
> provided by your programming language. For example, the Err object in VB.
> Alternatively you can use SQL Server Profiler to see what statements are
> being executed by SQL Server. You can trace the Exception event to capture
> errors.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
> When I right click on my sql server in the enterprise manager and select all
> tasks --> manage server messages I am not seeing the object the error is
> referring to. Instead I see a % wherever it should be inserting the object
> name like below.
> Incorrect value for parameter '%s'.
> What do i have to do to get the server to substitute the tablename, etc. for
> the % sign. Also is there anyway to see a timestamp for the errors returned.
> It would be helpful to know when the error occured. Basically I am having
> issues with an ASP.net page that is doing an update on a particular table.
> It
> is not throwing an exception on the asp.net side so i wanted to look on the
> sql side to see what happened. But on the sql side i can't tell because
> there
> is no indication which table or when a particualr message in the manage
> server messages tool refers too. Is there a better way to go about this?
> Server is SQL server 2000.
>
>|||Could it be that you have something like 'On Error Resume Next' in your VB
app, that is silently ignoring the error?
I am not aware of any step by step instructions for using Profiler but, it
should be quite simple and intuitive once you open it. I have some related
articles at: http://vyaskn.tripod.com/analyzing_profiler_output.htm
Apart from that do checkout SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:A60CD0CE-FB4B-4CB3-B74D-0226A913D920@.microsoft.com...
Narayana:
Thanks for your suggestions. Can you point me to a resource on tracing the
message with profiler. The vb app does not seem to be throwing an exception
so i am trying to figure out what is going on. I am not sure it is even
trying to write to the db at this point.
"Narayana Vyas Kondreddi" wrote:
> What you are seeing in Enterprise Manager is a collection of all the error
> messages, that are stored in the master..sysmessages table. They are not
the
> errors raised by your application. When an error occurs, SQL Server will
> raise those errors by substituting the parameters with appropriate values.
> If you think you are experiencing an error in your application, then you
> should capture the error number and the error description via the
mechanism
> provided by your programming language. For example, the Err object in VB.
> Alternatively you can use SQL Server Profiler to see what statements are
> being executed by SQL Server. You can trace the Exception event to capture
> errors.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
> When I right click on my sql server in the enterprise manager and select
all
> tasks --> manage server messages I am not seeing the object the error is
> referring to. Instead I see a % wherever it should be inserting the
object
> name like below.
> Incorrect value for parameter '%s'.
> What do i have to do to get the server to substitute the tablename, etc.
for
> the % sign. Also is there anyway to see a timestamp for the errors
returned.
> It would be helpful to know when the error occured. Basically I am having
> issues with an ASP.net page that is doing an update on a particular table.
> It
> is not throwing an exception on the asp.net side so i wanted to look on
the
> sql side to see what happened. But on the sql side i can't tell because
> there
> is no indication which table or when a particualr message in the manage
> server messages tool refers too. Is there a better way to go about this?
> Server is SQL server 2000.
>
>|||Narayana:
Thanks, I had already opened the profiler and you are right it is pretty
intuitive. I was able to capture the sql statement being sent from the vb app
and it did not cause an exception it just was returning 0 rows affected.
Maybe you guys could take a quick look at the statement 9hah - it is ugly)
and see if you can see the error. It is generated by ASP.Net 2.0 using a
gridview and sqldatasource. All of the code is generated by the asp controls
not me so I was expecting it to work for the simple test i was doing. here is
the generated statement with linebreaks inserted to make it more readable.
exec sp_executesql N
'UPDATE [MSDS]
SET
[MSDSName] = @.MSDSName,
[MSDSLink] = @.MSDSLink,
[MSDSSubmittedBy] = @.MSDSSubmittedBy,
[MSDSApprovedBy] = @.MSDSApprovedBy,
[ProductCategoryID] = @.ProductCategoryID,
[MSDSLanguageID] = @.MSDSLanguageID,
[MSDSApproved] = @.MSDSApproved,
[MSDSSubmittedDate] = @.MSDSSubmittedDate,
[MSDSExpired] = @.MSDSExpired
WHERE [MSDSID] = @.original_MSDSID
AND [MSDSName] = @.original_MSDSName
AND [MSDSLink] = @.original_MSDSLink
AND [MSDSSubmittedBy] = @.original_MSDSSubmittedBy
AND [MSDSApprovedBy] = @.original_MSDSApprovedBy
AND [ProductCategoryID] = @.original_ProductCategoryID
AND [MSDSLanguageID] = @.original_MSDSLanguageID
AND [MSDSApproved] = @.original_MSDSApproved
AND [MSDSSubmittedDate] = @.original_MSDSSubmittedDate
AND [MSDSExpired] = @.original_MSDSExpired',
N'@.MSDSName nvarchar(4000),
@.MSDSLink nvarchar(4000),
@.MSDSSubmittedBy nvarchar(8),
@.MSDSApprovedBy nvarchar(4000),
@.ProductCategoryID int,
@.MSDSLanguageID int,
@.MSDSApproved bit,
@.MSDSSubmittedDate nvarchar(4000),
@.MSDSExpired bit,@.original_MSDSID int,
@.original_MSDSName nvarchar(4000),
@.original_MSDSLink nvarchar(4000),
@.original_MSDSSubmittedBy nvarchar(8),
@.original_MSDSApprovedBy nvarchar(4000),
@.original_ProductCategoryID int,
@.original_MSDSLanguageID int,
@.original_MSDSApproved bit,
@.original_MSDSSubmittedDate nvarchar(4000),
@.original_MSDSExpired bit',
@.MSDSName = NULL,
@.MSDSLink = NULL,
@.MSDSSubmittedBy = N'KMcPhail',
@.MSDSApprovedBy = NULL,
@.ProductCategoryID = 5,
@.MSDSLanguageID = 1,
@.MSDSApproved = 0,
@.MSDSSubmittedDate = NULL,
@.MSDSExpired = 0,
@.original_MSDSID = 133,
@.original_MSDSName = NULL,
@.original_MSDSLink = NULL,
@.original_MSDSSubmittedBy = N'KMcPhail',
@.original_MSDSApprovedBy = NULL,
@.original_ProductCategoryID = 5,
@.original_MSDSLanguageID = 3,
@.original_MSDSApproved = 0,
@.original_MSDSSubmittedDate = NULL,
@.original_MSDSExpired = 0
"Narayana Vyas Kondreddi" wrote:
> Could it be that you have something like 'On Error Resume Next' in your VB
> app, that is silently ignoring the error?
> I am not aware of any step by step instructions for using Profiler but, it
> should be quite simple and intuitive once you open it. I have some related
> articles at: http://vyaskn.tripod.com/analyzing_profiler_output.htm
> Apart from that do checkout SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> news:A60CD0CE-FB4B-4CB3-B74D-0226A913D920@.microsoft.com...
> Narayana:
> Thanks for your suggestions. Can you point me to a resource on tracing the
> message with profiler. The vb app does not seem to be throwing an exception
> so i am trying to figure out what is going on. I am not sure it is even
> trying to write to the db at this point.
> "Narayana Vyas Kondreddi" wrote:
> > What you are seeing in Enterprise Manager is a collection of all the error
> > messages, that are stored in the master..sysmessages table. They are not
> the
> > errors raised by your application. When an error occurs, SQL Server will
> > raise those errors by substituting the parameters with appropriate values.
> >
> > If you think you are experiencing an error in your application, then you
> > should capture the error number and the error description via the
> mechanism
> > provided by your programming language. For example, the Err object in VB.
> >
> > Alternatively you can use SQL Server Profiler to see what statements are
> > being executed by SQL Server. You can trace the Exception event to capture
> > errors.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> > news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
> > When I right click on my sql server in the enterprise manager and select
> all
> > tasks --> manage server messages I am not seeing the object the error is
> > referring to. Instead I see a % wherever it should be inserting the
> object
> > name like below.
> >
> > Incorrect value for parameter '%s'.
> >
> > What do i have to do to get the server to substitute the tablename, etc.
> for
> > the % sign. Also is there anyway to see a timestamp for the errors
> returned.
> > It would be helpful to know when the error occured. Basically I am having
> > issues with an ASP.net page that is doing an update on a particular table.
> > It
> > is not throwing an exception on the asp.net side so i wanted to look on
> the
> > sql side to see what happened. But on the sql side i can't tell because
> > there
> > is no indication which table or when a particualr message in the manage
> > server messages tool refers too. Is there a better way to go about this?
> > Server is SQL server 2000.
> >
> >
> >
>
>

Manage server messages shows % instead of name of item in error

Wednesday, March 28, 2012

Manage MSDE from SQL Server

Hi everybody, I have 2 "server" one run SQL Server and other run MSDE, I nee
d use Enterprise Manager to manage MSDE, but I receive a messager error "Sql
does not exist or access denied" I have the SA password MSDE, I can only ma
nage the MSDE in the machin
e installed,
I hope your answer!
Best Regards!Thanks to Bishop for his help, this is the solution
set this in setup.ini:
DisableNetworkProtocols=0
Best Regards!

Manage MSDE from SQL Server

Hi everybody, I have 2 "server" one run SQL Server and other run MSDE, I need use Enterprise Manager to manage MSDE, but I receive a messager error "Sql does not exist or access denied" I have the SA password MSDE, I can only manage the MSDE in the machin
e installed,
I hope your answer!
Best Regards!
Thanks to Bishop for his help, this is the solution
set this in setup.ini:
DisableNetworkProtocols=0
Best Regards!

Making SSAS Developer Edition act like standard edition

My understanding is that the Developer Edition of SQL/SSAS contain all of the functionality of the Enterprise Edition. Is there a way to force it to act as if it were Standard Edition (and therefore subject to all of the limitations of SE)?

The reason I ask is that with our product we're trying to maintain compatibility with both SE and EE. I realize I can just install SE instead of DE, but if there's a way to simple toggle the behavior, that would be even better (especially since I already have DE installed. :)

A partial answer:

If you have a BI project (that you open with Visual Studio to design and then deploy to AS2005), there is a project setting for specifying the Edition (right click on the project node, use 'Properties' and notice the edition setting at the top of the page). This will affect the validations to be done in the tools only (in Visual Studio for the BI project), the AS2005 server will do his own validations (based on its Developer Edition that you mention).

Adrian Dumitrascu.

|||Ah, good tip, thanks. Unfortunately, that's not quite good enough for us, because we're doing programmatic manipulations of the cube definition using AMO. But it's good to know about, nonetheless.
|||

In this case, you can use the AMO's Validations. After you create or change a major object with AMO, before you call the .Update method, use the .Validate method, passing it explicitly the StandardEdition (otherwise it will use the engine's edition).

Sample code:

Cube myCube = ...;

ValidationErrorCollection errors = new ValidationErrorCollection();

myCube.Validate(errors, true, ServerEdition.Standard); // 'true' = include detailed errors, no problem to use 'false' for our scenario

if (errors.Count > 0)

{

// We either specified something invalid or we used features unsupported in the standard edition

foreach (ValidationError error in errors)

{

Console.WriteLine(error.FullErrorText);

}

Adrian.

|||Thanks Adrian. I would prefer to be able to just fix the server level, so I don't need to change the code to support this (once the product is deployed, these checks aren't really necessary). But in the absense of that capability, I can probably make do with the validation stuff.
|||

I asked around and we don't have a setting per server to downgrade the edition. You could install the standard edition as a named instance eventually (side-by-side with your current developer edition).

Adrian Dumitrascu.

|||Well, it's nice to know definitively. Thanks for the info. Feel free to add that to the enhancement request list for the next version. ;)

Monday, March 19, 2012

Make SQL use more memory

I've got a Windows 2003 Enterprise server with 8GB of memory running
SQL 2005 Standard (32 bit). According to the documentation SQL can
take up to the OS maximum amount of memory (64 GB with PAE). Well,
SQL isn't taking more than about 1.5GB of memory, and the Buffer Cache
Hit Ratio is up close to 100. How can I make SQL use more physical
memory?
Thanks
DylanIf the Buffer Cache Hit Ratio is 100, that means that SQL Server doesn't
need to take any more memory. Your queries are all using data that's
already cached. SQL Server will not take memory it doesn't need.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182433233.008715.247450@.o61g2000hsh.googlegroups.com...
> I've got a Windows 2003 Enterprise server with 8GB of memory running
> SQL 2005 Standard (32 bit). According to the documentation SQL can
> take up to the OS maximum amount of memory (64 GB with PAE). Well,
> SQL isn't taking more than about 1.5GB of memory, and the Buffer Cache
> Hit Ratio is up close to 100. How can I make SQL use more physical
> memory?
> Thanks
> Dylan
>|||On Jun 21, 9:47 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> If the Buffer Cache Hit Ratio is 100, that means that SQL Server doesn't
> need to take any more memory. Your queries are all using data that's
> already cached. SQL Server will not take memory it doesn't need.
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182433233.008715.247450@.o61g2000hsh.googlegroups.com...
> > I've got a Windows 2003 Enterprise server with 8GB of memory running
> > SQL 2005 Standard (32 bit). According to the documentation SQL can
> > take up to the OS maximum amount of memory (64 GB with PAE). Well,
> > SQL isn't taking more than about 1.5GB of memory, and the Buffer Cache
> > Hit Ratio is up close to 100. How can I make SQL use more physical
> > memory?
> > Thanks
> > Dylan
Ah, so you're right - I was reading my thresholds sheet wrong. I guess
I should have thought about what a ratio is before posting.
In any event, this SQL server's not very highly utilized right now.
If it becomes more highly utilized and does in fact need to take more
memory, will it, or do I need to change anything? I was reading about
enabling AWE - is that something that I should have enabled?
Thanks!
Dylan|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182434972.723295.308160@.o61g2000hsh.googlegroups.com...
> In any event, this SQL server's not very highly utilized right now.
> If it becomes more highly utilized and does in fact need to take more
> memory, will it, or do I need to change anything? I was reading about
> enabling AWE - is that something that I should have enabled?
Yes, you'll have to enable AWE if you want SQL Server to use any RAM
over the 2GB user-mode VAS limit.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 10:16 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182434972.723295.308160@.o61g2000hsh.googlegroups.com...
>
> > In any event, this SQL server's not very highly utilized right now.
> > If it becomes more highly utilized and does in fact need to take more
> > memory, will it, or do I need to change anything? I was reading about
> > enabling AWE - is that something that I should have enabled?
> Yes, you'll have to enable AWE if you want SQL Server to use any RAM
> over the 2GB user-mode VAS limit.
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
Great, thanks - one last question. I see that you need to enable Lock
Pages in Memory access through group Policy. It says to:
In the Select Users or Groups dialog box, add an account with
privileges to run sqlservr.exe.
Is that referring to the user that the MSSQLSERVER service logs in as
when it starts, or is that just any user with login access to the
database? It seems like no matter what account (or accounts) I give
that access to, SQL still spits out an error saying that they don't
have access.|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182436089.647770.79590@.p77g2000hsh.googlegroups.com...
> Great, thanks - one last question. I see that you need to enable Lock
> Pages in Memory access through group Policy. It says to:
As far as I know, that's only applicable to 64-bit, Enterprise Edition
SQL Server.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 10:34 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182436089.647770.79590@.p77g2000hsh.googlegroups.com...
>
> > Great, thanks - one last question. I see that you need to enable Lock
> > Pages in Memory access through group Policy. It says to:
> As far as I know, that's only applicable to 64-bit, Enterprise Edition
> SQL Server.
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
Hmm, well according to this article http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
AWE is not needed and cannot be configured on 64-bit operating systems.|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
> Hmm, well according to this article
> http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
> AWE is not needed and cannot be configured on 64-bit operating systems.
AWE is needed for 32-bit SQL Server, whether running on a 32-bit or
64-bit operating system. Lock Pages in Memory is only needed for 64-bit SQL
Server. You're running 32-bit SQL Server, according to your first post?
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
>
> > Hmm, well according to this article
> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
> > AWE is not needed and cannot be configured on 64-bit operating systems.
> AWE is needed for 32-bit SQL Server, whether running on a 32-bit or
> 64-bit operating system. Lock Pages in Memory is only needed for 64-bit SQL
> Server. You're running 32-bit SQL Server, according to your first post?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
I really need to read these things better, I didn't see in your
previous post that you were talking about Enterprise Edition SQL
Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
Enterprise. Given that, I can't get AWE to enable. When I try to
enable it, I get the following error:
Alter Failed (Microsoft.SQLServer.SMO)
Additional information
+ An exception occured while executing a Transact-SQL statement or
batch.
(Microsoft.SQLServer.ConnectionInfo)
+ Address Windowing Extensions (AWE) requires the "lock pages in
memory" privilege which is not currently present in the access token
of the process.
When I looked that up, I found that I needed to enable the Enable the
Lock Pages in Memory Option. Using this article
http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspx I tried
to do that, but it is still not working. Any insight?
Thanks very much for your help.|||Do you have the /PAE switch enabled in boot.ini ?
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182440393.064379.241090@.p77g2000hsh.googlegroups.com...
> On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> wrote:
>> "dylan" <dylan.roeh...@.gmail.com> wrote in message
>> news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
>>
>> > Hmm, well according to this article
>> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
>> > AWE is not needed and cannot be configured on 64-bit operating systems.
>> AWE is needed for 32-bit SQL Server, whether running on a 32-bit or
>> 64-bit operating system. Lock Pages in Memory is only needed for 64-bit
>> SQL
>> Server. You're running 32-bit SQL Server, according to your first post?
>> --
>> Adam Machanic
>> SQL Server MVP
>> Author, "Expert SQL Server 2005
>> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> I really need to read these things better, I didn't see in your
> previous post that you were talking about Enterprise Edition SQL
> Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
> Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
> Enterprise. Given that, I can't get AWE to enable. When I try to
> enable it, I get the following error:
> Alter Failed (Microsoft.SQLServer.SMO)
> Additional information
> + An exception occured while executing a Transact-SQL statement or
> batch.
> (Microsoft.SQLServer.ConnectionInfo)
> + Address Windowing Extensions (AWE) requires the "lock pages in
> memory" privilege which is not currently present in the access token
> of the process.
> When I looked that up, I found that I needed to enable the Enable the
> Lock Pages in Memory Option. Using this article
> http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspx I tried
> to do that, but it is still not working. Any insight?
> Thanks very much for your help.
>|||On Jun 21, 11:53 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> Do you have the /PAE switch enabled in boot.ini ?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182440393.064379.241090@.p77g2000hsh.googlegroups.com...
> > On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> > wrote:
> >> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> >>news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
> >> > Hmm, well according to this article
> >> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
> >> > AWE is not needed and cannot be configured on 64-bit operating systems.
> >> AWE is needed for 32-bit SQL Server, whether running on a 32-bit or
> >> 64-bit operating system. Lock Pages in Memory is only needed for 64-bit
> >> SQL
> >> Server. You're running 32-bit SQL Server, according to your first post?
> >> --
> >> Adam Machanic
> >> SQL Server MVP
> >> Author, "Expert SQL Server 2005
> >> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> > I really need to read these things better, I didn't see in your
> > previous post that you were talking about Enterprise Edition SQL
> > Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
> > Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
> > Enterprise. Given that, I can't get AWE to enable. When I try to
> > enable it, I get the following error:
> > Alter Failed (Microsoft.SQLServer.SMO)
> > Additional information
> > + An exception occured while executing a Transact-SQL statement or
> > batch.
> > (Microsoft.SQLServer.ConnectionInfo)
> > + Address Windowing Extensions (AWE) requires the "lock pages in
> > memory" privilege which is not currently present in the access token
> > of the process.
> > When I looked that up, I found that I needed to enable the Enable the
> > Lock Pages in Memory Option. Using this article
> >http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspxI tried
> > to do that, but it is still not working. Any insight?
> > Thanks very much for your help.
Yes I do.|||Can you go into gpedit and make sure the perms are set properly, then go
back out to a command prompt, run gpupdate, and try again?
--
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182442259.823849.93310@.q75g2000hsh.googlegroups.com...
> On Jun 21, 11:53 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> wrote:
>> Do you have the /PAE switch enabled in boot.ini ?
>> --
>> Adam Machanic
>> SQL Server MVP
>> Author, "Expert SQL Server 2005
>> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
>> "dylan" <dylan.roeh...@.gmail.com> wrote in message
>> news:1182440393.064379.241090@.p77g2000hsh.googlegroups.com...
>> > On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
>> > wrote:
>> >> "dylan" <dylan.roeh...@.gmail.com> wrote in message
>> >>news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
>> >> > Hmm, well according to this article
>> >> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
>> >> > AWE is not needed and cannot be configured on 64-bit operating
>> >> > systems.
>> >> AWE is needed for 32-bit SQL Server, whether running on a 32-bit
>> >> or
>> >> 64-bit operating system. Lock Pages in Memory is only needed for
>> >> 64-bit
>> >> SQL
>> >> Server. You're running 32-bit SQL Server, according to your first
>> >> post?
>> >> --
>> >> Adam Machanic
>> >> SQL Server MVP
>> >> Author, "Expert SQL Server 2005
>> >> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
>> > I really need to read these things better, I didn't see in your
>> > previous post that you were talking about Enterprise Edition SQL
>> > Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
>> > Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
>> > Enterprise. Given that, I can't get AWE to enable. When I try to
>> > enable it, I get the following error:
>> > Alter Failed (Microsoft.SQLServer.SMO)
>> > Additional information
>> > + An exception occured while executing a Transact-SQL statement or
>> > batch.
>> > (Microsoft.SQLServer.ConnectionInfo)
>> > + Address Windowing Extensions (AWE) requires the "lock pages in
>> > memory" privilege which is not currently present in the access token
>> > of the process.
>> > When I looked that up, I found that I needed to enable the Enable the
>> > Lock Pages in Memory Option. Using this article
>> >http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspxI tried
>> > to do that, but it is still not working. Any insight?
>> > Thanks very much for your help.
> Yes I do.
>|||On Jun 21, 12:20 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> Can you go into gpedit and make sure the perms are set properly, then go
> back out to a command prompt, run gpupdate, and try again?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182442259.823849.93310@.q75g2000hsh.googlegroups.com...
> > On Jun 21, 11:53 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> > wrote:
> >> Do you have the /PAE switch enabled in boot.ini ?
> >> --
> >> Adam Machanic
> >> SQL Server MVP
> >> Author, "Expert SQL Server 2005
> >> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> >> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> >>news:1182440393.064379.241090@.p77g2000hsh.googlegroups.com...
> >> > On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> >> > wrote:
> >> >> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> >> >>news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
> >> >> > Hmm, well according to this article
> >> >> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
> >> >> > AWE is not needed and cannot be configured on 64-bit operating
> >> >> > systems.
> >> >> AWE is needed for 32-bit SQL Server, whether running on a 32-bit
> >> >> or
> >> >> 64-bit operating system. Lock Pages in Memory is only needed for
> >> >> 64-bit
> >> >> SQL
> >> >> Server. You're running 32-bit SQL Server, according to your first
> >> >> post?
> >> >> --
> >> >> Adam Machanic
> >> >> SQL Server MVP
> >> >> Author, "Expert SQL Server 2005
> >> >> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> >> > I really need to read these things better, I didn't see in your
> >> > previous post that you were talking about Enterprise Edition SQL
> >> > Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
> >> > Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
> >> > Enterprise. Given that, I can't get AWE to enable. When I try to
> >> > enable it, I get the following error:
> >> > Alter Failed (Microsoft.SQLServer.SMO)
> >> > Additional information
> >> > + An exception occured while executing a Transact-SQL statement or
> >> > batch.
> >> > (Microsoft.SQLServer.ConnectionInfo)
> >> > + Address Windowing Extensions (AWE) requires the "lock pages in
> >> > memory" privilege which is not currently present in the access token
> >> > of the process.
> >> > When I looked that up, I found that I needed to enable the Enable the
> >> > Lock Pages in Memory Option. Using this article
> >> >http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspxItried
> >> > to do that, but it is still not working. Any insight?
> >> > Thanks very much for your help.
> > Yes I do.
Well, that's part of the issue, I guess. It says "add an account with
privileges to run sqlservr.exe" - by that does it mean to add the
account that the MSSQLSERVER service starts as, or an account that has
sysadmin rights to the SQL Server?|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182446047.108936.106190@.w5g2000hsg.googlegroups.com...
> Well, that's part of the issue, I guess. It says "add an account with
> privileges to run sqlservr.exe" - by that does it mean to add the
> account that the MSSQLSERVER service starts as, or an account that has
> sysadmin rights to the SQL Server?
The account that the SQL Server service runs as.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 1:17 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182446047.108936.106190@.w5g2000hsg.googlegroups.com...
>
> > Well, that's part of the issue, I guess. It says "add an account with
> > privileges to run sqlservr.exe" - by that does it mean to add the
> > account that the MSSQLSERVER service starts as, or an account that has
> > sysadmin rights to the SQL Server?
> The account that the SQL Server service runs as.
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
I still get the same error. Does it matter that I'm not logging into
the SQL Server as the account that starts the service?|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182447460.877712.292450@.n60g2000hse.googlegroups.com...
> I still get the same error. Does it matter that I'm not logging into
> the SQL Server as the account that starts the service?
No, that shouldn't be an issue. Did you try rebooting? I'm wondering
if the policy isn't being applied for some reason.
Also, if you look in Task Manager, is SQL Server running using the
account that you think it should be using?
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 2:12 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182447460.877712.292450@.n60g2000hse.googlegroups.com...
>
> > I still get the same error. Does it matter that I'm not logging into
> > the SQL Server as the account that starts the service?
> No, that shouldn't be an issue. Did you try rebooting? I'm wondering
> if the policy isn't being applied for some reason.
> Also, if you look in Task Manager, is SQL Server running using the
> account that you think it should be using?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 2:12 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182447460.877712.292450@.n60g2000hse.googlegroups.com...
>
> > I still get the same error. Does it matter that I'm not logging into
> > the SQL Server as the account that starts the service?
> No, that shouldn't be an issue. Did you try rebooting? I'm wondering
> if the policy isn't being applied for some reason.
> Also, if you look in Task Manager, is SQL Server running using the
> account that you think it should be using?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
I haven't tried rebooting, I can't do that until tonight, but I will
try that. I know sometimes Computer Configuration policies do not
take effect until after rebooting. I checked, and the SQL Server is
running as the account I expect it to be.|||Do you see "Address Windowing Extensions enabled" in the message log?
> I haven't tried rebooting, I can't do that until tonight, but I will
> try that. I know sometimes Computer Configuration policies do not
> take effect until after rebooting. I checked, and the SQL Server is
> running as the account I expect it to be.
>

Friday, March 9, 2012

Major Problem - Please Help!

Hello Experts,
When I Open Enterprise Manager and click on my server,
following msg appears:
SQL Server Enterprise Manager
----
A Connection could not be established to (LOCAL).
Reason: Data source name not found and no default driver
specified.
Please Verify SQL Server is running...bla, bla.
----
What Cause this:
1) I have Windows XP with SP1
2) I have had installed MDAC 2.8
3) I uninstall MDAC 2.8 using "dasetup.exe /U" command,
setup found that MDAC 2.6 is referenced to MDAC 2.8 an
had to uninstall this too, I pressed OK and then the
uninstall failed because the default of Windows XP MDAC
is 2.7 SP1 and setup propably found that some other MDAC
had to be the default.
Any way, I ReInstall MDAC 2.6 (Latest), MDAC 2.7 Refresh
and Q323263_MDAC27_x86_en.
What do I have to do having as default "MDAC 2.7 SP1 on
Windows XP SP1" and ODBC Drivers work?
Thanks in advance.Make sure shared memory is enabled in client network utility (start | run |
cliconfg)
Try editing the registration property for the server in Enterprise Manager
(Right click on the server and choose to edit registration properties). Use
the server name instead of (local)
Thanks
Deepali
This posting is provided "AS IS" with no warranties, and confers no rights|||Thanks for your response, I will do that and let you know.

>--Original Message--
>Make sure shared memory is enabled in client network
utility (start | run |
>cliconfg)
>Try editing the registration property for the server in
Enterprise Manager
>(Right click on the server and choose to edit
registration properties). Use
>the server name instead of (local)
>Thanks
>Deepali
>This posting is provided "AS IS" with no warranties, and
confers no rights
>.
>

Wednesday, March 7, 2012

Maintenance-Backup question

Hi,
I have a SQl database, when I view its properties from Enterprise manager, it's size is about 159 MB. I'm pretty sure I don't have that mucj data in it.

I know that Transaction log files grow over time, but I don't know how to control its growth? Is there a way to delete logs that are unecessary?
By backing up Transaction logs in a maintenance plan will the problem be solved?if you don't need transaction logs change your recovery model to Simple, that way the transaction logs will be truncated at checkpoint.

If you need transaction logs then you should be backing them up on a regular basis. After backup you can truncate the logs. See BOL and maintenance plan for syntax.|||Thanks for your reply,
I have read BOL's explanation of backups and recovery models, however I'm still confused.

I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
I have a maintenance plan that backs up both Log files and the database on a daily bases.

My confusion is mainly on the issue of Full versus other types of recovery models.

I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:

"I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."

Is this strategy correct?

Thanks|||Ah no.

Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.

My suggestion is to create a schedule of full backups and transaction log backups.

All transaction logs can be deleted AFTER a full backup is again taken.

Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.

You can specify al this in the maintance plan.

Think about this and poste back.|||Also, lookup "differential backups" in BOL, another good example of a backup strategy.|||A log dump is what I wanted to do, how do I specify a log dump?
Is a log dump another term for backing up transactions logs?

Originally posted by Paul Young
Ah no.

Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.

My suggestion is to create a schedule of full backups and transaction log backups.

All transaction logs can be deleted AFTER a full backup is again taken.

Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.

You can specify al this in the maintance plan.

Think about this and poste back.|||After backing up, use dbcc shrink file to shrink log file. Since DBCC shrinkfile only shrink from the end of the log file, sometime you have to intentionally do some operations to move the log to the start of the log file. Normally, after two times of backup and dbcc shrink, the log file will be shrinked dramatiscally.

Search google for Andrew Zanevsky, he wrote a good article and script of how to autamatically do the above steps.

Here's header of this script.
use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
/*
************************************************** ***********
Name: sp_force_shrink_log
Description:
Shrink transaction log of the current database in SQL Server 7.0.
Switch context to proper db to execute.
Usage: exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>
exec pubs..sp_force_shrink_log

Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
zanevsky@.azdatabases.com|||Originally posted by Sia
A log dump is what I wanted to do, how do I specify a log dump?
Is a log dump another term for backing up transactions logs?

Yes, refer to BOL for Tlog backups.|||Sia, finally found the article in BOL... look up "transaction logs, truncating" This should help you out.

Yes, log dump = backing up transaction log.

You will need to follow jzhu suggestion on shrinking the log files if yo uhave auto shrink turned off. IMNotSoHO I would wathc the log files and only shrink is space were a problem. If you consistantly need 1 gig of log space why go through the cycle of shrinking and growing? I shrink my logs only once a month.|||Thanks for your replay Paul.
I think I got it!|||Paul,
Thanks for your recent replys.

I have scheduled a Database and Log backup every day at 1 and 2 am.

The thing is that, before I do the Log backup my database had the following properties:
Space alloacted for data file was 50MB
Space alloacted for Log file was about 100 MB
availble space was about 70MB.

The I did a manual database and Log backup which resulted in the following db properties

Space alloacted for data file =50MB
Space alloacted for Log file =50MB
availble space= 100MB

Its now about 2 days that I'm doing the Log and Data backup using a scheduled maintenance plan.
I have the following DB Properties:

Space alloacted for data file =50MB
Space alloacted for Log file =2 MB
availble space= 14MB

=>My questions:

Why did the Log File size reduced dramatically after 2 backups with the maintenance plan but was not reduced that much when I did the first 2 backups manually.

Why is the "availble space" reduced!
My guess is that the availble space is calculated using the allocated space and since I have autogrowth it woud increase itself when the file reaches the limit.

I would appreciate if you let me know I'm on the right track or not.

Thanks

Originally posted by Sia
Thanks for your reply,
I have read BOL's explanation of backups and recovery models, however I'm still confused.

I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
I have a maintenance plan that backs up both Log files and the database on a daily bases.

My confusion is mainly on the issue of Full versus other types of recovery models.

I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:

"I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."

Is this strategy correct?

Thanks|||Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.

As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.

Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while|||Thank you for your help!

Originally posted by Paul Young
Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.

As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.

Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while

Saturday, February 25, 2012

Maintenance Plans and Integrity checks

Just inherited a couple os SQL V7 servers and am reviewing the backups they
have been doing. New to SQL server.
In Enterprise Manager in the plan window I see two intregrity options
1) check database integrity (which is not checked)
2) verify the intergrity of the backup upon completion (checked)
What is number 2 doing?
If #1 is not checked, it seems to me their is no DBCC being run. So far I
cannot get their ops dept to send me the job logs for these backups so at
this point want to make sure from what I can see in Enterprise Manager that
they are running consistancy checks.
Thanks
Mike Lackey
Database Adminstration
Sybase/Oracle and enthusiastically trying to learn MSSQL
If #1 is not checked and there are no other jobs doing this that means there
are no DBCC CHECKDB's being run.
#2 refers to issuing a Verify against the backup after it completes and does
not pertain to DBCC's.
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:15060A70-AAE2-43D4-86C5-CCC0B08483A8@.microsoft.com...
> Just inherited a couple os SQL V7 servers and am reviewing the backups
they
> have been doing. New to SQL server.
> In Enterprise Manager in the plan window I see two intregrity options
> 1) check database integrity (which is not checked)
> 2) verify the intergrity of the backup upon completion (checked)
> What is number 2 doing?
> If #1 is not checked, it seems to me their is no DBCC being run. So far I
> cannot get their ops dept to send me the job logs for these backups so at
> this point want to make sure from what I can see in Enterprise Manager
that
> they are running consistancy checks.
> Thanks
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and enthusiastically trying to learn MSSQL

Maintenance Plans and Integrity checks

Just inherited a couple os SQL V7 servers and am reviewing the backups they
have been doing. New to SQL server.
In Enterprise Manager in the plan window I see two intregrity options
1) check database integrity (which is not checked)
2) verify the intergrity of the backup upon completion (checked)
What is number 2 doing?
If #1 is not checked, it seems to me their is no DBCC being run. So far I
cannot get their ops dept to send me the job logs for these backups so at
this point want to make sure from what I can see in Enterprise Manager that
they are running consistancy checks.
Thanks
--
Mike Lackey
Database Adminstration
Sybase/Oracle and enthusiastically trying to learn MSSQLIf #1 is not checked and there are no other jobs doing this that means there
are no DBCC CHECKDB's being run.
#2 refers to issuing a Verify against the backup after it completes and does
not pertain to DBCC's.
--
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:15060A70-AAE2-43D4-86C5-CCC0B08483A8@.microsoft.com...
> Just inherited a couple os SQL V7 servers and am reviewing the backups
they
> have been doing. New to SQL server.
> In Enterprise Manager in the plan window I see two intregrity options
> 1) check database integrity (which is not checked)
> 2) verify the intergrity of the backup upon completion (checked)
> What is number 2 doing?
> If #1 is not checked, it seems to me their is no DBCC being run. So far I
> cannot get their ops dept to send me the job logs for these backups so at
> this point want to make sure from what I can see in Enterprise Manager
that
> they are running consistancy checks.
> Thanks
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and enthusiastically trying to learn MSSQL

Maintenance Plans - Restrict users from deleting or editing

Hi,
Is it possible to restrict users that use Enterprise Manager from deleting
maintenance plans? Could anyone please point me to a good URL that talks
about this.
thanks a lot,1) Try placing strict security on the msdb tables related to jobs/plans?
2) Not sure if you can do this but possibly triggers on same tables to check
login and rollback if appropriate.
TheSQLGuru
President
Indicium Resources, Inc.
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:60A6EF35-0306-428E-993B-04A76B9484B8@.microsoft.com...
> Hi,
> Is it possible to restrict users that use Enterprise Manager from deleting
> maintenance plans? Could anyone please point me to a good URL that talks
> about this.
>
> thanks a lot,
>

Maintenance Plans - Restrict users from deleting or editing

Hi,
Is it possible to restrict users that use Enterprise Manager from deleting
maintenance plans? Could anyone please point me to a good URL that talks
about this.
thanks a lot,
1) Try placing strict security on the msdb tables related to jobs/plans?
2) Not sure if you can do this but possibly triggers on same tables to check
login and rollback if appropriate.
TheSQLGuru
President
Indicium Resources, Inc.
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:60A6EF35-0306-428E-993B-04A76B9484B8@.microsoft.com...
> Hi,
> Is it possible to restrict users that use Enterprise Manager from deleting
> maintenance plans? Could anyone please point me to a good URL that talks
> about this.
>
> thanks a lot,
>

Maintenance Plans - Restrict users from deleting or editing

Hi,
Is it possible to restrict users that use Enterprise Manager from deleting
maintenance plans? Could anyone please point me to a good URL that talks
about this.
thanks a lot,1) Try placing strict security on the msdb tables related to jobs/plans?
2) Not sure if you can do this but possibly triggers on same tables to check
login and rollback if appropriate.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:60A6EF35-0306-428E-993B-04A76B9484B8@.microsoft.com...
> Hi,
> Is it possible to restrict users that use Enterprise Manager from deleting
> maintenance plans? Could anyone please point me to a good URL that talks
> about this.
>
> thanks a lot,
>

Maintenance plans

We have Veritas' Backupexec running in our Enterprise and the Veritas Install actually installs MS SQL Server MSDN on each Server in the Enterprise.

It looks like it also sets up a default Maintenance plan within each of the MSDN Instances.

I guess my question is.. Can I manage the Maintenance Plans on these MSDN Instances via the SQL Server EM GUI from my desktop?? Seems like when I look at the Maintenance plans alot of the options are greyed out or not available. What I am trying to do is modify one of the maintenance plans to have the backups deleted after one week (One of the Instances has been running a complete backup on the Backupexec Databases for a year and there are a years worth of backups on the Server) but the option to "remove files older than" is 'greyed out' ??The main reason I've seen options greyed out is insufficient privleges.|||I logged into my local MSDN Instance as 'sa' and created a new maintenance plan via EM on my desktop.

I can create the plan, but the options to "remove files older than" is still missing. This is so puzzling!!!!!??|||Have you checked with Veritas? My guess is that Backupexec may have permanently disabled those items.|||I thought Veritas might be the culprit also, So I installed MSDE locally on my machine and I get the same results. In a new maintenance plan, on the 'specify backup disk directory' screen, I put a check in the 'remove files older than' box and the dropdrop that should read 'hours, days, weeks, months' is blank and willl not populate. Is this something that is particular to MSDE???|||There's a reason it is called MSDE and not MS SQL Server 2000. MSDE is intended for disconnected applications and/or development. It is not intended as a way to avoid paying for what you need.

You need to read up on limitations in MSDE. Here's one article that may be interpreted as a keen marketing ploy. (http://www.teratrax.com/articles/msde_vs_sql_server.html)|||Thank you for the link to the article!!!!!!!!!! basically, i need to go back to veritas then.

Monday, February 20, 2012

Maintenance plan, reindexing

In the Enterprise Manager of SQL Server 2000 I have set up a maintenance plan which rebuilds my indexes. I've stuided the documentation, and from what I've learned what happens behind the curtain is that several DBCC REINDEX commands are being issued.
Question:
If I have 20 tables and 40 indexes: Will SQL Server do the maintance plan in 1 single transaction, or will it divide the it up to eg. 20 or 40 transactions?

-hIf your SQL Server Agent is installed with a Service Account,create Jobs to run the Reindex DBCC on the large tables and usethe Maintaince Plans for minor problems. It is also a good ideato document the maintaince plans. I think if the reindex includesrepair SQL Server must be in single user mode. One of the bestbooks on SQL Server indexes is by Ken England he quantifies theIAM(Index Allocation Mapping) pages. Hope this helps.
Kind regards,
Gift Peddie

Friday, March 30, 2012

Manage server messages shows % instead of name of item in error

Manage server messages shows % instead of name of item in error

Manage server messages shows % instead of name of item in error

Wednesday, March 28, 2012

Manage MSDE from SQL Server

Manage MSDE from SQL Server

Making SSAS Developer Edition act like standard edition

Monday, March 19, 2012

Make SQL use more memory

Make SQL use more memory

Make SQL use more memory

Friday, March 9, 2012

Major Problem - Please Help!

Wednesday, March 7, 2012

Maintenance-Backup question

Saturday, February 25, 2012

Maintenance Plans and Integrity checks

Maintenance Plans and Integrity checks

Maintenance Plans - Restrict users from deleting or editing

Maintenance Plans - Restrict users from deleting or editing

Maintenance Plans - Restrict users from deleting or editing

Maintenance plans

Monday, February 20, 2012

Maintenance plan, reindexing

maintenance plan wizard

Blog Archive

About Me