check sql licence: running

Showing posts with label running. Show all posts

Sunday, March 25, 2012

Cleaning up goldmine Data

Hi,
We are running version 6.5 of Goldmine, which I am migrating to CRM
3.0 - I used access to get the data into SQL, but am now stuck with a
Last Name Column and a Name Column. To get the data into CRM Server
(which the boss is very keen to have done ASAP) I need to have First
Name and Last Name.
Is there an easy (!?) way to get the SQL server to remove the last
name data from name data? For example as it stands at the moment I
would have two cells:
Last Name: Smith
Name: John Smith
What I want to end up with is:
First Name: John
Last Name: Smith
Any suggestions gratefully received!
Many thanks
BlairIf there are only ever two names (one first name followed by one last name,
separated by a space or other distinct charactere) you can simply use
SUBSTRING and CHARINDEX (maybe even PATINDEX). Books Online has several
examples.
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Cleaning up goldmine Data

Hi,
We are running version 6.5 of Goldmine, which I am migrating to CRM
3.0 - I used access to get the data into SQL, but am now stuck with a
Last Name Column and a Name Column. To get the data into CRM Server
(which the boss is very keen to have done ASAP) I need to have First
Name and Last Name.
Is there an easy (!?) way to get the SQL server to remove the last
name data from name data? For example as it stands at the moment I
would have two cells:
Last Name: Smith
Name: John Smith
What I want to end up with is:
First Name: John
Last Name: Smith
Any suggestions gratefully received!
Many thanks
Blair
If there are only ever two names (one first name followed by one last name,
separated by a space or other distinct charactere) you can simply use
SUBSTRING and CHARINDEX (maybe even PATINDEX). Books Online has several
examples.
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Cleaning up goldmine Data

Hi,
We are running version 6.5 of Goldmine, which I am migrating to CRM
3.0 - I used access to get the data into SQL, but am now stuck with a
Last Name Column and a Name Column. To get the data into CRM Server
(which the boss is very keen to have done ASAP) I need to have First
Name and Last Name.
Is there an easy (!?) way to get the SQL server to remove the last
name data from name data? For example as it stands at the moment I
would have two cells:
Last Name: Smith
Name: John Smith
What I want to end up with is:
First Name: John
Last Name: Smith
Any suggestions gratefully received!
Many thanks
BlairIf there are only ever two names (one first name followed by one last name,
separated by a space or other distinct charactere) you can simply use
SUBSTRING and CHARINDEX (maybe even PATINDEX). Books Online has several
examples.
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Thursday, March 22, 2012

CLASSPATH variable?

