Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
MS SQL 2000 - LDF 3G , can shrink it ?
- Thread starter sheong
- Start date
nigelrivett
IS-IT--Management
Shrinking the ldf will only have an adverse effect if it has to grow again.
Are you taking tr log backups? If not then set the database to simple recovery model. You should now be able to shring the file using dbcc shrinkfile or enterprise manager.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Are you taking tr log backups? If not then set the database to simple recovery model. You should now be able to shring the file using dbcc shrinkfile or enterprise manager.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
- Thread starter
- #3
nigelrivett
IS-IT--Management
>> Shrinking the ldf will only have an adverse effect if it has to grow again.
It takes a lot of resources to grow a file so will slow down the system while it is happenning. You shouldn't shrink the file if the larger size is needed.
I use dbcc shrikfile to do the shrink.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
It takes a lot of resources to grow a file so will slow down the system while it is happenning. You shouldn't shrink the file if the larger size is needed.
I use dbcc shrikfile to do the shrink.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
One thing you can do is shrink the TL to a size you think is appropriate. Then watch to see if it grows. Check every day. Since your MDF is 1 GB, I would start with at LDF of 1.5 GB. If the TL is growing quickly, you should change the initial size to something more reasonable.
The next thing to do is look at what size you allow the TL to grow by. Are you allowing growth by 1 MB? If so that's, really low, especially if your TL is growing by 500 MB a day.
-SQLBill
The next thing to do is look at what size you allow the TL to grow by. Are you allowing growth by 1 MB? If so that's, really low, especially if your TL is growing by 500 MB a day.
-SQLBill
I goofed on this statement: Since your MDF is 1 GB, I would start with at LDF of 1.5 GB.
First find out what size your database grows by on a daily basis sp_spaceused will help with that. Run it each day and see how the data is growing. Then make your TL start at that amount plus 1/2 (so if your data grows by 10MB each day, a starting size should be AT LEAST 15MB).
Of course, this is just my opinion. And you will have to monitor the growth until you get your amounts correct.
Lastly, if you backup the transaction log or truncate it, there's nothing wrong with leaving the empty space. It will be used by later transactions.
-SQLBill
First find out what size your database grows by on a daily basis sp_spaceused will help with that. Run it each day and see how the data is growing. Then make your TL start at that amount plus 1/2 (so if your data grows by 10MB each day, a starting size should be AT LEAST 15MB).
Of course, this is just my opinion. And you will have to monitor the growth until you get your amounts correct.
Lastly, if you backup the transaction log or truncate it, there's nothing wrong with leaving the empty space. It will be used by later transactions.
-SQLBill
- Thread starter
- #7
If you delete the ldf file, you will only have the mdf file and that has caused people problems in the past. It's usually only a matter of attaching a single file (sp_attach_single_file via Query Analyzer), but sometimes there's additional problems.
If you really need the space back, I suggest doing what Nigel recommends - backup or truncate the log and then run DBCC SHRINKFILE. Refer to Books OnLine for more information about DBCC SHRINKFILE.
-SQLBill
If you really need the space back, I suggest doing what Nigel recommends - backup or truncate the log and then run DBCC SHRINKFILE. Refer to Books OnLine for more information about DBCC SHRINKFILE.
-SQLBill
nigelrivett
IS-IT--Management
That will reliably create a small ldf.
You should backup the database first as you should with any of these commands.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
You should backup the database first as you should with any of these commands.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
- Thread starter
- #10
- Thread starter
- #11
Hi Nigel and SQLBill,
Recently i did a restore a backup database. But realized that my application program response very slow. I suspect is the indexing problems.
I did some indexing in the database before it crash. Do i need to restore the master database also ? Because i read some articles said the master db also recorded the indexing.
Any comments ? thankx
Recently i did a restore a backup database. But realized that my application program response very slow. I suspect is the indexing problems.
I did some indexing in the database before it crash. Do i need to restore the master database also ? Because i read some articles said the master db also recorded the indexing.
Any comments ? thankx
nigelrivett
IS-IT--Management
No in v2000 almost everything is self contained in the database.
Try updating statistics if the relevant indexes are there but not being used.
You can use the profiler to see which queries are slow then run them in query analyser to see the query plan.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Try updating statistics if the relevant indexes are there but not being used.
You can use the profiler to see which queries are slow then run them in query analyser to see the query plan.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
