Issues with Transaction Log backups

I'm pretty new to SQL, and am running into a problem. I had setup a
Maintenance plan on one of my databases and the Transaction Log Backups are
not working. I'm not getting any feedback in the error on why they are not
working. This is the error that I get:
JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
The last step to run was step 1 (Step 1).
Does anyone know what could be causing the error? I first thought it was
because of the account assigned to run the job, so I switched it from the
administrator to the SA account. But, still fails. Any suggestions on what
could be going on would be appreciated...My daily backups configured in the
Maintenance plan of the database are working though.
Thank you, Sara
Thanks!Saral6978 wrote:
> I'm pretty new to SQL, and am running into a problem. I had setup a
> Maintenance plan on one of my databases and the Transaction Log Backups are
> not working. I'm not getting any feedback in the error on why they are not
> working. This is the error that I get:
> JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
> Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
> DURATION: 0 hours, 0 minutes, 0 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
> The last step to run was step 1 (Step 1).
> Does anyone know what could be causing the error? I first thought it was
> because of the account assigned to run the job, so I switched it from the
> administrator to the SA account. But, still fails. Any suggestions on what
> could be going on would be appreciated...My daily backups configured in the
> Maintenance plan of the database are working though.
> Thank you, Sara
> Thanks!
>
>
Are you possibly trying to do a transaction log backup on a database
that is in Simple recovery mode?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I guess I'm not sure...how would I know that?
"Tracy McKibben" wrote:
> Saral6978 wrote:
> > I'm pretty new to SQL, and am running into a problem. I had setup a
> > Maintenance plan on one of my databases and the Transaction Log Backups are
> > not working. I'm not getting any feedback in the error on why they are not
> > working. This is the error that I get:
> >
> > JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
> > Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
> > DURATION: 0 hours, 0 minutes, 0 seconds
> > STATUS: Failed
> > MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
> > The last step to run was step 1 (Step 1).
> >
> > Does anyone know what could be causing the error? I first thought it was
> > because of the account assigned to run the job, so I switched it from the
> > administrator to the SA account. But, still fails. Any suggestions on what
> > could be going on would be appreciated...My daily backups configured in the
> > Maintenance plan of the database are working though.
> >
> > Thank you, Sara
> >
> > Thanks!
> >
> >
> >
> Are you possibly trying to do a transaction log backup on a database
> that is in Simple recovery mode?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> I guess I'm not sure...how would I know that?
>
Check the database properties of each database you're attempting to back
up. If the recovery model is "Simple", you can't (nor do you need to)
run a transaction log backup against that database. Consult Books
Online to learn what the various recovery models (Simple, Full,
Bulk-Logged) mean and what they offer.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
course on SQL in the next month, so I'm sure that will help too. Thank you
so much for your input.
"Tracy McKibben" wrote:
> Saral6978 wrote:
> > I guess I'm not sure...how would I know that?
> >
> Check the database properties of each database you're attempting to back
> up. If the recovery model is "Simple", you can't (nor do you need to)
> run a transaction log backup against that database. Consult Books
> Online to learn what the various recovery models (Simple, Full,
> Bulk-Logged) mean and what they offer.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
> course on SQL in the next month, so I'm sure that will help too. Thank you
> so much for your input.
>
No problem. I won't go into my rant about the maintenance plan wizards
allowing you to do this to yourself... After you finish your course,
you should learn how to write your own maintenance scripts without
relying on those wizards.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Aww, c'mon Tracy. We love your rants. ;)
To the poster, I found that the SQL Server Unleashed book is invaluable
as a resource. I picked it up on Ebay for under $30 and I use it
nearly every day.
Tracy McKibben wrote:
> Saral6978 wrote:
> > Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
> > course on SQL in the next month, so I'm sure that will help too. Thank you
> > so much for your input.
> >
> No problem. I won't go into my rant about the maintenance plan wizards
> allowing you to do this to yourself... After you finish your course,
> you should learn how to write your own maintenance scripts without
> relying on those wizards.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Issues with Transaction Log backups

