Rebuild of SQL Server

With the initial system DISK configuration now running out of space, it will
be necessary to reconfigure the disk structure - with a reload of Win2k and
SQL 2000.
Question: Prior to the server rebuild, if SQL is stopped and system/user d
atabase device files are copied to tape, after the rebuild of the server (sa
me name) and a reload of SQL 2000 to same drive, would it possible to stop S
QL and copy the same databa
se device files from tape to avoid a SQL restore of the databases. This woul
d save some time and effort in recreating the databases from scratch, includ
ing the master database.
Thanks in Advance for any advice.Hi,
Yes, You can do that and it will be the faster and easy methodology. But
ensure that you have the same service pack in new server as well.
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Greg H" <anonymous@.discussions.microsoft.com> wrote in message
news:F5F90AF5-EA63-4192-BEB5-E4238C2A624F@.microsoft.com...
> With the initial system DISK configuration now running out of space, it
will be necessary to reconfigure the disk structure - with a reload of Win2k
and SQL 2000.
> Question: Prior to the server rebuild, if SQL is stopped and system/user
database device files are copied to tape, after the rebuild of the server
(same name) and a reload of SQL 2000 to same drive, would it possible to
stop SQL and copy the same database device files from tape to avoid a SQL
restore of the databases. This would save some time and effort in recreating
the databases from scratch, including the master database.
> Thanks in Advance for any advice.|||Provided all the path namews for location of master, model, tempdb etc =
and the user databses are the same, same service pack levels etc then I =
be;leive this should be OK. I would still test it though.=20
Being paranoid I would take at least 2 tape copies. And if possible =
restore to new disks BEFORE ditching the old ones.
But do you really ned to reinstall win2k to make the disk changes.?
Mike John
"Greg H" <anonymous@.discussions.microsoft.com> wrote in message =
news:F5F90AF5-EA63-4192-BEB5-E4238C2A624F@.microsoft.com...
> With the initial system DISK configuration now running out of space, =
it will be necessary to reconfigure the disk structure - with a reload =
of Win2k and SQL 2000. =20
> Question: Prior to the server rebuild, if SQL is stopped and =
system/user database device files are copied to tape, after the rebuild =
of the server (same name) and a reload of SQL 2000 to same drive, would =
it possible to stop SQL and copy the same database device files from =
tape to avoid a SQL restore of the databases. This would save some time =
and effort in recreating the databases from scratch, including the =
master database.
>=20
> Thanks in Advance for any advice.

Real size of database

I have a database which I do not think is really big, but when it was created the initial file sizes where set to very large amounts.

How do I get the actual size of the data?

When I do shrink database, SQL say there is no space to reclaim but when I back it up the backup is extreamly small, does this mean that shrink database will not shrink past the original size?

How do I resize the database to only the actual size needed?

Here is the result of sp_spaceused:

database_name database_size unallocated space

--

CCCca 99863.81 MB 96318.27 MB

reserved data index_size unused

5680 KB 3000 KB 2232 KB 448 KB

One thing about shrinking database files is that you cannot shink below the true size of your data. Imagine your database as a box filled with paper. If the box was half-filled, you can only shrink it to that size and no more than that. You cannot shirink it to 1/3 of the size of the box as you have 1/2 as data. Your database size includes not just data and metadata but indexes as well. From the data you posted here, you cannot shrink the database to below approx 3GB|||

u can use,

backup log databasename with truncate_only and then shrink the files and database.

|||

After I shrink the database still getting a database size of 134534.56 MB not 3GB. I ran the following:

backup log CCCca with truncate_only

DBCC SHRINKFILE (CCCca_Primary, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_Log, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_Analysis, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_AnalysisIdx3, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_AnalysisIdx, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_AnalysisIdx2, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_PrimeIdx, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_Word, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_WordIdx, TRUNCATEONLY);

DBCC SHRINKDATABASE (CCCca, TRUNCATEONLY);

but all I get is : "

DBCC SHRINKDATABASE: File ID 1 of database ID 5 was skipped because the file does not have enough free space to reclaim."

is this data base really 134534.56 MB or is it 3GB?

|||Truncating the log does not necessarily mean shrinking the database. It simply means you are freeing up space on your transaction log and committing the transactions in your database. Microsoft did not include the shrinking as part of the truncate option for the transaction log as this will incurr overhead when transactions starts occurring after the truncate command. You have to do them individually to shrink your database.|||I have same problem, I can't shrink the data and index files.... even there are unused space|||

Have you tried DBCC SHRINKFILE with the 'target_size' option specified

Real size of database

I have a database which I do not think is really big, but when it was created the initial file sizes where set to very large amounts.

How do I get the actual size of the data?

When I do shrink database, SQL say there is no space to reclaim but when I back it up the backup is extreamly small, does this mean that shrink database will not shrink past the original size?

How do I resize the database to only the actual size needed?

Here is the result of sp_spaceused:

database_name database_size unallocated space

--

CCCca 99863.81 MB 96318.27 MB

reserved data index_size unused

5680 KB 3000 KB 2232 KB 448 KB

One thing about shrinking database files is that you cannot shink below the true size of your data. Imagine your database as a box filled with paper. If the box was half-filled, you can only shrink it to that size and no more than that. You cannot shirink it to 1/3 of the size of the box as you have 1/2 as data. Your database size includes not just data and metadata but indexes as well. From the data you posted here, you cannot shrink the database to below approx 3GB|||

u can use,

backup log databasename with truncate_only and then shrink the files and database.

|||

After I shrink the database still getting a database size of 134534.56 MB not 3GB. I ran the following:

backup log CCCca with truncate_only

DBCC SHRINKFILE (CCCca_Primary, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_Log, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_Analysis, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_AnalysisIdx3, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_AnalysisIdx, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_AnalysisIdx2, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_PrimeIdx, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_Word, TRUNCATEONLY);

DBCC SHRINKFILE (CCCca_WordIdx, TRUNCATEONLY);

DBCC SHRINKDATABASE (CCCca, TRUNCATEONLY);

but all I get is : "

DBCC SHRINKDATABASE: File ID 1 of database ID 5 was skipped because the file does not have enough free space to reclaim."

is this data base really 134534.56 MB or is it 3GB?

|||Truncating the log does not necessarily mean shrinking the database. It simply means you are freeing up space on your transaction log and committing the transactions in your database. Microsoft did not include the shrinking as part of the truncate option for the transaction log as this will incurr overhead when transactions starts occurring after the truncate command. You have to do them individually to shrink your database.|||I have same problem, I can't shrink the data and index files.... even there are unused space|||

Have you tried DBCC SHRINKFILE with the 'target_size' option specified