Re: What is LOP_SHRINK_NOOP

"Thomas Yan" <yantaotime@xxxxxxxxx> wrote in message news:grros9$k5f$1@xxxxxxxxxxxxxxxx

Well,the situation is that I was using MSDE 2000 with SP4 on a PC, and the database is small. The original mdf is 20Mb and the ldf file is 5Mb. The original system is developed by other guy with the settings:
1, Recovery Model: FULL
2, Auto-Shrink: ON
3, No nightly backup
The system works normally. The ldf file is about 100Mb after months and never grows large. Yes,indeed,there are fragments on hard disk.

Now, I add nightly backup to the system using DBCC backup.

Umm, not sure what you mean here. DBCC has no backup command.

I backuped the mdf file and didn't backup the ldf file. The other settings are not changed.
Then, I encounter the big log issue, the ldf file grows to 60Gb after months and full of 'LOP_SHRINK_NOOPS' records.

Yes, the log file grows because you're not backing it up.

Basically EVERYTHING that changes data in the DB is recorded to the log file. This includes not only INSERTS, DELETES and UPDATES, but schema changes and index updates/defrags, etc.

So you need to use the T-SQL backup command to back up the log to a safe place periodically. Note, you need to have every transaction log backup since the last FULL (or FULL+Differential) Backup in order to successfully recover the database.

If this is data is important I would recommend a couple of things:

Upgrade to SQL 2005 Express (I don't believe MSDE 2000 is still supported).
Setup a task to do a FULL backup nightly using the T-SQL BACKUP DATABASE command.
Setup a task to do an hourly or so transaction log backup command. Store these and the other backup in a safe place.



I want to know why this happened,because of I didn't backup the ldf file?

Basically yes.

BTW, my personal opinion is you made a mistake that many people are guilty of. They focus on the MDF file since that's where all the data is. They greatly underestimate exactly how important the LDF file is. Don't worry, it's a common mistake.

(Think of it this way, in theory, if you had every transaction log backup since the moment the database was created, you could not only recreate the DB to the point of failure, you could with a little effort recreate ANY previous state.)

Or any other reasons?

Thanks.



--
Greg Moore
Ask me about lily, an RPI based CMC.

.

Relevant Pages

  • What is the best practice to create Maintenance Plans
    ... There is not a definitive answer to the question of which backup is better. ... the database can be recovered to the point ... Full Recovery or Bulk-Logged Recovery model. ... Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • Re: .LDF files and SharePoint Portal
    ... So when you take a backup of the database, ... I have some questions about .LDF files and Sharepoint. ... Is it safe to delete the data in the .LDF file or will it cause ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Crash Recovery
    ... You can't apply an LDF file to a restored database backup. ... >> How to restore the BAK file. ...
    (microsoft.public.sqlserver.server)
  • Flat File Backup Failed *suddenly*
    ... DB: SQL Server 2000 SP3a ... I have a database maintenanec plan set up to do full database backup (i.e. ... I have a DB which has a 3.8GB LDF file which backup with no problems!) ...
    (microsoft.public.sqlserver.server)
  • Flat File Backup Failed *suddenly*
    ... DB: SQL Server 2000 SP3a ... I have a database maintenanec plan set up to do full database backup (i.e. ... I have a DB which has a 3.8GB LDF file which backup with no problems!) ...
    (microsoft.public.sqlserver.server)