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 File Size Increase

Hi,
Currently we are having 20 databases and mergereplicating the databases
also.
But only for 2 databases the DATAFile size got increased.
Please give what may be the reason for that.
Please give me the Solution as early as Possible
Thanks
SouraHi
Do you have autogrow feature enabled?
Look at ALTER DATABASE command to increase a size if the file
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the
databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>|||Perhaps there was free space for the other database's database data files to accommodate the
modifications?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>|||Hi,
Also check if you have restricted file growth to the other databases in the
databases option tab.
CU
Andreas
"Uri Dimant" wrote:
> Hi
> Do you have autogrow feature enabled?
> Look at ALTER DATABASE command to increase a size if the file
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> > Hi,
> > Currently we are having 20 databases and mergereplicating the
> databases
> > also.
> >
> > But only for 2 databases the DATAFile size got increased.
> >
> > Please give what may be the reason for that.
> >
> > Please give me the Solution as early as Possible
> >
> > Thanks
> > Soura
> >
> >
>
>

Data File Size Increase

Hi,
Currently we are having 20 databases and mergereplicating the databases
also.
But only for 2 databases the DATAFile size got increased.
Please give what may be the reason for that.
Please give me the Solution as early as Possible
Thanks
Soura
Hi
Do you have autogrow feature enabled?
Look at ALTER DATABASE command to increase a size if the file
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the
databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>
|||Perhaps there was free space for the other database's database data files to accommodate the
modifications?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>
|||Hi,
Also check if you have restricted file growth to the other databases in the
databases option tab.
CU
Andreas
"Uri Dimant" wrote:

> Hi
> Do you have autogrow feature enabled?
> Look at ALTER DATABASE command to increase a size if the file
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> databases
>
>

Data File Size Increase

Hi,
Currently we are having 20 databases and mergereplicating the databases
also.
But only for 2 databases the DATAFile size got increased.
Please give what may be the reason for that.
Please give me the Solution as early as Possible
Thanks
SouraHi
Do you have autogrow feature enabled?
Look at ALTER DATABASE command to increase a size if the file
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the
databases
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>|||Perhaps there was free space for the other database's database data files to
accommodate the
modifications?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> Hi,
> Currently we are having 20 databases and mergereplicating the database
s
> also.
> But only for 2 databases the DATAFile size got increased.
> Please give what may be the reason for that.
> Please give me the Solution as early as Possible
> Thanks
> Soura
>|||Hi,
Also check if you have restricted file growth to the other databases in the
databases option tab.
CU
Andreas
"Uri Dimant" wrote:

> Hi
> Do you have autogrow feature enabled?
> Look at ALTER DATABASE command to increase a size if the file
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:371E1A9F-F22C-497E-A548-CA8EBE68D833@.microsoft.com...
> databases
>
>sql

Data File Management on SQL Server 2000

