SQL 2000 DATA disk cleanup

Hello,

 I have a client running Microsoft SQL 2000 and disk space is low. I am running TreeSize and found that my SQL DATA is high. Not sure if I need to truncate data or how I can clean it up? I am trying to locate disk reports within SQL 2000 and then find out how to clean up data. TreeSize reports: 4,632.1 MB (test2_log.ldf).



 Thanks,

 Nimdatx
LVL 1
Jaime CamposAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
If it's "FULL" recovery mode,  you'll need to truncate the log before you can shrink it:

BACKUP LOG [Test2] --db_name
WITH TRUNCATE_ONLY

CHECKPOINT

DBCC SHRINKFILE( 2, 1024 )
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Open Enterprise Manager right-click the database, click View and then click Taskpad and you'll see the information about the data and log files.
0
 
Jaime CamposAuthor Commented:
Ok.

Database
15,714.36 MB
Space availiable: 0.00MB
Database options: normal
Number of users: 7

Maintenance: LAst DB Backup: 3/9/2015 2:02:18 AM
Last differential backup: None
Last transaction log backup:3/10/2015 12:00:05 AM

Space allocated:
DATA TEST2 11082.19MB - 5934.38MB used/5147.81MB free
Transaction log space: 4632.12 28.42MB Used/4403.7MB Free
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Jaime CamposAuthor Commented:
When I navigate to f:\SQL Logs, the biggest file is TEST2_log.ldf. How can I clean this up? THANKS!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
As you can see the files are large but still a lot of free space inside them.
What you should investigate is why the transaction log reached 4.5GB with only around 200MB filled. The fast thing to do is to shrink the log file but that doesn't mean that in a near future the file size won't increase again.

For shrink the transaction log file, open Query Analyzer and run the following:
USE TEST2
GO
DBCC SHRINKFILE(TEST2_log)

Open in new window

After that go check again in the Enterprise Manager (you may need to Refresh the page to see the changes).
0
 
Jaime CamposAuthor Commented:
What will shrinking the log files do for me? I am kinda new to this concept. Can I do this while server in production? THANK YOU so much.
0
 
Jaime CamposAuthor Commented:
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'Test2_log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
 
Scott PletcherSenior DBACommented:
I'd leave at least a gig in the log file, since it had already grown to 4GB:

DBCC SHRINKFILE( 2, 1024 )
0
 
Jaime CamposAuthor Commented:
Do I run that query? What does the 2, 1024 mean?
0
 
Scott PletcherSenior DBACommented:
Yes, from within that db.

2 = File #2 -- log file; 1024 = leave 1024 mb = 1 gb in the log file after shrinking it.
0
 
Jaime CamposAuthor Commented:
I checked SQL log file to verify any errors with backup.
2015-03-10 06:45:05.64 - Database log truncated: Database: conceptTest2.
2015-03-10 06:43:58.65 - BACKUP failed to complete the command backup log conceptTest2_log with truncate_only

This is the properties of test2 for your review..

Test2 Properties
Transaction log files
File Name: concept700mfr_log - Location f:\SQL Logs\Test2_log.ldf Space Allocated: 4633
File properties
checked - Automatically grow file
File growth - By percent: 10
Maximum File size: Unrestricted file growth

Backup: last database backup: 3/9/2015 2:02:18 AM
Last transaction log backup: 3/10/2015 12:00:05 AM

Maintenance plan: ConceptDevWeeklyMonday
Collation name: SQL_Latin1_General_CP1_CI_AS

Recovery Model: FULL

Compatibility Level: 80
0
 
Jaime CamposAuthor Commented:
Do I leave db_name or put in the db name. sorry man..
0
 
Scott PletcherSenior DBACommented:
Put in your actual db name in place of "<your_db_name_here>".

USE [<your_db_name_here>]

BACKUP LOG [<your_db_name_here>] WITH TRUNCATE_ONLY

CHECKPOINT

DBCC SHRINKFILE ( 2, 1024 )
0
 
Jaime CamposAuthor Commented:
All one command/query right?
0
 
Jaime CamposAuthor Commented:
DbId|FileID|Current size|MinimumSize|UsedPages|EstimatePages
8      2      132272      128      132272      128
0
 
Jaime CamposAuthor Commented:
ok it looks like it worked..what did I just do :/, I'll have to explain to my boss. I APPRECITAE YOUR HELP SO MUCH!!!!!
 
What did I just truncated and shrinked the way I just did?

how did you know to use file#2?

How can I prevent this from happening?

Why did this happen in the first place?
0
 
Scott PletcherSenior DBACommented:
In technical terms, you truncated the log without having to back it up first, then you shrunk the log.

In non-technical terms, you got rid of the modification history on the db.  You can no longer see who did what when, and you can no longer recover the db using the log.  Because of that, you need to take a full db backup as soon as possible!

Then decide if this db needs to be in FULL recovery mode or not.  This decision is based on how much data you can afford to lose in a crash.

If you decide to stay in FULL mode, you'll need to set up regular log backups ...
BACKUP LOG <db_name>
TO DISK = 'x:\full\path\to\log\backup\unique_file_name_for_each_backup.trn'
0
 
Jaime CamposAuthor Commented:
From the errors I sent you it look like the BACKUP failed to complete the command backup log Test_log with truncate. Would that have caused the log  file to grow that big? Then it looked like the Database log truncated: Database: test2 a bit after that. I am just trying to find out why this happened. So the backup job I have in place truncates, but what shrinks the file or does this happen on it's own?
0
 
Jaime CamposAuthor Commented:
Looks like their is a weekly backup FULL. How would I run it right now and will the log file grow back to it's size?
0
 
Scott PletcherSenior DBACommented:
No, the file will never physically shrink on its own,  you have to explicitly do that.

In FULL recovery mode, if the log is not being backed up, then it will continue to grow forever (or until you run out of disk).

If you just truncate the log, and never back it up, it would be simpler to just put the db into SIMPLE mode.  You won't be able to recover to a specified point in time, but you could also take differential backups periodically -- say every 6 hours between full backups -- which would let you recover to the time of the last differential.
0
 
Jaime CamposAuthor Commented:
WOW. ok how do I run a backup now so I do not lose anything.
0
 
Jaime CamposAuthor Commented:
I see a transaction backup scheduled for today at 12 AM (Backup the transaction log of the database as part of the maintenance plan) Should I wait and verify this backup goes successfully? I am so sorry I keep asking more questions I almost got it. If this TLBackup is setup to run every night, why would the log file be so big?
0
 
Scott PletcherSenior DBACommented:
No problem on the qs.

I'd do a full db as soon as you reasonably can.  A log backup won't really help with that, although it won't hurt anything.

If the log is backed up successfully every night, the only reason it would have grown that big is a runaway task at some point put a lot of data into the log.  Once the log grows, even for a one-time freak thing, it will never shrink down in size on its own.
0
 
Jaime CamposAuthor Commented:
How do I  execute my existing full backup maintenance plan? Should I do this tonight?
0
 
Scott PletcherSenior DBACommented:
I can't give you expert advice on maintenance plans, as I don't use them.  But I think you can create a schedule for them just like for any other job.  So you could create a special, one-time-only schedule to get a full db backup and/or a log backup very quickly, then let the regular maintenance plan schedule handle it from then on.
0
 
Jaime CamposAuthor Commented:
I cannot thank you enough on your expertise. I appreciate you staying with me. Now, I am going to print this and review every note so I may better understand. THANK you so much! Keep up the great work!
0
 
Jaime CamposAuthor Commented:
Thank you so much!
0
 
Scott PletcherSenior DBACommented:
You're welcome ... really glad it helped!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.