Data Flow Task Problem: files

Showing posts with label files. Show all posts

Thursday, March 29, 2012

Data files location.

Hello.
I have large database with very high workload (100 trans/sec avg.)
When database files were located on local disks on the server I tried to
part data files, index files, files with binary objects and log files from
each other and place them on separate physical discs to improve performance.
Now we're planning to use cluster server with external storage (HP MSA 1000).
A technician, who was configuring the hardware, told me that in this
configuration storage library segments presented to MSSQL server as discs are
already spread on many physical discs and there is no reason now to put
database files on different drives.
The question is: is this true? Will it be good if I'll put all files on
one logical disc and let storage library to manage data distridution across
physical discs?
Thanks.
Serge Shakhov
Hi
Ideally, the log and data portion of the database should be on different
LUNs ("storage library segments") and the Log should be on RAID-1 or RAID-10.
RAID-5 is not optimal for the Log due to it's high latency. Actually, with
the price of storage today, RAID-5 has no place near a IO intensive server.
Further to that, if you get presented 5 LUNs, and then present them to SQL
Server as only one disk, generally, you do not use up all the storage, so the
first 2 or 3 of the LUNS will have all the traffic, and there is no data on
the other LUNs, so they get no traffic. You don't want this. You rather have
5 database files, one on each LUN so that all the drives are busy.
Without knowing exactly how the LUNs are mapped to the physical drives and
how you want to map to LUNs to OS drives, I can't pass more comment.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Serge Shakhov" wrote:

> Hello.
> I have large database with very high workload (100 trans/sec avg.)
> When database files were located on local disks on the server I tried to
> part data files, index files, files with binary objects and log files from
> each other and place them on separate physical discs to improve performance.
> Now we're planning to use cluster server with external storage (HP MSA 1000).
> A technician, who was configuring the hardware, told me that in this
> configuration storage library segments presented to MSSQL server as discs are
> already spread on many physical discs and there is no reason now to put
> database files on different drives.
> The question is: is this true? Will it be good if I'll put all files on
> one logical disc and let storage library to manage data distridution across
> physical discs?
> Thanks.
> Serge Shakhov
|||Mike,
Is there a net guide somewhere on setting up log files and data optimally?
Thanks,
Mica
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F43766EE-6358-4515-B6A3-7705B516DA18@.microsoft.com...[vbcol=seagreen]
> Hi
> Ideally, the log and data portion of the database should be on different
> LUNs ("storage library segments") and the Log should be on RAID-1 or
> RAID-10.
> RAID-5 is not optimal for the Log due to it's high latency. Actually, with
> the price of storage today, RAID-5 has no place near a IO intensive
> server.
> Further to that, if you get presented 5 LUNs, and then present them to SQL
> Server as only one disk, generally, you do not use up all the storage, so
> the
> first 2 or 3 of the LUNS will have all the traffic, and there is no data
> on
> the other LUNs, so they get no traffic. You don't want this. You rather
> have
> 5 database files, one on each LUN so that all the drives are busy.
> Without knowing exactly how the LUNs are mapped to the physical drives and
> how you want to map to LUNs to OS drives, I can't pass more comment.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Serge Shakhov" wrote:

Data files location.

Hello.
I have large database with very high workload (100 trans/sec avg.)
When database files were located on local disks on the server I tried to
part data files, index files, files with binary objects and log files from
each other and place them on separate physical discs to improve performance.
Now we're planning to use cluster server with external storage (HP MSA 1000)
.
A technician, who was configuring the hardware, told me that in this
configuration storage library segments presented to MSSQL server as discs ar
e
already spread on many physical discs and there is no reason now to put
database files on different drives.
The question is: is this true? Will it be good if I'll put all files on
one logical disc and let storage library to manage data distridution across
physical discs?
Thanks.
Serge ShakhovHi
Ideally, the log and data portion of the database should be on different
LUNs ("storage library segments") and the Log should be on RAID-1 or RAID-10
.
RAID-5 is not optimal for the Log due to it's high latency. Actually, with
the price of storage today, RAID-5 has no place near a IO intensive server.
Further to that, if you get presented 5 LUNs, and then present them to SQL
Server as only one disk, generally, you do not use up all the storage, so th
e
first 2 or 3 of the LUNS will have all the traffic, and there is no data on
the other LUNs, so they get no traffic. You don't want this. You rather have
5 database files, one on each LUN so that all the drives are busy.
Without knowing exactly how the LUNs are mapped to the physical drives and
how you want to map to LUNs to OS drives, I can't pass more comment.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Serge Shakhov" wrote:

> Hello.
> I have large database with very high workload (100 trans/sec avg.)
> When database files were located on local disks on the server I tried to
> part data files, index files, files with binary objects and log files from
> each other and place them on separate physical discs to improve performanc
e.
> Now we're planning to use cluster server with external storage (HP MSA 100
0).
> A technician, who was configuring the hardware, told me that in this
> configuration storage library segments presented to MSSQL server as discs
are
> already spread on many physical discs and there is no reason now to put
> database files on different drives.
> The question is: is this true? Will it be good if I'll put all files on
> one logical disc and let storage library to manage data distridution acros
s
> physical discs?
> Thanks.
> Serge Shakhov|||Mike,
Is there a net guide somewhere on setting up log files and data optimally?
Thanks,
Mica
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F43766EE-6358-4515-B6A3-7705B516DA18@.microsoft.com...[vbcol=seagreen]
> Hi
> Ideally, the log and data portion of the database should be on different
> LUNs ("storage library segments") and the Log should be on RAID-1 or
> RAID-10.
> RAID-5 is not optimal for the Log due to it's high latency. Actually, with
> the price of storage today, RAID-5 has no place near a IO intensive
> server.
> Further to that, if you get presented 5 LUNs, and then present them to SQL
> Server as only one disk, generally, you do not use up all the storage, so
> the
> first 2 or 3 of the LUNS will have all the traffic, and there is no data
> on
> the other LUNs, so they get no traffic. You don't want this. You rather
> have
> 5 database files, one on each LUN so that all the drives are busy.
> Without knowing exactly how the LUNs are mapped to the physical drives and
> how you want to map to LUNs to OS drives, I can't pass more comment.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Serge Shakhov" wrote:
>

Data files in Flash Drive

Hi friends,
i want to know something abt sql server installation and database properties as under.
1) how much disk space reqd by multiuser sqlserver 2000 db to install ?
2) is it possible to install sql 2K on Flash Drive ( gen connect at USB) ?
3) When v create a database user Enterprise Manager , which files created by database ?
4) If i Know datafiles name of a database e.g. pubs , and if v copied on cd and paste on other system which has sql 2K install , does those database works ?
5) is it possible to create database on flash drive ?

thanx in advance.Come on...are these trick questions?|||I suppose it could be done, but you would have to be very sure that you properly detach the database before someone goes and unplugs the USB drive. I think you would set yourself up for a heck of a lot more user errors than SQL Server errors this way.

Data files from Database Object

The database object contains a member 'get_LogFiles' that returns a nice collection of the database's logfiles. I can't seem to find the comparable member to return the collection of datafiles. If someone can point me in the right direction, I would be most grateful

That's because log files do not reside in filegroups.

Check out the filegroup property, this contains a collection of data files.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

sql

data files filling up algorithm

Hi,
I have an sql database which has 3 datafiles belonging to the same filegroup. Could anyone tell me in what fashion the datafiles will fillup.
RegardsEqually in proportion
"ramy" <anonymous@.discussions.microsoft.com> wrote in message
news:162788A4-56D3-4BD0-8B42-15C0C740C85D@.microsoft.com...
> Hi,
> I have an sql database which has 3 datafiles belonging to the same
filegroup. Could anyone tell me in what fashion the datafiles will fillup.
> Regards|||As Hassan suggest, the algorythm is called "proportional fill"... SQL tries
to keep EACH data file the same percentage full... So it will add new
records to the data files which are less full, and when they become equally
full, a round robin type approach will keep the data files equally (by
percentage) full.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ramy" <anonymous@.discussions.microsoft.com> wrote in message
news:162788A4-56D3-4BD0-8B42-15C0C740C85D@.microsoft.com...
> Hi,
> I have an sql database which has 3 datafiles belonging to the same
filegroup. Could anyone tell me in what fashion the datafiles will fillup.
> Regards

