Filter a Model Table?

I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.

A 2 part question, simple question first:

1. How can I filter the records in a Model Entity? I thought this would be possible from a Perspective but it is not.

2. How can I filter the records in a Model Entity based on the locale of the person who is logged on? And also based on their permissions?

TIA

You are correct that this is not possible with Perspectives. Note that Perspectives do not secure your data in any way.

You can use Model Item Security and Security Filters to expose different records to different users. A security filter could include a formula filter condition that uses the GETUSERCULTURE function. However, this seems a little odd, since culture is trivial to “spoof”, so it doesn’t really secure anything.

--Bob

|||Thanks for the reply Bob.

So I am in SQL Server Management Studio, I have double clicked on the model and gone to Model Item Security. But, I cannot see where to set up a Security Filter? Also, when I seach on SQL Server 2005 BOL for "Security Filter" it finds nothing.

|||Hi, can someone please help me with this ... please. People keep talking about Security Filter Scripts but I cannot find them anywhere ...|||Ummm, well can someone then please just tell me if this is a difficult question? I have seen a few other posts on the same question and the answer is never posted. Can someone at MSFT please just put me out of my misery and tell me either how to use this facility or, even better, refer me to the associated documentation or, not so good, that the feature is a figment of our collective imaginations. :)|||SecurityFilters is a collection property of a model entity. You "turn on" security filters by adding at least one filter attribute to this collection (i.e. if this collection is empty, security filters are "off" and all users with permission to the entity will be able to see all rows). Each filter in the collection defines a set of rows to which a user or group may be granted access. You can grant access to a specific user or group by giving them permission to see the filter attribute using Model Item Security. Users will have access to the UNION of all rows exposed by the security filters for which they have permission. Note 1: Filter attributes are typically used only for security filters, so the Hidden property is usually set to true. Note 2: Model entities also have a DefaultSecurityFilter property which can be used to grant access to some set of rows for users that do not have access to any of the filter attributes in the SecurityFilters collection.|||I am afraid this is getting very frustrating for me. Your answer here sounds very nice Bob - but it does not tell me how to do it. Also if I search google and microsoft for SecurityFilters or look for books I find no further help. I have a book on Report Services 2000, but obviously it does not cover of Report Models (a 2005 feature).

I am using v8.0.50215.44 of Visual Studio 2005. And I am using v9.00.1187.00 of Microsoft SQL Server Management Studio. Maybe these versions are too old?

If I open up the model designer and click on a model entity to see it's properties, then where do I add in collection properties for the model entity? Especially, how can I say it is a filter attribute? I can see no such properties.

TIA|||I'm sorry this has been frustrating. Let me try to fill in some of the gaps here.
SQL 2005 documentation ("SQL Books Online") is not available on the web yet. It sounds like you have the July CTP build installed; I'm not sure what state the documentation was in back then. Even if you can find the SecurityFilters property, it may have just been stub docs at that point.
If you want, you can download the September CTP docs here . RTM bits (including docs) are now available to MSDN subscribers, and will be publicly available after the launch next week.
SecurityFilters is a property on a model entity. You should see it near the end of the list in the property grid in Model Designer. Like several other model entity properties (e.g. IdentifyingAttributes, DefaultDetailAttributes, SortAttributes), it contains a set of references to model attributes you have previously defined. The only constraint on this particular collection is that all attributes referenced by it must have DataType=Boolean and IsFilter=True. The easiest way to create such an attribute is to select the entity on which it will be defined, right-click on an empty area of the attribute list, and choose New->Filter.
Hopefully this is enough to help you get started.|||Many thanks Bob! Yes, the problem has been that I have had the July CTP build. That info looks perfect to get me started - I appreciate your help.

I'll go get the Septmeber CTP build and doco now and work from there.

I'm looking forward to the release date - I am registered for the one day launch here in NZ - should be grand! :)|||Current books online still has very few things to say about secufity filters on report models. Does anyone know of any sites or articles that would greatly explain how to do this?|||

Hi Bob,

Your article on http://msdn2.microsoft.com/en-us/library/ms365343.aspx is very helpful, but when I use SQL Server Management Studio to set Model Item Security for differnt security filters, it seems "Permissions" property surpass "Model Item Security" property.

For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;

in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.

My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?

I posted my issue on http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1905805&SiteID=17, I attach my post here:

"

I read the following on article http://msdn2.microsoft.com/en-us/library/ms365343.aspx:

"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."

So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.

