Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
--
- - - - - - - - -
Thanks
Yogish
"durai" wrote:
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
- - - - - - - - -
Thanks
Yogish
"durai" wrote:

> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?
Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
- - - - - - - - -
Thanks
Yogish
"durai" wrote:

> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>
|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

adding and viewing comments in a backup file.

Hi all,
I'm using SQL Server 2000 Standard edition on Windows 2000 server.
It is possible to add comments when creating a backup file. If so, how do I
later view those comments
Thanks
AviYou can enter a description when you perform the backup
using the DESCRIPTION argument. You can view the description
of a backup file, device using RESTORE HEADERONLY.
You can find more information in books online under backup
and restore headeronly.
-Sue
On Thu, 4 Dec 2003 16:22:12 +0200, "Avi"
<rememberoti@.yahoo.com> wrote:
>Hi all,
>I'm using SQL Server 2000 Standard edition on Windows 2000 server.
>It is possible to add comments when creating a backup file. If so, how do I
>later view those comments
>Thanks
>Avi
>

Adding and additional drive

On a database server, I have mapped a new drive letter to
a SAN so that I can backup my database. When I go into
Enterprise Manager, I can see the drive, but when I try to
create a new backup device, it gives me an error stating
that 'the directory in whch you want to create this file
does not exist'.
If I try to create a new data or log file for a database
on that server, I do not see the drive.
Should I just reboot the server or is it something else?
SQL Server will probably not see mapped drives... it is running under a
different account than what you used when you mapped the drive.
The BEST way to do this is to make sure that the account SQL Server is
running under has rights to the remote share.
Simply issue a backup using the UNC
BACKUP DATABASE master TO DISK = '\\RemoteServer\SomeShare\master.bak' WITH
INIT
Keith
"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:225801c49a93$8ec19da0$a501280a@.phx.gbl...
> On a database server, I have mapped a new drive letter to
> a SAN so that I can backup my database. When I go into
> Enterprise Manager, I can see the drive, but when I try to
> create a new backup device, it gives me an error stating
> that 'the directory in whch you want to create this file
> does not exist'.
> If I try to create a new data or log file for a database
> on that server, I do not see the drive.
> Should I just reboot the server or is it something else?
>
|||http://support.microsoft.com/default...b;en-us;555128
Everything you ever wanted to know about backing up to remote shares.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:225801c49a93$8ec19da0$a501280a@.phx.gbl...
> On a database server, I have mapped a new drive letter to
> a SAN so that I can backup my database. When I go into
> Enterprise Manager, I can see the drive, but when I try to
> create a new backup device, it gives me an error stating
> that 'the directory in whch you want to create this file
> does not exist'.
> If I try to create a new data or log file for a database
> on that server, I do not see the drive.
> Should I just reboot the server or is it something else?
>

Adding and additional drive

On a database server, I have mapped a new drive letter to
a SAN so that I can backup my database. When I go into
Enterprise Manager, I can see the drive, but when I try to
create a new backup device, it gives me an error stating
that 'the directory in whch you want to create this file
does not exist'.
If I try to create a new data or log file for a database
on that server, I do not see the drive.
Should I just reboot the server or is it something else?SQL Server will probably not see mapped drives... it is running under a
different account than what you used when you mapped the drive.
The BEST way to do this is to make sure that the account SQL Server is
running under has rights to the remote share.
Simply issue a backup using the UNC
BACKUP DATABASE master TO DISK = '\\RemoteServer\SomeShare\master.bak' WITH
INIT
--
Keith
"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:225801c49a93$8ec19da0$a501280a@.phx.gbl...
> On a database server, I have mapped a new drive letter to
> a SAN so that I can backup my database. When I go into
> Enterprise Manager, I can see the drive, but when I try to
> create a new backup device, it gives me an error stating
> that 'the directory in whch you want to create this file
> does not exist'.
> If I try to create a new data or log file for a database
> on that server, I do not see the drive.
> Should I just reboot the server or is it something else?
>|||http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
Everything you ever wanted to know about backing up to remote shares.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:225801c49a93$8ec19da0$a501280a@.phx.gbl...
> On a database server, I have mapped a new drive letter to
> a SAN so that I can backup my database. When I go into
> Enterprise Manager, I can see the drive, but when I try to
> create a new backup device, it gives me an error stating
> that 'the directory in whch you want to create this file
> does not exist'.
> If I try to create a new data or log file for a database
> on that server, I do not see the drive.
> Should I just reboot the server or is it something else?
>