A few BLOBs per page

Does SQL server 2005 places a few VARBINARY(MAX) values on a single page if
the length of those values are let's say 2KB?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200512/1
Hi Alex
Varbinary(max) data will actually be placed in the data row itself if there
is room.
You can set the table property to store all large objects out of the row,
and then varbinary(max) is treated just like image.
Image columns from the same table CAN share space on the same pages for
greater storage space efficiency.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Alex via droptable.com" <no@.spam.pls> wrote in message
news:589565dfa15f3@.uwe...
> Does SQL server 2005 places a few VARBINARY(MAX) values on a single page
> if
> the length of those values are let's say 2KB?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200512/1
>
|||Thanks a lot for your response.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200512/1
sql

A few BLOBs per page

Does SQL server 2005 places a few VARBINARY(MAX) values on a single page if
the length of those values are let's say 2KB?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1Hi Alex
Varbinary(max) data will actually be placed in the data row itself if there
is room.
You can set the table property to store all large objects out of the row,
and then varbinary(max) is treated just like image.
Image columns from the same table CAN share space on the same pages for
greater storage space efficiency.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Alex via SQLMonster.com" <no@.spam.pls> wrote in message
news:589565dfa15f3@.uwe...
> Does SQL server 2005 places a few VARBINARY(MAX) values on a single page
> if
> the length of those values are let's say 2KB?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1
>|||Thanks a lot for your response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1

A few BLOBs per page

Does SQL server 2005 places a few VARBINARY(MAX) values on a single page if
the length of those values are let's say 2KB?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1Hi Alex
Varbinary(max) data will actually be placed in the data row itself if there
is room.
You can set the table property to store all large objects out of the row,
and then varbinary(max) is treated just like image.
Image columns from the same table CAN share space on the same pages for
greater storage space efficiency.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Alex via droptable.com" <no@.spam.pls> wrote in message
news:589565dfa15f3@.uwe...
> Does SQL server 2005 places a few VARBINARY(MAX) values on a single page
> if
> the length of those values are let's say 2KB?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200512/1
>|||Thanks a lot for your response.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1

A feature available in oracle, is it available in sql server?

Theres a feature in oracle that allows you to modify tables, colums, values and the data from its enterprise console the same way that you can in sql server. In oracle however theres a button called 'show sql' that allows you to see and copy/paste the resulting sql for the changes made via the console.

I would imagine that sql server has a similar option. The reason i ask is that i would like to more fully learn how to do this through the query analyser and get more familiar with sql involved and I would be able to do this if I could see the resulting sql from enterprise manager.

Hope this makes sense.

I did find something in sql server called 'generate sql' but this doesnt update during changes you make automatically.

Thanksyeah if you have downloaded ther BOL ( which you should) look for ALTER TABLE key word. you can add columns, drop 'em modify 'em etc.

hth|||Thanks for the reply. But, whats the BOL. And what are you talking about?!>?! This doesnt answer my question. I'm talking about the ability to see the resulting sql when modifying it in the enterprise console.|||What you are looking for is called "Save Change Script".

If you are in Enterprise Manager, right click on a table name, and choose "Design". This will take you into the Design Table interface. If you hover over the 3rd icon from the left you will see that it says "save change script" (note that you actually have to make a change in order for this to become active). If you click on this you will see the exact commands that EM is going to execute to accomomdate your changes, and you can opt to save them to disk.

Also, BOL is Books Online, an invaluable free SQL Server reference from Microsoft. It is a huge download but well worth it. You can find it here:SQL Server 2000 Books Online (Updated 2004).

Terri

a fcuntion to compare two tables

I need a function witch compares two tables.
can some one help me ?Hi
SELECT OneTable.*, TwoTable.*
FROM OneTable
FULL OUTER JOIN
TwoTable
ON OneTable.c1 = TwoTable.c1
AND OneTable.c2 = TwoTable.c2
...
AND OneTable.cn = TwoTable.cn
WHERE OneTable.key IS NULL
OR TwoTable.key IS NULL;
"olli_d" <info@.dithmer.de> wrote in message
news:1193241328.829023.299090@.y27g2000pre.googlegroups.com...
>I need a function witch compares two tables.
> can some one help me ?
>

a fatal error of sqlserver2005

I find many errors these days in my sqlserver 2005. description is :

SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xfab258f6; actual: 0x7ab258fb). It occurred during a read of page (1:387359) in database ID 20 at offset 0x000000bd23e000 in file 'F:\SQL2005_Data\UKDB.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

when I run dbcc checkdb, it report "Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC which prevented further processing. Please contact Product Support."

sqlserver is :

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

OS is : Win2003 Server Standard edition with sp1

hardware is :

two hardisk with RAID 1 on Silicon image 3114 RAID controller