I can only get one result at a time but not both:

either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.

Anybody please please help me? Thank you!

"

Report server is using Custom Authentication.

Thank you for your help.

Temple1

Filter a Model Table?

I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.

A 2 part question, simple question first:

1. How can I filter the records in a Model Entity? I thought this would be possible from a Perspective but it is not.

2. How can I filter the records in a Model Entity based on the locale of the person who is logged on? And also based on their permissions?

TIA

You are correct that this is not possible with Perspectives. Note that Perspectives do not secure your data in any way.

You can use Model Item Security and Security Filters to expose different records to different users. A security filter could include a formula filter condition that uses the GETUSERCULTURE function. However, this seems a little odd, since culture is trivial to “spoof”, so it doesn’t really secure anything.

--Bob

|||Thanks for the reply Bob.

So I am in SQL Server Management Studio, I have double clicked on the model and gone to Model Item Security. But, I cannot see where to set up a Security Filter? Also, when I seach on SQL Server 2005 BOL for "Security Filter" it finds nothing.|||Hi, can someone please help me with this ... please. People keep talking about Security Filter Scripts but I cannot find them anywhere ...|||Ummm, well can someone then please just tell me if this is a difficult question? I have seen a few other posts on the same question and the answer is never posted. Can someone at MSFT please just put me out of my misery and tell me either how to use this facility or, even better, refer me to the associated documentation or, not so good, that the feature is a figment of our collective imaginations. :)|||SecurityFilters is a collection property of a model entity. You "turn on" security filters by adding at least one filter attribute to this collection (i.e. if this collection is empty, security filters are "off" and all users with permission to the entity will be able to see all rows). Each filter in the collection defines a set of rows to which a user or group may be granted access. You can grant access to a specific user or group by giving them permission to see the filter attribute using Model Item Security. Users will have access to the UNION of all rows exposed by the security filters for which they have permission. Note 1: Filter attributes are typically used only for security filters, so the Hidden property is usually set to true. Note 2: Model entities also have a DefaultSecurityFilter property which can be used to grant access to some set of rows for users that do not have access to any of the filter attributes in the SecurityFilters collection.|||I am afraid this is getting very frustrating for me. Your answer here sounds very nice Bob - but it does not tell me how to do it. Also if I search google and microsoft for SecurityFilters or look for books I find no further help. I have a book on Report Services 2000, but obviously it does not cover of Report Models (a 2005 feature).

I am using v8.0.50215.44 of Visual Studio 2005. And I am using v9.00.1187.00 of Microsoft SQL Server Management Studio. Maybe these versions are too old?

If I open up the model designer and click on a model entity to see it's properties, then where do I add in collection properties for the model entity? Especially, how can I say it is a filter attribute? I can see no such properties.

TIA|||I'm sorry this has been frustrating. Let me try to fill in some of the gaps here.
SQL 2005 documentation ("SQL Books Online") is not available on the web yet. It sounds like you have the July CTP build installed; I'm not sure what state the documentation was in back then. Even if you can find the SecurityFilters property, it may have just been stub docs at that point.
If you want, you can download the September CTP docs here . RTM bits (including docs) are now available to MSDN subscribers, and will be publicly available after the launch next week.
SecurityFilters is a property on a model entity. You should see it near the end of the list in the property grid in Model Designer. Like several other model entity properties (e.g. IdentifyingAttributes, DefaultDetailAttributes, SortAttributes), it contains a set of references to model attributes you have previously defined. The only constraint on this particular collection is that all attributes referenced by it must have DataType=Boolean and IsFilter=True. The easiest way to create such an attribute is to select the entity on which it will be defined, right-click on an empty area of the attribute list, and choose New->Filter.
Hopefully this is enough to help you get started.|||Many thanks Bob! Yes, the problem has been that I have had the July CTP build. That info looks perfect to get me started - I appreciate your help.

I'll go get the Septmeber CTP build and doco now and work from there.

I'm looking forward to the release date - I am registered for the one day launch here in NZ - should be grand! :)|||Current books online still has very few things to say about secufity filters on report models. Does anyone know of any sites or articles that would greatly explain how to do this?|||

Hi Bob,

Your article on http://msdn2.microsoft.com/en-us/library/ms365343.aspx is very helpful, but when I use SQL Server Management Studio to set Model Item Security for differnt security filters, it seems "Permissions" property surpass "Model Item Security" property.

For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;

in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.

My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?