I'm pretty new to SQL, and am running into a problem. I had setup a
Maintenance plan on one of my databases and the Transaction Log Backups are
not working. I'm not getting any feedback in the error on why they are not
working. This is the error that I get:
JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
The last step to run was step 1 (Step 1).
Does anyone know what could be causing the error? I first thought it was
because of the account assigned to run the job, so I switched it from the
administrator to the SA account. But, still fails. Any suggestions on what
could be going on would be appreciated...My daily backups configured in the
Maintenance plan of the database are working though.
Thank you, Sara
Thanks!Saral6978 wrote:
> I'm pretty new to SQL, and am running into a problem. I had setup a
> Maintenance plan on one of my databases and the Transaction Log Backups ar
e
> not working. I'm not getting any feedback in the error on why they are no
t
> working. This is the error that I get:
> JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
> Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
> DURATION: 0 hours, 0 minutes, 0 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
> The last step to run was step 1 (Step 1).
> Does anyone know what could be causing the error? I first thought it was
> because of the account assigned to run the job, so I switched it from the
> administrator to the SA account. But, still fails. Any suggestions on wh
at
> could be going on would be appreciated...My daily backups configured in th
e
> Maintenance plan of the database are working though.
> Thank you, Sara
> Thanks!
>
>
Are you possibly trying to do a transaction log backup on a database
that is in Simple recovery mode?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I guess I'm not sure...how would I know that?
"Tracy McKibben" wrote:

> Saral6978 wrote:
> Are you possibly trying to do a transaction log backup on a database
> that is in Simple recovery mode?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> I guess I'm not sure...how would I know that?
>
Check the database properties of each database you're attempting to back
up. If the recovery model is "Simple", you can't (nor do you need to)
run a transaction log backup against that database. Consult Books
Online to learn what the various recovery models (Simple, Full,
Bulk-Logged) mean and what they offer.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
course on SQL in the next month, so I'm sure that will help too. Thank you
so much for your input.
"Tracy McKibben" wrote:

> Saral6978 wrote:
> Check the database properties of each database you're attempting to back
> up. If the recovery model is "Simple", you can't (nor do you need to)
> run a transaction log backup against that database. Consult Books
> Online to learn what the various recovery models (Simple, Full,
> Bulk-Logged) mean and what they offer.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
> course on SQL in the next month, so I'm sure that will help too. Thank yo
u
> so much for your input.
>
No problem. I won't go into my rant about the maintenance plan wizards
allowing you to do this to yourself... After you finish your course,
you should learn how to write your own maintenance scripts without
relying on those wizards.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Aww, c'mon Tracy. We love your rants. ;)
To the poster, I found that the SQL Server Unleashed book is invaluable
as a resource. I picked it up on Ebay for under $30 and I use it
nearly every day.
Tracy McKibben wrote:
> Saral6978 wrote:
> No problem. I won't go into my rant about the maintenance plan wizards
> allowing you to do this to yourself... After you finish your course,
> you should learn how to write your own maintenance scripts without
> relying on those wizards.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Issues PLEASE HELP

i am running the following and i keep getting process errors " can not read next data row for data set SERVER. transaction process id 838 was deadlocked on lock comminication buffer resources with another process and has been chosen as the deadlock victum rerun transactions."

SELECT transactions.practice_id, practice.practice_name, CONVERT(smalldatetime, transactions.closing_date) AS Date, transactions.type,

ISNULL(SUM(trans_detail.paid_amt), 0) AS Pmts, ISNULL(SUM(trans_detail.adj_amt), 0) AS Adjs, 0 AS NSF, transactions.tran_code_id, 0 AS Debits,

0 AS Credits, 0 AS Capitation, 0 AS UnAppl, trans_detail.system_charge_code,''

FROM provider_mstr RIGHT OUTER JOIN

charges ON provider_mstr.provider_id = charges.rendering_id LEFT OUTER JOIN

location_mstr ON charges.location_id = location_mstr.location_id RIGHT OUTER JOIN

transactions INNER JOIN

trans_detail ON transactions.trans_id = trans_detail.trans_id INNER JOIN

practice ON transactions.practice_id = practice.practice_id ON charges.charge_id = trans_detail.charge_id

