By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

Database and Log maintenance

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"serge" <se****@nospam.ehmail.com> wrote in message
news:UT*********************@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
Jul 20 '05 #2

P: n/a
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
Jul 20 '05 #3

P: n/a
serge (se****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

P: n/a
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.

Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.