Comparing table structures between databases

Unfortunatly no there isn't an easy way.
If you have connection to the internet log on to the site
http://www.adeptsql.com/download.htm and download AdeptSQL
Diff ver 1.6.
We have used that for all our server and works very well.
N.B. You can program it yourself in SQL but will take a
bit of time, and this will hi-light every difference.
Peter
"What makes him think a middle aged actor [Clint
Eastwood], who's played with a chimp, could have a future
in politics?"
Ronald Reagan

>--Original Message--
>What is the easiest way to compare the table structure of
two databases?
>I have inherited a project and have found a couple of
undocumented
>inconsistencies between the development and production
databases.
>Is there an in-built function or a utility that can help
with this?
>Thanks,
>Mike
>
>.
>
Thanks, Peter.
That looks like it will do all I need and more.
Regards,
Mike
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4bbe01c490da$17ef8560$a601280a@.phx.gbl...[vbcol=seagreen]
> Unfortunatly no there isn't an easy way.
> If you have connection to the internet log on to the site
> http://www.adeptsql.com/download.htm and download AdeptSQL
> Diff ver 1.6.
> We have used that for all our server and works very well.
> N.B. You can program it yourself in SQL but will take a
> bit of time, and this will hi-light every difference.
> Peter
> "What makes him think a middle aged actor [Clint
> Eastwood], who's played with a chimp, could have a future
> in politics?"
> Ronald Reagan
>
> two databases?
> undocumented
> databases.
> with this?

Comparing Log Tools

I've only ever used built in SQL Server tools but have been looking at
log tools. I looked at Log Explorer 4.02 from lumigent.com and was very
impressed with what I saw. The prices tag per instance was a bit hefty
I felt though. I've been over to LogPI.com and am reading some of the
"features" I've yet to d/l the evaluation demo. The price is over half
that of Log Explorer (I'm converting to local currency on the fly
here).
Questions... logPI any good? Log Explorer worth the extra $?
Anyone using either and have any opinions to share?
Are 3rd party tools worthwhile?

Unrelated but for my info... does/will Yukon come with a similar built
in tool? I haven't had a chance to look at Yukon yet.
Cheers
Jay<JazeMail@.gmail.com> wrote:

> I've only ever used built in SQL Server tools but have been looking at
> log tools. I looked at Log Explorer 4.02 from lumigent.com and was very
> impressed with what I saw. The prices tag per instance was a bit hefty
> I felt though. I've been over to LogPI.com and am reading some of the
> "features" I've yet to d/l the evaluation demo. The price is over half
> that of Log Explorer (I'm converting to local currency on the fly
> here).
> Questions... logPI any good? Log Explorer worth the extra $?
> Anyone using either and have any opinions to share?
> Are 3rd party tools worthwhile?
> Unrelated but for my info... does/will Yukon come with a similar built
> in tool? I haven't had a chance to look at Yukon yet.
> Cheers
> Jay

Jay,

[I have no affiliation with Lumigent other than as a satisfied customer.]

When we bought Log Explorer (about 8 months ago), we didn't evaluate LogPI,
so I can't comment on it. However, we've been very happy with Log Explorer
and what we're able to do with it. I probably traded 4 or 5 emails with
technical staff and had one conference call before we bought it: they were
very knowledgeable and very forthcoming. After we purchased it, every email
I sent to our technical contact was answered promptly (some in less than 15
minutes). It's still my feeling that the technical and sales support we got
went beyond the norm for software in that price range.

Overall, we feel like we got much more than our money's worth. To put the
money in perspective, our company is considered small (privately owned, ca
150 employees) and while we're not averse to spending money it's not unusual
for us to go 3 - 12 months without spending any money on IT software.

Although we played with it extensively after we got it, our main reason to
buy was as a safety net (for when "OH CRAP!" was heard coming from a
developer's cubicle :). 8 months later, we actually get the most use out of
the various emails that can be sent by Log Explorer: now I actually know
when a developer decides to just recompile a new version of a stored proc on
the main database on our main data server. It's always nice to have
documentation before beating someone senseless :)

Of course, I'd have paid double if it had come with an SDK, but that's
another story entirely.

