do we at least have $Y_n=\Theta(\frac{\log n}{n})$ almost surely?
The answer to this is no. It is not even true that $Y_n=\Theta(\frac{h(n)}{n})$ almost surely (a.s.) for any (deterministic or random) function $h$ such that $h(n)\to\infty$ a.s. (as $n\to\infty$). Indeed, suppose the contrary: that $\liminf_n nY_n=\infty$ a.s. Then, by the Fatou lemma, $$\infty=E\infty\le\liminf_n EnY_n=\liminf_n n\frac1{n+1}=1,$$ a contradiction.
Remark 1: If $Y_n\sim f(n)$ a.s. for some deterministic positive function $f$, then, reasoning similarly to the above, we conclude that $f(n)\lesssim 1/n$. Indeed, otherwise $n_kf(n_k)\ge c$ for some real $c>1$ and all natural $k$, where $(n_k)$ is some strictly increasing sequence of positive integers. So, by the Fatou lemma, $$1=E\lim_k\frac{Y_{n_k}}{f(n_k)}\le\liminf_k E\frac{Y_{n_k}}{f(n_k)}=\liminf_k \frac1{(n_k+1)f(n_k)}\le\frac1c<1,$$ a contradiction.
Remark 2: If $Y_n\sim f(n)$ a.s. for some deterministic positive function $f$, then without loss of generality $f$ is nonincreasing. Indeed, let $$g(n):=\min(f(1),\dots,f(n)).$$ Then $0<g(n)\le f(n)$ and $g(n)=f(k_n)$ for some integer $k_n$ between $1$ and $n$. Also, it follows from Remark 1 that $g(n)=O(1/n)=o(1)$. So, $k_n\to\infty$ and hence $$1\leftarrow\frac{Y_n}{f(n)}\le\frac{Y_n}{g(n)}\le\frac{Y_{k_n}}{f(k_n)}\to1,$$ so that $Y_n\sim g(n)$, and $g$ is a deterministic positive nonincreasing function.
Remark 3: If $Y_n\sim f(n)$ a.s. for some deterministic positive function $f$, then for any positive sequence $(a_n)$ such that $\sum_n a_n=\infty$ we have $f(n)\le a_n$ infinitely often (i.o.), that is, for infinitely many values of $n$. In particular, $f(n)\le\frac1{n\ln n\,\ln\ln n}$ i.o. Indeed, $$\sum_n P(X_n<a_n/2)=\sum_n a_n/2=\infty.$$ So, by the Borel--Cantelli lemma, events $\{X_n<a_n/2\}$ occur i.o. Therefore and because $\{X_n<a_n/2\}\subseteq\{Y_n<a_n/2\}$, we see that events $\{Y_n<a_n/2\}$ occur i.o. Recalling now that $Y_n\sim f(n)$ a.s., we conclude that $f(n)\le a_n$ i.o.