Database Change Log

Is there any way to see what jobs and or changes have been
made to a database. Not the data itself, but things like
indexes, statistics, DB settings, or outside/automated
jobs? I know I can run a trace to see real-time actions,
but I'm interested to see what may have happened overnight
or yesterday. Any help would be greatly appreciated.
Thank you,
Heidi
Some stuff are logged in the errolog file. You can also configure each SQL Server message to say
whether it will go to the errorlog file. However, DBCC DBREINDEX, for instance, doesn't return any
messages. You can check the Agent log, but that will not catch of someone does it from outside
Agent. Depending on your situation, your only option can be Profiler, or some audit tool reading the
transaction loge (see my links page about log reader tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3801c49c0d$a14666b0$a301280a@.phx.gbl...
> Is there any way to see what jobs and or changes have been
> made to a database. Not the data itself, but things like
> indexes, statistics, DB settings, or outside/automated
> jobs? I know I can run a trace to see real-time actions,
> but I'm interested to see what may have happened overnight
> or yesterday. Any help would be greatly appreciated.
> Thank you,
> Heidi

Database Change Log

Is there any way to see what jobs and or changes have been
made to a database. Not the data itself, but things like
indexes, statistics, DB settings, or outside/automated
jobs? I know I can run a trace to see real-time actions,
but I'm interested to see what may have happened overnight
or yesterday. Any help would be greatly appreciated.
Thank you,
Heidi
Some stuff are logged in the errolog file. You can also configure each SQL Server message to say
whether it will go to the errorlog file. However, DBCC DBREINDEX, for instance, doesn't return any
messages. You can check the Agent log, but that will not catch of someone does it from outside
Agent. Depending on your situation, your only option can be Profiler, or some audit tool reading the
transaction loge (see my links page about log reader tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3801c49c0d$a14666b0$a301280a@.phx.gbl...
> Is there any way to see what jobs and or changes have been
> made to a database. Not the data itself, but things like
> indexes, statistics, DB settings, or outside/automated
> jobs? I know I can run a trace to see real-time actions,
> but I'm interested to see what may have happened overnight
> or yesterday. Any help would be greatly appreciated.
> Thank you,
> Heidi
sql

Database Change Log

Is there any way to see what jobs and or changes have been
made to a database. Not the data itself, but things like
indexes, statistics, DB settings, or outside/automated
jobs? I know I can run a trace to see real-time actions,
but I'm interested to see what may have happened overnight
or yesterday. Any help would be greatly appreciated.
Thank you,
HeidiSome stuff are logged in the errolog file. You can also configure each SQL Server message to say
whether it will go to the errorlog file. However, DBCC DBREINDEX, for instance, doesn't return any
messages. You can check the Agent log, but that will not catch of someone does it from outside
Agent. Depending on your situation, your only option can be Profiler, or some audit tool reading the
transaction loge (see my links page about log reader tools).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3801c49c0d$a14666b0$a301280a@.phx.gbl...
> Is there any way to see what jobs and or changes have been
> made to a database. Not the data itself, but things like
> indexes, statistics, DB settings, or outside/automated
> jobs? I know I can run a trace to see real-time actions,
> but I'm interested to see what may have happened overnight
> or yesterday. Any help would be greatly appreciated.
> Thank you,
> Heidi

Database become suspect..

Hello,
One of my production database become suspect. Here is the messages taken from event veiwer-> Application Log. This is all I can share with you. Please help.
Regards
Nurullah
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA "19013 :
SQL server listening on TCP, Shared Memory, Named Pipes.
"
--
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 17126 :
SQL Server is ready for client connections =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'. =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=3D2610 Failed Assertion =3D 'spaceNeeded <=3D spaceContig && spaceNeeded <=3D space_usable'.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "18052 :
Error: 3624, Severity: 20, State: 1.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '1618d4000000100' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <scanrid.cpp>, line=3D321 Failed Assertion =3D 'm_len !=3D 0'.
"
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '=01' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:34 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3063 Failed Assertion =3D '(m_lastLSN =3D=3D NullLSN) || (m_lastLSN > m_curLSN)'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3282 Failed Assertion =3D 'm_lastLSN =3D=3D NullLSN || startLSN < m_lastLSN'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9004, Severity: 23, State: 7
An error occurred while processing the log for database 'eBill_Main'. "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9001, Severity: 21, State: 1
The log for database 'eBill_Main' is not available. "
--An assertion is when a piece of SQL Server's C Code goes west.
This line interested me though
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=2610
Failed Assertion = 'spaceNeeded <= spaceContig &&
spaceNeeded <= space_usable'.
Do you have enough disk space?
What version SQL Server and SP are you running ?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Database become inaccessible

Hi,
log file of my database become inaccessible after some time.
After restarting mssqlserver, few hours it is working fine and then it happe
n again.
In the serverlog I can see:
2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The proce
ss cannot access the file because it is being used by another process.) duri
ng the creation/opening of physical device C:\Program Files\Microsoft SQL Se
rver\MSSQL\Data\Replication
DB_log.LDF.
2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\
Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virt
ual device number (VDN) 2.
2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for
database ReplicationDB.
2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for
file C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
regards
janCheck eventlog etc for OS and HW errors. Also, check if you have autoclose o
n, that can result in
the file being closed that the file is used by some other program (anti-viru
s, defrag etc).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Jan Zatko" <jan.zatko@.frequentis.com> wrote in message
news:855E0933-1BE8-4B7F-94D1-BA214CBADDB7@.microsoft.com...

quote:

---

> Hi,
> log file of my database become inaccessible after some time.
> After restarting mssqlserver, few hours it is working fine and then it hap
pen again.
> In the serverlog I can see:
> 2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The process cannot acce
ss the

---

file because it is being used by another process.) during the creation/openi
ng of physical device
C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.