Hi Guys,
I have got some serious data management issues with my company's
implementation of about 70 some odd databases on SQL Server 2000. The server
where the databases are physically located has been partitioned into 5
drives, 4 of which are used to store our data files. I am running into some
problems managing the disk space because the used space on each of these
drives is growing disproportionately.
The problem arises from the fact that when these databases were created, the
main data files were allowed to have unrestricted growth, so what has
happened now is that one of the 4 drives is chock full of data while the
others have a lot of free space. My question to you all is, would there be a
way to take say about half the data from the main data file in each of my
databases and redistribute it among the secondary data files? This way, I
can have much more proportionate growth amongst my server drives.
Any ideas would be greatly apprecited. Thanks!I had a simliar problem. A 260GB database had one data file which was
impossible to work with. What I did was to stop any growth on the main file
and created secondary files of fixed size(20GB). As data was written to the
DB, SQL managed the process of balancing where data was located. If you want
a particualr file to grow, trun itsgrowth on, SQL will be forced to write
data to it. Just ensure you have enough free space and shrink the main file
when you get a chance, you will see the data redistributed over all the files
and start to equal out.
"Shishir Viriyala" wrote:
> Hi Guys,
> I have got some serious data management issues with my company's
> implementation of about 70 some odd databases on SQL Server 2000. The server
> where the databases are physically located has been partitioned into 5
> drives, 4 of which are used to store our data files. I am running into some
> problems managing the disk space because the used space on each of these
> drives is growing disproportionately.
> The problem arises from the fact that when these databases were created, the
> main data files were allowed to have unrestricted growth, so what has
> happened now is that one of the 4 drives is chock full of data while the
> others have a lot of free space. My question to you all is, would there be a
> way to take say about half the data from the main data file in each of my
> databases and redistribute it among the secondary data files? This way, I
> can have much more proportionate growth amongst my server drives.
> Any ideas would be greatly apprecited. Thanks!
>|||Thanks for the prompt reply! That is a great idea and I definitely intend to
use it, but what about some of my main data files right now that are already
so very big? How do I get some of the used space reduced so that I can cap
off each main data file at around 40 GB?
Thanks again!
"Jeffrey K. Ericson" wrote:
> I had a simliar problem. A 260GB database had one data file which was
> impossible to work with. What I did was to stop any growth on the main file
> and created secondary files of fixed size(20GB). As data was written to the
> DB, SQL managed the process of balancing where data was located. If you want
> a particualr file to grow, trun itsgrowth on, SQL will be forced to write
> data to it. Just ensure you have enough free space and shrink the main file
> when you get a chance, you will see the data redistributed over all the files
> and start to equal out.
> "Shishir Viriyala" wrote:
> > Hi Guys,
> >
> > I have got some serious data management issues with my company's
> > implementation of about 70 some odd databases on SQL Server 2000. The server
> > where the databases are physically located has been partitioned into 5
> > drives, 4 of which are used to store our data files. I am running into some
> > problems managing the disk space because the used space on each of these
> > drives is growing disproportionately.
> >
> > The problem arises from the fact that when these databases were created, the
> > main data files were allowed to have unrestricted growth, so what has
> > happened now is that one of the 4 drives is chock full of data while the
> > others have a lot of free space. My question to you all is, would there be a
> > way to take say about half the data from the main data file in each of my
> > databases and redistribute it among the secondary data files? This way, I
> > can have much more proportionate growth amongst my server drives.
> >
> > Any ideas would be greatly apprecited. Thanks!
> >|||I found that this was a process that took several weeks to drop the main file
down. Beyond that, you'd have to relocate specific tables to specific files.
This would include dropping and transferring data. I wasn't willing to do
that but this was also a decision support system where all the data came at
night from upstream systems.
"Shishir Viriyala" wrote:
> Thanks for the prompt reply! That is a great idea and I definitely intend to
> use it, but what about some of my main data files right now that are already
> so very big? How do I get some of the used space reduced so that I can cap
> off each main data file at around 40 GB?
> Thanks again!
> "Jeffrey K. Ericson" wrote:
> > I had a simliar problem. A 260GB database had one data file which was
> > impossible to work with. What I did was to stop any growth on the main file
> > and created secondary files of fixed size(20GB). As data was written to the
> > DB, SQL managed the process of balancing where data was located. If you want
> > a particualr file to grow, trun itsgrowth on, SQL will be forced to write
> > data to it. Just ensure you have enough free space and shrink the main file
> > when you get a chance, you will see the data redistributed over all the files
> > and start to equal out.
> >
> > "Shishir Viriyala" wrote:
> >
> > > Hi Guys,
> > >
> > > I have got some serious data management issues with my company's
> > > implementation of about 70 some odd databases on SQL Server 2000. The server
> > > where the databases are physically located has been partitioned into 5
> > > drives, 4 of which are used to store our data files. I am running into some
> > > problems managing the disk space because the used space on each of these
> > > drives is growing disproportionately.
> > >
> > > The problem arises from the fact that when these databases were created, the
> > > main data files were allowed to have unrestricted growth, so what has
> > > happened now is that one of the 4 drives is chock full of data while the
> > > others have a lot of free space. My question to you all is, would there be a
> > > way to take say about half the data from the main data file in each of my
> > > databases and redistribute it among the secondary data files? This way, I
> > > can have much more proportionate growth amongst my server drives.
> > >
> > > Any ideas would be greatly apprecited. Thanks!
> > >|||Thank you. I think I will have to go the route of manually dropping and
transferring tables to manage used space. Appreciate the help!
"Jeffrey K. Ericson" wrote:
> I found that this was a process that took several weeks to drop the main file
> down. Beyond that, you'd have to relocate specific tables to specific files.
> This would include dropping and transferring data. I wasn't willing to do
> that but this was also a decision support system where all the data came at
> night from upstream systems.
> "Shishir Viriyala" wrote:
> > Thanks for the prompt reply! That is a great idea and I definitely intend to
> > use it, but what about some of my main data files right now that are already
> > so very big? How do I get some of the used space reduced so that I can cap
> > off each main data file at around 40 GB?
> >
> > Thanks again!
> >
> > "Jeffrey K. Ericson" wrote:
> >
> > > I had a simliar problem. A 260GB database had one data file which was
> > > impossible to work with. What I did was to stop any growth on the main file
> > > and created secondary files of fixed size(20GB). As data was written to the
> > > DB, SQL managed the process of balancing where data was located. If you want
> > > a particualr file to grow, trun itsgrowth on, SQL will be forced to write
> > > data to it. Just ensure you have enough free space and shrink the main file
> > > when you get a chance, you will see the data redistributed over all the files
> > > and start to equal out.
> > >
> > > "Shishir Viriyala" wrote:
> > >
> > > > Hi Guys,
> > > >
> > > > I have got some serious data management issues with my company's
> > > > implementation of about 70 some odd databases on SQL Server 2000. The server
> > > > where the databases are physically located has been partitioned into 5
> > > > drives, 4 of which are used to store our data files. I am running into some
> > > > problems managing the disk space because the used space on each of these
> > > > drives is growing disproportionately.
> > > >
> > > > The problem arises from the fact that when these databases were created, the
> > > > main data files were allowed to have unrestricted growth, so what has
> > > > happened now is that one of the 4 drives is chock full of data while the
> > > > others have a lot of free space. My question to you all is, would there be a
> > > > way to take say about half the data from the main data file in each of my
> > > > databases and redistribute it among the secondary data files? This way, I
> > > > can have much more proportionate growth amongst my server drives.
> > > >
> > > > Any ideas would be greatly apprecited. Thanks!
> > > >|||Actually, you could accomplish exactly what Jeff was discribing, turn
AUTOGROW off of the main file and shrink it down to its minimum size, then,
all you have to do is find some of your large tables and rebuild the cluster
index.
Because rebuilding the index must process the pages in sorted order, the
system must copy the information to new pages. If the main file is capped,
then the data must go to the secondary files.
If you rebuild the Clustered index, you will be relocating the data pages
themselves. Because a rebuild of the Cluster Index forces a rebuild of the
non-clustered indexes, the Index Pages for that table will move as well.
Just make sure you shrink the main file down again before you start
rebuilding indexes on the next table.
It's a little slow going, but faster than letting it happen just through
normal usage.
Sincerely,
Anthony Thomas
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in
message news:0B400C78-8FD5-416B-AE77-6CFD75CAFEB9@.microsoft.com...
Thank you. I think I will have to go the route of manually dropping and
transferring tables to manage used space. Appreciate the help!
"Jeffrey K. Ericson" wrote:
> I found that this was a process that took several weeks to drop the main
file
> down. Beyond that, you'd have to relocate specific tables to specific
files.
> This would include dropping and transferring data. I wasn't willing to
do
> that but this was also a decision support system where all the data came
at
> night from upstream systems.
> "Shishir Viriyala" wrote:
> > Thanks for the prompt reply! That is a great idea and I definitely
intend to
> > use it, but what about some of my main data files right now that are
already
> > so very big? How do I get some of the used space reduced so that I can
cap
> > off each main data file at around 40 GB?
> >
> > Thanks again!
> >
> > "Jeffrey K. Ericson" wrote:
> >
> > > I had a simliar problem. A 260GB database had one data file which was
> > > impossible to work with. What I did was to stop any growth on the
main file
> > > and created secondary files of fixed size(20GB). As data was written
to the
> > > DB, SQL managed the process of balancing where data was located. If
you want
> > > a particualr file to grow, trun itsgrowth on, SQL will be forced to
write
> > > data to it. Just ensure you have enough free space and shrink the
main file
> > > when you get a chance, you will see the data redistributed over all
the files
> > > and start to equal out.
> > >
> > > "Shishir Viriyala" wrote:
> > >
> > > > Hi Guys,
> > > >
> > > > I have got some serious data management issues with my company's
> > > > implementation of about 70 some odd databases on SQL Server 2000.
The server
> > > > where the databases are physically located has been partitioned into
5
> > > > drives, 4 of which are used to store our data files. I am running
into some
> > > > problems managing the disk space because the used space on each of
these
> > > > drives is growing disproportionately.
> > > >
> > > > The problem arises from the fact that when these databases were
created, the
> > > > main data files were allowed to have unrestricted growth, so what
has
> > > > happened now is that one of the 4 drives is chock full of data while
the
> > > > others have a lot of free space. My question to you all is, would
there be a
> > > > way to take say about half the data from the main data file in each
of my
> > > > databases and redistribute it among the secondary data files? This
way, I
> > > > can have much more proportionate growth amongst my server drives.
> > > >
> > > > Any ideas would be greatly apprecited. Thanks!
> > > >

