Hi,
We have scheduled a job for DB Reindexing (Maitinance Plan) for a OLTP database on sunday.
We have used mirroring for automatic failover with a witness server now the DR Reindexing job fails after 30 mins without any error.
Please let me know why Database reindexing gets failed.
Regards
Sufian
All,
I've got a medium sized database in a mirror configuration with witness. The database size is about 300gb and I would to reindex all of the tables in the database. My process would go something like this:
1) Backup principal
2) Break the mirror
3) Set the principal database to simple recovery mode
4) Perform the reindexing
5) Backup the principal and transfer that backup to the mirror
6) Restore the backup
7) Re-establish the mirror
Does anyone see any issues with the process itself?
Regards,
Ian
hi
i have reindex of the tables
is it really improve performance that making table reindexing ?
I need to reindex all tables in my database and would like to do this without using a Cursor. What is the simplest way to achieve this.
Cheers
Nat
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
View 5 Replies View RelatedI have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
View 2 Replies View RelatedWhen I issue this command:
ALTER DATABASE foo set PARTNER = 'TCP://10.3.3.1:1234'
I get this error message:
The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed.
What does that mean, and how do I fix it?
Hi,
View 6 Replies View RelatedAttempt to fetch logical page (1:166354) in database 'pm_pmc_prod' belongs to object '837120', not to object 'ng_visit'.
I got his error while importing data into the error, when i ran dbcc checkdb it gave me Msg 8928,8942,8976 etct witl serverity level 16...
is it possible to fix this curropt tables?
We load tables from text files for inquiries.
My procedure is to truncate the table
Use DTS to move the text file into the table
do the command DBCC DBREINDEX (TABLE,'')
Am I wasting my time?
Does SQL 7 rebuild the indicis as it loads the data from the text file?
Hello guys,
Two things:
1) Could somebody explain me how to reindex all tables in my db?
2) How do I know when I should reindex my db?
Thank you very much for any help!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
I've just recently tried to perform a scheduled reindexing job with the following command:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
Unfortunately, this command has only finished once of the five or six times I've tried to run it. The times it has failed, it deadlocks behind what seems to be a succession of other transactions. How can I make sure this command finishes?
Hello Everyone,
I need to rebuild all indexes in one table, is there any command that will do automatically instead of deleting and recreating them all?
Any help will be appreciated.
Thanks
Hi All,
is it really improve performance that making table reindexing?
what i mean to say is i've one script, which will automatically drops all the indexes in a database, and reconstruct them with the same name.
is it really worth doing that?.....
thankyou very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
Hi experts,
For defragmenation and reindexing they are using the below cursor, and now they have asked me to remove the cursor and schedule the job accordingly to do the same functionality, so what will be the other way we can do without cursor? can you plase let me know the solution?
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT database_name FROM dbadmin.dbo.tdbstatus WHERE status='y'
AND database_name NOT IN ('master','model','msdb','tempdb')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
Your help will be appreciated.
I set up a new mirror server. Everything is good except that the Database Mirroring Monitor is not working for one of the databases. In the monitor the principal data is showing up as blank
If I look at dbm_monitor_data on the principal most of the data columns (e.g. Send_queue_size) are null where as they have data for the other databases.
Both servers are SQL Server 9.0.2047 Enterprise Edition.
Any idea what might be going on here?
Thanks in advance.
In the Enterprise Manager of SQL Server 2000 I have set up a maintenance plan which rebuilds my indexes. I've stuided the documentation, and from what I've learned what happens behind the curtain is that several DBCC REINDEX commands are being issued.
Question:
If I have 20 tables and 40 indexes: Will SQL Server do the maintance plan in 1 single transaction, or will it divide the it up to eg. 20 or 40 transactions?
-h
Hi
We are upgrading from sql 7 to 2000.During the upgrade process do we have to do a reindexing of all tables or will update statistics take care of that.
Or do we have to do both?
What is the difference between reindexing and update statistics.
Thanks
Madhukar Gole
Hi All,
Just after some feedback on a scenario where we have full logging setup on one of the databases, and the transaction logs are backed up every 60min. At 0000-0100 the log jumps from being a few thousand k up to over 1.7gb.
I did some profiling for this time, and it appears that this jump is related to the reindexing of the indexes on the database.
Is this normal for the log file to jump in size so much? Or is this an indication of some other issue (potentially with the indexes)?
Is there any way that the reindexing can be excluded from the log files or is this a necessity?
Thanks in advance for your help.
Cheers
Troy
Folks,I work on a system which is growing rapidly, with the number oftransactions we process growing on a daily basis. While this is goodnews or the business, maintenance is starting to become an issue as thedatabase is the backend for a website which cannot be down for alengthy period of time.While I do defrag the indexes, periodically the indexes do need to berebuilt. When this happens, the process locks pages and transactionsstart getting bounced out.Are their any third party utilities which will rebuild an indexwithout this locking occuring? Any help in pointing me in the rightdirection would be appreciated.
View 1 Replies View RelatedDoes someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.
View 1 Replies View RelatedAll,
I've got a medium sized database in a mirror configuration with witness. The database size is about 300gb and I would to reindex all of the tables in the database. My process would go something like this:
1) Backup principal
2) Break the mirror
3) Set the principal database to simple recovery mode
4) Perform the reindexing
5) Backup the principal and transfer that backup to the mirror
6) Restore the backup
7) Re-establish the mirror
Does anyone see any issues with the process itself?
Regards,
Ian
Hi All,
Does anybody know the OS requirements for Database Mirroring? I checked BOL and Google but couldn't find any info on that.
Thanks.
Hi All,
I have read on the web that high protection mode not recommended, except in the event of replacing the existing witness server. But I can't find the reason why anywhere. Can anybody explain? Thanks.
Hi All,
Couple of questions about database mirroing.
1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?
2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?
Thanks.
for a database mirroring , which stretergy is good.
Creating with witness server or without it.?
I am firsttime doing this , so i am posting it to the forum..
and also if we are creating the database mirroring with witness server for automatic failover , will the witness server need the same amount of harddisk space like the pricipal or mirror server.??
thanks
we want to migrate our production server , without taking the database down.so what we did was , we mirrored the databases from production to one of our development servers and we took the prodcution server for uprgrade.
so today we are moving back (mirroring) all the databases from that development server to our new production server.
my question is , when i move
productionserver_old(principal server) - having the script for principal server
development server(mirror server---->principalserver)) - having the script for mirror server , which is now acting as principal server
new production server(mirror server) - having the script for mirror server. will become principal server , once i moved all the databases
now while mirroring between the developement server and the new production server, i have to run the scripts for the mirror server only.
so both the servers are havign the mirror script now...
is that a problem, or any other way i can handle this....
Hi All,
Does anybody know any good Tutorials for Database Mirroring (Automatic Failover) or Manual Sync with Mirror Server. I tried some sites online, but doesn't have detailed steps..
pls.help as i need to sync data with One of my Mirror using Sql Server 2005.
thanks
Hi,
I have some questions about database mirroring. we have almost 40 databases in one server. Can I set up the database mirroring for all the databases. Is it going to any affect on performance. we have already setup the mirroring on almost 30 databases, and I need to set up for the rest of 10. please some body could help on this. Thank you.
Hi,
i'm a novice of sql server and I have a problem.
I have to reply a server in which there are database that are managed with sql server 2000 sp3; what I must make in order to set up the mirroring between the database of the two server so that the data are always aligned ?
thanks for the attention
best regards
alessandro
I am new user of SQl and preparing the exam database mirroring....can you tell me how i start the mirroirng ..what is my first tep..and is there only one instance
View 3 Replies View RelatedHi All,
Couple of questions about database mirroing.
1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?
2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?
Thank you.