DBCC SHRINKDATABASE QUESTION

I am archiving about 25 gigs of images from our OLTP database to an archive database. After archiving these images(table which contains a column datatype image), there is about 25 gigs of unallocated space reported by EM. I run the normal Shrink database routine
DBCC SHRINKDATABASE
( database_name [ , target_percent ] )
--I set the target percent low(about 5%), current db size after archive is 75 gigs so it should shrink to about 79 gigs...
and instead of releasing the free space to the operating system the routine actually increases the used space of the db to fill almost all the newly freed space. Basically I remove 25 gigs of data, run the routine and now magically the database grows 25 gigs instead of freeing up 25 gigs.(now the db is back to the orginal size before archive process!!) I've only experienced this when dealing with image datatypes. I am wondering if the way images are stored has something to do with this unexpected behavior. If anybody has any insight please let me know. ThanksYes this is a know issue that it can happen sometimes but I don't know if
there is a KB regarding this or not. Try using DBCC CLEANTABLE and see if
that helps. If not then you might have to BCP out all the data from that
table, truncate it and bcp it back in.
--
Andrew J. Kelly
SQL Server MVP
"mike petanovitch" <mpetanovitch@.hotmail.com> wrote in message
news:5DB04B5D-64C6-4422-AEB4-A1E99BEE8574@.microsoft.com...
> I am archiving about 25 gigs of images from our OLTP database to an
archive database. After archiving these images(table which contains a
column datatype image), there is about 25 gigs of unallocated space reported
by EM. I run the normal Shrink database routine:
> DBCC SHRINKDATABASE
> ( database_name [ , target_percent ] )
> --I set the target percent low(about 5%), current db size after archive is
75 gigs so it should shrink to about 79 gigs...
> and instead of releasing the free space to the operating system the
routine actually increases the used space of the db to fill almost all the
newly freed space. Basically I remove 25 gigs of data, run the routine
and now magically the database grows 25 gigs instead of freeing up 25
gigs.(now the db is back to the orginal size before archive process!!)
I've only experienced this when dealing with image datatypes. I am
wondering if the way images are stored has something to do with this
unexpected behavior. If anybody has any insight please let me know. Thanks

dbcc showcontig with tablersults

DBCC SHOWCONTIG WITH TABLERESULTS will show a ROW column for an index's row count. However, on an important table with quite a few indexes that I am using as a baseline, ROW is NULL for all indexes. Why? BOL definines ROWS as:-

"Number of data or index records at that level of the index. For a heap, this is the number of data records in the entire heap."

By the way, if I select from sysindexes for a one of the above indexes, there is a correct rowcount for the index.

Any ideas?

CliveI found it. I was using the 'WITH FAST' parameter. Without FAST, it returns the row count.

Clive

dbcc show_statistics wrong output

Has anyone ever come across the rows column in dbcc show_statistics not
reporting the right number of rows( I mean way off : Actual count maybe 500
whereas the rows column indicates 50000 ) even after stats are being updated
? Could that be a bug ? Using SQL 2K and SP2 with Slammer HF ofcourseThey may well not be accurate. You need to run DBCC UPDATEUSAGE or UPDATE
STATISTICS WITH FULLSCAN to get them right. See BOL for details
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uaO6TeduDHA.560@.TK2MSFTNGP11.phx.gbl...
Has anyone ever come across the rows column in dbcc show_statistics not
reporting the right number of rows( I mean way off : Actual count maybe 500
whereas the rows column indicates 50000 ) even after stats are being updated
? Could that be a bug ? Using SQL 2K and SP2 with Slammer HF ofcourse

DBCC LOGINFO('DBname')

When I run DBCC LOGINFO('DBname') I get 2 and 0 for Status column. How do I
make value 2 as zero before I can truncate the logfile?
ThanksTry to backup the transaction log. then look again.

>--Original Message--
>When I run DBCC LOGINFO('DBname') I get 2 and 0 for
Status column. How do I make value 2 as zero before I can
truncate the logfile?
>Thanks
>.
>

DBCC LOGINFO question