I posted my issue on http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1905805&SiteID=17, I attach my post here:

"

I read the following on article http://msdn2.microsoft.com/en-us/library/ms365343.aspx:

"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."

So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.

I can only get one result at a time but not both:

either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.

Anybody please please help me? Thank you!

"

Report server is using Custom Authentication.

Thank you for your help.

Temple1

fill factors

How come most of the indexes created in MS SQL 2000 the
fill factor is 90 even though the default setting is 0?
Thanks,
LeighAre you running maintenance jobs generated by the maintenance plan =wizard? - it sets fillfactors to 90 by default.
Mike John
"Leigh Wilson" <anonymous@.discussions.microsoft.com> wrote in message =news:069901c3bbea$69d2cd30$a401280a@.phx.gbl...
> How come most of the indexes created in MS SQL 2000 the > fill factor is 90 even though the default setting is 0?
> > Thanks,
> Leigh|||Leigh,
From where did you get the 90% value? Sysindexes?
Perhaps the ones who created the indexes used some GUI tool (like EM
perhaps) which has a default value of 90%? Or someone re-configured the
default value with sp_configure?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Leigh Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:069901c3bbea$69d2cd30$a401280a@.phx.gbl...
> How come most of the indexes created in MS SQL 2000 the
> fill factor is 90 even though the default setting is 0?
> Thanks,
> Leigh

Fileshare Data Driven subscription - Setting up report manager fil

I am trying set up a data driven subscription that will render reports as
PDFs to specific folders in Report Manager. I have setup a fileshare to which
I am pushing reports, but I am having trouble relating specific Report
Manager folders to the fileshares and there is little documentation about
this. Is there a simple way to push PDFs to Report Manager folders via
fileshare subscriptions?
Also, on a data driven subscription, I am setting up the table that will
read the specifications for the data driven subscription. How do a refer to
the PDF rendering method in the table - as "PDF" or " Adobe (PDF) file" or
something else? Also, what is the bit I should use to increment the file if
it already exists? The documentation on data driven subsciptions is really
not adequate to answer these questions.Incidentally, this question relates to Reporting Services 2005
"Mark" wrote:
> I am trying set up a data driven subscription that will render reports as
> PDFs to specific folders in Report Manager. I have setup a fileshare to which
> I am pushing reports, but I am having trouble relating specific Report
> Manager folders to the fileshares and there is little documentation about
> this. Is there a simple way to push PDFs to Report Manager folders via
> fileshare subscriptions?
> Also, on a data driven subscription, I am setting up the table that will
> read the specifications for the data driven subscription. How do a refer to
> the PDF rendering method in the table - as "PDF" or " Adobe (PDF) file" or
> something else? Also, what is the bit I should use to increment the file if
> it already exists? The documentation on data driven subsciptions is really
> not adequate to answer these questions.
>|||Hi Mark,
Not sure I understand third question, but you must provide a different
FileName paramter to the DDS for each report. Whatever logic you want.
Answer to second question is: PDF
As to the first question - I don't think that is possible. DDS can
output to fileshares, but the folders and files in those folders in RM
are controlledby the RS catalog and not accessible outside RM.
As a quick test, I uploaded a PDF file using RM and then searched the
RS Server for the pdf and it was not found. (So RS must be storing it
away somewhere on the server in a secured spot).
Are you sure you really want to do that anyway? The folders may get a
little crowded if you start storing PDF's there. You can, as another
option, store data with the report by using Snapshots.
If you do find a solution for stroing PDF's though, please post it!!
As a workaround, maybe|||Matt - Regarding your last comment, can I arrange the snapshots for the
reports into different folders? My understanding is that you can create a new
snapshot under the history tab for each report, but you cannot move the
history "snapshots" to other folders. Or can you?
"Matt" wrote:
> Hi Mark,
> Not sure I understand third question, but you must provide a different
> FileName paramter to the DDS for each report. Whatever logic you want.
>
> Answer to second question is: PDF
> As to the first question - I don't think that is possible. DDS can
> output to fileshares, but the folders and files in those folders in RM
> are controlledby the RS catalog and not accessible outside RM.
> As a quick test, I uploaded a PDF file using RM and then searched the
> RS Server for the pdf and it was not found. (So RS must be storing it
> away somewhere on the server in a secured spot).
> Are you sure you really want to do that anyway? The folders may get a
> little crowded if you start storing PDF's there. You can, as another
> option, store data with the report by using Snapshots.
> If you do find a solution for stroing PDF's though, please post it!!
> As a workaround, maybe
>