Data File Management on SQL Server 2000

Hi Guys,
I have got some serious data management issues with my company's
implementation of about 70 some odd databases on SQL Server 2000. The server
where the databases are physically located has been partitioned into 5
drives, 4 of which are used to store our data files. I am running into some
problems managing the disk space because the used space on each of these
drives is growing disproportionately.
The problem arises from the fact that when these databases were created, the
main data files were allowed to have unrestricted growth, so what has
happened now is that one of the 4 drives is chock full of data while the
others have a lot of free space. My question to you all is, would there be a
way to take say about half the data from the main data file in each of my
databases and redistribute it among the secondary data files? This way, I
can have much more proportionate growth amongst my server drives.
Any ideas would be greatly apprecited. Thanks!
I had a simliar problem. A 260GB database had one data file which was
impossible to work with. What I did was to stop any growth on the main file
and created secondary files of fixed size(20GB). As data was written to the
DB, SQL managed the process of balancing where data was located. If you want
a particualr file to grow, trun itsgrowth on, SQL will be forced to write
data to it. Just ensure you have enough free space and shrink the main file
when you get a chance, you will see the data redistributed over all the files
and start to equal out.
"Shishir Viriyala" wrote:

> Hi Guys,
> I have got some serious data management issues with my company's
> implementation of about 70 some odd databases on SQL Server 2000. The server
> where the databases are physically located has been partitioned into 5
> drives, 4 of which are used to store our data files. I am running into some
> problems managing the disk space because the used space on each of these
> drives is growing disproportionately.
> The problem arises from the fact that when these databases were created, the
> main data files were allowed to have unrestricted growth, so what has
> happened now is that one of the 4 drives is chock full of data while the
> others have a lot of free space. My question to you all is, would there be a
> way to take say about half the data from the main data file in each of my
> databases and redistribute it among the secondary data files? This way, I
> can have much more proportionate growth amongst my server drives.
> Any ideas would be greatly apprecited. Thanks!
>
|||Thanks for the prompt reply! That is a great idea and I definitely intend to
use it, but what about some of my main data files right now that are already
so very big? How do I get some of the used space reduced so that I can cap
off each main data file at around 40 GB?
Thanks again!
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> I had a simliar problem. A 260GB database had one data file which was
> impossible to work with. What I did was to stop any growth on the main file
> and created secondary files of fixed size(20GB). As data was written to the
> DB, SQL managed the process of balancing where data was located. If you want
> a particualr file to grow, trun itsgrowth on, SQL will be forced to write
> data to it. Just ensure you have enough free space and shrink the main file
> when you get a chance, you will see the data redistributed over all the files
> and start to equal out.
> "Shishir Viriyala" wrote:
|||I found that this was a process that took several weeks to drop the main file
down. Beyond that, you'd have to relocate specific tables to specific files.
This would include dropping and transferring data. I wasn't willing to do
that but this was also a decision support system where all the data came at
night from upstream systems.
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Thanks for the prompt reply! That is a great idea and I definitely intend to
> use it, but what about some of my main data files right now that are already
> so very big? How do I get some of the used space reduced so that I can cap
> off each main data file at around 40 GB?
> Thanks again!
> "Jeffrey K. Ericson" wrote:
|||Thank you. I think I will have to go the route of manually dropping and
transferring tables to manage used space. Appreciate the help!
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> I found that this was a process that took several weeks to drop the main file
> down. Beyond that, you'd have to relocate specific tables to specific files.
> This would include dropping and transferring data. I wasn't willing to do
> that but this was also a decision support system where all the data came at
> night from upstream systems.
> "Shishir Viriyala" wrote:
|||Actually, you could accomplish exactly what Jeff was discribing, turn
AUTOGROW off of the main file and shrink it down to its minimum size, then,
all you have to do is find some of your large tables and rebuild the cluster
index.
Because rebuilding the index must process the pages in sorted order, the
system must copy the information to new pages. If the main file is capped,
then the data must go to the secondary files.
If you rebuild the Clustered index, you will be relocating the data pages
themselves. Because a rebuild of the Cluster Index forces a rebuild of the
non-clustered indexes, the Index Pages for that table will move as well.
Just make sure you shrink the main file down again before you start
rebuilding indexes on the next table.
It's a little slow going, but faster than letting it happen just through
normal usage.
Sincerely,
Anthony Thomas

