Field Description Won't Save in MSDE Table

I've been working with numerous tables (creating them, that is) in
MSDE.
Everything's working fine aside from the fact that I can't seem to get
any descriptive information on any field in any table to save.
I add the descriptive text to the field, click to save the table with
the newly-entered description, the hourglass displays and the table is
being saved, and then when the design view of the table refreshes, the
descriptions I've just entered on the various fields are gone.
Anyone else have problems with this?
Thanks!
Sincerely,
Brad H. McCollum
bmccoll1@.midsouth.rr.com
What application are you using to design the table?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0405070905.9a523cb@.posting.google.co m...
> I've been working with numerous tables (creating them, that is) in
> MSDE.
> Everything's working fine aside from the fact that I can't seem to get
> any descriptive information on any field in any table to save.
> I add the descriptive text to the field, click to save the table with
> the newly-entered description, the hourglass displays and the table is
> being saved, and then when the design view of the table refreshes, the
> descriptions I've just entered on the various fields are gone.
> Anyone else have problems with this?
> Thanks!
> Sincerely,
> Brad H. McCollum
> bmccoll1@.midsouth.rr.com
|||I'm using MSDE Manager by Vale Software and haven't found anything
dealing with this issue on their website.
Thanks for any additional information/assistance/recommendations you
might be able to offer.
Sincerely,
Brad H. McCollum
bmccoll1@.midsouth.rr.com
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<#DZOEnFNEHA.808@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> What application are you using to design the table?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
> news:52031869.0405070905.9a523cb@.posting.google.co m...
|||hi Brad,
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> ha scritto nel messaggio
news:52031869.0405100708.c92ddd@.posting.google.com ...
> I'm using MSDE Manager by Vale Software and haven't found anything
> dealing with this issue on their website.
> Thanks for any additional information/assistance/recommendations you
> might be able to offer.
>
coul'd be a "feature" of MSDE Manager, as both Enterprise Manager and my own
management tool, available at the link following my sign., provide the
expectet behaviour...
please contact the application provider...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You should contact the vendor directly. I imagine it's not supported
because column descriptions are kind of superfluous. I tend to shy away
from storing column descriptions in the database, and instead opt for good
documentation on my tables. A user can always look at the documentation but
shouldn't be forced to read a (max 255?) character description by connecting
to the database.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0405100708.c92ddd@.posting.google.com ...
> I'm using MSDE Manager by Vale Software and haven't found anything
> dealing with this issue on their website.
> Thanks for any additional information/assistance/recommendations you
> might be able to offer.
> Sincerely,
> Brad H. McCollum
> bmccoll1@.midsouth.rr.com

Fastest way to delete to huge datarow?

Hi all,
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.de
Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:

> On 04.12.2006 15:29, Gerrit Horeis wrote:
>
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert

Failure to Remove Old Transaction Log Backups

I've just become the DBA. The origianl Maintenance Plan was setup to remove
old TRasact Log Backups. This has not worked in several months. Does anyone
have an idea why a working plan would fail? Everything appears to be set
correctly. Is there any advantage to placing optimimization in a seperate
plan from the backups?
Here is a post from Bill at MS that outlines the most common reasons for
this:
-- Log files don't delete --
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"mark_at_msb" <mark_at_msb@.discussions.microsoft.com> wrote in message
news:486897A4-7C19-4924-845D-0A724D4E4899@.microsoft.com...
> I've just become the DBA. The origianl Maintenance Plan was setup to
remove
> old TRasact Log Backups. This has not worked in several months. Does
anyone
> have an idea why a working plan would fail? Everything appears to be set
> correctly. Is there any advantage to placing optimimization in a seperate
> plan from the backups?

Failure adding a new partition range to a partition function.