The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset that
includes a column entitled CreateLSN, does anybody know what this is and
what it means? I've tried to find what it is but met with no success so far.
It seems to have changed as well from earlier version of SQL where this
column used to be a date, and had a different name.
TIA
Michael MacGregor
Database Architect
This column is also defined as create_lsn in the (documented) catalog view
sys.database_files. It is defined as "Log sequence number (LSN) at which
the file was created."
For more information about LSNs, I recommend starting with the BOL topic
titled "Introduction to Log Sequence Numbers".
Regards,
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pro...ads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:ekkdPEx4GHA.4256@.TK2MSFTNGP03.phx.gbl...
> The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset
> that includes a column entitled CreateLSN, does anybody know what this is
> and what it means? I've tried to find what it is but met with no success
> so far. It seems to have changed as well from earlier version of SQL where
> this column used to be a date, and had a different name.
> TIA
> Michael MacGregor
> Database Architect
>
|||Thanks Gail.
MTM
|||Ok, read that but unfortunately it doesn't really help me much.
What I'm trying to find out is at what time, and by how much, the
transaction log grows? I was hoping that this CreateLSN might have helped,
but I can't see how it can. Is there any other way to find this out?
Michael MacGregor
Database Architect
|||The CreateLSN is really only interesting for backup/restore purposes. You
might consider using Profiler to monitor for events that cause the log file
to autogrow. See the topic "Log File Auto Grow Event Class."
Maybe one or more of the MVPs will jump in with some recommendations as
well.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pro...ads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
> Ok, read that but unfortunately it doesn't really help me much.
> What I'm trying to find out is at what time, and by how much, the
> transaction log grows? I was hoping that this CreateLSN might have helped,
> but I can't see how it can. Is there any other way to find this out?
> Michael MacGregor
> Database Architect
>
|||I have a feeling that is it difficult to get the time of the grow operations, at least from what is
exposed. Event though we have the LSN, and each LSN has a timestamp, we would need to grab the
original log records to map that LSN to a timestamp. fn_dblog() can probably show us the timestamp
for the log record based on the LSN, but that requires that the log record exists in the tlog.
Why not have a job that monitors the log at desired frequency? Or, use a server-side trace to
capture the autogrow operations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:uZpFSjz4GHA.900@.TK2MSFTNGP04.phx.gbl...
> The CreateLSN is really only interesting for backup/restore purposes. You might consider using
> Profiler to monitor for events that cause the log file to autogrow. See the topic "Log File Auto
> Grow Event Class."
> Maybe one or more of the MVPs will jump in with some recommendations as well.
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pro...ads/books.mspx
> "Michael MacGregor" <nospam@.nospam.com> wrote in message
> news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
>

DBCC LOGINFO question

The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset that
includes a column entitled CreateLSN, does anybody know what this is and
what it means? I've tried to find what it is but met with no success so far.
It seems to have changed as well from earlier version of SQL where this
column used to be a date, and had a different name.
TIA
Michael MacGregor
Database ArchitectThis column is also defined as create_lsn in the (documented) catalog view
sys.database_files. It is defined as "Log sequence number (LSN) at which
the file was created."
For more information about LSNs, I recommend starting with the BOL topic
titled "Introduction to Log Sequence Numbers".
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:ekkdPEx4GHA.4256@.TK2MSFTNGP03.phx.gbl...
> The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset
> that includes a column entitled CreateLSN, does anybody know what this is
> and what it means? I've tried to find what it is but met with no success
> so far. It seems to have changed as well from earlier version of SQL where
> this column used to be a date, and had a different name.
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks Gail.
MTM|||Ok, read that but unfortunately it doesn't really help me much.
What I'm trying to find out is at what time, and by how much, the
transaction log grows? I was hoping that this CreateLSN might have helped,
but I can't see how it can. Is there any other way to find this out?
Michael MacGregor
Database Architect|||The CreateLSN is really only interesting for backup/restore purposes. You
might consider using Profiler to monitor for events that cause the log file
to autogrow. See the topic "Log File Auto Grow Event Class."
Maybe one or more of the MVPs will jump in with some recommendations as
well.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
> Ok, read that but unfortunately it doesn't really help me much.
> What I'm trying to find out is at what time, and by how much, the
> transaction log grows? I was hoping that this CreateLSN might have helped,
> but I can't see how it can. Is there any other way to find this out?
> Michael MacGregor
> Database Architect
>|||I have a feeling that is it difficult to get the time of the grow operations
, at least from what is
exposed. Event though we have the LSN, and each LSN has a timestamp, we woul
d need to grab the
original log records to map that LSN to a timestamp. fn_dblog() can probably
show us the timestamp
for the log record based on the LSN, but that requires that the log record e
xists in the tlog.
Why not have a job that monitors the log at desired frequency? Or, use a ser
ver-side trace to
capture the autogrow operations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:uZpFSjz4GHA.900@.TK2MSFTNGP04.phx.gbl...
> The CreateLSN is really only interesting for backup/restore purposes. You
might consider using
> Profiler to monitor for events that cause the log file to autogrow. See t
he topic "Log File Auto
> Grow Event Class."
> Maybe one or more of the MVPs will jump in with some recommendations as we
ll.
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Michael MacGregor" <nospam@.nospam.com> wrote in message
> news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
>

