IF i uninstall SQL and reinstall it, how do I get my databases back online from my origina

Sorry, newbie here...
BJHi,
"BJ" <lmelogra@.optonline.net> wrote in message
news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
> Sorry, newbie here...
> BJ
>|||Hi,
Before Reinstall:-
1. For safe take the backup of databases
2. Detach the databases (SP_detach_db)
3. Copy the MDF and LDF to a safe location
After install
1. Apply the same service pack
2. Restore the master database
3. Restore the other database one by one or use SP_ATTCH_DB to attach the
databases
Thanks
Hari
SQL Server MVP
"BJ" <lmelogra@.optonline.net> wrote in message
news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
> Sorry, newbie here...
> BJ
>|||ok, so if I installed MS CRM which creates a few databases with the newly
created MASTER database before I did any kind of restore it fails? I did
use this which I think will work?:
Did a SQL database backup to D:\<database_1>
then loaded new SQL server.
Ran the RESTORE database option in SQL pointing it to the existing databases
on my hard drive. They show up in SQL.
But, one database we did NOT run the backup on for Goldmine. Any way to
restore that now or are we SOL?
THANKS!
BJ
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uchspB9uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Before Reinstall:-
> 1. For safe take the backup of databases
> 2. Detach the databases (SP_detach_db)
> 3. Copy the MDF and LDF to a safe location
> After install
> 1. Apply the same service pack
> 2. Restore the master database
> 3. Restore the other database one by one or use SP_ATTCH_DB to attach the
> databases
>
> --
> Thanks
> Hari
> SQL Server MVP
> "BJ" <lmelogra@.optonline.net> wrote in message
> news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
> > Sorry, newbie here...
> >
> > BJ
> >
> >
>|||Hi,
Check if the MDF and LDF file exists for that database,if exits then you try
using sp_attach_db (see books onlinr) to attach the database back to sql
server.
Thanks
Hari
SQL Server MVP
"BJ" <lmelogra@.optonline.net> wrote in message
news:u5kmQG9uEHA.4020@.TK2MSFTNGP10.phx.gbl...
> ok, so if I installed MS CRM which creates a few databases with the newly
> created MASTER database before I did any kind of restore it fails? I did
> use this which I think will work?:
> Did a SQL database backup to D:\<database_1>
> then loaded new SQL server.
> Ran the RESTORE database option in SQL pointing it to the existing
> databases
> on my hard drive. They show up in SQL.
> But, one database we did NOT run the backup on for Goldmine. Any way to
> restore that now or are we SOL?
> THANKS!
> BJ
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uchspB9uEHA.3200@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Before Reinstall:-
>> 1. For safe take the backup of databases
>> 2. Detach the databases (SP_detach_db)
>> 3. Copy the MDF and LDF to a safe location
>> After install
>> 1. Apply the same service pack
>> 2. Restore the master database
>> 3. Restore the other database one by one or use SP_ATTCH_DB to attach the
>> databases
>>
>> --
>> Thanks
>> Hari
>> SQL Server MVP
>> "BJ" <lmelogra@.optonline.net> wrote in message
>> news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
>> > Sorry, newbie here...
>> >
>> > BJ
>> >
>> >
>>
>|||Yes they are there!! we copied this folder to another drive! Thank you!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23OvJo79uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Check if the MDF and LDF file exists for that database,if exits then you
try
> using sp_attach_db (see books onlinr) to attach the database back to sql
> server.
>
> --
> Thanks
> Hari
> SQL Server MVP
> "BJ" <lmelogra@.optonline.net> wrote in message
> news:u5kmQG9uEHA.4020@.TK2MSFTNGP10.phx.gbl...
> > ok, so if I installed MS CRM which creates a few databases with the
newly
> > created MASTER database before I did any kind of restore it fails? I
did
> > use this which I think will work?:
> >
> > Did a SQL database backup to D:\<database_1>
> >
> > then loaded new SQL server.
> >
> > Ran the RESTORE database option in SQL pointing it to the existing
> > databases
> > on my hard drive. They show up in SQL.
> >
> > But, one database we did NOT run the backup on for Goldmine. Any way to
> > restore that now or are we SOL?
> >
> > THANKS!
> >
> > BJ
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:uchspB9uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> Before Reinstall:-
> >>
> >> 1. For safe take the backup of databases
> >> 2. Detach the databases (SP_detach_db)
> >> 3. Copy the MDF and LDF to a safe location
> >>
> >> After install
> >>
> >> 1. Apply the same service pack
> >> 2. Restore the master database
> >> 3. Restore the other database one by one or use SP_ATTCH_DB to attach
the
> >> databases
> >>
> >>
> >> --
> >> Thanks
> >> Hari
> >> SQL Server MVP
> >>
> >> "BJ" <lmelogra@.optonline.net> wrote in message
> >> news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
> >> > Sorry, newbie here...
> >> >
> >> > BJ
> >> >
> >> >
> >>
> >>
> >
> >
>

