Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNameSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so. Still takes a long time. I then notice what is happening. The SQL is taking the SOUNDEX of the LastName of the first record, apply it to ALL the records, then taking the SOUNDEX of the LastName of the second records, then updating it to ALL the records, etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!
> It takes forever. I start tweeking the SQL to commit every 500 records or
so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.
>
How did you notice this? This is really strange, I've never heard of
something like this.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Wednesday, March 28, 2012
Problems in SQL, Updating many rows at once
Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNam
eSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so
. Still takes a long time. I then notice what is happening. The SQL is takin
g the SOUNDEX of the LastName of the first record, apply it to ALL the recor
ds, then taking the SOUNDEX
of the LastName of the second records, then updating it to ALL the records,
etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!
so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.
How did you notice this? This is really strange, I've never heard of
something like this.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNam
eSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so
. Still takes a long time. I then notice what is happening. The SQL is takin
g the SOUNDEX of the LastName of the first record, apply it to ALL the recor
ds, then taking the SOUNDEX
of the LastName of the second records, then updating it to ALL the records,
etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!
quote:
> It takes forever. I start tweeking the SQL to commit every 500 records or
so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.
quote:
>
How did you notice this? This is really strange, I've never heard of
something like this.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Monday, March 26, 2012
Problems exporting from reporting services 2005 to excel with a lot of rows
Hello,
When I export to excel a report made in Reporting Services 2005, and it has a lot of rows in a table (in the detail section), I have to wait a lot of time, and when it finish the size of the file is bigger than the excel file exported from the old reports in crystal report 6. When I open this exported documents, I have to wait more time until I can see them.
The document have 3 sheets, the first have the document map, the second has a report header, and the last one the page header and the body section (with the table). We thought that it could be the document map because it has hyperlinks to the third sheet, but I have tried to delete this sheet in the excel document and the size is more or less the same.
I tried also exporting to CSV but the results aren't what we need.
What could I do?
Anybody can help me? :/|||Hi ,
I suggest you to align your report items perfectly to the standards of reporting services
what can you notice when the Excel is opened.if you can see unnecessary columns with zero width.then my expectation is right.
if you can see the dots on report layout tab,when you design the report
,make sure that a report item should not end between these two dots
(size should be the multiple of 0.125 inch)
Follow the same for each Report item in the Report and let me know what did you end up with ?
Thank you,
Raj Deep>A
|||Hi Raj Deep,
Thanks for your response, but my report items are not between any dots, and the size of every textbox is 0.5. The problem must be because there are too many data in the table, I think, but it wasn′t a matter for crystal reports 8. Any other idea? :)
Thank you,
Pablo
Friday, March 9, 2012
Problem writing to fixed width text file destination
I am trying to export data from a query in SQL Server 2005 SSIS to a flat file destination. Everything works fine except the rows returned from my query are written to the flat file in one long string (i.e., without line breaks). I have tried appending a new line character to the rows returned from the query but that only throws an error when the package is executed. My rows returned from the query are 133 characters wide (essentially only one column per row) so I have set the properties accordingly for a fixed width file format with 133 character wide rows.
Any suggestions or ideas on how to correct this would be greatly appreciated.
Thank you,
Michael
What viewer are you using when looking at the text file? Are you sure you don't have newlines in the files? Look in notepad and again in word and see if that helps.|||The Flat File Connection manager has a Format property, and I guess you have selected "Fixed Width", but strictly speaking this format does not include row delimiters. What you actually need is Ragged Right.
The best way to do this is to open your Destination, and click the New connection button. Now read the options carefull, as most people probably select #2, as it says Fixed Width, but #3 is what you want, Fixed Width with Row Delimiters. This builds the appropriate connection using Ragged Right, giving you what you want.
Wednesday, March 7, 2012
Problem with WHERE clause when column length > 255
Hello, I am using the JDCB-ODBC driver for my app, but one of the columns in my WHERE clause is 255 characters long. Because of this no rows are returned even though the statement should return 1 or 2 rows. I've tried other JDBC-ODBC drivers too and they have the same problem. Additionally, I've tried using RTrim(), Substring(), etc and it still will not work...any ideas? Is it a driver bug? Anyone know of a driver that will work?
Sample code...
ResultSet rs = dbRetrieve.getStatement().executeQuery( sql ) ;
if (rs.next()) {
// Never gets here
}
Thanx!Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?
-PatP|||Originally posted by Pat Phelan
Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?
-PatP
Yes, I have and it returns results. Additionally, I've removed the column from the where clause in my java file and it returns data, but of course its uselys because I need that column in the where clause...
Thanks...|||Hmm... Microsoft's JDBC driver says it will support upt to 4000 character strings. Have you used profiler to see exactly what's being sent to your SQL server?|||Since the SQL code works ok from Query Analyzer, we can assume that SQL Server to the MDAC on your client works fine. The problem must be in the JDBC driver or beyond it.
Which JDBC driver are you using? Does it have any length limitations that you are aware of (or does it keep them as surprises for the unwary)? As Peter pointed out, the Microsoft JDBC driver handles big strings (I've run 2 Kb columns out through it).
Just as another thought, are you certain that your Java app is behaving and actually submitting what you expect it to.
-PatP
Sample code...
ResultSet rs = dbRetrieve.getStatement().executeQuery( sql ) ;
if (rs.next()) {
// Never gets here
}
Thanx!Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?
-PatP|||Originally posted by Pat Phelan
Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?
-PatP
Yes, I have and it returns results. Additionally, I've removed the column from the where clause in my java file and it returns data, but of course its uselys because I need that column in the where clause...
Thanks...|||Hmm... Microsoft's JDBC driver says it will support upt to 4000 character strings. Have you used profiler to see exactly what's being sent to your SQL server?|||Since the SQL code works ok from Query Analyzer, we can assume that SQL Server to the MDAC on your client works fine. The problem must be in the JDBC driver or beyond it.
Which JDBC driver are you using? Does it have any length limitations that you are aware of (or does it keep them as surprises for the unwary)? As Peter pointed out, the Microsoft JDBC driver handles big strings (I've run 2 Kb columns out through it).
Just as another thought, are you certain that your Java app is behaving and actually submitting what you expect it to.
-PatP
Monday, February 20, 2012
Problem with updating column in table - transaction log full
Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
Ragnar
On Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.
|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
Ragnar
On Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.
|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar
Problem with updating column in table - transaction log full
Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the si
ze
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you g
enerate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log
automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insigh
t into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_kara...>
rinking.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but
> I have been digging through the SQL Server Books Online without finding an
ything about this.
> I have seen a few cases in our company where someone has set up an SQL Ser
ver DB without setting
> up regular backups, where the log just kept growing. In one case the log u
sed up all the available
> space on the disk and effectively shut down the computer, not enough room
for the swap file.
> I think there is a way to control the growth of the log file, but I have n
ot found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the si
ze
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you g
enerate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log
automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insigh
t into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_kara...>
rinking.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but
> I have been digging through the SQL Server Books Online without finding an
ything about this.
> I have seen a few cases in our company where someone has set up an SQL Ser
ver DB without setting
> up regular backups, where the log just kept growing. In one case the log u
sed up all the available
> space on the disk and effectively shut down the computer, not enough room
for the swap file.
> I think there is a way to control the growth of the log file, but I have n
ot found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>
Problem with updating column in table - transaction log full
Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you generate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insight into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log will grow if needed, but
> I have been digging through the SQL Server Books Online without finding anything about this.
> I have seen a few cases in our company where someone has set up an SQL Server DB without setting
> up regular backups, where the log just kept growing. In one case the log used up all the available
> space on the disk and effectively shut down the computer, not enough room for the swap file.
> I think there is a way to control the growth of the log file, but I have not found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you generate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insight into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log will grow if needed, but
> I have been digging through the SQL Server Books Online without finding anything about this.
> I have seen a few cases in our company where someone has set up an SQL Server DB without setting
> up regular backups, where the log just kept growing. In one case the log used up all the available
> space on the disk and effectively shut down the computer, not enough room for the swap file.
> I think there is a way to control the growth of the log file, but I have not found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>
Problem with update when updating all rows of a table through dataset and saving back to d
Hi,
I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?
SqlCommand snUpdate = conn.CreateCommand();
snUpdate.CommandType =CommandType.Text;
snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate";
snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");
string jdate ="";
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();
ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);
}
da.Update(ds,"Table1");
conn.Close();
-Thanks
a quick once over and i have a few questions--what all in your TestTable? Also in all lines of code you refer to your table as Tables[0] you should always stick to one way. Your right about your Update, so when we see what's in your TestTable we'll be able to help you out a little bit more.
|||Thanks for your response. My table contains 2 fields SKU numbers and Ship Dates. I changed my update statement as follows and it worked.
snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate where skunum=@.skunum";
snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");
snUpdate.Parameters.Add("@.skunum",SqlDbType.Int, 4,"skunum");
snUpdate.Parameters["@.skunum"].SourceVersion =DataRowVersion.Original;
-Thanks
|||So is skunum a unique value? If not, then it'll update all rows with that skunum. If it is, then why isn't that your primary key?|||The skunum is not unique. The table does have some duplicated data which I don't have to worry about if they get updated with the same value. This is just an old table whose data will be transformed into a different table and used in an app after my updates are done. Thanks.
Subscribe to:
Posts (Atom)