Data Flow Task Problem: March 2012

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 will not shrink

I have an issue, and hopefully I can explain this fully enough. We
have a 3 server environment, all running SQL Server 2000 SP4 on
Windows 2000 Server. Server A, Server B, and Server C. Server A
replicates to Server B, Server B then replicates to Server C.
Server B has a database that two data files and two log files on two
separate drives. So drive D has 1 datafile and 1 log file, and drive
E has the same. Drive E is running out of disk space and drive D had
some to spare. In Enterprise Manager, I went to the Shrink Database
menu for that database, and I choosed 'Empty the file (data will
migrate to other files in the file group) option. That finished
successfully. But now I see that only 2 mb is being used, but the
database size is still 22358 mb. I have tried the other shrink
options with no luck.
So I have tried:
-Compress Pages and then truncate free space from the file
-Truncate free space from the other end of file
-Shrink file to "min size"
-I have also tried checking the 'Move pages to beginning of file
before shrinking, then retrying the steps above.
Also, we have tried shrinking the transaction table using a stored
proceedure called sp_forceshrink_log as well as a few other things we
have seen online.
After all this, the file size is still 20+ gb. My question is this: Is
there anything else we can try to free up the close to 20 gigs of
space? Also, this database is in the middle of a replicated chain,
what effect would there be in deleting the empty file in the
properties of the database. Is that even possible with replication
enabled?
Thanks for any help you can provide.
JonIf you want to shrink a log file, you should do it immediately after
you backup the transaction log. If you do it later, you will get a
warning like "Cannot shrink log file 2 (LogFile) because all logical
log files are in use." and the file will not be shrinked.
If you get a warning saying "The log was not truncated because records
at the beginning of the log are pending replication. Ensure the Log
Reader Agent is running or use sp_repldone to mark transactions as
distributed", you should see this thread, for example:
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
read/23ca52b9df791e59/
In any case, you should not delete the transaction log file (especially
if the database is in a transactional replication chain).
--
Razvan Socol
SQL Server MVP|||Thanks for your response, but the issue is not with the log file, but
the data file. I emptied the contents of one data file to another
data file on another drive. So I have 2 mb used in the file, but the
file size is 20+ gb on the physical drive. I am unable to shrink the
file size using the options found in the "Shrink Database" window. So
I am looking for alternatives to getting the data file size down.
Jon
On Feb 3, 2:42 am, "Razvan Socol" <rso...@.gmail.com> wrote:
> If you want to shrink a log file, you should do it immediately after
> you backup the transaction log. If you do it later, you will get a
> warning like "Cannot shrink log file 2 (LogFile) because all logical
> log files are in use." and the file will not be shrinked.
> If you get a warning saying "The log was not truncated because records
> at the beginning of the log are pending replication. Ensure the Log
> Reader Agent is running or use sp_repldone to mark transactions as
> distributed", you should see this thread, for example:http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
> read/23ca52b9df791e59/
> In any case, you should not delete the transaction log file (especially
> if the database is in a transactional replication chain).
> --
> Razvan Socol
> SQL Server MVP

Data File Used Space

Using the master.sysperfinfo table in a SQL Server database you can obtain
the "Log file(s) Used sizes (KB)".
I would like to determine the "Database file(s) Used Sizes (KB)".
How can I determine this value?
Thank You,Lookup the usage and details of the system procedure sp_spaceused in SQL
Server Books Online.
Anith

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.com
See my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.droptable.co m...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com
|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.comSee my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.SQ
droptable.com...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.codecomments.com ***sql

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
--
Message posted via http://www.sqlmonster.comSee my reply to your other message.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.SQLMonster.com...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.sqlmonster.com

Data file space used calculations

Hi,

Is there any table I can query from to get the space used by a data or log file? I can get this info from TaskPad but I want to query from tables. The sp_spaceused procedure gets the info for a database or table but not the 'data or log' files.

Thanks for any help.

VinnieYou can do this

select cast(size * 8 as int) as Size from dbo.sysfiles

The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