where (CONVERT(smalldatetime, transactions.closing_date) BETWEEN @.begin_date AND @.end_date) and (transactions.practice_id = @.practice_id) and (convert(char(36), charges.rendering_id) like @.provider_id) and (convert(char(36), charges.location_id) like @.location_id)

GROUP BY transactions.practice_id, CONVERT(smalldatetime, transactions.closing_date), transactions.type, practice.practice_name, transactions.tran_code_id,

trans_detail.system_charge_code

HAVING (transactions.type = 'C' OR transactions.type = 'R')

AND transactions.tran_code_id NOT IN ('14759070-3370-4E4D-B752-01AE84A350EF', '23783F6F-8661-4785-922C-F55C154349F9', '36037DC4-4799-4E84-9AAB-78BE4F271CCD', '5F1665EE-08DC-47FF-8BC4-6F4BA6CC84C0',

'39137DD5-796F-4824-860B-EBD00D66CBB0') AND (trans_detail.system_charge_code is null)

UNION

SELECT tr.practice_id, pr.practice_name, CONVERT(smalldatetime, tr.closing_date) AS Date, tr.type, 0 AS Pmts, 0 AS Adjs, ISNULL(SUM(td.adj_amt), 0) AS NSF,

tr.tran_code_id, 0, 0, 0, 0, '',''

FROM provider_mstr pm1 RIGHT OUTER JOIN

charges c1 ON pm1.provider_id = c1.rendering_id LEFT OUTER JOIN

location_mstr lm1 ON c1.location_id = lm1.location_id RIGHT OUTER JOIN

transactions tr INNER JOIN

trans_detail td ON tr.trans_id = td.trans_id INNER JOIN

practice pr ON tr.practice_id = pr.practice_id ON c1.charge_id = td.charge_id

where (CONVERT(smalldatetime, tr.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c1.rendering_id) like @.provider_id) and (convert(char(36), c1.location_id) like @.location_id)

GROUP BY tr.practice_id, CONVERT(smalldatetime, tr.closing_date), tr.type, pr.practice_name, tr.tran_code_id

HAVING (tr.practice_id = @.practice_id) AND (tr.type = 'A') AND

(tr.tran_code_id = 'CEC50D49-3317-404D-A082-36610DAE6F0F')

UNION

SELECT tn.practice_id, prs.practice_name, CONVERT(smalldatetime, tn.closing_date) AS Date, tn.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF, tn.tran_code_id,

isnull(SUM(tds.paid_amt), 0) AS Debits, 0, 0, 0, '',''

FROM provider_mstr pm2 RIGHT OUTER JOIN

charges c2 ON pm2.provider_id = c2.rendering_id LEFT OUTER JOIN

location_mstr lm2 ON c2.location_id = lm2.location_id RIGHT OUTER JOIN

transactions tn INNER JOIN

trans_detail tds ON tn.trans_id = tds.trans_id INNER JOIN

practice prs ON tn.practice_id = prs.practice_id ON c2.charge_id = tds.charge_id

where (CONVERT(smalldatetime, tn.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c2.rendering_id) like @.provider_id) and (convert(char(36), c2.location_id) like @.location_id)

GROUP BY tn.practice_id, CONVERT(smalldatetime, tn.closing_date), tn.type, prs.practice_name, tn.tran_code_id

HAVING (tn.practice_id = @.practice_id) AND (tn.type = 'C') AND

(tn.tran_code_id = '14759070-3370-4E4D-B752-01AE84A350EF' OR

tn.tran_code_id = '23783F6F-8661-4785-922C-F55C154349F9' OR tn.tran_code_id='B6BA0D7D-BAC6-4653-9050-C5C5E8029DCB') AND isnull(SUM(tds.paid_amt), 0) > 0

UNION

SELECT trs.practice_id, ps.practice_name, CONVERT(smalldatetime, trs.closing_date) AS Date, trs.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF, trs.tran_code_id, 0,

isnull(SUM(ts .paid_amt), 0) AS Credits, 0, 0, '',''

FROM provider_mstr pm3 RIGHT OUTER JOIN