if exists condition..

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?

regardsfireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?

Whichever you fancy. I would probably write the second nine times out
of ten, but that it would only be because db_id() would not come in
my mind. And a nice characteristic of the first, is that it works on
SQL 2000 as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I would probably write the second
[...]

thank you.
so you would say, first solution is not less professional at all then the
second one.. - right?|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I would probably write the second

[...]
>
thank you.
so you would say, first solution is not less professional at all then the
second one.. - right?

Yes, both are perfectly OK, and which you pick is a matter of taste or
the flux of the moment.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The first one; its more portable between versions of SQL Server, the second
only works in SQL 2005 and upwards.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"fireball" <fireball@.onet.kropka.euwrote in message
news:embrdl$n8d$1@.atlantis.news.tpi.pl...

Quote:

Originally Posted by

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?
>
>
>
>
regards
>
>

identity ranges on republished restore of db

Hi folks,
We are having issues with restores of our production databases into our
test environments.
Our production environment is SQL server 2000 sp3 with merge
replication with 3 publications using automatic identity range
management.
The problem we're having is that when we try to restore a production
backup into a test environment and republish the database - inserts on
the server are failing due to the identity ranges being full. We run
the sp_adjustpublisheridentityrange stored procedure but this produces
inconsistent results. Sometimes it fixes the problem and allows inserts
to proceed but other times it doesnt
What we see happening is that it might fix the first table the range is
full on but when we try an insert on another table we get the range
full message again.
Its causing us major grief for an app we have thats trying to merge
duplicate customers on our system. The merge process involves lots of
inserts server side as we make back up copies of the customers before
we try merging them etc.
i guess what we're after is a consistent way to set/reset and make sure
identity ranges are ok across the database once we republish it.
Any help would be greatly appreciated
Thanks,
Michael
This should be working. What you should do is run your merge agents at a
greater frequency. You might also want to change your subscriber ranges to
match the largest increment which might ever occur on your subscriber
between merge agent runs. So if your merge agent runs once a week, and you
do 1000 inserts on the subscriber at a max in this interval, tune your
subscriber range accordingly.
To address your problem
Run dbcc checkident('tablename')
Get the current value
then issue a select max(identititycolumn) from tablename and get the current
value in your table.
reseed for this value
run dbcc checkident('tablename',reseed, maxvalue)
The alter your check constraint
to do this issue sp_help tablename
look for the check constraint called something like
repl_identity_range_pub_84EB4FA5_28B9_4AD2_82CC_27 B1CE554D0D
Alter this constrainst for your new range.
Keep in mind when you blow a range, subsequent inserts fail, but the
identity value is incremented.
Also adjust the MSrepl_identity_range table in your distribution database
for this new range.
The merge agent polls sysmergearticles on your subscriber with each run to
see if any of them have automatic identity range management support. It
should adjust the identity ranges automatically with each run. I am not
exactly sure why this is not working for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117018628.625701.77440@.g47g2000cwa.googlegro ups.com...
> Hi folks,
> We are having issues with restores of our production databases into our
> test environments.
> Our production environment is SQL server 2000 sp3 with merge
> replication with 3 publications using automatic identity range
> management.
> The problem we're having is that when we try to restore a production
> backup into a test environment and republish the database - inserts on
> the server are failing due to the identity ranges being full. We run
> the sp_adjustpublisheridentityrange stored procedure but this produces
> inconsistent results. Sometimes it fixes the problem and allows inserts
> to proceed but other times it doesnt
> What we see happening is that it might fix the first table the range is
> full on but when we try an insert on another table we get the range
> full message again.
> Its causing us major grief for an app we have thats trying to merge
> duplicate customers on our system. The merge process involves lots of
> inserts server side as we make back up copies of the customers before
> we try merging them etc.
> i guess what we're after is a consistent way to set/reset and make sure
> identity ranges are ok across the database once we republish it.
> Any help would be greatly appreciated
> Thanks,
> Michael
>
|||Hi Hilary,
Thanks very much for the quick reply. We have a script which checks the
current ident for each table - - then compares this value to the max
column value in each table. If the max column value is above the seed
value it sets the seed value to be the max column value plus 1 doing a
run dbcc checkident('tablename',reseed, maxvalue+1)
do we also need to update the constraint on the table? and if so what
do you set the constraint values to be?
For example one of our tables is called ApplProduct
The max value in the identity column is 1881002
The current identity value is 12427472
The current range constraint on the table is
([ApplProd_ID] > 11490000 and [ApplProd_ID] < 13490000)
After we run our script to produce the new seed value is produces the
DBCC command below:
DBCC CHECKIDENT ('ApplProduct', RESEED, 18810003)
We dont alter the constraint... i would have thought the
adjustpublisheridentityrang=ADe sp might do that? If we need to alter
the constraint what should the new value be?
Again your help is much appreciated
Thanks,
Michael
|||You only need to update the constraint IF non replication processes will be
inserting the rows, i.e. if you or your app want to insert the rows on the
subscriber.
First off I would reseed your table to DBCC CHECKIDENT ('ApplProduct',
RESEED, 1881002)
Basically from what I see you have inserted or attempted to insert
12427472 -1881002 rows which the constraint has kicked back a mere 10546470
rows.
I'd alter the constraint for this range as well
([ApplProd_ID] > 1881000 and [ApplProd_ID] < 3000000)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117023368.665062.255950@.g47g2000cwa.googlegr oups.com...
Hi Hilary,
Thanks very much for the quick reply. We have a script which checks the
current ident for each table - - then compares this value to the max
column value in each table. If the max column value is above the seed
value it sets the seed value to be the max column value plus 1 doing a
run dbcc checkident('tablename',reseed, maxvalue+1)
do we also need to update the constraint on the table? and if so what
do you set the constraint values to be?
For example one of our tables is called ApplProduct
The max value in the identity column is 1,881,002
The current identity value is 12,427,472
The current range constraint on the table is
([ApplProd_ID] > 11490000 and [ApplProd_ID] < 13490000)
After we run our script to produce the new seed value is produces the
DBCC command below:
DBCC CHECKIDENT ('ApplProduct', RESEED, 18810003)
We dont alter the constraint... i would have thought the
adjustpublisheridentityrangXe sp might do that? If we need to alter
the constraint what should the new value be?
Again your help is much appreciated
Thanks,
Michael
|||Our insertions are happening on the server Hilary - does this make any
difference? Our inserts all use SCOPE IDENTITY to get their values so i
would have thought we'd be ok i.e. we'd be inserting in a replication
friendly way?
Thanks,
Michael
|||Hi folks,
It looks like we need to do a lot more around identity ranges for bulk
server side inserts. From our investigations it looks like our initial
server ranges on some tables are almost full. The laptop(subscriber)
ranges all seem to sit above the server range at the moment. The server
has a much larger range than any of the subscribers - 2 million plus
(to handle out initial migration and nightly imports from another
database). One solution might be to reset our server range on all the
tables to above the current max subscriber range. That would give us a
fresh start as it were in a new range and hopefully the level of server
side inserts wont be as large to blow the range again for some time
If we leave the ranges as is then we're likely to have to build some
sort of range checking into our inserts to check when the server range
is near its max and re adjust accordingly.
apart from leaving gaps in our indenties will reseting them upset
anything else in the db?
Cheers,
Michael
|||I'm having a problem understanding how you are using the scope_identity
property.
What I said holds for both inserts occurring at the publisher or subscriber.
Just make sure you adjust the publisher range accordingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117032974.032687.146510@.g44g2000cwa.googlegr oups.com...
> Our insertions are happening on the server Hilary - does this make any
> difference? Our inserts all use SCOPE IDENTITY to get their values so i
> would have thought we'd be ok i.e. we'd be inserting in a replication
> friendly way?
> Thanks,
> Michael
>
|||Possibly. One thing that worked for one client of mine was not to use
automatic identity range management and just travel to the publisher or
subscriber, issue a dbcc checkident('tablename',reseed,12312396490)
on an as needed basis.
We used large ranges and monitored to see when we were getting close to
being full.
What made this work for us is that we knew we weren't going to grow, and had
a good handle on where data was going to be inserted and how much.
So, it was basically the set it and forget it philosophy, although it
required some monitoring. Naturally after we got everything working and all
the bugs shaken out, management added 10 more subscribers.
Automatic Identity Range Management is a maintenance free way, scalable way
of partitioning and efficiently using identity ranges. With planning you
should be able to have it parcel out the identity range chunks you need. You
should by now have a handle on what the representative subscriber and
publisher ranges should be.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117100177.816930.108770@.z14g2000cwz.googlegr oups.com...
> Hi folks,
> It looks like we need to do a lot more around identity ranges for bulk
> server side inserts. From our investigations it looks like our initial
> server ranges on some tables are almost full. The laptop(subscriber)
> ranges all seem to sit above the server range at the moment. The server
> has a much larger range than any of the subscribers - 2 million plus
> (to handle out initial migration and nightly imports from another
> database). One solution might be to reset our server range on all the
> tables to above the current max subscriber range. That would give us a
> fresh start as it were in a new range and hopefully the level of server
> side inserts wont be as large to blow the range again for some time
> If we leave the ranges as is then we're likely to have to build some
> sort of range checking into our inserts to check when the server range
> is near its max and re adjust accordingly.
> apart from leaving gaps in our indenties will reseting them upset
> anything else in the db?
> Cheers,
> Michael
>
|||I've been following along this thread, as it sounds similar to an issue I'm
ranting about above ... one question in this thread: how should the check
constraint be altered?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Oj8D31gYFHA.796@.TK2MSFTNGP09.phx.gbl...
> Possibly. One thing that worked for one client of mine was not to use
> automatic identity range management and just travel to the publisher or
> subscriber, issue a dbcc checkident('tablename',reseed,12312396490)
> on an as needed basis.
> We used large ranges and monitored to see when we were getting close to
> being full.
> What made this work for us is that we knew we weren't going to grow, and
> had
> a good handle on where data was going to be inserted and how much.
> So, it was basically the set it and forget it philosophy, although it
> required some monitoring. Naturally after we got everything working and
> all
> the bugs shaken out, management added 10 more subscribers.
> Automatic Identity Range Management is a maintenance free way, scalable
> way
> of partitioning and efficiently using identity ranges. With planning you
> should be able to have it parcel out the identity range chunks you need.
> You
> should by now have a handle on what the representative subscriber and
> publisher ranges should be.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> <micks_address@.yahoo.co.uk> wrote in message
> news:1117100177.816930.108770@.z14g2000cwz.googlegr oups.com...
>