Data Files and one FileGroup

Hi,
Is it OK if we have one FileGroup but 3 data files in different disk drives?
We have a database of size 150G, two data fils are in the same disk drive
and the third data file is in a different disk driver. All are in the same
filegroup.
Shouldn't we create another file group for the third data file?
Thanks,
RosieRosie,
You're probably fine. Multiple files in a filegroup is common. MS
recommends one file per physical processor to help parrallelism. One thing
to look out for is for best performance you want the files to file at the
same rate. Autogrow and adding additional files often leads to hot spots on
the IO subsystem.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
> Shouldn't we create another file group for the third data file?
> Thanks,
> Rosie
>
>|||inline
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
sunila> Yes. This will help you distribute the IO load across multiple
spindles.
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
Sunila> I will be curious to know why created two files belonging to same
file group on the same disk?
> Shouldn't we create another file group for the third data file?
Sunila> SQL Server allows creating file group that contains files from one
or more physical disks.
> Thanks,
> Rosie
>
>|||> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism.
Interesting... Do you have a link to that statement? So far, the only thing I've seen is that you
might want to consider the number of physical disks. Somewhere between 1 file per disk to 1 file per
8 disks. MS haven't given out a firm recommendation as it is very hw dependent.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <someone@.nowhere.com> wrote in message news:CIPef.19373$%t4.15694@.trnddc07...
> Rosie,
> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism. One thing to look out for is for best performance you
> want the files to file at the same rate. Autogrow and adding additional files often leads to hot
> spots on the IO subsystem.
>
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
>> Hi,
>> Is it OK if we have one FileGroup but 3 data files in different disk drives?
>> We have a database of size 150G, two data fils are in the same disk drive
>> and the third data file is in a different disk driver. All are in the same
>> filegroup.
>> Shouldn't we create another file group for the third data file?
>> Thanks,
>> Rosie
>>
>

Data Files and one FileGroup

Hi,
Is it OK if we have one FileGroup but 3 data files in different disk drives?
We have a database of size 150G, two data fils are in the same disk drive
and the third data file is in a different disk driver. All are in the same
filegroup.
Shouldn't we create another file group for the third data file?
Thanks,
Rosie
Rosie,
You're probably fine. Multiple files in a filegroup is common. MS
recommends one file per physical processor to help parrallelism. One thing
to look out for is for best performance you want the files to file at the
same rate. Autogrow and adding additional files often leads to hot spots on
the IO subsystem.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
> Shouldn't we create another file group for the third data file?
> Thanks,
> Rosie
>
>
|||inline
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
sunila> Yes. This will help you distribute the IO load across multiple
spindles.

> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
Sunila> I will be curious to know why created two files belonging to same
file group on the same disk?
> Shouldn't we create another file group for the third data file?
Sunila> SQL Server allows creating file group that contains files from one
or more physical disks.

> Thanks,
> Rosie
>
>
|||> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism.
Interesting... Do you have a link to that statement? So far, the only thing I've seen is that you
might want to consider the number of physical disks. Somewhere between 1 file per disk to 1 file per
8 disks. MS haven't given out a firm recommendation as it is very hw dependent.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <someone@.nowhere.com> wrote in message news:CIPef.19373$%t4.15694@.trnddc07...
> Rosie,
> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism. One thing to look out for is for best performance you
> want the files to file at the same rate. Autogrow and adding additional files often leads to hot
> spots on the IO subsystem.
>
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
>

Data Files and one FileGroup

Hi,
Is it OK if we have one FileGroup but 3 data files in different disk drives?
We have a database of size 150G, two data fils are in the same disk drive
and the third data file is in a different disk driver. All are in the same
filegroup.
Shouldn't we create another file group for the third data file?
Thanks,
RosieRosie,
You're probably fine. Multiple files in a filegroup is common. MS
recommends one file per physical processor to help parrallelism. One thing
to look out for is for best performance you want the files to file at the
same rate. Autogrow and adding additional files often leads to hot spots on
the IO subsystem.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
> Shouldn't we create another file group for the third data file?
> Thanks,
> Rosie
>
>|||inline
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
sunila> Yes. This will help you distribute the IO load across multiple
spindles.

> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
Sunila> I will be curious to know why created two files belonging to same
file group on the same disk?
> Shouldn't we create another file group for the third data file?
Sunila> SQL Server allows creating file group that contains files from one
or more physical disks.

> Thanks,
> Rosie
>
>|||> You're probably fine. Multiple files in a filegroup is common. MS recommends one file pe
r
> physical processor to help parrallelism.
Interesting... Do you have a link to that statement? So far, the only thing
I've seen is that you
might want to consider the number of physical disks. Somewhere between 1 fil
e per disk to 1 file per
8 disks. MS haven't given out a firm recommendation as it is very hw depende
nt.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <someone@.nowhere.com> wrote in message news:CIPef.19373$%t4.15694@.trnddc07...seagreen">
> Rosie,
> You're probably fine. Multiple files in a filegroup is common. MS recomm
ends one file per
> physical processor to help parrallelism. One thing to look out for is for
best performance you
> want the files to file at the same rate. Autogrow and adding additional f
iles often leads to hot
> spots on the IO subsystem.
>
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
>

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 files & Transaction log recovery

How do I backup a data file & transaction log from one instance of SQL to a different sql server. I have done a complete backup and now I want to restore the database but on a different SQL server, but the *.mdf and *.ldf is missing, so how do I restore them also?When you did the full backup you created a backup file. When you restore
this the mdf and ldf will be created.
On the instance you want to restore to restore from the device. On the
second tab will be the location of the mdf and ldf - this will be the
file names from the other instance so you will have to overtype with
another filename or path.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||If you restore to another SQL server you also have to think about databaseusers. The users from the database you restore stil are in the DB when it's on the new server but you not see them. You can either run the drop user or use sp_change_users_login 'Auto_Fix', 'username', NULL. This is call ophran users
/Joel|||Thanks a million. I completed the backup and did the restore on the other instance of SQL server and it worked like a charm. You're a life saver!

Data files & Log Files in SQL2000

Dear all,
I have a question of the Data files & Log Files in SQL2000.
Is that once someone got the data files and log files of the database, and
he had a SQL server 2000 installed, he can use sp_attach_db command to
restore the database?
Is there any control for this case?
Best Regards,
IvanOnce you have the mdf and or ldf files of a particular db, then you can
attach the database, as far as I know, there is no remedy for this
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||Yes , just before detaching you need to kick off all users of the database
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||If someone stop the sql server service, copy out the data file and log file,
and start the service...
Then, he can easily duplicate the database.
Ivan
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
> Yes , just before detaching you need to kick off all users of the
> database
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Dear all,
>> I have a question of the Data files & Log Files in SQL2000.
>> Is that once someone got the data files and log files of the database,
>> and he had a SQL server 2000 installed, he can use sp_attach_db command
>> to restore the database?
>> Is there any control for this case?
>> Best Regards,
>> Ivan
>|||Ivan
Well , not in the same name unless the old database will be droped.
Actually you don't need to stop Services because you can use BACKUP/RESTORE
commands
"Ivan" <ivan@.microsoft.com> wrote in message
news:eyYLC9x1GHA.2036@.TK2MSFTNGP05.phx.gbl...
> If someone stop the sql server service, copy out the data file and log
> file, and start the service...
> Then, he can easily duplicate the database.
> Ivan
> "Uri Dimant" <urid@.iscar.co.il>
> ¼¶¼g©ó¶l¥ó·s»D:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
>> Yes , just before detaching you need to kick off all users of the
>> database
>>
>> "Ivan" <ivan@.microsoft.com> wrote in message
>> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Dear all,
>> I have a question of the Data files & Log Files in SQL2000.
>> Is that once someone got the data files and log files of the database,
>> and he had a SQL server 2000 installed, he can use sp_attach_db command
>> to restore the database?
>> Is there any control for this case?
>> Best Regards,
>> Ivan
>>
>|||> Is there any control for this case?
You can secure your database files (and backups) with NTFS permissions so
that only authorized users have access.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||... and even use EFS to encrypt the files, in case someone get their hands on the machine and
installs another OS on top of the old OS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
>> Is there any control for this case?
> You can secure your database files (and backups) with NTFS permissions so that only authorized
> users have access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.microsoft.com> wrote in message news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Dear all,
>> I have a question of the Data files & Log Files in SQL2000.
>> Is that once someone got the data files and log files of the database, and he had a SQL server
>> 2000 installed, he can use sp_attach_db command to restore the database?
>> Is there any control for this case?
>> Best Regards,
>> Ivan
>|||Hi,
To add on to tibor; take a look into the below article on implementing EFS
on sql server and its benfits.
http://www.sqlservercentral.com/columnists/bkelley/implementing_efs.asp
With EFS, database files are encrypted under the identity of the account
running SQL Server. Only this account can decrypt the files. If you need to
change the account that runs SQL Server, you should first decrypt the files
under the old account, then re-encrypt them under the new account.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfrIXSy1GHA.4752@.TK2MSFTNGP05.phx.gbl...
> ... and even use EFS to encrypt the files, in case someone get their hands
> on the machine and installs another OS on top of the old OS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
>> Is there any control for this case?
>> You can secure your database files (and backups) with NTFS permissions so
>> that only authorized users have access.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ivan" <ivan@.microsoft.com> wrote in message
>> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Dear all,
>> I have a question of the Data files & Log Files in SQL2000.
>> Is that once someone got the data files and log files of the database,
>> and he had a SQL server 2000 installed, he can use sp_attach_db command
>> to restore the database?
>> Is there any control for this case?
>> Best Regards,
>> Ivan
>>
>

