Data files and filegroups

Hi all,

Can anyone tell me direct me to any resources on the net on SQL Server data files and filegroups. I want to manage historic data (which is about 500GB and growing) for reporting purposes. I was thinking about creating a data warehouse. But first I want to build a strong file structure for database objects like tables, views, indexes etc.

I got basic information about files and filegroups from books online but I need more juice before I start actual work.

All comments are appreciated and thank you in advance.

Yogi

Hi Yogi...is this SQL 2000 or 2005? (could be some big differences in how you want to handle things between the two...).

If 2005, respond and say so...here are some articles for 2000:

For an overview on the physical architecture and general usage, these are good:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6epf.asp

For data placement and performance (see sub-topics also):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_2upf.asp

For moving data:

http://www.sqljunkies.com/How%20To/B9F7F302-964A-4825-9246-6143A8681900.scuk

Performance tips:

http://www.sql-server-performance.com/filegroups.asp

Also, note that there are some special considerations sometimes for backups, administration, etc. for filegroups. Go to Books Online, move to the index tab, and type 'filegroups' in the textbox...this should bring you to the top-level filegroups item which has 10-15 sub-items...look over those too, great information there.

HTH

|||

Hey,

Thank you very much but I have gone through these links already most of them are similary to BOL.
I am still collecting information comments are welcome!

sql

Data File size issue

Hello everyone,
I have a problem with the data files on one of my databases. I was trying
to manage the file size for a secondary data file (that had blown up on me
because there were no growth limits set initially) by trying something on a
hunch. It was probably a mistake to this, but I wanted to reduce the size of
this file by transferring only part of the data from this file to the other
files in the filegroup. Here's an illustration of what I did:
Original Scenario:
Used Space Free Space Total Space
-- -- --
File 1 18945 MB 0 MB 18945 MB
File 2 87052 MB 10.8 MB 87062 MB
I wanted to reduce the size of secondary data file File 2. So, I created
File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
set growth limits of 30000 MB each for File 1 and File 3, and decided to try
and empty the data from File 2. The theory behind this being that SQL Server
would try to empty File 2, but will only be able to transfer up to the growth
limits set on Files 1 & 2. This way, I will have more evenly displaced usage
of data space. Well, here is what it now looks like:
Used Space Free Space Total Space
-- -- --
File 1 29854 MB 0 MB 29854 MB
File 2 46251 MB 40774 MB 87025 MB
File 3 29652 MB 0 MB 29652 MB
This is all fine and dandy except the fact that I am unable to shrink File
2. No matter what I try, I am unable to get the 40 GB of free space in this
file back to the Operating System. This is an extremely frustrating problem,
and I would appreciate any advice. Please help!!
Thank you!
What about creating two secondary files instead one and using "dbcc
shrinkfile (file_2, , EMPTYFILE)" so the data from file_2 is migrated to the
other files in the filegroup and then deleting file_2 using "alter database".
AMB
"Shishir Viriyala" wrote:

> Hello everyone,
> I have a problem with the data files on one of my databases. I was trying
> to manage the file size for a secondary data file (that had blown up on me
> because there were no growth limits set initially) by trying something on a
> hunch. It was probably a mistake to this, but I wanted to reduce the size of
> this file by transferring only part of the data from this file to the other
> files in the filegroup. Here's an illustration of what I did:
> Original Scenario:
> Used Space Free Space Total Space
> -- -- --
> File 1 18945 MB 0 MB 18945 MB
> File 2 87052 MB 10.8 MB 87062 MB
> I wanted to reduce the size of secondary data file File 2. So, I created
> File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> set growth limits of 30000 MB each for File 1 and File 3, and decided to try
> and empty the data from File 2. The theory behind this being that SQL Server
> would try to empty File 2, but will only be able to transfer up to the growth
> limits set on Files 1 & 2. This way, I will have more evenly displaced usage
> of data space. Well, here is what it now looks like:
> Used Space Free Space Total Space
> -- -- --
> File 1 29854 MB 0 MB 29854 MB
> File 2 46251 MB 40774 MB 87025 MB
> File 3 29652 MB 0 MB 29652 MB
> This is all fine and dandy except the fact that I am unable to shrink File
> 2. No matter what I try, I am unable to get the 40 GB of free space in this
> file back to the Operating System. This is an extremely frustrating problem,
> and I would appreciate any advice. Please help!!
> Thank you!
|||Did you try shrinking the file using dbcc shrinkfile?
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> What about creating two secondary files instead one and using "dbcc
> shrinkfile (file_2, , EMPTYFILE)" so the data from file_2 is migrated to the
> other files in the filegroup and then deleting file_2 using "alter database".
>
> AMB
> "Shishir Viriyala" wrote:
|||I suspect you need to have some free swing space in another file to
accomplish the operation?
"Shishir Viriyala" wrote:

