Format Alignment: physical

Showing posts with label physical. Show all posts

Monday, March 19, 2012

Filegroups and physical files

Hello All,
I am trying to analyse a DB schema created by someone
else. There is a file groups. How can I find out how many
files (and on what drives) belong to that file group?
Thanks a lot in advance for your help.
regards,
Abhishek.Hi,
If you are using enterprise manager right click on the database and look at
the properties.
From TSQL do this
select * from master.dbo.sysaltfiles
--
I hope this helps
regards
Greg O MCSD
SQL Scribe Documentation Builder
Document any SQL server database in minutes
Programmers love it, DBA dream of it
AGS SQL Scribe download a 30 day trial today
http://www.ag-software.com/ags_scribe_index.asp
"Abhishek Srivastava" <abhishek@.nospam.net> wrote in message
news:085701c345d8$7178cae0$a301280a@.phx.gbl...
> Hello All,
> I am trying to analyse a DB schema created by someone
> else. There is a file groups. How can I find out how many
> files (and on what drives) belong to that file group?
> Thanks a lot in advance for your help.
> regards,
> Abhishek.|||You can list all database files along with the file group name with
sp_helpdb:
EXEC sp_helpdb 'MyDatabase'
You can list files in a specific file group with sp_helpfilegroup:
USE MyDatabase
EXEC sp_helpfilegroup 'PRIMARY'
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Abhishek Srivastava" <abhishek@.nospam.net> wrote in message
news:085701c345d8$7178cae0$a301280a@.phx.gbl...
> Hello All,
> I am trying to analyse a DB schema created by someone
> else. There is a file groups. How can I find out how many
> files (and on what drives) belong to that file group?
> Thanks a lot in advance for your help.
> regards,
> Abhishek.