charges c3 ON pm3.provider_id = c3.rendering_id LEFT OUTER JOIN

location_mstr lm3 ON c3.location_id = lm3.location_id RIGHT OUTER JOIN

transactions trs INNER JOIN

trans_detail ts ON trs.trans_id = ts .trans_id INNER JOIN

practice ps ON trs.practice_id = ps.practice_id ON c3.charge_id = ts .charge_id

where (CONVERT(smalldatetime, trs.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c3.rendering_id) like @.provider_id) and (convert(char(36), c3.location_id) like @.location_id)

GROUP BY trs.practice_id, CONVERT(smalldatetime, trs.closing_date), trs.type, ps.practice_name, trs.tran_code_id

HAVING (trs.practice_id = @.practice_id) AND (trs.type = 'C') AND

(trs.tran_code_id = '14759070-3370-4E4D-B752-01AE84A350EF' OR

trs.tran_code_id = '23783F6F-8661-4785-922C-F55C154349F9' OR trs.tran_code_id='B6BA0D7D-BAC6-4653-9050-C5C5E8029DCB') AND isnull(SUM(ts .paid_amt), 0) < 0

UNION

SELECT tran1.practice_id, ps1.practice_name, CONVERT(smalldatetime, tran1.closing_date) AS Date, tran1.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF,

tran1.tran_code_id, 0, 0, isnull(SUM(ts1.paid_amt), 0), 0, '',''

FROM provider_mstr pm4 RIGHT OUTER JOIN

charges c4 ON pm4.provider_id = c4.rendering_id LEFT OUTER JOIN

location_mstr lm4 ON c4.location_id = lm4.location_id RIGHT OUTER JOIN

transactions tran1 INNER JOIN

trans_detail ts1 ON tran1.trans_id = ts1.trans_id INNER JOIN

practice ps1 ON tran1.practice_id = ps1.practice_id ON c4.charge_id = ts1.charge_id

where (CONVERT(smalldatetime, tran1.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c4.rendering_id) like @.provider_id) and (convert(char(36), c4.location_id) like @.location_id)

GROUP BY tran1.practice_id, CONVERT(smalldatetime, tran1.closing_date), tran1.type, ps1.practice_name, tran1.tran_code_id

HAVING (tran1.practice_id = @.practice_id) AND (tran1.type = 'C')

AND (tran1.tran_code_id IN ('36037DC4-4799-4E84-9AAB-78BE4F271CCD', '5F1665EE-08DC-47FF-8BC4-6F4BA6CC84C0', '39137DD5-796F-4824-860B-EBD00D66CBB0'))

UNION

SELECT tran2.practice_id, ps2.practice_name, CONVERT(smalldatetime, tran2.closing_date) AS Date, tran2.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF,

tran2.tran_code_id, 0, 0, 0, isnull(SUM(ts2.paid_amt), 0), ts2.system_charge_code,''

FROM provider_mstr pm5 RIGHT OUTER JOIN

charges c5 ON pm5.provider_id = c5.rendering_id LEFT OUTER JOIN

location_mstr lm5 ON c5.location_id = lm5.location_id RIGHT OUTER JOIN

transactions tran2 INNER JOIN

trans_detail ts2 ON tran2.trans_id = ts2.trans_id INNER JOIN

practice ps2 ON tran2.practice_id = ps2.practice_id ON c5.charge_id = ts2.charge_id

where (CONVERT(smalldatetime, tran2.closing_date) BETWEEN @.begin_date AND @.end_date) AND (isnull(convert(char(36), c5.rendering_id),'') like @.provider_id) and (isnull(convert(char(36), c5.location_id),'') like @.location_id)

GROUP BY tran2.practice_id, CONVERT(smalldatetime, tran2.closing_date), tran2.type, ps2.practice_name, tran2.tran_code_id, ts2.system_charge_code

HAVING (tran2.practice_id = @.practice_id) AND (tran2.type = 'C')

AND ts2.system_charge_code is not null

ORDER BY CONVERT(smalldatetime, transactions.closing_date)

Try moving all your "HAVING" clauses to WHERE clauses.