I'm running Win/2003 Server, SQL Server 2000, and Sun Java 1.5.
MS article # 3323200 "How to get started with ms jdbc" says I need to set
the CLASSPATH variable to point to my jdbc.jar file location.
Where do I go to make this setting. Apparently it is not set like the PATH
variable.
Thanks
Jim Shaw wrote:
> I'm running Win/2003 Server, SQL Server 2000, and Sun Java 1.5.
> MS article # 3323200 "How to get started with ms jdbc" says I need
to set
> the CLASSPATH variable to point to my jdbc.jar file location.
> Where do I go to make this setting. Apparently it is not set like
the PATH
> variable.
You have to add the driver jar (or jars, in the case of the MS driver
-- msutil.jar, mssqlserver.jar, msbase.jar) to the CLASSPATH system
variable. It's exactly the same as setting the PATH system variable
(the only difference might be that the CLASSPATH variable is not yet
defined, so you should add it).
I would also recommend jTDS ( http://jtds.sourceforge.net/ ) instead of
the MS driver. But that's just my oppinion.
Alin,
The jTDS Project.
|||Thanks Alin.
I initally tried to use jtds. I like its features a lot. I even joined
sourceforge and downloaded jtds, but I could not find any documentation on
how to install and use it. I'm an experienced programmer, but new to both
SQL Server and the Java language. I'm in the process of self teaching them
to myself, so I need some simple documentation to get started. I'm finding
that at MS, so I downloaded their driver for starters. Does jtds have such
documentation, or must I complete my Java learnings and then read the jtds
source code to figure out how to use it?
Jim
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1107866811.028206.24460@.g14g2000cwa.googlegro ups.com...
> Jim Shaw wrote:
> to set
> the PATH
> You have to add the driver jar (or jars, in the case of the MS driver
> -- msutil.jar, mssqlserver.jar, msbase.jar) to the CLASSPATH system
> variable. It's exactly the same as setting the PATH system variable
> (the only difference might be that the CLASSPATH variable is not yet
> defined, so you should add it).
> I would also recommend jTDS ( http://jtds.sourceforge.net/ ) instead of
> the MS driver. But that's just my oppinion.
> Alin,
> The jTDS Project.
>
|||Jim Shaw wrote:
> Does jtds have such
> documentation, or must I complete my Java learnings and then read the
jtds
> source code to figure out how to use it?
jTDS has all the documentation you need to get started with it (both
online and in the distribution package -- the web site contents are
part of the distribution). There are also README files for each feature
that needs special configuration/installation, such as SSL, Windows
Single Sign On or XA distributed transactions. Start with the FAQ:
http://jtds.sourceforge.net/faq.html
Alin.

class that uses sql server isnt releasing memory

I'm running into a problem where the class I'm running seems to eat up a lot of memory with sql server. When it's done running, the memory usage never goes down in taskmanager. I can't figure out where the memory leak might be. Here's the code that is being called. Does anyone see a reason why it would continue to eat memory as it runs and then not release it? Thanks.

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;namespace QueryLoadTester{ class JobSeeker { private string ConStr = @."Data Source=server;Initial Catalog=db;Integrated Security=True"; public JobSeeker() { } #region UpdateJobSeeker public void UpdateJobSeeker(string JobSeekerId) { string qry = "SELECT top 100 dbo.JobSeeker.JobSeekerID, dbo.JobSeeker.SiteId, dbo.JobSeeker.PositionTitle, dbo.JobSeeker.LocationID, dbo.JobSeeker.CurrentSalary, " + "dbo.JobSeeker.DesiredSalary, dbo.JobSeeker.MinSalary, dbo.JobSeeker.CurrentHourly, dbo.JobSeeker.MinHourly, dbo.JobSeeker.Comments, " + "dbo.JobSeeker.Resume, dbo.JobSeeker.WillRelocate, dbo.JobSeeker.DateAdded, dbo.JobSeeker.LastModified FROM dbo.JobSeeker " + "where dbo.JobSeeker.Active=1 and dbo.JobSeeker.samplejobseeker=0 "; if (JobSeekerId.Length > 0) qry += " and dbo.JobSeeker.JobSeekerID=" + JobSeekerId; qry += " and dbo.JobSeeker.JobSeekerID not in (Select JobSeekerId from JobSeekerFullTextSearch)"; SqlConnection cnInsert = new SqlConnection(ConStr); SqlDataAdapter adp = new SqlDataAdapter(qry, cnInsert); cnInsert.Open(); DataSet dsJobSeekers = new DataSet(); adp.Fill(dsJobSeekers); adp.Dispose(); adp = null; if (dsJobSeekers.Tables[0].Rows.Count > 0) { string jid = string.Empty; string degree, degreegroup; StringBuilder sb = new StringBuilder(); SqlCommand cmdInsert = new SqlCommand(); cmdInsert.Connection = cnInsert; foreach (DataRow dr in dsJobSeekers.Tables[0].Rows) { jid = dr["JobSeekerID"].ToString(); if (JobSeekerId.Length > 0) { cmdInsert.CommandText = "Delete from JobSeekerFullTextSearch where JobSeekerId=" + JobSeekerId; cmdInsert.ExecuteNonQuery(); } DataSet dsExtras = GetJobSeekerExtras(jid, cnInsert); SqlTransaction trans = cnInsert.BeginTransaction(); cmdInsert.Transaction = trans; degree = degreegroup = string.Empty; #region insert record into fulltextsearch try { sb.Remove(0, sb.Length); sb.Append("Insert into JobSeekerFullTextSearch (JobSeekerId, PositionTitle, LocationID, CurrentSalary, "); sb.Append("DesiredSalary, MinSalary, CurrentHourly, MinHourly, CommentsResume, SiteId, WillRelocate, DateAdded, LastModified) values ("); sb.Append(jid); sb.Append(",'"); sb.Append(dr["PositionTitle"].ToString().Replace("'", "''")); sb.Append("',"); sb.Append("'"); sb.Append(dr["LocationID"].ToString().Replace("'", "''")); sb.Append("',"); sb.Append(Nullify(dr["CurrentSalary"])); sb.Append(","); sb.Append(Nullify(dr["DesiredSalary"])); sb.Append(","); sb.Append(Nullify(dr["MinSalary"])); sb.Append(","); sb.Append(Nullify(dr["CurrentHourly"])); sb.Append(","); sb.Append(Nullify(dr["MinHourly"])); sb.Append(",'"); sb.Append(dr["Comments"].ToString().Replace("'", "''")); sb.Append(" "); sb.Append(dr["Resume"].ToString().Replace("'", "''")); sb.Append("',"); sb.Append(dr["SiteId"].ToString()); sb.Append(","); sb.Append(Convert.ToInt32(Convert.ToBoolean(dr["WillRelocate"].ToString()))); sb.Append(",'"); sb.Append(dr["DateAdded"].ToString()); sb.Append("','"); sb.Append(dr["LastModified"].ToString()); sb.Append("')"); cmdInsert.CommandText = sb.ToString(); cmdInsert.ExecuteNonQuery(); #region dsExtras insert //degree info if (dsExtras.Tables.Count > 0 && dsExtras.Tables[0].Rows.Count > 0) { degree = dsExtras.Tables[0].Rows[0][0].ToString(); degreegroup = dsExtras.Tables[0].Rows[0][1].ToString(); if (degree.Length > 0 || degreegroup.Length > 0) { sb.Remove(0, sb.Length); sb.Append("Update JobSeekerFullTextSearch set DegreeLevel='"); sb.Append(degree); sb.Append("', DegreeLevelGroup="); sb.Append(degreegroup); sb.Append(" where JobSeekerId="); sb.Append(jid); cmdInsert.CommandText = sb.ToString(); cmdInsert.ExecuteNonQuery(); } } //disciplines info if (dsExtras.Tables.Count > 1 && dsExtras.Tables[1].Rows.Count > 0) { sb.Remove(0, sb.Length); foreach (DataRow d in dsExtras.Tables[1].Rows) { sb.Append(d[0].ToString()); sb.Append(","); } if (sb.ToString().Length > 0) { cmdInsert.CommandText = "Update JobSeekerFullTextSearch set DisciplineIdList='" + sb.ToString().Substring(0, sb.ToString().Length - 1) + "' where JobSeekerId=" + jid; cmdInsert.ExecuteNonQuery(); } } //industries info if (dsExtras.Tables.Count > 2 && dsExtras.Tables[2].Rows.Count > 0) { sb.Remove(0, sb.Length); foreach (DataRow d in dsExtras.Tables[2].Rows) { sb.Append(d[0].ToString()); sb.Append(","); } if (sb.ToString().Length > 0) { cmdInsert.CommandText = "Update JobSeekerFullTextSearch set IndustryIdList='" + sb.ToString().Substring(0, sb.ToString().Length - 1) + "' where JobSeekerId=" + jid; cmdInsert.ExecuteNonQuery(); } } //jobtypes info if (dsExtras.Tables.Count > 3 && dsExtras.Tables[3].Rows.Count > 0) { sb.Remove(0, sb.Length); foreach (DataRow d in dsExtras.Tables[3].Rows) { sb.Append(d[0].ToString()); sb.Append(","); } if (sb.ToString().Length > 0) { cmdInsert.CommandText = "Update JobSeekerFullTextSearch set JobTypeIdList='" + sb.ToString().Substring(0, sb.ToString().Length - 1) + "' where JobSeekerId=" + jid; cmdInsert.ExecuteNonQuery(); } } #endregion trans.Commit(); Console.WriteLine("Insert for " + jid); } catch (Exception exc) { trans.Rollback(); Console.WriteLine(jid + " - " + exc.ToString()); } finally { trans.Dispose(); trans = null; } #endregion dsExtras.Clear(); dsExtras.Dispose(); dsExtras = null; }//end foreach cmdInsert.Dispose(); cmdInsert = null; } cnInsert.Close(); cnInsert.Dispose(); cnInsert = null; GC.Collect(); } #endregion #region GetJobSeekerExtras private static DataSet GetJobSeekerExtras(string JobSeekerId, SqlConnection cn) { string qry = "Select JobSeekerDegree.DegreeLevel, VDegreeLevels.DegreeGroup from JobSeekerDegree inner join " + " VDegreeLevels on JobSeekerDegree.DegreeLevel=VDegreeLevels.DegreeLevel where JobSeekerId=" + JobSeekerId + ";" + "Select DisciplineID from JobSeekerDiscipline where JobSeekerId=" + JobSeekerId + ";" + "Select IndustryID from JobSeekerIndustry where JobSeekerID=" + JobSeekerId + ";" + "Select JobTypeID from JobSeekerJobType where JobSeekerID=" + JobSeekerId; DataSet ds = new DataSet(); SqlDataAdapter adp = new SqlDataAdapter(qry, cn); adp.Fill(ds); adp.Dispose(); adp = null; return ds; } #endregion #region Nullify private static string Nullify(object p) { if (p != System.DBNull.Value) return p.ToString(); else return "null"; } #endregion }}

GC.Collect(); is killing your code, you DON'T call that method in C# the CLR(common langauge runtime) calls it, any code calling GC.Collect(), must be C++ in .NET. The correct way to do it is very easy in C# wrap all those code blocks with the second USING statement that calls dispose automatically. If you must do it manually then call Dispose Bool. Hope this helps.|||GC.Collect doesn't seem to make a difference when it all runs. I was just trying that to see if it would release all or some of the memory when the class terminated. I'll try the using statement, but since I'm already disposing of all the objects, isn't that basically doing the same thing?|||

No it is not to quote C# and Asp.net expert Jeff Prosise GC.Collect can do really nasty stuff to your application. The USING statement on the other hand calls Dispose automatically if you place it after the Preprocessor directives. You can call Dispose Bool or Call Close and then Dispose. Hope this helps.

|||I just updated the code to use the using statements, but the memory is still not being released. Any other ideas? Thanks.|||

The first thing to do is try click on show execution cost before running your SQL statements in either Management studio or Query Analyzer. If it did not show high cost then try the profiler under tools to run trace on the statements to see what is killing the code. Another option is to open the connection late and close it quickly. Hope this helps.

Monday, March 19, 2012

Choosing the most frequent

There must be a way to do this simply. We're running SQL Server 2000. I'm looking for some generic SQL statement that I can apply.

If I have a table with a person column and a location column and multiple records for the same person / locatioin combination, how do I select the person with the location they most frequently visited? Say George visits Mexico 5 times, and the Bahamas twice and costa rica once. I would have 8 records in my table for George. The data looks something like this:

George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Bahamas
George/Bahamas
George/Costa Rica
Ben/Brazil
Ben/Brazil
Ben/Peru

The results would be:

George/Mexico
Ben/Brazil

Thanks!

Myles

With SQL Server 2005, it's fairly simple. Here's an example using the Northwind sample database, since you didn't provide your table definition:

with T(CustomerID, EmployeeID, rk) as (
select
CustomerID,
EmployeeID,
row_number() over (partition by CustomerID order by count(*) desc)
from Northwind..Orders
group by CustomerID, EmployeeID
)
select
CustomerID,
EmployeeID
from T
where rk = 1

If you want to include ties for most frequent location per person, use rank() instead of row_number().

With SQL Server 2000, it's a little less efficient - the same query would look like this:
select
CustomerID,
EmployeeID
from Northwind..Orders as O1
where CustomerID like 'A%'
group by CustomerID, EmployeeID
having count(*) = (
select max(ct) from (
select count(*) as ct
from Northwind..Orders as O2
where O2.CustomerID = O1.CustomerID
group by CustomerID, EmployeeID
) as Cts
)

Steve Kass
Drew University
www.stevekass.com|||

create table visits
(
visit varchar (200)
)
insert visits(visit) values ('George/Mexico')
insert visits(visit) values ('George/Mexico')
insert visits(visit) values ('George/Mexico')
insert visits(visit) values ('George/Mexico')
insert visits(visit) values('George/Mexico')
insert visits(visit) values('George/Mexico')
insert visits(visit) values ('George/Bahamas')
insert visits(visit) values ('George/Bahamas')
insert visits(visit) values ('George/Costa Rica')
insert visits(visit) values ('Ben/Brazil')
insert visits(visit) values('Ben/Brazil')
insert visits(visit) values ('Ben/Peru')

select left(visit,(CHARINDEX('/',visit,0)-1))AS person,
right(visit,len(visit)-1-len(left(visit,(CHARINDEX('/',visit,0)-1)))) as places
into #tempx
from visits

select * from #tempx

select person , places, count(*) as counts into #tempy from #tempx
group by person, places
order by person,counts desc

select a.person,b.places, a.counts from
(
selecT person, max(counTs)as counts from #tempy group by person )
as
a
join

(
select * from #tempy
) as b
on a.person=b.person and a.counts=b.counts

--drop table visits
--drop table #tempx
--drop table #tempy

|||Thank you joeydj!

Choosing RAID level

Hi,
In your opinion what would be a better choice for running Microsoft SQL
Server 2000 on Microsoft Windows 2000 Advanced Server:
1) 4-disk RAID10 for OS and tempdb,
2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
Why?
The server will be dedicated only for running the SQL Server so paging
shouldn't be an issue.
Many thanks,
Oskar
You might want to start here:
http://b.wunder.home.comcast.net/18960.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:51D6E5C7-88C3-45B4-96A9-14C3CCDCCD2C@.microsoft.com...
> Hi,
> In your opinion what would be a better choice for running Microsoft SQL
> Server 2000 on Microsoft Windows 2000 Advanced Server:
> 1) 4-disk RAID10 for OS and tempdb,
> 2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
> Why?
> The server will be dedicated only for running the SQL Server so paging
> shouldn't be an issue.
> --
> Many thanks,
> Oskar
>

Choosing RAID level

Hi,
In your opinion what would be a better choice for running Microsoft SQL
Server 2000 on Microsoft Windows 2000 Advanced Server:
1) 4-disk RAID10 for OS and tempdb,
2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
Why?
The server will be dedicated only for running the SQL Server so paging
shouldn't be an issue.
Many thanks,
OskarYou might want to start here:
http://b.wunder.home.comcast.net/18960.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:51D6E5C7-88C3-45B4-96A9-14C3CCDCCD2C@.microsoft.com...
> Hi,
> In your opinion what would be a better choice for running Microsoft SQL
> Server 2000 on Microsoft Windows 2000 Advanced Server:
> 1) 4-disk RAID10 for OS and tempdb,
> 2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
> Why?
> The server will be dedicated only for running the SQL Server so paging
> shouldn't be an issue.
> --
> Many thanks,
> Oskar
>

Choosing RAID level

Hi,
In your opinion what would be a better choice for running Microsoft SQL
Server 2000 on Microsoft Windows 2000 Advanced Server:
1) 4-disk RAID10 for OS and tempdb,
2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
Why?
The server will be dedicated only for running the SQL Server so paging
shouldn't be an issue.
--
Many thanks,
OskarYou might want to start here:
http://b.wunder.home.comcast.net/18960.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:51D6E5C7-88C3-45B4-96A9-14C3CCDCCD2C@.microsoft.com...
> Hi,
> In your opinion what would be a better choice for running Microsoft SQL
> Server 2000 on Microsoft Windows 2000 Advanced Server:
> 1) 4-disk RAID10 for OS and tempdb,
> 2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
> Why?
> The server will be dedicated only for running the SQL Server so paging
> shouldn't be an issue.
> --
> Many thanks,
> Oskar
>