Filegroups and Joins

Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
LeilaThat's correct. You cannot direct a table or index to a particular file -
only to a filegroup. What the author probably meant to say was to place the
tables into separate filegroups on different drives.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
Leila|||Leila
CREATE DATABASE test
GO
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I read this tip in a article:
> "For very large joins, consider placing the tables to be joined in
> separate physical files in the same filegroup"
> How can I force SQL Server to create a table in filegroup on a particular
> file? As far as I know, I can tell SQL Server to create table on desired
> filegroup, but if that filegroup contains more than one file, is it
possible
> to put table on a particular one?
> Thanks in advance.
> Leila
>|||Thanks Uri,
But you have placed two tables on two different filegroups. The author
mentioned that the tables can be placed in two different files, on the SAME
filegroup!
Leila
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Leila
> CREATE DATABASE test
> GO
> ALTER DATABASE test
> ADD FILEGROUP ww_Group
> GO
> ALTER DATABASE test
> ADD FILE
> ( NAME = ww,
> FILENAME = 'D:\wwdat1.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB)
> TO FILEGROUP ww_Group
> create table test..test(id int identity) on [primary]
> create table test..test_GR(id int identity) on ww_Group
>
>
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> > I read this tip in a article:
> > "For very large joins, consider placing the tables to be joined in
> > separate physical files in the same filegroup"
> >
> > How can I force SQL Server to create a table in filegroup on a
particular
> > file? As far as I know, I can tell SQL Server to create table on desired
> > filegroup, but if that filegroup contains more than one file, is it
> possible
> > to put table on a particular one?
> >
> > Thanks in advance.
> > Leila
> >
> >
>|||The author was wrong. You cannot place an object on a particular file, only
a particular filegroup.
The space for the object is then allocated more or less evenly across all
files in that filegroup.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> Thanks Uri,
> But you have placed two tables on two different filegroups. The author
> mentioned that the tables can be placed in two different files, on the
> SAME
> filegroup!
> Leila
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> Leila
>> CREATE DATABASE test
>> GO
>> ALTER DATABASE test
>> ADD FILEGROUP ww_Group
>> GO
>> ALTER DATABASE test
>> ADD FILE
>> ( NAME = ww,
>> FILENAME = 'D:\wwdat1.ndf',
>> SIZE = 5MB,
>> MAXSIZE = 100MB,
>> FILEGROWTH = 5MB)
>> TO FILEGROUP ww_Group
>> create table test..test(id int identity) on [primary]
>> create table test..test_GR(id int identity) on ww_Group
>>
>>
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> > I read this tip in a article:
>> > "For very large joins, consider placing the tables to be joined in
>> > separate physical files in the same filegroup"
>> >
>> > How can I force SQL Server to create a table in filegroup on a
> particular
>> > file? As far as I know, I can tell SQL Server to create table on
>> > desired
>> > filegroup, but if that filegroup contains more than one file, is it
>> possible
>> > to put table on a particular one?
>> >
>> > Thanks in advance.
>> > Leila
>> >
>> >
>>
>|||Thanks Kalen,
What about log files? If I have more than one log file for my DB, are the
logged information written in multiple files or the log files are filled one
by one?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> The author was wrong. You cannot place an object on a particular file,
only
> a particular filegroup.
> The space for the object is then allocated more or less evenly across all
> files in that filegroup.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > Thanks Uri,
> > But you have placed two tables on two different filegroups. The author
> > mentioned that the tables can be placed in two different files, on the
> > SAME
> > filegroup!
> >
> > Leila
> >
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> Leila
> >> CREATE DATABASE test
> >> GO
> >> ALTER DATABASE test
> >> ADD FILEGROUP ww_Group
> >> GO
> >> ALTER DATABASE test
> >> ADD FILE
> >> ( NAME = ww,
> >> FILENAME = 'D:\wwdat1.ndf',
> >> SIZE = 5MB,
> >> MAXSIZE = 100MB,
> >> FILEGROWTH = 5MB)
> >> TO FILEGROUP ww_Group
> >>
> >> create table test..test(id int identity) on [primary]
> >> create table test..test_GR(id int identity) on ww_Group
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> > Hi,
> >> > I read this tip in a article:
> >> > "For very large joins, consider placing the tables to be joined in
> >> > separate physical files in the same filegroup"
> >> >
> >> > How can I force SQL Server to create a table in filegroup on a
> > particular
> >> > file? As far as I know, I can tell SQL Server to create table on
> >> > desired
> >> > filegroup, but if that filegroup contains more than one file, is it
> >> possible
> >> > to put table on a particular one?
> >> >
> >> > Thanks in advance.
> >> > Leila
> >> >
> >> >
> >>
> >>
> >
> >
>|||One by one. SQL Server "rattles through the log files like a snake", where the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Love the analogy, Tibor! ;-)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23vZOx3tEHA.1356@.TK2MSFTNGP11.phx.gbl...
One by one. SQL Server "rattles through the log files like a snake", where
the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message
news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled
one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across
all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

Filegroups and indexes

Hi,
I have two physical disks that each have and NDF file(two different
filegroups: FG1 on Disk1, FG2 on disk2).
Also I have two tables that my queries always join them.
Which decision will lead to better performance:
1) Placing Table1 and Table2 on first disk(FG1) and placing their indexes on
disk2(FG2)
2) Placing Table1 and its indexes on FG1 and placing Table2 and its indexes
on FG2.
Many thanks in advance.
LeilaThe only way to know is to test it in your exact situation. It could be
that neither will give you any better performance.
--
Andrew J. Kelly SQL MVP
"Leila" <lelas@.hotpop.com> wrote in message
news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have two physical disks that each have and NDF file(two different
> filegroups: FG1 on Disk1, FG2 on disk2).
> Also I have two tables that my queries always join them.
> Which decision will lead to better performance:
> 1) Placing Table1 and Table2 on first disk(FG1) and placing their indexes
on
> disk2(FG2)
> 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
indexes
> on FG2.
> Many thanks in advance.
> Leila
>|||Andrew,
What conditions should be met so that it helps to increase performance?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#A0VAPApEHA.2948@.TK2MSFTNGP11.phx.gbl...
> The only way to know is to test it in your exact situation. It could be
> that neither will give you any better performance.
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have two physical disks that each have and NDF file(two different
> > filegroups: FG1 on Disk1, FG2 on disk2).
> > Also I have two tables that my queries always join them.
> > Which decision will lead to better performance:
> > 1) Placing Table1 and Table2 on first disk(FG1) and placing their
indexes
> on
> > disk2(FG2)
> > 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
> indexes
> > on FG2.
> >
> > Many thanks in advance.
> > Leila
> >
> >
>|||That's a big subject but when it comes to disks your best bet is to see if
you have disk queue issues first. You can do this by monitoring the Avg &
current disk queues in Perfmon. Here are some links that may help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:uuRgqnApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Andrew,
> What conditions should be met so that it helps to increase performance?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#A0VAPApEHA.2948@.TK2MSFTNGP11.phx.gbl...
> > The only way to know is to test it in your exact situation. It could be
> > that neither will give you any better performance.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Leila" <lelas@.hotpop.com> wrote in message
> > news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> > > Hi,
> > > I have two physical disks that each have and NDF file(two different
> > > filegroups: FG1 on Disk1, FG2 on disk2).
> > > Also I have two tables that my queries always join them.
> > > Which decision will lead to better performance:
> > > 1) Placing Table1 and Table2 on first disk(FG1) and placing their
> indexes
> > on
> > > disk2(FG2)
> > > 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
> > indexes
> > > on FG2.
> > >
> > > Many thanks in advance.
> > > Leila
> > >
> > >
> >
> >
>