> Hello everyone,
> I have a problem with the data files on one of my databases. I was trying
> to manage the file size for a secondary data file (that had blown up on me
> because there were no growth limits set initially) by trying something on a
> hunch. It was probably a mistake to this, but I wanted to reduce the size of
> this file by transferring only part of the data from this file to the other
> files in the filegroup. Here's an illustration of what I did:
> Original Scenario:
> Used Space Free Space Total Space
> -- -- --
> File 1 18945 MB 0 MB 18945 MB
> File 2 87052 MB 10.8 MB 87062 MB
> I wanted to reduce the size of secondary data file File 2. So, I created
> File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> set growth limits of 30000 MB each for File 1 and File 3, and decided to try
> and empty the data from File 2. The theory behind this being that SQL Server
> would try to empty File 2, but will only be able to transfer up to the growth
> limits set on Files 1 & 2. This way, I will have more evenly displaced usage
> of data space. Well, here is what it now looks like:
> Used Space Free Space Total Space
> -- -- --
> File 1 29854 MB 0 MB 29854 MB
> File 2 46251 MB 40774 MB 87025 MB
> File 3 29652 MB 0 MB 29652 MB
> This is all fine and dandy except the fact that I am unable to shrink File
> 2. No matter what I try, I am unable to get the 40 GB of free space in this
> file back to the Operating System. This is an extremely frustrating problem,
> and I would appreciate any advice. Please help!!
> Thank you!
|||Yep, I have used both Enterpise Manager and Transact SQL (DBCC SHRINKFILE
method) to try and shrink this file. Nothing changes on the file however. I
will try emptying File 2 into another data file and see what happens. Hope
it works. Thanks for the idea!
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Did you try shrinking the file using dbcc shrinkfile?
>
> AMB
> "Alejandro Mesa" wrote:
|||Alejandro,
I tried to create additional data files, and then tried to empty File 2, but
nothing happened. In Enterprise Manager, I got a confirmation message saying
the file was shrunk successfully, but the File remains at the same size.
Then I tried DBCC SHRINKFILE (EMPTYFILE), and again I didnt receive any
errors, but my file's size remains the same. Something is seriously wrong,
and I am not sure how to troubleshoot. Any ideas?
Thanks!
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Yep, I have used both Enterpise Manager and Transact SQL (DBCC SHRINKFILE
> method) to try and shrink this file. Nothing changes on the file however. I
> will try emptying File 2 into another data file and see what happens. Hope
> it works. Thanks for the idea!
> "Alejandro Mesa" wrote:
|||Jeff,
I managed to find just enough space on the disk to create a new data file
that could hold all of my problem data file. However, referring to my
previous post in this thread, the file has stopped responding to any shrink
attempts. Not sure what to do at the moment.....
Shishir
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> I suspect you need to have some free swing space in another file to
> accomplish the operation?
> "Shishir Viriyala" wrote:
sql

Data File size issue

Hello everyone,
I have a problem with the data files on one of my databases. I was trying
to manage the file size for a secondary data file (that had blown up on me
because there were no growth limits set initially) by trying something on a
hunch. It was probably a mistake to this, but I wanted to reduce the size o
f
this file by transferring only part of the data from this file to the other
files in the filegroup. Here's an illustration of what I did:
Original Scenario:
Used Space Free Space Total Space
-- -- --
File 1 18945 MB 0 MB 18945 MB
File 2 87052 MB 10.8 MB 87062 MB
I wanted to reduce the size of secondary data file File 2. So, I created
File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
set growth limits of 30000 MB each for File 1 and File 3, and decided to try
and empty the data from File 2. The theory behind this being that SQL Serve
r
would try to empty File 2, but will only be able to transfer up to the growt
h
limits set on Files 1 & 2. This way, I will have more evenly displaced usag
e
of data space. Well, here is what it now looks like:
Used Space Free Space Total Space
-- -- --
File 1 29854 MB 0 MB 29854 MB
File 2 46251 MB 40774 MB 87025 MB
File 3 29652 MB 0 MB 29652 MB
This is all fine and dandy except the fact that I am unable to shrink File
2. No matter what I try, I am unable to get the 40 GB of free space in this
file back to the Operating System. This is an extremely frustrating problem
,
and I would appreciate any advice. Please help!!
Thank you!What about creating two secondary files instead one and using "dbcc
shrinkfile (file_2, , EMPTYFILE)" so the data from file_2 is migrated to the
other files in the filegroup and then deleting file_2 using "alter database"
.
AMB
"Shishir Viriyala" wrote:

> Hello everyone,
> I have a problem with the data files on one of my databases. I was trying
> to manage the file size for a secondary data file (that had blown up on me
> because there were no growth limits set initially) by trying something on
a
> hunch. It was probably a mistake to this, but I wanted to reduce the size
of
> this file by transferring only part of the data from this file to the othe
r
> files in the filegroup. Here's an illustration of what I did:
> Original Scenario:
> Used Space Free Space Total Space
> -- -- --
> File 1 18945 MB 0 MB 18945 MB
> File 2 87052 MB 10.8 MB 87062 MB
> I wanted to reduce the size of secondary data file File 2. So, I created
> File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> set growth limits of 30000 MB each for File 1 and File 3, and decided to t
ry
> and empty the data from File 2. The theory behind this being that SQL Ser
ver
> would try to empty File 2, but will only be able to transfer up to the gro
wth
> limits set on Files 1 & 2. This way, I will have more evenly displaced us
age
> of data space. Well, here is what it now looks like:
> Used Space Free Space Total Space
> -- -- --
> File 1 29854 MB 0 MB 29854 MB
> File 2 46251 MB 40774 MB 87025 MB
> File 3 29652 MB 0 MB 29652 MB
> This is all fine and dandy except the fact that I am unable to shrink File
> 2. No matter what I try, I am unable to get the 40 GB of free space in th
is
> file back to the Operating System. This is an extremely frustrating probl
em,
> and I would appreciate any advice. Please help!!
> Thank you!|||Did you try shrinking the file using dbcc shrinkfile?
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> What about creating two secondary files instead one and using "dbcc
> shrinkfile (file_2, , EMPTYFILE)" so the data from file_2 is migrated to t
he
> other files in the filegroup and then deleting file_2 using "alter databas
e".
>
> AMB
> "Shishir Viriyala" wrote:
>|||I suspect you need to have some free swing space in another file to
accomplish the operation?
"Shishir Viriyala" wrote:

> Hello everyone,
> I have a problem with the data files on one of my databases. I was trying
> to manage the file size for a secondary data file (that had blown up on me
> because there were no growth limits set initially) by trying something on
a
> hunch. It was probably a mistake to this, but I wanted to reduce the size
of
> this file by transferring only part of the data from this file to the othe
r
> files in the filegroup. Here's an illustration of what I did:
> Original Scenario:
> Used Space Free Space Total Space
> -- -- --
> File 1 18945 MB 0 MB 18945 MB
> File 2 87052 MB 10.8 MB 87062 MB
> I wanted to reduce the size of secondary data file File 2. So, I created
> File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> set growth limits of 30000 MB each for File 1 and File 3, and decided to t
ry
> and empty the data from File 2. The theory behind this being that SQL Ser
ver
> would try to empty File 2, but will only be able to transfer up to the gro
wth
> limits set on Files 1 & 2. This way, I will have more evenly displaced us
age
> of data space. Well, here is what it now looks like:
> Used Space Free Space Total Space
> -- -- --
> File 1 29854 MB 0 MB 29854 MB
> File 2 46251 MB 40774 MB 87025 MB
> File 3 29652 MB 0 MB 29652 MB
> This is all fine and dandy except the fact that I am unable to shrink File
> 2. No matter what I try, I am unable to get the 40 GB of free space in th
is
> file back to the Operating System. This is an extremely frustrating probl
em,
> and I would appreciate any advice. Please help!!
> Thank you!|||Yep, I have used both Enterpise Manager and Transact SQL (DBCC SHRINKFILE
method) to try and shrink this file. Nothing changes on the file however.
I
will try emptying File 2 into another data file and see what happens. Hope
it works. Thanks for the idea!
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Did you try shrinking the file using dbcc shrinkfile?
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro,
I tried to create additional data files, and then tried to empty File 2, but
nothing happened. In Enterprise Manager, I got a confirmation message sayin
g
the file was shrunk successfully, but the File remains at the same size.
Then I tried DBCC SHRINKFILE (EMPTYFILE), and again I didnt receive any
errors, but my file's size remains the same. Something is seriously wrong,
and I am not sure how to troubleshoot. Any ideas?
Thanks!
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Yep, I have used both Enterpise Manager and Transact SQL (DBCC SHRINKFILE
> method) to try and shrink this file. Nothing changes on the file however.
I
> will try emptying File 2 into another data file and see what happens. Hop
e
> it works. Thanks for the idea!
> "Alejandro Mesa" wrote:
>|||Jeff,
I managed to find just enough space on the disk to create a new data file
that could hold all of my problem data file. However, referring to my
previous post in this thread, the file has stopped responding to any shrink
attempts. Not sure what to do at the moment.....
Shishir
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> I suspect you need to have some free swing space in another file to
> accomplish the operation?
> "Shishir Viriyala" wrote:
>