Sunday, March 11, 2012

Chkdsk and MS SQL Server 7

Dear Subscribers,
My SQL Server 7 is running on Win NT 4.0 SP6a system with RAID5. I suspect
that one of the system disk volumes has a problem with its file system, and
I was advised to check that by running CHKDSK /F /R. The problem is the
database files are stored on this volume and I'm not sure what kind of
effect the disk checker will have on them if it finds any errors. Will
everything be OK?
Many thanks!Hi
I am not sure what effect it will have as the files will be in use, and if
there was any corruption SQL Server may have problems anyhow!
I suggest that you back up the databases before trying this and make sure
they are retained on some reliable media.
John
"Oskars Salnins" <osalnins@.inbox.lv> wrote in message
news:uwmiilFVDHA.532@.TK2MSFTNGP10.phx.gbl...
> Dear Subscribers,
> My SQL Server 7 is running on Win NT 4.0 SP6a system with RAID5. I suspect
> that one of the system disk volumes has a problem with its file system,
and
> I was advised to check that by running CHKDSK /F /R. The problem is the
> database files are stored on this volume and I'm not sure what kind of
> effect the disk checker will have on them if it finds any errors. Will
> everything be OK?
>
> Many thanks!
>|||Thanks John.
Surely I would stop SQL server before carrying out the check. This far I
didn't notice any problems with SQL Server itself (i.e. DBCC CHECKDB shows
all DB's are clean). I think that means DB files aren't affected.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f240abf$0$10766$afc38c87@.news.easynet.co.uk...
> Hi
> I am not sure what effect it will have as the files will be in use, and if
> there was any corruption SQL Server may have problems anyhow!
> I suggest that you back up the databases before trying this and make sure
> they are retained on some reliable media.
> John
> "Oskars Salnins" <osalnins@.inbox.lv> wrote in message
> news:uwmiilFVDHA.532@.TK2MSFTNGP10.phx.gbl...
> > Dear Subscribers,
> >
> > My SQL Server 7 is running on Win NT 4.0 SP6a system with RAID5. I
suspect
> > that one of the system disk volumes has a problem with its file system,
> and
> > I was advised to check that by running CHKDSK /F /R. The problem is the
> > database files are stored on this volume and I'm not sure what kind of
> > effect the disk checker will have on them if it finds any errors. Will
> > everything be OK?
> >
> >
> > Many thanks!
> >
> >
>|||Oskars,
> Surely I would stop SQL server before carrying out the check.
Yes you would.
> This far I
> didn't notice any problems with SQL Server itself (i.e. DBCC CHECKDB shows
> all DB's are clean). I think that means DB files aren't affected.
What symptoms are you getting that makes you think a chkdsk is needed?
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Neil,
The system's paging file and DB files are stored on the same disk volume,
and I got into paging file corruption issue as discussed in this KB article:
http://support.microsoft.com/default.aspx?kbid=216446
which suggests to run chkdsk /F /R to see if it corrects the problem. I've
already followed the other suggestion from the article - rebuilt the paging
file and moved it to another disk volume. Nevertheless, I'd also like to
know what caused the corruption, and fix that. Server hardware is clean so I
suspect the file system. Besides, if the paging file got corrupt then the
same thing could probably happen to DB files as well.
"Neil Pike" <neilpike@.compuserve.com> wrote in message
news:VA.000060fd.0fe50a5f@.compuserve.com...
> Oskars,
> > Surely I would stop SQL server before carrying out the check.
> Yes you would.
> > This far I
> > didn't notice any problems with SQL Server itself (i.e. DBCC CHECKDB
shows
> > all DB's are clean). I think that means DB files aren't affected.
> What symptoms are you getting that makes you think a chkdsk is needed?
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>|||Oskars - the the chkdsk /f /r output say that it found and fixed problems?
The Q article you listed is a tad vague - just because you got an A or a 1E
blue screen doesn't mean you had a corrupt page file. This is one of 100's or
1000's of reasons for the same blue screen...
> The system's paging file and DB files are stored on the same disk volume,
> and I got into paging file corruption issue as discussed in this KB article:
> http://support.microsoft.com/default.aspx?kbid=216446
> which suggests to run chkdsk /F /R to see if it corrects the problem. I've
> already followed the other suggestion from the article - rebuilt the paging
> file and moved it to another disk volume. Nevertheless, I'd also like to
> know what caused the corruption, and fix that. Server hardware is clean so I
> suspect the file system. Besides, if the paging file got corrupt then the
> same thing could probably happen to DB files as well.
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