Friday, March 9, 2012

Filegroup & .NDF Question

Jude,
1 - You can change the name of the physical files during restore operation
using "with move" option.
2 - You can change the logical name of the files, using the statement "alter
database".
alter database northwind
modify file (name = 'northwind', newname = 'northwind1')
exec sp_helpdb northwind
go
See BOL for more info.
AMB
"Jude" wrote:

> How can I change the FILEGROUP on database properties?
> SQL Server 2000
> I restored one database to create a new database, but the .ndf filegroup o
f the restored db has the name of the restored db.
> For example;
> DB 1 = TestDB1.mdf TestINDX1.ndf TestLog1.ldf
> Backup this db & restore as Test2
> DB 2 = TestDB2.mdf TestINDX2.ndf (BUT in EM Properties under filegroup
it has "TestINDX1"
> I tried dbcc shrinkfile(TestIndx2, emptyfile), so that I could just delete
the .NDF & recreate it in EM with the correct filegroup name, but it keeps
telling me I can't drop the .ndf because it is NOT empty.
> Where am I going wrong?
> Also, am I missing something in my restore? Is there T-Sql in the restore
that designates the .ndf filegroup name & I am missing it?
> Any instruction/suggestions will be appreciated!!!
> thanx!
>"With Move", I'll look it up in BOL.
thanx!
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:509ED16F-2201-4D31-835E-B5EAE48C53B0@.microsoft.com...[vbcol=seagreen]
> Jude,
> 1 - You can change the name of the physical files during restore operation
> using "with move" option.
> 2 - You can change the logical name of the files, using the statement
> "alter
> database".
> alter database northwind
> modify file (name = 'northwind', newname = 'northwind1')
> exec sp_helpdb northwind
> go
> See BOL for more info.
>
> AMB
> "Jude" wrote:
>|||How can I change the FILEGROUP on database properties?
SQL Server 2000
I restored one database to create a new database, but the .ndf filegroup of
the restored db has the name of the restored db.
For example;
DB 1 = TestDB1.mdf TestINDX1.ndf TestLog1.ldf
Backup this db & restore as Test2
DB 2 = TestDB2.mdf TestINDX2.ndf (BUT in EM Properties under filegroup it
has "TestINDX1"
I tried dbcc shrinkfile(TestIndx2, emptyfile), so that I could just delete t
he .NDF & recreate it in EM with the correct filegroup name, but it keeps te
lling me I can't drop the .ndf because it is NOT empty.
Where am I going wrong?
Also, am I missing something in my restore? Is there T-Sql in the restore t
hat designates the .ndf filegroup name & I am missing it?
Any instruction/suggestions will be appreciated!!!
thanx!|||Jude,
1 - You can change the name of the physical files during restore operation
using "with move" option.
2 - You can change the logical name of the files, using the statement "alter
database".
alter database northwind
modify file (name = 'northwind', newname = 'northwind1')
exec sp_helpdb northwind
go
See BOL for more info.
AMB
"Jude" wrote:

> How can I change the FILEGROUP on database properties?
> SQL Server 2000
> I restored one database to create a new database, but the .ndf filegroup o
f the restored db has the name of the restored db.
> For example;
> DB 1 = TestDB1.mdf TestINDX1.ndf TestLog1.ldf
> Backup this db & restore as Test2
> DB 2 = TestDB2.mdf TestINDX2.ndf (BUT in EM Properties under filegroup
it has "TestINDX1"
> I tried dbcc shrinkfile(TestIndx2, emptyfile), so that I could just delete
the .NDF & recreate it in EM with the correct filegroup name, but it keeps
telling me I can't drop the .ndf because it is NOT empty.
> Where am I going wrong?
> Also, am I missing something in my restore? Is there T-Sql in the restore
that designates the .ndf filegroup name & I am missing it?
> Any instruction/suggestions will be appreciated!!!
> thanx!
>|||"With Move", I'll look it up in BOL.
thanx!
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:509ED16F-2201-4D31-835E-B5EAE48C53B0@.microsoft.com...[vbcol=seagreen]
> Jude,
> 1 - You can change the name of the physical files during restore operation
> using "with move" option.
> 2 - You can change the logical name of the files, using the statement
> "alter
> database".
> alter database northwind
> modify file (name = 'northwind', newname = 'northwind1')
> exec sp_helpdb northwind
> go
> See BOL for more info.
>
> AMB
> "Jude" wrote:
>

Wednesday, March 7, 2012

File system error: The following file is corrupted Analysis Services

Hi i got this error while deploying Analysis Services project.

Error 1 File system error: The following file is corrupted: Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Tutorial.0.db\Customer.0.dim\5.English Education.(All).sstore. Logical file . Errors in the metadata manager. An error occurred when loading the Customer dimension, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Tutorial.0.db\Customer.5.dim.xml'. Errors in the metadata manager. An error occurred when loading the Analysis Services Tutorial cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Tutorial.0.db\Adventure Works DW.9.cub.xml'. 0 0

pls help to resolve this problem... thanks in advance.

kapil

File system error: The background thread running lazy writer encountered an I/O error. Physical

Hi All,

Anyone encountered this kind of error:

File system error: The background thread running lazy writer encountered an I/O error. Physical file: \\?\D:\OLAP_DATA\Project88.6.db\Dim Service Dim Std Sub Key.0.dim\677.Hierarchy 5.Subkey Only.lstore. Logical file: .

I believe a corruption of OLAP database occured during the dimension update processing and I don't know what might have caused and factors that have caused this problem.

The resolution to this error was to restart analysis service then process update the dimension.

Any insight on this will be appreciated.

thanks

Please contact Customer Support with you situation.

Are you running SP1? See if installing it resolves the problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Yes, SQL Server 2005 SP1 and cumulative hotfixes were already installed.

File system error: The background thread running lazy writer encountered an I/O error. Physi

Hi All,

Anyone encountered this kind of error:

I believe a corruption of OLAP database occured during the dimension update processing and I don't know what might have caused and factors that have caused this problem.

The resolution to this error was to restart analysis service then process update the dimension.

Any insight on this will be appreciated.

thanks

Please contact Customer Support with you situation.

Are you running SP1? See if installing it resolves the problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Yes, SQL Server 2005 SP1 and cumulative hotfixes were already installed.

File system error: A FileStore error from WriteFile occurred

Error: Code: 0x00000000
Source: SRC Process dimensions Error:
Description: File system error: A FileStore error from WriteFile occurred. Physical file: \\?\<pathto filename> attribute.asstore. Logical file: . .
Errors in the OLAP storage engine: An error occurred while the <attribute id> attr
ibute' attribute of the <dimensionname> dimension from the '<database>' database was being processed.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
End Error

I encountered this while incrementally processing a dimension via AMO script in SSIS. Does anyone have ideas on what this error means and ways to avoid?

I unprocessed and reprocessed to resolve this.|||

What do you mean by "Unprocess a cube"?

How did you unprocess a cube?

Thanks.

Chris

|||

Description: File system error: A FileStore error from WriteFile occurred. Physical file: \\?\<pathto filename> attribute.asstore. Logical file: . .

Yikes. This has happened to me a few times within the cubes and dimensions. I also get the writefile error, which appears to be a bad reference to a file that no longer exists. I tried to use other processing methods ot process the dimension that was broken, and now I don't even get the errror anymore. Now the processing dialog box just hangs, saying that it is building a processing schedule. I do not want to unprocess the dimension, because I will have to reprocess the entire database. Does anyone have a clue as to how to avoid this, or why it happens? How could one fix it without unprocessing?

Thank,

Sally

|||

That's a while ago. My problem was actually caused by the size of a dimension that I was processing.

It was just too big. Later I reduced the size, and the error disappeared.

Thanks anyway.

Chris

|||

I have been experimenting with large dimensions (or at least what we consider to be large - ~100mm members), and have learned the string store (filename ends in .asstore) cannot exceed 4 gig. Is this the same thing you have run into, or have you found another limitation?

John

File system error: A FileStore error from WriteFile occurred

I encountered this while incrementally processing a dimension via AMO script in SSIS. Does anyone have ideas on what this error means and ways to avoid?

I unprocessed and reprocessed to resolve this.|||

What do you mean by "Unprocess a cube"?

How did you unprocess a cube?

Thanks.

Chris

|||

Description: File system error: A FileStore error from WriteFile occurred. Physical file: \\?\<pathto filename> attribute.asstore. Logical file: . .

Thank,

Sally

|||

That's a while ago. My problem was actually caused by the size of a dimension that I was processing.

It was just too big. Later I reduced the size, and the error disappeared.

Thanks anyway.

Chris

|||

John

File system error: A FileStore error from WriteFile occurred

I encountered this while incrementally processing a dimension via AMO script in SSIS. Does anyone have ideas on what this error means and ways to avoid?

I unprocessed and reprocessed to resolve this.|||

What do you mean by "Unprocess a cube"?

How did you unprocess a cube?

Thanks.

Chris

|||

Description: File system error: A FileStore error from WriteFile occurred. Physical file: \\?\<pathto filename> attribute.asstore. Logical file: . .

Thank,

Sally

|||

That's a while ago. My problem was actually caused by the size of a dimension that I was processing.

It was just too big. Later I reduced the size, and the error disappeared.

Thanks anyway.

Chris

|||

John

File system error: A FileStore error from WriteFile occurred

I encountered this while incrementally processing a dimension via AMO script in SSIS. Does anyone have ideas on what this error means and ways to avoid?

I unprocessed and reprocessed to resolve this.|||

What do you mean by "Unprocess a cube"?

How did you unprocess a cube?

Thanks.

Chris

|||

Description: File system error: A FileStore error from WriteFile occurred. Physical file: \\?\<pathto filename> attribute.asstore. Logical file: . .

Thank,

Sally

|||

That's a while ago. My problem was actually caused by the size of a dimension that I was processing.

It was just too big. Later I reduced the size, and the error disappeared.

Thanks anyway.

Chris

|||

John

File system error: A FileStore error from WriteFile occurred

I encountered this while incrementally processing a dimension via AMO script in SSIS. Does anyone have ideas on what this error means and ways to avoid?

I unprocessed and reprocessed to resolve this.|||

What do you mean by "Unprocess a cube"?

How did you unprocess a cube?

Thanks.

Chris

|||

Description: File system error: A FileStore error from WriteFile occurred. Physical file: \\?\<pathto filename> attribute.asstore. Logical file: . .

Thank,

Sally

|||

That's a while ago. My problem was actually caused by the size of a dimension that I was processing.

It was just too big. Later I reduced the size, and the error disappeared.

Thanks anyway.

Chris

|||

John

Sunday, February 26, 2012

File placement on server with 2 instances of SQL

I have inherited a stand alone server with 2 instances of SQL installed.
There are 6 physical disks in the server which have been made into 3 mirrors.
The first mirror has the OS on it. Currently, the 2nd disk has all the
database and transaction log files from both instances of SQL.
I plan to make use of the 3rd disk. My question is: is it better to move the
database and transaction log files from the second instance to the new disk
so that all the files for the first instance are on disk 2 and all the files
for the 2nd instance are on disk 3 OR is it better to keep all the database
files from both instances on disk 2 and move all the log files for both
instances to disk 3?
I'm sure I have read somehwere that in this situation, the disks should be
separated by instance rather than seperating by file type.Seen from the databases' perspective, same or different instances doesn't really make a difference.
As usual, only testing with a realistic workload can give you a definitive answer. My guess, though,
is to have the tlog files separated.
Unless you have some pretty unusual workload. Like only reads during day and modifications during
night. And it doesn't matter that modifications take a bit longer. Then you might consider having db
1 (all db files) on one raid and db 2 (all files) on another. That would mean that the read
operations during day would not compete between db1 and db2.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>I have inherited a stand alone server with 2 instances of SQL installed.
> There are 6 physical disks in the server which have been made into 3 mirrors.
> The first mirror has the OS on it. Currently, the 2nd disk has all the
> database and transaction log files from both instances of SQL.
> I plan to make use of the 3rd disk. My question is: is it better to move the
> database and transaction log files from the second instance to the new disk
> so that all the files for the first instance are on disk 2 and all the files
> for the 2nd instance are on disk 3 OR is it better to keep all the database
> files from both instances on disk 2 and move all the log files for both
> instances to disk 3?
> I'm sure I have read somehwere that in this situation, the disks should be
> separated by instance rather than seperating by file type.
>|||+1 to both of Tibor's sentiments.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>|||I also second Tibor's response. Especially the part about the only
difinitive answer will come from testing.
And don't forget there is a third option. That's to put the data from DB1
and the log from DB2 on Disk2 and the log from DB1 and the data from DB2 on
Disk3. That might help in a situation where you have primarily reads on
both databases during the day and updates during the night and almost all
the updates are done to one database.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>

Sunday, February 19, 2012

File Group backup and restores

I have a database that has multiple data files (.mdf, .ndf), the complete
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
SalRead about the PARTIAL options of the RESTORE command. Also see below section of Books Online
"Partial Database Restore Operations".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive that
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if using
a FileGroup Backup?
"Tibor Karaszi" wrote:
> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
> >I have a database that has multiple data files (.mdf, .ndf), the complete
> > backup of this database has now exceeded the size of the physical drive that
> > I used to store the backup file.
> > We have tried taking FileGroup Backups, placing files of the backup on
> > separate drives. The Backup is sucessful.
> > I am having a problem restoring the backup files. It seems that I have to
> > overwrite the original database file. How can I restore this FileGroup
> > backup to the same server but to a different database? Is this possible?
> > Thanks,
> > Sal
>|||If you follow the rules for a PARTIAL restore (as documented in Books Online), then the answer is
yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if using
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:
>> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
>> "Partial Database Restore Operations".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
>> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>> >I have a database that has multiple data files (.mdf, .ndf), the complete
>> > backup of this database has now exceeded the size of the physical drive that
>> > I used to store the backup file.
>> > We have tried taking FileGroup Backups, placing files of the backup on
>> > separate drives. The Backup is sucessful.
>> > I am having a problem restoring the backup files. It seems that I have to
>> > overwrite the original database file. How can I restore this FileGroup
>> > backup to the same server but to a different database? Is this possible?
>> > Thanks,
>> > Sal
>>

Monday, March 19, 2012

Filegroups and physical files

Filegroups and Joins

Filegroups and Joins

Filegroups and Joins

Filegroups and indexes

Filegroups and indexes

Friday, March 9, 2012

Filegroup & .NDF Question

Wednesday, March 7, 2012

File system error: The following file is corrupted Analysis Services

File system error: The background thread running lazy writer encountered an I/O error. Physical

File system error: The background thread running lazy writer encountered an I/O error. Physi

File system error: A FileStore error from WriteFile occurred

File system error: A FileStore error from WriteFile occurred

File system error: A FileStore error from WriteFile occurred

File system error: A FileStore error from WriteFile occurred

File system error: A FileStore error from WriteFile occurred

Sunday, February 26, 2012

File placement on server with 2 instances of SQL

Sunday, February 19, 2012

File Group backup and restores

File Group backup and restores

File Group backup and restores

Format Alignment

Blog Archive

About Me