Long Post (so you know what I've already tried).
I'll start off with the definitions of existing objects, but in summary, I'm
attempting to add a new range to a partition function, and expect this to be
an almost instantaneous operation, but it is not (and, at the moment, I can't
get it to complete at all).
The partition scheme started off with two groups:
CREATE PARTITION SCHEME [schemePartitonFile] AS PARTITION [fnPartitionFile]
TO ([MeterGroup1], [MeterGroup2])
We have managed to fill the second group (MeterGroup2) (poor planning), and
so need to add a new partition.
MeterGroup2 was specified not to grow past (the size of the LUN it was on -
0.5GB).
The partition function we are using is based on a FileID (FID), and is
currently defined as:
CREATE PARTITION FUNCTION [fnPartitionFile](int) AS RANGE LEFT FOR VALUES
(8000)
So, all files < 8000 go into MeterGroup1, and all Files >= 8000 go into
meter group 2.
There are four tables using the scheme - for example:
CREATE TABLE [dbo].[tbReadIntDevDtl](
[FID] [int] NOT NULL,
[Block] [smallint] NOT NULL,
[Period] [tinyint] NOT NULL,
[IntervalValue] [int] NOT NULL,
[Quality] [char](1) NOT NULL,
CONSTRAINT [PK_tbReadIntDevDtl] PRIMARY KEY CLUSTERED
(
[FID] ASC,
[Block] ASC,
[Period] ASC
)ON [schemePartitonFile]([FID])
)
So with MeterGroup2 filled, we would like to add a new group to the
partition scheme, and direct all new files to that group.
Checked the current max FID: 31976
What we expect to be able to do is:
ALTER PARTITION SCHEME schemePartitonFile NEXT USED MeterGroup3
...followed by...
ALTER PARTITION FUNCTION fnPartitionFile() SPLIT RANGE (31977)
Given that the current max FID is 31976, we would expect to be able to run
the alter partition function statement, and it would not need to do any
physical movement of data, and should complete pretty much instantly,
resulting in the following scheme...
FID < 8000 in Partition 1
FID >= 8000 AND FID < 31977 in Partition 2
FID >= 31977 in Partition 3
At first, I immediately got the following error:
Could not allocate space for object
'dbo.tbReadIntDevDtl'.'PK_tbReadIntDevDtl' in database 'SageEMS' because the
'MeterGroup2' filegroup is full. Create disk space by deleting unneeded
files, dropping objects in the filegroup, adding additional files to the
filegroup, or setting autogrowth on for existing files in the filegroup.
I increased the size of MeterGroup2 to use the 0.5GB of free space on the
LUN, and then the process would run for around 45 minutes, then start rolling
back, ending with an error that the database was out of log.
I increased the log from 15GB to 36GB, and now the process runs for around
45 minutes before rolling back, and ends with the same error that we got at
first - can't increase space in MeterGroup2.
To verify that my understanding of the process of splitting partitions is
correct, I created a new scheme, a new function, and a new copy of the table
in question - identical to existing scheme/function/table in structure.
I then put in the first and last 1000 rows for each range (e.g. top 1000 *
order by FID asc where FID < 8000, then top 1000 * order by FID desc where
FID < 8000, followed by top 1000 * order by FID asc where FID >= 8000 and top
1000 * order by FID desc where FID >= 8000.
As expected, this indicates 2000 rows in each of the two partitions.
Splitting the new partition, and checking the rows shows (as expected) 2000
rows in the first two partitions, and none in the third.
select index_id, partition_number, rows
from sys.partitions
where object_id = object_id('tbReadIntDevDtl')
index_id partition_number rows
-- -- --
1 1 2000
1 2 2000
1 3 0
What could be causing this issue. Have I got something fundamentally wrong
in my understanding of how RANGE SPLIT works?
Thanks
Bernard
P.S. Please, no answers suggesting I should switch partitions, because that
isn't what I'm trying to do. We will switch older partitions out, but here
we simply want to direct new records into a new partition.
> Given that the current max FID is 31976, we would expect to be able to run
> the alter partition function statement, and it would not need to do any
> physical movement of data, and should complete pretty much instantly,
> resulting in the following scheme...
No, what you need to do is to always have an empty partition "at the end". And when you want to
split, you split so you have two empty partitions. I.e., there will be no data movement when you
split one empty partition into two empty partitions. Same basic principle goes for when you purge
data (if you are going to do that). In other words, never populate your "unbound"/"edge" partitions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bernard Sheppard" <BernardSheppard@.discussions.microsoft.com> wrote in message
news:C9A212E7-1A8D-4054-81C5-4E2DA6214025@.microsoft.com...
> Long Post (so you know what I've already tried).
> I'll start off with the definitions of existing objects, but in summary, I'm
> attempting to add a new range to a partition function, and expect this to be
> an almost instantaneous operation, but it is not (and, at the moment, I can't
> get it to complete at all).
> The partition scheme started off with two groups:
> CREATE PARTITION SCHEME [schemePartitonFile] AS PARTITION [fnPartitionFile]
> TO ([MeterGroup1], [MeterGroup2])
> We have managed to fill the second group (MeterGroup2) (poor planning), and
> so need to add a new partition.
> MeterGroup2 was specified not to grow past (the size of the LUN it was on -
> 0.5GB).
> The partition function we are using is based on a FileID (FID), and is
> currently defined as:
> CREATE PARTITION FUNCTION [fnPartitionFile](int) AS RANGE LEFT FOR VALUES
> (8000)
> So, all files < 8000 go into MeterGroup1, and all Files >= 8000 go into
> meter group 2.
> There are four tables using the scheme - for example:
> CREATE TABLE [dbo].[tbReadIntDevDtl](
> [FID] [int] NOT NULL,
> [Block] [smallint] NOT NULL,
> [Period] [tinyint] NOT NULL,
> [IntervalValue] [int] NOT NULL,
> [Quality] [char](1) NOT NULL,
> CONSTRAINT [PK_tbReadIntDevDtl] PRIMARY KEY CLUSTERED
> (
> [FID] ASC,
> [Block] ASC,
> [Period] ASC
> )ON [schemePartitonFile]([FID])
> )
> So with MeterGroup2 filled, we would like to add a new group to the
> partition scheme, and direct all new files to that group.
> Checked the current max FID: 31976
> What we expect to be able to do is:
> ALTER PARTITION SCHEME schemePartitonFile NEXT USED MeterGroup3
> ...followed by...
> ALTER PARTITION FUNCTION fnPartitionFile() SPLIT RANGE (31977)
> Given that the current max FID is 31976, we would expect to be able to run
> the alter partition function statement, and it would not need to do any
> physical movement of data, and should complete pretty much instantly,
> resulting in the following scheme...
> FID < 8000 in Partition 1
> FID >= 8000 AND FID < 31977 in Partition 2
> FID >= 31977 in Partition 3
> At first, I immediately got the following error:
> Could not allocate space for object
> 'dbo.tbReadIntDevDtl'.'PK_tbReadIntDevDtl' in database 'SageEMS' because the
> 'MeterGroup2' filegroup is full. Create disk space by deleting unneeded
> files, dropping objects in the filegroup, adding additional files to the
> filegroup, or setting autogrowth on for existing files in the filegroup.
> I increased the size of MeterGroup2 to use the 0.5GB of free space on the
> LUN, and then the process would run for around 45 minutes, then start rolling
> back, ending with an error that the database was out of log.
> I increased the log from 15GB to 36GB, and now the process runs for around
> 45 minutes before rolling back, and ends with the same error that we got at
> first - can't increase space in MeterGroup2.
> To verify that my understanding of the process of splitting partitions is
> correct, I created a new scheme, a new function, and a new copy of the table
> in question - identical to existing scheme/function/table in structure.
> I then put in the first and last 1000 rows for each range (e.g. top 1000 *
> order by FID asc where FID < 8000, then top 1000 * order by FID desc where
> FID < 8000, followed by top 1000 * order by FID asc where FID >= 8000 and top
> 1000 * order by FID desc where FID >= 8000.
> As expected, this indicates 2000 rows in each of the two partitions.
> Splitting the new partition, and checking the rows shows (as expected) 2000
> rows in the first two partitions, and none in the third.
> select index_id, partition_number, rows
> from sys.partitions
> where object_id = object_id('tbReadIntDevDtl')
> index_id partition_number rows
> -- -- --
> 1 1 2000
> 1 2 2000
> 1 3 0
> What could be causing this issue. Have I got something fundamentally wrong
> in my understanding of how RANGE SPLIT works?
> Thanks
> Bernard
> P.S. Please, no answers suggesting I should switch partitions, because that
> isn't what I'm trying to do. We will switch older partitions out, but here
> we simply want to direct new records into a new partition.
|||Beranrd
Check out this great article
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
"Bernard Sheppard" <BernardSheppard@.discussions.microsoft.com> wrote in
message news:C9A212E7-1A8D-4054-81C5-4E2DA6214025@.microsoft.com...
> Long Post (so you know what I've already tried).
> I'll start off with the definitions of existing objects, but in summary,
> I'm
> attempting to add a new range to a partition function, and expect this to
> be
> an almost instantaneous operation, but it is not (and, at the moment, I
> can't
> get it to complete at all).
> The partition scheme started off with two groups:
> CREATE PARTITION SCHEME [schemePartitonFile] AS PARTITION
> [fnPartitionFile]
> TO ([MeterGroup1], [MeterGroup2])
> We have managed to fill the second group (MeterGroup2) (poor planning),
> and
> so need to add a new partition.
> MeterGroup2 was specified not to grow past (the size of the LUN it was
> on -
> 0.5GB).
> The partition function we are using is based on a FileID (FID), and is
> currently defined as:
> CREATE PARTITION FUNCTION [fnPartitionFile](int) AS RANGE LEFT FOR VALUES
> (8000)
> So, all files < 8000 go into MeterGroup1, and all Files >= 8000 go into
> meter group 2.
> There are four tables using the scheme - for example:
> CREATE TABLE [dbo].[tbReadIntDevDtl](
> [FID] [int] NOT NULL,
> [Block] [smallint] NOT NULL,
> [Period] [tinyint] NOT NULL,
> [IntervalValue] [int] NOT NULL,
> [Quality] [char](1) NOT NULL,
> CONSTRAINT [PK_tbReadIntDevDtl] PRIMARY KEY CLUSTERED
> (
> [FID] ASC,
> [Block] ASC,
> [Period] ASC
> )ON [schemePartitonFile]([FID])
> )
> So with MeterGroup2 filled, we would like to add a new group to the
> partition scheme, and direct all new files to that group.
> Checked the current max FID: 31976
> What we expect to be able to do is:
> ALTER PARTITION SCHEME schemePartitonFile NEXT USED MeterGroup3
> ...followed by...
> ALTER PARTITION FUNCTION fnPartitionFile() SPLIT RANGE (31977)
> Given that the current max FID is 31976, we would expect to be able to run
> the alter partition function statement, and it would not need to do any
> physical movement of data, and should complete pretty much instantly,
> resulting in the following scheme...
> FID < 8000 in Partition 1
> FID >= 8000 AND FID < 31977 in Partition 2
> FID >= 31977 in Partition 3
> At first, I immediately got the following error:
> Could not allocate space for object
> 'dbo.tbReadIntDevDtl'.'PK_tbReadIntDevDtl' in database 'SageEMS' because
> the
> 'MeterGroup2' filegroup is full. Create disk space by deleting unneeded
> files, dropping objects in the filegroup, adding additional files to the
> filegroup, or setting autogrowth on for existing files in the filegroup.
> I increased the size of MeterGroup2 to use the 0.5GB of free space on the
> LUN, and then the process would run for around 45 minutes, then start
> rolling
> back, ending with an error that the database was out of log.
> I increased the log from 15GB to 36GB, and now the process runs for around
> 45 minutes before rolling back, and ends with the same error that we got
> at
> first - can't increase space in MeterGroup2.
> To verify that my understanding of the process of splitting partitions is
> correct, I created a new scheme, a new function, and a new copy of the
> table
> in question - identical to existing scheme/function/table in structure.
> I then put in the first and last 1000 rows for each range (e.g. top 1000 *
> order by FID asc where FID < 8000, then top 1000 * order by FID desc where
> FID < 8000, followed by top 1000 * order by FID asc where FID >= 8000 and
> top
> 1000 * order by FID desc where FID >= 8000.
> As expected, this indicates 2000 rows in each of the two partitions.
> Splitting the new partition, and checking the rows shows (as expected)
> 2000
> rows in the first two partitions, and none in the third.
> select index_id, partition_number, rows
> from sys.partitions
> where object_id = object_id('tbReadIntDevDtl')
> index_id partition_number rows
> -- -- --
> 1 1 2000
> 1 2 2000
> 1 3 0
> What could be causing this issue. Have I got something fundamentally
> wrong
> in my understanding of how RANGE SPLIT works?
> Thanks
> Bernard
> P.S. Please, no answers suggesting I should switch partitions, because
> that
> isn't what I'm trying to do. We will switch older partitions out, but
> here
> we simply want to direct new records into a new partition.
|||Thanks for the reply, Tibor.
That leads to a couple of follow up questions:
With RANGE LEFT, it would appear that the edge partition can't ever have
data (if I want to be able to quickly change ranges), which means that I
can't have a strategy where I fill an edge partition until full, and then
create a new partition and adjust the ranges so new data flows into it.
This is the strategy that I would like to use: we want to be able to load
data into "bucket" (last partition) where we always fill the last bucket
until (nearly) full, then start filling the next bucket. Each bucket is
sized to hold a reasonable amount of data (around 1 billion rows, or
thereabouts), but we can't accurately predict when we will fill a bucket.
We will eventually switch out then delete old data (once it is 7 years after
it was loaded) - but our date partitioning is less to do with the date that
the data is for, and more to do with the date it was loaded.
Any suggestions?
Thanks
"Tibor Karaszi" wrote:

> No, what you need to do is to always have an empty partition "at the end". And when you want to
> split, you split so you have two empty partitions. I.e., there will be no data movement when you
> split one empty partition into two empty partitions. Same basic principle goes for when you purge
> data (if you are going to do that). In other words, never populate your "unbound"/"edge" partitions.
>
|||I'm not sure I follow you, Bernard...

> With RANGE LEFT, it would appear that the edge partition can't ever have
> data (if I want to be able to quickly change ranges), which means that I
> can't have a strategy where I fill an edge partition until full
This is what I have problems with. How can you say "until full" for something which is unrestricted?
I assume that the values you add (what you are partitioning over) are either increasing or
decreasing. Otherwise, you wouldn't know that they go to the "last" or "first" partition. I also
assume that they are restricted in some sense, else you wouldn't be able to do a SPLIT later on.
Based on above, it seems that the values might as well go into a predefined unrestricted partition.
But perhaps it is just me being dense (wouldn't be the first time ;-) ).
To the best of my knowledge, the partition that you SPLIT or partitions that you MERGE need to be
empty to ensure meta-data only operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bernard Sheppard" <BernardSheppard@.discussions.microsoft.com> wrote in message
news:DF45FCAC-2A4B-4481-927C-A17485CEC65C@.microsoft.com...
> Thanks for the reply, Tibor.
> That leads to a couple of follow up questions:
> With RANGE LEFT, it would appear that the edge partition can't ever have
> data (if I want to be able to quickly change ranges), which means that I
> can't have a strategy where I fill an edge partition until full, and then
> create a new partition and adjust the ranges so new data flows into it.
> This is the strategy that I would like to use: we want to be able to load
> data into "bucket" (last partition) where we always fill the last bucket
> until (nearly) full, then start filling the next bucket. Each bucket is
> sized to hold a reasonable amount of data (around 1 billion rows, or
> thereabouts), but we can't accurately predict when we will fill a bucket.
> We will eventually switch out then delete old data (once it is 7 years after
> it was loaded) - but our date partitioning is less to do with the date that
> the data is for, and more to do with the date it was loaded.
> Any suggestions?
> Thanks
>
> "Tibor Karaszi" wrote:
>
>
|||> With RANGE LEFT, it would appear that the edge partition can't ever have
> data (if I want to be able to quickly change ranges), which means that I
> can't have a strategy where I fill an edge partition until full, and then
> create a new partition and adjust the ranges so new data flows into it.
Regardless of the RANGE specification, you have the same issue. Like Tibor
mentioned, if you want to split a partition without data movement, plan so
that the last partition is always empty.

> This is the strategy that I would like to use: we want to be able to load
> data into "bucket" (last partition) where we always fill the last bucket
> until (nearly) full, then start filling the next bucket. Each bucket is
> sized to hold a reasonable amount of data (around 1 billion rows, or
> thereabouts), but we can't accurately predict when we will fill a bucket.
For a sliding window, I suggest you create your partition function to allow
for 2 empty (n and n - 1) partitions. Once you start filling the second
from last one (n - 1), split the last partition (n, which is still empty) to
prepare for the next time around. When you are ready to delete/archive the
oldest partition, simply switch out the first partition and merge it with
the second.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernard Sheppard" <BernardSheppard@.discussions.microsoft.com> wrote in
message news:DF45FCAC-2A4B-4481-927C-A17485CEC65C@.microsoft.com...
> Thanks for the reply, Tibor.
> That leads to a couple of follow up questions:
> With RANGE LEFT, it would appear that the edge partition can't ever have
> data (if I want to be able to quickly change ranges), which means that I
> can't have a strategy where I fill an edge partition until full, and then
> create a new partition and adjust the ranges so new data flows into it.
> This is the strategy that I would like to use: we want to be able to load
> data into "bucket" (last partition) where we always fill the last bucket
> until (nearly) full, then start filling the next bucket. Each bucket is
> sized to hold a reasonable amount of data (around 1 billion rows, or
> thereabouts), but we can't accurately predict when we will fill a bucket.
> We will eventually switch out then delete old data (once it is 7 years
> after
> it was loaded) - but our date partitioning is less to do with the date
> that
> the data is for, and more to do with the date it was loaded.
> Any suggestions?
> Thanks
>
> "Tibor Karaszi" wrote:
>
>
|||Hi Tibor, thanks, you've answered my question (with your very last statement).
Cheers.
"Tibor Karaszi" wrote:

> I'm not sure I follow you, Bernard...
>
> This is what I have problems with. How can you say "until full" for something which is unrestricted?
>
Physically full - each partition will (almost) fill a mounted disk (a SAN
LUN). Whilst I could distribute a filegroup across multiple LUNs, it makes
for more difficult management, and makes life more difficult for piecemeal
restores. If I can stick with a single filegroup per LUN, then my
file/filegroup/partitioning strategy can be automated.
I'm partitioning for a few reasons - recoverability, ability to archive
(read delete, or alternatively move to slow storage (e.g. read only on NTFS
compression), ability to restore subsets of data (e.g. just data loaded in
the last x month) into other environments, etc, ability to utilise multiple
LUNs on a SAN for maximum parallelism, etc.
My main table will grow to several terabytes and hundreds of billions of
rows. Distributing this (evenly) over many small LUNs will allow maintenance
activities such as backup, restore, integrity checks, etc to run at maximum
speed.
I used to use DPVs on SQL Server 2000.

> I assume that the values you add (what you are partitioning over) are either increasing or
> decreasing. Otherwise, you wouldn't know that they go to the "last" or "first" partition. I also
> assume that they are restricted in some sense, else you wouldn't be able to do a SPLIT later on.
>
Absolutely correct. My raw value that I am partitioning over is an identity
column that relates the data back to the batch in which it was recieved -
this ID is then used as part of the PK (and partition scheme input) for the
lower level data.
This means that I am only ever inserting into the last partition (and, for
that matter, physically in order due to the clustered index).

> Based on above, it seems that the values might as well go into a predefined unrestricted partition.
> But perhaps it is just me being dense (wouldn't be the first time ;-) ).
Yes, except I then can't partition across (fixed) size LUNSs.

> To the best of my knowledge, the partition that you SPLIT or partitions
that you MERGE need to be
> empty to ensure meta-data only operations.
That has hit the nail on the head, and tests I've done have confirmed this.
I've read (and re-read) BOL, and whilst it does describe where the new range
value goes (e.g. on LEFT, becomes part of the new partition), and it
indicates that if you split an existing range, data that falls into the new
range gets moved, it doesn't seem to state (at least clearly enough for me to
tell) that, even if there is no data at the split point, if there is any data
in the partition being split, it is not a meta-data only operation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/