Filegrowth by percent or by size. (And how much).

Yesterday we encountered a problem.
Setting a small testmachine (slow disks) filegrowth at 10 % and
a databasesize of 1.2 Gbyte.
Problem : A simple insert took over 1 minute.
Causing a timeout.
Took some time, but the growing of the database took some time.
Because of the timeout, the extend was not added after the action,
so the next action causes the same problem.
Our production machines are quite a bit faster, so the problem has
not been noticed (yet) on a production machine.
Our development machines are faster as wel, but a glitch once
probably wouldn't be noticed as significant.
So what timing is to be expected by extending the database ?
(Is 64 Mbyte a good size for extending)
Any thoughts about this ?
ben brugmanHi Ben,
In our production environment, we give a good initial size
to the data and log files and set the increment value by
200 MB and not by size. We have found that this is the
best way to handle databases that grow by 100 MB in a week
and more than 2 GB in size... In this way, we reduce
frequent growth of dbs.
regards,
bharath
mcdba
>--Original Message--
>Yesterday we encountered a problem.
>Setting a small testmachine (slow disks) filegrowth at 10
% and
>a databasesize of 1.2 Gbyte.
>Problem : A simple insert took over 1 minute.
>Causing a timeout.
>Took some time, but the growing of the database took some
time.
>Because of the timeout, the extend was not added after
the action,
>so the next action causes the same problem.
>Our production machines are quite a bit faster, so the
problem has
>not been noticed (yet) on a production machine.
>Our development machines are faster as wel, but a glitch
once
>probably wouldn't be noticed as significant.
>So what timing is to be expected by extending the
database ?
>(Is 64 Mbyte a good size for extending)
>Any thoughts about this ?
>ben brugman
>
>.
>|||Can you give an indication of the 'size' of your machine
and the time it takes to extend by 200 MB ?
ben
"bharath" <barathsing@.hotmail.com> wrote in message
news:a20401c3b7d3$6dc70d90$a601280a@.phx.gbl...
> Hi Ben,
> In our production environment, we give a good initial size
> to the data and log files and set the increment value by
> 200 MB and not by size. We have found that this is the
> best way to handle databases that grow by 100 MB in a week
> and more than 2 GB in size... In this way, we reduce
> frequent growth of dbs.
> regards,
> bharath
> mcdba
>
> >--Original Message--
> >Yesterday we encountered a problem.
> >Setting a small testmachine (slow disks) filegrowth at 10
> % and
> >a databasesize of 1.2 Gbyte.
> >
> >Problem : A simple insert took over 1 minute.
> >Causing a timeout.
> >
> >Took some time, but the growing of the database took some
> time.
> >Because of the timeout, the extend was not added after
> the action,
> >so the next action causes the same problem.
> >
> >Our production machines are quite a bit faster, so the
> problem has
> >not been noticed (yet) on a production machine.
> >Our development machines are faster as wel, but a glitch
> once
> >probably wouldn't be noticed as significant.
> >
> >So what timing is to be expected by extending the
> database ?
> >(Is 64 Mbyte a good size for extending)
> >
> >Any thoughts about this ?
> >
> >ben brugman
> >
> >
> >.
> >

Filegroup full