DBCC LOGINFO question

The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset that
includes a column entitled CreateLSN, does anybody know what this is and
what it means? I've tried to find what it is but met with no success so far.
It seems to have changed as well from earlier version of SQL where this
column used to be a date, and had a different name.
TIA
Michael MacGregor
Database ArchitectThis column is also defined as create_lsn in the (documented) catalog view
sys.database_files. It is defined as "Log sequence number (LSN) at which
the file was created."
For more information about LSNs, I recommend starting with the BOL topic
titled "Introduction to Log Sequence Numbers".
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:ekkdPEx4GHA.4256@.TK2MSFTNGP03.phx.gbl...
> The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset
> that includes a column entitled CreateLSN, does anybody know what this is
> and what it means? I've tried to find what it is but met with no success
> so far. It seems to have changed as well from earlier version of SQL where
> this column used to be a date, and had a different name.
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks Gail.
MTM|||Ok, read that but unfortunately it doesn't really help me much.
What I'm trying to find out is at what time, and by how much, the
transaction log grows? I was hoping that this CreateLSN might have helped,
but I can't see how it can. Is there any other way to find this out?
Michael MacGregor
Database Architect|||The CreateLSN is really only interesting for backup/restore purposes. You
might consider using Profiler to monitor for events that cause the log file
to autogrow. See the topic "Log File Auto Grow Event Class."
Maybe one or more of the MVPs will jump in with some recommendations as
well.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
> Ok, read that but unfortunately it doesn't really help me much.
> What I'm trying to find out is at what time, and by how much, the
> transaction log grows? I was hoping that this CreateLSN might have helped,
> but I can't see how it can. Is there any other way to find this out?
> Michael MacGregor
> Database Architect
>|||I have a feeling that is it difficult to get the time of the grow operations, at least from what is
exposed. Event though we have the LSN, and each LSN has a timestamp, we would need to grab the
original log records to map that LSN to a timestamp. fn_dblog() can probably show us the timestamp
for the log record based on the LSN, but that requires that the log record exists in the tlog.
Why not have a job that monitors the log at desired frequency? Or, use a server-side trace to
capture the autogrow operations...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:uZpFSjz4GHA.900@.TK2MSFTNGP04.phx.gbl...
> The CreateLSN is really only interesting for backup/restore purposes. You might consider using
> Profiler to monitor for events that cause the log file to autogrow. See the topic "Log File Auto
> Grow Event Class."
> Maybe one or more of the MVPs will jump in with some recommendations as well.
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Michael MacGregor" <nospam@.nospam.com> wrote in message
> news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
>> Ok, read that but unfortunately it doesn't really help me much.
>> What I'm trying to find out is at what time, and by how much, the transaction log grows? I was
>> hoping that this CreateLSN might have helped, but I can't see how it can. Is there any other way
>> to find this out?
>> Michael MacGregor
>> Database Architect
>

DBCC loginfo

