473,324 Members | 2,248 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Database and Log maintenance

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
4 11590

"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Alex | last post by:
Hi all, We're looking at a vendor who uses the InterSystems Cache Database Platform, but our IT department has zero experience with this system. This software package will have a pivotal and...
8
by: Greg | last post by:
Hello, I've to manage many 'table' (having same scheme) on the same server. And I ask myself what could be the best to do (and if you know, why) : Creating as many database (the name would...
2
by: trotter | last post by:
I want to know if there is a "best-practice" for setting up Database Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want to know the order in which I complete the tasks. Do I...
6
by: Eric Robinson | last post by:
Our database just slowed down radically. Certain queries drive CPU utilization through the roof. This happened once before, and the technican simply dumped the data to disk and then played it back...
0
by: Sean Powell | last post by:
I am a SQL Server DBA Manager and my employer (Symantec) will be opening a position in the near future (20-60 days) for my DBA team. Here is the job description:...
13
by: Larry L | last post by:
Access is noted for bloating a database when you add and delete records frequently. I have always had mine set to compact on close, and that works great. Now after everyone's advice I split my...
6
by: LB | last post by:
Hi, I have a client that I support totally remotely. I have them on a weekly rollout schedule to get updates to their application, which I send them via email. I don't have access to their...
3
by: Jerry | last post by:
Hopefully this is the right group to ask. I am new to databases and I want to build a database to record maintenance for my car to learn with. I would appreciate any suggestions for fields to use...
5
by: Icemokka | last post by:
Hi, We have a SQL-cluster with over 6300 databases, most of them pretty small in size and usage. For this reason all these databases are on auto-close ( Otherwise SQL would need many many...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.