Running SQL Server 2000 Enterprise Edition SP3. The database is also
used by Microsoft Project Server 2002 and also has OLAP views, so the
database is being used to view/run cubes in the Analysis Manager.

What is the best way of shrinking the database size and its log file too?

Is there an automatic way to do this with a maintenance plan or i have
to manually run a SQL statement periodically?

What are the best practices?

I have a database that the size of it is 260megs and now the log file is
over 800megs...
Everytime i run the following:

BACKUP LOG DBName WITH TRUNCATE ONLY
DBCC SHRINKDATABASE (DBName, 10, TRUNCATEONLY)

When running it, i get the following 2 records returned:

DbId FieldId CurrentSize MinimumSize UsedPages EstimatedPages
7 1 32304 128 28152
28152
7 2 160 128 160
128

I don't know what the above 2 records mean. I am also concerned if i should
be running that statement or not.

Also concerned as to why the Windows Server 2003 (Enterprise Edition)
always creates a CRITICAL error in the Application event viewer with EventID
number 17055, source being MSSQLSERVER and the description of the event:

18278:
Database log truncated: Database: DBName.


If it's a critical error message, then what i am doing is bad? Am i damaging
my database?


Within like 7-10 days of running the above statement, the log file becomes
close
to 1 GB again! How can i find out what causes the log file to grow that big?
Is it being
caused by running some OLAP views or what? The db has about 20 users
connected to it
using an ASP web aplication as the front-end, and MSP Professional 2002 also
to connect
to the Project Server 2002 (which uses the same database as the above
DBName).


I would appreciate any help.

Thank you very much

"serge" <sergea@nospam.ehmail.com> wrote in message
news:UTiZc.17326$CG3.1077546@news20.bellglobal.com ...
> Running SQL Server 2000 Enterprise Edition SP3. The database is also
> used by Microsoft Project Server 2002 and also has OLAP views, so the
> database is being used to view/run cubes in the Analysis Manager.
>
> What is the best way of shrinking the database size and its log file too?
>
> Is there an automatic way to do this with a maintenance plan or i have
> to manually run a SQL statement periodically?
>
> What are the best practices?
>
> I have a database that the size of it is 260megs and now the log file is
> over 800megs...
> Everytime i run the following:
>
> BACKUP LOG DBName WITH TRUNCATE ONLY
> DBCC SHRINKDATABASE (DBName, 10, TRUNCATEONLY)
>
> When running it, i get the following 2 records returned:
>
> DbId FieldId CurrentSize MinimumSize UsedPages EstimatedPages
> 7 1 32304 128 28152
> 28152
> 7 2 160 128 160
> 128
>
> I don't know what the above 2 records mean. I am also concerned if i
> should
> be running that statement or not.
>
> Also concerned as to why the Windows Server 2003 (Enterprise Edition)
> always creates a CRITICAL error in the Application event viewer with
> EventID
> number 17055, source being MSSQLSERVER and the description of the event:
>
> 18278:
> Database log truncated: Database: DBName.
>
>
> If it's a critical error message, then what i am doing is bad? Am i
> damaging
> my database?
>
>
> Within like 7-10 days of running the above statement, the log file becomes
> close
> to 1 GB again! How can i find out what causes the log file to grow that
> big?
> Is it being
> caused by running some OLAP views or what? The db has about 20 users
> connected to it
> using an ASP web aplication as the front-end, and MSP Professional 2002
> also
> to connect
> to the Project Server 2002 (which uses the same database as the above
> DBName).
>
>
> I would appreciate any help.
>
> Thank you very much
>
>

It sounds as if your database is in full recovery - all transactions are
logged, and the transaction log is not truncated unless you back it up; this
allows recovery to a specific point in time. Since your database is very
small, you might want to change it to simple recovery, where the log is
automatically truncated and the space reused. This means the only recovery
option you have is to restore your last full backup, so you would lose any
modifications since then. If you do need point in time recovery, then you
would need to schedule transaction log backups - backing up the log
truncates it.

The reason for the critical error (I assume) is that if the database is in
full recovery and you truncate the log, it is no longer possible to restore
any backups - you need to make a full backup immediately.

This KB article might also be helpful:

http://support.microsoft.com/default...&Product=sql2k

Simon

I will check the simple/full recovery thing you are talking about.

Thank you Simon

> It sounds as if your database is in full recovery - all transactions are
> logged, and the transaction log is not truncated unless you back it up;
this
> allows recovery to a specific point in time. Since your database is very
> small, you might want to change it to simple recovery, where the log is
> automatically truncated and the space reused. This means the only recovery
> option you have is to restore your last full backup, so you would lose any
> modifications since then. If you do need point in time recovery, then you
> would need to schedule transaction log backups - backing up the log
> truncates it.
>
> The reason for the critical error (I assume) is that if the database is in
> full recovery and you truncate the log, it is no longer possible to
restore
> any backups - you need to make a full backup immediately.
>
> This KB article might also be helpful:
>
>
http://support.microsoft.com/default...&Product=sql2k

serge (sergea@nospam.ehmail.com) writes:
> Running SQL Server 2000 Enterprise Edition SP3. The database is also
> used by Microsoft Project Server 2002 and also has OLAP views, so the
> database is being used to view/run cubes in the Analysis Manager.
>
> What is the best way of shrinking the database size and its log file too?
>
> Is there an automatic way to do this with a maintenance plan or i have
> to manually run a SQL statement periodically?
>
> What are the best practices?

Depends on your requirement. If you want up-to-the point recovery, you
should backup your transaction log regularly, and never truncate it.
Notice that just backing up the database does not truncate the transaction
log.

If you don't care about up-to-the point recovery, the best practice is
to switch to simple recovery mode.

> Also concerned as to why the Windows Server 2003 (Enterprise Edition)
> always creates a CRITICAL error in the Application event viewer with
> EventID number 17055, source being MSSQLSERVER and the description of
> the event:
>
> 18278:
> Database log truncated: Database: DBName.
>
>
> If it's a critical error message, then what i am doing is bad? Am i
> damaging my database?

You are losing the ability to do up-to-the-point recovery, and for many
production sites, this is really bad.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thank you for your response Erland, I'll have to think over my backup
procedures
and learn more about simpre/advanced recover mode options.

> Depends on your requirement. If you want up-to-the point recovery, you
> should backup your transaction log regularly, and never truncate it.
> Notice that just backing up the database does not truncate the transaction
> log.
>
> If you don't care about up-to-the point recovery, the best practice is
> to switch to simple recovery mode.
>
> > Also concerned as to why the Windows Server 2003 (Enterprise Edition)
> > always creates a CRITICAL error in the Application event viewer with
> > EventID number 17055, source being MSSQLSERVER and the description of
> > the event:
> >
> > 18278:
> > Database log truncated: Database: DBName.
> >
> >
> > If it's a critical error message, then what i am doing is bad? Am i
> > damaging my database?
>
> You are losing the ability to do up-to-the-point recovery, and for many
> production sites, this is really bad.