making a log file smaller

I have a log file that is pretty big (700 MB). The mdf
file is at 200 MB. How can I make the log file smaller
without destroying the integrity of the database?
Thanks for any help.If you do not require transactional recovery, for the short term do a
"backup log dbname with truncate_only"
for the long term set the recovery mode to simple.
If you do require transactional recovery, schedule timely log backups.
Once you backup the log or truncate it, work with dbcc shrinkfile to shrink
the footprint, see BOL for syntax. You shouldn't try to keep the log as lean
as possible as if it has to reallocate space to grow during the production
day that is a performance hit. Lots more info on transactional logs in BOL.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"dre" <anonymous@.discussions.microsoft.com> wrote in message
news:027201c394d0$f4e802a0$a101280a@.phx.gbl...
> I have a log file that is pretty big (700 MB). The mdf
> file is at 200 MB. How can I make the log file smaller
> without destroying the integrity of the database?
> Thanks for any help.

Maintenence Plan Questions

I wanted to know at what time should I be scheduling the data optimization and data integrity jobs? Will these jobs hinder performance? If so then I should probably schedule the jobs after work hours.

Also, we were not going to use the backup/recovery jobs that sql server offer, we have our own backup software. So, will the data integrity or data optimization job affect backups? Should I perform these jobs before or after backups?

Thanks,
LauraThat is a good plan. The optimizations are NORMALLY non-intrusive, but particularly the first time they are run after significant changes they can hit the database like a freight train.

Your plan to start the optimizations after the backup finishes is a good one.

-PatP|||I would recommend not to use 3rd-party backup tools (unless it's SQLLiteSpeed) to backup live database/transaction log. Use native or purchase SQLLiteSpeed to create OS backup files, and only then use other tools to backup to a tape, for example.|||Ok thanks for the responses, it helped me alot

-laura

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

I created a SQL Server Maintenance Plan that performs sql
backups, optimizations and integrity checks through a
maintenance plan.
The backup portion of the maintenance plan works okay.
But the optimization and integrity checks fail with the
following errors:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
We are running SQL 2000 Standard Edition SP3.
I saw the Microsoft Knowledge Base Article - 301292 which
seems to be the problem. I tried setting the sp_options
mentioned in the article at the database level but the
problem still occurred. I really want to use a generic
maintenance plan, I don't want to have to write jobs for
each database I need to perform optimization or integrity
checks on.
As a side note the database that is reporting the errors
is part of a sharepoint install.
Any help would be great.
Thanks.
Gary,
We ran into this also, but the Maintenance Plan will not issue the proper
SET as the note describes. The only way that I know to do it is to go ahead
and write your own script.
Sorry,
Russell Fields
"Gary Murphy" <anonymous@.discussions.microsoft.com> wrote in message
news:ec0c01c43d0e$6512bda0$a101280a@.phx.gbl...
> I created a SQL Server Maintenance Plan that performs sql
> backups, optimizations and integrity checks through a
> maintenance plan.
> The backup portion of the maintenance plan works okay.
> But the optimization and integrity checks fail with the
> following errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
> because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> We are running SQL 2000 Standard Edition SP3.
> I saw the Microsoft Knowledge Base Article - 301292 which
> seems to be the problem. I tried setting the sp_options
> mentioned in the article at the database level but the
> problem still occurred. I really want to use a generic
> maintenance plan, I don't want to have to write jobs for
> each database I need to perform optimization or integrity
> checks on.
> As a side note the database that is reporting the errors
> is part of a sharepoint install.
> Any help would be great.
> Thanks.

Maintenance Plans

I created a SQL Server Maintenance Plan that performs sql
backups, optimizations and integrity checks through a
maintenance plan.
The backup portion of the maintenance plan works okay.
But the optimization and integrity checks fail with the
following errors:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
We are running SQL 2000 Standard Edition SP3.
I saw the Microsoft Knowledge Base Article - 301292 which
seems to be the problem. I tried setting the sp_options
mentioned in the article at the database level but the
problem still occurred. I really want to use a generic
maintenance plan, I don't want to have to write jobs for
each database I need to perform optimization or integrity
checks on.
As a side note the database that is reporting the errors
is part of a sharepoint install.
Any help would be great.
Thanks.Gary,
We ran into this also, but the Maintenance Plan will not issue the proper
SET as the note describes. The only way that I know to do it is to go ahead
and write your own script.
Sorry,
Russell Fields
"Gary Murphy" <anonymous@.discussions.microsoft.com> wrote in message
news:ec0c01c43d0e$6512bda0$a101280a@.phx.gbl...
> I created a SQL Server Maintenance Plan that performs sql
> backups, optimizations and integrity checks through a
> maintenance plan.
> The backup portion of the maintenance plan works okay.
> But the optimization and integrity checks fail with the
> following errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
> because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> We are running SQL 2000 Standard Edition SP3.
> I saw the Microsoft Knowledge Base Article - 301292 which
> seems to be the problem. I tried setting the sp_options
> mentioned in the article at the database level but the
> problem still occurred. I really want to use a generic
> maintenance plan, I don't want to have to write jobs for
> each database I need to perform optimization or integrity
> checks on.
> As a side note the database that is reporting the errors
> is part of a sharepoint install.
> Any help would be great.
> Thanks.

Maintenance Plans

I created a SQL Server Maintenance Plan that performs sql
backups, optimizations and integrity checks through a
maintenance plan.
The backup portion of the maintenance plan works okay.
But the optimization and integrity checks fail with the
following errors:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
We are running SQL 2000 Standard Edition SP3.
I saw the Microsoft Knowledge Base Article - 301292 which
seems to be the problem. I tried setting the sp_options
mentioned in the article at the database level but the
problem still occurred. I really want to use a generic
maintenance plan, I don't want to have to write jobs for
each database I need to perform optimization or integrity
checks on.
As a side note the database that is reporting the errors
is part of a sharepoint install.
Any help would be great.
Thanks.Gary,
We ran into this also, but the Maintenance Plan will not issue the proper
SET as the note describes. The only way that I know to do it is to go ahead
and write your own script.
Sorry,
Russell Fields
"Gary Murphy" <anonymous@.discussions.microsoft.com> wrote in message
news:ec0c01c43d0e$6512bda0$a101280a@.phx.gbl...
> I created a SQL Server Maintenance Plan that performs sql
> backups, optimizations and integrity checks through a
> maintenance plan.
> The backup portion of the maintenance plan works okay.
> But the optimization and integrity checks fail with the
> following errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
> because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> We are running SQL 2000 Standard Edition SP3.
> I saw the Microsoft Knowledge Base Article - 301292 which
> seems to be the problem. I tried setting the sp_options
> mentioned in the article at the database level but the
> problem still occurred. I really want to use a generic
> maintenance plan, I don't want to have to write jobs for
> each database I need to perform optimization or integrity
> checks on.
> As a side note the database that is reporting the errors
> is part of a sharepoint install.
> Any help would be great.
> Thanks.

maintenance plan wizard - security question, possible bug?

Hi All,
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way to
specify that (concering that I get IS package, very hard to modify anything)?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:
> Pedja
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000).
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed the
> password
> In other words , your SQL Agent runs under the same account as you logged ,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> > Hi All,
> > I am trying to create maintenance plan for updating statistics, checking
> > database integrity and database backup, using wizard. First of all, I
> > don't
> > see any option for how long do I want to keep backup files on the system
> > (like we had on 2000). I find it very inconvinient. Is there any other way
> > to
> > specify that (concering that I get IS package, very hard to modify
> > anything)?
> > More important question is regarding security issue with this wizard: I
> > have
> > only 'sa' access to the server (not member of local admin group, and I'm
> > not
> > able to use windows authentication). Each month we change 'sa' password on
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> > am I
> > missing something?
> > Thanks in advance,
> > Pedja
> >
> > P.S. The same thing would happen even if I created maintenance plan
> > manually, because I would have to create connection objects manually (and
> > specify 'sa' again)...
>
>