I've been using this useful undocumented DBCC command
recently but the only information I can find on the Status
column it returns, is that above 0 means that the virtual
log is in use.
Now all the VLF's I examined had a status of 2 and these
we're not uncommitted or undistrubted, just not backed
up. These returned to a status of 0 when backed up.
So, does anyone know what the values in the Status field
mean? How is say 1 different to 2? What other values can
it take?
Graham Davies
DBAI think a value of 1 is the VL that SQL is currently writing to, 0 is the
inactive part of the log and 2 is the active part of the log... Only the
inactive part of the log can be shrunk away...
BTW, this is undocumented and these are just my observations ( which may be
incorrect.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Graham Davies" <anonymous@.discussions.microsoft.com> wrote in message
news:186401c43f48$a96c9fc0$3a01280a@.phx.gbl...
> I've been using this useful undocumented DBCC command
> recently but the only information I can find on the Status
> column it returns, is that above 0 means that the virtual
> log is in use.
> Now all the VLF's I examined had a status of 2 and these
> we're not uncommitted or undistrubted, just not backed
> up. These returned to a status of 0 when backed up.
> So, does anyone know what the values in the Status field
> mean? How is say 1 different to 2? What other values can
> it take?
> Graham Davies
> DBA
>

DBCC CHECKIDENT (jobs, RESEED, 30) does not work

Hi,

I'm trying to set up merge replication and when I try to synchrinize with the subscriber I get the error "invalid column name ROWGUIDCOL". Following an article it recomended to reseed the identity columns on the subscriber so that there is no conflict. Now the tables with the identity column have the "Not for replication "option on. Using DBCC CHECKIDENT (table_name, RESEED, some_value) does not change the SEED when I check. SQL server help file remarks on DBCC CHECKIDENT say that if the column was created with the "Not for replication " option on it cannot change the value.

From sql server help file:

Remarks

If necessary, DBCC CHECKIDENT corrects the current identity value for a column. The current identity value is not corrected, however, if the identity column was created with the NOT FOR REPLICATION clause (in either the CREATE TABLE or ALTER TABLE statement).

Any way around it or any help so I dont get that "invalid column name ROWGUIDCOL" error?

Thanks allot. Looking forword

George

I had a similar problem before, but my problem was that I add a column with the name ROWGUID but I did forget to mark this field as ROWGUIDCOL, then the replication process make another ROWGUID field but with other name. Check that this is not the case.

I hope this can help you.

|||

Can you turn on profiler and see if a query failed due to invalid column name ROWGUIDCOL?

Thanks.

|||

Thanks for the advice

Digging a bit more Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes

This happens on the subscriber when I start the merge agent from the publisher.

George

|||

Hello George,

More questions:

1. Before you setup merge replication between 2 servers, does users table already available on both servers? Can I assume they have the same schema?

2. If tables available on both servers already, did you setup merge replication subscription using "NOSYNC" (do sp_addmergesubscription, and let @.sync_type = 'NONE')?

3. Do both tables, non of them has rowguidcol column, one has, both have?

4. Do you have a long table name?

5. I assume both publisher and subscriber are SQL server 2000.

Thanks.

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

|||

Hello George,

Please try this, running the follow DDL on the subscriber database.

ALTER TABLE [table_need_to_be_merged] ADD rowguid uniqueidentifier ROWGUIDCOL

Drop the subscription and re-create it with "NOSYNC" option. (SELECT "No, the Subscriber already has the schema and data" from the "Push Subscription Wizard" on the "Initialize Subscription" Page).

The subscription is created and the error message "Invalid column name 'rowguidcol'" should be disappeared.

If this method is still not working for you, I may need to look at your table schema.

Thanks.

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

|||

Hello Zhou,

Answers to your questions

1. Both databases are available on both servers with the same schema

2. I used NOSYNC frof the wizard

3. One of them (the publisher ) has rowguid column filled. On the subscriber I used your script to created it but is not filled. The merge agent says "No data needed to be merged"

4. The longest table name has 56 characters.

5. Both publisher and subscriber are SQL server 2000.

Thanks

|||

1. So you can create the merge sync by publication and subscription.

2. After you set up the sync and there is no data merged between the publisher and subscriber.

3. Try to update all your rowguid column on the subscriber with newid() and try to sync.

4. if no data sync from step3, try to do a dummy update such as update [table name] set column1 = column1 and do sync.

Thanks.

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

DBCC CHECKDB Reporting Errors in - SQL Server 2005

HI,
I am getting this ttype of errors in SQL Server 2005, This table have xml
column.
Msg 8964, Sev 16, State 1, Line 1 : Table error: Object ID 304720138, index
ID 1, partition ID 72057594055557120, alloc unit ID 72057594058702848 (type
LOB data). The off-row data node at page (4:26), slot 0, text ID 19136512 is
not referenced. [SQLSTATE 42000]
Please give some help on this , what will be the route cause of this problem?
how to resolve it.Hi
You may want to check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_0prl.asp
You may also want to check that this has not been re-introduced
http://support.microsoft.com/default.aspx?scid=kb;en-us;281287
Try dropping and re-creating the index. If the error persists then you
should log it with PSS.
John
"Srikanth" wrote:
> HI,
> I am getting this ttype of errors in SQL Server 2005, This table have xml
> column.
> Msg 8964, Sev 16, State 1, Line 1 : Table error: Object ID 304720138, index
> ID 1, partition ID 72057594055557120, alloc unit ID 72057594058702848 (type
> LOB data). The off-row data node at page (4:26), slot 0, text ID 19136512 is
> not referenced. [SQLSTATE 42000]
> Please give some help on this , what will be the route cause of this problem?
> how to resolve it.
>
>