HTH|||Originally posted by rhigdon
You can do this

select cast(size * 8 as int) as Size from dbo.sysfiles

The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

HTH

Thanks a lot but the sysfiles can give me only size whereas I need the space used and space free for a data file /log file. Any other suggestions are appreciated.

Thanks in advance.|||For log file you can do:

dbcc sqlperf('logspace')

I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

HTH|||Originally posted by rhigdon
For log file you can do:

dbcc sqlperf('logspace')

I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

HTH

Hi,

That is exactly what I am looking for is 'data file'. I could get for log file in sysperfinfo table. I was unsuccessful to find any info for datafile to calculate space used or space free. How does the Taskpad display these things? They may be doing from table. What is it is the question? If any one can help It will be a great help.

Thanks
Vinnie|||True, you can use SP_SPACEUSED to get the data, index space usage and DBCC SQLPERF(LOGSPACE) for the %age of Tlog used for any database.

Run SP_HELPFILE to get the information for physical files associated to that database.

Refer to Vyas's link (http://vyaskn.tripod.com/track_sql_database_file_growth.htm) for more information.

Data file size used space

In the SQL EM, we are able to get the data file size with
used space and free space details. Similarly for the log
file also.
We have the dbcc sqlperf command to find out the log file
used space details. Is there any way to find out the
data file size? sp_helpfile just gives the total size of
the data file wherein sp_spaceused is also not
distinguishing between used and free space.
Could anyone help me out in this?Do a google search on DBCC SHOWFILESTATS. This is what EM is using. Not documented, so all usual
warnings apply.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:08fc01c3ad95$4cf28380$a301280a@.phx.gbl...
> In the SQL EM, we are able to get the data file size with
> used space and free space details. Similarly for the log
> file also.
> We have the dbcc sqlperf command to find out the log file
> used space details. Is there any way to find out the
> data file size? sp_helpfile just gives the total size of
> the data file wherein sp_spaceused is also not
> distinguishing between used and free space.
> Could anyone help me out in this?

data file size problem

how can i change the initial size of the data and log file size ?

in my database properties it shows that my data file size is 81 mb and log file size is 985 mb! but my database only contains some tables and stored procedures with few rows of data in each table

and i checked that the actual mdf and ldf files are really that big... i tried to change it but it didn't work...

can someone please teach me how to change it thanks!

Backup the DB with Truncate log. check books online for more info.

|||

http://www.codeproject.com/useritems/truncate_log_SQL_server.asp

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 Size

SELECT size_in_mb,used_size_in_mb,size_in_mb-used_size_in_mb as free_in_mb FROM (
SELECT cntr_value/1024 size_in_mb ,
(SELECT cntr_value/1024 FROM master..sysperfinfo WHERE counter_name='Log File(s) Used Size (KB)' AND instance_name='mydb') used_size_in_mb
FROM master..sysperfinfO WHERE counter_name='Log File(s) Size (KB)' AND INSTANCE_NAME='mydb'
) a

I need to store totalsize,usedsize,freesize of the datafiles in a table to get an average of how much my datafile has increased over a week.
The above query i am using is for logfile size. Can any one help me with datafile size plz.
I've checked sp_helpfile, sysfiles but couldn't find what i am lookin for(used and free space). EM in taskpad view for a database shows the statistics for the datafile. I've tried a trace to find out a stored procedure but couldn't!!!
May be i am unaware of a simple stored-procedure that can do this for me.

Howdy!use master
go
sp_helptext sp_spaceused
go

Maybe you will get some ideas from here ... am a little busy ... so just help yourself ...|||You can use code from sp_spaceused to make your own logic ...|||use master
go
sp_helptext sp_spaceused
go

Maybe you will get some ideas from here ... am a little busy ... so just help yourself ...

Thanx for guiding; i would try.

Howdy!

Data File size

