Clear log after backup

Hello everybody,
I set up a backup routine to one of my SQL-Server databases.
It´s a full backup, and I don´t know why, but the log file it´s not cleared
after the backup. I thought the full backup, when executed, would clear the
database log. Is there something missing ?
EvandroFull backups do not truncate the log. You need to issue a log backup for
that or set the recovery mode to SIMPLE.
--
Andrew J. Kelly
SQL Server MVP
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:uGI4TGn9DHA.2640@.TK2MSFTNGP10.phx.gbl...
> Hello everybody,
> I set up a backup routine to one of my SQL-Server databases.
> It´s a full backup, and I don´t know why, but the log file it´s not
cleared
> after the backup. I thought the full backup, when executed, would clear
the
> database log. Is there something missing ?
>
> Evandro
>

Clear log after backup

Hello everybody,
I set up a backup routine to one of my SQL-Server databases.
Its a full backup, and I dont know why, but the log file its not cleared
after the backup. I thought the full backup, when executed, would clear the
database log. Is there something missing ?
EvandroFull backups do not truncate the log. You need to issue a log backup for
that or set the recovery mode to SIMPLE.
Andrew J. Kelly
SQL Server MVP
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:uGI4TGn9DHA.2640@.TK2MSFTNGP10.phx.gbl...
> Hello everybody,
> I set up a backup routine to one of my SQL-Server databases.
> Its a full backup, and I dont know why, but the log file its not
cleared
> after the backup. I thought the full backup, when executed, would clear
the
> database log. Is there something missing ?
>
> Evandro
>

Checking performance problem

Hello everybody,
I am experiencing some performance problems and i need to check what
database is creating a large work on my server, whats analisys parameter i'd
add to performance monitor to check this.
Atenciosamente
Leandro Loureiro dos SantosSql Profiler -- You can run a trace on your server, and include the fields (TextData, HostName, Duration, DatabaseID, DatabaseName, CPU, Reads, Writes) Then run it for events TSQL:StmtComplete and/or Stored Procs-SP:Completed. I like to set the trade to log to a sql server table, then you can query the avg cpu, reads, and writes by database id... You can also use TextData to find out what procedures are the most resource intensive.
Or, a simpler, but less exact method, is to run sp_who2... This will show you connections, what database it is connected to, and accumlative cpu time, reads and writes.|||Stored Procs-SP:Completed does not report CPU or Reads,
you will need Stored Procs-RPC:Completed for that
>--Original Message--
>Sql Profiler -- You can run a trace on your server, and
include the fields (TextData, HostName, Duration,
DatabaseID, DatabaseName, CPU, Reads, Writes) Then run it
for events TSQL:StmtComplete and/or Stored Procs-
SP:Completed. I like to set the trade to log to a sql
server table, then you can query the avg cpu, reads, and
writes by database id... You can also use TextData to find
out what procedures are the most resource intensive.
>Or, a simpler, but less exact method, is to run
sp_who2... This will show you connections, what database
it is connected to, and accumlative cpu time, reads and
writes.
>.
>|||If i run this trace on the Host(SQLSERVER) this will impact the server
performance?
Would be better to execute in a client machine?
Thanks
"joe chang" <anonymous@.discussions.microsoft.com> escreveu na mensagem
news:084501c3ad24$24b9f360$a501280a@.phx.gbl...
> Stored Procs-SP:Completed does not report CPU or Reads,
> you will need Stored Procs-RPC:Completed for that
> >--Original Message--
> >Sql Profiler -- You can run a trace on your server, and
> include the fields (TextData, HostName, Duration,
> DatabaseID, DatabaseName, CPU, Reads, Writes) Then run it
> for events TSQL:StmtComplete and/or Stored Procs-
> SP:Completed. I like to set the trade to log to a sql
> server table, then you can query the avg cpu, reads, and
> writes by database id... You can also use TextData to find
> out what procedures are the most resource intensive.
> >
> >Or, a simpler, but less exact method, is to run
> sp_who2... This will show you connections, what database
> it is connected to, and accumlative cpu time, reads and
> writes.
> >.
> >|||the most critical item to avoid adverse performance impact
is:
DO NOT save the profiler trace to a table on the
production server
my preference is to save to a file, doesn't matter too
much where, but probably not the server log disk
the reason for this is that single row inserts to a
database is a cpu intensive task, i believe profiler may
be issuing each row separately,
when saving to a file, i believe profiler buffers 64k,
before writing to disk
also, don't too many events to trace, start with the one
recommended below. if the captures exceed 100-200
events/sec, you might think about filtering out the less
interesting items
>--Original Message--
>If i run this trace on the Host(SQLSERVER) this will
impact the server
>performance?
>Would be better to execute in a client machine?
>Thanks
>"joe chang" <anonymous@.discussions.microsoft.com>
escreveu na mensagem
>news:084501c3ad24$24b9f360$a501280a@.phx.gbl...
>> Stored Procs-SP:Completed does not report CPU or Reads,
>> you will need Stored Procs-RPC:Completed for that
>> >--Original Message--
>> >Sql Profiler -- You can run a trace on your server, and
>> include the fields (TextData, HostName, Duration,
>> DatabaseID, DatabaseName, CPU, Reads, Writes) Then run
it
>> for events TSQL:StmtComplete and/or Stored Procs-
>> SP:Completed. I like to set the trade to log to a sql
>> server table, then you can query the avg cpu, reads, and
>> writes by database id... You can also use TextData to
find
>> out what procedures are the most resource intensive.
>> >
>> >Or, a simpler, but less exact method, is to run
>> sp_who2... This will show you connections, what database
>> it is connected to, and accumlative cpu time, reads and
>> writes.
>> >.
>> >
>
>.
>|||On Mon, 17 Nov 2003 15:22:02 -0300, "Leandro Loureiro dos Santos"
<leandro@.email.com> wrote:
>If i run this trace on the Host(SQLSERVER) this will impact the server
>performance?
Yes, there is a small impact.
Of coure, this hurts the most when you already have a performance
problem!
>Would be better to execute in a client machine?
Of course.
But as Joe says, you can run the trace on the host, and save the
results to a database or file on another machine, to split the impact.
J.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.