maintenance plan wizard - security question, possible bug?

Hi All,
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way t
o
specify that (concering that I get IS package, very hard to modify anything)
?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am
I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:

> Pedja
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed t
he
> password
> In other words , your SQL Agent runs under the same account as you logged
,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
>
>

Maintenance Plan Tasks In SSIS(Sql 2005)

I am finding difficult to set Expression Property "SelectedDatabases" in Check Databases Integrity Task in SSIS.

I keep getting error:

TITLE: Expression Builder

Expression cannot be evaluated.

ADDITIONAL INFORMATION:

The data type of variable "User::varDataset" is not supported in an expression.

Reading the variable "User::varDataset" failed with error code 0xC00470D0.

(Microsoft.DataTransformationServices.Controls)

I need the flexibilty to be able to control the Task behaviour during runtime through Variables.

Any suggestions .... or even a different approach will be helpful..

cheers

aigbor

Hi Aigbor

I encountered a similar problem when using the Shrink Database Task and decided to use Execute SQL Task with an expression instead.

You might find the execute sql task with the DBCC CHECKDB command to be more useful.

Maintenance Plan Tasks In SSIS(Sql 2005)

I am finding difficult to set Expression Property "SelectedDatabases" in Check Databases Integrity Task in SSIS.

I keep getting error:

TITLE: Expression Builder

Expression cannot be evaluated.

ADDITIONAL INFORMATION:

The data type of variable "User::varDataset" is not supported in an expression.

Reading the variable "User::varDataset" failed with error code 0xC00470D0.

(Microsoft.DataTransformationServices.Controls)

I need the flexibilty to be able to control the Task behaviour during runtime through Variables.

Any suggestions .... or even a different approach will be helpful..

cheers

aigbor

Hi Aigbor

I encountered a similar problem when using the Shrink Database Task and decided to use Execute SQL Task with an expression instead.

You might find the execute sql task with the DBCC CHECKDB command to be more useful.