A couple LDF questions.

I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.