Basic Analysis Server questions

1) Can an AS database be created from multiple data sources .. i.e. can we
create cubes with tables( dimensions) that may belong on different data
sources ?
2) What kind of storage mode do I currently have ? How do I check ? I know
there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
out from AS Manager how the cube was created ?
3) I believe the default is MOLAP.. And where are the cubes physically
stored ? How can I find the path ?> 1) Can an AS database be created from multiple data sources .. i.e. can we
> create cubes with tables( dimensions) that may belong on different data
> sources ?
With SQL Server 2000, No.
Your best bet is to create views in one data source which reference
structures in the other.
We have a better story coming in SQL Server 2005.

> 2) What kind of storage mode do I currently have ? How do I check ? I know
> there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
> out from AS Manager how the cube was created ?
If you edit the partition, Analysis Manager tells you storage mode the
partition is in.

> 3) I believe the default is MOLAP.. And where are the cubes physically
> stored ? How can I find the path ?
In SQL Server 2000, everything is stored off the data path.
Typically <install folder>\Data, e.g. C:\Program Files\Microsoft Analysis
Services\Data
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ubJ6mBQMFHA.1172@.TK2MSFTNGP12.phx.gbl...
> 1) Can an AS database be created from multiple data sources .. i.e. can we
> create cubes with tables( dimensions) that may belong on different data
> sources ?
> 2) What kind of storage mode do I currently have ? How do I check ? I know
> there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
> out from AS Manager how the cube was created ?
> 3) I believe the default is MOLAP.. And where are the cubes physically
> stored ? How can I find the path ?
>
>

Basic Analysis Server questions

1) Can an AS database be created from multiple data sources .. i.e. can we
create cubes with tables( dimensions) that may belong on different data
sources ?
2) What kind of storage mode do I currently have ? How do I check ? I know
there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
out from AS Manager how the cube was created ?
3) I believe the default is MOLAP.. And where are the cubes physically
stored ? How can I find the path ?
> 1) Can an AS database be created from multiple data sources .. i.e. can we
> create cubes with tables( dimensions) that may belong on different data
> sources ?
With SQL Server 2000, No.
Your best bet is to create views in one data source which reference
structures in the other.
We have a better story coming in SQL Server 2005.

> 2) What kind of storage mode do I currently have ? How do I check ? I know
> there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
> out from AS Manager how the cube was created ?
If you edit the partition, Analysis Manager tells you storage mode the
partition is in.

> 3) I believe the default is MOLAP.. And where are the cubes physically
> stored ? How can I find the path ?
In SQL Server 2000, everything is stored off the data path.
Typically <install folder>\Data, e.g. C:\Program Files\Microsoft Analysis
Services\Data
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ubJ6mBQMFHA.1172@.TK2MSFTNGP12.phx.gbl...
> 1) Can an AS database be created from multiple data sources .. i.e. can we
> create cubes with tables( dimensions) that may belong on different data
> sources ?
> 2) What kind of storage mode do I currently have ? How do I check ? I know
> there are a few types such as ROLAP,MOLAP,HOLAP. Is there a way I can find
> out from AS Manager how the cube was created ?
> 3) I believe the default is MOLAP.. And where are the cubes physically
> stored ? How can I find the path ?
>
>

Bar charts

I have created a bar graph but cannot find a way to change the bar's color for different series.

I have a graph which has sales men and their sales between dates

Thanks for your help

Hi there,

There are lots of postings for this. Can you try this one?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=222204&SiteID=1

cheers,

Andrew

|||Have you set up a series group?|||

No I hadn't

Thanks for your help

|||

The only problem Im getting with setting it up as a series group is the chart lines are now the size of a match.

Is it possible to fatten them up?

sql

Bar Chart problem

I have created this report using a chart where I specify a series group as well as a category group. It works fine. The problem is the bar chart is displaying funny, the bars are REALLY thin. How can I make the bars thicker?

make sure that The 'Side margins' is not cheacked in X Axis and Y Axis tab

sql

Bad executed Plan and wrong Result by SQL

I have one query that executes many times in a week.
I created one Maintenances plan that Rebuild all index in my Database that
has been executed at 23:40 Saturday until stop finished at Sunday.

However at middle of week (Wednesday or Thursday), that query dont return
result like that must be. The time exceeded and the result are total wrong.

I compare the normal executed plan and the crazy one that SQL create to
mount result.

