Greetings,
I've been sent an alternative "Query Analizer" thingy to play with and it seems to do the job - but it ain't my area.
I've posted the tool in a free public place for a short time:
http://www.adoanywhere.com/members/yeohray/7C1_SQLTool.zip
But, for the benefit of people reding this from the archives, please follow
the registerable author link [ http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1&TPN=1 ]
Apart from needing documentation (we all know about that old scenario) it seems good.
It didn't waste my time (and I wouldn't waste yours I hope). Could I have your comments please ?
Also, I'm looking for other tools that do the same as this one - so I can compare functionality. Even without try the freebie, any links please ?
TIAyou've "been sent"? by whom? where's it from? did you virus-check it?
okay, i tried it (yes, i took a chance, i'm stupid that way)
it actually works nicely -- a damn site better at submitting queries to MSDE than the way i was doing it up till now (via a pass-through query in access)
it's cute, too|||Good point about the security!
I'll address that issue with the author, virus scanners here should have picked them up though.
I got it from a chap I was assisting with some Delphi knowledge.
Thanks for checking it out, I didn't understand all of it myself.|||so, this isn't yours, then? i mean, it isn't adoanywhere's? don't they have something similar? how come you posted it on their forum? do you work for adoanywhere? do they mind you mentioning what appears to be a competing product? just what does their product do, anyway? and where did you say you got this little Delphi app, in case it screws somethying up on my machine and i feel like visiting the said chap at his place of residence with a clue-by-four?
:D|||Ther author was having problems with bugs in Delphi and retrieval of stats & plans through ADO. With adoanywhere forum help he eventually got it resolved and posted the tool for us to play with. I've used it witout problems since it was uploaded but I'll make it clearer in future posts and on the forum that all these third party apps are at downloaders risk.
I work for adoanwyhere - I wrote adoanywhere, and it's no problem adding other tools to forum if you know of any. Glad to 'ave 'em.
Mike (adoanywhere)|||okay, thanks for the clarification
by the way, does your product allow me to run queries against MSDE too?
i should mention i haven't a clue what ADO is (and no particular desire to find out)|||Yeah, you can query MSDE with adoanywhere.
ADO is an interface (bit like ODBC), so you can query databases that have and ADO "driver" - called provider in ado lingo.
Most databses have an ADO provider. Problem is though that ADO has loads of properties are that may or may not be available at any give time in your application.
Adoanywhere lets you fiddle with the ado settings. So although you can query data with it, it does more complex stuff at its heart.
I generally use adoanywhere for querying & checking ado properties - then switch to proprietry tools (like query analizer and Enterprise manager) for database management stuff. Horses for courses as they say.
All the best, Mike.sql
Showing posts with label greetings. Show all posts
Showing posts with label greetings. Show all posts
Friday, March 30, 2012
Monday, March 19, 2012
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
| |
|
|
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
| |
|
|
Subscribe to:
Posts (Atom)