quote:

---

> 2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\Program[/color
]

---

Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virtual device number (VDN) 2.[QUO
TE]
> 2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for database[/colo
r][/QUOTE]
ReplicationDB.

quote:

---

> 2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for file C:\Progra
m

---

Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.

quote:

---

> regards
> jan

---

Database become inaccessible

Hi
log file of my database become inaccessible after some time.
After restarting mssqlserver, few hours it is working fine and then it happen again
In the serverlog I can see
2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF
2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virtual device number (VDN) 2
2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for database ReplicationDB.
2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for file C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
regard
janCheck eventlog etc for OS and HW errors. Also, check if you have autoclose on, that can result in
the file being closed that the file is used by some other program (anti-virus, defrag etc).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jan Zatko" <jan.zatko@.frequentis.com> wrote in message
news:855E0933-1BE8-4B7F-94D1-BA214CBADDB7@.microsoft.com...
> Hi,
> log file of my database become inaccessible after some time.
> After restarting mssqlserver, few hours it is working fine and then it happen again.
> In the serverlog I can see:
> 2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The process cannot access the
file because it is being used by another process.) during the creation/opening of physical device
C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
> 2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\Program
Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virtual device number (VDN) 2.
> 2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for database
ReplicationDB.
> 2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for file C:\Program
Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
> regards
> jansql

Database Backups and Transaction Logs

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
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
|||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
>
>
|||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
>
>
|||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...[vbcol=seagreen]
> 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
>
|||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
>

Database Backups and Transaction Logs

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?
ThanksYou 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|||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
>
>|||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
>
>|||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[vbcol=seagreen]
>|||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 u
p
> 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
>

Database Backups and Transaction Logs

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?
ThanksYou 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|||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
>
>|||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
>
>|||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...
> > 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
> >
> >
> >
>|||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...
>> 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
>> >
>> >
>> >
>>
>

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.
Tom,

> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:

> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>
|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.

> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/inf..._truncate.asp.

> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.

> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,

> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restor
e
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:

> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model i
s
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db a
nd
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say a
t
> noon. this will save time restoring the db because you do not have to rest
ore
> all transaction log backups from the last full one, instead you restore th
e
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full
,
> or from diff to diff if there is not a full between them, or between full
to
> full if not diff), as many as you need to avoid it from growing and be sav
e
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
>|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNC
ATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox shou
ld be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the
exact same BACKUP
DATABASE command whether or not the checkbox is checked.

> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done
of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and als
o
http://www.karaszi.com/SQLServer/in...o_truncate.asp.

> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If y
ou don't do log backups,
then run in simple recovery mode.

> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every t
en minutes).
Or if you don't want to do log backups, put the db in simple recovery mode a
nd only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gb
l...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>sql

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,
> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
> Tom,
> > How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
> > I have some confusion about backing up databases and transaction logs.
> > When I do a complete database backup it backs up the db and the log, but
> > when I have it set to clear out inactive entries in the log it does not.
> > When I do a log backup and set it to clear out inactive entries it does do
> > that. My question is then if I do complete database backups all the time and
> > the log is not cleared for a long time and I do a restore and then play back
> > the log from a long time ago will I corrupt the database? How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> >
> >
> >|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.
> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp.
> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.
> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
IvanHi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> > I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> > device.
> >
> > I have backup the database into a file, and try to restore it into
another
> > SQL Server 2000.
> > The new database has only 1 data device and 1 log.
> > However, when I try to restore it, in Option page, it has 2 data device
> and
> > 2 log device and I have no way to restore
> >
> > how can I restore that database?
> >
> > Ivan
> >
> >
>sql

Database backup strategy questions....

I have been reading about the SQL Server transaction log and I'm still
confused about a lot of things. First of all, what is this file
actually used for? In the past when backing up my database, I only
backup and restore the actual database and skip the log file and this
has worked just fine in getting all my data back. Why do I have to
backup the log file and when would I ever use it?
I am having a problem now becuase my log file is over 4 GB for a 50 MB
database and I'm not sure why. My application is a POS system where
hundreds of transactions are entered daily and backups are done each
night of the database but not the log. I can restore the database just
fine without the log file, what is the advantage of backing up the log
file also? I can't backup that much data so I'll need to make that
much smaller if I did back it up. Based on my needs what is the best
backup strategy and what would I lose by not backing up the log?
Thanks.Ray,
When you restore the database file from a Full backup it actually restores
the log file along with it. The Log file holds the transactional
information to ensure consistency within the db and can not be used without
it. The log backups allow you to potentially restore to a particular point
in time under thright conditions. If all you care about is being able to
restore to the last Full backup (potentially loosing all transactions since
that last backup) you should place the database in SIMPLE recovery mode and
it will automatically truncate the log and not grow. See here for more
details:
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly SQL MVP
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0401131950.120fda92@.posting.google.com...

quote:

---

> I have been reading about the SQL Server transaction log and I'm still
> confused about a lot of things. First of all, what is this file
> actually used for? In the past when backing up my database, I only
> backup and restore the actual database and skip the log file and this
> has worked just fine in getting all my data back. Why do I have to
> backup the log file and when would I ever use it?
> I am having a problem now becuase my log file is over 4 GB for a 50 MB
> database and I'm not sure why. My application is a POS system where
> hundreds of transactions are entered daily and backups are done each
> night of the database but not the log. I can restore the database just
> fine without the log file, what is the advantage of backing up the log
> file also? I can't backup that much data so I'll need to make that
> much smaller if I did back it up. Based on my needs what is the best
> backup strategy and what would I lose by not backing up the log?
> Thanks.

---

sql

Database backup strategy questions....

