Greetings all
I have a database where the primary filegroup has been allocated very
fragmented. I have tried to defragment my database by adding a new file
to the primary filegroup, allocation space properly and calling DBCC
SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
other.
However, when I try to remove the original file i get an error message
that "The primary data file cannot be removed". After some research, I
discovered that the system tables seem to be allocated din the primary
data file and that EMPTYFILE does not move these.
Is there any way to move system tables to another file in the PRIMARY
filegroup making the other file the new primary datafile?
/Thomas Kejser
Thomas Kejser wrote:
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new
> file to the primary filegroup, allocation space properly and calling
> DBCC SHRINKFILE('File1', EMPTYFILE) to move all the data from one
> file to the other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser
I don't think you can remove the primary data file. See this thread as
it mentnions a possible hack. Just test it on a dev server and back up
your database before trying it in production:
http://webservertalk.com/t429554.html
David Gugick
Imceda Software
www.imceda.com
|||if you have extent fragmentation then adding another filegroup will not
necessarilly resolve your problem. you need to
1) drop the additional file (run dbcc shrinkfile with the emptyfile option
and delete secondary file)
2) run dbcc checkdb with repair_rebuild to repair allocation errors and
rebuild indexes.(this will fix extent fragmentation currently experienced)
3) if this is resolved but you still feel that you need to defragment the
MDF file .. .then you can either
- stop sql server
- copy mdf and ldf file off the OS drive
- defragment drive
- copy mdf file back to the OS drive
OR
- use a utility which it's name escapes me at the moment(SQLDefrag?)
- this will allow you to perform task without copying the database files
off the drive(I have not found it as effective as the first option)
HTH
"Thomas Kejser" <thomas@.kejser.org> wrote in message
news:%23bNEQe7IFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new file
> to the primary filegroup, allocation space properly and calling DBCC
> SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
> other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser
|||You might want to consider moving your data to another filegroup and use
your primary filegroup just for database metadata.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O48tom7IFHA.656@.TK2MSFTNGP14.phx.gbl...
> Thomas Kejser wrote:
> I don't think you can remove the primary data file. See this thread as it
> mentnions a possible hack. Just test it on a dev server and back up your
> database before trying it in production:
> http://webservertalk.com/t429554.html
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
Showing posts with label emptying. Show all posts
Showing posts with label emptying. Show all posts
Friday, February 17, 2012
Emptying the primary data file
Greetings all
I have a database where the primary filegroup has been allocated very
fragmented. I have tried to defragment my database by adding a new file
to the primary filegroup, allocation space properly and calling DBCC
SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
other.
However, when I try to remove the original file i get an error message
that "The primary data file cannot be removed". After some research, I
discovered that the system tables seem to be allocated din the primary
data file and that EMPTYFILE does not move these.
Is there any way to move system tables to another file in the PRIMARY
filegroup making the other file the new primary datafile?
/Thomas KejserThomas Kejser wrote:
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new
> file to the primary filegroup, allocation space properly and calling
> DBCC SHRINKFILE('File1', EMPTYFILE) to move all the data from one
> file to the other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser
I don't think you can remove the primary data file. See this thread as
it mentnions a possible hack. Just test it on a dev server and back up
your database before trying it in production:
http://dbforums.com/t429554.html
David Gugick
Imceda Software
www.imceda.com|||if you have extent fragmentation then adding another filegroup will not
necessarilly resolve your problem. you need to
1) drop the additional file (run dbcc shrinkfile with the emptyfile option
and delete secondary file)
2) run dbcc checkdb with repair_rebuild to repair allocation errors and
rebuild indexes.(this will fix extent fragmentation currently experienced)
3) if this is resolved but you still feel that you need to defragment the
MDF file .. .then you can either
- stop sql server
- copy mdf and ldf file off the OS drive
- defragment drive
- copy mdf file back to the OS drive
OR
- use a utility which it's name escapes me at the moment(SQLDefrag?)
- this will allow you to perform task without copying the database files
off the drive(I have not found it as effective as the first option)
HTH
"Thomas Kejser" <thomas@.kejser.org> wrote in message
news:%23bNEQe7IFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new file
> to the primary filegroup, allocation space properly and calling DBCC
> SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
> other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser|||You might want to consider moving your data to another filegroup and use
your primary filegroup just for database metadata.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O48tom7IFHA.656@.TK2MSFTNGP14.phx.gbl...
> Thomas Kejser wrote:
>> Greetings all
>> I have a database where the primary filegroup has been allocated very
>> fragmented. I have tried to defragment my database by adding a new
>> file to the primary filegroup, allocation space properly and calling
>> DBCC SHRINKFILE('File1', EMPTYFILE) to move all the data from one
>> file to the other.
>> However, when I try to remove the original file i get an error message
>> that "The primary data file cannot be removed". After some research, I
>> discovered that the system tables seem to be allocated din the primary
>> data file and that EMPTYFILE does not move these.
>> Is there any way to move system tables to another file in the PRIMARY
>> filegroup making the other file the new primary datafile?
>> /Thomas Kejser
> I don't think you can remove the primary data file. See this thread as it
> mentnions a possible hack. Just test it on a dev server and back up your
> database before trying it in production:
> http://dbforums.com/t429554.html
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
I have a database where the primary filegroup has been allocated very
fragmented. I have tried to defragment my database by adding a new file
to the primary filegroup, allocation space properly and calling DBCC
SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
other.
However, when I try to remove the original file i get an error message
that "The primary data file cannot be removed". After some research, I
discovered that the system tables seem to be allocated din the primary
data file and that EMPTYFILE does not move these.
Is there any way to move system tables to another file in the PRIMARY
filegroup making the other file the new primary datafile?
/Thomas KejserThomas Kejser wrote:
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new
> file to the primary filegroup, allocation space properly and calling
> DBCC SHRINKFILE('File1', EMPTYFILE) to move all the data from one
> file to the other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser
I don't think you can remove the primary data file. See this thread as
it mentnions a possible hack. Just test it on a dev server and back up
your database before trying it in production:
http://dbforums.com/t429554.html
David Gugick
Imceda Software
www.imceda.com|||if you have extent fragmentation then adding another filegroup will not
necessarilly resolve your problem. you need to
1) drop the additional file (run dbcc shrinkfile with the emptyfile option
and delete secondary file)
2) run dbcc checkdb with repair_rebuild to repair allocation errors and
rebuild indexes.(this will fix extent fragmentation currently experienced)
3) if this is resolved but you still feel that you need to defragment the
MDF file .. .then you can either
- stop sql server
- copy mdf and ldf file off the OS drive
- defragment drive
- copy mdf file back to the OS drive
OR
- use a utility which it's name escapes me at the moment(SQLDefrag?)
- this will allow you to perform task without copying the database files
off the drive(I have not found it as effective as the first option)
HTH
"Thomas Kejser" <thomas@.kejser.org> wrote in message
news:%23bNEQe7IFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new file
> to the primary filegroup, allocation space properly and calling DBCC
> SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
> other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser|||You might want to consider moving your data to another filegroup and use
your primary filegroup just for database metadata.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O48tom7IFHA.656@.TK2MSFTNGP14.phx.gbl...
> Thomas Kejser wrote:
>> Greetings all
>> I have a database where the primary filegroup has been allocated very
>> fragmented. I have tried to defragment my database by adding a new
>> file to the primary filegroup, allocation space properly and calling
>> DBCC SHRINKFILE('File1', EMPTYFILE) to move all the data from one
>> file to the other.
>> However, when I try to remove the original file i get an error message
>> that "The primary data file cannot be removed". After some research, I
>> discovered that the system tables seem to be allocated din the primary
>> data file and that EMPTYFILE does not move these.
>> Is there any way to move system tables to another file in the PRIMARY
>> filegroup making the other file the new primary datafile?
>> /Thomas Kejser
> I don't think you can remove the primary data file. See this thread as it
> mentnions a possible hack. Just test it on a dev server and back up your
> database before trying it in production:
> http://dbforums.com/t429554.html
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
Emptying the primary data file
Greetings all
I have a database where the primary filegroup has been allocated very
fragmented. I have tried to defragment my database by adding a new file
to the primary filegroup, allocation space properly and calling DBCC
SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
other.
However, when I try to remove the original file i get an error message
that "The primary data file cannot be removed". After some research, I
discovered that the system tables seem to be allocated din the primary
data file and that EMPTYFILE does not move these.
Is there any way to move system tables to another file in the PRIMARY
filegroup making the other file the new primary datafile?
/Thomas KejserThomas Kejser wrote:
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new
> file to the primary filegroup, allocation space properly and calling
> DBCC SHRINKFILE('File1', EMPTYFILE) to move all the data from one
> file to the other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser
I don't think you can remove the primary data file. See this thread as
it mentnions a possible hack. Just test it on a dev server and back up
your database before trying it in production:
http://webservertalk.com/t429554.html
David Gugick
Imceda Software
www.imceda.com|||if you have extent fragmentation then adding another filegroup will not
necessarilly resolve your problem. you need to
1) drop the additional file (run dbcc shrinkfile with the emptyfile option
and delete secondary file)
2) run dbcc checkdb with repair_rebuild to repair allocation errors and
rebuild indexes.(this will fix extent fragmentation currently experienced)
3) if this is resolved but you still feel that you need to defragment the
MDF file .. .then you can either
- stop sql server
- copy mdf and ldf file off the OS drive
- defragment drive
- copy mdf file back to the OS drive
OR
- use a utility which it's name escapes me at the moment(SQLDefrag?)
- this will allow you to perform task without copying the database files
off the drive(I have not found it as effective as the first option)
HTH
"Thomas Kejser" <thomas@.kejser.org> wrote in message
news:%23bNEQe7IFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new file
> to the primary filegroup, allocation space properly and calling DBCC
> SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
> other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser|||You might want to consider moving your data to another filegroup and use
your primary filegroup just for database metadata.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O48tom7IFHA.656@.TK2MSFTNGP14.phx.gbl...
> Thomas Kejser wrote:
> I don't think you can remove the primary data file. See this thread as it
> mentnions a possible hack. Just test it on a dev server and back up your
> database before trying it in production:
> http://webservertalk.com/t429554.html
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
I have a database where the primary filegroup has been allocated very
fragmented. I have tried to defragment my database by adding a new file
to the primary filegroup, allocation space properly and calling DBCC
SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
other.
However, when I try to remove the original file i get an error message
that "The primary data file cannot be removed". After some research, I
discovered that the system tables seem to be allocated din the primary
data file and that EMPTYFILE does not move these.
Is there any way to move system tables to another file in the PRIMARY
filegroup making the other file the new primary datafile?
/Thomas KejserThomas Kejser wrote:
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new
> file to the primary filegroup, allocation space properly and calling
> DBCC SHRINKFILE('File1', EMPTYFILE) to move all the data from one
> file to the other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser
I don't think you can remove the primary data file. See this thread as
it mentnions a possible hack. Just test it on a dev server and back up
your database before trying it in production:
http://webservertalk.com/t429554.html
David Gugick
Imceda Software
www.imceda.com|||if you have extent fragmentation then adding another filegroup will not
necessarilly resolve your problem. you need to
1) drop the additional file (run dbcc shrinkfile with the emptyfile option
and delete secondary file)
2) run dbcc checkdb with repair_rebuild to repair allocation errors and
rebuild indexes.(this will fix extent fragmentation currently experienced)
3) if this is resolved but you still feel that you need to defragment the
MDF file .. .then you can either
- stop sql server
- copy mdf and ldf file off the OS drive
- defragment drive
- copy mdf file back to the OS drive
OR
- use a utility which it's name escapes me at the moment(SQLDefrag?)
- this will allow you to perform task without copying the database files
off the drive(I have not found it as effective as the first option)
HTH
"Thomas Kejser" <thomas@.kejser.org> wrote in message
news:%23bNEQe7IFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new file
> to the primary filegroup, allocation space properly and calling DBCC
> SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
> other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser|||You might want to consider moving your data to another filegroup and use
your primary filegroup just for database metadata.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O48tom7IFHA.656@.TK2MSFTNGP14.phx.gbl...
> Thomas Kejser wrote:
> I don't think you can remove the primary data file. See this thread as it
> mentnions a possible hack. Just test it on a dev server and back up your
> database before trying it in production:
> http://webservertalk.com/t429554.html
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
Emptying reportservertempdb
I have a reportservertempdb database that has suddenly grown to 16Gb and has
stayed that way for a few days. How do I clear what might be cached in there
and/or get this database to shrink back down to a reasonable size?John,
We are experiencing exactly the same problem as you described: the
ReportServerTempDB suddenly grow to over 10GB and stay that large.
We recently run large number of reports, more than 5,000 pages, with
images merged in using URL on every page. At first, the transaction
log grow rapidly to over 15GB. We then turned off the caching,
controled the size of the transaction log, and scheduled backup of the
transaction log every hour. Now the size of the tranaction log is
under control, but there is a table named CHUNKDATA in
ReportServerTempDB which stayed to be large (over 11GB). I wonder if
it is save to truncate the CHUNKDATA table. And how to correctly
control the overall size of this database.
If you or anybody found or heard of any solutions to this problem,
please share them with me.
Thanks in advance!
Tommy|||I'm thinking since nothing in that database is important I'm going to drop
and recreate it from a script. I too turned off all caching but it didn't
help.
"Tommy" wrote:
> John,
> We are experiencing exactly the same problem as you described: the
> ReportServerTempDB suddenly grow to over 10GB and stay that large.
> We recently run large number of reports, more than 5,000 pages, with
> images merged in using URL on every page. At first, the transaction
> log grow rapidly to over 15GB. We then turned off the caching,
> controled the size of the transaction log, and scheduled backup of the
> transaction log every hour. Now the size of the tranaction log is
> under control, but there is a table named CHUNKDATA in
> ReportServerTempDB which stayed to be large (over 11GB). I wonder if
> it is save to truncate the CHUNKDATA table. And how to correctly
> control the overall size of this database.
> If you or anybody found or heard of any solutions to this problem,
> please share them with me.
> Thanks in advance!
> Tommy
>|||need to delete snapshots that are in history.
John wrote:
>I'm thinking since nothing in that database is important I'm going to drop
>and recreate it from a script. I too turned off all caching but it didn't
>help.
>"Tommy" wrote:
>> John,
>> We are experiencing exactly the same problem as you described: the
>[quoted text clipped - 16 lines]
>> Tommy
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
stayed that way for a few days. How do I clear what might be cached in there
and/or get this database to shrink back down to a reasonable size?John,
We are experiencing exactly the same problem as you described: the
ReportServerTempDB suddenly grow to over 10GB and stay that large.
We recently run large number of reports, more than 5,000 pages, with
images merged in using URL on every page. At first, the transaction
log grow rapidly to over 15GB. We then turned off the caching,
controled the size of the transaction log, and scheduled backup of the
transaction log every hour. Now the size of the tranaction log is
under control, but there is a table named CHUNKDATA in
ReportServerTempDB which stayed to be large (over 11GB). I wonder if
it is save to truncate the CHUNKDATA table. And how to correctly
control the overall size of this database.
If you or anybody found or heard of any solutions to this problem,
please share them with me.
Thanks in advance!
Tommy|||I'm thinking since nothing in that database is important I'm going to drop
and recreate it from a script. I too turned off all caching but it didn't
help.
"Tommy" wrote:
> John,
> We are experiencing exactly the same problem as you described: the
> ReportServerTempDB suddenly grow to over 10GB and stay that large.
> We recently run large number of reports, more than 5,000 pages, with
> images merged in using URL on every page. At first, the transaction
> log grow rapidly to over 15GB. We then turned off the caching,
> controled the size of the transaction log, and scheduled backup of the
> transaction log every hour. Now the size of the tranaction log is
> under control, but there is a table named CHUNKDATA in
> ReportServerTempDB which stayed to be large (over 11GB). I wonder if
> it is save to truncate the CHUNKDATA table. And how to correctly
> control the overall size of this database.
> If you or anybody found or heard of any solutions to this problem,
> please share them with me.
> Thanks in advance!
> Tommy
>|||need to delete snapshots that are in history.
John wrote:
>I'm thinking since nothing in that database is important I'm going to drop
>and recreate it from a script. I too turned off all caching but it didn't
>help.
>"Tommy" wrote:
>> John,
>> We are experiencing exactly the same problem as you described: the
>[quoted text clipped - 16 lines]
>> Tommy
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
Emptying Log file
Backup Log has Active and Inactive Portions. To Truncate Inactive
portion user the following command in SQL Query Analyser
USE The following Command
BACKUP LOG { database_name | @.database_name_var }
WITH TRUNCATE_ONLYThanx Rex
*** Sent via Developersdex http://www.examnotes.net ***
portion user the following command in SQL Query Analyser
USE The following Command
BACKUP LOG { database_name | @.database_name_var }
WITH TRUNCATE_ONLYThanx Rex
*** Sent via Developersdex http://www.examnotes.net ***
Emptying Log file
I have a database with mdf size 10 gb and ldf size 8 gb. i want to
reduce the size of ldf file how can i do it.
Regards,
Farid.
*** Sent via Developersdex http://www.examnotes.net ***You can shrink transaction log size.
1. DBCC ShrinkDatabase
2. Dbcc ShrinkFile --log
3. Auto shrink option
"Ghulam Farid"?? ??? ??:
>
> I have a database with mdf size 10 gb and ldf size 8 gb. i want to
> reduce the size of ldf file how can i do it.
> Regards,
> Farid.
> *** Sent via Developersdex http://www.examnotes.net ***
>
emptying database
Hi friend ! is there anyway to erase the whole content of a database
other than executing the script."Phung" <purwa@.purwadhika.comwrote in message
news:1193056060.280220.235250@.e9g2000prf.googlegro ups.com...
You could drop and recreate the DB.
other than executing the script."Phung" <purwa@.purwadhika.comwrote in message
news:1193056060.280220.235250@.e9g2000prf.googlegro ups.com...
Quote:
Originally Posted by
Hi friend ! is there anyway to erase the whole content of a database
other than executing the script.
>
You could drop and recreate the DB.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||If you are dropping and recreating , don't forget to take a script backup of
the db
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Phung" <purwa@.purwadhika.comwrote in message
news:1193056060.280220.235250@.e9g2000prf.googlegro ups.com...
Quote:
Originally Posted by
Hi friend ! is there anyway to erase the whole content of a database
other than executing the script.
>
emptying data file and removing it
Using SS2000. File 1 was 6G and file 2 was 3.3G. Using dbcc shrinkfile
(file2id,emptyfile) I shrank file 2. After shrinking, file 1 was 7.1G but
file 2 was still 3.3G. I shrank the whole database in EM but the file sizes
were the same.
Is it safe to remove file 2 with alter database dns remove file
E:\sql\data\dns_data.mdf or is something wrong?
Thanks,
Dan D.
Yes, it is safe to remove file 2 using ALTER DATABASE REMOVE FILE. If the
file is not empty, ALTER DATABASE command will fail and you will get an
error.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:381F2480-0556-40D2-9C2B-26F4694864D0@.microsoft.com...
> Using SS2000. File 1 was 6G and file 2 was 3.3G. Using dbcc shrinkfile
> (file2id,emptyfile) I shrank file 2. After shrinking, file 1 was 7.1G but
> file 2 was still 3.3G. I shrank the whole database in EM but the file
sizes
> were the same.
> Is it safe to remove file 2 with alter database dns remove file
> E:\sql\data\dns_data.mdf or is something wrong?
> Thanks,
> --
> Dan D.
|||We used the database yesterday and all of the data seemed to be there. We
also added data and only file1 changed size. The size of the file I emptied
was unchanged.
Thanks,
Dan D.
"Stephen Yuan Jiang [MSFT]" wrote:
> Yes, it is safe to remove file 2 using ALTER DATABASE REMOVE FILE. If the
> file is not empty, ALTER DATABASE command will fail and you will get an
> error.
> --
> Stephen Jiang
> Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:381F2480-0556-40D2-9C2B-26F4694864D0@.microsoft.com...
> sizes
>
>
(file2id,emptyfile) I shrank file 2. After shrinking, file 1 was 7.1G but
file 2 was still 3.3G. I shrank the whole database in EM but the file sizes
were the same.
Is it safe to remove file 2 with alter database dns remove file
E:\sql\data\dns_data.mdf or is something wrong?
Thanks,
Dan D.
Yes, it is safe to remove file 2 using ALTER DATABASE REMOVE FILE. If the
file is not empty, ALTER DATABASE command will fail and you will get an
error.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:381F2480-0556-40D2-9C2B-26F4694864D0@.microsoft.com...
> Using SS2000. File 1 was 6G and file 2 was 3.3G. Using dbcc shrinkfile
> (file2id,emptyfile) I shrank file 2. After shrinking, file 1 was 7.1G but
> file 2 was still 3.3G. I shrank the whole database in EM but the file
sizes
> were the same.
> Is it safe to remove file 2 with alter database dns remove file
> E:\sql\data\dns_data.mdf or is something wrong?
> Thanks,
> --
> Dan D.
|||We used the database yesterday and all of the data seemed to be there. We
also added data and only file1 changed size. The size of the file I emptied
was unchanged.
Thanks,
Dan D.
"Stephen Yuan Jiang [MSFT]" wrote:
> Yes, it is safe to remove file 2 using ALTER DATABASE REMOVE FILE. If the
> file is not empty, ALTER DATABASE command will fail and you will get an
> error.
> --
> Stephen Jiang
> Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:381F2480-0556-40D2-9C2B-26F4694864D0@.microsoft.com...
> sizes
>
>
Subscribe to:
Posts (Atom)