Data files & Log Files in SQL2000

Dear all,
I have a question of the Data files & Log Files in SQL2000.
Is that once someone got the data files and log files of the database, and
he had a SQL server 2000 installed, he can use sp_attach_db command to
restore the database?
Is there any control for this case?
Best Regards,
Ivan
Once you have the mdf and or ldf files of a particular db, then you can
attach the database, as far as I know, there is no remedy for this
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>
|||Yes , just before detaching you need to kick off all users of the database
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>
|||If someone stop the sql server service, copy out the data file and log file,
and start the service...
Then, he can easily duplicate the database.
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gb l...
> Yes , just before detaching you need to kick off all users of the
> database
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>
|||Ivan
Well , not in the same name unless the old database will be droped.
Actually you don't need to stop Services because you can use BACKUP/RESTORE
commands
"Ivan" <ivan@.microsoft.com> wrote in message
news:eyYLC9x1GHA.2036@.TK2MSFTNGP05.phx.gbl...
> If someone stop the sql server service, copy out the data file and log
> file, and start the service...
> Then, he can easily duplicate the database.
> Ivan
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gb l...
>
|||> Is there any control for this case?
You can secure your database files (and backups) with NTFS permissions so
that only authorized users have access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>
|||... and even use EFS to encrypt the files, in case someone get their hands on the machine and
installs another OS on top of the old OS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
> You can secure your database files (and backups) with NTFS permissions so that only authorized
> users have access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.microsoft.com> wrote in message news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>
|||Hi,
To add on to tibor; take a look into the below article on implementing EFS
on sql server and its benfits.
http://www.sqlservercentral.com/colu...enting_efs.asp
With EFS, database files are encrypted under the identity of the account
running SQL Server. Only this account can decrypt the files. If you need to
change the account that runs SQL Server, you should first decrypt the files
under the old account, then re-encrypt them under the new account.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfrIXSy1GHA.4752@.TK2MSFTNGP05.phx.gbl...
> ... and even use EFS to encrypt the files, in case someone get their hands
> on the machine and installs another OS on top of the old OS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
>

Data files & Log Files in SQL2000