On the negative side, I have heard people (but I don't remember the source)
complain about tech support they received from Lumigent. We've had nothing
but positive things to say about them, but my experience isn't broad enough
to tell you if we're the exception or the norm.

Craig|||Many thanks for that Craig.
We have 2 developers here (myself and one other) and we do all testing
in the Dev environment before letting loose in the real world (99% of
the time if the truth be told) my interest in LE was the ability to
role back row level records (changes) rather than having to restore to
a point in time thus potentially losing other bonafide changes.
Jay|||"Jay" <JazeMail@.gmail.com> wrote:

> Many thanks for that Craig.
> We have 2 developers here (myself and one other) and we do all testing
> in the Dev environment before letting loose in the real world (99% of
> the time if the truth be told) my interest in LE was the ability to
> role back row level records (changes) rather than having to restore to
> a point in time thus potentially losing other bonafide changes.
> Jay

Jay,

You're welcome.

That's originally why we bought it as well (and the fact that we could look
at row-level changes: our system isn't fully audited). It works well for
fixing problems, but the farther away you get from the modification, the
less useful it is (obviously from a data integrity standpoint, but also
because of the sheer volume of transactions you have to pour through,
although there is a search function).

"99% of the time if the truth be told"

We have four actual developers and that's about how we handle it: but the 1%
was getting kinda hairy for us :)

One thing that I forgot: we have an automated build procedure that (among
other things) checks table schema and scripts stored procedures from test to
production. Running one of these builds results in about 2500 procs getting
re-scripted on production... It was quite a bit of fun the night of the
first build after installing Log Explorer when the first of about 5000
emails started rolling in (one for the DROP and one for the CREATE).

The problem here is that Log Explorer provides no documented interface to
automatically turn those emails on and off. Their technical people helped
us with the necessary changes so our steps look like:

- Delete specific rows from a table in the Lumigent database

- Fire off a Log Explorer extended procedure to notify the engine things
have changed (named xp_LEServer_TraceConfigure)

- Run the rest of the automated build

- Re-insert the deleted rows

- Call xp_LEServer_TraceConfigure again

This didn't bother us but some might consider it a PITA, especially if you
aren't that concerned with the emails in the first place (although you can
just turn them off permanently).

Craig

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted.

Sincerely,
-Lawrence

I don't know anything about the transactions logs.

Bulk insert is generally much faster.

Jonathan

|||

BULK INSERT can be a minimally logged operation (depending on various parameters like indexes, constraints on the tables, recovery model of the database etc). Minimally logged operations only log allocations and deallocations. In case of BULK INSERT, only extent allocations are logged instead of the actual data being inserted. This will provide much better performance than INSERT. You can start with the links below for more information:

http://msdn2.microsoft.com/en-us/library/ms190421.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT
vs INSERT? Performance speaking, which operations is generally faster given
the same amout of data inserted.
Sincerely,
-LawrenceIf conditions are right (e.g. destination table is not replicated), BULK
INSERT can exploit minimal transaction logging for performance advantage.
Linchi
"Lawrence" wrote:
> Hello,
> I am wondering is the Transaction Log logged differently between BULK INSERT
> vs INSERT? Performance speaking, which operations is generally faster given
> the same amout of data inserted.
> Sincerely,
> -Lawrence
>|||I should add that in SQL2005, INSERT can make use of the bulk rowset provider
in the OpenRowset function. But I have not tested the performance difference,
if any, between BULK INSERT and INSERT ... SELECT FROM OpenRowSet(bulk...).
Linchi
"Linchi Shea" wrote:
> If conditions are right (e.g. destination table is not replicated), BULK
> INSERT can exploit minimal transaction logging for performance advantage.
> Linchi
> "Lawrence" wrote:
> > Hello,
> > I am wondering is the Transaction Log logged differently between BULK INSERT
> > vs INSERT? Performance speaking, which operations is generally faster given
> > the same amout of data inserted.
> >
> > Sincerely,
> > -Lawrence
> >|||"Lawrence" <Lawrence@.discussions.microsoft.com> wrote in message
news:097C24AA-AE0A-45DC-9C2D-B09AAD9A4539@.microsoft.com...
> Hello,
> I am wondering is the Transaction Log logged differently between BULK
INSERT
> vs INSERT? Performance speaking, which operations is generally faster
given
> the same amout of data inserted.
>
BULK INSERT can be incredibly fast compared to INSERT, simply because of how
it can handle the logging.
> Sincerely,
> -Lawrence
>

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT
vs INSERT? Performance speaking, which operations is generally faster given
the same amout of data inserted.
Sincerely,
-LawrenceIf conditions are right (e.g. destination table is not replicated), BULK
INSERT can exploit minimal transaction logging for performance advantage.
Linchi
"Lawrence" wrote:

> Hello,
> I am wondering is the Transaction Log logged differently between BULK INSE
RT
> vs INSERT? Performance speaking, which operations is generally faster giv
en
> the same amout of data inserted.
> Sincerely,
> -Lawrence
>|||I should add that in SQL2005, INSERT can make use of the bulk rowset provide
r
in the OpenRowset function. But I have not tested the performance difference
,
if any, between BULK INSERT and INSERT ... SELECT FROM OpenRowSet(bulk...).
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> If conditions are right (e.g. destination table is not replicated), BULK
> INSERT can exploit minimal transaction logging for performance advantage.
> Linchi
> "Lawrence" wrote:
>|||"Lawrence" <Lawrence@.discussions.microsoft.com> wrote in message
news:097C24AA-AE0A-45DC-9C2D-B09AAD9A4539@.microsoft.com...
> Hello,
> I am wondering is the Transaction Log logged differently between BULK
INSERT
> vs INSERT? Performance speaking, which operations is generally faster
given
> the same amout of data inserted.
>
BULK INSERT can be incredibly fast compared to INSERT, simply because of how
it can handle the logging.

> Sincerely,
> -Lawrence
>

compacting LOG file

how can you compact .log file ?
they can be > 20 gigas ! ...
can you delete it ?do you mean the log files that the server itself writes? Those have no file extension normally, usuall called something like ERRORLOG. when the server restarts it renames it to ERRORLOG.1 and starts a new one. I would be very surprised if you have a 20gb one though. that's pretty large. maybe you have a lot of failed login attempts (which are logged as I recall).

Or perhaps you mean a ldf file? These are transaction log files for a database - it's common for these to get large. You can shrink the size of the ldf using DBCC SHRINKFILE. You should not delete an ldf file unless you want to delete the database it's associated with.

Note that if you care about recoverability, you should be making regular backups of both your database and transaction log.|||thank you Jezemine

yes of course I mean transaction log files
DBCC SHRINKFILE ? i musyt look how to use it ? do you run it as a stored procedure ?

EXECUTE DBCC SHRINKFILE

thank you for helping|||it's not a sproc.

read about DBCC SHRINKFILE in bol.

Commit fail and transaction log is full

Hi,

Our testing server experienced a timeout exception when execute System.Data.SqlClient.SqlTransaction.Commit() in SQL Server 2000 thru .Net Framework 1.1. And this happened at 2007-02-13 18:07:05,954.
It was strange to us that all the insert statement can be executed without error within the transaction but the commit operation fails.
Moreover, after about 40 minutes, we found that the transaction log of this database is full.

Here is the exception and SQL Server 2000 Error Log:
Exception Stack Trace:
DateTime: 2007-02-13 18:07:05,954
Req Id: bccdae08-cc47-4f85-8f48-5f0b9dbbbf88
Exception: MyDatabaseException
Detail:
MyDatabaseException:
Index #0
Server: MySQLServer
Source: .Net SqlClient Data Provider
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
LineNumber: 0
Procedure: ConnectionRead (recv()).
State: 0
Error Number: -2 > System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ReadBuffer()
at System.Data.SqlClient.TdsParser.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlTransaction.Commit()

SQL Server Error Log:

2007-02-13 13:02:23.72 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944769, first LSN: 9434:22326:1, last LSN: 9434:22360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\MSSQL\Backup\MyDatabaseName.bak'}).
2007-02-13 15:50:52.40 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944970, first LSN: 9442:38096:1, last LSN: 9443:748:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'Legato#4f96edfd-7fdb-4cd1-a740-3fe9a54d66c6'}).
2007-02-13 18:48:51.42 spid66 Error: 9002, Severity: 17, State: 6
2007-02-13 18:48:51.42 spid66 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:52:40.61 spid58 Error: 9002, Severity: 17, State: 6
2007-02-13 18:52:40.61 spid58 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:53:22.69 spid61 Error: 9002, Severity: 17, State: 6
2007-02-13 18:53:22.69 spid61 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:54:01.11 spid57 Error: 9002, Severity: 17, State: 6
2007-02-13 18:54:01.11 spid57 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..

Are these 2 things related? Would a full transaction log cause a commit operation fail?

Please kindly advice.

Thanks a lot.

the error is self descriptive and the transaction log drive in the machine have no space now. What u need to do now is

(a) Truncate the Log

backup log SomeDatabase with truncate_only

(b) Shrink the log file

dbcc shrinkfile(SomeDatabaseLogicalFileName_Log)

(c) Take FUll Backup -- Very important otherwise the backup chain will break if it is in full recovery

backup database SomeDatabase to Disk='D:\SomeFile.bak' with init

To control the Future growth read the article mentioned below

Refer

http://support.microsoft.com/kb/873235

http://www.databasejournal.com/features/mssql/article.php/1460151

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894

Madhu