Error running report: 2k5

Showing posts with label 2k5. Show all posts

Thursday, March 29, 2012

Error when trying to save or write to table

Hi,
I am doing a tuning exercise on a SQL 2K5 instance, I am encountering
a problem when Profiler goes to create a table. I have profiled
Profiler and have extracted the following code
CREATE TABLE [dbo].[con1] ([RowNumber] int IDENTITY(0,1) PRIMARY KEY,
[EventClass] int NULL,[Duration] bigint NULL,,
[TextData] ntext NULL,[SPID] int NULL,[BinaryData] image NULL,[CPU]
int NULL,[Reads] bigint NULL,[Writes] bigint NULL,[ApplicationName]
nvarchar(128) NULL,[ClientProcessID] int NULL,[DatabaseID] int NULL,
[DatabaseName] nvarchar(128) NULL,[EventSequence] int NULL,
[EventSubClass] int NULL,[HostName] nvarchar(128) NULL,[IntegerData]
int NULL,[IsSystem] int NULL,[LoginName] nvarchar(128) NULL,[LoginSid]
image NULL,[NTDomainName] nvarchar(128) NULL,[NTUserName]
nvarchar(128) NULL,[RequestID] int NULL,[ServerName] nvarchar(128)
NULL,[SessionLoginName] nvarchar(128) NULL,[StartTime] datetime NULL,
[TransactionID] bigint NULL)
As you can see, after Duration (the 3rd column), there is a double
comma inserted which of course is erroring.
This occurs when defining a table to save to prior to running the
trace, and defining a table after running the trace.
I have tried the standard profiler which appears to work fine.
I also updated to SP2 CTP (this is a test server) because it appeared
to be an issue simular to this http://support.microsoft.com/?
kbid=925335.
A quick response would be appreciated as I am a contractor and I am on
tight timescales.
Cheers
Steve
Steve,
I don't know how to fix the error with profiler but I recommend you not do
that in the fist place. Tracing to a table can be a huge performance hit and
can skew your results. It is recommended that you trace directly to a file
using sp_trace_create and then use fn_trace_gettable to retrieve the data.
You can do something like this to put the trace results in a table:
SELECT * INTO YourTable FROM fn_trace_gettable(xxx)
If you already have a trace in profiler you can save it to a file and do the
same.
Andrew J. Kelly SQL MVP
"Steve L" <steve_lawrenson@.tiscali.co.uk> wrote in message
news:1170411376.241148.280690@.v33g2000cwv.googlegr oups.com...
> Hi,
> I am doing a tuning exercise on a SQL 2K5 instance, I am encountering
> a problem when Profiler goes to create a table. I have profiled
> Profiler and have extracted the following code
> CREATE TABLE [dbo].[con1] ([RowNumber] int IDENTITY(0,1) PRIMARY KEY,
> [EventClass] int NULL,[Duration] bigint NULL,,
> [TextData] ntext NULL,[SPID] int NULL,[BinaryData] image NULL,[CPU]
> int NULL,[Reads] bigint NULL,[Writes] bigint NULL,[ApplicationName]
> nvarchar(128) NULL,[ClientProcessID] int NULL,[DatabaseID] int NULL,
> [DatabaseName] nvarchar(128) NULL,[EventSequence] int NULL,
> [EventSubClass] int NULL,[HostName] nvarchar(128) NULL,[IntegerData]
> int NULL,[IsSystem] int NULL,[LoginName] nvarchar(128) NULL,[LoginSid]
> image NULL,[NTDomainName] nvarchar(128) NULL,[NTUserName]
> nvarchar(128) NULL,[RequestID] int NULL,[ServerName] nvarchar(128)
> NULL,[SessionLoginName] nvarchar(128) NULL,[StartTime] datetime NULL,
> [TransactionID] bigint NULL)
> As you can see, after Duration (the 3rd column), there is a double
> comma inserted which of course is erroring.
> This occurs when defining a table to save to prior to running the
> trace, and defining a table after running the trace.
> I have tried the standard profiler which appears to work fine.
> I also updated to SP2 CTP (this is a test server) because it appeared
> to be an issue simular to this http://support.microsoft.com/?
> kbid=925335.
> A quick response would be appreciated as I am a contractor and I am on
> tight timescales.
> Cheers
> Steve
>
|||On 2 Feb, 14:11, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> Steve,
> I don't know how to fix the error with profiler but I recommend you not do
> that in the fist place. Tracing to a table can be a huge performance hit and
> can skew your results. It is recommended that you trace directly to a file
> using sp_trace_create and then use fn_trace_gettable to retrieve the data.
> You can do something like this to put the trace results in a table:
> SELECT * INTO YourTable FROM fn_trace_gettable(xxx)
> If you already have a trace in profiler you can save it to a file and do the
> same.
> --
> Andrew J. Kelly SQL MVP
> "Steve L" <steve_lawren...@.tiscali.co.uk> wrote in message
> news:1170411376.241148.280690@.v33g2000cwv.googlegr oups.com...
>
>
>
>
>
>
> - Show quoted text -
Hi Andrew
thanks for the response, I will use the method above as a workaround
and thanks for that information. It would be nice to know if this is a
recognised issue by MS and what their recommended workarounds are for
it.
Cheers
Steve L