Identity field property lost after import tables between databases

I have found an issue on sql server 2005 data importing function:

For example, I have two databases named DB_A and DB_B. If it has a

table named table_a on DB_A with an identity field (say field_a), then

after transfer table_a to DB_B by using the 'Import and Export Wizard'

of MS SQL Server Management Studio, the identity field of table_a on

DB_B is no longer an identity field. i.e. DB_B.table_a.field_a is not

an identity field any more.

This issue is remain the same as transfering tables from sql server 2000 to 2005, or from sql server 2005 to 2005.

Has anyone know how to work around for this problem?

Thanks in advance.

Athens Yan

P.S.: I have using sql server 2005 with SP1 applied (9.0.2047).

Nobody know that? Or I am the only person to deal with this problem? I just can't believe it!|||

You are not alone. I tried to import tables from a SQL 2005 database into another SQL 2005 database (same instance) and have encountered the problem as well. I noticed that the default setting in the SQL DTSWizard has Identity_Insert not checked. However, checked or not checked, the wizard does not retains the value of identity column.

Have you found a solution?

|||Buddy, it is a dead end for using tools of Microsoft SQL Server 2005 to fix this issue. No any workable way is found. I write a short program to get the job done. The brief of my method is as follows:

Let the source database called DB-A, and the target database is DB-B.

