Dear All,
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
--
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
>--Original Message--
>By any chance, are you trying to shrink the tempdb data
file? Sometimes,
>that fails because there are temporary worktables in
there that are still
>being used, though QA would report as if the file had
been shrunk.
>--
>Regards
>Ray Mond
>
>.
>|||Hi,
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
be residing on the end of the file, which prevents any significant
truncation.
--
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> >--Original Message--
> >By any chance, are you trying to shrink the tempdb data
> file? Sometimes,
> >that fails because there are temporary worktables in
> there that are still
> >being used, though QA would report as if the file had
> been shrunk.
> >
> >--
> >Regards
> >Ray Mond
> >
> >
> >.
> >|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Thanks Guys,
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
>--Original Message--
>THe command you are using with the Truncateonly parameter
does NOT move any
>of the data from the end of the file to the front of the
file, it only
>returns any unused end portion of the file... instead
try something like
>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,
then truncate all of
>the unused portion ...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> Dear All,
>> I have just tried to shrink a file, both in Enterprise
>> Manager and in Query Analyser.
>> Within EM it hung.
>> Within QA it said it shrinked it but when I looked at
the
>> size of the file it was still the same size.
>> The code I used in QA was
>> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> Can anyone gice me pointers ?
>> Thanks
>> J
>
>.
>|||> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> >--Original Message--
> >THe command you are using with the Truncateonly parameter
> does NOT move any
> >of the data from the end of the file to the front of the
> file, it only
> >returns any unused end portion of the file... instead
> try something like
> >
> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
> >
> >this command will move data from the end to the front ,
> then truncate all of
> >the unused portion ...
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> >> Dear All,
> >>
> >> I have just tried to shrink a file, both in Enterprise
> >> Manager and in Query Analyser.
> >>
> >> Within EM it hung.
> >>
> >> Within QA it said it shrinked it but when I looked at
> the
> >> size of the file it was still the same size.
> >>
> >> The code I used in QA was
> >>
> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> >>
> >> Can anyone gice me pointers ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||Thanks Paul
>--Original Message--
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of
shrink is dependent
>on a bunch of things, including how much you ask the file
to shrink, the
>distribution of free space at the start of the file, the
speed of your IO
>system, and most importantly, the amount of concurrent
activity on the
>system that may block shrink. Remember that shrink is an
online operation
>and so does not block concurrent activity - this means
you can let it run
>longer than your maintenance window as long as you can
cope with the small
>drop in workload throughput from having shrink working in
the database.
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
>> Thanks Guys,
>> It worked using the dbcc shrinkfile(FileName, 4670)
option
>> a couple of you guys sugested.
>> The main problem here was time. In this case it took
over
>> 20 minutes to shrink, and I was about to cancel it when
it
>> worked ;)
>> Anyway thanks for that.
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>> Again thanks for your help
>> J
>>
>>
>> >--Original Message--
>> >THe command you are using with the Truncateonly
parameter
>> does NOT move any
>> >of the data from the end of the file to the front of
the
>> file, it only
>> >returns any unused end portion of the file... instead
>> try something like
>> >
>> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>> >
>> >this command will move data from the end to the front ,
>> then truncate all of
>> >the unused portion ...
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> >> Dear All,
>> >>
>> >> I have just tried to shrink a file, both in
Enterprise
>> >> Manager and in Query Analyser.
>> >>
>> >> Within EM it hung.
>> >>
>> >> Within QA it said it shrinked it but when I looked at
>> the
>> >> size of the file it was still the same size.
>> >>
>> >> The code I used in QA was
>> >>
>> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> >>
>> >> Can anyone gice me pointers ?
>> >>
>> >> Thanks
>> >> J
>> >
>> >
>> >.
>> >
>
>.
>
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Thursday, March 29, 2012
DBCC Shrinkfile
Dear All,
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
file? Sometimes,
there that are still
been shrunk.
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
be residing on the end of the file, which prevents any significant
truncation.
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...[QUOTE]
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> file? Sometimes,
> there that are still
> been shrunk.|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
does NOT move any
file, it only
try something like
then truncate all of
Charlotte, NC
(PASS) and it's
message
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...[QUOTE]
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> does NOT move any
> file, it only
> try something like
> then truncate all of
> Charlotte, NC
> (PASS) and it's
> message
> the|||Thanks Paul
shrink is dependent
to shrink, the
speed of your IO
activity on the
online operation
you can let it run
cope with the small
the database.
confers no rights.
message
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
quote:
>--Original Message--
>By any chance, are you trying to shrink the tempdb data
file? Sometimes,
quote:
>that fails because there are temporary worktables in
there that are still
quote:
>being used, though QA would report as if the file had
been shrunk.
quote:|||Hi,
>--
>Regards
>Ray Mond
>
>.
>
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
quote:|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J
be residing on the end of the file, which prevents any significant
truncation.
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...[QUOTE]
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> file? Sometimes,
> there that are still
> been shrunk.|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
quote:|||Thanks Guys,
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
quote:
>--Original Message--
>THe command you are using with the Truncateonly parameter
does NOT move any
quote:
>of the data from the end of the file to the front of the
file, it only
quote:
>returns any unused end portion of the file... instead
try something like
quote:
>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,
then truncate all of
quote:
>the unused portion ...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
quote:
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
quote:
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||> Question though, is there an automated way without using
>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
the[QUOTE]
>
>.
>
quote:
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...[QUOTE]
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> does NOT move any
> file, it only
> try something like
> then truncate all of
> Charlotte, NC
> (PASS) and it's
> message
> the|||Thanks Paul
quote:
>--Original Message--
of[QUOTE]
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of
shrink is dependent
quote:
>on a bunch of things, including how much you ask the file
to shrink, the
quote:
>distribution of free space at the start of the file, the
speed of your IO
quote:
>system, and most importantly, the amount of concurrent
activity on the
quote:
>system that may block shrink. Remember that shrink is an
online operation
quote:
>and so does not block concurrent activity - this means
you can let it run
quote:
>longer than your maintenance window as long as you can
cope with the small
quote:
>drop in workload throughput from having shrink working in
the database.
quote:
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
quote:
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
quote:
>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
option[QUOTE]
over[QUOTE]
it[QUOTE]
of[QUOTE]
parameter[QUOTE]
the[QUOTE]
Enterprise[QUOTE]
>
>.
>
Monday, March 19, 2012
DBCC MEMORYSTATUS
Dear all,
How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
Computer.
I dont want to connect my Query Analyser to the server i want to get
information.
thanx
Doller
EXEC TIBWORK.master.dbo.sp_executesql N'DBCC MEMORYSTATUS'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"doller" <sufianarif@.gmail.com> wrote in message
news:1142663795.418452.123220@.e56g2000cwe.googlegr oups.com...
> Dear all,
> How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
> Computer.
> I dont want to connect my Query Analyser to the server i want to get
> information.
> thanx
> Doller
>
|||HI Thanx
How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
Computer.
I dont want to connect my Query Analyser to the server i want to get
information.
thanx
Doller
EXEC TIBWORK.master.dbo.sp_executesql N'DBCC MEMORYSTATUS'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"doller" <sufianarif@.gmail.com> wrote in message
news:1142663795.418452.123220@.e56g2000cwe.googlegr oups.com...
> Dear all,
> How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
> Computer.
> I dont want to connect my Query Analyser to the server i want to get
> information.
> thanx
> Doller
>
|||HI Thanx
DBCC MEMORYSTATUS
Dear all,
How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
Computer.
I dont want to connect my Query Analyser to the server i want to get
information.
thanx
DollerEXEC TIBWORK.master.dbo.sp_executesql N'DBCC MEMORYSTATUS'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"doller" <sufianarif@.gmail.com> wrote in message
news:1142663795.418452.123220@.e56g2000cwe.googlegroups.com...
> Dear all,
> How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
> Computer.
> I dont want to connect my Query Analyser to the server i want to get
> information.
> thanx
> Doller
>|||HI Thanx
How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
Computer.
I dont want to connect my Query Analyser to the server i want to get
information.
thanx
DollerEXEC TIBWORK.master.dbo.sp_executesql N'DBCC MEMORYSTATUS'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"doller" <sufianarif@.gmail.com> wrote in message
news:1142663795.418452.123220@.e56g2000cwe.googlegroups.com...
> Dear all,
> How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
> Computer.
> I dont want to connect my Query Analyser to the server i want to get
> information.
> thanx
> Doller
>|||HI Thanx
DBCC MEMORYSTATUS
Dear all,
How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
Computer.
I dont want to connect my Query Analyser to the server i want to get
information.
thanx
DollerEXEC TIBWORK.master.dbo.sp_executesql N'DBCC MEMORYSTATUS'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"doller" <sufianarif@.gmail.com> wrote in message
news:1142663795.418452.123220@.e56g2000cwe.googlegroups.com...
> Dear all,
> How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
> Computer.
> I dont want to connect my Query Analyser to the server i want to get
> information.
> thanx
> Doller
>|||HI Thanx
How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
Computer.
I dont want to connect my Query Analyser to the server i want to get
information.
thanx
DollerEXEC TIBWORK.master.dbo.sp_executesql N'DBCC MEMORYSTATUS'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"doller" <sufianarif@.gmail.com> wrote in message
news:1142663795.418452.123220@.e56g2000cwe.googlegroups.com...
> Dear all,
> How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
> Computer.
> I dont want to connect my Query Analyser to the server i want to get
> information.
> thanx
> Doller
>|||HI Thanx
DBCC LOG
Dear all,
I would like to do an application. As frontend VB6 and as backend, of
course, Sql2k or sql25k. Anyway my main goal is that such application might
look for information stored inside .LDF files.
Searches and so on will be done by mean DBCC commands as dbcc log and all
that sort of stuff.
I am stuck on how do I for to stored the info provided for DBCC command to a
Sql table.
It doesn't work:
insert into log_x(current_lsn, operation,,,,,,) dbcc log(mydb,-1)
Thanks for any advice or thought.
This code and information are provided "as is" without warranty of any kind.
Please post statements as well as any error message in order to understand
better your request.I'm guessing your problem might be related to the fact that dbcc log(mydb,-1
)
command returns multiple records sets. One way to get around this is to get
the output of the DBCC command to a flat file then import that flat file int
o
a table.
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Enric" wrote:
> Dear all,
> I would like to do an application. As frontend VB6 and as backend, of
> course, Sql2k or sql25k. Anyway my main goal is that such application migh
t
> look for information stored inside .LDF files.
> Searches and so on will be done by mean DBCC commands as dbcc log and all
> that sort of stuff.
> I am stuck on how do I for to stored the info provided for DBCC command to
a
> Sql table.
> It doesn't work:
> insert into log_x(current_lsn, operation,,,,,,) dbcc log(mydb,-1)
> Thanks for any advice or thought.
> --
> This code and information are provided "as is" without warranty of any kin
d.
> Please post statements as well as any error message in order to understand
> better your request.
I would like to do an application. As frontend VB6 and as backend, of
course, Sql2k or sql25k. Anyway my main goal is that such application might
look for information stored inside .LDF files.
Searches and so on will be done by mean DBCC commands as dbcc log and all
that sort of stuff.
I am stuck on how do I for to stored the info provided for DBCC command to a
Sql table.
It doesn't work:
insert into log_x(current_lsn, operation,,,,,,) dbcc log(mydb,-1)
Thanks for any advice or thought.
This code and information are provided "as is" without warranty of any kind.
Please post statements as well as any error message in order to understand
better your request.I'm guessing your problem might be related to the fact that dbcc log(mydb,-1
)
command returns multiple records sets. One way to get around this is to get
the output of the DBCC command to a flat file then import that flat file int
o
a table.
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Enric" wrote:
> Dear all,
> I would like to do an application. As frontend VB6 and as backend, of
> course, Sql2k or sql25k. Anyway my main goal is that such application migh
t
> look for information stored inside .LDF files.
> Searches and so on will be done by mean DBCC commands as dbcc log and all
> that sort of stuff.
> I am stuck on how do I for to stored the info provided for DBCC command to
a
> Sql table.
> It doesn't work:
> insert into log_x(current_lsn, operation,,,,,,) dbcc log(mydb,-1)
> Thanks for any advice or thought.
> --
> This code and information are provided "as is" without warranty of any kin
d.
> Please post statements as well as any error message in order to understand
> better your request.
Friday, February 24, 2012
Dbcc Checkident
Dear All,
I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.
Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about DBCC CHECKIDENT:
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.
However I get the following:
User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
All users get the same message. This is an ASP.NET web app.
Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.
A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident
I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.
Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about DBCC CHECKIDENT:
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.
However I get the following:
User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
All users get the same message. This is an ASP.NET web app.
Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.
A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident
Subscribe to:
Posts (Atom)