| Author |
Database Backups and Transaction Logs
|
|
| Stephen Costanzo 2005-10-17, 1:23 pm |
| Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
| |
| Stephen Costanzo 2005-10-17, 1:23 pm |
| ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@TK2MSFTNGP15.phx.gbl...
> Stephen,
>
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
>
> HTH
>
> Jerry
> "Stephen Costanzo" <sxcostanzo@hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@TK2MSFTNGP09.phx.gbl...
was[color=darkred]
>
>
| |
| Tom Moreau 2005-10-17, 1:23 pm |
| You must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stephen Costanzo" <sxcostanzo@hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
| |
| Jerry Spivey 2005-10-17, 1:23 pm |
| Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
>
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
>
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
>
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
>
> Thanks
>
>
>
| |
| Jerry Spivey 2005-10-17, 1:23 pm |
| Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
>
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
>
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
>
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
>
> Thanks
>
>
>
| |
| Tibor Karaszi 2005-10-17, 1:23 pm |
| I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"Stephen Costanzo" <sxcostanzo@hotmail.com> wrote in message
news:emVLHa2zFHA.560@TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
>
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
>
> Thanks for the quick response.
>
> "Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@TK2MSFTNGP15.phx.gbl...
> was
>
>
|
|
|
|