Data File size issue

Hello everyone,
I have a problem with the data files on one of my databases. I was trying
to manage the file size for a secondary data file (that had blown up on me
because there were no growth limits set initially) by trying something on a
hunch. It was probably a mistake to this, but I wanted to reduce the size of
this file by transferring only part of the data from this file to the other
files in the filegroup. Here's an illustration of what I did:
Original Scenario:
Used Space Free Space Total Space
-- -- --
File 1 18945 MB 0 MB 18945 MB
File 2 87052 MB 10.8 MB 87062 MB
I wanted to reduce the size of secondary data file File 2. So, I created
File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
set growth limits of 30000 MB each for File 1 and File 3, and decided to try
and empty the data from File 2. The theory behind this being that SQL Server
would try to empty File 2, but will only be able to transfer up to the growth
limits set on Files 1 & 2. This way, I will have more evenly displaced usage
of data space. Well, here is what it now looks like:
Used Space Free Space Total Space
-- -- --
File 1 29854 MB 0 MB 29854 MB
File 2 46251 MB 40774 MB 87025 MB
File 3 29652 MB 0 MB 29652 MB
This is all fine and dandy except the fact that I am unable to shrink File
2. No matter what I try, I am unable to get the 40 GB of free space in this
file back to the Operating System. This is an extremely frustrating problem,
and I would appreciate any advice. Please help!!
Thank you!What about creating two secondary files instead one and using "dbcc
shrinkfile (file_2, , EMPTYFILE)" so the data from file_2 is migrated to the
other files in the filegroup and then deleting file_2 using "alter database".
AMB
"Shishir Viriyala" wrote:
> Hello everyone,
> I have a problem with the data files on one of my databases. I was trying
> to manage the file size for a secondary data file (that had blown up on me
> because there were no growth limits set initially) by trying something on a
> hunch. It was probably a mistake to this, but I wanted to reduce the size of
> this file by transferring only part of the data from this file to the other
> files in the filegroup. Here's an illustration of what I did:
> Original Scenario:
> Used Space Free Space Total Space
> -- -- --
> File 1 18945 MB 0 MB 18945 MB
> File 2 87052 MB 10.8 MB 87062 MB
> I wanted to reduce the size of secondary data file File 2. So, I created
> File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> set growth limits of 30000 MB each for File 1 and File 3, and decided to try
> and empty the data from File 2. The theory behind this being that SQL Server
> would try to empty File 2, but will only be able to transfer up to the growth
> limits set on Files 1 & 2. This way, I will have more evenly displaced usage
> of data space. Well, here is what it now looks like:
> Used Space Free Space Total Space
> -- -- --
> File 1 29854 MB 0 MB 29854 MB
> File 2 46251 MB 40774 MB 87025 MB
> File 3 29652 MB 0 MB 29652 MB
> This is all fine and dandy except the fact that I am unable to shrink File
> 2. No matter what I try, I am unable to get the 40 GB of free space in this
> file back to the Operating System. This is an extremely frustrating problem,
> and I would appreciate any advice. Please help!!
> Thank you!|||Did you try shrinking the file using dbcc shrinkfile?
AMB
"Alejandro Mesa" wrote:
> What about creating two secondary files instead one and using "dbcc
> shrinkfile (file_2, , EMPTYFILE)" so the data from file_2 is migrated to the
> other files in the filegroup and then deleting file_2 using "alter database".
>
> AMB
> "Shishir Viriyala" wrote:
> > Hello everyone,
> >
> > I have a problem with the data files on one of my databases. I was trying
> > to manage the file size for a secondary data file (that had blown up on me
> > because there were no growth limits set initially) by trying something on a
> > hunch. It was probably a mistake to this, but I wanted to reduce the size of
> > this file by transferring only part of the data from this file to the other
> > files in the filegroup. Here's an illustration of what I did:
> >
> > Original Scenario:
> > Used Space Free Space Total Space
> > -- -- --
> > File 1 18945 MB 0 MB 18945 MB
> > File 2 87052 MB 10.8 MB 87062 MB
> >
> > I wanted to reduce the size of secondary data file File 2. So, I created
> > File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> > set growth limits of 30000 MB each for File 1 and File 3, and decided to try
> > and empty the data from File 2. The theory behind this being that SQL Server
> > would try to empty File 2, but will only be able to transfer up to the growth
> > limits set on Files 1 & 2. This way, I will have more evenly displaced usage
> > of data space. Well, here is what it now looks like:
> >
> > Used Space Free Space Total Space
> > -- -- --
> > File 1 29854 MB 0 MB 29854 MB
> > File 2 46251 MB 40774 MB 87025 MB
> > File 3 29652 MB 0 MB 29652 MB
> >
> > This is all fine and dandy except the fact that I am unable to shrink File
> > 2. No matter what I try, I am unable to get the 40 GB of free space in this
> > file back to the Operating System. This is an extremely frustrating problem,
> > and I would appreciate any advice. Please help!!
> >
> > Thank you!|||I suspect you need to have some free swing space in another file to
accomplish the operation?
"Shishir Viriyala" wrote:
> Hello everyone,
> I have a problem with the data files on one of my databases. I was trying
> to manage the file size for a secondary data file (that had blown up on me
> because there were no growth limits set initially) by trying something on a
> hunch. It was probably a mistake to this, but I wanted to reduce the size of
> this file by transferring only part of the data from this file to the other
> files in the filegroup. Here's an illustration of what I did:
> Original Scenario:
> Used Space Free Space Total Space
> -- -- --
> File 1 18945 MB 0 MB 18945 MB
> File 2 87052 MB 10.8 MB 87062 MB
> I wanted to reduce the size of secondary data file File 2. So, I created
> File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> set growth limits of 30000 MB each for File 1 and File 3, and decided to try
> and empty the data from File 2. The theory behind this being that SQL Server
> would try to empty File 2, but will only be able to transfer up to the growth
> limits set on Files 1 & 2. This way, I will have more evenly displaced usage
> of data space. Well, here is what it now looks like:
> Used Space Free Space Total Space
> -- -- --
> File 1 29854 MB 0 MB 29854 MB
> File 2 46251 MB 40774 MB 87025 MB
> File 3 29652 MB 0 MB 29652 MB
> This is all fine and dandy except the fact that I am unable to shrink File
> 2. No matter what I try, I am unable to get the 40 GB of free space in this
> file back to the Operating System. This is an extremely frustrating problem,
> and I would appreciate any advice. Please help!!
> Thank you!|||Yep, I have used both Enterpise Manager and Transact SQL (DBCC SHRINKFILE
method) to try and shrink this file. Nothing changes on the file however. I
will try emptying File 2 into another data file and see what happens. Hope
it works. Thanks for the idea!
"Alejandro Mesa" wrote:
> Did you try shrinking the file using dbcc shrinkfile?
>
> AMB
> "Alejandro Mesa" wrote:
> > What about creating two secondary files instead one and using "dbcc
> > shrinkfile (file_2, , EMPTYFILE)" so the data from file_2 is migrated to the
> > other files in the filegroup and then deleting file_2 using "alter database".
> >
> >
> > AMB
> >
> > "Shishir Viriyala" wrote:
> >
> > > Hello everyone,
> > >
> > > I have a problem with the data files on one of my databases. I was trying
> > > to manage the file size for a secondary data file (that had blown up on me
> > > because there were no growth limits set initially) by trying something on a
> > > hunch. It was probably a mistake to this, but I wanted to reduce the size of
> > > this file by transferring only part of the data from this file to the other
> > > files in the filegroup. Here's an illustration of what I did:
> > >
> > > Original Scenario:
> > > Used Space Free Space Total Space
> > > -- -- --
> > > File 1 18945 MB 0 MB 18945 MB
> > > File 2 87052 MB 10.8 MB 87062 MB
> > >
> > > I wanted to reduce the size of secondary data file File 2. So, I created
> > > File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> > > set growth limits of 30000 MB each for File 1 and File 3, and decided to try
> > > and empty the data from File 2. The theory behind this being that SQL Server
> > > would try to empty File 2, but will only be able to transfer up to the growth
> > > limits set on Files 1 & 2. This way, I will have more evenly displaced usage
> > > of data space. Well, here is what it now looks like:
> > >
> > > Used Space Free Space Total Space
> > > -- -- --
> > > File 1 29854 MB 0 MB 29854 MB
> > > File 2 46251 MB 40774 MB 87025 MB
> > > File 3 29652 MB 0 MB 29652 MB
> > >
> > > This is all fine and dandy except the fact that I am unable to shrink File
> > > 2. No matter what I try, I am unable to get the 40 GB of free space in this
> > > file back to the Operating System. This is an extremely frustrating problem,
> > > and I would appreciate any advice. Please help!!
> > >
> > > Thank you!|||Alejandro,
I tried to create additional data files, and then tried to empty File 2, but
nothing happened. In Enterprise Manager, I got a confirmation message saying
the file was shrunk successfully, but the File remains at the same size.
Then I tried DBCC SHRINKFILE (EMPTYFILE), and again I didnt receive any
errors, but my file's size remains the same. Something is seriously wrong,
and I am not sure how to troubleshoot. Any ideas?
Thanks!
"Shishir Viriyala" wrote:
> Yep, I have used both Enterpise Manager and Transact SQL (DBCC SHRINKFILE
> method) to try and shrink this file. Nothing changes on the file however. I
> will try emptying File 2 into another data file and see what happens. Hope
> it works. Thanks for the idea!
> "Alejandro Mesa" wrote:
> > Did you try shrinking the file using dbcc shrinkfile?
> >
> >
> > AMB
> >
> > "Alejandro Mesa" wrote:
> >
> > > What about creating two secondary files instead one and using "dbcc
> > > shrinkfile (file_2, , EMPTYFILE)" so the data from file_2 is migrated to the
> > > other files in the filegroup and then deleting file_2 using "alter database".
> > >
> > >
> > > AMB
> > >
> > > "Shishir Viriyala" wrote:
> > >
> > > > Hello everyone,
> > > >
> > > > I have a problem with the data files on one of my databases. I was trying
> > > > to manage the file size for a secondary data file (that had blown up on me
> > > > because there were no growth limits set initially) by trying something on a
> > > > hunch. It was probably a mistake to this, but I wanted to reduce the size of
> > > > this file by transferring only part of the data from this file to the other
> > > > files in the filegroup. Here's an illustration of what I did:
> > > >
> > > > Original Scenario:
> > > > Used Space Free Space Total Space
> > > > -- -- --
> > > > File 1 18945 MB 0 MB 18945 MB
> > > > File 2 87052 MB 10.8 MB 87062 MB
> > > >
> > > > I wanted to reduce the size of secondary data file File 2. So, I created
> > > > File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> > > > set growth limits of 30000 MB each for File 1 and File 3, and decided to try
> > > > and empty the data from File 2. The theory behind this being that SQL Server
> > > > would try to empty File 2, but will only be able to transfer up to the growth
> > > > limits set on Files 1 & 2. This way, I will have more evenly displaced usage
> > > > of data space. Well, here is what it now looks like:
> > > >
> > > > Used Space Free Space Total Space
> > > > -- -- --
> > > > File 1 29854 MB 0 MB 29854 MB
> > > > File 2 46251 MB 40774 MB 87025 MB
> > > > File 3 29652 MB 0 MB 29652 MB
> > > >
> > > > This is all fine and dandy except the fact that I am unable to shrink File
> > > > 2. No matter what I try, I am unable to get the 40 GB of free space in this
> > > > file back to the Operating System. This is an extremely frustrating problem,
> > > > and I would appreciate any advice. Please help!!
> > > >
> > > > Thank you!|||Jeff,
I managed to find just enough space on the disk to create a new data file
that could hold all of my problem data file. However, referring to my
previous post in this thread, the file has stopped responding to any shrink
attempts. Not sure what to do at the moment.....
Shishir
"Jeffrey K. Ericson" wrote:
> I suspect you need to have some free swing space in another file to
> accomplish the operation?
> "Shishir Viriyala" wrote:
> > Hello everyone,
> >
> > I have a problem with the data files on one of my databases. I was trying
> > to manage the file size for a secondary data file (that had blown up on me
> > because there were no growth limits set initially) by trying something on a
> > hunch. It was probably a mistake to this, but I wanted to reduce the size of
> > this file by transferring only part of the data from this file to the other
> > files in the filegroup. Here's an illustration of what I did:
> >
> > Original Scenario:
> > Used Space Free Space Total Space
> > -- -- --
> > File 1 18945 MB 0 MB 18945 MB
> > File 2 87052 MB 10.8 MB 87062 MB
> >
> > I wanted to reduce the size of secondary data file File 2. So, I created
> > File 3 on a third Server hard drive (File 1 & 2 are on 2 separate drives),
> > set growth limits of 30000 MB each for File 1 and File 3, and decided to try
> > and empty the data from File 2. The theory behind this being that SQL Server
> > would try to empty File 2, but will only be able to transfer up to the growth
> > limits set on Files 1 & 2. This way, I will have more evenly displaced usage
> > of data space. Well, here is what it now looks like:
> >
> > Used Space Free Space Total Space
> > -- -- --
> > File 1 29854 MB 0 MB 29854 MB
> > File 2 46251 MB 40774 MB 87025 MB
> > File 3 29652 MB 0 MB 29652 MB
> >
> > This is all fine and dandy except the fact that I am unable to shrink File
> > 2. No matter what I try, I am unable to get the 40 GB of free space in this
> > file back to the Operating System. This is an extremely frustrating problem,
> > and I would appreciate any advice. Please help!!
> >
> > Thank you!