My datafile is at 80GB. The data used in the datafile is about 70GB and
growing, with approximately 4,000,000 records in a table that contains a
Text field. I have a weekly purge that deletes about 500,000 records. (Disk
space is at a premium.) When I delete the 500,000 records in the table, the
data used in the data file does not reduce, but remains the same and keeps
on growing. I need to keep the datafile comfortably below 80GB.
My understanding is that shrinking the data file is not the best thing to
do, but if I have to, then I will. So essentially, to keep the data file
comfortably below 80GB I would:
1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
Would the above 2 steps be the means of keeping my datafile comfortably
below 80GB?
Message posted via http://www.droptable.com
You can turn off auto-grow option and lock your
datafile size on 80Gb. Make sure you have enough space
for your transaction log though in order to be able to
delete records weekly.
Shrink is really not good operation especially on large DBs.
Regards.
"Robert Richards via droptable.com" wrote:

> My datafile is at 80GB. The data used in the datafile is about 70GB and
> growing, with approximately 4,000,000 records in a table that contains a
> Text field. I have a weekly purge that deletes about 500,000 records. (Disk
> space is at a premium.) When I delete the 500,000 records in the table, the
> data used in the data file does not reduce, but remains the same and keeps
> on growing. I need to keep the datafile comfortably below 80GB.
> My understanding is that shrinking the data file is not the best thing to
> do, but if I have to, then I will. So essentially, to keep the data file
> comfortably below 80GB I would:
> 1. Perform the weekly purge (deleting approximately 500,000 records).
> 2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
> Would the above 2 steps be the means of keeping my datafile comfortably
> below 80GB?
> --
> Message posted via http://www.droptable.com
>
|||That does not sound like a solution, turning off auto grow. The reason why
is that at the current rate, the data file is growing and will eventually
fill up the 80GB. The purge is not reducing space in the data file.
Probably due to a high water mark. Therefore, even though I am keeping the
number of records at 4,000,000 or below, the unused space never gets
recovered, thus the datafile keeps growing even though the number of
records remains approximately the same.
I understand that ShrinkFile is not the best option, but what other option
is there?
Message posted via http://www.droptable.com
|||Hi,
What you are performing is absolutely perfect. Do a weekly purge or move the
old data into a history database.
Since you are going to store the data back into the same file you may not
need to shrink the file because the space you
purged will be utilized for the new data which is coming in.
What is the main reason you need to keep 80GB as maximum for your database?
There are many databases in the globe with more than Terabytes
and running smoothly.
Thanks
Hari
Sql Server MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:0be9557414954ceca658ef750e5bb45e@.droptable.co m...
> My datafile is at 80GB. The data used in the datafile is about 70GB and
> growing, with approximately 4,000,000 records in a table that contains a
> Text field. I have a weekly purge that deletes about 500,000 records.
> (Disk
> space is at a premium.) When I delete the 500,000 records in the table,
> the
> data used in the data file does not reduce, but remains the same and keeps
> on growing. I need to keep the datafile comfortably below 80GB.
> My understanding is that shrinking the data file is not the best thing to
> do, but if I have to, then I will. So essentially, to keep the data file
> comfortably below 80GB I would:
> 1. Perform the weekly purge (deleting approximately 500,000 records).
> 2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
> Would the above 2 steps be the means of keeping my datafile comfortably
> below 80GB?
> --
> Message posted via http://www.droptable.com
|||I do not believe I am being understood. When I delete the 500,000, it is
not to an archive table. These 500,000 records are gone.
The 80GB max is due to available disk space.
When I delete records, I need to reclaim the unused space. Are the two
steps below a way of doing that?
1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
Is there a better way to reclaim unused space?
Message posted via http://www.droptable.com
|||What you are seeing is mainly due to the fact you have text or image
columns. Even though you delete rows it may not always free up all the
space it previously used for the text columns. If you have a clustered
index on the table you can reindex the table and possibly free up some space
that may have been due to fragmentation of the non-text columns. But in
2000 there is nothing you can do to clean up text space usage short of
exporting all the data, truncating the table and importing it back in. SQL
2005 will allow you to reorganize blobs. Your real solution is to get more
disk space so you can deal with it better.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:0be9557414954ceca658ef750e5bb45e@.droptable.co m...
> My datafile is at 80GB. The data used in the datafile is about 70GB and
> growing, with approximately 4,000,000 records in a table that contains a
> Text field. I have a weekly purge that deletes about 500,000 records.
> (Disk
> space is at a premium.) When I delete the 500,000 records in the table,
> the
> data used in the data file does not reduce, but remains the same and keeps
> on growing. I need to keep the datafile comfortably below 80GB.
> My understanding is that shrinking the data file is not the best thing to
> do, but if I have to, then I will. So essentially, to keep the data file
> comfortably below 80GB I would:
> 1. Perform the weekly purge (deleting approximately 500,000 records).
> 2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
> Would the above 2 steps be the means of keeping my datafile comfortably
> below 80GB?
> --
> Message posted via http://www.droptable.com
|||Why then does the "data used" decrease on the data file when I delete
records from the same table in my Test environment, but when I delete
records from the table in production the "data used" does not decrease?
Message posted via http://www.droptable.com
|||How are you determining the "data used"? Are you using sp_spaceused? Have
you tried running DBCC UPDATE USAGE or specifying the UpdateUasge option?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:ce78b9bd5c5447658b8a0e2badaf82cb@.droptable.co m...
> Why then does the "data used" decrease on the data file when I delete
> records from the same table in my Test environment, but when I delete
> records from the table in production the "data used" does not decrease?
> --
> Message posted via http://www.droptable.com
|||Also, the TRUCATE ONLY option will not Free Up the space becaue truncate
will only remove from the end of the file. You need to MOVE all the data to
the head of the file before shrinking. You can only do that if you use the
DBCC SHRINKFILE(MyDB_Data, TargetSize).
Reindexing the clustered index will most likely cause the file to grow even
more because the entire table is basically copied to a new when as it
reindexes. However, if you reindex, that will defragment by the index
order. Then if you shrink the file, it will move the data pages in
defragmented order, but could take quite some time, especially on a 80 GB
database.
You should consider creating 1 to 1 relationships for the LOB data and their
associated base tables. Then you could create VIEWs to replace the original
table definitions to minimize code impact. Then put all of the LOB data in
a seperate table(s). Those tables could then be created in seperate files
on seperate FileGroups. The shrinking and reordering just those files
should minimize the durations of those operations.
If you want to garauntee what you are looking at, run sp_spaceused
@.UPDATEUSAGE = 'true'.
Sincerely,
Anthony Thomas

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ecOFGg9ZFHA.3168@.TK2MSFTNGP10.phx.gbl...
How are you determining the "data used"? Are you using sp_spaceused? Have
you tried running DBCC UPDATE USAGE or specifying the UpdateUasge option?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:ce78b9bd5c5447658b8a0e2badaf82cb@.droptable.co m...
> Why then does the "data used" decrease on the data file when I delete
> records from the same table in my Test environment, but when I delete
> records from the table in production the "data used" does not decrease?
> --
> Message posted via http://www.droptable.com
|||I am still not clear how or why my data continues to increase. What you say
I cannot quite comprehend given my current data set. For instance:
5/21/2005
Records in table: 4,091,571
Data used within data file: 64,546.3 MB
6/5/2005
Records in table: 3,679,559
Data used within data file: 71,828.5 MB
So even though I have 412,012 less records in the table (the only other
tables are small, static lookup tables) the data within the data file has
grown 7,282.2 MB.
I guess I just do not understand the text column well enough, to understand
why the substantial growth, despite the significantly less records in the
table.
Please help, as I am going to have to explain this to my supervisor in such
a way to either justify more disk space, or resolve the growth issue
despite less records. Thanks for your patience.
Message posted via http://www.droptable.com

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 will not shrink

Data File Used Space

Data File Used Space

Data File Used Space

Data File Used Space

Data file space used calculations

Data file size used space

data file size problem

Data File size issue

Data File size issue

Data File size issue

Data File Size Increase

Data File Size Increase

Data File Size Increase

Data File Size

Data File size

Data Flow Task Problem

Blog Archive

About Me