Hi - I am no exprt at setting up SQL Server and am in a situation where I am
the ONLY SQL Server resource around now. I have set up a Db where the
primary file group was set to the servers c:\ drive which only has about 5Gb
of space and only about 200mb left. I can't find anything on the c:\ drive
to delete to create some space.
The tables I am setting up are large - many millions of records. I have
managed to assign space on a much bigger disk (50Gb) but can't delete the
space allocated to the c:\ drive. I assume it is the default drive where
system files are kept.
The problem I am now getting is that I can no longer even save a DTS - I get
a message saying no more disk space.
Is there any way I can ensure that I can either get rid of the allocation to
the c:\ disk or force SQL Server to save system files to the bigger disk.
I did find an option to set new Db's to the bigger drive so I could delete
the Db and recreate in a new DB.
Any thoughts anyone?
TIA
Andreww
One idea would be to create a separate filegroup on a bigger drive, drop and
re-create all your clustered indexes and use the ON keyword to specify the
new filegroup.
In theory, you should be able to get all of the data out of the filegroup
that is only on C:\ and then you should be able to remove the allocation by
using ALTER DATABASE ... REMOVE FILE
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.u k...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>
|||You could also detach the database, move the MDF/LDF files to the bigger
drive, and re-attach them. Or BACKUP the database and then RESTORE...WITH
MOVE.
Also see the following (thanks to Andrew Kelly):
http://support.microsoft.com/?id=314546 Moving DB's between Servers
http://support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.u k...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>
|||Aaron - I have created a filegroup on a much bigger drive (I may not have
used the correct terminology in my original post).
I don't have any clustered indexes so will have a go with the ALTER
DATABASE...REMOVE FILE command.
Cheers
Andrew
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uAfESLdbEHA.996@.TK2MSFTNGP12.phx.gbl...
> One idea would be to create a separate filegroup on a bigger drive, drop
and
> re-create all your clustered indexes and use the ON keyword to specify the
> new filegroup.
> In theory, you should be able to get all of the data out of the filegroup
> that is only on C:\ and then you should be able to remove the allocation
by[vbcol=seagreen]
> using ALTER DATABASE ... REMOVE FILE
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Andreww" <andrew@.fake.com> wrote in message
> news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.u k...
I[vbcol=seagreen]
> am
> 5Gb
> drive
the[vbcol=seagreen]
where[vbcol=seagreen]
> get
allocation[vbcol=seagreen]
> to
disk.[vbcol=seagreen]
delete
>
|||> I don't have any clustered indexes so will have a go with the ALTER
> DATABASE...REMOVE FILE command.
You don't have any primary keys (they are created as clustered by default)?
You could create a clustered index on each table. They certainly aren't
going to hurt.
However, it would likely be easier to BACKUP and then RESTORE ... WITH MOVE
or detach/re-attach. I prefer the latter because it is typically quicker.
BTW, I've never tried to remove a file that is non-empty.
http://www.aspfaq.com/
(Reverse address to reply.)
|||Sorted:
1. detached db
2. Moved data and log files to big drive
3. reattached db
... so far so good!
Thanks Aaron
Andrew
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.u k...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>

Filegroup full

Hi - I am no exprt at setting up SQL Server and am in a situation where I am
the ONLY SQL Server resource around now. I have set up a Db where the
primary file group was set to the servers c:\ drive which only has about 5Gb
of space and only about 200mb left. I can't find anything on the c:\ drive
to delete to create some space.
The tables I am setting up are large - many millions of records. I have
managed to assign space on a much bigger disk (50Gb) but can't delete the
space allocated to the c:\ drive. I assume it is the default drive where
system files are kept.
The problem I am now getting is that I can no longer even save a DTS - I get
a message saying no more disk space.
Is there any way I can ensure that I can either get rid of the allocation to
the c:\ disk or force SQL Server to save system files to the bigger disk.
I did find an option to set new Db's to the bigger drive so I could delete
the Db and recreate in a new DB.
Any thoughts anyone?
TIA
AndrewwOne idea would be to create a separate filegroup on a bigger drive, drop and
re-create all your clustered indexes and use the ON keyword to specify the
new filegroup.
In theory, you should be able to get all of the data out of the filegroup
that is only on C:\ and then you should be able to remove the allocation by
using ALTER DATABASE ... REMOVE FILE
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>|||You could also detach the database, move the MDF/LDF files to the bigger
drive, and re-attach them. Or BACKUP the database and then RESTORE...WITH
MOVE.
Also see the following (thanks to Andrew Kelly):
http://support.microsoft.com/?id=314546 Moving DB's between Servers
http://support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>|||Aaron - I have created a filegroup on a much bigger drive (I may not have
used the correct terminology in my original post).
I don't have any clustered indexes so will have a go with the ALTER
DATABASE...REMOVE FILE command.
Cheers
Andrew
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uAfESLdbEHA.996@.TK2MSFTNGP12.phx.gbl...
> One idea would be to create a separate filegroup on a bigger drive, drop
and
> re-create all your clustered indexes and use the ON keyword to specify the
> new filegroup.
> In theory, you should be able to get all of the data out of the filegroup
> that is only on C:\ and then you should be able to remove the allocation
by
> using ALTER DATABASE ... REMOVE FILE
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Andreww" <andrew@.fake.com> wrote in message
> news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> > Hi - I am no exprt at setting up SQL Server and am in a situation where
I
> am
> > the ONLY SQL Server resource around now. I have set up a Db where the
> > primary file group was set to the servers c:\ drive which only has about
> 5Gb
> > of space and only about 200mb left. I can't find anything on the c:\
> drive
> > to delete to create some space.
> >
> > The tables I am setting up are large - many millions of records. I have
> > managed to assign space on a much bigger disk (50Gb) but can't delete
the
> > space allocated to the c:\ drive. I assume it is the default drive
where
> > system files are kept.
> >
> > The problem I am now getting is that I can no longer even save a DTS - I
> get
> > a message saying no more disk space.
> >
> > Is there any way I can ensure that I can either get rid of the
allocation
> to
> > the c:\ disk or force SQL Server to save system files to the bigger
disk.
> >
> > I did find an option to set new Db's to the bigger drive so I could
delete
> > the Db and recreate in a new DB.
> >
> > Any thoughts anyone?
> >
> > TIA
> >
> > Andreww
> >
> >
>|||> I don't have any clustered indexes so will have a go with the ALTER
> DATABASE...REMOVE FILE command.
You don't have any primary keys (they are created as clustered by default)?
You could create a clustered index on each table. They certainly aren't
going to hurt.
However, it would likely be easier to BACKUP and then RESTORE ... WITH MOVE
or detach/re-attach. I prefer the latter because it is typically quicker.
BTW, I've never tried to remove a file that is non-empty.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Sorted:
1. detached db
2. Moved data and log files to big drive
3. reattached db
... so far so good!
Thanks Aaron
Andrew
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>