Data Driven Subscriptions - Recipient List Management

Hi,
We have a need to be able to manage recipient lists for data driven
subscriptions in a user friendly way. (i.e. we need users to be able to edit
these recipient lists, and they are not the type of users that we want to
allow to open enterprise manager !!)
The general plan is to develop some ASP.Net type app that will edit the
table contents.
Before developing something new and possibly reinventing the wheel, I would
just like to know if the Reporting Services team are considering building
some type of recipient list managemtent into future releases of RS ?
Thanks !Not at this time. You will need to write your own application to take care
of this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gavin R" <GavinR@.discussions.microsoft.com> wrote in message
news:691AABAD-DE25-4B3E-9B68-9F3A9814AFD2@.microsoft.com...
> Hi,
> We have a need to be able to manage recipient lists for data driven
> subscriptions in a user friendly way. (i.e. we need users to be able to
> edit
> these recipient lists, and they are not the type of users that we want to
> allow to open enterprise manager !!)
> The general plan is to develop some ASP.Net type app that will edit the
> table contents.
> Before developing something new and possibly reinventing the wheel, I
> would
> just like to know if the Reporting Services team are considering building
> some type of recipient list managemtent into future releases of RS ?
> Thanks !

Customizing Report

Hi All,

Really need advise and help out there!

I'm using CR11 with SQL d/b platform. I did manage to publish the report via web. But have some issues here:

1) Can I enable user to choose which paper size to print? How?

2) Is it possible to allow user to specify the sorting order in the report? How?

Please! Appreciate any help..Thanks

Regards,
Mee1) No idea!
2) Have a parameter; base a formula on the parameter; sort on the formula.|||Hi JaganEllis

Thanks. So you mean that do I need to customize from the backend or just simply create formula to sort the records. Hmm, let me do some try and error.

Well, I really need helps to resolve the problem.

Thanks for your concern