It sounds like you have a corrupt database.
Some types of corruption result in severe errors.

As a first step, you should consider restoring this database from a good backup.

Be sure to check your system event log to see if any hardware errors are being logged.

The invalid checksum indicates that the storage of the database has been changed after we last wrote it. Flakey hardware is the main reason that checksums were added in sql2005.

If this problem persists, let us know.

Also, let me know if you'd like me to use the contact information in your forum profile to communicate with you offline.

|||

I find my memory has problem. It cause the sql error, thank you for your help.

sql

a faster way to update a big table

hi
well I am working on a project that our database has 2, 8000 record table that they have triggers on the update of their feilds
our application must update these tables but it takes a long time to do this
I know there is something named bulk insert but I couldn't find sth similar to this command for update
so would you please help me to find a faster way to update these tables?
thanks for your attention
Best Regards
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
hi,
netman Mo wrote:
> hi
> well I am working on a project that our database has 2, 8000 record
> table that they have triggers on the update of their feilds our
> application must update these tables but it takes a long time to do
> this
> I know there is something named bulk insert but I couldn't find sth
> similar to this command for update
> so would you please help me to find a faster way to update these
> tables?
nope.. update syntax is not overloaded with bulk operators..
if the cause of your delay is dependent on the trigger fired by the update
statement, you should perhaps check it's code... or... if you are sure the
updates you are performing do not involve the trigger check, you can disable
it before executing the statements..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply

A error in partition table ,could you tell me ?

1 HIS_HTTP_LOG a partition table
2 REL_HTTP_LOG not a partition table,the same structure of HIS_HTTP_LOG；
3 When HIS_HTTP_LOG doesn't exist any index
the following executed succeed

ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03]
ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997')
ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3

4 However when I added the index in HIS_HTTP_LOG and execute the step 3,It made error:
a) CREATE INDEX IDX_HIS_HTTP_LOG_001 ON HIS_HTTP_LOG(USERID)ON PS_HIS_HTTP_LOG (STARTIME)
b) ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03]
ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997')
ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3

========================= Error messages================================================
"ALTER TABLE SWITCH statement failed. There is no identical index in source table 'TMP_HTTP_LOG SWITCH ' for the index 'IDX_HIS_HTTP_LOG_001' in target table 'HIS_HTTP_LOG' ."

When I added index in REL_HTTP_LOG ,it gave me the same error message

Could you tell me how can I solve the problem !

The error says you need to create anidentical index on?TMP_HTTP_LOG SWITCH,?as?'IDX_HIS_HTTP_LOG_001' in target table 'HIS_HTTP_LOG' .?So?create?such?an?index?and?try?again.

When?use?ALTER?TABLE?SWITCH?to?transfer?schema, there is no physical data movement, only metadata change, the partitions and tables involved in the switching are required to be homogeneous. They must have the same columns of the same data type, name, order, and collation on the same filegroup.

A duplicate value cannot be inserted into a unique index

I'll first give the caveat that I've been away from this project for many
weeks (but at least it is my own creation). Having said that, I'm not sure
if this is a replication problem or exactly what I have on my hands here.
When I submit an Insert to a particular table, I can submit as many Inserts
as I would like with no exceptions -- until I replicate my changes back to
the server, then download that table again. It would appear that I have
identical data in both the SQLCE table and the SQL2k table, but for a reason
that I have yet to figure out, the downloaded table will no longer accept
Inserts. I get the message: "A duplicate value cannot be inserted into a
unique index. [,,,,,].
I do not have indexes on any of the columns. I do have a primary key. Any
advice would be appreciated.
It means you are trying to insert identical values into your PK.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:uXndNKDNFHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'll first give the caveat that I've been away from this project for many
> weeks (but at least it is my own creation). Having said that, I'm not sure
> if this is a replication problem or exactly what I have on my hands here.
> When I submit an Insert to a particular table, I can submit as many
> Inserts as I would like with no exceptions -- until I replicate my changes
> back to the server, then download that table again. It would appear that I
> have identical data in both the SQLCE table and the SQL2k table, but for a
> reason that I have yet to figure out, the downloaded table will no longer
> accept Inserts. I get the message: "A duplicate value cannot be inserted
> into a unique index. [,,,,,].
> I do not have indexes on any of the columns. I do have a primary key. Any
> advice would be appreciated.
>
|||Nope. Identical values into unique indexed columns. I just dropped the
indexes for now.
"ChrisR" <noemail@.bla.com> wrote in message
news:uFug$fHNFHA.576@.TK2MSFTNGP15.phx.gbl...
> It means you are trying to insert identical values into your PK.
> "Earl" <brikshoe@.newsgroups.nospam> wrote in message
> news:uXndNKDNFHA.2020@.TK2MSFTNGP10.phx.gbl...
>

A doubt about this: "Could not find stored Procedure"