Thursday, March 8, 2012

checktable repair_rebuild taking long time

Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.
I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com.
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.
|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>
|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how
>
|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and[vbcol=seagreen]
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command
> (how
>

checktable repair_rebuild taking long time

Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at com." target="_blank">www.sqlmag.
com.
Check the delta of the CPU usage in sysprocesses to determine how much progr
ess the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how[vbcol=seagreen]
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command[vbcol=seagreen]
> (how
>

checktable repair_rebuild taking long time

Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > Hi! I am running dbcc checktable with repair_rebuild option for a table
of
> > 121 Million record (about 150 GB) in size and its already running for 74
> > hours and still going. Table had Keys out of order on page (1:11667248),
> > slots 5 and 6 (Which was clustered Index).
> > Could anyone tell me how long does it normally take to run this command
> for
> > table of this size? Is there any way we can see the status of process
(how
> > far it has gone percentage wise)?
> > Environment:
> > Sql 2k SP2 running on Wi2k Advanced server
> > 8 CPU 2.7 GH and 8 GB RAM.
> >
> >
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> > It's rebuilding the clustered index and all the non-clustered indexes as
> > part of the repair - depending on how much and the distribution of free
> > space this could take a while but I wouldn't expect it to take that
long.
> > What was the exact output from checkdb before you re-ran with repair?
> You'd
> > have been much better off restoring from your backups (which is the
> > recommeneded strategy)
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > > Hi! I am running dbcc checktable with repair_rebuild option for a
table
> of
> > > 121 Million record (about 150 GB) in size and its already running for
74
> > > hours and still going. Table had Keys out of order on page
(1:11667248),
> > > slots 5 and 6 (Which was clustered Index).
> > > Could anyone tell me how long does it normally take to run this
command
> > for
> > > table of this size? Is there any way we can see the status of process
> (how
> > > far it has gone percentage wise)?
> > > Environment:
> > > Sql 2k SP2 running on Wi2k Advanced server
> > > 8 CPU 2.7 GH and 8 GB RAM.
> > >
> > >
> >
> >
>

checktable error

I receive the following after running checktable:
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'RequestWords' (object ID 910678342).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (synergy.dbo.RequestWords ).
I have a backup and would like to know the best way to proceed to correct
the consistency errors...also where can I find the sp_repair_allow_data_loss?
Thanks> I have a backup and would like to know the best way to proceed to correct
> the consistency errors...also where can I find the
> sp_repair_allow_data_loss?
Restoring from your last known good backup is usually the best option rather
than allowing data loss. There may be other methods depending on the
specific types of corruption and affected object types.
REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC CHECKTABLE
commands. See the Books online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
>I receive the following after running checktable:
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'RequestWords' (object ID 910678342).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> I have a backup and would like to know the best way to proceed to correct
> the consistency errors...also where can I find the
> sp_repair_allow_data_loss?
> Thanks|||Not sure when my last known good is and it could take quite awhile to try and
find it...is there a way to see what data would be lost first before running
REPAIR_ALLOW_DATA_LOSS ?
"Dan Guzman" wrote:
> > I have a backup and would like to know the best way to proceed to correct
> > the consistency errors...also where can I find the
> > sp_repair_allow_data_loss?
> Restoring from your last known good backup is usually the best option rather
> than allowing data loss. There may be other methods depending on the
> specific types of corruption and affected object types.
> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC CHECKTABLE
> commands. See the Books online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
> >I receive the following after running checktable:
> >
> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> > 'RequestWords' (object ID 910678342).
> > repair_allow_data_loss is the minimum repair level for the errors found by
> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> >
> > I have a backup and would like to know the best way to proceed to correct
> > the consistency errors...also where can I find the
> > sp_repair_allow_data_loss?
> >
> > Thanks
>|||> Not sure when my last known good is and it could take quite awhile to try
> and
> find it...is there a way to see what data would be lost first before
> running
> REPAIR_ALLOW_DATA_LOSS ?
The DBCC CHECKTABLE output should specify the problem pages and error
details. You can use DBCC PAGE (google is your friend) to examine the page
contents and get an idea of data might be affected. I don't believe it's
possible to provide exact details of lost data beforehand; I would think
DBCC could recover the data without loss if that were possible.
You might try posting the DBCC error details in case we can provide an
alternate solution. You could also try running the DBCC CHECKTABLE WITH
REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to correct
the problem and identify lost data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
> Not sure when my last known good is and it could take quite awhile to try
> and
> find it...is there a way to see what data would be lost first before
> running
> REPAIR_ALLOW_DATA_LOSS ?
> "Dan Guzman" wrote:
>> > I have a backup and would like to know the best way to proceed to
>> > correct
>> > the consistency errors...also where can I find the
>> > sp_repair_allow_data_loss?
>> Restoring from your last known good backup is usually the best option
>> rather
>> than allowing data loss. There may be other methods depending on the
>> specific types of corruption and affected object types.
>> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
>> CHECKTABLE
>> commands. See the Books online for details.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
>> >I receive the following after running checktable:
>> >
>> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
>> > 'RequestWords' (object ID 910678342).
>> > repair_allow_data_loss is the minimum repair level for the errors found
>> > by
>> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>> >
>> > I have a backup and would like to know the best way to proceed to
>> > correct
>> > the consistency errors...also where can I find the
>> > sp_repair_allow_data_loss?
>> >
>> > Thanks|||Dan, I am going to paste the following detail, the table is a simple 2 column
that is used by a full text indexing job, it seems, the first field is just
text and the second is a guid...I went to record 128, but saw nothing
funny.....appreciate all your help
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 910678342, index ID 0: Page (1:369538) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 910678342, index ID 0, page (1:369538), row 125. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 2088 and 43.
DBCC results for 'RequestWords'.
There are 1313272 rows in 11072 pages for object 'RequestWords'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'RequestWords' (object ID 910678342).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (synergy.dbo.RequestWords ).
"Dan Guzman" wrote:
> > Not sure when my last known good is and it could take quite awhile to try
> > and
> > find it...is there a way to see what data would be lost first before
> > running
> > REPAIR_ALLOW_DATA_LOSS ?
> The DBCC CHECKTABLE output should specify the problem pages and error
> details. You can use DBCC PAGE (google is your friend) to examine the page
> contents and get an idea of data might be affected. I don't believe it's
> possible to provide exact details of lost data beforehand; I would think
> DBCC could recover the data without loss if that were possible.
> You might try posting the DBCC error details in case we can provide an
> alternate solution. You could also try running the DBCC CHECKTABLE WITH
> REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to correct
> the problem and identify lost data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
> > Not sure when my last known good is and it could take quite awhile to try
> > and
> > find it...is there a way to see what data would be lost first before
> > running
> > REPAIR_ALLOW_DATA_LOSS ?
> >
> > "Dan Guzman" wrote:
> >
> >> > I have a backup and would like to know the best way to proceed to
> >> > correct
> >> > the consistency errors...also where can I find the
> >> > sp_repair_allow_data_loss?
> >>
> >> Restoring from your last known good backup is usually the best option
> >> rather
> >> than allowing data loss. There may be other methods depending on the
> >> specific types of corruption and affected object types.
> >>
> >> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
> >> CHECKTABLE
> >> commands. See the Books online for details.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
> >> >I receive the following after running checktable:
> >> >
> >> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> >> > 'RequestWords' (object ID 910678342).
> >> > repair_allow_data_loss is the minimum repair level for the errors found
> >> > by
> >> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> >> >
> >> > I have a backup and would like to know the best way to proceed to
> >> > correct
> >> > the consistency errors...also where can I find the
> >> > sp_repair_allow_data_loss?
> >> >
> >> > Thanks
> >>
>|||The DBCC error indicates bad column offsets, which will prevent the problem
row from being parsed. I think DBCC will need to delete that row to remove
the error. You can probably fix the error with a normal delete command if
you can identify the key value of the problem row from the raw DBCC PAGE
output.
If you haven't already done so, you might try DBCC PAGE print option 3
(http://support.microsoft.com/kb/83065) to print the individual column
values (example below). I don't know how this will behave with bad column
offsets but I'm curious to find out, if you don't mind giving that a try.
The DBCC PAGE dump is probably the only data you'll have to salvage the
deleted row, unless you contact Microsoft PSS.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:042EAEE7-D5C2-4847-9AF9-DFC92E3885FB@.microsoft.com...
> Dan, I am going to paste the following detail, the table is a simple 2
> column
> that is used by a full text indexing job, it seems, the first field is
> just
> text and the second is a guid...I went to record 128, but saw nothing
> funny.....appreciate all your help
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 910678342, index ID 0: Page (1:369538) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 910678342, index ID 0, page (1:369538), row 125.
> Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2088 and 43.
> DBCC results for 'RequestWords'.
> There are 1313272 rows in 11072 pages for object 'RequestWords'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'RequestWords' (object ID 910678342).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>
>
> "Dan Guzman" wrote:
>> > Not sure when my last known good is and it could take quite awhile to
>> > try
>> > and
>> > find it...is there a way to see what data would be lost first before
>> > running
>> > REPAIR_ALLOW_DATA_LOSS ?
>> The DBCC CHECKTABLE output should specify the problem pages and error
>> details. You can use DBCC PAGE (google is your friend) to examine the
>> page
>> contents and get an idea of data might be affected. I don't believe it's
>> possible to provide exact details of lost data beforehand; I would think
>> DBCC could recover the data without loss if that were possible.
>> You might try posting the DBCC error details in case we can provide an
>> alternate solution. You could also try running the DBCC CHECKTABLE WITH
>> REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to correct
>> the problem and identify lost data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
>> > Not sure when my last known good is and it could take quite awhile to
>> > try
>> > and
>> > find it...is there a way to see what data would be lost first before
>> > running
>> > REPAIR_ALLOW_DATA_LOSS ?
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > I have a backup and would like to know the best way to proceed to
>> >> > correct
>> >> > the consistency errors...also where can I find the
>> >> > sp_repair_allow_data_loss?
>> >>
>> >> Restoring from your last known good backup is usually the best option
>> >> rather
>> >> than allowing data loss. There may be other methods depending on the
>> >> specific types of corruption and affected object types.
>> >>
>> >> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
>> >> CHECKTABLE
>> >> commands. See the Books online for details.
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
>> >> >I receive the following after running checktable:
>> >> >
>> >> > CHECKTABLE found 0 allocation errors and 2 consistency errors in
>> >> > table
>> >> > 'RequestWords' (object ID 910678342).
>> >> > repair_allow_data_loss is the minimum repair level for the errors
>> >> > found
>> >> > by
>> >> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>> >> >
>> >> > I have a backup and would like to know the best way to proceed to
>> >> > correct
>> >> > the consistency errors...also where can I find the
>> >> > sp_repair_allow_data_loss?
>> >> >
>> >> > Thanks
>> >>|||Dan, sorry took so long to get back...
I was able to locate the corrupt record in the referenced table...it was a
null id that had a guid assigned to it from another table...very weird...the
delete statement would not work on it....had to finally run
repair_allow_data_loss and then repair_fast. I lost 132 records but they
were all bogus references to the corruption...so it turned out all good.
Thanks for your assistance.
"Dan Guzman" wrote:
> The DBCC error indicates bad column offsets, which will prevent the problem
> row from being parsed. I think DBCC will need to delete that row to remove
> the error. You can probably fix the error with a normal delete command if
> you can identify the key value of the problem row from the raw DBCC PAGE
> output.
> If you haven't already done so, you might try DBCC PAGE print option 3
> (http://support.microsoft.com/kb/83065) to print the individual column
> values (example below). I don't know how this will behave with bad column
> offsets but I'm curious to find out, if you don't mind giving that a try.
> The DBCC PAGE dump is probably the only data you'll have to salvage the
> deleted row, unless you contact Microsoft PSS.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:042EAEE7-D5C2-4847-9AF9-DFC92E3885FB@.microsoft.com...
> > Dan, I am going to paste the following detail, the table is a simple 2
> > column
> > that is used by a full text indexing job, it seems, the first field is
> > just
> > text and the second is a guid...I went to record 128, but saw nothing
> > funny.....appreciate all your help
> >
> > Server: Msg 8928, Level 16, State 1, Line 1
> > Object ID 910678342, index ID 0: Page (1:369538) could not be processed.
> > See
> > other errors for details.
> > Server: Msg 8944, Level 16, State 1, Line 1
> > Table error: Object ID 910678342, index ID 0, page (1:369538), row 125.
> > Test
> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2088 and 43.
> > DBCC results for 'RequestWords'.
> > There are 1313272 rows in 11072 pages for object 'RequestWords'.
> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> > 'RequestWords' (object ID 910678342).
> > repair_allow_data_loss is the minimum repair level for the errors found by
> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> >
> >
> >
> >
> > "Dan Guzman" wrote:
> >
> >> > Not sure when my last known good is and it could take quite awhile to
> >> > try
> >> > and
> >> > find it...is there a way to see what data would be lost first before
> >> > running
> >> > REPAIR_ALLOW_DATA_LOSS ?
> >>
> >> The DBCC CHECKTABLE output should specify the problem pages and error
> >> details. You can use DBCC PAGE (google is your friend) to examine the
> >> page
> >> contents and get an idea of data might be affected. I don't believe it's
> >> possible to provide exact details of lost data beforehand; I would think
> >> DBCC could recover the data without loss if that were possible.
> >>
> >> You might try posting the DBCC error details in case we can provide an
> >> alternate solution. You could also try running the DBCC CHECKTABLE WITH
> >> REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to correct
> >> the problem and identify lost data.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
> >> > Not sure when my last known good is and it could take quite awhile to
> >> > try
> >> > and
> >> > find it...is there a way to see what data would be lost first before
> >> > running
> >> > REPAIR_ALLOW_DATA_LOSS ?
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> > I have a backup and would like to know the best way to proceed to
> >> >> > correct
> >> >> > the consistency errors...also where can I find the
> >> >> > sp_repair_allow_data_loss?
> >> >>
> >> >> Restoring from your last known good backup is usually the best option
> >> >> rather
> >> >> than allowing data loss. There may be other methods depending on the
> >> >> specific types of corruption and affected object types.
> >> >>
> >> >> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
> >> >> CHECKTABLE
> >> >> commands. See the Books online for details.
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >>
> >> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> >> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
> >> >> >I receive the following after running checktable:
> >> >> >
> >> >> > CHECKTABLE found 0 allocation errors and 2 consistency errors in
> >> >> > table
> >> >> > 'RequestWords' (object ID 910678342).
> >> >> > repair_allow_data_loss is the minimum repair level for the errors
> >> >> > found
> >> >> > by
> >> >> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
> >> >> >
> >> >> > I have a backup and would like to know the best way to proceed to
> >> >> > correct
> >> >> > the consistency errors...also where can I find the
> >> >> > sp_repair_allow_data_loss?
> >> >> >
> >> >> > Thanks
> >> >>
> >>
>|||I'm glad you were able to get your database fixed.
--
Dan Guzman
SQL Server MVP
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:8525A1AF-4A88-47B3-AA8F-1144B9814DA8@.microsoft.com...
> Dan, sorry took so long to get back...
> I was able to locate the corrupt record in the referenced table...it was
> a
> null id that had a guid assigned to it from another table...very
> weird...the
> delete statement would not work on it....had to finally run
> repair_allow_data_loss and then repair_fast. I lost 132 records but they
> were all bogus references to the corruption...so it turned out all good.
> Thanks for your assistance.
> "Dan Guzman" wrote:
>> The DBCC error indicates bad column offsets, which will prevent the
>> problem
>> row from being parsed. I think DBCC will need to delete that row to
>> remove
>> the error. You can probably fix the error with a normal delete command
>> if
>> you can identify the key value of the problem row from the raw DBCC PAGE
>> output.
>> If you haven't already done so, you might try DBCC PAGE print option 3
>> (http://support.microsoft.com/kb/83065) to print the individual column
>> values (example below). I don't know how this will behave with bad
>> column
>> offsets but I'm curious to find out, if you don't mind giving that a try.
>> The DBCC PAGE dump is probably the only data you'll have to salvage the
>> deleted row, unless you contact Microsoft PSS.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:042EAEE7-D5C2-4847-9AF9-DFC92E3885FB@.microsoft.com...
>> > Dan, I am going to paste the following detail, the table is a simple 2
>> > column
>> > that is used by a full text indexing job, it seems, the first field is
>> > just
>> > text and the second is a guid...I went to record 128, but saw nothing
>> > funny.....appreciate all your help
>> >
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 910678342, index ID 0: Page (1:369538) could not be
>> > processed.
>> > See
>> > other errors for details.
>> > Server: Msg 8944, Level 16, State 1, Line 1
>> > Table error: Object ID 910678342, index ID 0, page (1:369538), row 125.
>> > Test
>> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2088 and 43.
>> > DBCC results for 'RequestWords'.
>> > There are 1313272 rows in 11072 pages for object 'RequestWords'.
>> > CHECKTABLE found 0 allocation errors and 2 consistency errors in table
>> > 'RequestWords' (object ID 910678342).
>> > repair_allow_data_loss is the minimum repair level for the errors found
>> > by
>> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>> >
>> >
>> >
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > Not sure when my last known good is and it could take quite awhile
>> >> > to
>> >> > try
>> >> > and
>> >> > find it...is there a way to see what data would be lost first before
>> >> > running
>> >> > REPAIR_ALLOW_DATA_LOSS ?
>> >>
>> >> The DBCC CHECKTABLE output should specify the problem pages and error
>> >> details. You can use DBCC PAGE (google is your friend) to examine the
>> >> page
>> >> contents and get an idea of data might be affected. I don't believe
>> >> it's
>> >> possible to provide exact details of lost data beforehand; I would
>> >> think
>> >> DBCC could recover the data without loss if that were possible.
>> >>
>> >> You might try posting the DBCC error details in case we can provide an
>> >> alternate solution. You could also try running the DBCC CHECKTABLE
>> >> WITH
>> >> REPAIR_ALLOW_DATA_LOSS on a database copy to see what DBCC did to
>> >> correct
>> >> the problem and identify lost data.
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> news:60356673-18A9-41B8-929D-265E419590DA@.microsoft.com...
>> >> > Not sure when my last known good is and it could take quite awhile
>> >> > to
>> >> > try
>> >> > and
>> >> > find it...is there a way to see what data would be lost first before
>> >> > running
>> >> > REPAIR_ALLOW_DATA_LOSS ?
>> >> >
>> >> > "Dan Guzman" wrote:
>> >> >
>> >> >> > I have a backup and would like to know the best way to proceed to
>> >> >> > correct
>> >> >> > the consistency errors...also where can I find the
>> >> >> > sp_repair_allow_data_loss?
>> >> >>
>> >> >> Restoring from your last known good backup is usually the best
>> >> >> option
>> >> >> rather
>> >> >> than allowing data loss. There may be other methods depending on
>> >> >> the
>> >> >> specific types of corruption and affected object types.
>> >> >>
>> >> >> REPAIR_ALLOW_DATA_LOSS is an option of the DBCC CHECKDB and DBCC
>> >> >> CHECKTABLE
>> >> >> commands. See the Books online for details.
>> >> >>
>> >> >> --
>> >> >> Hope this helps.
>> >> >>
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> >> news:264DC93B-200F-4567-B853-8B0DCF91852C@.microsoft.com...
>> >> >> >I receive the following after running checktable:
>> >> >> >
>> >> >> > CHECKTABLE found 0 allocation errors and 2 consistency errors in
>> >> >> > table
>> >> >> > 'RequestWords' (object ID 910678342).
>> >> >> > repair_allow_data_loss is the minimum repair level for the errors
>> >> >> > found
>> >> >> > by
>> >> >> > DBCC CHECKTABLE (synergy.dbo.RequestWords ).
>> >> >> >
>> >> >> > I have a backup and would like to know the best way to proceed to
>> >> >> > correct
>> >> >> > the consistency errors...also where can I find the
>> >> >> > sp_repair_allow_data_loss?
>> >> >> >
>> >> >> > Thanks
>> >> >>
>> >>

Saturday, February 25, 2012

Checkpoint and TempDB

cbrichards via droptable.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We ru
n
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly s
top
> and restart services.
> One thing I would like information on is the checkpoint process, suspectin
g
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/n
ot
> working?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200701/1|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is no
t
occurring on TempDB, I need to know if there is a way to verify my suspicion
s
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
Message posted via http://www.droptable.com|||To capture CHECKPOINT you need to run profiler.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.droptable.com
>|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:[vbcol=seagreen]
>To capture CHECKPOINT you need to run profiler.
>
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200701/1
>|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You can restart SQL Server and it will create a new tempdb database
>
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
> --
> Message posted via http://www.droptable.com
>|||While that is a good link, and I have it bookmarked, my initial question tha
t
started this thread is still not being addressed:
****************************************
************************************
***************************************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/of
f,
working/not
working?
****************************************
************************************
***************************************
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> ****************************************
**********************************
****************************************
*
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> ****************************************
**********************************
****************************************
*
> Roger Wolter[MSFT] wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200701/1
>

Checkpoint and TempDB

We are running SQL 2005, SP1, on Windows 2003.
Occasionally we have a TempDB log that starts growing exponentially. We run
DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
sysindexes for objects like '#%', but nothing shows its face. This happens
very infrequently, and since nothing definitive is found, we reluctantly stop
and restart services.
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time, if checkpoint is on/off, working/not
working?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1I have a similar problelm. We have some in-house databases and some
that we have unfortunately inherited. One of them pulls in millions of
records into tables that are temporary using #tablename. Our company is
not going to pay to change the application at this point, so I have to
deal with it. What I did is create a simple job that checks the size of
the temp db file. I am on 2000 so I used sysfiles. When the size in our
case exceeds 15 gigs, I just use msdb.db.sp_start_job to kick off a job
to shrink the temp db. I think it probably runs about 3X a week, but
has been working to keep everything under control. I also set a max
size of 20 gigs so it doesn't eat up all the space it has on its drive.
Maybe these ideas will help. At least you wouldn't have to stop/start
services and manually intervene. My shrinks generally occur at 2-4 a.m.
when I am sleeping :)
cbrichards via SQLMonster.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We run
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly stop
> and restart services.
> One thing I would like information on is the checkpoint process, suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/not
> working?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is not
occurring on TempDB, I need to know if there is a way to verify my suspicions
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
--
Message posted via http://www.sqlmonster.com|||To capture CHECKPOINT you need to run profiler.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.sqlmonster.com
>|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:
>To capture CHECKPOINT you need to run profiler.
>> Thanks Kristina, but that does not address my issue, as I suspect the
>> Checkpoint is not working. Since I suspect that the checkpoint process is
>[quoted text clipped - 4 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
>>To capture CHECKPOINT you need to run profiler.
>> Thanks Kristina, but that does not address my issue, as I suspect the
>> Checkpoint is not working. Since I suspect that the checkpoint process
>> is
>>[quoted text clipped - 4 lines]
>> This Thread is not closed. Please HELP!!
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1
>|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:
>Hi
>You can restart SQL Server and it will create a new tempdb database
>> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
>> a
>[quoted text clipped - 16 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via http://www.sqlmonster.com|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
>>Hi
>>You can restart SQL Server and it will create a new tempdb database
>> So I am in the middle of a crisis, my tempdb log is growing at about 2
>> gig
>> a
>>[quoted text clipped - 16 lines]
>> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.sqlmonster.com
>|||While that is a good link, and I have it bookmarked, my initial question that
started this thread is still not being addressed:
*******************************************************************************************************************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/off,
working/not
working?
*******************************************************************************************************************
Roger Wolter[MSFT] wrote:
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>> Wow!
>[quoted text clipped - 14 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> *******************************************************************************************************************
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> *******************************************************************************************************************
> Roger Wolter[MSFT] wrote:
>>Does this help?
>>http://support.microsoft.com/kb/317375/
>>If this is urgent you should open a support case.
>> Wow!
>>[quoted text clipped - 14 lines]
>>>
>>> This Thread is not closed. Please HELP!!
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1
>|||Thanks Uri. I appreciate the info.
Uri Dimant wrote:
>Hi
>> Is there any way to verify at the time of the crisis, if checkpoint is
>> on/off,
>> working/not
>> working?
>1)
>You can monitor the number of pages flushed by a checkpoint
>using PerfMon - SQLServer:Buffer Manager object, Checkpoint
>pages/sec counter
>2)
>You can start SQL Server with the
>traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
>will be recorded in the SQL Server error log, along with the time of the
>checkpoint.
>> While that is a good link, and I have it bookmarked, my initial question
>> that
>[quoted text clipped - 22 lines]
>>>
>>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1

Checkpoint and TempDB

cbrichards via droptable.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We run
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly stop
> and restart services.
> One thing I would like information on is the checkpoint process, suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/not
> working?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is not
occurring on TempDB, I need to know if there is a way to verify my suspicions
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
Message posted via http://www.droptable.com
|||To capture CHECKPOINT you need to run profiler.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.droptable.com
>
|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:[vbcol=seagreen]
>To capture CHECKPOINT you need to run profiler.
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
>
|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You can restart SQL Server and it will create a new tempdb database
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
> --
> Message posted via http://www.droptable.com
>
|||While that is a good link, and I have it bookmarked, my initial question that
started this thread is still not being addressed:
************************************************** ************************************************** ***************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/off,
working/not
working?
************************************************** ************************************************** ***************
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> ************************************************** ************************************************** ***************
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> ************************************************** ************************************************** ***************
> Roger Wolter[MSFT] wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
>

Sunday, March 25, 2012

Cleaning up goldmine Data

Cleaning up goldmine Data

Cleaning up goldmine Data

Thursday, March 22, 2012

CLASSPATH variable?

class that uses sql server isnt releasing memory

Monday, March 19, 2012

Choosing the most frequent

Choosing RAID level

Choosing RAID level

Choosing RAID level

Sunday, March 11, 2012

Chkdsk and MS SQL Server 7

Thursday, March 8, 2012

checktable repair_rebuild taking long time

checktable repair_rebuild taking long time

checktable repair_rebuild taking long time

checktable error

Saturday, February 25, 2012

Checkpoint and TempDB

Checkpoint and TempDB

Checkpoint and TempDB

check sql licence

Blog Archive

About Me