The normal is nested with index seek (very fast, the wrong is Merger with
hash aggregate (very slow). After Index Rebuild, the executed plan bring
result that must be, but when the merge plan are executed with many updates
on that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of week, the
result are total wrong, with many rows back.

I recommended Index Seek force by coalesce function on one column
aggregate, but everyone here were very panic with that behavior of SQL
Server.

Please , anyone help me to explain that!

Krisnamourt!

P.S: Attachments :

--Force Index Query with coalesce
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=coalesce(G.HANDLE,G.HANDLE) AND E.CLASSEGERENCIALPAGTO is NULL

--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL

--
Message posted via http://www.sqlmonster.comStmtText
-----------------------
-----------------------
------------
--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
option(merge join)

(1 row(s) affected)

StmtText
-----------------------
-----------------------
----------
|--Compute Scalar(DEFINE:([Expr1002]=Convert([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM([partialagg1003])))
|--Parallelism(Gather Streams)
|--Merge Join(Inner Join, MERGE:([G].[HANDLE])=([E].[GUIA])
, RESIDUAL:([G].[HANDLE]=[E].[GUIA]))
|--Parallelism(Distribute Streams, PARTITION COLUMNS:
([G].[HANDLE]))
| |--Index Seek(OBJECT:([Saude].[dbo].[SAM_GUIA].
[AX_1603PEG] AS [G]), SEEK:([G].[PEG]=736740) ORDERED FORWARD)
|--Sort(ORDER BY:([E].[GUIA] ASC))
|--Hash Match(Aggregate, HASH:([E].[GUIA]),
RESIDUAL:([E].[GUIA]=[E].[GUIA]) DEFINE:([partialagg1003]=COUNT(*)))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([E].[GUIA]))
|--Clustered Index Scan(OBJECT:([Saude]
..[dbo].[SAM_GUIA_EVENTOS].[PK__SAM_GUIA_EVENTOS__68736660] AS [E]), WHERE:(
[E].[CLASSEGERENCIALPAGTO]=NULL))

(10 row(s) affected)

--
Message posted via http://www.sqlmonster.com|||I Mean...the wrong result bring back many row with E.CLASSEGERENCIALPAGTO
not null(this column shows many data )...CRAZY!!!

Anyone help me to explain that!!

Kris

--
Message posted via http://www.sqlmonster.com|||Krisnamourt Correia via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I have one query that executes many times in a week.
> I created one Maintenances plan that Rebuild all index in my Database that
> has been executed at 23:40 Saturday until stop finished at Sunday.
> However at middle of week (Wednesday or Thursday), that query don't
> return result like that must be. The time exceeded and the result are
> total wrong.
> I compare the normal executed plan and the "crazy" one that SQL create to
> mount result.
> The normal is nested with index seek (very fast, the wrong is Merger
> with hash aggregate (very slow). After Index Rebuild, the executed plan
> bring result that must be, but when the merge plan are executed with
> many updates on that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of
> week, the result are total wrong, with many rows back.
> I recommended Index Seek force by coalesce function on one column
> aggregate, but everyone here were very panic with that behavior of SQL
> Server.

Do I understand you clarifiation in the other article correctly, that
when you say "results are total wrong", you do in fact mean the query
plan? If you really get incorrect resuls from the query, this is a
serious bug, and you should definitely open a case with Microsoft to
have it investigate.

If the problem is "only" the incorrect query plan, and the slow execution
time, this is more "normal" behaviour.

Recall that SQL Server uses a cost-based optimizer that estimates the
cost of various query plans from statistics about the data. A small
error in the estimate can have serious consequences.

Since you have good performance after index rebuild, it might be a good
idea to schedule index rebuild on these two tables daily.

I also notice that the bad plan involves parallelism. If you add
OPTION (MAXDOP 1), you tell SQL Server not to use parallelism. This
is often enough to get a good plan.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The real problem is incorret result. I cant rebuild index on these two
table , because our scenario works 24 hours by day. These table are too big
(17 Gbytes one and 4 Gbytes other), with many Index. The Index Rebuild only
can do at weekends. I intend to eliminated some Index that are redundant(I
just begun), but that bug is very crazy. That became SQL Server not a good
solution for OLTP that grows up strongly. I saw many scenarios like
that...bad performance when the Database became too large.

--
Message posted via http://www.sqlmonster.com|||Krisnamourt Correia via SQLMonster.com (forum@.SQLMonster.com) writes:
> The real problem is incorret result. I cant rebuild index on these two
> table , because our scenario works 24 hours by day. These table are too
> big (17 Gbytes one and 4 Gbytes other), with many Index. The Index
> Rebuild only can do at weekends. I intend to eliminated some Index that
> are redundant(I just begun), but that bug is very crazy. That became SQL
> Server not a good solution for OLTP that grows up strongly. I saw many
> scenarios like that...bad performance when the Database became too
> large.

Looking at your query, the incorrect results may be a known issue.
I think I recognize the type of query. I would suggest that you open
a case with Microsoft to investigate this.

If there is a fix, it is likely to be available in SP4 which was recently.
Unfortunate there is an issue which concerns AWE which I would expect to
concern you, given your table sizes. I would expect Microsoft to have a fix
for this issue soon, though. See further
http://www.microsoft.com/sql/downloads/2000/sp4.asp.

Note that SP4 is only likely to address the incorrect result. The query
plan and the fragmentation is less likely to improve.

Some questions:
o Do you have autostats enabled on these tables? (Maybe you should turn
them off)
o What actual fragmentation do you have by the middle of the week?

If the fragmentation increases rapidly, maybe you should look at changing
the clustered index to one that is less prone to fragmentation given
the update pattern. But here is of course a tradeoff with queries
that may depend on the clustered index.

Could you post the CREATE TABLE and CREATE INDEX statements for the
two tables?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

backupset table info

My employer wants me to extract information for each nightly database backup
s
for each production server.
So I have created a DTS package that extracts this info into excel using the
following script:
use msdb
go
select database_name as dbname,
convert(char(20),backup_start_date,100) as start_time,
convert(char(20),backup_finish_date,100)
as end_time, backup_size from
backupset
where backup_start_date >
(SELECT MAX(backup_start_date) FROM backupset) - 1
order by database_name, backup_start_date
Which gives the following output:
dbname start_time end_time backup_size
db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
He now wants two additional columns in the excel sheet. One with yesterday's
backup_size and 1 w ago's backup size.
Can anyone tell me how to modify this sql script to be able to retieve this
additional info from the backupset table?
Thanks,
HillaireLook at sysbackuphistory system table.
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> My employer wants me to extract information for each nightly database
backups
> for each production server.
> So I have created a DTS package that extracts this info into excel using
the
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> DB2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with
yesterday's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve
this
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||Try,
select
a.database_name as dbname,
convert(char(20),a.backup_start_date,100) as start_time,
convert(char(20),a.backup_finish_date,100) as end_time,
a.backup_size,
b.backup_size
from
msdb.dbo.backupset as a
left join
msdb.dbo.backupset as b
on
a.database_name = b.database_name
and a.backup_start_date > (SELECT MAX(backup_start_date) FROM
msdb.dbo.backupset) - 1
and b.backup_start_date = dateadd(w, -1, (SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset) - 1)
order by
a.database_name,
a.backup_start_date
AMB
"gracie" wrote:

> My employer wants me to extract information for each nightly database back
ups
> for each production server.
> So I have created a DTS package that extracts this info into excel using t
he
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with yesterday
's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve thi
s
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||What I actually need to accomplish is a comparison on the same excel sheet
showing yesterday's backup size and 1 w ago's backup size, side by side s
o
that he can look for any large decrepancies in the sizes.
"Uri Dimant" wrote:

> Look at sysbackuphistory system table.
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> backups
> the
> yesterday's
> this
>
>

Backups Not Deleting

We're running SQL Server 2000 service pack 4. I've created a Maintenance
Plan that backs up all user databases, each to its own subfolder, every
night. The plan is supposed to delete old copies as well which it is not
doing. If I change the plan to backup only 1 database it works (old copies
deleted). When I switch it to all user databases, no more deletes. And the
sqlmaint log file does not even show that any deletes were attempted. Note
that we do have some databases offline, so those are skipped, of course, and
the job does fail.
Please help! What's the secret to get the older files to delete?
Here's the Step on the SQL Agent job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
21EA0A98-9812-4AEA-8BC0-08AEAEAB24EC -Rpt "c:\temp\DB Maintenance
Plan14.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpDB "D:\Backup" -DelBkUps 1DAYS -CrBkSubDir -BkExt "BAK"'
Thanks,
Krip
>I believe that deletion is done *at the end* of the job, so if anything
>fails, it bails out and no deletion is performed. You can try by first
>including only database so the whole job succeeds.
Tibor,
Thanks for the tip! I detached all our offline databases, and job finishes
without error, and deletes the old backups. Beautiful.
-Krip
|||I have also seen this behavior when the plan includes both Full and Simple
Recovery model databases.
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Krip" <amk@.kynetix.com> wrote in message
news:7A2C4350-5AED-4F70-A22A-47E64ED68DFD@.microsoft.com...
> Tibor,
> Thanks for the tip! I detached all our offline databases, and job
> finishes without error, and deletes the old backups. Beautiful.
> -Krip
>

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?
How do you run these jobs, and what types are they of. Maint Wiz? SQL Server Agent jobs, TSQL or
CmdExec? Etc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transaction
> logs. 2 out of the 3 databases are being backed up and the transaction logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?
|||Tibor Karaszi wrote:

> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.
|||You may need to look at both the job history (make sure to view step
details) and the MP history.
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?
|||In addition to Andrew's answer, I suggest you specify a report file in Main Wiz and look for error
messages in there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>
> They were created using the Maintenance Wizard and scheduled to run nightly.

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?How do you run these jobs, and what types are they of. Maint Wiz? SQL Server Agent jobs, TSQL or
CmdExec? Etc...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transaction
> logs. 2 out of the 3 databases are being backed up and the transaction logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||Tibor Karaszi wrote:
> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.|||You may need to look at both the job history (make sure to view step
details) and the MP history.
--
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||In addition to Andrew's answer, I suggest you specify a report file in Main Wiz and look for error
messages in there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How do you run these jobs, and what types are they of. Maint Wiz? SQL
>> Server Agent jobs, TSQL or CmdExec? Etc...
>
> They were created using the Maintenance Wizard and scheduled to run nightly.

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?How do you run these jobs, and what types are they of. Maint Wiz? SQL Server
Agent jobs, TSQL or
CmdExec? Etc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transactio
n
> logs. 2 out of the 3 databases are being backed up and the transaction log
s
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||Tibor Karaszi wrote:

> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.|||You may need to look at both the job history (make sure to view step
details) and the MP history.
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||In addition to Andrew's answer, I suggest you specify a report file in Main
Wiz and look for error
messages in there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Tibor Karaszi wrote:
>
>
> They were created using the Maintenance Wizard and scheduled to run nightly.[/vbco
l]

Backups

I have created a backup schedule for all databases on a
SQL server. I have Created a Backup device for each db.
and a single stored procedure that is called with database
name, device & retain days passed as parameters. All
Devices are network locations. I didn't want these backups
to swallow the entire disk space so i set the retain days
to 14. After 2 weeks i hoped that each backup would have
been overwriten. Well that was my logic. 2 weeks are up
and the files created are still growing. After some
investigation it is apparent that the expiry dates have
been passed but the backups sets have not been
overwritten. So much for my plan. I have now re-read BOL
and realised that i have got my wires crossed, the entire
media is overwriten when the expiry dates of all backups
within have been reached. To me this is topsy turvy, why
would i wish to overwrite an entire backup file, maybe if
i had taken a back up of the backup then i would wish to
delete it. Have i yet again misunderstood BOL. What i want
to do is maintain a dynamic history of backups. I back up
my database to a device, this backup lasts for 2 weeks and
then is overwriten. so the actual physical file is never
deleted, only the contents within when the expiry date is
reached.
Help!!!!!!!Expiredays and retaindays are only there to not allow you to overwrite using
the INIT before a certain day. If you aren't using INIT or if you are using
NOINIT, it will always be append. And, it is all or nothing.
If you want generation handling, either use the Maint Wizard, a 3:rd party
like www.dbmaint.com or some TSQL programming to handle this (using more
than one backup device).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
> I have created a backup schedule for all databases on a
> SQL server. I have Created a Backup device for each db.
> and a single stored procedure that is called with database
> name, device & retain days passed as parameters. All
> Devices are network locations. I didn't want these backups
> to swallow the entire disk space so i set the retain days
> to 14. After 2 weeks i hoped that each backup would have
> been overwriten. Well that was my logic. 2 weeks are up
> and the files created are still growing. After some
> investigation it is apparent that the expiry dates have
> been passed but the backups sets have not been
> overwritten. So much for my plan. I have now re-read BOL
> and realised that i have got my wires crossed, the entire
> media is overwriten when the expiry dates of all backups
> within have been reached. To me this is topsy turvy, why
> would i wish to overwrite an entire backup file, maybe if
> i had taken a back up of the backup then i would wish to
> delete it. Have i yet again misunderstood BOL. What i want
> to do is maintain a dynamic history of backups. I back up
> my database to a device, this backup lasts for 2 weeks and
> then is overwriten. so the actual physical file is never
> deleted, only the contents within when the expiry date is
> reached.
> Help!!!!!!!
>|||Thanks Tibor
Are there any sys SP's or XP's that can be used to edit
backup files? Maybe i could remove expired files..
I have created a stored procedure that looks at a backup
device and tells me which full, DIff and TL backups need
to be applied to restore to a specified point in time. It
looks as if this will nor work if i have to create new
devices..rats...
I pull my hair out some times with the illogical-ness of
SQL server

>--Original Message--
>Expiredays and retaindays are only there to not allow you
to overwrite using
>the INIT before a certain day. If you aren't using INIT
or if you are using
>NOINIT, it will always be append. And, it is all or
nothing.
>If you want generation handling, either use the Maint
Wizard, a 3:rd party
>like www.dbmaint.com or some TSQL programming to handle
this (using more
>than one backup device).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
database
backups
days
entire
why
if
want
up
and
is
>
>.
>|||There are no tool with which you can remove selective backups inside a
backup file, I'm afraid.
One alternative is to do append, say over one day (assume db backup once per
day and log backup once per hour). Then after one day, you rename the Active
to give it a timestamp (so you have generations) and then do INIT to the
active one. This is how we did it in Db Maint up until the current version,
where we decided to not do append anymore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
> Thanks Tibor
> Are there any sys SP's or XP's that can be used to edit
> backup files? Maybe i could remove expired files..
> I have created a stored procedure that looks at a backup
> device and tells me which full, DIff and TL backups need
> to be applied to restore to a specified point in time. It
> looks as if this will nor work if i have to create new
> devices..rats...
> I pull my hair out some times with the illogical-ness of
> SQL server
>
> to overwrite using
> or if you are using
> nothing.
> Wizard, a 3:rd party
> this (using more
> message
> database
> backups
> days
> entire
> why
> if
> want
> up
> and
> is|||Thanks tibor, thats great. Part of my backup script now
contains code to create a backup device every time it is
run that is named depending on a variable passed.
Alternating every week the physical file names change and
the retain days are set to 7. So as you advised i create a
file and add my backups. After a week i swich to a second
file and use this for a week. After another 7 days i
switch back to the original file that is now ready tbe
overwriten.
My SP that advises me of what backup files to aply now
works in pretty much the same way. It creates a device
based on the parameters and returns the backup history. it
then re-creates the device with the second file name and
apends this to the first run.
Thanks so much, you have been a great help..

>--Original Message--
>There are no tool with which you can remove selective
backups inside a
>backup file, I'm afraid.
>One alternative is to do append, say over one day (assume
db backup once per
>day and log backup once per hour). Then after one day,
you rename the Active
>to give it a timestamp (so you have generations) and then
do INIT to the
>active one. This is how we did it in Db Maint up until
the current version,
>where we decided to not do append anymore.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
It
you
on a
db.
have
are up
have
BOL
backups
maybe
wish to
back
weeks
never
date
>
>.
>|||I'm glad I could help, mat. Just one thing: As far as I can see, retains
days doesn't really buy you anything. I only mention this so you don't read
anything into this parameter which isn't there... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d55d01c40aad$fb535920$a501280a@.phx.gbl...
> Thanks tibor, thats great. Part of my backup script now
> contains code to create a backup device every time it is
> run that is named depending on a variable passed.
> Alternating every week the physical file names change and
> the retain days are set to 7. So as you advised i create a
> file and add my backups. After a week i swich to a second
> file and use this for a week. After another 7 days i
> switch back to the original file that is now ready tbe
> overwriten.
> My SP that advises me of what backup files to aply now
> works in pretty much the same way. It creates a device
> based on the parameters and returns the backup history. it
> then re-creates the device with the second file name and
> apends this to the first run.
> Thanks so much, you have been a great help..
>
> backups inside a
> db backup once per
> you rename the Active
> do INIT to the
> the current version,
> message
> It
> you
> on a
> db.
> have
> are up
> have
> BOL
> backups
> maybe
> wish to
> back
> weeks
> never
> date

backupRestore progress bar

Hi,
My app uses MSDE and I have created a UI for user which has buttons to backup and restore the database. Everything works fine but I want to give a visual display to user about the progress of the operation. You know like a progress bar indicating how mu
ch work is done.. so the question is:
Is there a way to find out how much time backup/restore will take.. does sql server provide any such event to us telling this information. I just want to trap this event and give the progress indication to my loyal users.
Thanks all.
dev
Thanks Andrea for the reply. I am using T-SQL commands right now to backup and restore database. If I use sql-dmo to trap progress event then would that mean that I will have to change the backup/restore sourcecode also to use sql-dmo. Please confirm.
Thanks
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:8CD41CBC-6EF7-46F8-BC3C-3415F14A6BD5@.microsoft.com...
> Thanks Andrea for the reply. I am using T-SQL commands right now to
backup
>and restore database. If I use sql-dmo to trap progress event then would
that
>mean that I will have to change the backup/restore sourcecode also to use
>sql-dmo. Please confirm.
yep.. you have to use the SQL-DMO backup object to trap the raised events...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||oh no.. so what is this sql-dmo. What is the purpose of it's existance. Why do we need it (besides for the progress bar). How is it different from programming with T-SQL. How can we choose which way to go, T-SQL or SQL-DMO. What are it's advantages an
d disadvantages.
Thanks
|||Also was it unwise of me to opt for T-SQL for doing everything instead of going for DMO. And is it ok to use DMO for backup/restore and use T-SQL for everything else. What are your recommendations.
We are as before an ISV, app will be deployed using MSDE, will have max a couple of databases. Will provide an interface to the user to backup and restore database. Will creare database from app and do version checks for database.
What should we do... or should we wait for SMO..
Thanks for your valuable time.
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:07DA4684-529D-48D5-9D6A-56DDFDB077A7@.microsoft.com...
> oh no.. so what is this sql-dmo. What is the purpose of it's existance.
Why do we need it
>(besides for the progress bar). How is it different from programming with
T-SQL. How can
> we choose which way to go, T-SQL or SQL-DMO. What are it's advantages and
disadvantages.
> Thanks
SQL-DMO is an acronym for SQL Distributed Management Object and is a full
object model to manage and administer SQL Server..
it exposes a nice object model you can navigate to perform quiet all
management tasks on SQL Server..
it's not designed for data manipulation even if it provides some features
to.
it comes with MSDE and/or can be installed from the Client Tools
installation package of SQL Server.
it is not provided as a separate download and/or package, so you have to
depoly it yourself in MSDE scenarios (you are legitimate to) ... this can be
count as a disadvantage too :-)
it's a little buggy =;-) and eats a lot of memory, but is very handy for
some admin scenario...
Transact-SQL, on the contrary, is the language SQL Server better understand,
and can perform both data manipulation and administration tasks... it's a
separate language where DMO is just a COM object model you can use in any
COM compliant client, so they can not be compared... you shoul'd stick with
Transact-SQL, and use DMO where and when appropriated...
by the way... there's only 1 book worth reading about SQL-DMO, by SQL Server
MVPs Allan Mitchell and Mark Allison,
http://www.compman.co.uk/cgi-win/browse.exe?ref=552118 , which I personally
recommend reading...
FWIW, you can have a look at a free prj of mine at the link following my
sign to have an idea aboout what you can do with SQL-DMO, a prj that
provides a user interface similar to Enteprise Manager written in VB6
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:AF907AC5-9610-41F9-95DD-FC68828844F6@.microsoft.com...
> Also was it unwise of me to opt for T-SQL for doing everything instead of
going for DMO.
>And is it ok to use DMO for backup/restore and use T-SQL for everything
else. What are your
>recommendations.
> We are as before an ISV, app will be deployed using MSDE, will have max a
couple of
>databases. Will provide an interface to the user to backup and restore
database.
>Will creare database from app and do version checks for database.
> What should we do... or should we wait for SMO..
if you only need DMO for presenting a progress bar indicating backup
progress, let it be...
you have to talk to SQL Server via Transact-SQL...
perform backup via ADO.Net commands...
SMO will be only available wit SQL Server 2005, and anyway I don't think it
shoul'd be used for traditional programming, but for the same things now
covered by DMO...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea for the insight. I already use your tool since quiet some time and appreciate it. So do you recommend I use T-SQL for everything as now, you know db creation, data manipulation and use dmo for backup/restore which I can later port to use s
mo when yukon arrives..
dev
|||I am sorry Andrea but I didn't understand what you said here:
"if you only need DMO for presenting a progress bar indicating backup
progress, let it be...
you have to talk to SQL Server via Transact-SQL...
perform backup via ADO.Net commands..."
Do you mean that I use T-SQL for everything else and just use DMO for the backup/restore.
Sorry to bother you like this but Thanks
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:778938C6-1142-4954-B633-BBF0669342D3@.microsoft.com...
> I am sorry Andrea but I didn't understand what you said here:
> "if you only need DMO for presenting a progress bar indicating backup
> progress, let it be...
> you have to talk to SQL Server via Transact-SQL...
> perform backup via ADO.Net commands..."
> Do you mean that I use T-SQL for everything else and just use DMO for the
backup/restore.
> Sorry to bother you like this but Thanks
please excuse my poor english...
what I mean is go with Transact-SQL for all you stuffs, including
Backup/Restore, if you only need SQL-DMO for providing a progress
indicator...
if you need SQL-DMO for other things, then you can consider adding it to
your project references [and setup package :-( ]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backup/Restore SP2 to SP3?

Can I take a Backup created on a server running SQL Server
2000 (Service Pack 2) and Restore it on a server running
SQL Server 2000 (Service Pack 3)? (Without problems, of
course).
I want to not only restore User DBs, but also System DBs
sush as Master, Model, and msdb.
ThanksSystem databases, no. YOu cannot restore a previous
version (i.e. SP2) under SP3.
For user dbs, sure.
>--Original Message--
>Can I take a Backup created on a server running SQL
Server
>2000 (Service Pack 2) and Restore it on a server running
>SQL Server 2000 (Service Pack 3)? (Without problems, of
>course).
>I want to not only restore User DBs, but also System DBs
>sush as Master, Model, and msdb.
>Thanks
>.
>|||Allan
Does this mean that I can restore a user database saved on SP2 into SP3?
Ron Lounsbury
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:561601c3b2c4$5b6ed470$a601280a@.phx.gbl...
> System databases, no. YOu cannot restore a previous
> version (i.e. SP2) under SP3.
> For user dbs, sure.
> >--Original Message--
> >Can I take a Backup created on a server running SQL
> Server
> >2000 (Service Pack 2) and Restore it on a server running
> >SQL Server 2000 (Service Pack 3)? (Without problems, of
> >course).
> >
> >I want to not only restore User DBs, but also System DBs
> >sush as Master, Model, and msdb.
> >
> >Thanks
> >
> >.
> >|||Yes, of course, you can. I do that all the time. Imagine
if you couldn't restore an SP2 user database to an SP3
instance, you would have to upgrade your Dev and prod
environments at the same time if you want to copy any
databases from the prod to the Dev for whatever purposes.
Linchi
>--Original Message--
>Allan
> Does this mean that I can restore a user database
saved on SP2 into SP3?
>Ron Lounsbury
>
>"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
>news:561601c3b2c4$5b6ed470$a601280a@.phx.gbl...
>> System databases, no. YOu cannot restore a previous
>> version (i.e. SP2) under SP3.
>> For user dbs, sure.
>> >--Original Message--
>> >Can I take a Backup created on a server running SQL
>> Server
>> >2000 (Service Pack 2) and Restore it on a server
running
>> >SQL Server 2000 (Service Pack 3)? (Without problems, of
>> >course).
>> >
>> >I want to not only restore User DBs, but also System
DBs
>> >sush as Master, Model, and msdb.
>> >
>> >Thanks
>> >
>> >.
>> >
>
>.
>

Backup/restore

We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...
That's not the way to do it. First, use DBCC SHRINKFILE on the larger file.
Next use ALTER DATABASE to increase the size of the same file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...
|||The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...

>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>
|||OK, let's say that You have 2 files - File1 and File2 - both in the same
filegroup. File1 has all of your data and is 10 GB. (Assume File1 is
full.) File2 is also 10 GB and has no data. Run DBCC SHRINKFILE (File1,
5210). What should happen is 5GB moves from File1 to File2, since they are
both in the same filegroup. Now, FIle1 is 5GB and full, while File2 is 10GB
and half-empty. At this point, you can shrink File2 and have two 5GB files
or you can expand File1 to, say, 10 GB and have 2 equally empty files.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<fkutz@.surebridge.com> wrote in message
news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...

>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>
|||Cool!
Thank you *very* much. That makes much more sense.
Fred...

>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>on the larger file.
>file.
-
>--
>.
>
|||Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...

>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>on the larger file.
>file.
-
>--
>.
>
|||I'll check into that. Could you please confirm that neither of these files
is the primary file and that the filegroup is not the primary filegroup? If
we're not talking about the primary file/filegroup, then I have an
alternative.
Let's say that you have a filegroup that has the one 30GB file - File1.
Now, add two more files to the filegroup - File1 and File2 - each 15GB in
size. Here's the clincher. Now run:
DBCC SHRINKFILE (File1, EMPTYFILE)
This will force the move and then disallow any further addition of data to
File1. Now run:
ALTER DATABASE MyDB
REMOVE FILE File1
At this point, you can rename File3 to File1:
ALTER DATABASE MyDB
MODIFY FILE (NAME = File3, NEWNAME = File1)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...

>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>on the larger file.
>file.
-
>--
>.
>
|||It's a good practice to use the primary file and filegroup just for system
objects. What you're now left with is to move data from your primary
filegroup to your application filegroup. This means rebuilding indexes:
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\temp\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\temp\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\temp\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\temp\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
go
use Sales
go
create table t
(
id int constraint PK_t primary key
)
go
insert t
select id
from Northwind..sysobjects
go
create unique clustered index PK_t on t (id) with drop_existing on
SalesGroup1
go
sp_help t
go
use master
go
drop database Sales
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:578601c42d30$82c6e710$a301280a@.phx.gbl...
Unfortunately they are the primaries. The big one is the
primary file on the primary group. Till now, we've just
been running with a single large file. I had thought of
your alternative as well, but I wasn't sure if it could
be done with the primary file. Though the help files
seem to imply it could. Right now I'm just playing with
a test db, so I can do anything with no worries.
Fred...

>--Original Message--
>I'll check into that. Could you please confirm that
neither of these files
>is the primary file and that the filegroup is not the
primary filegroup? If
>we're not talking about the primary file/filegroup, then
I have an
>alternative.
>Let's say that you have a filegroup that has the one
30GB file - File1.
>Now, add two more files to the filegroup - File1 and
File2 - each 15GB in
>size. Here's the clincher. Now run:
>DBCC SHRINKFILE (File1, EMPTYFILE)
>This will force the move and then disallow any further
addition of data to
>File1. Now run:
>ALTER DATABASE MyDB
>REMOVE FILE File1
>At this point, you can rename File3 to File1:
>ALTER DATABASE MyDB
>MODIFY FILE (NAME = File3, NEWNAME = File1)
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>Hey Tom, I tried your suggestion, and it's not working.
>Here is the command I'm using and the results:
>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>11 1 3925712 128 3925648 3925648
>(1 row(s) affected)
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>No data is moving, and the file is not shrinking. My
>second file, in the same file group is set to 15360MB.
>Am I missing something?
>Fred...
>both in the same
>(Assume File1 is
>SHRINKFILE (File1,
>File2, since they are
>while File2 is 10GB
>have two 5GB files
>equally empty files.
-[vbcol=seagreen]
>--
it[vbcol=seagreen]
SHRINKFILE[vbcol=seagreen]
same[vbcol=seagreen]
-[vbcol=seagreen]
>-
break
>.
>
|||In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).

>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>neither of these files
>primary filegroup? If
>I have an
>30GB file - File1.
>File2 - each 15GB in
>addition of data to
-[vbcol=seagreen]
>--
3925648[vbcol=seagreen]
messages,[vbcol=seagreen]
full,[vbcol=seagreen]
and[vbcol=seagreen]
-[vbcol=seagreen]
>-
>it
into[vbcol=seagreen]
>SHRINKFILE
>same
-[vbcol=seagreen]
>-
>break
file,
>.
>
|||When I built the original table t, it was in the default filegroup. Since I
did not explicitly change the default filegroup when I built the DB, it was
the primary filegroup. I implicitly created a clustered index by adding a
primary key to the CREATE TABLE script. If you ran sp_help right after that
table was built, you'd see that the table existed in the primary filegroup.
When I re-created the index, using the drop_existing option, I explicitly
created it on SalesGroup1 by adding "on SalesGroup1". This forces the
clustered index - and hence, the data - to be moved to the new filegroup.
As for doing this on an entire database, you can use EM to script out your
tables, including indexes. Throw away the table stuff and keep the index
scripts. Change all references from the primary filegroup to the new
filegroup. For primary keys, the following will generate your script:
create function CreateIndexScript
(
@.Table sysname
, @.FileGroup sysname
)
returns varchar (8000)
as
begin
declare
@.str varchar (8000)
, @.constraint sysname
, @.IsClustered char (1)
, @.Column sysname
select
@.constraint = CONSTRAINT_NAME
, @.IsClustered = case when indexproperty (object_id (@.Table),
CONSTRAINT_NAME, 'IsClustered') = 1
then 'Y' else 'N' end
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
TABLE_NAME = @.Table
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
select
@.str = 'create unique ' + case when @.IsClustered = 'Y' then 'clustered '
else '' end
+ 'index ' + @.constraint + ' on [' + @.Table + '] ('
declare c cursor fast_forward for
select
COLUMN_NAME
from
information_schema.key_column_usage
where
TABLE_NAME = @.Table
and CONSTRAINT_NAME = @.constraint
order by
ORDINAL_POSITION
open c
fetch c into
@.Column
while @.@.fetch_status = 0
begin
set
@.str = @.str + '[' + @.Column + '], '
fetch c into
@.Column
end
close c
deallocate c
set
@.str = left (@.str, len (@.str) - 1) + ') with drop_existing on [' +
@.FileGroup + ']'
return (@.str)
end
go
select
dbo.CreateIndexScript (TABLE_NAME, 'MyFG')
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
go
drop function CreateIndexScript
You don't need to do anything for your views.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:59bd01c42d3e$a4603fd0$a101280a@.phx.gbl...
In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).

>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>neither of these files
>primary filegroup? If
>I have an
>30GB file - File1.
>File2 - each 15GB in
>addition of data to
-[vbcol=seagreen]
>--
3925648[vbcol=seagreen]
messages,[vbcol=seagreen]
full,[vbcol=seagreen]
and[vbcol=seagreen]
-[vbcol=seagreen]
>-
>it
into[vbcol=seagreen]
>SHRINKFILE
>same
-[vbcol=seagreen]
>-
>break
file,
>.
>

Backup/restore

We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...That's not the way to do it. First, use DBCC SHRINKFILE on the larger file.
Next use ALTER DATABASE to increase the size of the same file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...|||The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...
>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>|||OK, let's say that You have 2 files - File1 and File2 - both in the same
filegroup. File1 has all of your data and is 10 GB. (Assume File1 is
full.) File2 is also 10 GB and has no data. Run DBCC SHRINKFILE (File1,
5210). What should happen is 5GB moves from File1 to File2, since they are
both in the same filegroup. Now, FIle1 is 5GB and full, while File2 is 10GB
and half-empty. At this point, you can shrink File2 and have two 5GB files
or you can expand File1 to, say, 10 GB and have 2 equally empty files.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<fkutz@.surebridge.com> wrote in message
news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...
>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>|||Cool!
Thank you *very* much. That makes much more sense.
Fred...
>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC SHRINKFILE
>on the larger file.
>>Next use ALTER DATABASE to increase the size of the same
>file.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>.
>|||Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...
>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC SHRINKFILE
>on the larger file.
>>Next use ALTER DATABASE to increase the size of the same
>file.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>.
>|||I'll check into that. Could you please confirm that neither of these files
is the primary file and that the filegroup is not the primary filegroup? If
we're not talking about the primary file/filegroup, then I have an
alternative.
Let's say that you have a filegroup that has the one 30GB file - File1.
Now, add two more files to the filegroup - File1 and File2 - each 15GB in
size. Here's the clincher. Now run:
DBCC SHRINKFILE (File1, EMPTYFILE)
This will force the move and then disallow any further addition of data to
File1. Now run:
ALTER DATABASE MyDB
REMOVE FILE File1
At this point, you can rename File3 to File1:
ALTER DATABASE MyDB
MODIFY FILE (NAME = File3, NEWNAME = File1)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...
>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC SHRINKFILE
>on the larger file.
>>Next use ALTER DATABASE to increase the size of the same
>file.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>.
>|||Unfortunately they are the primaries. The big one is the
primary file on the primary group. Till now, we've just
been running with a single large file. I had thought of
your alternative as well, but I wasn't sure if it could
be done with the primary file. Though the help files
seem to imply it could. Right now I'm just playing with
a test db, so I can do anything with no worries.
Fred...
>--Original Message--
>I'll check into that. Could you please confirm that
neither of these files
>is the primary file and that the filegroup is not the
primary filegroup? If
>we're not talking about the primary file/filegroup, then
I have an
>alternative.
>Let's say that you have a filegroup that has the one
30GB file - File1.
>Now, add two more files to the filegroup - File1 and
File2 - each 15GB in
>size. Here's the clincher. Now run:
>DBCC SHRINKFILE (File1, EMPTYFILE)
>This will force the move and then disallow any further
addition of data to
>File1. Now run:
>ALTER DATABASE MyDB
>REMOVE FILE File1
>At this point, you can rename File3 to File1:
>ALTER DATABASE MyDB
>MODIFY FILE (NAME = File3, NEWNAME = File1)
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>Hey Tom, I tried your suggestion, and it's not working.
>Here is the command I'm using and the results:
>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>11 1 3925712 128 3925648 3925648
>(1 row(s) affected)
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>No data is moving, and the file is not shrinking. My
>second file, in the same file group is set to 15360MB.
>Am I missing something?
>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and full,
>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2 and
>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>equally empty files.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
it
>>larger? I need to break this single large datafile into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
same
>>file.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>.
>|||It's a good practice to use the primary file and filegroup just for system
objects. What you're now left with is to move data from your primary
filegroup to your application filegroup. This means rebuilding indexes:
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\temp\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\temp\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\temp\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\temp\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
go
use Sales
go
create table t
(
id int constraint PK_t primary key
)
go
insert t
select id
from Northwind..sysobjects
go
create unique clustered index PK_t on t (id) with drop_existing on
SalesGroup1
go
sp_help t
go
use master
go
drop database Sales
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:578601c42d30$82c6e710$a301280a@.phx.gbl...
Unfortunately they are the primaries. The big one is the
primary file on the primary group. Till now, we've just
been running with a single large file. I had thought of
your alternative as well, but I wasn't sure if it could
be done with the primary file. Though the help files
seem to imply it could. Right now I'm just playing with
a test db, so I can do anything with no worries.
Fred...
>--Original Message--
>I'll check into that. Could you please confirm that
neither of these files
>is the primary file and that the filegroup is not the
primary filegroup? If
>we're not talking about the primary file/filegroup, then
I have an
>alternative.
>Let's say that you have a filegroup that has the one
30GB file - File1.
>Now, add two more files to the filegroup - File1 and
File2 - each 15GB in
>size. Here's the clincher. Now run:
>DBCC SHRINKFILE (File1, EMPTYFILE)
>This will force the move and then disallow any further
addition of data to
>File1. Now run:
>ALTER DATABASE MyDB
>REMOVE FILE File1
>At this point, you can rename File3 to File1:
>ALTER DATABASE MyDB
>MODIFY FILE (NAME = File3, NEWNAME = File1)
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>Hey Tom, I tried your suggestion, and it's not working.
>Here is the command I'm using and the results:
>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>11 1 3925712 128 3925648 3925648
>(1 row(s) affected)
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>No data is moving, and the file is not shrinking. My
>second file, in the same file group is set to 15360MB.
>Am I missing something?
>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and full,
>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2 and
>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>equally empty files.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
it
>>larger? I need to break this single large datafile into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
same
>>file.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>.
>|||In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).
>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>>--Original Message--
>>I'll check into that. Could you please confirm that
>neither of these files
>>is the primary file and that the filegroup is not the
>primary filegroup? If
>>we're not talking about the primary file/filegroup, then
>I have an
>>alternative.
>>Let's say that you have a filegroup that has the one
>30GB file - File1.
>>Now, add two more files to the filegroup - File1 and
>File2 - each 15GB in
>>size. Here's the clincher. Now run:
>>DBCC SHRINKFILE (File1, EMPTYFILE)
>>This will force the move and then disallow any further
>addition of data to
>>File1. Now run:
>>ALTER DATABASE MyDB
>>REMOVE FILE File1
>>At this point, you can rename File3 to File1:
>>ALTER DATABASE MyDB
>>MODIFY FILE (NAME = File3, NEWNAME = File1)
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>>Hey Tom, I tried your suggestion, and it's not working.
>>Here is the command I'm using and the results:
>>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>>11 1 3925712 128 3925648
3925648
>>(1 row(s) affected)
>>DBCC execution completed. If DBCC printed error
messages,
>>contact your system administrator.
>>No data is moving, and the file is not shrinking. My
>>second file, in the same file group is set to 15360MB.
>>Am I missing something?
>>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and
full,
>>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2
and
>>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>>equally empty files.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
>it
>>larger? I need to break this single large datafile
into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
>SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
>same
>>file.
>>--
>>Tom
>>---
-
>-
>>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
>break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data
file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>>
>>.
>.
>|||When I built the original table t, it was in the default filegroup. Since I
did not explicitly change the default filegroup when I built the DB, it was
the primary filegroup. I implicitly created a clustered index by adding a
primary key to the CREATE TABLE script. If you ran sp_help right after that
table was built, you'd see that the table existed in the primary filegroup.
When I re-created the index, using the drop_existing option, I explicitly
created it on SalesGroup1 by adding "on SalesGroup1". This forces the
clustered index - and hence, the data - to be moved to the new filegroup.
As for doing this on an entire database, you can use EM to script out your
tables, including indexes. Throw away the table stuff and keep the index
scripts. Change all references from the primary filegroup to the new
filegroup. For primary keys, the following will generate your script:
create function CreateIndexScript
(
@.Table sysname
, @.FileGroup sysname
)
returns varchar (8000)
as
begin
declare
@.str varchar (8000)
, @.constraint sysname
, @.IsClustered char (1)
, @.Column sysname
select
@.constraint = CONSTRAINT_NAME
, @.IsClustered = case when indexproperty (object_id (@.Table),
CONSTRAINT_NAME, 'IsClustered') = 1
then 'Y' else 'N' end
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
TABLE_NAME = @.Table
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
select
@.str = 'create unique ' + case when @.IsClustered = 'Y' then 'clustered '
else '' end
+ 'index ' + @.constraint + ' on [' + @.Table + '] ('
declare c cursor fast_forward for
select
COLUMN_NAME
from
information_schema.key_column_usage
where
TABLE_NAME = @.Table
and CONSTRAINT_NAME = @.constraint
order by
ORDINAL_POSITION
open c
fetch c into
@.Column
while @.@.fetch_status = 0
begin
set
@.str = @.str + '[' + @.Column + '], '
fetch c into
@.Column
end
close c
deallocate c
set
@.str = left (@.str, len (@.str) - 1) + ') with drop_existing on [' +
@.FileGroup + ']'
return (@.str)
end
go
select
dbo.CreateIndexScript (TABLE_NAME, 'MyFG')
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
go
drop function CreateIndexScript
You don't need to do anything for your views.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:59bd01c42d3e$a4603fd0$a101280a@.phx.gbl...
In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).
>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>>--Original Message--
>>I'll check into that. Could you please confirm that
>neither of these files
>>is the primary file and that the filegroup is not the
>primary filegroup? If
>>we're not talking about the primary file/filegroup, then
>I have an
>>alternative.
>>Let's say that you have a filegroup that has the one
>30GB file - File1.
>>Now, add two more files to the filegroup - File1 and
>File2 - each 15GB in
>>size. Here's the clincher. Now run:
>>DBCC SHRINKFILE (File1, EMPTYFILE)
>>This will force the move and then disallow any further
>addition of data to
>>File1. Now run:
>>ALTER DATABASE MyDB
>>REMOVE FILE File1
>>At this point, you can rename File3 to File1:
>>ALTER DATABASE MyDB
>>MODIFY FILE (NAME = File3, NEWNAME = File1)
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>>Hey Tom, I tried your suggestion, and it's not working.
>>Here is the command I'm using and the results:
>>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>>11 1 3925712 128 3925648
3925648
>>(1 row(s) affected)
>>DBCC execution completed. If DBCC printed error
messages,
>>contact your system administrator.
>>No data is moving, and the file is not shrinking. My
>>second file, in the same file group is set to 15360MB.
>>Am I missing something?
>>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and
full,
>>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2
and
>>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>>equally empty files.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
>it
>>larger? I need to break this single large datafile
into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
>SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
>same
>>file.
>>--
>>Tom
>>---
-
>-
>>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
>break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data
file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>>
>>.
>.
>|||take a look at SQL LiteSpeed for backup and recovery. Thats what I use. Take a look at www.imceda.com