?
Solved

Shrink DB

Posted on 2009-02-22
13
Medium Priority
?
390 Views
Last Modified: 2012-05-06
Hi,

I recently notice that my LDF file growing, although i run a full backup everyday with shrink after that, the LDF file keep growing and not shrinking after the shrink job.
I have SQL ENT 2000.

How can I shrink it and how can i do it automatically after that?

Thanks.
0
Comment
Question by:yarshw
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23703983
if the database is in full recovery mode, you need not only a full backup, but also transaction log backups, otherwise, your space used in the -.ldf will not be recovered.

so, either implement regular (hourly) transaction log backups (to also enable point in time recovery scenarios), or change the database recovery mode to simple.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23703990
Instead of doing Log File Shrinking each and every day, try doing a transaction LOG backup job scheduled it for each and every day which will help you to effectively handle the LOG File size.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23703991
note: in either scenario, once you have this in place, you can do 1 manual shrink of the ldf file... check out the context menu of the database
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:yarshw
ID: 23704024
I made a transaction log backup and then run shrinking job, didn't help yet.

I do i know which recovery mode i am using?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 23704089
in sys.databases:
recovery_model
 tinyint
 Recovery model selected:

1 = FULL

2 = BULK_LOGGED

3 = SIMPLE
 
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23704153
>I made a transaction log backup

after a single log backup 8which, if successful, makes clear the db is in full recovery mode) is not sufficient for the shrink to succeed.

you need to run this log backup at least 2 times, and in between some transactions must occur (for the internal log writer pointer to move to the beginning of the file ...
0
 

Author Comment

by:yarshw
ID: 23704249
OK, i changed it to full recovery mode and backup again the transaction log and shrink it again.
didn't see any change.

i think the problem is that i see the size is 17.5G and the free space is 16.5G, i guess the DB already shrink
but i need to set to remove the available space from the file.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 23704372
did you try

DBCC SHRINKFILE (N'file_name_comes_here' , 0, TRUNCATEONLY)
0
 

Author Comment

by:yarshw
ID: 23704485
OK run it, should i wait, is it take time? few min after the size remain the same.

Btw, where the file name i type the file name without the extension (LDF), is it right?
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 750 total points
ID: 23704512
this is weird
if you backed up the file and then run the shrink command it should have shrunk
what is the output of the above command?
0
 

Author Comment

by:yarshw
ID: 23704529
DbId    FileId   CurrentSize   MinimumSize   UsedPages   EstimatedPages
16       1         60856           80                    51080           51064
0
 

Author Comment

by:yarshw
ID: 23704908
OK, after the shrinkfile command i need to run a transaction log back and shrink db again.
now it's ok.

Thanks.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23705167
>OK, after the shrinkfile command i need to run a transaction log back and shrink db again.

lol, wasn't that what I suggested also? :)
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question