Tuesday, March 27, 2012

Error when shrinking data file in SQL 2005

I've just upgraded my production server from 2K to 2K5, and I've got one
database that is way too big (almost 5 GB with 88% free space). I have no
problems shrinking the transaction log, but when I try to shrink the data
file, I get the following message:
"A severe error occurred on the current command. The results, if any,
should be discarded. File ID 1 of database ID 8 cannot be shrunk as it is
either being shrunk by another process or is empty. (Microsoft SQL Server,
Error: 0)"
At one point, I had autoshrink turned on for the database to handle the
transaction log, but this has never been a problem for the data file. During
my upgrade process, I detatched this database and copied it over to my test
server, where I am still having no problems. Help!!Hi
I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC CHECKDB?
John
"Gary" wrote:
> I've just upgraded my production server from 2K to 2K5, and I've got one
> database that is way too big (almost 5 GB with 88% free space). I have no
> problems shrinking the transaction log, but when I try to shrink the data
> file, I get the following message:
> "A severe error occurred on the current command. The results, if any,
> should be discarded. File ID 1 of database ID 8 cannot be shrunk as it is
> either being shrunk by another process or is empty. (Microsoft SQL Server,
> Error: 0)"
> At one point, I had autoshrink turned on for the database to handle the
> transaction log, but this has never been a problem for the data file. During
> my upgrade process, I detatched this database and copied it over to my test
> server, where I am still having no problems. Help!!
>|||"John Bell" wrote:
> I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?
John,
I ended up getting that problem solved (by detaching and reattaching the
database and then taking it offline and rebooting the server), but I've got
another somewhat related problem. In a maintenance plan I was doing a
rebuild on the indexes for the database. That maintenance plan never
completed (it hung up), and now my data file size is about 4 GB (it was about
500 MB) with basically no free space. I've run DBCC CHECKDB along with about
everything else I can think of, and I can't find any way to get that file
size back down.
Thanks!|||Hi Gary
Is this the data file or log file? If it is the log file you can use BACKUP
LOG ... WITH TRUNCATE_ONLY and then DBCC SHRINKFILE.
John
"Gary" wrote:
> "John Bell" wrote:
> > I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?
> John,
> I ended up getting that problem solved (by detaching and reattaching the
> database and then taking it offline and rebooting the server), but I've got
> another somewhat related problem. In a maintenance plan I was doing a
> rebuild on the indexes for the database. That maintenance plan never
> completed (it hung up), and now my data file size is about 4 GB (it was about
> 500 MB) with basically no free space. I've run DBCC CHECKDB along with about
> everything else I can think of, and I can't find any way to get that file
> size back down.
> Thanks!
>|||John,
No, it's the data file. I've tried to manually rebuild/reorganize
individual indexes, and that's not really working, either.
Gary
> Hi Gary
> Is this the data file or log file? If it is the log file you can use BACKUP
> LOG ... WITH TRUNCATE_ONLY and then DBCC SHRINKFILE.
> John
>
> "Gary" wrote:
> > "John Bell" wrote:
> >
> > > I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?
> >
> > John,
> >
> > I ended up getting that problem solved (by detaching and reattaching the
> > database and then taking it offline and rebooting the server), but I've got
> > another somewhat related problem. In a maintenance plan I was doing a
> > rebuild on the indexes for the database. That maintenance plan never
> > completed (it hung up), and now my data file size is about 4 GB (it was about
> > 500 MB) with basically no free space. I've run DBCC CHECKDB along with about
> > everything else I can think of, and I can't find any way to get that file
> > size back down.
> >
> > Thanks!
> >|||Hi
You can use sp_spaceused to see what is being used by the file, and DBCC
SHRINKFILE will shrink it. In general it is not a good idea to contunually
expand/shrink the files.
John
"Gary" wrote:
> John,
> No, it's the data file. I've tried to manually rebuild/reorganize
> individual indexes, and that's not really working, either.
> Gary
>
> > Hi Gary
> >
> > Is this the data file or log file? If it is the log file you can use BACKUP
> > LOG ... WITH TRUNCATE_ONLY and then DBCC SHRINKFILE.
> >
> > John
> >
> >
> > "Gary" wrote:
> >
> > > "John Bell" wrote:
> > >
> > > > I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?
> > >
> > > John,
> > >
> > > I ended up getting that problem solved (by detaching and reattaching the
> > > database and then taking it offline and rebooting the server), but I've got
> > > another somewhat related problem. In a maintenance plan I was doing a
> > > rebuild on the indexes for the database. That maintenance plan never
> > > completed (it hung up), and now my data file size is about 4 GB (it was about
> > > 500 MB) with basically no free space. I've run DBCC CHECKDB along with about
> > > everything else I can think of, and I can't find any way to get that file
> > > size back down.
> > >
> > > Thanks!
> > >|||i came accross this problem too. what did was to restart the service
and executed dbcc shrinkfile again. that time it succeeded.|||Hi,
I got the same error,
When i moved all of user tables and indexes out of primary FG and to a new
FG, my Primary is left with around 17GB of which less than a 1GB is actually
used, and when I ran shrink file on the only file on that FG, it took ages.
then I stoped it (in 2000 it would have taken max 2-3 hours, our SAN is not
that bad :-) ). when I ran it again I get the error described.
when I back up the db and restore it, then I can shrink the file.
this is a db in simle mode, dev environment. worried what happens when we go
to production with this.
error:
File ID 1 of database ID X cannot be shrunk as it is either being shrunk by
another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
--
CTS DBA
"XNMB" wrote:
> i came accross this problem too. what did was to restart the service
> and executed dbcc shrinkfile again. that time it succeeded.
>|||I tried detach and re-attach the database fie and run DBCC SHRINKFILE
But I wouldn't recommend shrinking. Here's an article by the great Tibor
Karaszi on database shrinking
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:630320F3-22A4-4D33-A245-54D14D4784FD@.microsoft.com...
> Hi,
> I got the same error,
> When i moved all of user tables and indexes out of primary FG and to a new
> FG, my Primary is left with around 17GB of which less than a 1GB is
> actually
> used, and when I ran shrink file on the only file on that FG, it took
> ages.
> then I stoped it (in 2000 it would have taken max 2-3 hours, our SAN is
> not
> that bad :-) ). when I ran it again I get the error described.
> when I back up the db and restore it, then I can shrink the file.
> this is a db in simle mode, dev environment. worried what happens when we
> go
> to production with this.
> error:
> File ID 1 of database ID X cannot be shrunk as it is either being shrunk
> by
> another process or is empty.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should
> be discarded.
> --
> CTS DBA
>
> "XNMB" wrote:
>> i came accross this problem too. what did was to restart the service
>> and executed dbcc shrinkfile again. that time it succeeded.
>>|||hi,
thanks for the reply.
I know what you mean, but what I don't understand is why shrinkfile wouldn't
work when there's so much free space in the file.
over 8GB
if the original database shrunk in SQL 2000 it works perfectly.
Thanks
--
CTS DBA
"bass_player [SBS-MVP]" wrote:
> I tried detach and re-attach the database fie and run DBCC SHRINKFILE
> But I wouldn't recommend shrinking. Here's an article by the great Tibor
> Karaszi on database shrinking
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:630320F3-22A4-4D33-A245-54D14D4784FD@.microsoft.com...
> > Hi,
> >
> > I got the same error,
> > When i moved all of user tables and indexes out of primary FG and to a new
> > FG, my Primary is left with around 17GB of which less than a 1GB is
> > actually
> > used, and when I ran shrink file on the only file on that FG, it took
> > ages.
> > then I stoped it (in 2000 it would have taken max 2-3 hours, our SAN is
> > not
> > that bad :-) ). when I ran it again I get the error described.
> >
> > when I back up the db and restore it, then I can shrink the file.
> > this is a db in simle mode, dev environment. worried what happens when we
> > go
> > to production with this.
> >
> > error:
> > File ID 1 of database ID X cannot be shrunk as it is either being shrunk
> > by
> > another process or is empty.
> > Msg 0, Level 11, State 0, Line 0
> > A severe error occurred on the current command. The results, if any,
> > should
> > be discarded.
> >
> > --
> > CTS DBA
> >
> >
> > "XNMB" wrote:
> >
> >> i came accross this problem too. what did was to restart the service
> >> and executed dbcc shrinkfile again. that time it succeeded.
> >>
> >>
>
>|||I had similar problem with the data file shrinking, but I think I solved the
problem safer way (without restoring or detaching the database and without
service restart):
1) Backup database log
2) Run sp_helpfile and note the size of the data file (6444160 KB, for
example)
3) Run ALTER DATABASE and use the file size number slightly bigger than
actual file size:
ALTER DATABASE TESTDB
MODIFY FILE
( NAME = TESTDB_DATA
, SIZE = 6444165KB )
4) Now shrink file command works:
USE TESTDB
GO
DBCC SHRINKFILE (N'TESTDB_DATA' , TRUNCATEONLY)
GO
Let me know if this worked for anybody else.
"CTS_DBA" wrote:
> hi,
> thanks for the reply.
> I know what you mean, but what I don't understand is why shrinkfile wouldn't
> work when there's so much free space in the file.
> over 8GB
> if the original database shrunk in SQL 2000 it works perfectly.
> Thanks
> --
> CTS DBA
>
> "bass_player [SBS-MVP]" wrote:
> > I tried detach and re-attach the database fie and run DBCC SHRINKFILE
> >
> > But I wouldn't recommend shrinking. Here's an article by the great Tibor
> > Karaszi on database shrinking
> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >
> > "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> > news:630320F3-22A4-4D33-A245-54D14D4784FD@.microsoft.com...
> > > Hi,
> > >
> > > I got the same error,
> > > When i moved all of user tables and indexes out of primary FG and to a new
> > > FG, my Primary is left with around 17GB of which less than a 1GB is
> > > actually
> > > used, and when I ran shrink file on the only file on that FG, it took
> > > ages.
> > > then I stoped it (in 2000 it would have taken max 2-3 hours, our SAN is
> > > not
> > > that bad :-) ). when I ran it again I get the error described.
> > >
> > > when I back up the db and restore it, then I can shrink the file.
> > > this is a db in simle mode, dev environment. worried what happens when we
> > > go
> > > to production with this.
> > >
> > > error:
> > > File ID 1 of database ID X cannot be shrunk as it is either being shrunk
> > > by
> > > another process or is empty.
> > > Msg 0, Level 11, State 0, Line 0
> > > A severe error occurred on the current command. The results, if any,
> > > should
> > > be discarded.
> > >
> > > --
> > > CTS DBA
> > >
> > >
> > > "XNMB" wrote:
> > >
> > >> i came accross this problem too. what did was to restart the service
> > >> and executed dbcc shrinkfile again. that time it succeeded.
> > >>
> > >>
> >
> >
> >

Thursday, March 29, 2012

Error when trying to save or write to table

Tuesday, March 27, 2012

Error when shrinking data file in SQL 2005

Error when shrinking data file in SQL 2005

Error running report

Blog Archive

About Me