I have been reading about the SQL Server transaction log and I'm still
confused about a lot of things. First of all, what is this file
actually used for? In the past when backing up my database, I only
backup and restore the actual database and skip the log file and this
has worked just fine in getting all my data back. Why do I have to
backup the log file and when would I ever use it?
I am having a problem now becuase my log file is over 4 GB for a 50 MB
database and I'm not sure why. My application is a POS system where
hundreds of transactions are entered daily and backups are done each
night of the database but not the log. I can restore the database just
fine without the log file, what is the advantage of backing up the log
file also? I can't backup that much data so I'll need to make that
much smaller if I did back it up. Based on my needs what is the best
backup strategy and what would I lose by not backing up the log?
Thanks.Ray,
When you restore the database file from a Full backup it actually restores
the log file along with it. The Log file holds the transactional
information to ensure consistency within the db and can not be used without
it. The log backups allow you to potentially restore to a particular point
in time under thright conditions. If all you care about is being able to
restore to the last Full backup (potentially loosing all transactions since
that last backup) you should place the database in SIMPLE recovery mode and
it will automatically truncate the log and not grow. See here for more
details:
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly SQL MVP
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0401131950.120fda92@.posting.google.com...
> I have been reading about the SQL Server transaction log and I'm still
> confused about a lot of things. First of all, what is this file
> actually used for? In the past when backing up my database, I only
> backup and restore the actual database and skip the log file and this
> has worked just fine in getting all my data back. Why do I have to
> backup the log file and when would I ever use it?
> I am having a problem now becuase my log file is over 4 GB for a 50 MB
> database and I'm not sure why. My application is a POS system where
> hundreds of transactions are entered daily and backups are done each
> night of the database but not the log. I can restore the database just
> fine without the log file, what is the advantage of backing up the log
> file also? I can't backup that much data so I'll need to make that
> much smaller if I did back it up. Based on my needs what is the best
> backup strategy and what would I lose by not backing up the log?
> Thanks.

database backup problem

I got error message, Error: 9002, Severity: 17, State: 6 The log file for database 'test' is full. Anybody can help me? why happen, how to fix, thanks in advance.I got that yesterday when trying to change a column width! Turned out the log file was set to a fixed size. As soon as I changed it to automatically grow the problem went away.|||i don't know if this'll work, but if the database is still operational, i think you could expand the database size. this should cancel out the "Full" error message. It allows you to do it 6.5 and 7, but i can't remember off the top of my head how to do it in 2000 (if that's what you're running)|||Grahamt: can you describe the step of how to I changed it to automatically grow . I appreciate it. Thanks|||Cruickshanks: i am running on sql server 2000|||Right-click the DB in Enterprise Mangler
Click the Transaction Log Tab at the top
Make sure the "Automatically Grow file" and "Unrestricted file growth" are both checked.

See if that works. :)|||I check the transaction log, "Automatically Grow file" and "Unrestricted file growth" are both checked. What happen?|||Disk full?|||If the disk is full...just try truncating the log . in query analyzer execute

use test
go

back up log test with truncate_only .

This should resolve your problem. Also try to create a maintenance plan .|||Praveenvc: Why need to trunck the log file, is the log file locate in the management\SQL Sever Agent\SQL Sever Log, but that is not two big, only have 7 logfile there. Another thing, after you trunck the log file, how to do mantainance plan, can you give me more detail as i am a new with sql server . Thanks again!

database backup not clearing the transaction log...

Hi
I have a 150Gb 'time series' type database running in SQL 2000. every sunday
@. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
creates a big fat transaction log.
At 0030 Monday morning the database is backed up using
BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
NOFORMAT
which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
cleared at 0800 Monday morning when the trans log is backed up (and is
continually backed up every 10 minutes throughout the working day all week
until Saturday) using the following syntax:
surely the database backup at 0030 should clear down the size of the trans
log?

> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
Sure , it does not. You need to BACKUP LOG file in order to truncate
inactive portions in the LOG
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:11993BB5-D220-4AFF-99D1-F0C7FAE045F6@.microsoft.com...
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every
> sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD
> ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
|||Methodology wrote:
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
transaction log, only a log backup will do that (assuming Full or
Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
of the log file will not change when truncating. Shrinking is a
separate process, and there are many reasons why you don't want to do it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Here is an idea. I did this once at one job I had. I had a similarlarly
large sized databases whose transaction logs "blew out" on a reindex
(weekly). I had a lot of ugly clustered indexes that had to be rebuilt
and took a lot of space. SInce the database was not heavily used during
that period, I did an alter database and put the db on simple mode.
Then it won't blow out the log. I did the re-index on all necessary
tables and then did a full backup. Note, this is dangerous as you break
the transaction log sequence at that time, so you must do an immediate
full backup. In my case, this was ok as it was a mainly Mon-Sat db.
KR
Tracy McKibben wrote:
> Methodology wrote:
> A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
> transaction log, only a log backup will do that (assuming Full or
> Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
> of the log file will not change when truncating. Shrinking is a
> separate process, and there are many reasons why you don't want to do it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
sql

database backup not clearing the transaction log...

Hi
I have a 150Gb 'time series' type database running in SQL 2000. every sunday
@. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
creates a big fat transaction log.
At 0030 Monday morning the database is backed up using
BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NO
UNLOAD ,
NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
NOFORMAT
which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
cleared at 0800 Monday morning when the trans log is backed up (and is
continually backed up every 10 minutes throughout the working day all week
until Saturday) using the following syntax:
surely the database backup at 0030 should clear down the size of the trans
log?
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
Sure , it does not. You need to BACKUP LOG file in order to truncate
inactive portions in the LOG
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:11993BB5-D220-4AFF-99D1-F0C7FAE045F6@.microsoft.com...
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every
> sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT ,
NOUNLOAD
> ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>|||Methodology wrote:
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every sund
ay
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT ,
NOUNLOAD ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is onl
y
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
transaction log, only a log backup will do that (assuming Full or
Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
of the log file will not change when truncating. Shrinking is a
separate process, and there are many reasons why you don't want to do it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Here is an idea. I did this once at one job I had. I had a similarlarly
large sized databases whose transaction logs "blew out" on a reindex
(weekly). I had a lot of ugly clustered indexes that had to be rebuilt
and took a lot of space. SInce the database was not heavily used during
that period, I did an alter database and put the db on simple mode.
Then it won't blow out the log. I did the re-index on all necessary
tables and then did a full backup. Note, this is dangerous as you break
the transaction log sequence at that time, so you must do an immediate
full backup. In my case, this was ok as it was a mainly Mon-Sat db.
KR
Tracy McKibben wrote:
> Methodology wrote:
> A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
> transaction log, only a log backup will do that (assuming Full or
> Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
> of the log file will not change when truncating. Shrinking is a
> separate process, and there are many reasons why you don't want to do it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Database Backup in 0.5 B and Transaction log backup in GBs

SQL gurus,
Simple question:
Our database is 470MB and transaction log has become 18GB! (Clustered). I
see local Fixed drives in both the SQL boxes from 'System Information' and
disk manager where data is being stored.
We are backing up database once in 24 hours and transaction logs every two
hours during business hours and full transaction log at night.
Simple Question is if we are backing full transaction log at night then
isn't supposed to make the transaction log to "zero" bytes or say clear the
transaction log automatically? After all when transaction logs have been
backup then why the transaction log continue to grow'
If the database is crashed what would be the sequence to recover:
Restore database in restore mode then restore first transaction log backup
taken after full backup of database and then restore the next backup taken
of transaction log and so on. (to apply in sequence starting from the first
one backup of transaction log taken).
Now question is :
Situation #1
1) if database is crashed and we still have transaction logs then can we
restore transaction logs (not backup of transaction logs) at the end of
procedure to restore as given above'
Situation #2
2) database is crashed and we do not have transaction logs then we can
restore as given in the procedure above except that in the last stage we
keep "no restore mode"'
Thanks
MeiNarendra
All of these issues are described on BOL very well.
Please refer to BOL.
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically? After all when transaction logs have been
> backup then why the transaction log continue to grow'
> If the database is crashed what would be the sequence to recover:
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
> Thanks
> Mei
>|||see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically?
Backing up the log does truncate the log( making space available for
re-use), but it does not shrink the log. Long running transactions can cause
the log to grow larger than you intended. After backing up the log, you may
use dbcc shrinkfile on the log file to reduce its physical size.
After all when transaction logs have been
> backup then why the transaction log continue to grow'
The only explanation I can think of is long running transactions...
> If the database is crashed what would be the sequence to recover:
>
set the db to dbo use only and kick off all users
backup the log with truncate_only
Restore the last whole data backup
restore each transaction log in the proper sequence.
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
No, restore the log backups
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
Yes, you may restore the whole database backup and allow recovery to run.
The database will be available, but you will have lost all of the data which
was in the transaction logs which you did not restore.
> Thanks
> Mei
>

Database Backup for Log Shipping

Hi,
Does the Log Shipping Setup wizard provide any options to do the database
backup and restore after the initialization? what if the table structures
are changed and stored procedures are added? If not, does that mean I have
to start over the setup process again?
THanks
EdI am a little as to what you are asking as Log shipping is simply
Backup and Restore that is somewhat automated. If you add a sp then when the
backup gets applied to the other server it will create that sp there as
well. You might want to read the sections in BooksOnLIne under Log Shipping.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:96DCEC95-6391-4BFA-A236-D04F26D5BD1E@.microsoft.com...
> Hi,
> Does the Log Shipping Setup wizard provide any options to do the database
> backup and restore after the initialization? what if the table structures
> are changed and stored procedures are added? If not, does that mean I
> have
> to start over the setup process again?
> THanks
> Ed|||Sorry for the question.
I have the log shipping setup and everything works fine. However, the
current database structure needs to be changed, do I need to start over to
setup the log shipping again or I can just easily copy and restored the
altered database to the standby server?
"Andrew J. Kelly" wrote:

> I am a little as to what you are asking as Log shipping is simply
> Backup and Restore that is somewhat automated. If you add a sp then when t
he
> backup gets applied to the other server it will create that sp there as
> well. You might want to read the sections in BooksOnLIne under Log Shippin
g.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:96DCEC95-6391-4BFA-A236-D04F26D5BD1E@.microsoft.com...
>
>|||Hi
The log contains both DML and DDL events, so if you make a change to a SP,
or a table, or a row of data, on the source server, the change will be in th
e
log backup, and since you are using log shipping, the change will be applied
to the destination database automatically.
Get "Inside SQL Server 2000" by Kalen Delaney to get a full unserstanding
how SQL Server works internally.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ed" wrote:
> Sorry for the question.
> I have the log shipping setup and everything works fine. However, the
> current database structure needs to be changed, do I need to start over to
> setup the log shipping again or I can just easily copy and restored the
> altered database to the standby server?
> "Andrew J. Kelly" wrote:
>|||Yes, this is the case. It is something that I don't know...
How about Trasactional Replication? I don't think the transfered Reader Log
contains all the DML and DDL, am I right?
Ed
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The log contains both DML and DDL events, so if you make a change to a SP,
> or a table, or a row of data, on the source server, the change will be in
the
> log backup, and since you are using log shipping, the change will be appli
ed
> to the destination database automatically.
> Get "Inside SQL Server 2000" by Kalen Delaney to get a full unserstanding
> how SQL Server works internally.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Ed" wrote:
>|||Replication and Log shipping are two different things. Replication may or
may not pass certain commands off to the subscriber but Log shipping will
make sure the other db is an exact duplicate of the first.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:BC2E60AA-5D35-4A7A-B992-50E7B782A267@.microsoft.com...
> Yes, this is the case. It is something that I don't know...
> How about Trasactional Replication? I don't think the transfered Reader
> Log
> contains all the DML and DDL, am I right?
> Ed
> "Mike Epprecht (SQL MVP)" wrote:
>