| Author |
Topic |
|
melcraig
Starting Member
39 Posts |
 Posted - 2006-08-23 : 11:04:33
|
| HiI'm still fighting to free disk space.If I run the DBCC SHRINKFILE on a transaction log will I lose any data? Thanks for the helpMel |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-23 : 11:17:04
|
quote:
Originally posted by melcraig
HiI'm still fighting to free disk space.If I run the DBCC SHRINKFILE on a transaction log will I lose any data? Thanks for the helpMel
nope.-ec |
 |
|
|
melcraig
Starting Member
39 Posts |
Posted - 2006-08-23 : 14:00:14
|
| I guess I should tell that these are SharePoint .ldf files. I tried running the DBCC SHRINKFILE (log file name, TruncateOnly) but it did not free any space.Our backups are run nightly by Tivoli.Any suggestions on how to get these under control as I have no disk space left.Thanks to everyone for their help.Mel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 14:21:55
|
| What is the database recovery model set to for the SharePoint database? If FULL or BULK_LOGGED, are you running regular transaction log backups?Tara Kizer |
|
|
|
melcraig
Starting Member
39 Posts |
Posted - 2006-08-23 : 14:48:23
|
| The recovery model is set to full.And they DO NOT do transaction log backups in Tivoli.Thanks, Mel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 14:50:15
|
| Then that's your problem. If you aren't going to do any transaction log backups, then switch the recovery model to SIMPLE then perform the shrink.Tara Kizer |
|
|
|
melcraig
Starting Member
39 Posts |
Posted - 2006-08-23 : 14:57:04
|
| So If I change the recovery model to simple, run the dbcc shrinkfile,can I put it back in full recovery and start doing my trans. log backups.Will I lose any data by putting it in simple recovery mode and then shrinking the file?Thanks, Mel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 15:01:27
|
| You will not lose any data by doing any of this. If you do not require point in time recovery, then you should use SIMPLE recovery model. Both BULK_LOGGED and FULL recovery models are so that you can recover to a point in time rather than just the full backup. We backup our transaction logs every 15 minutes due to the criticality of the data and the amount of data we can lose in a disaster situation. You can also perform the shrink if you just leave it in FULL and perform one transaction log backup. You don't have to switch it to SIMPLE.Tara Kizer |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-23 : 15:05:52
|
quote:
Originally posted by melcraig
So If I change the recovery model to simple, run the dbcc shrinkfile,can I put it back in full recovery and start doing my trans. log backups.Will I lose any data by putting it in simple recovery mode and then shrinking the file?Thanks, Mel
don't change to simple yet! I would try to back these tlogs up first.why don't you do tlog backups in tivoli? it is fully supported.-ec |
|
|
|
melcraig
Starting Member
39 Posts |
Posted - 2006-08-23 : 16:06:34
|
| I would backup the tranaction log if I had the disk space available.Unfortunately I am not the Tivoli administrator or the SharePoint admin.So I don't know what to do?I was thinking to get it under control today I could put it in simple mode and do the DBCC Shrinkfile.I tried the dbcc shrinkfile with the db being in the full recovery mode but it did not shrink anything.Then to get SharePoing back on the right track after I did the simple recovery/dbcc shrinkfile I would do a full backup and schedule tran log back ups.I think this is about the only way I can do this.Thanks, Melinda |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 16:24:36
|
| The reason why the shrink doesn't work when in full recovery model is because the data in the tlog is active. There is nothing to shrink due to this.Tara Kizer |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-23 : 17:05:35
|
quote:
Originally posted by melcraig
I would backup the tranaction log if I had the disk space available.Unfortunately I am not the Tivoli administrator or the SharePoint admin.So I don't know what to do?I was thinking to get it under control today I could put it in simple mode and do the DBCC Shrinkfile.I tried the dbcc shrinkfile with the db being in the full recovery mode but it did not shrink anything.Then to get SharePoing back on the right track after I did the simple recovery/dbcc shrinkfile I would do a full backup and schedule tran log back ups.I think this is about the only way I can do this.Thanks, Melinda
Use tivoli to do the tlog backup. Tivoli supports all full, diff and incremental (transaction log) SQL Server backups.IF you don't do incremental backups, then you lose the ability to perform point-in-time recovery. This means that you can only restore your database to the last full backup, or the last full plus differential backup. You could lose hours or days or weeks of data.Maybe that isn't important. If this system is not that important, then by all means change to simple logged mode. If it is important then get your tivoli admin off his ass and have them figure out how to allow your system to take incremental backups.-ec |
|
|
|
melcraig
Starting Member
39 Posts |
Posted - 2006-08-23 : 18:51:50
|
| Thank you all so much for your advice.I was able to convince the Tivoli peeps to do log backups. We had to dump the logs twice and run the dbcc shrinkfile twice too before we saw any space being released.Again thank you all for your help.Mel |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-23 : 20:14:18
|
quote:
Originally posted by melcraig
Thank you all so much for your advice.I was able to convince the Tivoli peeps to do log backups. We had to dump the logs twice and run the dbcc shrinkfile twice too before we saw any space being released.Again thank you all for your help.Mel
tlog backups should be scheduled regularly - like every hour, or every couple of hours. On really critical systems we do these backups every 15 mins.Glad to hear things are under control again. :)-ec |
|
|
|
|
DBCC SHRINKFILE