"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in
message news:0B400C78-8FD5-416B-AE77-6CFD75CAFEB9@.microsoft.com...
Thank you. I think I will have to go the route of manually dropping and
transferring tables to manage used space. Appreciate the help!
"Jeffrey K. Ericson" wrote:

> I found that this was a process that took several weeks to drop the main
file
> down. Beyond that, you'd have to relocate specific tables to specific
files.
> This would include dropping and transferring data. I wasn't willing to
do
> that but this was also a decision support system where all the data came
at[vbcol=seagreen]
> night from upstream systems.
> "Shishir Viriyala" wrote:
intend to[vbcol=seagreen]
already[vbcol=seagreen]
cap[vbcol=seagreen]
[vbcol=seagreen]
main file[vbcol=seagreen]
to the[vbcol=seagreen]
you want[vbcol=seagreen]
write[vbcol=seagreen]
main file[vbcol=seagreen]
the files[vbcol=seagreen]
The server[vbcol=seagreen]
5[vbcol=seagreen]
into some[vbcol=seagreen]
these[vbcol=seagreen]
created, the[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
there be a[vbcol=seagreen]
of my[vbcol=seagreen]
way, I[vbcol=seagreen]
sql

Data File Management on SQL Server 2000

Hi Guys,
I have got some serious data management issues with my company's
implementation of about 70 some odd databases on SQL Server 2000. The serve
r
where the databases are physically located has been partitioned into 5
drives, 4 of which are used to store our data files. I am running into some
problems managing the disk space because the used space on each of these
drives is growing disproportionately.
The problem arises from the fact that when these databases were created, the
main data files were allowed to have unrestricted growth, so what has
happened now is that one of the 4 drives is chock full of data while the
others have a lot of free space. My question to you all is, would there be
a
way to take say about half the data from the main data file in each of my
databases and redistribute it among the secondary data files? This way, I
can have much more proportionate growth amongst my server drives.
Any ideas would be greatly apprecited. Thanks!I had a simliar problem. A 260GB database had one data file which was
impossible to work with. What I did was to stop any growth on the main file
and created secondary files of fixed size(20GB). As data was written to the
DB, SQL managed the process of balancing where data was located. If you wan
t
a particualr file to grow, trun itsgrowth on, SQL will be forced to write
data to it. Just ensure you have enough free space and shrink the main file
when you get a chance, you will see the data redistributed over all the file
s
and start to equal out.
"Shishir Viriyala" wrote:

> Hi Guys,
> I have got some serious data management issues with my company's
> implementation of about 70 some odd databases on SQL Server 2000. The ser
ver
> where the databases are physically located has been partitioned into 5
> drives, 4 of which are used to store our data files. I am running into so
me
> problems managing the disk space because the used space on each of these
> drives is growing disproportionately.
> The problem arises from the fact that when these databases were created, t
he
> main data files were allowed to have unrestricted growth, so what has
> happened now is that one of the 4 drives is chock full of data while the
> others have a lot of free space. My question to you all is, would there b
e a
> way to take say about half the data from the main data file in each of my
> databases and redistribute it among the secondary data files? This way, I
> can have much more proportionate growth amongst my server drives.
> Any ideas would be greatly apprecited. Thanks!
>|||Thanks for the prompt reply! That is a great idea and I definitely intend t
o
use it, but what about some of my main data files right now that are already
so very big? How do I get some of the used space reduced so that I can cap
off each main data file at around 40 GB?
Thanks again!
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> I had a simliar problem. A 260GB database had one data file which was
> impossible to work with. What I did was to stop any growth on the main fi
le
> and created secondary files of fixed size(20GB). As data was written to t
he
> DB, SQL managed the process of balancing where data was located. If you w
ant
> a particualr file to grow, trun itsgrowth on, SQL will be forced to write
> data to it. Just ensure you have enough free space and shrink the main fi
le
> when you get a chance, you will see the data redistributed over all the fi
les
> and start to equal out.
> "Shishir Viriyala" wrote:
>|||I found that this was a process that took several weeks to drop the main fil
e
down. Beyond that, you'd have to relocate specific tables to specific files
.
This would include dropping and transferring data. I wasn't willing to do
that but this was also a decision support system where all the data came at
night from upstream systems.
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Thanks for the prompt reply! That is a great idea and I definitely intend
to
> use it, but what about some of my main data files right now that are alrea
dy
> so very big? How do I get some of the used space reduced so that I can ca
p
> off each main data file at around 40 GB?
> Thanks again!
> "Jeffrey K. Ericson" wrote:
>|||Thank you. I think I will have to go the route of manually dropping and
transferring tables to manage used space. Appreciate the help!
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> I found that this was a process that took several weeks to drop the main f
ile
> down. Beyond that, you'd have to relocate specific tables to specific fil
es.
> This would include dropping and transferring data. I wasn't willing to d
o
> that but this was also a decision support system where all the data came a
t
> night from upstream systems.
> "Shishir Viriyala" wrote:
>|||Actually, you could accomplish exactly what Jeff was discribing, turn
AUTOGROW off of the main file and shrink it down to its minimum size, then,
all you have to do is find some of your large tables and rebuild the cluster
index.
Because rebuilding the index must process the pages in sorted order, the
system must copy the information to new pages. If the main file is capped,
then the data must go to the secondary files.
If you rebuild the Clustered index, you will be relocating the data pages
themselves. Because a rebuild of the Cluster Index forces a rebuild of the
non-clustered indexes, the Index Pages for that table will move as well.
Just make sure you shrink the main file down again before you start
rebuilding indexes on the next table.
It's a little slow going, but faster than letting it happen just through
normal usage.
Sincerely,
Anthony Thomas
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in
message news:0B400C78-8FD5-416B-AE77-6CFD75CAFEB9@.microsoft.com...
Thank you. I think I will have to go the route of manually dropping and
transferring tables to manage used space. Appreciate the help!
"Jeffrey K. Ericson" wrote:

> I found that this was a process that took several weeks to drop the main
file
> down. Beyond that, you'd have to relocate specific tables to specific
files.
> This would include dropping and transferring data. I wasn't willing to
do
> that but this was also a decision support system where all the data came
at[vbcol=seagreen]
> night from upstream systems.
> "Shishir Viriyala" wrote:
>
intend to[vbcol=seagreen]
already[vbcol=seagreen]
cap[vbcol=seagreen]
[vbcol=seagreen]
main file[vbcol=seagreen]
to the[vbcol=seagreen]
you want[vbcol=seagreen]
write[vbcol=seagreen]
main file[vbcol=seagreen]
the files[vbcol=seagreen]
The server[vbcol=seagreen]
5[vbcol=seagreen]
into some[vbcol=seagreen]
these[vbcol=seagreen]
created, the[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
there be a[vbcol=seagreen]
of my[vbcol=seagreen]
way, I[vbcol=seagreen]

data extraction/mapping between two databases

Hi ,
just wondering, whats the easiest way to map about 10000 rows from database
a (old) to database b.
the complexity is that the structure is completely different, database b is
more normalized. has relationships maintained..where as database a has
everything in one table ..So I have to map one column (choose distinct rows)
and move to datbase b.new table...
I did try DTS for couple of them..but seems tedious and never ending..
Whats the easiest way to do this? any OR tools ' anything ...
Thanks,I'd just understand the data, and then populate tables manually, using
INSERT statements, to populate the primary key and foreign key tables.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mani" <mani@.spamthis@.evikasystems.com> wrote in message
news:eACFDzamEHA.1904@.TK2MSFTNGP09.phx.gbl...
Hi ,
just wondering, whats the easiest way to map about 10000 rows from database
a (old) to database b.
the complexity is that the structure is completely different, database b is
more normalized. has relationships maintained..where as database a has
everything in one table ..So I have to map one column (choose distinct rows)
and move to datbase b.new table...
I did try DTS for couple of them..but seems tedious and never ending..
Whats the easiest way to do this? any OR tools ' anything ...
Thanks,

data extraction/mapping between two databases

Hi ,
just wondering, whats the easiest way to map about 10000 rows from database
a (old) to database b.
the complexity is that the structure is completely different, database b is
more normalized. has relationships maintained..where as database a has
everything in one table ..So I have to map one column (choose distinct rows)
and move to datbase b.new table...
I did try DTS for couple of them..but seems tedious and never ending..
Whats the easiest way to do this? any OR tools ? anything ...
Thanks,
I'd just understand the data, and then populate tables manually, using
INSERT statements, to populate the primary key and foreign key tables.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mani" <mani@.spamthis@.evikasystems.com> wrote in message
news:eACFDzamEHA.1904@.TK2MSFTNGP09.phx.gbl...
Hi ,
just wondering, whats the easiest way to map about 10000 rows from database
a (old) to database b.
the complexity is that the structure is completely different, database b is
more normalized. has relationships maintained..where as database a has
everything in one table ..So I have to map one column (choose distinct rows)
and move to datbase b.new table...
I did try DTS for couple of them..but seems tedious and never ending..
Whats the easiest way to do this? any OR tools ? anything ...
Thanks,

Data Dictionary project

We have several databases on SQL 2005 which were built seperately. Ive
been asked to come up with a plan for making sure that data elements are
consistent across all of them, and conform to an outside standard
definition which I have a document for. Ive also been asked to make it
easy for any changes in the external definition to be quickly
transferred to all databases. e.g. If the field for Surname changes to
Varchar(55) we should eb able to change it on all databases without
hunting around for places it occurs.
One idea Ive had is to create our own internal set of User Defined Data
Types matching the external dictionary. But ive then got to somehow map
this to all our databases and change them.
Has anyone out there been involved in anything similar or have any ideas?
tia, Matt"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
RedGate software's SQLCompare application does database schema comparisons
and helps keep schemas in sync. Another option is to script out all your
changes ahead of time, apply strict QA and source control policies to them,
and make sure you apply any change scripts to all databases as necessary
(probably a good idea if you're not doing it already anyway). After all, in
a properly normalized database how many different places does Surname
actually occur? (My guess would be once, maybe twice if you have some sort
of pre-load "work table" in place). I'm not too big a fan of the old-style
User-Defined Types, but to each his/her own...|||> If the field for Surname changes to Varchar(55) we should eb able to
> change it on all databases without hunting around for places it occu
I wouldn't use user-defined datatypes for this requirement. Although they
may facilitate a standard definition for types don't change, UDTs complicate
changes to the type after implementation.
Most data modeling tools have features that allow you to address your
dictionary requirements and create change scripts as well. You can also
store dictionary meta-data using extended properties. Also, VS 2005 Team
Edition for Database Professionals (currently in beta) provides refactoring
features and related tools.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
> tia, Matt

Data Dictionary project

We have several databases on SQL 2005 which were built seperately. Ive
been asked to come up with a plan for making sure that data elements are
consistent across all of them, and conform to an outside standard
definition which I have a document for. Ive also been asked to make it
easy for any changes in the external definition to be quickly
transferred to all databases. e.g. If the field for Surname changes to
Varchar(55) we should eb able to change it on all databases without
hunting around for places it occurs.
One idea Ive had is to create our own internal set of User Defined Data
Types matching the external dictionary. But ive then got to somehow map
this to all our databases and change them.
Has anyone out there been involved in anything similar or have any ideas?
tia, Matt
"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
RedGate software's SQLCompare application does database schema comparisons
and helps keep schemas in sync. Another option is to script out all your
changes ahead of time, apply strict QA and source control policies to them,
and make sure you apply any change scripts to all databases as necessary
(probably a good idea if you're not doing it already anyway). After all, in
a properly normalized database how many different places does Surname
actually occur? (My guess would be once, maybe twice if you have some sort
of pre-load "work table" in place). I'm not too big a fan of the old-style
User-Defined Types, but to each his/her own...
|||> If the field for Surname changes to Varchar(55) we should eb able to
> change it on all databases without hunting around for places it occu
I wouldn't use user-defined datatypes for this requirement. Although they
may facilitate a standard definition for types don't change, UDTs complicate
changes to the type after implementation.
Most data modeling tools have features that allow you to address your
dictionary requirements and create change scripts as well. You can also
store dictionary meta-data using extended properties. Also, VS 2005 Team
Edition for Database Professionals (currently in beta) provides refactoring
features and related tools.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
> tia, Matt

Data Dictionary project

We have several databases on SQL 2005 which were built seperately. Ive
been asked to come up with a plan for making sure that data elements are
consistent across all of them, and conform to an outside standard
definition which I have a document for. Ive also been asked to make it
easy for any changes in the external definition to be quickly
transferred to all databases. e.g. If the field for Surname changes to
Varchar(55) we should eb able to change it on all databases without
hunting around for places it occurs.
One idea Ive had is to create our own internal set of User Defined Data
Types matching the external dictionary. But ive then got to somehow map
this to all our databases and change them.
Has anyone out there been involved in anything similar or have any ideas?
tia, Matt"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
RedGate software's SQLCompare application does database schema comparisons
and helps keep schemas in sync. Another option is to script out all your
changes ahead of time, apply strict QA and source control policies to them,
and make sure you apply any change scripts to all databases as necessary
(probably a good idea if you're not doing it already anyway). After all, in
a properly normalized database how many different places does Surname
actually occur? (My guess would be once, maybe twice if you have some sort
of pre-load "work table" in place). I'm not too big a fan of the old-style
User-Defined Types, but to each his/her own...|||> If the field for Surname changes to Varchar(55) we should eb able to
> change it on all databases without hunting around for places it occu
I wouldn't use user-defined datatypes for this requirement. Although they
may facilitate a standard definition for types don't change, UDTs complicate
changes to the type after implementation.
Most data modeling tools have features that allow you to address your
dictionary requirements and create change scripts as well. You can also
store dictionary meta-data using extended properties. Also, VS 2005 Team
Edition for Database Professionals (currently in beta) provides refactoring
features and related tools.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
> tia, Mattsql

data cleansing and translation tools for relational databases

Hello All
We have a data migration project. This is basically what we plan to
achieve we have a legacy system which as some data with German long
texts like material texts,etc
We want to take data coming in flat files which as this german texts
and convert this to English text the texts are primarily products
names please advise what tools external or whatever is available to do
this translation to english the texts are about 15 to 20 characters
max.
Next we have data coming from legacy systems that we want to cleanse
like identify duplicates based on things like addresses:
eq: we might have a customer called ABC one at address 118 Main
Street, Nashua and another same customer defined in our another legacy
system called ABC Inc but address at Main Street, Nashua
We want a data cleansing tool or library that works on top of SQL
Server that can do this kind of data pattern identification,etc
Please advice free tools and tools within SQL if I am not aware of
that can do this as well good 3rd party tools that can do this.
Thanks
KarenHi
I don't know of any data cleansing tools that may perform all the functions
that you require. Even if you employed a company to manually clean the data
there is likely to be some degree of error at th e nd of the exercise.
I you carried out the changes yourself it may be possible to clean the data
using a translation table and/or ad-hoc queries.
John
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||There are many companies providing software and services to help clean up
name and address databases. These methods are usually specific to particular
postal systems or geographical areas so I suggest you Google for something
that meets your requirements in the locales of interest to you.
--
David Portas
SQL Server MVP
--|||Try the following link :
http://www.itcg.nl/
From their site :
CLUE
Have you ever experienced problems with duplicate customers,
addresses, products or any other information?
CLUE®, the CLUster Engine from ITCG is a generic component that uses
fuzzy matching logic to match data that is nearly the same. As a generic
component CLUE® is useful in many ways.
Few years ago they were specialising in cleaning data and
merging data from several sources into one database.
They had some fancy algoritms to detect double entries in
the database. They were very convincing in their presentation,
but I have no experience with their range of products.
good luck,
ben brugman
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||Another thing you might look at for address cleaning is CASS certification.
This is a standard set by the US Postal service, and there is much software
around to help identify bad addresses, but it does not address your other
data quality problems.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||I would suggest calling a company like www.listadvantage.com and have
the names cleaned and cass certified.
they r self serve and very easy to work with
"ben brugman" <ben@.niethier.nl> wrote in message news:<OlCP4zAlEHA.536@.TK2MSFTNGP11.phx.gbl>...
> Try the following link :
> http://www.itcg.nl/
> From their site :
> CLUE
> Have you ever experienced problems with duplicate customers,
> addresses, products or any other information?
> CLUE®, the CLUster Engine from ITCG is a generic component that uses
> fuzzy matching logic to match data that is nearly the same. As a generic
> component CLUE® is useful in many ways.
>
> Few years ago they were specialising in cleaning data and
> merging data from several sources into one database.
> They had some fancy algoritms to detect double entries in
> the database. They were very convincing in their presentation,
> but I have no experience with their range of products.
> good luck,
> ben brugman
>
> "Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
> news:a5fd468a.0409032125.35235ea@.posting.google.com...
> > Hello All
> >
> > We have a data migration project. This is basically what we plan to
> > achieve we have a legacy system which as some data with German long
> > texts like material texts,etc
> >
> > We want to take data coming in flat files which as this german texts
> > and convert this to English text the texts are primarily products
> > names please advise what tools external or whatever is available to do
> > this translation to english the texts are about 15 to 20 characters
> > max.
> >
> > Next we have data coming from legacy systems that we want to cleanse
> > like identify duplicates based on things like addresses:
> >
> > eq: we might have a customer called ABC one at address 118 Main
> > Street, Nashua and another same customer defined in our another legacy
> > system called ABC Inc but address at Main Street, Nashua
> >
> > We want a data cleansing tool or library that works on top of SQL
> > Server that can do this kind of data pattern identification,etc
> >
> > Please advice free tools and tools within SQL if I am not aware of
> > that can do this as well good 3rd party tools that can do this.
> >
> > Thanks
> > Karen

Data and log file naming conventions

I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryUse whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:

>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>

Data and log file naming conventions

I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:

>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>

Data and log file naming conventions

I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryThis is a multi-part message in MIME format.
--020803060805070804090702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:
>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>
--020803060805070804090702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Use whatever convention is logical and meets your needs; the main
thing is to be consistent.<br>
<br>
Personally, I tend to stick with the GUI default
<dbname>_data.mdf & <dbname>_log.ldf for everything I
can (using default values for things, if they're sensible &
reasonable, tends to avoid the pain of having to memorise different
conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer
(1, 2, 3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf,
mydb2_Data.ndf, etc.<br>
<br>
CREATE DATABASE <dbname>, with no extra parameters, will create
the database with <dbname>.mdf & <dbname>_log.ldf but
I've always thought that to be inconsistent (with "_log" but not
"_data") so I've always gone with the recommendation of the GUI (with
the _data & _log postfixes).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
HarrySmith wrote:
<blockquote cite="midONj5bFAtFHA.3604@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
</pre>
</blockquote>
</body>
</html>
--020803060805070804090702--