Hello-
We were on SQL 7.0 / Windows NT and have moved to SQL 2000 sp4a / Windows
2003. Since upgrade, we have seen some strange behavior.
Following are some of the observations so far:
1. We have witnessed exceedingly large database restoration timings. With
SQL 7.0, it used to take around 1.5-2.0 hours to restore database sized 125+
GB (with around 70 GB of data... we had some fragmentation problems!). Now,
the whole process takes from 4 to 9 hours. The restoration times are very
large when restored the first time (true, I am trying to create a database as
well).
2. We are using a vertical solution that used to have high level of
fragmentation. The fragmentation used to be so high that it used to increase
data device to 122+ GB with actual data of ~ 65+ GB. This forces me to
rebuild indexes and run the maintenance DBCC on every weekend. Since
upgrading to SQL 2000, this problem has reduced a lot. But, the maintenance
job timings since upgrade have also increased by 50% (it used to take ~ 8
hours and now the job is taking ~ 12 hours).
3. On at least two occasions (the latest on last Friday), the database has
generated exceedingly large transaction log backups. True, the application
system administrator has told me there was a runaway process that might have
created exceedingly large logs. But, the backups were so huge (+61 GB) that
it filled up the whole backup drive resulting in failures for other
transaction log backups till I cleaned the drive and took a full backup. The
normal hourly transaction log backups have total of size of 600+ MB (624 MB
as of now).
My question is: Am I seeing some kind of structural problems and has anyone
encountered similar problems?
Please share your experiences and expertise.
--
Regards,
MZeeshanHello Mzeeshan,
Before going any further, I'd like to confirm if the current transaction
log backup is larger than before and if you are restoring from the
transaction log backup. If yes, it is normal that it take more time to
restore.
The reason why the log backup is larger than before is that you run DBCC
command to rebuild indexes every week. Reindexing can cause the transaction
log grow much.
Because of the changes in the recovery model in SQL Server 2000, when you
use the Full recovery mode and you run DBCC DBREINDEX, the transaction log
may expand significantly more compared to that of SQL Server 7.0 in an
equivalent recovery mode with the use of SELECT INTO or BULK COPY and with
"Trunc. Log on chkpt." off.
Although the size of the transaction log after the DBREINDEX operation
might be an issue, this approach provides better log restore performance.
The following article has addressed the similar issue:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
If you only rely on full database backups (not transaction log backups),
the recovery model can be changed to Simple to have a smaller transaction
log file. To do this, please follow these steps:
1. Start SQL Server Enterprise Manager (SEM).
2. Expand a server group, and then expand a server.
3. Expand Databases, right-click the database to modify, and then click
Properties.
4. Click the Options tab.
5. Select "Simple" from the Model list under the Recovery section.
6. Click OK.
You can also run the following command to set the recovery model to Simple:
ALTER DATABASE database_name SET RECOVERY Simple
Note: Replace database_name with your database's name
Note: If you set the recovery option of the database to SIMPLE, the
transaction log is set to be truncated at every checkpoint and thus
prevents the log from filling up. However, with the Simple Recovery model,
the database can be recovered only to the point of the last database backup
and not to the last transaction and you cannot backup the transaction log
since the sequence of transaction logs is not being maintained. Also, you
cannot restore the database to the point of failure or to a specific point
in time. To do that, you need to use the Full Recovery or Bulk-Logged
Recovery model.
After that, run the "dbcc shrinkfile" command against the transaction log
to shrink the log file.
For more information on shrinking the transaction Log, please refer to the
following article:
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
<http://support.microsoft.com/?id=272318>
Please understand that the latest SQL Server 2000 Service Pack is Microsoft
SQL Server 2000 Service Pack 3a instead of SQL 2000 SP4a. Please clarify
the Service Pack you installed.
In addition, I want to let you know that performance issues can be caused
by various factors, and it is difficult to locate the root cause in a
newsgroup thread. If the issue still exists after you have used the
troubleshooting steps above, to efficiently troubleshoot a performance
issue, we recommend that you contact Microsoft Product Support Services and
open a support incident and work with a dedicated Support Professional.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
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.
Showing posts with label behavior. Show all posts
Showing posts with label behavior. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
Is this expected behavior?
I posted this at the asp.net forums but somone suggested I post it here. So:
Try this in sql server:
select COALESCE(a1, char(254)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
select COALESCE(a1, char(255)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:
W
Y
Ya
Z
t
The second one sorts like this:
W
Y
?
Ya
Z
Is this expected behavior?
It is because the sort order is based on the character set you are using, not where the characters show up in the ascii or unicode charts. If you want it to sort based on position like that, you need to use a binary sort order.
select COALESCE(a1, char(254)) COLLATE Latin1_General_BIN as c1
from (select 'Z' as a1
union select 'Ya' as a1
union select 'Y' as a1
union select 'W' as a1) as b1
group by a1
with rollup order by c1
select COALESCE(a1, char(255)) COLLATE Latin1_General_BIN as c1
from (select 'Z' as a1
union select 'Ya' as a1
union select 'Y' as a1
union select 'W' as a1) as b1
group by a1
with rollup order by c1
In this case both characters that are NULL will sort to the end of the results.
Wednesday, March 21, 2012
Is this default behavior?
Hello all,
I'm having difficulties understanding why something is not going the way I
want it (sounds familiar?). I'm testing SQL injection on my own PC, based on
the article at http://aspalliance.com/articleViewer...Id=385&pId=-1.
when I enter only my credentials in the user field, like
administrator';use master exec xp_cmdshell 'dir c:\*.*'--
I get the resultant string
SELECT strusername, strpassword FROM tUser WHERE strusername = 'beheerder';
use master exec xp_cmdshell 'dir c:\*.*' --' AND strpassword = ''
Now the output of "Response.write objrso.Fields.count" is 2. A closer look
gives me 'administrator' and 'password'. Not the C:\ drive listing, which
does show up in SQL Query Analyzer! Is this by any means possible with the
code
Set objConn = Server.CreateObject("ADODB.Connection")
Set objrso = Server.CreateObject("ADODB.Recordset")
sql = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" +
username & _
"' AND strpassword = '" + password & _
"'"
objConn.Open cn
objrso.open sql, cn
If not, how should I change this code? Any hints would be highly welcome.
Best regards,
Carl.
Hi Carl,
Since you have two different SQL statements, separated by semicolon, then
provider executes them separately and returns two resultsets (recordsets).
When you open objrso recordset, then it points to the first one. To be able
to get information from the subsequent recordsets, you need to call
NextRecordset method of the opened recordset
Set objrso=objrso.NextRecordset
If provider returns another resultset, then you will see it after this call
Val Mazur
Microsoft MVP
"Carl Matthews" <ecvaneersel@.nospam.hotmail.com> wrote in message
news:ecJmrkjIEHA.1140@.tk2msftngp13.phx.gbl...
> Hello all,
> I'm having difficulties understanding why something is not going the way I
> want it (sounds familiar?). I'm testing SQL injection on my own PC, based
> on
> the article at http://aspalliance.com/articleViewer...Id=385&pId=-1.
> when I enter only my credentials in the user field, like
> administrator';use master exec xp_cmdshell 'dir c:\*.*'--
> I get the resultant string
> SELECT strusername, strpassword FROM tUser WHERE strusername =
> 'beheerder';
> use master exec xp_cmdshell 'dir c:\*.*' --' AND strpassword = ''
>
> Now the output of "Response.write objrso.Fields.count" is 2. A closer look
> gives me 'administrator' and 'password'. Not the C:\ drive listing, which
> does show up in SQL Query Analyzer! Is this by any means possible with the
> code
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objrso = Server.CreateObject("ADODB.Recordset")
> sql = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" +
> username & _
> "' AND strpassword = '" + password & _
> "'"
> objConn.Open cn
> objrso.open sql, cn
> If not, how should I change this code? Any hints would be highly welcome.
> Best regards,
> Carl.
>
I'm having difficulties understanding why something is not going the way I
want it (sounds familiar?). I'm testing SQL injection on my own PC, based on
the article at http://aspalliance.com/articleViewer...Id=385&pId=-1.
when I enter only my credentials in the user field, like
administrator';use master exec xp_cmdshell 'dir c:\*.*'--
I get the resultant string
SELECT strusername, strpassword FROM tUser WHERE strusername = 'beheerder';
use master exec xp_cmdshell 'dir c:\*.*' --' AND strpassword = ''
Now the output of "Response.write objrso.Fields.count" is 2. A closer look
gives me 'administrator' and 'password'. Not the C:\ drive listing, which
does show up in SQL Query Analyzer! Is this by any means possible with the
code
Set objConn = Server.CreateObject("ADODB.Connection")
Set objrso = Server.CreateObject("ADODB.Recordset")
sql = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" +
username & _
"' AND strpassword = '" + password & _
"'"
objConn.Open cn
objrso.open sql, cn
If not, how should I change this code? Any hints would be highly welcome.
Best regards,
Carl.
Hi Carl,
Since you have two different SQL statements, separated by semicolon, then
provider executes them separately and returns two resultsets (recordsets).
When you open objrso recordset, then it points to the first one. To be able
to get information from the subsequent recordsets, you need to call
NextRecordset method of the opened recordset
Set objrso=objrso.NextRecordset
If provider returns another resultset, then you will see it after this call
Val Mazur
Microsoft MVP
"Carl Matthews" <ecvaneersel@.nospam.hotmail.com> wrote in message
news:ecJmrkjIEHA.1140@.tk2msftngp13.phx.gbl...
> Hello all,
> I'm having difficulties understanding why something is not going the way I
> want it (sounds familiar?). I'm testing SQL injection on my own PC, based
> on
> the article at http://aspalliance.com/articleViewer...Id=385&pId=-1.
> when I enter only my credentials in the user field, like
> administrator';use master exec xp_cmdshell 'dir c:\*.*'--
> I get the resultant string
> SELECT strusername, strpassword FROM tUser WHERE strusername =
> 'beheerder';
> use master exec xp_cmdshell 'dir c:\*.*' --' AND strpassword = ''
>
> Now the output of "Response.write objrso.Fields.count" is 2. A closer look
> gives me 'administrator' and 'password'. Not the C:\ drive listing, which
> does show up in SQL Query Analyzer! Is this by any means possible with the
> code
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objrso = Server.CreateObject("ADODB.Recordset")
> sql = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" +
> username & _
> "' AND strpassword = '" + password & _
> "'"
> objConn.Open cn
> objrso.open sql, cn
> If not, how should I change this code? Any hints would be highly welcome.
> Best regards,
> Carl.
>
Is this default behavior?
Hello all,
I'm having difficulties understanding why something is not going the way I
want it (sounds familiar?). I'm testing SQL injection on my own PC, based on
the article at http://aspalliance.com/articleViewe...aId=385&pId=-1.
when I enter only my credentials in the user field, like
administrator';use master exec xp_cmdshell 'dir c:\*.*'--
I get the resultant string
SELECT strusername, strpassword FROM tUser WHERE strusername = 'beheerder';
use master exec xp_cmdshell 'dir c:\*.*' --' AND strpassword = ''
Now the output of "Response.write objrso.Fields.count" is 2. A closer look
gives me 'administrator' and 'password'. Not the C:\ drive listing, which
does show up in SQL Query Analyzer! Is this by any means possible with the
code
Set objConn = Server.CreateObject("ADODB.Connection")
Set objrso = Server.CreateObject("ADODB.Recordset")
sql = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" +
username & _
"' AND strpassword = '" + password & _
"'"
objConn.Open cn
objrso.open sql, cn
If not, how should I change this code? Any hints would be highly welcome.
Best regards,
Carl.Hi Carl,
Since you have two different SQL statements, separated by semicolon, then
provider executes them separately and returns two resultsets (recordsets).
When you open objrso recordset, then it points to the first one. To be able
to get information from the subsequent recordsets, you need to call
NextRecordset method of the opened recordset
Set objrso=objrso.NextRecordset
If provider returns another resultset, then you will see it after this call
Val Mazur
Microsoft MVP
"Carl Matthews" <ecvaneersel@.nospam.hotmail.com> wrote in message
news:ecJmrkjIEHA.1140@.tk2msftngp13.phx.gbl...
> Hello all,
> I'm having difficulties understanding why something is not going the way I
> want it (sounds familiar?). I'm testing SQL injection on my own PC, based
> on
> the article at http://aspalliance.com/articleViewe...aId=385&pId=-1.
> when I enter only my credentials in the user field, like
> administrator';use master exec xp_cmdshell 'dir c:\*.*'--
> I get the resultant string
> SELECT strusername, strpassword FROM tUser WHERE strusername =
> 'beheerder';
> use master exec xp_cmdshell 'dir c:\*.*' --' AND strpassword = ''
>
> Now the output of "Response.write objrso.Fields.count" is 2. A closer look
> gives me 'administrator' and 'password'. Not the C:\ drive listing, which
> does show up in SQL Query Analyzer! Is this by any means possible with the
> code
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objrso = Server.CreateObject("ADODB.Recordset")
> sql = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" +
> username & _
> "' AND strpassword = '" + password & _
> "'"
> objConn.Open cn
> objrso.open sql, cn
> If not, how should I change this code? Any hints would be highly welcome.
> Best regards,
> Carl.
>sql
I'm having difficulties understanding why something is not going the way I
want it (sounds familiar?). I'm testing SQL injection on my own PC, based on
the article at http://aspalliance.com/articleViewe...aId=385&pId=-1.
when I enter only my credentials in the user field, like
administrator';use master exec xp_cmdshell 'dir c:\*.*'--
I get the resultant string
SELECT strusername, strpassword FROM tUser WHERE strusername = 'beheerder';
use master exec xp_cmdshell 'dir c:\*.*' --' AND strpassword = ''
Now the output of "Response.write objrso.Fields.count" is 2. A closer look
gives me 'administrator' and 'password'. Not the C:\ drive listing, which
does show up in SQL Query Analyzer! Is this by any means possible with the
code
Set objConn = Server.CreateObject("ADODB.Connection")
Set objrso = Server.CreateObject("ADODB.Recordset")
sql = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" +
username & _
"' AND strpassword = '" + password & _
"'"
objConn.Open cn
objrso.open sql, cn
If not, how should I change this code? Any hints would be highly welcome.
Best regards,
Carl.Hi Carl,
Since you have two different SQL statements, separated by semicolon, then
provider executes them separately and returns two resultsets (recordsets).
When you open objrso recordset, then it points to the first one. To be able
to get information from the subsequent recordsets, you need to call
NextRecordset method of the opened recordset
Set objrso=objrso.NextRecordset
If provider returns another resultset, then you will see it after this call
Val Mazur
Microsoft MVP
"Carl Matthews" <ecvaneersel@.nospam.hotmail.com> wrote in message
news:ecJmrkjIEHA.1140@.tk2msftngp13.phx.gbl...
> Hello all,
> I'm having difficulties understanding why something is not going the way I
> want it (sounds familiar?). I'm testing SQL injection on my own PC, based
> on
> the article at http://aspalliance.com/articleViewe...aId=385&pId=-1.
> when I enter only my credentials in the user field, like
> administrator';use master exec xp_cmdshell 'dir c:\*.*'--
> I get the resultant string
> SELECT strusername, strpassword FROM tUser WHERE strusername =
> 'beheerder';
> use master exec xp_cmdshell 'dir c:\*.*' --' AND strpassword = ''
>
> Now the output of "Response.write objrso.Fields.count" is 2. A closer look
> gives me 'administrator' and 'password'. Not the C:\ drive listing, which
> does show up in SQL Query Analyzer! Is this by any means possible with the
> code
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objrso = Server.CreateObject("ADODB.Recordset")
> sql = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" +
> username & _
> "' AND strpassword = '" + password & _
> "'"
> objConn.Open cn
> objrso.open sql, cn
> If not, how should I change this code? Any hints would be highly welcome.
> Best regards,
> Carl.
>sql
Is this a limitation of Reporting Services...?
I am beginning to wonder if the behavior I am running into is a possible
limitation of Reporting Services. This is related to exporting a report to
.pdf that contains an embedded image. I have seen some discussion on this,
but have been unable to glean a solution from the existing threads.
My problem is that when I embed an image into my reports, it looks ok
in Visual Studio, but when I export to .pdf, it is blocky and distorted. My
graphics guy has put the image into ten different formats with different
resolutions, but they all render the same way.
What is the Microsoft recommeded format and resolution for image rendering
to .pdf? He designed the logo and can put it in whatever format I need, I
just don't know what to tell him. I found something on Image Device
Information Settings,
that lists 96 dpi as the best, but we tried that with the same results. It
also said something about the tif format, but when I tried to import the .tif
file, .tif was not listed as an image format in the wizard.
And yes, I have upgraded to SP1.I have posted this same message 7 times and still have not received an
answer. I am beginning to think that I am either invisible, or I have found
a limitation of Reporting Services. If that is the case, I would love for
someone to tell me so. I have spent more hours than I can count trying to
resolve this...
PLEASE HELP ME!!!!!!!!!!!!!!!!!|||OK, so I read this to see if I can help. I don't have a clue what your
problem is. Either repost with the problem to this thread or better yet,
repost with a good description in the subject. Depending on the subject
people with different expertise will jump in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:9FB5F4A6-75FD-4DAD-9B73-B7F63CCE92F1@.microsoft.com...
> I have posted this same message 7 times and still have not received an
> answer. I am beginning to think that I am either invisible, or I have
found
> a limitation of Reporting Services. If that is the case, I would love for
> someone to tell me so. I have spent more hours than I can count trying to
> resolve this...
> PLEASE HELP ME!!!!!!!!!!!!!!!!!|||Here is my original post, which I was replying to in the one you saw...but
maybe you couldn't see the original...
My problem is that when I embed an image into my reports, it looks ok
in Visual Studio, but when I export to .pdf, it is blocky and distorted. My
graphics guy has put the image into ten different formats with different
resolutions, but they all render the same way.
What is the Microsoft recommeded format and resolution for image rendering
to .pdf? He designed the image and can put it in whatever format I need, I
just don't know what to tell him. I found something on Image Device
Information Settings, that lists 96 dpi as the best, but we tried that with
the same results. It also said something about the tif format, but when I
tried to import the .tif file, .tif was not listed as an image format in the
wizard.
And yes, I have upgraded to SP1.
Thanks so much for your help!
Lisa
"Bruce L-C [MVP]" wrote:
> OK, so I read this to see if I can help. I don't have a clue what your
> problem is. Either repost with the problem to this thread or better yet,
> repost with a good description in the subject. Depending on the subject
> people with different expertise will jump in.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:9FB5F4A6-75FD-4DAD-9B73-B7F63CCE92F1@.microsoft.com...
> > I have posted this same message 7 times and still have not received an
> > answer. I am beginning to think that I am either invisible, or I have
> found
> > a limitation of Reporting Services. If that is the case, I would love for
> > someone to tell me so. I have spent more hours than I can count trying to
> > resolve this...
> >
> > PLEASE HELP ME!!!!!!!!!!!!!!!!!
>
>
limitation of Reporting Services. This is related to exporting a report to
.pdf that contains an embedded image. I have seen some discussion on this,
but have been unable to glean a solution from the existing threads.
My problem is that when I embed an image into my reports, it looks ok
in Visual Studio, but when I export to .pdf, it is blocky and distorted. My
graphics guy has put the image into ten different formats with different
resolutions, but they all render the same way.
What is the Microsoft recommeded format and resolution for image rendering
to .pdf? He designed the logo and can put it in whatever format I need, I
just don't know what to tell him. I found something on Image Device
Information Settings,
that lists 96 dpi as the best, but we tried that with the same results. It
also said something about the tif format, but when I tried to import the .tif
file, .tif was not listed as an image format in the wizard.
And yes, I have upgraded to SP1.I have posted this same message 7 times and still have not received an
answer. I am beginning to think that I am either invisible, or I have found
a limitation of Reporting Services. If that is the case, I would love for
someone to tell me so. I have spent more hours than I can count trying to
resolve this...
PLEASE HELP ME!!!!!!!!!!!!!!!!!|||OK, so I read this to see if I can help. I don't have a clue what your
problem is. Either repost with the problem to this thread or better yet,
repost with a good description in the subject. Depending on the subject
people with different expertise will jump in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:9FB5F4A6-75FD-4DAD-9B73-B7F63CCE92F1@.microsoft.com...
> I have posted this same message 7 times and still have not received an
> answer. I am beginning to think that I am either invisible, or I have
found
> a limitation of Reporting Services. If that is the case, I would love for
> someone to tell me so. I have spent more hours than I can count trying to
> resolve this...
> PLEASE HELP ME!!!!!!!!!!!!!!!!!|||Here is my original post, which I was replying to in the one you saw...but
maybe you couldn't see the original...
My problem is that when I embed an image into my reports, it looks ok
in Visual Studio, but when I export to .pdf, it is blocky and distorted. My
graphics guy has put the image into ten different formats with different
resolutions, but they all render the same way.
What is the Microsoft recommeded format and resolution for image rendering
to .pdf? He designed the image and can put it in whatever format I need, I
just don't know what to tell him. I found something on Image Device
Information Settings, that lists 96 dpi as the best, but we tried that with
the same results. It also said something about the tif format, but when I
tried to import the .tif file, .tif was not listed as an image format in the
wizard.
And yes, I have upgraded to SP1.
Thanks so much for your help!
Lisa
"Bruce L-C [MVP]" wrote:
> OK, so I read this to see if I can help. I don't have a clue what your
> problem is. Either repost with the problem to this thread or better yet,
> repost with a good description in the subject. Depending on the subject
> people with different expertise will jump in.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:9FB5F4A6-75FD-4DAD-9B73-B7F63CCE92F1@.microsoft.com...
> > I have posted this same message 7 times and still have not received an
> > answer. I am beginning to think that I am either invisible, or I have
> found
> > a limitation of Reporting Services. If that is the case, I would love for
> > someone to tell me so. I have spent more hours than I can count trying to
> > resolve this...
> >
> > PLEASE HELP ME!!!!!!!!!!!!!!!!!
>
>
Subscribe to:
Posts (Atom)