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
4  11603 
"serge" <se****@nospam. ehmail.com> wrote in message
news:UT******** *************@n ews20.bellgloba l.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 (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****@sommarsk og.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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 mission critical roll in
our organization, so I'd like some comments on what others think of
this database platform.
Mainly I'm curious how easy/difficult it is to query a Cache Database,
and does it use standard SQL calls like Oracle and MS SQL? ...
|
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 be a 8byte int value
(converted to a string)) as necessary, all with the same table struct
(and table name), or create 1 database and in it create also as many
table as necessary (so also a 8byte int value as name) ?
|
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 complete
optimization first, then integrity checks, then translog backup, then
full backup??? OR is there a better order which should be used?
Should I ALWAYS backup the transaction Log before I complete a full
database backup, and if so, why??
...
|
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 in. This seemed to
correct database corruption. After than, the database went back to normal
performance. Does anyone know how to do this?
--Eric
|
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:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sr Security Infrastructure Adm
Calgary, Canada
Consulting
Job...
| | |
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 database, so the data is in
a second (back-end) database with all the tables linked. However, now when
I close the database, it compacts the front end, since that's what's open,
and the back-end grows.
I now have to manually open and close the...
|
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 network. My
question is this:
I want to have the upgrade send me an email silently whenever a user
upgrades. I've done this with clients where I support in-house, and my
boss thought it was a great idea since most of our support calls were
|
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 for information I should
capture and should I make multiple tables or just one? Information pointing
me in a direction of ideas would be appreciated.
Thanks.
|
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 gigabytes ).
We've recently upgraded to SQL 2005 because of the Management Studio,
because the EM is unusable on a SQL-Server with lots of databases.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