1. Rebuild the database structure of DB-A to DB-B by using 'sysobjects' and 'sp_columns'.

2. Set identity insertion mode on.

3. Read data from all tables on DB-A one by one, and then insert into the corresponding tables on DB-B.

The way is stupid, but work for me. May be it work for you also.

Good luck.
Athens Yan|||

Thanks for the reply.

I started to take the same approach you out lined...not ideal...but as you said, it works.

Thanks again.

Identity crisis: GUID VS. Range

Hi!
I'm developing a project in which I have several Sql Server Express and
Sql Server Mobile 2005 databases and all of them are subscribed to a
central Sql Server 2005 database.
Now, I'm in trouble with the primary keys because if i insert two
registers with the same primary key in two different subscribers, when
the record is merged to the publisher, there is a primary key
collision.
I'm studying two different solutions:
A) Assign ranges of primary keys in each subscriber in order to assign
different IDs in each subscriber
B) User GUID as primary keys, so each primary key will be diferent.
Which are the pros and cons of each solution? Do you think one is best
than the other? Will I have performance problems using GUIDs in a Sql
Server Mobile database?
Thaks in advance!
Jordi
Guid's don't perform as well as int identity. If you use the
newsequentialid() property you will get sequential guids but although
fragmentation is reduced you still don't get the performance of a int data
type.
Your choices are to extend your primary key to include a location identifier
or implement automatic identity range management.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jordics@.gmail.com> wrote in message
news:1166626358.727257.205170@.t46g2000cwa.googlegr oups.com...
> Hi!
> I'm developing a project in which I have several Sql Server Express and
> Sql Server Mobile 2005 databases and all of them are subscribed to a
> central Sql Server 2005 database.
> Now, I'm in trouble with the primary keys because if i insert two
> registers with the same primary key in two different subscribers, when
> the record is merged to the publisher, there is a primary key
> collision.
> I'm studying two different solutions:
> A) Assign ranges of primary keys in each subscriber in order to assign
> different IDs in each subscriber
> B) User GUID as primary keys, so each primary key will be diferent.
> Which are the pros and cons of each solution? Do you think one is best
> than the other? Will I have performance problems using GUIDs in a Sql
> Server Mobile database?
> Thaks in advance!
>
> Jordi
>