Hi Everybody!

I have a problem in my SQL Server 2000 SP1 Database Called "Embossamento". When I run the following comand in the Query Analyzer, I have this error message :

command: exec dbcc_all_dbreindex
message: Could not find stored procedure 'dbcc_all_dbreindex'.

Otherwise, in the same server, but in another Database called "Autorizacao" I execute this dbcc_all_dbreindex with no problems.
Does anybody know why this happens?
I will be waiting for some reply, ok?
Thanks,This must be a custom stored procedure that uses the dbcc dbreindex command - check out the database, Autorizacao, and look for your stored procedures under that database.|||Ok! Thanks!
It worked! I just realized that my database did not have the procedure dbcc_all_dbreindex.
Now it's ok...

A DOS question! (about aspnet_regsql.exe with paramaters)

I need to setup some asp security databases and I have seen several sets of instructions. Some say "Navigate to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727" and some say at the "asp command box". I do know that the directory & file exists on my machine. I have run aspnet_regsql.exe to configure my local server. I now need to run it to configure my web host SQL Server 2005 and that requires some paramaters like "aspnet_regsql.exe -S [DB Server Name] -U [DB login] -P [Password] -A all -d [Database name]". I am old and started before windows. I know DOS. But obviously not well enough.

How do you "Navigate to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727"?

If I say "CD\WINDOWS", it works.

When I am at the "C:\WINDOWS>" prompt and say "CD Microsoft.NET" it says "invalid directory"

When I am at the "C:\>" prompt and say "CD\WINDOWS\Microsoft.NET" it says "invalid directory"

When I am at the "C:\>" prompt and say "CD\WINDOWS\Micros~1" it says "invalid directory"

Thanks for your help. John Brown

Not much traffic on a DOS question! I figured out a work around.

I copied aspnet_regsql.exe to a directory with a short name (C:\trash) and then ran it from the command prompt. It worked!

John Brown

sql

A domain error occurred. from SQL statement, only when above 10

Attempting to run a query that is looping through cursor, anything over
choosing the TOP 10 from a table for the cursor results in a
"A domain error occurred."
error message? below 10 all runs fine. What is this message caused by?It usually happens when performing mathematical functions
and using values outside of acceptable ranges.
-Sue
On Thu, 20 Nov 2003 11:51:01 -0600, "Kory"
<kory@.removeme-mlsc.com> wrote:
>Attempting to run a query that is looping through cursor, anything over
>choosing the TOP 10 from a table for the cursor results in a
>"A domain error occurred."
>error message? below 10 all runs fine. What is this message caused by?
>

a Distinct Query

I have 2 tables as following :

tbl_Articles
3 ArticleID int
0 AuthorID int
0 ArticleTitle
0 ArticleText
0 ArticleDate

tbl_Authors
3 AuthorID
0 AuthorFullName
0 AuthorEmail
0 AuthorDescription
0 AuthorImage

I want to write a query to see the Authors and their last articles with no distinct values.
Like AuthorImage - AuthorFullName - ArticleTitle - ArticleDate

If anyone knows the solution i will be glad .
Thanks from now onselect AuthorImage, AuthorFullName, ArticleTitle, ArticleDate = aDate
from tbl_Authors a
inner join (
select AuthorID, aDate = max(ArticleDate)
from tbl_Articles) x
on a.AuthorID = x.AuthorID
inner join tbl_Articles b
on x.AuthorID = b.AuthorID
and x.aDate = b.ArticleDate|||another version:select AuthorImage
, AuthorFullName
, ArticleTitle
, ArticleDate
from tbl_Authors AUTH
inner
join tbl_Articles ART
on AUTH.AuthorID
= ART.AuthorID
where ART.ArticleDate
= ( select max(ArticleDate)
from tbl_Articles
where AuthorID
= AUTH.AuthorID )

A Dimension Attribute with Double DataType converts a zero member to "(blank)"

In one of our SSAS 2005 sp2 cubes, we have a Dimension sourced from an Oracle table, with an Attribute that has a DataType of Double. When we process the Dimension and browse the Attribute, we see the following members:

All

-.01

-.002

.00000000001

.0000000001

Unknown

Notice how its blank where the '0' (zero) member should be. The underlying Oracle table does have '0' (zero) values for this column (you can see them when you explore the data in the DSV). To make things even more confusing,

the Unique_Name for this blank member is: [Dimension].[Attribute].&[0]

its Member_Value is: 0

its Member_Caption is: Null

its Member_Key is: 0

Has anyone seen this behaviour before? If so, how do we fix it?

Thank you.

Hi,

Have you set the NameColumn to look at the same field? And is it a datatype of WChar?

Matt|||

Hi Matt,