Filegroup full

Hi - I am no exprt at setting up SQL Server and am in a situation where I am
the ONLY SQL Server resource around now. I have set up a Db where the
primary file group was set to the servers c:\ drive which only has about 5Gb
of space and only about 200mb left. I can't find anything on the c:\ drive
to delete to create some space.
The tables I am setting up are large - many millions of records. I have
managed to assign space on a much bigger disk (50Gb) but can't delete the
space allocated to the c:\ drive. I assume it is the default drive where
system files are kept.
The problem I am now getting is that I can no longer even save a DTS - I get
a message saying no more disk space.
Is there any way I can ensure that I can either get rid of the allocation to
the c:\ disk or force SQL Server to save system files to the bigger disk.
I did find an option to set new Db's to the bigger drive so I could delete
the Db and recreate in a new DB.
Any thoughts anyone?
TIA
AndrewwOne idea would be to create a separate filegroup on a bigger drive, drop and
re-create all your clustered indexes and use the ON keyword to specify the
new filegroup.
In theory, you should be able to get all of the data out of the filegroup
that is only on C:\ and then you should be able to remove the allocation by
using ALTER DATABASE ... REMOVE FILE
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>|||You could also detach the database, move the MDF/LDF files to the bigger
drive, and re-attach them. Or BACKUP the database and then RESTORE...WITH
MOVE.
Also see the following (thanks to Andrew Kelly):
http://support.microsoft.com/?id=314546 Moving DB's between Servers
http://support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>|||Aaron - I have created a filegroup on a much bigger drive (I may not have
used the correct terminology in my original post).
I don't have any clustered indexes so will have a go with the ALTER
DATABASE...REMOVE FILE command.
Cheers
Andrew
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uAfESLdbEHA.996@.TK2MSFTNGP12.phx.gbl...
> One idea would be to create a separate filegroup on a bigger drive, drop
and
> re-create all your clustered indexes and use the ON keyword to specify the
> new filegroup.
> In theory, you should be able to get all of the data out of the filegroup
> that is only on C:\ and then you should be able to remove the allocation
by
> using ALTER DATABASE ... REMOVE FILE
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Andreww" <andrew@.fake.com> wrote in message
> news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
I[vbcol=seagreen]
> am
> 5Gb
> drive
the[vbcol=seagreen]
where[vbcol=seagreen]
> get
allocation[vbcol=seagreen]
> to
disk.[vbcol=seagreen]
delete[vbcol=seagreen]
>|||> I don't have any clustered indexes so will have a go with the ALTER
> DATABASE...REMOVE FILE command.
You don't have any primary keys (they are created as clustered by default)?
You could create a clustered index on each table. They certainly aren't
going to hurt.
However, it would likely be easier to BACKUP and then RESTORE ... WITH MOVE
or detach/re-attach. I prefer the latter because it is typically quicker.
BTW, I've never tried to remove a file that is non-empty.
http://www.aspfaq.com/
(Reverse address to reply.)|||Sorted:
1. detached db
2. Moved data and log files to big drive
3. reattached db
... so far so good!
Thanks Aaron
Andrew
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>