Dear all,
I have a question of the Data files & Log Files in SQL2000.
Is that once someone got the data files and log files of the database, and
he had a SQL server 2000 installed, he can use sp_attach_db command to
restore the database?
Is there any control for this case?
Best Regards,
IvanOnce you have the mdf and or ldf files of a particular db, then you can
attach the database, as far as I know, there is no remedy for this
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||Yes , just before detaching you need to kick off all users of the database
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||If someone stop the sql server service, copy out the data file and log file,
and start the service...
Then, he can easily duplicate the database.
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...[vbcol
=seagreen]
> Yes , just before detaching you need to kick off all users of the
> database
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>[/vbcol]|||Ivan
Well , not in the same name unless the old database will be droped.
Actually you don't need to stop Services because you can use BACKUP/RESTORE
commands
"Ivan" <ivan@.microsoft.com> wrote in message
news:eyYLC9x1GHA.2036@.TK2MSFTNGP05.phx.gbl...
> If someone stop the sql server service, copy out the data file and log
> file, and start the service...
> Then, he can easily duplicate the database.
> Ivan
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
>|||> Is there any control for this case?
You can secure your database files (and backups) with NTFS permissions so
that only authorized users have access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||... and even use EFS to encrypt the files, in case someone get their hands
on the machine and
installs another OS on top of the old OS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
> You can secure your database files (and backups) with NTFS permissions so
that only authorized
> users have access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.microsoft.com> wrote in message news:OjQEjMx1GHA.476@.TK2MSFTN
GP06.phx.gbl...
>|||Hi,
To add on to tibor; take a look into the below article on implementing EFS
on sql server and its benfits.
http://www.sqlservercentral.com/col...menting_efs.asp
With EFS, database files are encrypted under the identity of the account
running SQL Server. Only this account can decrypt the files. If you need to
change the account that runs SQL Server, you should first decrypt the files
under the old account, then re-encrypt them under the new account.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfrIXSy1GHA.4752@.TK2MSFTNGP05.phx.gbl...
> ... and even use EFS to encrypt the files, in case someone get their hands
> on the machine and installs another OS on top of the old OS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
>

Data Files

Hi,
I've got one database with a 50GB file. I want to split that file on, for
example 12 files of 4GB each. What is the best aproach to this operation ?
Any ideas ?
Thanks in avance
CCHere's the FileGroup example from BOL, this will enable the creation of the
12 x 4Gb files. If you then re-create all your CLUSTERED indexes specifying
this new filegroup the data will move...
B. Add a filegroup with two files to a database
This example creates a filegroup in the Test 1 database created in Example A
and adds two 5-MB files to the filegroup. It then makes Test1FG1 the default
filegroup.
USE master
GO
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1
GO
ALTER DATABASE Test1
ADD FILE
( NAME = test1dat3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1dat4,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP Test1FG1
ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT
GO
HTH. Ryan
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CC|||Why do you want to split them? if you mean in operation, you can use
filegroups...
MC
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CCsql

Data Files

Hi,
I've got one database with a 50GB file. I want to split that file on, for
example 12 files of 4GB each. What is the best aproach to this operation ?
Any ideas ?
Thanks in avance
CCHere's the FileGroup example from BOL, this will enable the creation of the
12 x 4Gb files. If you then re-create all your CLUSTERED indexes specifying
this new filegroup the data will move...
B. Add a filegroup with two files to a database
This example creates a filegroup in the Test 1 database created in Example A
and adds two 5-MB files to the filegroup. It then makes Test1FG1 the default
filegroup.
USE master
GO
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1
GO
ALTER DATABASE Test1
ADD FILE
( NAME = test1dat3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1dat4,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP Test1FG1
ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT
GO
--
HTH. Ryan
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CC|||Why do you want to split them? if you mean in operation, you can use
filegroups...
MC
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CC

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!

Tuesday, March 27, 2012

Data file missing

Last night one of my SQL Servers stopped and restarted for no reason. When
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>
|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas

"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>

Data file missing

Last night one of my SQL Servers stopped and restarted for no reason. When
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>

Thursday, March 29, 2012

Data files location.

Data files location.

Data files in Flash Drive

Data files from Database Object

data files filling up algorithm

Data Files and one FileGroup

Data Files and one FileGroup

Data Files and one FileGroup

Data files and filegroups

Data files & Transaction log recovery

Data files & Log Files in SQL2000

Data files & Log Files in SQL2000

Data files & Log Files in SQL2000

Data Files

Data Files

Data File size issue

Data File size issue

Data File size issue

Tuesday, March 27, 2012

Data file missing

Data file missing

Data Flow Task Problem

Blog Archive

About Me