Thanks for the reply. Yes, setting the NameColumn to the field was one of the things i tried. The database field is NUMERIC(15,9), the KeyColumn is a datatype of Double and the NameColumn is a datatype of WChar. Even with the NameColumn set to the same field, I still get a blank where the 0 value should be.

-Robbie

A difficult Combining Rows problem

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in one hour chunks, like below:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
This involves several things:
- Recognizing that there are variable # of rows (maybe we only have 3
15 minute entries instead of 4)
- Getting a min of those row's min column
- Getting a max of those row's max column
- Getting a total for those row's points column
- Input to any view or whatver would be based on the startTime and
endTime and would always be in whole hours.
I have a feeling that I am going to be doing this all in the C# .NET
end of things, but it's at least worth a shot asking all of you SQL
experts. What I am basically interested in knowing is, do you all
think that this is possible using views or stored procedures or
something else I don't know about. I didn't even know about views
until i started researching how to do this.
Any ideas? Is this possible? Should I just give up and do it on the
C# end of things? Seems to me that it might be possible to do in a
stored procedure, but possible not worth my time. I aprpeciate any
help or suggestions.
JasonTry this:
SELECT groupid,
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'20050101')),
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'2005-01-01T00:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(HH,'20050101',starttime) ;
David Portas
SQL Server MVP
--|||Hi
Check out the dateadd/datepart functions in Books Online for rounding times.
Try:
SELECT GROUPID, DATEADD(mi,-DATEPART(mi,Starttime),Starttime) AS StartTime,
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(mi,-DATEPART(mi,Starttime),Starttime),
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
John
"Factor" wrote:

> Greetings,
> I'm working to combine rows based on a time window and I am hoping to
> be able to write a stored procedure to do this for me, rather than have
> parse through all this data in my program. I'm not very well versed
> with T-SQL syntax.. just enough to get by selecting using inner joins,
> updating and inserting... thats about it. (Hence why I am here.)
> The raw data I have below looks like this:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
> 1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
> 1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
> 1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
> 1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
> 1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
> 1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
> 1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
> Pretty straight forward; you can see each entry is a 15 minute time
> interval. What I want to be able to do is to use a view or a stored
> procedure to view this in one hour chunks, like below:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
> 1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
> This involves several things:
> - Recognizing that there are variable # of rows (maybe we only have 3
> 15 minute entries instead of 4)
> - Getting a min of those row's min column
> - Getting a max of those row's max column
> - Getting a total for those row's points column
> - Input to any view or whatver would be based on the startTime and
> endTime and would always be in whole hours.
> I have a feeling that I am going to be doing this all in the C# .NET
> end of things, but it's at least worth a shot asking all of you SQL
> experts. What I am basically interested in knowing is, do you all
> think that this is possible using views or stored procedures or
> something else I don't know about. I didn't even know about views
> until i started researching how to do this.
> Any ideas? Is this possible? Should I just give up and do it on the
> C# end of things? Seems to me that it might be possible to do in a
> stored procedure, but possible not worth my time. I aprpeciate any
> help or suggestions.
> Jason
>|||John Bell and David Portas,
I will have to read up on these Dateadd/DatePart parameters an actually
interpret what is going on within these statements, but just from what
you gave me here it looks like this will work out very well, and I
really appreciate the insight. This will allow me to vary that time
window fairly easily I do believe, all on a SQL call (that's much
better than bringing back all the data and parsing through it all it.
Thanks again,
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||On 10 Nov 2005 11:41:54 -0800, Factor wrote:

>John
>I have read over those functions and I now understand what they do and
>how to use them, but I am still as to why the min / max /
>total fields actually work. I assume it has something to do with the
>GROUP BY statements, but again, I don't know why.
Hi Jason,
Correct. The GROUP BY tells SQL Server to combine the data from several
rows into one row. This is normally used to report totals, minimum,
maximum per project, per section, etc. But with the appropriate
expression, it cal also be used to combine rows that fit in the same
"period" into one group.
Though John's and David's versions both work, I suggest you go with
Davids version, as this is more flexible. (And, once you get your head
around it, easier to understand as well).
Basically, John's version works by taking each of the date parts you
want to disregard (milliseconds, seconds, minutes), then subtracting
that amount of time from the Starttime. The end result will of course be
the last full hour equal to or before Starttime.
David's version works the other way around - it calculates the number of
full hours that have elapsed since a chosen anchor date, then adds that
number to the chosen anchor date. The result will be the same as John's
expression.
(Note: David chose to just use the number of hours for the group by, and
add it back to the anchor date in the SELECT clause only)

>Assuming black magic happens and thats just how it works, I should just
>be able to change those hh,1 to hh,4 and get 4 hour increments instead.
No. I'll give you two examples how to modify David's query to report on
4-hour intervals and to report on 1/2-hour intervals.
For 4-hour intervals, again calculate the number of hours since an
anchor date. Divide by 4 and truncate, then multiply by 4 again. Add
this number of hours to the anchor date. There you have the start of the
last 4-hour interval
SELECT groupid,
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'20050101')),
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'2005-01-01T03:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
For 1/2-hour intervals, we can't divide the number of hours sice the
anchor date by 0.5, as that won't give us back the precision we already
lost. Instead, we'll have to calculate minutes and divide by 30:
SELECT groupid,
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'20050101')),
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'2005-01-01T00:29:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
In both cases, don't forget to change the shifted anchor value in the
expression for the end point of the interval. Instead of using the same
anchor date, then adding 30 minunte or 4 hours minus one second, the
anchor date is shifted by 30 minutes or 4 hours minus one second.
Now, the above code can still be simplified further. If your table
always has the complete data (as your sample roiws indicate), then you
could change the above queries to:
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
-- or: GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
Note that this might show "holes" in the periods if your real data is
not as complete as the sample you posted indicates. But the advantage is
that you get rid of the "shifted" anchor date for calculating end time.
Final step would be to put it in a stored procedure and use a parameter
for the interval length (in minutes):
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) / @.Interval;
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jason,
What David Provided is an Excellent query .
Let us see if this query can help you.
Select GID , Min(STime) , Max(ETime) ,
Min(Minimum),Max(Maximum),Sum(Points)[co
lor=darkred]
>From yourTableName Group By[/color]
GID,Convert(varchar,STime,112),DatePart(
hh,STime)
Having same name as Functions/ Keyword sound confusing to me so I
changed them.
With Warm Regards
Jatinder Singh|||Hi
This is easier with David's method (see Hugo's reply for an explanation).
Dividing the number of hours by 4 and dropping the remainder will give you 4
hour chunks when they are multiplied back up. You also need to change the en
d
time to give a 4 hour gap.
SELECT groupid,
MIN(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'20050101')
) AS Starttime,
MAX(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'2005-01-01T03:59:59')
) AS Endtime,
MIN(min) AS [Min],
MAX(max) AS [Max],
SUM(points) AS [Total Points]
FROM Readings
GROUP BY groupid,
4*(DATEDIFF(HH,'20050101',starttime)/4)
John
"Factor" wrote:

> John
> I have read over those functions and I now understand what they do and
> how to use them, but I am still as to why the min / max /
> total fields actually work. I assume it has something to do with the
> GROUP BY statements, but again, I don't know why.
> Assuming black magic happens and thats just how it works, I should just
> be able to change those hh,1 to hh,4 and get 4 hour increments instead.
> When I do that, the Starttime and Endtime values do return correctly
> (although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
> the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
> that black magic that is limiting the MIN/MAX/SUM to one hour is still
> limiting them to one hour even with the altered start and end times.
> I'm unsure how to fix or get around this because I don't yet understand
> what is limiting that max to an hour in the first place. How does this
> work? I've been tripped up GROUP BY things before, it's my kryptonite
> for some reason.
> Hope that is not too confusing, I'm all jumbled in my head.
> I really apprecaite the help with this so far, you've all been
> wonderful.
> Jason
>|||Wondeful! Lots ot take in, I thank everyone for their help. I've made
a lot of progress and I've learned a TON about SQL int he past two
days.
I hope I can help you all in the future with something!
Thanks again,
Jason

A differential backup that seems to darn big...

I have a db that is currently about 7 gb. It is currently on a test
instance of SQL 2005. It is not being used by anyone but me for the
purpose of learning one or two things about 2005. Here is what I deem
odd:
If i do a full backup of the database, I get a backup of 7 gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
I was started to see a pattern. I can guarantee there are no other
users and that I didn't change the database in between backups.
tia,
SteveHi
From BOL:
"A differential backup is based on the most recent, previous full backup of
the data that is included in the differential backup. A differential backup
captures only the data that has changed since that full backup. This is know
n
as the base of the differential. A differential backup includes only the dat
a
that have changed since the differential base. "
If you don't do another full backup between the differential backups they
will only get bigger if someone changes the data, and will stay the same if
they don't.
You don't say how old your base is, but the size of the differential backups
indicates a significant amount of changes. Have you re-indexes or shrunk the
files since the full backup?
John
"Not the Face" wrote:

> I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>|||"Not the Face" <nottheface@.gmail.com> wrote in message
news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
>I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
>
How large is the transaction log?
I believe it's backing up the entire transaction log PLUS any changes in the
database.

> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>|||Sorry about the lag and I appreciate the responses.
The DB is currently ~7.6 gb.
The Transaction Log is currently 5 mb.
I have tried shrinking the DB and Log files (shrinking the whole DB and
each file individually)
This is on a test system, so I have control over the database changing.
It isn't. I was literally doing the differential immediately after
the full backup.
Thanks for your help.
Steve.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Not the Face" <nottheface@.gmail.com> wrote in message
> news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
> How large is the transaction log?
> I believe it's backing up the entire transaction log PLUS any changes in t
he
> database.
>|||Hi
4.7GB does seem large for a differential backup. If you ran the backups as a
script such as:
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 1
0
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
Then a directory of C:\backups gives
Directory of C:\Backups
17/01/2007 15:18 <DIR> .
17/01/2007 15:18 <DIR> ..
17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
17/01/2007 15:18 171,002,368 AdventureworksFull.bak
4 File(s) 174,401,536 bytes
2 Dir(s) 1,241,235,456 bytes free
This shows what you would expect.
John
"Not the Face" wrote:

> Sorry about the lag and I appreciate the responses.
> The DB is currently ~7.6 gb.
> The Transaction Log is currently 5 mb.
> I have tried shrinking the DB and Log files (shrinking the whole DB and
> each file individually)
> This is on a test system, so I have control over the database changing.
> It isn't. I was literally doing the differential immediately after
> the full backup.
> Thanks for your help.
> Steve.
> Greg D. Moore (Strider) wrote:
>|||Yeah. So here was the problem.
Turns out that if you Shrink the DB and Log files after the backup, it
wants to make a really large differential backup for some reason.
Even though you have *changed* the data at all.
You just *moved* the data around a bit. You know. All of it (most of
it anyway).
Woops. I moved the Shrinks in front of the backup and now my
differential is 1,121 kb. Seems a bit more reasonable.
Thanks for the pointing of the fingers in the right direction.
Steve.
On Jan 17, 10:45 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> 4.7GB does seem large for a differential backup. If you ran the backups as
a
> script such as:
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
> N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS =
10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINI
T,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINI
T,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINI
T,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> Then a directory of C:\backups gives
> Directory of C:\Backups
> 17/01/2007 15:18 <DIR> .
> 17/01/2007 15:18 <DIR> ..
> 17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
> 17/01/2007 15:18 171,002,368 AdventureworksFull.bak
> 4 File(s) 174,401,536 bytes
> 2 Dir(s) 1,241,235,456 bytes free
> This shows what you would expect.
> John
>
> "Not the Face" wrote:
>
>
>
>
>
>
>
>
>
>
>sql

A differential backup that seems to darn big...

I have a db that is currently about 7 gb. It is currently on a test
instance of SQL 2005. It is not being used by anyone but me for the
purpose of learning one or two things about 2005. Here is what I deem
odd:
If i do a full backup of the database, I get a backup of 7 gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
I was started to see a pattern. I can guarantee there are no other
users and that I didn't change the database in between backups.
tia,
Steve
Hi
From BOL:
"A differential backup is based on the most recent, previous full backup of
the data that is included in the differential backup. A differential backup
captures only the data that has changed since that full backup. This is known
as the base of the differential. A differential backup includes only the data
that have changed since the differential base. "
If you don't do another full backup between the differential backups they
will only get bigger if someone changes the data, and will stay the same if
they don't.
You don't say how old your base is, but the size of the differential backups
indicates a significant amount of changes. Have you re-indexes or shrunk the
files since the full backup?
John
"Not the Face" wrote:

> I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>
|||"Not the Face" <nottheface@.gmail.com> wrote in message
news:1166552868.097104.258360@.73g2000cwn.googlegro ups.com...
>I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
>
How large is the transaction log?
I believe it's backing up the entire transaction log PLUS any changes in the
database.

> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>
|||Sorry about the lag and I appreciate the responses.
The DB is currently ~7.6 gb.
The Transaction Log is currently 5 mb.
I have tried shrinking the DB and Log files (shrinking the whole DB and
each file individually)
This is on a test system, so I have control over the database changing.
It isn't. I was literally doing the differential immediately after
the full backup.
Thanks for your help.
Steve.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Not the Face" <nottheface@.gmail.com> wrote in message
> news:1166552868.097104.258360@.73g2000cwn.googlegro ups.com...
> How large is the transaction log?
> I believe it's backing up the entire transaction log PLUS any changes in the
> database.
>
|||Hi
4.7GB does seem large for a differential backup. If you ran the backups as a
script such as:
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
Then a directory of C:\backups gives
Directory of C:\Backups
17/01/2007 15:18 <DIR> .
17/01/2007 15:18 <DIR> ..
17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
17/01/2007 15:18 171,002,368 AdventureworksFull.bak
4 File(s) 174,401,536 bytes
2 Dir(s) 1,241,235,456 bytes free
This shows what you would expect.
John
"Not the Face" wrote:

> Sorry about the lag and I appreciate the responses.
> The DB is currently ~7.6 gb.
> The Transaction Log is currently 5 mb.
> I have tried shrinking the DB and Log files (shrinking the whole DB and
> each file individually)
> This is on a test system, so I have control over the database changing.
> It isn't. I was literally doing the differential immediately after
> the full backup.
> Thanks for your help.
> Steve.
> Greg D. Moore (Strider) wrote:
>
|||Yeah. So here was the problem.
Turns out that if you Shrink the DB and Log files after the backup, it
wants to make a really large differential backup for some reason.
Even though you have *changed* the data at all.
You just *moved* the data around a bit. You know. All of it (most of
it anyway).
Woops. I moved the Shrinks in front of the backup and now my
differential is 1,121 kb. Seems a bit more reasonable.
Thanks for the pointing of the fingers in the right direction.
Steve.
On Jan 17, 10:45 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> 4.7GB does seem large for a differential backup. If you ran the backups as a
> script such as:
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
> N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> Then a directory of C:\backups gives
> Directory of C:\Backups
> 17/01/2007 15:18 <DIR> .
> 17/01/2007 15:18 <DIR> ..
> 17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
> 17/01/2007 15:18 171,002,368 AdventureworksFull.bak
> 4 File(s) 174,401,536 bytes
> 2 Dir(s) 1,241,235,456 bytes free
> This shows what you would expect.
> John
>
> "Not the Face" wrote:
>
>
>
>
>

A differential backup that seems to darn big...

I have a db that is currently about 7 gb. It is currently on a test
instance of SQL 2005. It is not being used by anyone but me for the
purpose of learning one or two things about 2005. Here is what I deem
odd:
If i do a full backup of the database, I get a backup of 7 gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
I was started to see a pattern. I can guarantee there are no other
users and that I didn't change the database in between backups.
tia,
Steve"Not the Face" <nottheface@.gmail.com> wrote in message
news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
>I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
>
How large is the transaction log?
I believe it's backing up the entire transaction log PLUS any changes in the
database.
> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>|||Sorry about the lag and I appreciate the responses.
The DB is currently ~7.6 gb.
The Transaction Log is currently 5 mb.
I have tried shrinking the DB and Log files (shrinking the whole DB and
each file individually)
This is on a test system, so I have control over the database changing.
It isn't. I was literally doing the differential immediately after
the full backup.
Thanks for your help.
Steve.
Greg D. Moore (Strider) wrote:
> "Not the Face" <nottheface@.gmail.com> wrote in message
> news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
> >I have a db that is currently about 7 gb. It is currently on a test
> > instance of SQL 2005. It is not being used by anyone but me for the
> > purpose of learning one or two things about 2005. Here is what I deem
> > odd:
> >
> > If i do a full backup of the database, I get a backup of 7 gb.
> > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > gb.
> > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > gb.
> > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > gb.
> >
> How large is the transaction log?
> I believe it's backing up the entire transaction log PLUS any changes in the
> database.
>
> > I was started to see a pattern. I can guarantee there are no other
> > users and that I didn't change the database in between backups.
> >
> > tia,
> > Steve
> >|||Yeah. So here was the problem.
Turns out that if you Shrink the DB and Log files after the backup, it
wants to make a really large differential backup for some reason.
Even though you have *changed* the data at all.
You just *moved* the data around a bit. You know. All of it (most of
it anyway).
Woops. I moved the Shrinks in front of the backup and now my
differential is 1,121 kb. Seems a bit more reasonable.
Thanks for the pointing of the fingers in the right direction.
Steve.
On Jan 17, 10:45 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> 4.7GB does seem large for a differential backup. If you ran the backups as a
> script such as:
> BACKUP DATABASE [AdventureWorks] TO DISK => N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME => N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK => N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK => N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK => N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> Then a directory of C:\backups gives
> Directory of C:\Backups
> 17/01/2007 15:18 <DIR> .
> 17/01/2007 15:18 <DIR> ..
> 17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
> 17/01/2007 15:18 171,002,368 AdventureworksFull.bak
> 4 File(s) 174,401,536 bytes
> 2 Dir(s) 1,241,235,456 bytes free
> This shows what you would expect.
> John
>
> "Not the Face" wrote:
> > Sorry about the lag and I appreciate the responses.
> > The DB is currently ~7.6 gb.
> > The Transaction Log is currently 5 mb.
> > I have tried shrinking the DB and Log files (shrinking the whole DB and
> > each file individually)
> > This is on a test system, so I have control over the database changing.
> > It isn't. I was literally doing the differential immediately after
> > the full backup.
> > Thanks for your help.
> > Steve.
> > Greg D. Moore (Strider) wrote:
> > > "Not the Face" <notthef...@.gmail.com> wrote in message
> > >news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
> > > >I have a db that is currently about 7 gb. It is currently on a test
> > > > instance of SQL 2005. It is not being used by anyone but me for the
> > > > purpose of learning one or two things about 2005. Here is what I deem
> > > > odd:
> > > > If i do a full backup of the database, I get a backup of 7 gb.
> > > > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > > > gb.
> > > > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > > > gb.
> > > > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > > > gb.
> > > How large is the transaction log?
> > > I believe it's backing up the entire transaction log PLUS any changes in the
> > > database.
> > > > I was started to see a pattern. I can guarantee there are no other
> > > > users and that I didn't change the database in between backups.
> > > > tia,
> > > > Steve- Hide quoted text -- Show quoted text -

A different question about SQL Server Access Denied

I have 2 development servers, both of which I need to use to run an ASP.NET app. The app connects to an external SQL Server with SQL Server authentication only. One of my servers connects properly, but the same exact app running on the second server generates the dreaded SQL Server Access Denied message. A traditional .asp file running on the second server does connect successfully to the database, so I have deduced that the problem is related either to the ASPNET account on the second server, or else to the structure of the server itself or IIS.

I have verified on both machines that IIS uses the ASPNET account for anonymous access. The only difference I can find is that the working server is using NTFS (and the VS_Developers account has full permission on my application's directory), while the problem server is using FAT. Does anyone know if it the FAT file system could be my problem? If so, should I convert to NTFS, or is there another solution? What else could I look at on the problem server? Any help would be greatly appreciated. Thanks.If you are using SQL Server authentication then using IIS anonymous accounts and ASP.Net accounts are not involved in the authentication. What is the exact message that appears? Login failed for user [...] ?

If you are using SQL server with SQL server authentication then in the connection string you should be providing the UID and Password of a SQL Server Login and there should be no "integrated security" clause in the connection string.|||Thanks for the reply. The error message is: "SQL Server does not exist or access is denied". There is no login failure error occurring.

Here is my connection string:
"Data Source=mySQLIPhere,1433;Network Library=DBMSSOCN;Initial Catalog=myDBName;User ID=mySQLUser;Password=mySQLPw;"

As I mentioned, this connection string works fine from one of the 2 development servers as well as from the production web server, so I don't think the connection string is the problem.|||Seems ok to me; if you are willing to do some experiments try these in order.

Verify that you can access mySQLIPhere machine from the machine causing the problem. e.g. by trying to open \\mySQLIPhere or by pinging.

Use query analyzer to connect to SQL Server from the machine causing problem. In "connect to SQL Server" dialog box provide mySQLIPhere as Sql Server name, select Sql Sever Auth, loginname and password. If this works you may experiment rewriting your connection string bit by bit e.g.:
"Data Source=mySQLIPhere; User ID=mySQLUser; Password=mySQLPw;"
"Data Source=mySQLIPhere,1433; User ID=mySQLUser; Password=mySQLPw;"
"Data Source=mySQLIPhere;Initial Catalog=myDBName; User ID=mySQLUser; Password=mySQLPw;"

It is most probably the SQL Server not exist (not found) part that looks true, rather than the access denied part.

A Different Login failed for user sa problem

Hello,

I know there are already topics on the "Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection" problem but I think this one might be different.

I am working with 2 servers, both running Windows Server 2003 with 1 running SQL Server 2000 and the other acting as a Web Server, and a PC running Windows XP Pro as a development machine - all of which are on the same domain.

I am developing a couple of ASP.NET web applications, and this is where the problem occurs. If I try to connect to the SQL database with the web application running on the development PC I get the "Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection" error message.

This is where it gets interesting. If I take a copy of the code that is running on the development PC and run it on the Web Server, it is able to connect to the SQL database without any problems.

The Authentication mode is set to SQL Server and Windows. I have tried several different things with our Techs and we get the same problem.

Any ideas or suggestions would be greatly appreciated as we are totally stumped.

Thanks,
AlHowdy

Never, ever, ever, ever has NT authentication on SQL been bullet proof...then mix that with multiple versions of VB & SQL & W2K & W2K3 and....aw man...game over.....

Worth trying using plain SQL authentication ( if thats acceptible in your environment ) and see how that goes - at least it will get your app working initially. Then work on the NT authentication bit.

You may wind up having to wait for the next SP for everything if its a bug.

Cheers,

SG.|||All I am using at the moment is SQL authentication and I have absolutely no interest in using NT or Windows Authentication.

Thanks anyway,
Al|||Howdy,

Sounds like connection thinks it is using a trusted connection...can you force it to be sure its using straight SQL authentication? I have seen similar errors with sql configured apps trying to use ODBC connections configured fro NT authentication.

HTH

Cheers,

SG|||Go to the enterprise manager, right click on the server, in the properties, select security tab and select the first option(SQL Server and Windows NT) in the Authentication.

Now try connecting from your application.