Identifying unused columns in multiple databases

Hi - we have a situation where we have 23 identical databases on
servers across the country. We know that there are fields within this
db that no one is using and would like to be able to identify all of
them so they can be removed across the board. (There are multiple
tables and columns) We would of course have to verify that none of the
23 offices are using a particular field before it's removed as the db's
need to stay identical. (data in each db varies).
Anyway - I came across this posting which sounds like what we're
looking for, but it specifically references varchar and nvarchar fields
- I can't figure out what to modify in it to make this work for any
type of field. Can anyone point me in the right direction?
This is the posting:
> How can I find all the varchar columns in a table?

> I'm looking for an existing empty or null column rather than add a column.
This will generate a script for the current database that will tell you
all
of the varchar or nvarchar columns, and what percentage of them are
"unused" -- either NULL or empty strings.
SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK) WHERE
LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
UNION
SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%varchar'
You can run this in Query Analyzer, and it will generate a script in
the
bottom pane. Copy to a new query window and let 'er rip. Should take
a
while on larger databases. I use WITH (NOLOCK) for this kind of task
to
avoid concurrency and blocking issues; if you need an
up-to-the-millisecond
guaranteed read on the whole table, you may opt to leave that out (but
I do
not recommend it on a production server).
Thank you in advance.Please do not take offense, but if are not familiar enough with SQL Server
and SQL in general to make the necessary change to this query, then you
really are not qualified to be working on the changes you are talking about.
I am not trying to be mean, but your question is much simpler than the task
you intend to eventually perform. If you need help at this point, you will
get disastrous results later on. It is best to get someone who is more of
an expert in such things to handle this project.
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
column.
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||By "using the column", do you mean:
#1 if the column is populated with at least some data
#2 if there are any queries or procedures that actually reference the
column
The programming below seems to be querying for any columns that are all NULL
or zero length.
Also, a column may have been populated at some point in the past, but is
currently obsolete and not used. If you really need to audit which tables /
columns are in use by the application during normal usage, then you can
setup an object trace in SQL Server Profiler and let it run the background
for a complete business cycle.
http://msdn.microsoft.com/library/d...
ethowto15.asp
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||I'd suggest running the trace (after it has been tuned to only trace relevan
t
events and return relevant data) for a month (or whatever the actual
turn-over time is).
After that you need to analyze the trace data, identifying individual unused
objects.
The following activities should not be done in live production.
Unused objects should first be renamed before actually being dropped -
rename one at a time to get a clear picture of their actual usage.
After the system has been running successfully for your specific turn-over
time with the renamed objects, you could consider dropping them - in a test
environment first, of course.
This is the bottom-to-top approach.
There is, however, another approach to this - analyze your actual business
requirements. If you think you have unused columns, maybe the entire data
model is wrong for you, and needs to be redesigned. This approach may even
yield better results.
ML
http://milambda.blogspot.com/