HAVING is for calculated fields. Your fields appear to be static, except for the "isnull(SUM(ts .paid_amt), 0) < 0" which will need to be in the HAVING clause.

I would suspect it is retrieving all records, multiple times, to check the having clauses, when it doesn't need too.

|||Also, try posting this to the TSQL forum, you will probably have better luck.

Issues Minimizing Size of LDF Transaction Log on SQL Server 2000

Greetings,
I'm trying to minimize my 40gb .ldf transaction log. First, I tried shrinking just the transaction log by using both the Shrink Database > Files method and by scripting (DBCC SHRINKFILE) and while the transaction log seemed to go down quite a bit according to the Enterprise Manager statistics, the LDF sitting in our Program Files folder remains at about 37gb.
I then read somewhere that backing up the transaction log can also aid in minimizing its size but this has yet to make any kind of a difference. In addition, I'm also seeing here on the newsgroup that converting the database to simple recovery mode can help keep the LDF to a good size but, according to MS documentation, "there are limitations placed on the recoverability of a database if this recovery model is used." That sounds a little too eerie to me as this particular DB is quite crucial to our operation. Does anyone have any tricks to get a nearly 40gb LDF down to a decent size?
Thanks in advance for any and al information!
- J
The first and most important thing is that you understand what the
transaction log is for and how to use it for backup and recovery purposes.
It seems obvious from several things you have said that you need to do some
reading. Please study the backup and recovery topics in Books Online and
review your backup and recovery strategy before deciding how to proceed.
If you are running under full recovery then you need to make sure your
transaction log is large enough to accommodate all transactions between each
log backup. The correct size for the transaction log will therefore be
determined by how many updates your database has and how often you do
backups. Note that backing up the log will not reduce its size but it will
allow SQL Server to re-use that space. On the information given therefore
there is no reason to believe you need do anything immediately. What makes
you want to reduce the size of the log?
David Portas
|||Dear Jason,
Thank you for posting here.
Before running the DBCC SHRINKFILE to shrink the Transaction Log, we must
first run a BACKUP LOG statement to backup the current Transaction Log. As
mentioned by David, although backing up the log will not directly reduce
its size, it removes the inactive portion of the log and then "DBCC
SHRINKFILE" command will be able to free these spaces. For your reference,
the following actions are required to complete the shrinking of the
transaction log:
1. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size.
Below is the related KB article:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318
Additionally, although Simple Recovery mode can keep the LDF file to a
smaller size, there are some limitation s exist. For example, we are unable
to recover to a specific point in time and can only recover to the end of a
backup. This recovery mode is NOT recommended for the database in the
production environment. You can refer to the SQL Server Book Online about
this topic (http://msdn2.microsoft.com/en-us/library/ms189275.aspx)
Hope it helps.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Jason H." <cupahr@.community.nospam>
| Subject: Issues Minimizing Size of LDF Transaction Log on SQL Server 2000
| Date: Thu, 13 Mar 2008 15:10:51 -0400
| Lines: 69
| Organization: CUPA-HR
| Message-ID: <CB7EEA2C-2E49-43E1-9491-E4082D529223@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: multipart/alternative;
| boundary="--=_NextPart_000_0028_01C8851C.6D2EC7E0"
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16480
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16545
| X-MS-CommunityGroup-PostID: {CB7EEA2C-2E49-43E1-9491-E4082D529223}
| X-MS-CommunityGroup-MessageCategory:
{E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: mail.cupahr.org 65.23.122.33
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:38532
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Greetings,
| I'm trying to minimize my 40gb .ldf transaction log. First, I tried
shrinking just the transaction log by using both the Shrink Database >
Files method and by scripting (DBCC SHRINKFILE) and while the transaction
log seemed to go down quite a bit according to the Enterprise Manager
statistics, the LDF sitting in our Program Files folder remains at about
37gb.
| I then read somewhere that backing up the transaction log can also aid in
minimizing its size but this has yet to make any kind of a difference. In
addition, I'm also seeing here on the newsgroup that converting the
database to simple recovery mode can help keep the LDF to a good size but,
according to MS documentation, "there are limitations placed on the
recoverability of a database if this recovery model is used." That sounds
a little too eerie to me as this particular DB is quite crucial to our
operation. Does anyone have any tricks to get a nearly 40gb LDF down to a
decent size?
| Thanks in advance for any and al information!
| - J
|
|||Dear Jason,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 89922039
| References: <CB7EEA2C-2E49-43E1-9491-E4082D529223@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Fri, 14 Mar 2008 09:46:48 GMT
| Subject: RE: Issues Minimizing Size of LDF Transaction Log on SQL Server
2000
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <LixsThbhIHA.360@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 99
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:38573
| NNTP-Posting-Host: tk5tomimport2.phx.gbl 10.201.218.20
|
| Dear Jason,
|
| Thank you for posting here.
|
| Before running the DBCC SHRINKFILE to shrink the Transaction Log, we must
| first run a BACKUP LOG statement to backup the current Transaction Log.
As
| mentioned by David, although backing up the log will not directly reduce
| its size, it removes the inactive portion of the log and then "DBCC
| SHRINKFILE" command will be able to free these spaces. For your
reference,
| the following actions are required to complete the shrinking of the
| transaction log:
|
| 1. You must run a BACKUP LOG statement to free up space by removing the
| inactive portion of the log.
| 2. You must run DBCC SHRINKFILE again with the desired target size until
| the log file shrinks to the target size.
|
| Below is the related KB article:
|
| INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
| http://support.microsoft.com/kb/272318
|
| Additionally, although Simple Recovery mode can keep the LDF file to a
| smaller size, there are some limitation s exist. For example, we are
unable
| to recover to a specific point in time and can only recover to the end of
a
| backup. This recovery mode is NOT recommended for the database in the
| production environment. You can refer to the SQL Server Book Online about
| this topic (http://msdn2.microsoft.com/en-us/library/ms189275.aspx)
|
| Hope it helps.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu, MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| ================================================== ===
| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| ================================================== ===
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
| --
| | From: "Jason H." <cupahr@.community.nospam>
| | Subject: Issues Minimizing Size of LDF Transaction Log on SQL Server
2000
| | Date: Thu, 13 Mar 2008 15:10:51 -0400
| | Lines: 69
| | Organization: CUPA-HR
| | Message-ID: <CB7EEA2C-2E49-43E1-9491-E4082D529223@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: multipart/alternative;
| | boundary="--=_NextPart_000_0028_01C8851C.6D2EC7E0"
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Windows Mail 6.0.6000.16480
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16545
| | X-MS-CommunityGroup-PostID: {CB7EEA2C-2E49-43E1-9491-E4082D529223}
| | X-MS-CommunityGroup-MessageCategory:
| {E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: mail.cupahr.org 65.23.122.33
| | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:38532
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Greetings,
| | I'm trying to minimize my 40gb .ldf transaction log. First, I tried
| shrinking just the transaction log by using both the Shrink Database >
| Files method and by scripting (DBCC SHRINKFILE) and while the transaction
| log seemed to go down quite a bit according to the Enterprise Manager
| statistics, the LDF sitting in our Program Files folder remains at about
| 37gb.
| | I then read somewhere that backing up the transaction log can also aid
in
| minimizing its size but this has yet to make any kind of a difference.
In
| addition, I'm also seeing here on the newsgroup that converting the
| database to simple recovery mode can help keep the LDF to a good size
but,
| according to MS documentation, "there are limitations placed on the
| recoverability of a database if this recovery model is used." That
sounds
| a little too eerie to me as this particular DB is quite crucial to our
| operation. Does anyone have any tricks to get a nearly 40gb LDF down to
a
| decent size?
| | Thanks in advance for any and al information!
| | - J
| |
|
|

Issueing a CREATE TABLE statement within a transaction

Sorry new to the newsgroups so please point me to where I need to be if this is the wrong group. Below is a chunck of code that I have written to play around with transactions in Visual Studion 2005. I want to be able to create a table, the columns in it, and add rows of data and if any of those statements fail I woudl like to rollback the entire transaction. My testing has shown that when the CREATE TABLE sql is issued it automatcially commits. In my example below the VER_INFO table is immediately created when the ExecuteNonQuery() statement is invoked. The next 2 insert statements are held correctly in the transaction, however when the second table, WADES_TEST is created that causes the 2 rows to be inserted and the second table is immediately created. So my commit statement is useless. Is this normal? Is there a way to force the system to keep the CREATE TABLE statement in the transaction so I can roll it back if needed?

OracleCommand cmd = new OracleCommand();

OracleConnection wConnection = new OracleConnection(wConnString);

wConnection.Open();

OracleTransaction trans = wConnection.BeginTransaction();

string wQry = "CREATE TABLE VER_INFO (VER NVARCHAR2(25))";

cmd.CommandText = wQry;

cmd.Connection = wConnection;

cmd.Transaction = trans;

try

{

cmd.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('INITIALIZED')";

OracleCommand cmd4 = new OracleCommand(wQry, wConnection, trans);

cmd4.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('testss')";

OracleCommand cmd2 = new OracleCommand(wQry, wConnection, trans);

cmd2.ExecuteNonQuery();

wQry = "CREATE TABLE WADES_TEST (WADE NVARCHAR2(25))";

OracleCommand cmd3 = new OracleCommand(wQry, wConnection, trans);

cmd3.ExecuteNonQuery();

trans.Commit();

wConnection.Dispose();

}

catch

{

trans.Rollback();

wConnection.Close();

}

Thanks in advance,

Wade Sharp

This might be more appropriate to be posted to an Oracle forum or newsgroup. Or to an ADO.NET forum.

Issue in Transaction

Dear All,

I'm very much new to SQL and right now working with basics before moving into complex SPs.

I'm facing issues in executing a simple transaction in sql. The following is the code:

"

BEGINTRANSACTION

INSERT INTO MyDatabase.dbo.Order (OrderID) values('1234')

INSERTINTO MyDatabase.dbo.MyTable (Age)values('we')

IF(@.@.ERROR<> 0)

BEGIN

PRINT('Exception Raised')

ROLLBACKTRANSACTION

END

COMMITTRANSACTION

"

The age column is of int data type and I'm trying to rollback transaction by passing char value to that column. On executing the above query, it does not go to rollback transaction part whereas it gives the error

"

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the varchar value 'we' to data type int.

"

Kindly request you all to help me in this regard.

Most conversion errors will fire a batch abort error, which will stop the batch from executing. If you are using SQL Server 2005, you can use the new error functionality which allows you to catch statement as well as batch abort errors.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Issolation level

When on Oracle in one transaction I modify data when I select data on other transaction I get old data (until commit on first transaction)

Using MS SQL I must wait or I get (dirty read) modified values
Example:

Transaction no 1:
begin transaction
update ckk_klienci set kl_skrot = 'KLINTIN' where kl_id = 300000001
-- commit

Transaction no2:
select kl_skrot from ckk_klienci where kl_id = 300000001

Until there is no commit on Transaction no 1 transaction no 2 waits (gives no answer) lowering issolation level I can get new value

Is it possible to configure MS SQL as it works like Oracle

Thanks for halp
I want to know; I don't want to say that this is wrong; I just want to know if it is possibleRE [Quote][Size=1]
When on Oracle in one transaction I modify data when I select data on other transaction I get old data (until commit on first transaction)
Using MS SQL I must wait or I get (dirty read) modified values
Example:
Transaction no 1:
begin transaction
update ckk_klienci set kl_skrot = 'KLINTIN' where kl_id = 300000001
-- commit
Transaction no2:
select kl_skrot from ckk_klienci where kl_id = 300000001
Until there is no commit on Transaction no 1 transaction no 2 waits (gives no answer) lowering issolation level I can get new value
Is it possible to configure MS SQL as it works like Oracle
Thanks for halp
I want to know; I don't want to say that this is wrong; I just want to know if it is possible
[\Quote][\Size]

Q1 [Is it possible to configure MS SQL as it works like Oracle?]
A1 Obviously they are different products; however you can certainly control transaction isolation levels as appropriate / needed. For example:

Set Transaction Isolation Level Read Committed