i have an sql db which is about 400 megs the transaction log is over 2 gig. how can i bring the size down. does enterprise manager have some sort of shrink, compress or delete tool that i can use.
thank you,
ThomasTry DBCC Shrinkfile.|||Read up dbcc shrinkfile from the Holy book (SQL Server Books Online)
Also here is a reference :
http://www.dbforums.com/showthread.php?threadid=861115|||You need to back it up! After is has been backed up once or twice then you can use DBCC SHRINKFILE to reduce its size.|||If you still have a problem shrinking the log, please post back.
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Clarification requested for 'Estimating the Size of a Table' (Estimating the Size of a Heap)
I am currently engaged in determining the estimated size of a table given a potentially high growth rate. I am following the instructions found in the SQL2000 BOL article entitled "Estimating the Size of a Table". I need clarification regarding the instructions specified for item #4, determining the Variable_Data_Size variable. Basically, the table I am using has 25 TEXT fields in it (please don't ask, I didn't design it), and I'm not certain how these play into the Variable_Data_Size calculation. None of these columns will EVER store anywhere near the potential size for the data type. This table has the 'text in row' option enabled, and is set to store up to 256 characters for each text field 'in row'. (On a side note, I think the original designer thought doing this actually set a limit on the size of the text field to 256 chars, but I can't be certain). If it helps you to visualize, here is the table schema:
CREATE TABLE [picaweb].[temp_response] (
[ResponseID] [varchar] (30) NOT NULL ,
[Term] [varchar] (5) NOT NULL ,
[Subject] [varchar] (4) NOT NULL ,
[Course] [varchar] (4) NOT NULL ,
[Sect] [varchar] (3) NOT NULL ,
[MidEndFlag] [varchar] (3) NOT NULL ,
[SID] [varchar] (9) NOT NULL ,
[TemplateID] [varchar] (30) NOT NULL ,
[LastModified] [datetime] NULL ,
[College] [varchar] (30) NULL ,
[Classification] [varchar] (10) NULL ,
[CourseRequired] [varchar] (3) NULL ,
[ExpectedGrade] [varchar] (10) NULL ,
[Sex] [varchar] (6) NULL ,
[ItemAnswer1] [varchar] (1) NULL ,
[ItemComments1] [text] NULL ,
[ItemAnswer2] [varchar] (1) NULL ,
[ItemComments2] [text] NULL ,
[ItemAnswer3] [varchar] (1) NULL ,
[ItemComments3] [text] NULL ,
[ItemAnswer4] [varchar] (1) NULL ,
[ItemComments4] [text] NULL ,
[ItemAnswer5] [varchar] (1) NULL ,
[ItemComments5] [text] NULL ,
[ItemAnswer6] [varchar] (1) NULL ,
[ItemComments6] [text] NULL ,
[ItemAnswer7] [varchar] (1) NULL ,
[ItemComments7] [text] NULL ,
[ItemAnswer8] [varchar] (1) NULL ,
[ItemComments8] [text] NULL ,
[ItemAnswer9] [varchar] (1) NULL ,
[ItemComments9] [text] NULL ,
[ItemAnswer10] [varchar] (1) NULL ,
[ItemComments10] [text] NULL ,
[ItemAnswer11] [varchar] (1) NULL ,
[ItemComments11] [text] NULL ,
[ItemAnswer12] [varchar] (1) NULL ,
[ItemComments12] [text] NULL ,
[ItemAnswer13] [varchar] (1) NULL ,
[ItemComments13] [text] NULL ,
[ItemAnswer14] [varchar] (1) NULL ,
[ItemComments14] [text] NULL ,
[ItemAnswer15] [varchar] (1) NULL ,
[ItemComments15] [text] NULL ,
[ItemAnswer16] [varchar] (1) NULL ,
[ItemComments16] [text] NULL ,
[ItemAnswer17] [varchar] (1) NULL ,
[ItemComments17] [text] NULL ,
[ItemAnswer18] [varchar] (1) NULL ,
[ItemComments18] [text] NULL ,
[ItemAnswer19] [varchar] (1) NULL ,
[ItemComments19] [text] NULL ,
[ItemAnswer20] [varchar] (1) NULL ,
[ItemComments20] [text] NULL ,
[EssayQuestionAnswer1] [text] NULL ,
[EssayQuestionAnswer2] [text] NULL ,
[EssayQuestionAnswer3] [text] NULL ,
[EssayQuestionAnswer4] [text] NULL ,
[EssayQuestionAnswer5] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I would appreciate any insight anyone might be able to provide. My calculations were wrong somewhere, and I believe it was on the Variable_Data_Size variable determination.
Best,
B.
Have you checked in the 2K5 version of Books Online? I'm not sure if it was updated...
http://msdn2.microsoft.com/en-us/library/ms187445.aspx
|||Yes, it's pretty much the same as for 2000. Now that I've really thought about it, I suppose the size of the data to be stored "in row" should be treated like a fixed-length field, especially since it will reduce row density per page. I think that's what I'll do.Question answered.
Best,
B.
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
>
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
>
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.
> > >
> > >
> >
> >
>
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.
> > >
> > >
> >
> >
>
Wednesday, March 7, 2012
checksum_agg and row size error. Require explanation.
Hi,
I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?
-- Works OK
select o.id
,checksum_agg(binary_checksum(m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(m.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id = o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x
Regards
LiamUse "OPTION (ROBUST PLAN)":
select o.name
,checksum_agg(binary_checksum(*m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
OPTION (ROBUST PLAN)
This forces SQL Server to use a plan that works for the maximum
potential row size.
Razvan
CheckPoint question
Is the quantity of data written to disk during a checkpoint, related to the
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger?
Thanks,
Don
SQL2000"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> > Is the quantity of data written to disk during a checkpoint, related to
> > the
> > size of the table? The checkpoint takes place when the log file is 70%
> > full.
> >
> > Compare these two identical files on separate servers except for rec amts.
> >
> > 10M rec file has checkpoint 47MG.
> > 100M rec file has checkpoint 81MG.
> >
> > Can I expect that as the tables get larger the checkpoint will become
> > larger?
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> > Is the quantity of data written to disk during a checkpoint, related to
>> > the
>> > size of the table? The checkpoint takes place when the log file is 70%
>> > full.
>> >
>> > Compare these two identical files on separate servers except for rec
>> > amts.
>> >
>> > 10M rec file has checkpoint 47MG.
>> > 100M rec file has checkpoint 81MG.
>> >
>> > Can I expect that as the tables get larger the checkpoint will become
>> > larger?
>> Checkpoint writes the dirty pages back to the database files, so the size
>> depends on the number of changes since the last checkpoint and how many
>> of
>> those pages have been flushed by the lazywriter thread. Also the
>> recovery
>> interval server parameter affects checkpoint size as well as the amount
>> of
>> memory on the server.
>> David
>>|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you have
> never done a proper FULL backup. The tran log will be truncated at 70% full
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Server
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reason
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not try
> to tweak around it. That means placing the log file on a Raid 1 or raid 10
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> > I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> > no
> > change at all in the frequency of the flush or the amount of data flushed.
> > It always flushes when the log file is 70% full.
> >
> > I'd be really interested in manipulating the amount of data stored in
> > memory
> > and/or the frequency of the flush...
> >
> > Any advice much appreicated.
> >
> > Don
> >
> >
> > "David Browne" wrote:
> >
> >>
> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> >> > Is the quantity of data written to disk during a checkpoint, related to
> >> > the
> >> > size of the table? The checkpoint takes place when the log file is 70%
> >> > full.
> >> >
> >> > Compare these two identical files on separate servers except for rec
> >> > amts.
> >> >
> >> > 10M rec file has checkpoint 47MG.
> >> > 100M rec file has checkpoint 81MG.
> >> >
> >> > Can I expect that as the tables get larger the checkpoint will become
> >> > larger?
> >>
> >> Checkpoint writes the dirty pages back to the database files, so the size
> >> depends on the number of changes since the last checkpoint and how many
> >> of
> >> those pages have been flushed by the lazywriter thread. Also the
> >> recovery
> >> interval server parameter affects checkpoint size as well as the amount
> >> of
> >> memory on the server.
> >>
> >> David
> >>
> >>
> >>
>
>|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
>> The 70% deal is because you have the recovery mode set to SIMPLE or you
>> have
>> never done a proper FULL backup. The tran log will be truncated at 70%
>> full
>> in Simple mode. This in turn forces a checkpoint to occur. But that just
>> means that the amount of data in the tran log is still less than SQL
>> Server
>> thinks it will take to recover in 1 minute. If the log file was larger
>> you
>> would probably see checkpoints before the 70% full mark. What is the
>> reason
>> for wanting to change this? If checkpoints are causing issues with
>> performance you really need to address the source of the trouble and not
>> try
>> to tweak around it. That means placing the log file on a Raid 1 or raid
>> 10
>> by itself and a good amount of write back cache will help as well.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>> > I've changed the recovery interval to 1, 100, 1000 respectively, and
>> > saw
>> > no
>> > change at all in the frequency of the flush or the amount of data
>> > flushed.
>> > It always flushes when the log file is 70% full.
>> >
>> > I'd be really interested in manipulating the amount of data stored in
>> > memory
>> > and/or the frequency of the flush...
>> >
>> > Any advice much appreicated.
>> >
>> > Don
>> >
>> >
>> > "David Browne" wrote:
>> >
>> >>
>> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> >> > Is the quantity of data written to disk during a checkpoint, related
>> >> > to
>> >> > the
>> >> > size of the table? The checkpoint takes place when the log file is
>> >> > 70%
>> >> > full.
>> >> >
>> >> > Compare these two identical files on separate servers except for rec
>> >> > amts.
>> >> >
>> >> > 10M rec file has checkpoint 47MG.
>> >> > 100M rec file has checkpoint 81MG.
>> >> >
>> >> > Can I expect that as the tables get larger the checkpoint will
>> >> > become
>> >> > larger?
>> >>
>> >> Checkpoint writes the dirty pages back to the database files, so the
>> >> size
>> >> depends on the number of changes since the last checkpoint and how
>> >> many
>> >> of
>> >> those pages have been flushed by the lazywriter thread. Also the
>> >> recovery
>> >> interval server parameter affects checkpoint size as well as the
>> >> amount
>> >> of
>> >> memory on the server.
>> >>
>> >> David
>> >>
>> >>
>> >>
>>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger?
Thanks,
Don
SQL2000"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> > Is the quantity of data written to disk during a checkpoint, related to
> > the
> > size of the table? The checkpoint takes place when the log file is 70%
> > full.
> >
> > Compare these two identical files on separate servers except for rec amts.
> >
> > 10M rec file has checkpoint 47MG.
> > 100M rec file has checkpoint 81MG.
> >
> > Can I expect that as the tables get larger the checkpoint will become
> > larger?
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> > Is the quantity of data written to disk during a checkpoint, related to
>> > the
>> > size of the table? The checkpoint takes place when the log file is 70%
>> > full.
>> >
>> > Compare these two identical files on separate servers except for rec
>> > amts.
>> >
>> > 10M rec file has checkpoint 47MG.
>> > 100M rec file has checkpoint 81MG.
>> >
>> > Can I expect that as the tables get larger the checkpoint will become
>> > larger?
>> Checkpoint writes the dirty pages back to the database files, so the size
>> depends on the number of changes since the last checkpoint and how many
>> of
>> those pages have been flushed by the lazywriter thread. Also the
>> recovery
>> interval server parameter affects checkpoint size as well as the amount
>> of
>> memory on the server.
>> David
>>|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you have
> never done a proper FULL backup. The tran log will be truncated at 70% full
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Server
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reason
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not try
> to tweak around it. That means placing the log file on a Raid 1 or raid 10
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> > I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> > no
> > change at all in the frequency of the flush or the amount of data flushed.
> > It always flushes when the log file is 70% full.
> >
> > I'd be really interested in manipulating the amount of data stored in
> > memory
> > and/or the frequency of the flush...
> >
> > Any advice much appreicated.
> >
> > Don
> >
> >
> > "David Browne" wrote:
> >
> >>
> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> >> > Is the quantity of data written to disk during a checkpoint, related to
> >> > the
> >> > size of the table? The checkpoint takes place when the log file is 70%
> >> > full.
> >> >
> >> > Compare these two identical files on separate servers except for rec
> >> > amts.
> >> >
> >> > 10M rec file has checkpoint 47MG.
> >> > 100M rec file has checkpoint 81MG.
> >> >
> >> > Can I expect that as the tables get larger the checkpoint will become
> >> > larger?
> >>
> >> Checkpoint writes the dirty pages back to the database files, so the size
> >> depends on the number of changes since the last checkpoint and how many
> >> of
> >> those pages have been flushed by the lazywriter thread. Also the
> >> recovery
> >> interval server parameter affects checkpoint size as well as the amount
> >> of
> >> memory on the server.
> >>
> >> David
> >>
> >>
> >>
>
>|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
>> The 70% deal is because you have the recovery mode set to SIMPLE or you
>> have
>> never done a proper FULL backup. The tran log will be truncated at 70%
>> full
>> in Simple mode. This in turn forces a checkpoint to occur. But that just
>> means that the amount of data in the tran log is still less than SQL
>> Server
>> thinks it will take to recover in 1 minute. If the log file was larger
>> you
>> would probably see checkpoints before the 70% full mark. What is the
>> reason
>> for wanting to change this? If checkpoints are causing issues with
>> performance you really need to address the source of the trouble and not
>> try
>> to tweak around it. That means placing the log file on a Raid 1 or raid
>> 10
>> by itself and a good amount of write back cache will help as well.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>> > I've changed the recovery interval to 1, 100, 1000 respectively, and
>> > saw
>> > no
>> > change at all in the frequency of the flush or the amount of data
>> > flushed.
>> > It always flushes when the log file is 70% full.
>> >
>> > I'd be really interested in manipulating the amount of data stored in
>> > memory
>> > and/or the frequency of the flush...
>> >
>> > Any advice much appreicated.
>> >
>> > Don
>> >
>> >
>> > "David Browne" wrote:
>> >
>> >>
>> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> >> > Is the quantity of data written to disk during a checkpoint, related
>> >> > to
>> >> > the
>> >> > size of the table? The checkpoint takes place when the log file is
>> >> > 70%
>> >> > full.
>> >> >
>> >> > Compare these two identical files on separate servers except for rec
>> >> > amts.
>> >> >
>> >> > 10M rec file has checkpoint 47MG.
>> >> > 100M rec file has checkpoint 81MG.
>> >> >
>> >> > Can I expect that as the tables get larger the checkpoint will
>> >> > become
>> >> > larger?
>> >>
>> >> Checkpoint writes the dirty pages back to the database files, so the
>> >> size
>> >> depends on the number of changes since the last checkpoint and how
>> >> many
>> >> of
>> >> those pages have been flushed by the lazywriter thread. Also the
>> >> recovery
>> >> interval server parameter affects checkpoint size as well as the
>> >> amount
>> >> of
>> >> memory on the server.
>> >>
>> >> David
>> >>
>> >>
>> >>
>>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David
Thursday, February 16, 2012
Checking for free disk space and getting mail when it falls below a certain limit
Hello
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
Thanks
Hi Vaddi -
Why not use the Alerts feature in Performance Monitor? The Logical Disk Performance Object has a Counter for % Free Space and you can select which drive letter you'd like to monitor. Once the limit is reached, you can have it email you using a WSH script.
HTH...
checking for free disk space and getting mail , when falls below a certain limit
Hello
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
ThanksHere's what I use:
set nocount on
declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)
create table #FreeSpace(Drive char(1), MB_Free int)
insert into #FreeSpace exec master..xp_fixeddrives
select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold
set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'
if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message
end
drop table #FreeSpace
go
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
ThanksHere's what I use:
set nocount on
declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)
create table #FreeSpace(Drive char(1), MB_Free int)
insert into #FreeSpace exec master..xp_fixeddrives
select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold
set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'
if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message
end
drop table #FreeSpace
go
Checking database size from T-SQL
Which is the best way to check the size of a database from Transact SQL ? (Selecting from Sysfiles gives a totally different result compared to right-clicking a database and selecting Properties. It's the result from the latter than I want to obtain through Transact sQL.)exec sp_spaceused|||might want to throw in the updateusage parameter.|||btw, if you ever want to know exactly what EM or SSMS is doing to get the results it's showing you, just turn on the profiler and start clicking around in EM/SSMS.
EDIT: Really, this will tell you what DMO/SMO is doing, since EM and SSMS use DMO and SMO under the covers.|||just turn on the profiler and start clicking around in EM/SSMS.
And I alway get very sad when I see just how much traffic just one click in EM generates... :eek:|||you think that's bad, try SSMS. SMO is a very chatty api.|||you think that's bad, try SSMS. SMO is a very chatty api.
I'm not sure I want to know ;) The only thing that can make it a little more easier to live with if the uncatchable "refresh"-problem in EM is history in SSMS:
Me: I just created the table you wanted
Developer: <click><click> I don't see it
Me: Did you refresh the table list? You were probably already connected before I created it.
Developer: <click><click><click> I did, but I still don't see it
Me: Did you do a refresh on the instance or on the table list, you must refresh the table list separately
Developer: <click><click><click> Did that but I still don't see it
Me: <sigh> just disconnect and reconnect...
Developer: <click><click><click> Ah, there it is!|||I was just about to try tracking what, in my case,
Mgmt Studio Express, is doing, but sp_spaceused
did the trick!
Thank you!|||fyi, you can also pass a table name to sp_spaceused to get the size of data/indexes in it.
EDIT: Really, this will tell you what DMO/SMO is doing, since EM and SSMS use DMO and SMO under the covers.|||just turn on the profiler and start clicking around in EM/SSMS.
And I alway get very sad when I see just how much traffic just one click in EM generates... :eek:|||you think that's bad, try SSMS. SMO is a very chatty api.|||you think that's bad, try SSMS. SMO is a very chatty api.
I'm not sure I want to know ;) The only thing that can make it a little more easier to live with if the uncatchable "refresh"-problem in EM is history in SSMS:
Me: I just created the table you wanted
Developer: <click><click> I don't see it
Me: Did you refresh the table list? You were probably already connected before I created it.
Developer: <click><click><click> I did, but I still don't see it
Me: Did you do a refresh on the instance or on the table list, you must refresh the table list separately
Developer: <click><click><click> Did that but I still don't see it
Me: <sigh> just disconnect and reconnect...
Developer: <click><click><click> Ah, there it is!|||I was just about to try tracking what, in my case,
Mgmt Studio Express, is doing, but sp_spaceused
did the trick!
Thank you!|||fyi, you can also pass a table name to sp_spaceused to get the size of data/indexes in it.
Friday, February 10, 2012
Check the size of individual table in the database
Hi
I am trying to check the size of each table in my database?
SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE
I can't for the lif of me figure out how this is done.
I Know if you execute
EXEC sp_HelpDB you get the size of the database, but i want each individual tables size
Any help would be greatly appreciated
Kind Regards
Carel Greaves
Check the responses to your identical question in the Database Engine forum here.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
If you are not opposed to using undocumented features, you can try:
Code Snippet
exec sp_MSforeachtable 'exec sp_spaceused ''?'''
|||Soz Arnie, I'm still Learning, won't do it again.
But while i'm on the topic.
get an error message when i execute the statement. Although the results are exactly what i want.
And when i send the results to a file it only gives me the tables names, not the stats.
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.
|||I don't see a place where you can change that setting; you might try switching your results to text instead of the grid.|||See your other posting for my response.Check the size of all tables in my database
Hi
I am trying to check the size of each table in my database?
SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE
I can't for the lif of me figure out how this is done.
Any help would be greatly appreciated
Kind Regards
Carel Greaves
Courtesy of Vinod Kumarhttp://www.extremeexperts.com/SQL/Scripts/FindSizeOfTable.aspx|||
Hi Carel,
in management studio click on database, then right click, then click on reports - > standard reports -> Disk usage by table.
There are quite a few useful reports there....and in case you have not heard of them, there are a set of performance reports which we have found very useful now from MSFT. They can show you things like the plans for sql in process on the machine.....so if you have a slow running query you can look at the machine and then look at the plan that is being used for the running of the query....
This set of performance reports is a good beginning for a set of tools to monitor a server.....well, good for free....if you want better you should look into things like quest.....
Best Regards
Peter
|||I must be blind because i don't see the reports link, maybe its a plug-in that i don't have with my management studio.
The database is SQL 2000, that is why i am looking for a query to perform the task, however i do connect to this specific server using a SQL Server 2005 Management Console.
|||Try this:
-- will result in the dump information on space occupied by each table.
EXECsp_msforeachtable'sp_spaceused "?"'
|||Thats EXACTLY what i want thanks, but it give me this error
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.
When i take the results to a file or report then it doesn't include the stats, it only has the table names.
I'm qute new to this, soz guys. I really appreciate the help.
|||If you still have Query Analyzer available, you can run that query there. It does not have the 100 resultset limit that SSMS has.|||Here is a stored procedure that I use, it doesn't have the resultset display limit, and it does not depend upon 'undocumented' functionality. (We keep getting MSFT folks warning us that it may change in the future...)
Code Snippet
CREATE PROCEDURE dbo.TableSpace
AS
BEGIN
DECLARE
@.TotalRows int,
@.Counter int,
@.TableName varchar(50)
DECLARE @.MyTables table
( RowID int IDENTITY,
TableName varchar(50),
Rows bigint,
Reserved varchar(12),
Data varchar(12),
IndexSize varchar(12),
Unused varchar(12)
)
INSERT INTO @.MyTables ( TableName )
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
SELECT
@.TotalRows = @.@.ROWCOUNT,
@.Counter = 1
WHILE ( @.Counter <= @.TotalRows )
BEGIN
SELECT @.TableName = TableName
FROM @.MyTables
WHERE RowID = @.Counter
INSERT INTO @.MyTables
EXECUTE sp_spaceused @.TableName
SET @.Counter = ( @.Counter + 1 )
END
DELETE FROM @.MyTables
WHERE RowID <= @.TotalRows
SELECT
TableName,
Rows,
Reserved,
Data,
IndexSize,
Unused
FROM @.MyTables
ORDER BY TableName
END
GO
EXECUTE dbo.TableSpace
This gives you the size for each table, each index, and each partition. The dmv returns the number of pages, which you need to multiple by 8K to get the byte size.
If you only want the information for a certain table / index / partition, you can specify the second, third and fourth parameter. If you specify NULL, you get all tables / indexes / partitions
Thanks,|||
Similar to the stored proc another poster provided, this script will get the info you are looking for and is compatilble with SQL 2000. Of course, you can adjust the results query as you need to fit your uses and provide different statistics. Hope this helps.
if object_id('tempdb..#TableUsage') is not null drop table #TableUsage
create table #TableUsage
(
TableName sysname,
Rows int,
Reserved varchar(20),
ReservedValue as cast(replace(Reserved, ' KB', '') as int),
Data varchar(20),
DataValue as cast(replace(Data, ' KB', '') as int),
Indexsize varchar(20),
IndexsizeValue as cast(replace(Indexsize, ' KB', '') as int),
Unused varchar(20),
UnusedValue as cast(replace(Unused, ' KB', '') as int),
)
exec sp_msforeachtable 'insert #TableUsage ( TableName, Rows, Reserved, Data, Indexsize, Unused ) exec sp_spaceused ''?'''
select
count(*) as Tables,
sum(ReservedValue) as Reserved,
sum(DataValue) as Data,
sum(IndexsizeValue) as Indexsize,
sum(UnusedValue) as Unused
from #TableUsage
if object_id('tempdb..#TableUsage') is not null drop table #TableUsage
Check the field existence of a database table
Check the field existence of a database table, if exist get the type, size, decimal ..etc attributes
I need SP
SP
(
@.Tablename varchar(30),
@.Fieldname varchar(30),
@.existance char(1) OUTPUT,
@.field_type varchar(30) OUTPUT,
@.field_size int OUTPUT,
@.field_decimal int OUTPUT
)
as
/* Below check the existance of a @.Fieldname in given @.Tablename */
/* And set the OUTPUT variables */
Thanks
To check existance of a data column, try code below:
IFEXISTS (SELECT *FROMSysObjects soINNERJOINSysColumns scON so.ID = sc.IDWHEREObjectProperty(so.ID,'IsUserTable') = 1AND so.Name ='yourtablename'AND sc.Name ='columnname' )
To get datatype of the data column, try:
SELECT data_typeFROM information_schema.columnsWHERE table_schema ='dbo'AND table_name ='yourtablename'AND column_name ='columnname'|||
You can also do an sp_Help 'Table' to get all the information.
|||Hi jackyang,
Thanks for your help.Your first query is running properly but second one has problem
SELECT data_typeFROM information_schema.columnsWHERE table_schema ='dbo'AND table_name ='yourtablename'AND column_name ='columnname'
does not work
here is 'dbo' static or my databas name (my database name is 'neuron')?
Thanks|||
You can disregard the table_schema then. It's likely the security schema is not default 'dbo' in your setup.
Just use the code below:
SELECT data_typeFROM information_schema.columnsWHERE table_name ='yourtablename'AND column_name ='columnname'
Check Temp table size?
I have a tempdb which fills up the disk frequently. I want to determine the
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FL
EXEC tempdb..spaceused '#temptablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>
|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> the
does[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES.
> its
file
>
|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?[vbcol=seagreen]
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
determine[vbcol=seagreen]
> does
check[vbcol=seagreen]
> file
reason.
>
|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable________________________________________ __________________________
_________________________________________000000058 617
#tempTable________________________________________ __________________________
_________________________________________000000058 628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.
|||>
#tempTable________________________________________ __________________________
> _________________________________________000000058 617
>
#tempTable________________________________________ __________________________
> _________________________________________000000058 628
> Please notice the appendix are different.
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
|||> > However, I don't why my tempdb size
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks
|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FL
EXEC tempdb..spaceused '#temptablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>
|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> the
does[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES.
> its
file
>
|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?[vbcol=seagreen]
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
determine[vbcol=seagreen]
> does
check[vbcol=seagreen]
> file
reason.
>
|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable________________________________________ __________________________
_________________________________________000000058 617
#tempTable________________________________________ __________________________
_________________________________________000000058 628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.
|||>
#tempTable________________________________________ __________________________
> _________________________________________000000058 617
>
#tempTable________________________________________ __________________________
> _________________________________________000000058 628
> Please notice the appendix are different.
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
|||> > However, I don't why my tempdb size
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks
|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
Check Temp table size?
I have a tempdb which fills up the disk frequently. I want to determine the
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FLEXEC tempdb..spaceused '#temptablename'
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > I have a tempdb which fills up the disk frequently. I want to determine
> the
> > size of each object in the tempdb.
> >
> > I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename"
does
> > not exist error. I got the temp table names from
> INFORMATION_SCHEMA.TABLES.
> > Since the temp tables only valid for their own session, how can I check
> its
> > size of temp tables through Query Analyzer?
> >
> > Another question is how to analyze the log file space usage? The log
file
> > for my TempDB grows dramatically fast too. I need understand the reason.
> >
> > Thanks a lot,
> > FL
> >
> >
> >
> >
> >
> >
> >
>|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> > EXEC tempdb..spaceused '#temptablename'
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "FLX" <nospam@.hotmail.com> wrote in message
> > news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > > I have a tempdb which fills up the disk frequently. I want to
determine
> > the
> > > size of each object in the tempdb.
> > >
> > > I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename"
> does
> > > not exist error. I got the temp table names from
> > INFORMATION_SCHEMA.TABLES.
> > > Since the temp tables only valid for their own session, how can I
check
> > its
> > > size of temp tables through Query Analyzer?
> > >
> > > Another question is how to analyze the log file space usage? The log
> file
> > > for my TempDB grows dramatically fast too. I need understand the
reason.
> > >
> > > Thanks a lot,
> > > FL
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable__________________________________________________________________
_________________________________________000000058617
#tempTable__________________________________________________________________
_________________________________________000000058628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.|||>
#tempTable__________________________________________________________________
> _________________________________________000000058617
>
#tempTable__________________________________________________________________
> _________________________________________000000058628
> Please notice the appendix are different.
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471|||> > However, I don't why my tempdb size
> > keeps growing and can not be shrinked.
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > However, I don't why my tempdb size
> > > keeps growing and can not be shrinked.
> >
> > http://www.aspfaq.com/2446
> > http://www.aspfaq.com/2471
> >
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FLEXEC tempdb..spaceused '#temptablename'
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > I have a tempdb which fills up the disk frequently. I want to determine
> the
> > size of each object in the tempdb.
> >
> > I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename"
does
> > not exist error. I got the temp table names from
> INFORMATION_SCHEMA.TABLES.
> > Since the temp tables only valid for their own session, how can I check
> its
> > size of temp tables through Query Analyzer?
> >
> > Another question is how to analyze the log file space usage? The log
file
> > for my TempDB grows dramatically fast too. I need understand the reason.
> >
> > Thanks a lot,
> > FL
> >
> >
> >
> >
> >
> >
> >
>|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> > EXEC tempdb..spaceused '#temptablename'
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "FLX" <nospam@.hotmail.com> wrote in message
> > news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > > I have a tempdb which fills up the disk frequently. I want to
determine
> > the
> > > size of each object in the tempdb.
> > >
> > > I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename"
> does
> > > not exist error. I got the temp table names from
> > INFORMATION_SCHEMA.TABLES.
> > > Since the temp tables only valid for their own session, how can I
check
> > its
> > > size of temp tables through Query Analyzer?
> > >
> > > Another question is how to analyze the log file space usage? The log
> file
> > > for my TempDB grows dramatically fast too. I need understand the
reason.
> > >
> > > Thanks a lot,
> > > FL
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable__________________________________________________________________
_________________________________________000000058617
#tempTable__________________________________________________________________
_________________________________________000000058628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.|||>
#tempTable__________________________________________________________________
> _________________________________________000000058617
>
#tempTable__________________________________________________________________
> _________________________________________000000058628
> Please notice the appendix are different.
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471|||> > However, I don't why my tempdb size
> > keeps growing and can not be shrinked.
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > However, I don't why my tempdb size
> > > keeps growing and can not be shrinked.
> >
> > http://www.aspfaq.com/2446
> > http://www.aspfaq.com/2471
> >
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
Check Temp table size?
I have a tempdb which fills up the disk frequently. I want to determine the
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FLEXEC tempdb..spaceused '#temptablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> the
does[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES.
> its
file[vbcol=seagreen]
>|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
determine[vbcol=seagreen]
> does
check[vbcol=seagreen]
> file
reason.[vbcol=seagreen]
>|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable______________________________
____________________________________
________________________________________
_000000058617
#tempTable______________________________
____________________________________
________________________________________
_000000058628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.|||>
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058617
>[/vbcol]
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058628
> Please notice the appendix are different.[/vbcol]
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471|||> > However, I don't why my tempdb size
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FLEXEC tempdb..spaceused '#temptablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> the
does[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES.
> its
file[vbcol=seagreen]
>|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
determine[vbcol=seagreen]
> does
check[vbcol=seagreen]
> file
reason.[vbcol=seagreen]
>|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable______________________________
____________________________________
________________________________________
_000000058617
#tempTable______________________________
____________________________________
________________________________________
_000000058628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.|||>
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058617
>[/vbcol]
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058628
> Please notice the appendix are different.[/vbcol]
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471|||> > However, I don't why my tempdb size
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
Check Table Size
Hello,
How can i check the size of one table ?
Best regards
Try sp_spaceused
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
Hello,
How can i check the size of one table ?
Best regards
|||Hi,
use dbname
go
sp_spaceused <table_name>
or
use <dbname>
go
sp_spaceused <table_name>, @.updateusage ='true'
@.updateusage will correct the inconsistencies in indexes and give you much
more perfected value.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
> Hello,
> How can i check the size of one table ?
> Best regards
How can i check the size of one table ?
Best regards
Try sp_spaceused
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
Hello,
How can i check the size of one table ?
Best regards
|||Hi,
use dbname
go
sp_spaceused <table_name>
or
use <dbname>
go
sp_spaceused <table_name>, @.updateusage ='true'
@.updateusage will correct the inconsistencies in indexes and give you much
more perfected value.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
> Hello,
> How can i check the size of one table ?
> Best regards
Check Table Size
Hello,
How can i check the size of one table ?
Best regardsTry sp_spaceused
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
Hello,
How can i check the size of one table ?
Best regards|||Hi,
use dbname
go
sp_spaceused <table_name>
or
use <dbname>
go
sp_spaceused <table_name>, @.updateusage ='true'
@.updateusage will correct the inconsistencies in indexes and give you much
more perfected value.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
> Hello,
> How can i check the size of one table ?
> Best regards
How can i check the size of one table ?
Best regardsTry sp_spaceused
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
Hello,
How can i check the size of one table ?
Best regards|||Hi,
use dbname
go
sp_spaceused <table_name>
or
use <dbname>
go
sp_spaceused <table_name>, @.updateusage ='true'
@.updateusage will correct the inconsistencies in indexes and give you much
more perfected value.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
> Hello,
> How can i check the size of one table ?
> Best regards
Check Table Size
Hello,
How can i check the size of one table ?
Best regardsTry sp_spaceused
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
Hello,
How can i check the size of one table ?
Best regards|||Hi,
use dbname
go
sp_spaceused <table_name>
or
use <dbname>
go
sp_spaceused <table_name>, @.updateusage ='true'
@.updateusage will correct the inconsistencies in indexes and give you much
more perfected value.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
> Hello,
> How can i check the size of one table ?
> Best regards
How can i check the size of one table ?
Best regardsTry sp_spaceused
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
Hello,
How can i check the size of one table ?
Best regards|||Hi,
use dbname
go
sp_spaceused <table_name>
or
use <dbname>
go
sp_spaceused <table_name>, @.updateusage ='true'
@.updateusage will correct the inconsistencies in indexes and give you much
more perfected value.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:fcda01c43e88$c17437b0$a001280a@.phx.gbl...
> Hello,
> How can i check the size of one table ?
> Best regards
Check space left
Hi,
I understand that the Express Edition can create databases up to a size of 4GB. Is there a way to check the space left for a particular database via C++?
Thanks in advance.
Regards
Melvin
You should be able to find some usefull commands in SMO, which you can call from C++. Start with the Size property and you'll find additional size related properties shown in the sample code.
Mike
|||hi,
i think u facing the problem regarding the property that is one property size property i am not exp in that so please check it up with that
Subscribe to:
Posts (Atom)